SqlBulkCopy is a new feature in ADO.NET 2.0 and above, that lets you to perform copy operation for a large amount of data between a source data store and a destination data table.
Copying a large amount of data from a source data store to a destination table in SQL database by using a traditional approach has performance effect because you need to call database for several times. There were some ways to solve this issue but now by having ADO.NET 2.0 in hand, you can perform a bulk copy and reduce the number of database accesses to improve performance and speed. SqlBulkCopy is the heart of bulk copy in ADO.NET 2.0 and SqlBulkCopyColumnMapping and SqlBulkCopyColumnMappingCollection objects assist it in this way. Later We’ll discuss these objects in more details.
SqlBulkCopy
SqlBulkCopy is the object that helps you to perform a bulk copy. You can use a DataReader or DataTable as source data store (you can load your data from SQL database , Access database, XML or … into these objects easily) and copy them to a destination table in database. To accomplish this task, SqlBulkCopy uses a collection of SqlBulkCopyColumnMapping objects which will be saved as its SqlBulkCopyColumnMappingCollection property. SqlBulkCopyColumnMapping maps a column in data source to a table in destination table via their name or index.
SqlBulkCopy has some important properties that you should be aware of them to be able to use it:
- BatchSize: This integer value specifies the number of rows that should be copied in each attempt to copy to database. This value has direct effect on the number of accesses to database.
- BulkCopyTimeOut: The number of seconds that system should wait to let SqlBulkCopy to copy rows.
- ColumnMappings: A ReadOnly SqlBulkCopyColumnMappingCollection. You need to use its Add() method to add a new SqlBulkCopyColumnMapping object to its collection.
- DestinationTableName: String value of destination table’s name.
- NotifyAfter: SqlRowsCopied event handler will be called when the number of rows specified in this property has been copied.
This object also has four overloads. You can pass a SqlConnection (or a connection string) plus an optional SqlBulkCopyOptions and SqlTransaction to its constructor. Latest two parameters can change the behavior of SqlBulkCopy object. Using SqlBulkCopyOptions enumerator you can specify that for example SqlBulkCopy keeps identities or check constraints and some other options. Using SqlTransaction you can pass an external SqlTransaction and your SqlBulkCopy uses this transaction in all parts of the process.
SqlBulkCopy also has a SqlRowsCopied event handler that triggers when the specific number of DataRows that have been copies. You specified this value via NotifyAfter property. This handler is helpful when you want to be aware of your process (for instance showing it via a ProgressBar to end user).
The last thing that should be mentioned about SqlBulkCopy object is its WriteToServer() method. This method can get an array of DataRows, a DataTable or a DataReader and copies their content to destination table in database.
SqlBulkCopyColumnMapping
SqlBulkCopyColumnMapping is the object that maps your source columns to destination columns in a bulk copy. A SqlBulkCopyColumnMapping can get the source and destination column names or ordinals via its properties or its constructor. It has these properties:
- SourceColumn: String value of source column’s name.
- SourceOrdinal: Integer value of source column’s index.
- DestinationColumn: String value of destination column’s name.
- DestinationOrdinal: Integer value of destination column’s index.
One of SourceColumn and SourceOrdinal and one of DestinationColumn and DestinationOrdinal should be set. Also you can set these properties via constructor which is an easier way.
Note that if your source and destination columns have same names, it’s not required to use SqlBulkCopyColumnMapping objects because SqlBulkCopy can do its job automatically.
#region (METHOD) CopyDataToDestination(String connectionString, DataTable table, string destinationTableName)
//SqlBulkCopy is a new feature in ADO.NET 2.0 that lets you to perform copy operation for a large amount of data between a source data store and a destination data table.
//
private void CopyDataToDestination(String connectionString, DataTable table, string destinationTableName)
{
try
{
SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString);
bulkCopy.BatchSize = 100; // number of records to be inserted in a single batch
bulkCopy.BulkCopyTimeout = 60; //Time out for bulk copy
fileRecCount = table.Rows.Count;
foreach (DataColumn col in table.Columns)
{
SqlBulkCopyColumnMapping mapping1 = new SqlBulkCopyColumnMapping(col.ColumnName, col.ColumnName);
bulkCopy.ColumnMappings.Add(mapping1);
}
bulkCopy.DestinationTableName = destinationTableName;
bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
//bulkCopy.
//This property intructs bulkcopy to notify about the progress of data transfer through the
// event: SqlRowsCopied
bulkCopy.NotifyAfter = 200;
bulkCopy.WriteToServer(table);
}
catch (Exception ex)
{
//Any exception occured while bulkcopy,
//like Datatype mismatch, column mismatch will be catched here
LogUtils.LogError("Exception Occured During BulkCopy",ex, log);
}
}
void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
LogUtils.LogDebug(String.Format("{0} Rows have been copied.", e.RowsCopied.ToString()), log);
saveRecCount = (int) e.RowsCopied;
//MessageBox.Show(String.Format("{0} Rows have been copied.", e.RowsCopied.ToString()));
// copyFinished = true;
}
#endregion
[/code]
I hope you got some idea on my sample program. I will write more detail later, my keyboard is sticky and i need to format the code etc.. So if you have any doubts write to me.. Stay tuned to my blog….
Thanks for spending time reading this article.
Hello Nithin,
your code sounds great !
However I have some issues to make it work.
fileRecCount = table.Rows.Count;
The fileRecCount variable is not declared ! (same question for saveRecCount)
LogUtils.LogError(“Exception Occured During BulkCopy”,ex, log);
What is the logutils ? do I have to declare someting or add a reference ?
The log parameter is also not declared
What am i missing, i am totally new to .net 🙂
Thx and regards
It’s just a small piece of code from my samples.
saveRecCount was been declared globally. so that’s the reason you are seeing the same inside the method. logutils is an implementation of my logic where it logs all exceptions to a file.
If you are totally new to .net, how are you doing coding on .NET. just a casual thought. i think you could figure out the same from above code with little bit understanding on .NET. just comment logutils, since you wouldn’t be needing it.