SqlBulkcopy in C#

Background

In a programming world, there are requirements where the developer need to Insert large number of records into a Database. Usually the developer uses SqlCommand class and Insert into query to insert records into database table. The SqlCommand Class inserts one record at a time into database table. So in case of large no of records, this approach will take so much time and resources. in such cases, SqlBulkCopy comes into picture.



What is SqlBulkCopy and How it is used?

SqlCommand Class resides in namespace System.Data.SqlClient.

This class improves the performance of inserting large data into the Table. It has several properties and methods to achieve this function.

Data to be inserted is filled in DataTable or read with IDataReader instance.

Program

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace sqlbulkcopy
{
class Program
{
static DataTable dt = new DataTable();
static void Main(string[] args)
{
dt.Columns.Add("ID");
dt.Columns.Add("CustomerName");
dt.Columns.Add("Customer_Type");
dt.Columns.Add("OrderID");
dt.Columns.Add("Date");
InsertlargeData();
}
internal static void InsertlargeData()
{
string connstring = ConfigurationManager.AppSettings["DBCS"].ToString();
SqlConnection con = new SqlConnection(connstring);
con.Open();
for(int i=1;i<=10000;i++)
{
dt.Rows.Add(i,"Sandy" + i, "prime" + i,i,DateTime.Now.ToString());
}
using (var sqlbul = new SqlBulkCopy(connstring))
{
sqlbul.BatchSize = 1000;
sqlbul.NotifyAfter = 1000;
sqlbul.ColumnMappings.Add("ID", "ID");
sqlbul.ColumnMappings.Add("CustomerName", "CustomerName");
sqlbul.ColumnMappings.Add("Customer_Type", "Customer_Type");
sqlbul.ColumnMappings.Add("OrderID", "OrderID");
sqlbul.ColumnMappings.Add("Date", "Date");
sqlbul.SqlRowsCopied += new SqlRowsCopiedEventHandler(rowcopied);
sqlbul.DestinationTableName = "Customers_2";
sqlbul.WriteToServer(dt);
}
}
private static void rowcopied(object sender,SqlRowsCopiedEventArgs data)
{
Console.WriteLine("Wrote " + data.RowsCopied + " records.");
}
}
}

Output

All the 10000 rows will be inserted into given Table.

Explanation

Here , we created a DataTable to fill with the large number of data.

We added the columns to the DataTable. The name and number of columns are same as that of the Table in SQL Server in which data to be inserted.

Then we established a connection to the SQL server table.

To insert the data , we used the for loop and written the values to insert.

Then using SqlBulkCopy we mapped the columns of the DataTable to Sql Server Table column using the property ColumnMappings.

To know the rows are getting copied , we defined the event and write on the console the number of rows getting copied

Properties

Batch Size

- the number of rows in a batch. Operations are carried in a batch.

Bulk Copy Timeout

- The number of seconds to complete the operation.

Column mappings

- defines the relation between column of the source DataTable and columns of the Destination table.

Destination Table Name

- The name of the table in which Data to be inserted.

Notify after

- defined no of rows before generating notification event.

Methods

There are many methods available. But we will study few important methods.

Write ToServer (Data Table)

- Copied all the rows in the provided DataTable to the Destination table.

Write ToServer Async (Data Table)

- Same as above but the rows are copied asynchronously.