ADO.NET 3.5 Cookbook, 2nd Edition

10.10. Bulk Copying with SQL Server

Problem

You need to bulk copy data into a SQL server table from another SQL Server table, or from an external file such as an XML file.

Solution

Use the SqlBulkCopy class.

The solution shows how to bulk copy data into a SQL Server table from two sources: a DataReader and an XML file.

Both solutions copy data into a table named AddressExpand in the database AdoDotNet35Cookbook. Execute the following T-SQL statement to create the table:

	USE AdoDotNet35Cookbook
	GO
	CREATE TABLE AddressExpand(
	    AddressID int NOT NULL PRIMARY KEY,
	    AddressLine1 nvarchar(60) NOT NULL,
	    AddressLine2 nvarchar(60) NULL,
	    City nvarchar(30) NOT NULL,
	    StateProvinceName nvarchar(50) NOT NULL,
	    PostalCode nvarchar(15) NOT NULL,
	    CountryRegionName nvarchar(50) NOT NULL )

The first solution creates a DataReader on the join of three tables in AdventureWorks that returns denormalized addresses for the data in the Person.Address table in AdventureWorks.A IDataReader instance is created for the query and used with the WriteToServer() method of the SqlBulkCopy class to perform the bulk copy into the AddressExpand table in the AdoDotNet35Cookbook database.

The C# code in Program.cs in the project BulkCopyDataReader is shown in Example 10-17.

Example 10-17. File: Program.cs for BulkCopyDataReader solution

using System; using System.Data; using System.Data.SqlClient; namespace BulkCopyDataReader { class Program { static void Main(string[] args) { string srcConnectString = "Data Source=(local);" + "Integrated ...