Hello! In our previous post Inserting bulk records using JavaScript, asp.net , we have learned about keeping all the data at client side and then save all the data in single database call for considering performance. Today we will learn that how can we achieve the same using SQLBulkCopy class in C#. Using this class, we can copy all the rows which are available in datatable variable taken on the page, to table in database. For this, we do not need any store procedure or sql command. Just establishing a sql connection and using SQLBulkCopy, we can insert multiple rows at once only.
Below is a code for the same:

DataSet DS = new DataSet();
DataTable dtPerson;
DS.ReadXml(new XmlTextReader(new StringReader(tempdata.Text)));
dtPerson = DS.Tables[0];

strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection SConn = new SqlConnection(strConnection))
{
    using (SqlBulkCopy SBCopy = new SqlBulkCopy(SConn))
    {
        SBCopy.DestinationTableName = "dbo.Student"; //Table name in Database

        SBCopy.ColumnMappings.Add("ID", "ID"); //Map columns of datatable with columns of table in database: This is optional
        SBCopy.ColumnMappings.Add("Name", "Name");
        SBCopy.ColumnMappings.Add("Age", "Age");

        SConn.Open();
        SBCopy.WriteToServer(dtPerson); //Copies rows from datatable to table in database
        SConn.Close();
    }
}

We will have DataTable with multiple rows. After initializing object of SQLBulkCopy class, we need to give DestinationTableName, a table in a database in which we want to copy all the data. We can map columns also- A column of datatable with a column of table in a database. As example, I want to add all data of ID column of datatable into ID column of table in database, so I will map these both all column. However, it is an optional.

Finally, after opening a connection, we will copy data by calling method named “WriteToServer” which takes name of datatable as input. This method will copy all data into database table.

One major advantage we can consider is, running time. SQLBulkCopy inserts data in very less running time than time taken by insert script. As an example, It can insert 10 records in 4 milliseconds(ms) where as insert script would take 5 ms. But, we must need to check that data to be inserted should be in a perfect form and in the same structure of the table in database. It must satisfy all the constraints applied on the table. For an example, if on column named ID, primary key is applied then we must not pass blank values in that column. Otherwise, it will throw an exception. Although we can check for the options also by using SqlBulkCopyOptions enum, like- CheckConstraints, FireTriggers, KeepNulls, TableLock, UseInternalTransaction.

In short, It is the easiest and efficient way to copy data in database table in bulk at once. I have attached demo solution with this post. Hope it will be helpful for you. Thank you.

Demo Insert With SqlBulkCopy