Introduction
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 I'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.
Write a Sample Application
Let's leave the theory and see an example. I think the sample application you'll see in a moment can cover all you need to use SqlBulkCopy.
I write a simple Windows Application that isn't more than a WinForm. This WinForm has a Button. User clicks on it and my code copies some columns of Orders table from Northwind database to my own database.
So I start by creating a database with only one table. This table has these columns (their correspond column in Orders table have been specified in parentheses):
- ID (OrderID): int
- Name (ShipName): nvarchar(40)
- Address (ShipAddress): nvarchar(60)
- City (ShipCity): nvarchar(15)
I also create a Stored Procedure to select my desire columns from source Orders table in Northwind database:
CREATE PROCEDURE dbo.SelectOrders
AS
SELECT OrderID, ShipName, ShipAddress, ShipCity
FROM Orders
My event handler for Button's click is as simple as calling two private methods. First I call SelectDataFromSource() method by passing the connection string of source (Northwind) database and save its result in a DataTable then pass this DataTable and my destination database connection string to CopyDataToDestination() method to copy them to destination table.
private void btnStart_Click(object sender, EventArgs e)
{
String sourceConnectionString =
"Data Source=KEYVANNAYYERI;Initial Catalog=Northwind;Integrated Security=True";
String destinationConnectionString =
"Data Source=KEYVANNAYYERI;Initial Catalog=SqlBulkCopySample;Integrated Security=True";
DataTable data = SelectDataFromSource(sourceConnectionString);
CopyDataToDestination(destinationConnectionString, data);
}
The logic for SelectDataFromSource is very simple. It just returns a DataTable of records from Orders table using SelectOrders Stored Procedure.
private DataTable SelectDataFromSource(String connectionString)
{
DataTable data = new DataTable();
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("SelectOrders", connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
data.Load(reader);
}
return data;
}
Finally my CopyDataToDestination() method which is the main part. First I create four SqlBulkCopyColumnMapping objects and map my source columns to destination columns then create a SqlBulkCopy object and pass my destination database connection string to its constructor. Now I set its BatchSize and BulkCopyTimeOut properties and add my SqlBulkCopyColumnMapping objects to its ColumnMappings property and specify its DestinationTableName. Not it's time to add a handler to SqlRowsCopied event handler and set a NotifyAfter property and call the WriteToServer() method at the end to bulk copy the data.
private void CopyDataToDestination(String connectionString, DataTable table)
{
SqlBulkCopyColumnMapping mapping1 =
new SqlBulkCopyColumnMapping("OrderID", "ID");
SqlBulkCopyColumnMapping mapping2 =
new SqlBulkCopyColumnMapping("ShipName", "Name");
SqlBulkCopyColumnMapping mapping3 =
new SqlBulkCopyColumnMapping("ShipAddress", "Address");
SqlBulkCopyColumnMapping mapping4 =
new SqlBulkCopyColumnMapping("ShipCity", "City");
SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString);
bulkCopy.BatchSize = 100;
bulkCopy.BulkCopyTimeout = 5;
bulkCopy.ColumnMappings.Add(mapping1);
bulkCopy.ColumnMappings.Add(mapping2);
bulkCopy.ColumnMappings.Add(mapping3);
bulkCopy.ColumnMappings.Add(mapping4);
bulkCopy.DestinationTableName = "MyData";
bulkCopy.SqlRowsCopied +=
new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
bulkCopy.NotifyAfter = 200;
bulkCopy.WriteToServer(table);
}
In bulkCopy_SqlRowsCopied handler, I simply show a MessageBox that lets end user to know how many rows have been copied so far.
void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
MessageBox.Show
(String.Format("{0} Rows have been copied.", e.RowsCopied.ToString()));
}
Now if I check my database trace logs, can see there are less attempts to database and this improves performance and speed.
Summary
In this article I talked about newly added feature in ADO.NET 2.0, SqlBulkCopy, which helps you to bulk copy large amounts of data between data source and data table and improve your performance.
Downloads
You can download the source code of my sample application from here.
About Keyvan Nayyeri
 |
Keyvan is a software architect and developer who has a bachelor of science degree in applied mathematics. He was born in Kermanshah, Kurdistan, in 1984.
Keyvan’s main focus is on Microsoft development technologies and their related technologies such as mark-up languages. He’s also experie...
View complete profile
|
Top Articles in this category
Two-way data binding in 3-Tier web application
In this article I would like to examine two-way data-binding in 3-tier web application and how using XLib library can substantially decrease the amount of data-binding code without compromising the amount of control you have over it. I start by describing the ways data flow has been handled in 3-tier applications and how XLib improves upon it.
Bulk Operations Using Oracle Data Provider for .NET (ODP.NET)
Chinh Do describes techniques to improve application performance with bulk operations using ODP.NET.
Mapping DataSet to XML and backwards
This article illustrates the translation to and from an ADO .NET DataSet and XML.
Managing Transactions using TransactionScope
A transaction is a unit of work. You use transactions to ensure the
consistency and integrity of a database. If a transaction is successful, all of
the data modifications performed during the transaction are committed and made
permanent. If an error occurs during a transaction, you can roll back the
transaction to undo the data modifications that occurred during the transaction.
Modeling domains with The Entity Data Model
Patrick Loewendahl talks about modeling domains with the Entity Data Model.
|
|
Please login to rate or to leave a comment.