Bulk Insert into SQL Server using C#

I was recently tasked with a project at my work to update SQL Server database with large amounts of data each hour. Every hour, I’ve got an text file contains more than 500,000 records and there were several that needed processing daily. 

I first tried to get all data from the file into SqlDataReader or DataTable, and iterate over them and insert the current row into the database, but with the amount of data, the inserts were very slow performing to say the least. Then I remembered the SqlBulkCopy class. SqlBulkCopy lets you efficiently bulk load a SQL Server table with data from another source. The SqlBulkCopy class can be used to write data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance. For this example the file will contain roughly 19,000 records, but this code can handle large amounts of data.

The text file looks like:

12371;Stefanie Subram;1963-02-14 00:00:00;S;F;60000;8433 Pacifica Avenue;#331;1 (11) 500 555-0113

To begin with let’s create a table in SQL Server that will hold the data. Copy and execute the following T-SQL script into SQL Server to create your table:

/* SQL SERVER SCRIPT */

CREATE TABLE [dbo].[Customers]
(
 [CustomerKey] [int] NOT NULL,
 [FullName] [nvarchar](101) NULL,
 [BirthDate] [datetime] NULL,
 [MaritalStatus] [nchar](1) NULL,
 [Gender] [nvarchar](1) NULL,
 [YearlyIncome] [money] NULL,
 [AddressLine1] [nvarchar](120) NULL,
 [AddressLine2] [nvarchar](120) NULL,
 [Phone] [nvarchar](20) NULL
)

Before moving on, to explain the code I have to work backwards and explain the final method that bulk loads data. SqlBulkCopy has a method called WriteToServer. One of the overloads of this method takes a DataTable as the parameter. Because a DataTable contains rows and columns, this seemed like a logical choice for the task I was facing.

DataTable dt = new DataTable();
 string line = null;
 int i = 0;
#region Get data from text file, and insert it into data table
 using (System.IO.StreamReader sr = System.IO.File.OpenText(@"C:\Customers.txt"))
 {
 while ((line = sr.ReadLine()) != null)
 {
 string[] data = line.Split(';');
 if (data.Length > 0)
 {
 if (i == 0)
 {
 foreach (var item in data)
 {
 dt.Columns.Add(new DataColumn());
 }
 i++;
 }
 DataRow row = dt.NewRow();
 row.ItemArray = data;
 dt.Rows.Add(row);
 }
 }
 }
 #endregion

In the code above, I created a DataTable that will store all the information from the text file. The text file resides in the C:\Customers.txt. I am using a StreamReader object to open the file and read each line in the file. Each line is then split up into a string array. That string array will be assigned to each DataRow as the ItemArray value. This sets the values for the row through the array.

When the file has been read, the next thing to do is use the SqlBulkCopy class to insert the data into SQL Server. The following code demonstrates how to do this:

using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection("Server=(local); Database=TestDB; UID=sa; PWD=sa"))
 {
 cn.Open();
 using (System.Data.SqlClient.SqlBulkCopy copy = new System.Data.SqlClient.SqlBulkCopy(cn))
 {
 copy.BulkCopyTimeout = 0; // unlimited time-out
 copy.ColumnMappings.Add(0, 0);
 copy.ColumnMappings.Add(1, 1);
 copy.ColumnMappings.Add(2, 2);
 copy.ColumnMappings.Add(3, 3);
 copy.ColumnMappings.Add(4, 4);
 copy.ColumnMappings.Add(5, 5);
 copy.ColumnMappings.Add(6, 6);
 copy.ColumnMappings.Add(7, 7);
 copy.ColumnMappings.Add(8, 8);
 copy.DestinationTableName = "Customers";
 copy.WriteToServer(dt);
 }
 }

SqlBulkCopy uses ADO.NET to connect to a database to bulk load the data. I have created an SqlConnection object, and that object reference is used to create the SqlBulkCopy object. The DestinationTableName property references a table in the database where the data is to be loaded. A handy feature of SqlBulkCopy is the SqlBulkCopyColumnMappingCollection. Column mappings define the relationships between columns in the data source and columns in the destination. This is handy if the data source file has columns that don’t need to be inserted into the database. Column mappings can be set by an index, such as the example above, or they can be set by the name of the column. Using the index is handy when you’re working with files that contain no column names. Finally the data is sent to the database by running the WriteToServer method.