SqlBulkCopy

If you are ever in a scenario where you are going to be doing tons of repetitive inserts, consider using the new SqlBulkCopy command that's found in .NET 2.0.   It uses an insert bulk to do all of your inserts in a single round trip, and has settings which you can use to batch up or break up the inserts if needed, and a timeout setting as well.  The potential performance savings are huge, especially if you are doing things like importing data from outside vendors or processing data submissions from third parties via FTP or web services.

 

Here's an example of how it's used.

 

First you would create a bulk copy object given the connection string.

SqlBulkCopy oBulk = new SqlBulkCopy(yourconnectionstring);

Then you would create a datatable to import...in this case I'll call some DB method
DataTable dtBulk = GetAllZipsOutMyDatabase();

Next set the destination table name where you want the bulk copy to go:

oBulk.DestinationTableName = "MyDestinationTable"

 

Now this next step is important, IF the columns in your datatable do not exactly match the column structure in the destination table.  If your local datatable is structured exactly like the destination table, meaning all of the columns are represented and they are the correct datatype, you can do without this step.  However, if your table has an identity column, or some columns are not represented, you MUST map your columns to the destination table.  In this example I am using the names of the local datatable columns and mapping them onto the destination table, since I named my local datatable columns the same as they are in the destination table.

oBulk.ColumnMappings.Clear();
foreach(DataColumn oCol in dtBulk.Columns)

{
         oBulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(oCol.ColumnName, oCol.ColumnName));

}

Now that that's done I can set the batch size and the timeout:

oBulk.BatchSize = 1000;
oBulk.BulkCopyTimeout = 6000;

Now I'm all set up and I can write to the server.

oBulk.WriteToServer(dtBulk);

And that's it.  Rather than looping on a bunch of inserts, I can do all of the inserts in a single round trip.

 

Happy bulk copying,

 

Rob