Skip to content

Commit 8b7bf0b

Browse files
author
Matt Richard
committed
Fixes issue ThatRendle#338. (SqlBulkInserter breaks when the records given to it aren't all the same.)
1 parent 35e2e06 commit 8b7bf0b

3 files changed

Lines changed: 112 additions & 4 deletions

File tree

‎Simple.Data.SqlServer/SqlBulkInserter.cs‎

Lines changed: 18 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -44,13 +44,15 @@ public IEnumerable<IDictionary<string, object>> Insert(AdoAdapter adapter, strin
4444
using (bulkCopy)
4545
{
4646
connection.OpenIfClosed();
47-
foreach (var record in data)
47+
48+
var dataList = data.ToList();
49+
foreach (var record in dataList)
4850
{
4951
if (count == 0)
5052
{
51-
dataTable = CreateDataTable(adapter, tableName, record.Keys, bulkCopy);
53+
dataTable = CreateDataTable(adapter, tableName, dataList.SelectMany(r => r.Keys).Distinct(), bulkCopy);
5254
}
53-
dataTable.Rows.Add(record.Values.ToArray());
55+
AddRow(dataTable, record);
5456

5557
if (++count%5000 == 0)
5658
{
@@ -82,7 +84,7 @@ private SqlBulkCopyOptions BuildBulkCopyOptions(AdoAdapter adapter)
8284
return options;
8385
}
8486

85-
private DataTable CreateDataTable(AdoAdapter adapter, string tableName, ICollection<string> keys, SqlBulkCopy bulkCopy)
87+
private DataTable CreateDataTable(AdoAdapter adapter, string tableName, IEnumerable<string> keys, SqlBulkCopy bulkCopy)
8688
{
8789
var table = adapter.GetSchema().FindTable(tableName);
8890
var dataTable = new DataTable(table.ActualName);
@@ -104,5 +106,17 @@ private DataTable CreateDataTable(AdoAdapter adapter, string tableName, ICollect
104106

105107
return dataTable;
106108
}
109+
110+
private void AddRow(DataTable dataTable, IDictionary<string, object> record)
111+
{
112+
var dataRow = dataTable.NewRow();
113+
foreach (DataColumn column in dataTable.Columns)
114+
{
115+
if (record.ContainsKey(column.ColumnName))
116+
dataRow[column] = record[column.ColumnName];
117+
}
118+
dataTable.Rows.Add(dataRow);
119+
}
120+
107121
}
108122
}

‎Simple.Data.SqlTest/BulkInsertTest.cs‎

Lines changed: 82 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -52,6 +52,59 @@ public void BulkInsertUsesSchemaAndFireTriggers()
5252
Assert.AreEqual(1000, rowsWhichWhereUpdatedByTrigger);
5353
}
5454

55+
56+
[Test]
57+
public void BulkInsertRecordsWithDifferentColumnsProperlyInsertsData()
58+
{
59+
DatabaseHelper.Reset();
60+
61+
var db = DatabaseHelper.Open();
62+
dynamic r1 = new SimpleRecord();
63+
r1.FirstName = "Bob";
64+
r1.LastName = "Dole";
65+
66+
dynamic r2 = new SimpleRecord();
67+
r2.FirstName = "Bob";
68+
r2.MiddleInitial = "L";
69+
r2.LastName = "Saget";
70+
71+
db.OptionalColumnTest.Insert(new[] { r2, r1 });
72+
73+
var objs = db.OptionalColumnTest.All().ToList<OptionalColumnTestObject>();
74+
75+
var expected = new[] {new OptionalColumnTestObject("Bob", "Dole"), new OptionalColumnTestObject("Bob", "Saget", "L"),};
76+
77+
Assert.That(objs, Is.EquivalentTo(expected));
78+
79+
}
80+
81+
[Test]
82+
public void BulkInsertRecordsWithDifferentColumnsAndFewerColumnsInFirstRecordProperlyInsertsData()
83+
{
84+
DatabaseHelper.Reset();
85+
86+
var db = DatabaseHelper.Open();
87+
88+
dynamic r1 = new SimpleRecord();
89+
r1.FirstName = "Bob";
90+
r1.LastName = "Dole";
91+
92+
dynamic r2 = new SimpleRecord();
93+
r2.FirstName = "Bob";
94+
r2.MiddleInitial = "L";
95+
r2.LastName = "Saget";
96+
97+
db.OptionalColumnTest.Insert(new[] { r1, r2 });
98+
99+
var objs = db.OptionalColumnTest.All().ToList<OptionalColumnTestObject>();
100+
101+
var expected = new[] { new OptionalColumnTestObject("Bob", "Dole"), new OptionalColumnTestObject("Bob", "Saget", "L"), };
102+
103+
Assert.That(objs, Is.EquivalentTo(expected));
104+
105+
}
106+
107+
55108
private static IEnumerable<SchemaItem> GenerateItems()
56109
{
57110
for (int i = 0; i < 1000; i++)
@@ -61,6 +114,35 @@ private static IEnumerable<SchemaItem> GenerateItems()
61114
}
62115
}
63116

117+
class OptionalColumnTestObject
118+
{
119+
public int Id { get; set; }
120+
public string FirstName { get; set; }
121+
public string LastName { get; set; }
122+
public string MiddleInitial { get; set; }
123+
124+
public OptionalColumnTestObject() {}
125+
126+
public OptionalColumnTestObject(string first, string last, string middle = null)
127+
{
128+
FirstName = first;
129+
LastName = last;
130+
MiddleInitial = middle;
131+
}
132+
133+
public override string ToString()
134+
{
135+
return string.Format("<FirstName={0}, LastName={1}, MiddleInitial={2}>", FirstName, LastName, MiddleInitial);
136+
}
137+
138+
public override bool Equals(object obj)
139+
{
140+
var other = obj as OptionalColumnTestObject;
141+
if (other == null) return false;
142+
return other.FirstName == FirstName && other.LastName == LastName && other.MiddleInitial == MiddleInitial;
143+
}
144+
}
145+
64146
class SchemaItem
65147
{
66148
public SchemaItem(int id, string description)

‎Simple.Data.SqlTest/Resources/DatabaseReset.txt‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -86,6 +86,8 @@ BEGIN
8686
DROP TABLE [dbo].[HierarchyIdTest]
8787
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TimestampTest]') AND type in (N'U'))
8888
DROP TABLE [dbo].[TimestampTest]
89+
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OptionalColumnTest]') AND type in (N'U'))
90+
DROP TABLE [dbo].[OptionalColumnTest]
8991

9092
CREATE TABLE [dbo].[Users] (
9193
[Id] INT IDENTITY (1, 1) NOT NULL,
@@ -245,6 +247,16 @@ BEGIN
245247
[Id] ASC
246248
))
247249

250+
CREATE TABLE [dbo].[OptionalColumnTest](
251+
[Id] [int] IDENTITY(1,1) NOT NULL,
252+
[FirstName] [nvarchar](50),
253+
[LastName] [nvarchar](50),
254+
[MiddleInitial] [nvarchar](50),
255+
)
256+
257+
ALTER TABLE [dbo].[OptionalColumnTest]
258+
ADD CONSTRAINT [PK_OptionalColumnTest] PRIMARY KEY CLUSTERED ([Id] ASC)
259+
248260
BEGIN TRANSACTION
249261
SET IDENTITY_INSERT [dbo].[Customers] ON
250262
INSERT INTO [dbo].[Customers] ([CustomerId], [Name], [Address]) VALUES (1, N'Test', N'100 Road')

0 commit comments

Comments
 (0)