Bulk Insert In SQL Server From C#

In this blog, you will learn about bulk Insert in SQL Server from C#.

Introduction

Inserting multiple records in a database is the most common and important task in almost all application. There are inbuilt classes in .NET which support bulk insert which helps to insert multiple records in Database. Recently, I worked on the Bulk insert task and below are the steps and code which help you to achieve insertion of multiple records in Database.

First of all, we need to create DataTable which has identical column names and in the same sequence.

DataTable tbl = new DataTable();
tbl.Columns.Add(new DataColumn("ID", typeof(Int32)));
tbl.Columns.Add(new DataColumn("isDeleted", typeof(bool)));
tbl.Columns.Add(new DataColumn("Manual", typeof(string)));
tbl.Columns.Add(new DataColumn("source", typeof(string)));

Note that we need the same Database Table and in the same sequence which is in datatable.

After that, fill the datatable with data. Here, I am inserting dummy data of 10000 records.

for(int i=0; i<10000; i++)
{
DataRow dr = tbl.NewRow();
dr["ID"] = i;
dr["isDeleted"] = DBNull.Value;
dr["Manual"] = DBNull.Value;
dr["source"] = "Test";

tbl.Rows.Add(dr);
}

Now, you are ready with the data of 10000 records which we need to insert. For that, follow the below steps.

string connection = "Data Source=192.168.1.1;Initial Catalog=XXXXXXX;User Id = abc123; Password = xxxxxxxx";
SqlConnection con = new SqlConnection(connection);
//create object of SqlBulkCopy which help to insert
SqlBulkCopy objbulk = new SqlBulkCopy(con);
//assign Destination table name
objbulk.DestinationTableName = "tblTest";

Now, the most important task is of MAPPING the columns of Datatable to Database Table. We need to MAP each column as below,

objbulk.ColumnMappings.Add("ID", "ID");
objbulk.ColumnMappings.Add("isDeleted", "isDeleted");
objbulk.ColumnMappings.Add("Manual", "Manual");
objbulk.ColumnMappings.Add("source", "source");
con.Open();
//insert bulk Records into DataBase.
objbulk.WriteToServer(tbl);
con.Close();

Common Errors

1. "The given ColumnMapping does not match up with any column in the source or destination. "
   Solution
   Check the database table names case sensitive. Also, check the sequence of the column.
2. There is another common and confusing error "Error-the given value of type String from the data source cannot be converted to type nvarchar of the specified target column."
   Solution
   Don't worry, please check the Length of the Column, if the length of data is bigger than column length, it will give this conversion error. increase the length of the column and you will be good.

Happy Coding !!!