Bulk INSERT using SqlBulkCopy in C#

Back to: ADO.NET Tutorial For Beginners and Professionals

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.

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.

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.

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.