SqlBulkCopy:
SqlBulkCopy class which has been introduced .NET2.0 onwards allows bulk insertion into Sql Server database.
Limitation of using SqlBulkCopy:
1. Only insertion is allowed. We can not achive updation any single row of databse (sql server) while using SqlBulkCopy class.
2. We can insert into SqlServer database only.
3. While inserting into db, SqlBulkCopy does not do any validation. SqlBulkCopy takes data "as is" and tries to insert data into Sql Server database.
For an example, say if you have column of type DateTime in Sql Server database. While using SqlCommand a string value is aumatically converted into DateTime in database. But in same scenario SqlBulkCopy fails.
If you have datatime column in Database, in memory also you need to have a datetime type column.
The bottomline is : SqlBulkCopy does not do any data validation or conversion for you. Therfore using SqlBulkCopy class error creps in frequently. Programmer need to take care of data validation also.
Advantage of SqlBulkCopy:
It inserts values much faster way that any other.
Programming Scenario:
You may come across following programming scenario
------------------------------------------------------------------------
Data source_____ Data destination
------------------------------------------------------------------------
1. A table in Sql Server_-----A table in Sql Server
------------------------------------------------------------------------
2. In memory----------------------------------------------------
--data representation-----------------------------------------
i.e. a DataTable object--------A table in Sql Server--
------------------------------------------------------------------------
3.Oracle or------------------ ------------------------------------
Other database-------------------A table in Sql Server
------------------------------------------------------------------------
4. Sql Server-----------------------A table in Sql Server
------------------------------------------------------------------------
Following is code shown for programming scenario 2
Assumtion:
1. "dtLog" is a DataTable with data.
2. Sql Server database table name is stored into app.config file.
3. Sql Server database table structure is like following
3. UpdateDatbase() function takes the a DataTable object as parameter and inserts all rows
into Sql Server DataBase.
CODE:
private void UpdateDatabase(DataTable dtLog)
{
SqlBulkCopy bulkCopy = null;
Stopwatch sw = new Stopwatch();
sw.Start();
SqlBulkCopyColumnMapping map1 = null, map2 = null, map3 = null, map4 = null, map5 = null, map6 = null, map7 = null, map8 = null, map9 = null, map10 = null, map11 = null, map12 = null,map13 = null;
try
{
bulkCopy = new SqlBulkCopy (Settings.Default.DBConnString ,SqlBulkCopyOptions.KeepIdentity);
map1 = new SqlBulkCopyColumnMapping("EngagementId", "EngagementId");
map2 = new SqlBulkCopyColumnMapping("EngagementName", "EngagementName");
map3 = new SqlBulkCopyColumnMapping("SyncGroup", "SyncGroup");
map4 = new SqlBulkCopyColumnMapping("SyncStartTime", "SyncStartTime");
map5 = new SqlBulkCopyColumnMapping("ClientSelectChangeStart", "ClientSelectChangeStart");
map6 = new SqlBulkCopyColumnMapping("ClientSelectChangeEnd", "ClientSelectChangeEnd");
map7 = new SqlBulkCopyColumnMapping("ServerApplyChangeStart", "ServerApplyChangeStart");
map8 = new SqlBulkCopyColumnMapping("ServerApplyChangeEnd", "ServerApplyChangeEnd");
map9 = new SqlBulkCopyColumnMapping("ServerSelectChangeStart", "ServerSelectChangeStart");
map10 = new SqlBulkCopyColumnMapping("ServerSelectChangeEnd", "ServerSelectChangeEnd");
map11 = new SqlBulkCopyColumnMapping("ClientApplyChangeStart", "ClientApplyChangeStart");
map12 = new SqlBulkCopyColumnMapping("ClientApplyChangeEnd", "ClientApplyChangeEnd");
map13 = new SqlBulkCopyColumnMapping("SyncEndTime", "SyncEndTime");
bulkCopy.ColumnMappings.Add(map1);
bulkCopy.ColumnMappings.Add(map2);
bulkCopy.ColumnMappings.Add(map3);
bulkCopy.ColumnMappings.Add(map4);
bulkCopy.ColumnMappings.Add(map5);
bulkCopy.ColumnMappings.Add(map6);
bulkCopy.ColumnMappings.Add(map7);
bulkCopy.ColumnMappings.Add(map8);
bulkCopy.ColumnMappings.Add(map9);
bulkCopy.ColumnMappings.Add(map10);
bulkCopy.ColumnMappings.Add(map11);
bulkCopy.ColumnMappings.Add(map12);
bulkCopy.ColumnMappings.Add(map13);
bulkCopy.DestinationTableName = Settings.Default.DBTableName.Trim();
bulkCopy.WriteToServer(dtLog);
}
catch (Exception ex)
{
errorCount++;
ApplicationLog(String.Format("Exception in UpdateDatabase. Message : {0},StackTrace {1}", ex.Message, ex.StackTrace));
}
finally
{
sw.Stop();
bulkCopy = null;
txtResult.Text = (sw.ElapsedMilliseconds / 1000.00).ToString();
map1 = null; map2 = null; map3 = null; map4 = null; map5 = null; map6 = null; map7 = null; map8 = null; map9 = null; map10 = null; map11 = null; map12 = null; map13 = null;
}
