Perform bulk copies with .NET 2.0's SqlBulkCopy class

by Tony Patton | May 29, 2007 5:12:00 PM

Tags: Databases, .NET, Microsoft .NET, development task, DestinationTableName property, Tony Patton, SqlBulkCopy, SqlBulkCopy class, Microsoft SQL Server, data source, database, server, .NET Newsletter

Takeaway: The .NET Framework 2.0's SqlBulkCopy class allows you to easily move data programmatically from any data source to a SQL Server table. Learn how to use SqlBulkCopy to streamline data transfers.

A common development task is transferring data between disparate data sources. If you have worked with SQL Server, the bcp (bulk copy) command will be familiar to you. It allows you to quickly bulk copy large files into SQL Server tables or views. With .NET Framework 1.1, you can utilize bcp via a SqlCommand object, but .NET Framework 2.0 adds the SqlBulkCopy class to simplify the chore.

The SqlBulkCopy class

While you may still use the t-sqlbcp command, the SqlBulkCopy class offers a significant performance advantage. You can only use the class to write data to SQL Server tables, but any data source may be used. The only caveat is the contents of the data source must be able to be loaded into a DataTable object.

Before I delve into the details of using the class, here's a brief outline of what the class provides:

• A single bulk copy operation, so data is moved from a data source to a SQL Server table.
• Multiple bulk copy operations may be performed.
• A bulk copy operation may exist within a database transaction.

MSDN offers more details about the methods and properties exposed in the SqlBulkCopy class. The most important property is DestinationTableName, and the most important method is WriteToServer.

The DestinationTableName property specifies the table to receive the copied records. It follows SQL Server syntax, as it is a three-part name (database.owningschema.name). You may qualify the table name with its database and owning schema. The database is often specified within the connection string (via Initial Catalog value). Also, if the table name uses an underscore or any other special characters, you must escape the name using surrounding brackets, as in [database.owningschema.name].

This overloaded WriteToServer method performs the actual bulk copy. It accepts DataTable, DataRow, and IDataReader objects as data sources for the copy. You may also include a DataRowState value with the DataTable object to specify only rows matching the state are to be copied. The next Windows console application demonstrates the very basic operation of copying data from one database table to another. It uses the standard SQL Server 2000 Northwind database while copying data from its Employees table to an exact copy (the design) of this table called Employees_bcp.

View Listing A (the equivalent VB.NET code is in Listing B). Basically, the code connects to the database and reads all values from the Employees table into SqlDataReader object. The destination table name is set, and the SqlDataReader object is used to perform the bulk copy operation (it is passed as the only parameter). You may examine the destination table on the server to verify the data has been copied.

Performing multiple updates is no different -- you simply reuse the SqlBulkCopy object. Copying from one table to another within the same database is a misuse of bcp. On the other hand, importing data from an external file is a common task. Listing C takes this approach as a text file is imported into the table used in the previous example. (Listing D contains the equivalent VB.NET code.) The code creates a new DataTable object with columns for each piece of data. The file is read one line at a time and parsed on the comma character with each data value assigned to the appropriate column in the DataTable. Next, the ColumnMappings property of the SqlBulkCopy class allows you to map a column from the data source (our DataTable) to the destination using the column names. With the mappings in place, the WriteToServer method performs the actual bulk copy with the DataTable passed to it.

Data migration best practices

Although data migration is a common task for developers, it usually isn't a very popular one. Share your best (and worst) data migration stories and best practices with the .NET community by posting to the article discussion.

Miss a column?

Check out the .NET Archive, and catch up on the most recent editions of Tony Patton's column.

Tony Patton began his professional career as an application developer earning Java, VB, Lotus, and XML certifications to bolster his knowledge.

Print/View all Posts Comments on this article

Identity ColumnsStephenInScotland  | 05/30/07

RE: Perform bulk copies with .NET 2.0's SqlBulkCopy classlt9440@...  | 03/04/08