SQL Server have great commands like BULK INSERT or OPENROWSET(BULK…) to insert a huge amount of data into a database. .NET Framework has a class SqlBulkCopy that provides similar capabilities in C#. Full documentation is available on MSDN article SqlBulkCopy Class. But SqlBulkCopy has some restrictions on input data. It can read from:
- DataRow[] array;
- DataTable;
- IDataReader.
A simple example of usage can be as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
// input data structure DataTable dtClients = new DataTable(); dtClients.Columns.Add("Name"); dtClients.Columns.Add("CountryCode"); // fill data var row = dtClients.NewRow(); row["Name"] = "Jeff"; row["CountryCode"] = "UK"; dtClients.Rows.Add(row); // bulk insert to SQL Server using (SqlConnection conn1 = new SqlConnection("...")) { using (SqlBulkCopy bc = new SqlBulkCopy(conn1)) { conn1.Open(); bc.DestinationTableName = "dbo.Clients"; bc.ColumnMappings.Add("Name", "Name"); bc.ColumnMappings.Add("CountryCode", "CountryCode"); bc.WriteToServer(dtClients); } } |
SQL Server Profiler shows that the correct command was really executed.
|
1 2 3 4 |
insert bulk dbo.Clients ( [Name] NVarChar(50) COLLATE Cyrillic_General_CI_AS, [CountryCode] NVarChar(3) COLLATE Cyrillic_General_CI_AS ) |
In order to use SqlBulkCopy class elegantly, it would better to have a method or extension to convert a generic list to DataTable or IDataReader. But it’s a topic for my next post.