How to Perform Bulk INSERT using SqlBulkCopy in C#
Posted by Superadmin on November 15 2023 11:20:05

How to Perform Bulk INSERT using SqlBulkCopy in C#

In this article, I am going to discuss How to Perform Bulk INSERT using SqlBulkCopy Class in C# and ADO.NET with Examples. Please read our previous article where we discussed How to Perform Bulk Insert and Update in C# and ADO.NET using SQL Server Stored Procedure with Example. SqlBulkCopy class as the name suggests does bulk insert from one source to another. At the end of this article, you will understand how to perform bulk INSERT using SqlBulkCopy class.

 

What is SqlBulkCopy Class in C#?

The SqlBulkCopy class in C# can be used to write data only to SQL Server tables. So, this SqlBulkCopy allows us to efficiently bulk load a SQL Server table with data from another source i.e. from a data table. So, if you have stored your data in a DataTable, then you can insert those data from the data table to the destination database table on the server with the SqlBulkCopy object.

 

Note: The point that you need to remember is using SqlBulkCopy, you can only perform the Bulk INSERT, you cannot perform the Bulk Update.

 

Example to Understand How to Perform Bulk INSERT using SqlBulkCopy in C#:

Let us understand How to Perform Bulk INSERT using SqlBulkCopy in C# and ADO.NET. We are going to use the following table to understand this concept.

Example to Understand How to Perform Bulk INSERT using SqlBulkCopy in C#

 

Please use the below SQL Script to Create the EmployeeDB database and Employee table and Populate the Employee table with the required sample data.

  

CREATE DATABASE EmployeeDB;
 GO 
 
 USE EmployeeDB;
 GO 
 
 CREATE TABLE Employee(
Id INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(50),
Mobile VARCHAR(50),
)
 GO 
 
 INSERT INTO Employee VALUES (100, 'Anurag','Anurag@dotnettutorial.net','1234567890')
 INSERT INTO Employee VALUES (101, 'Priyanka','Priyanka@dotnettutorial.net','2233445566')
 INSERT INTO Employee VALUES (102, 'Preety','Preety@dotnettutorial.net','6655443322')
 INSERT INTO Employee VALUES (103, 'Sambit','Sambit@dotnettutorial.net','9876543210')
 GO

As we are going to perform BULK Insert using SqlBulkCopy class, so we do not need any stored procedure or any SQL Command. Let us proceed and see how we can bulk insert a data table data to the Employee database table using SqlBulkCopy class.

ADO.NET C# Code to Perform Bulk Insert a Data table data to a database Table:

The following example code is self-explained, so please go through the comment lines.

  

using System; 
 using System.Data; 
 using System.Data.SqlClient; 
 namespace BulkInsertUsingSqlBulkCopy
 { 
class Program
{ 
static void Main(string[] args) 
{ 
try 
{ 
//Storing the connection string in a variable 
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
 
//Creating Data Table 
DataTable EmployeeDataTable = new DataTable("Employees");
 
//Add Columns to the Data Table as per the columns defined in the Table Type Parameter 
DataColumn Id = new DataColumn("Id");
EmployeeDataTable.Columns.Add(Id);
 
DataColumn Name = new DataColumn("Name");
EmployeeDataTable.Columns.Add(Name);
 
DataColumn Email = new DataColumn("Email");
EmployeeDataTable.Columns.Add(Email);
 
DataColumn Mobile = new DataColumn("Mobile");
EmployeeDataTable.Columns.Add(Mobile);
 
//Adding Multiple Rows into the DataTable 
//You cannot Perform the Update Operation using SqlBulkCopy 
//EmployeeDataTable.Rows.Add(101, "ABC", "ABC@dotnettutorials.net", "12345"); 
//EmployeeDataTable.Rows.Add(102, "PQR", "PQR@dotnettutorials.net", "11223"); 
//EmployeeDataTable.Rows.Add(103, "XYZ", "XYZ@dotnettutorials.net", "23432"); 
 
//Follwoing Rows are going to be Inserted 
EmployeeDataTable.Rows.Add(105, "Santosh", "Santosh@dotnettutorials.net", "12345");
EmployeeDataTable.Rows.Add(106, "Saroj", "Saroj@dotnettutorials.net", "23456");
EmployeeDataTable.Rows.Add(107, "Sameer", "Sameer@dotnettutorials.net", "34567");
//Creating the connection object 
using (SqlConnection connection = new SqlConnection(ConnectionString)) 
{ 
//Lets you efficiently bulk load a SQL Server table with data from another source. 
//Create an instance of SqlBulkCopy class 
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection)) 
{ 
//Set the database table name where we need to bulk insert the data 
sqlBulkCopy.DestinationTableName = "dbo.Employee";
 
//Map the Data Table column name with the database table column name 
sqlBulkCopy.ColumnMappings.Add("Id", "Id");
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
sqlBulkCopy.ColumnMappings.Add("Email", " Email");
sqlBulkCopy.ColumnMappings.Add("Mobile", "Mobile");
//Open the connection 
connection.Open();
 
//WriteToServer method will Insert all the Rows from the Datatable to the  
//Database Table specified by the DestinationTableName property of the SqlBulkCopy object 
sqlBulkCopy.WriteToServer(EmployeeDataTable);
} 
} 
 
Console.WriteLine("BULK INSERT Successful using SqlBulkCopy");
} 
catch (Exception ex) 
{ 
Console.WriteLine($"Exception Occurred: {ex.Message}");
} 
Console.ReadKey();
} 
} 
 }

Output: BULK INSERT Successful using SqlBulkCopy

   

As you are not getting any exceptions and as you are getting the above message means the BULK INSERT operation is successful. You can also verify the same in the database. Now, you can see the Employee database table having newly inserted rows.

ADO.NET C# Code to Perform Bulk Insert a Data table data to a database Table

 

Column Mapping is Optional in SqlBulkCopy Class in C#:

In SqlBulkCopy, Column Mapping is Optional when both the data table and database table have the same column names and number of columns. As you can see in our example, both the data table (i.e. EmployeeDataTable) and database table (i.e. Employee) have the same column names as well as the same number of columns. So, in this case, column mapping is optional. Let us remove the column mapping and run the application. In the below example, in order not to get any SQL Exception, I have updated the Id column values in the data table.

using System; 
 using System.Data; 
 using System.Data.SqlClient; 
 namespace BulkInsertUsingSqlBulkCopy
 { 
class Program
{ 
static void Main(string[] args) 
{ 
try 
{ 
//Storing the connection string in a variable 
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
 
//Creating Data Table 
DataTable EmployeeDataTable = new DataTable("Employees");
 
//Add Columns to the Data Table as per the columns defined in the Table Type Parameter 
DataColumn Id = new DataColumn("Id");
EmployeeDataTable.Columns.Add(Id);
 
DataColumn Name = new DataColumn("Name");
EmployeeDataTable.Columns.Add(Name);
 
DataColumn Email = new DataColumn("Email");
EmployeeDataTable.Columns.Add(Email);
DataColumn Mobile = new DataColumn("Mobile");
EmployeeDataTable.Columns.Add(Mobile);
//Adding Multiple Rows into the DataTable
//Following Rows are going to be Inserted
EmployeeDataTable.Rows.Add(108, "Santosh", "Santosh@dotnettutorials.net", "12345");
EmployeeDataTable.Rows.Add(109, "Saroj", "Saroj@dotnettutorials.net", "23456");
EmployeeDataTable.Rows.Add(110, "Sameer", "Sameer@dotnettutorials.net", "34567");
//Creating the connection object
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
//Lets you efficiently bulk load a SQL Server table with data from another source.
//Create an instance of SqlBulkCopy class
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection))
{
//Set the database table name where we need to bulk insert the data
sqlBulkCopy.DestinationTableName = "dbo.Employee";
//Column Mapping is Optional when both the data table and database table
//having same column names and number of columns
//sqlBulkCopy.ColumnMappings.Add("Id", "Id");
//sqlBulkCopy.ColumnMappings.Add("Name", "Name");
//sqlBulkCopy.ColumnMappings.Add("Email", "Email");
//sqlBulkCopy.ColumnMappings.Add("Mobile", "Mobile");
//Open the connection
connection.Open();
//WriteToServer method will Insert all the Rows from the Datatable to the
//Database Table specified by the DestinationTableName property of the SqlBulkCopy object
sqlBulkCopy.WriteToServer(EmployeeDataTable);
}
}
Console.WriteLine("BULK INSERT Successful using SqlBulkCopy");
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
}
}
Output: BULK INSERT Successful using SqlBulkCopy

As you can see, we are not getting any exceptions. Now, you can verify the Employee database table and the table shows newly inserted rows as shown in the below image.

How to Perform Bulk INSERT using SqlBulkCopy Object in C# and ADO.NET with Examples

Column Mapping is optional when both the source and destination table have the same column names as well as the same number of columns. The following example will give you Runtime Error as the number of columns is different and we are not using column mapping. In the below example, we have added a new column called IsActive in the data table.

using System;
using System.Data;
using System.Data.SqlClient;
namespace BulkInsertUsingSqlBulkCopy
{
class Program
{
static void Main(string[] args)
{
try
{
//Storing the connection string in a variable
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
//Creating Data Table
DataTable EmployeeDataTable = new DataTable("Employees");
//Add Columns to the Data Table as per the columns defined in the Table Type Parameter
DataColumn Id = new DataColumn("Id");
EmployeeDataTable.Columns.Add(Id);
DataColumn Name = new DataColumn("Name");
EmployeeDataTable.Columns.Add(Name);
DataColumn Email = new DataColumn("Email");
EmployeeDataTable.Columns.Add(Email);
DataColumn Mobile = new DataColumn("Mobile");
EmployeeDataTable.Columns.Add(Mobile);
DataColumn IsActive = new DataColumn("IsActive");
EmployeeDataTable.Columns.Add(IsActive);
//Adding Multiple Rows into the DataTable
//Following Rows are going to be Inserted
EmployeeDataTable.Rows.Add(111, "Santosh", "Santosh@dotnettutorials.net", "12345", true);
EmployeeDataTable.Rows.Add(112, "Saroj", "Saroj@dotnettutorials.net", "23456", false);
//Creating the connection object
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
//Lets you efficiently bulk load a SQL Server table with data from another source.
//Create an instance of SqlBulkCopy class
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection))
{
//Set the database table name where we need to bulk insert the data
sqlBulkCopy.DestinationTableName = "dbo.Employee";
//Open the connection
connection.Open();
//WriteToServer method will Insert all the Rows from the Datatable to the
//Database Table specified by the DestinationTableName property of the SqlBulkCopy object
sqlBulkCopy.WriteToServer(EmployeeDataTable);
}
}
Console.WriteLine("BULK INSERT Successful using SqlBulkCopy");
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
}
}

Now, run the above code and you will get the following error message.

Exception Occurred: The given ColumnMapping does not match up with any column in the source or destination.

In this case, for the IsActive column of the data table, there is no matched column found in the database table and hence this error is throwing and this makes sense. So, in situations like this, we need to use column mapping as shown in the below code.

using System;
using System.Data;
using System.Data.SqlClient;
namespace BulkInsertUsingSqlBulkCopy
{
class Program
{
static void Main(string[] args)
{
try
{
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
DataTable EmployeeDataTable = new DataTable("Employees");
DataColumn Id = new DataColumn("EmployeeId");
EmployeeDataTable.Columns.Add(Id);
DataColumn Name = new DataColumn("Name");
EmployeeDataTable.Columns.Add(Name);
DataColumn Email = new DataColumn("Email");
EmployeeDataTable.Columns.Add(Email);
DataColumn Mobile = new DataColumn("EmployeeMobile");
EmployeeDataTable.Columns.Add(Mobile);
DataColumn IsActive = new DataColumn("IsActive");
EmployeeDataTable.Columns.Add(IsActive);
EmployeeDataTable.Rows.Add(111, "Santosh", "Santosh@dotnettutorials.net", "12345", true);
EmployeeDataTable.Rows.Add(112, "Saroj", "Saroj@dotnettutorials.net", "23456", false);
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection))
{
sqlBulkCopy.DestinationTableName = "dbo.Employee";
sqlBulkCopy.ColumnMappings.Add("EmployeeId", "Id");
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
sqlBulkCopy.ColumnMappings.Add("Email", "Email");
sqlBulkCopy.ColumnMappings.Add("EmployeeMobile", "Mobile");
connection.Open();
sqlBulkCopy.WriteToServer(EmployeeDataTable);
}
}
Console.WriteLine("BULK INSERT Successful using SqlBulkCopy");
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
}
}

In the next article, I am going to discuss How to Perform Batch Operations in C# Using ADO.NET DataAdapters with Examples. Here, in this article, I try to explain How to perform Bulk INSERT using SqlBulkCopy Class in C# ADO.NET with Examples. I hope you enjoy this Bulk INSERT using SqlBulkCopy in C# article.