SqlBulkCopy Matching Columns

If you’re trying to do a SqlBulkCopy using a DataTable and getting lots of column type (integer, string) mismatches, it’s probably because you haven’t mapped not only the column types, but also the column names.

SqlBulkCopy assumes that your DataTable is going to have its columns in the same order as the database table, which will cause mismatches when it tries to send your (string) Name field into your (int) Age column.

Just having your DataTable column names match the table isn’t enough, either. There must be a manual mapping.

But if your DataTable column names are the same as the table column names, a simple method can take care of that mapping for any DataTable you send it.

Let’s say you have a DataTable populated as below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
private void StorePeople(IEnumerable<Person> people) {
 
  string tableName = "PeopleDBTable";
  DataTable peopleDataTable = new DataTable(tableName);
  peopleDataTable.Columns.AddRange(new DataColumn[] {
    new DataColumn("Id", typeInt32), 
    new DataColumn("FirstName"), 
    new DataColumn("ManagerId", typeInt32), 
    new DataColumn("LastName"), 
    new DataColumn("Suffix")
  });
 
  foreach (var person in people)
  {
    DataRow row = peopleDataTable.NewRow();
    row["FirstName"] = person.FirstName;
    row["LastName"] = person.LastName;
    row["ManagerId"] = person.ManagerId;
    row["Suffix"] = person.Suffix;
    peopleDataTable.Rows.Add(row);
  }
 
  BulkCopy(peopleDataTable, tableName)
}

And you have the copy process setup like so:

25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
public void BulkCopy(DataTable table, string tableName) {
 
  var options = SqlBulkCopyOptions.KeepNulls | 
    SqlBulkCopyOptions.KeepIdentity;
 
  using (SqlBulkCopy bulkCopy = 
    new SqlBulkCopy(connection, options))
  {
    bulkCopy.DestinationTableName = 
      "[DatabaseName].[dbo].[" + tableName + "]";
 
    MapColumns(table, bulkCopy);
    try
    {
      // Write from the source to the destination.
      bulkCopy.WriteToServer(table);
    }
    catch (Exception ex)
    {
      // Log the exception somewhere & recover gracefully.
    }
  }
}

Then you’ll want the MapColumns method to take care of that rudimentary mapping:

48
49
50
51
52
53
54
55
56
private void MapColumns(DataTable infoTable, 
  SqlBulkCopy bulkCopy) {
 
  foreach (DataColumn dc in infoTable.Columns)
  {
    bulkCopy.ColumnMappings.Add(dc.ColumnName, 
      dc.ColumnName);
  }
}

And that should take care of all the necessary mappings.

  • OK… my mind just short circuited reading that! hehehe good job!