While working in a Data driven application, we generally come across situations where we have to perform bulk data insert operations between Data sources.
Following are some of the common situations
Updating a database table,
Copy data from source files, store them in a data table, and run insert queries to database table.
Read data from a Database table, Populate them in a Data table, Edit data, and then insert them to anther table.
Like above there are lots of cases in which we have to do bulk data insert operations.
In such cases we usually copy records row by row by executing an INSERT statement using a foreach loop. For this we write lots of code and it takes good amount of time. We hit the database for each row. So, this approach has poor performance effect because we have to call database for several times.
To avoid such situations Microsoft .Net 2.0 introduces a class in the System.Data.SqlClient namespace called SqlBulkCopy. This class allows you to perform copy operation for a large amount of data between any data source and a destination SQL table. This approach reduces the number of database accesses to improve performance and speed.
DataSource can be flat file, XML file, SQL Database etc.
To store data from source you can use DataTable or DataReader.
/-- Declare a data table and add columns
DataTabledtSourceDataTable = new DataTable("Column");
//-- Populate Datable from DataSource
//-- Write your own code to populate source DataTable with Return type DataTable.
dtSourceDataTable = PopulateDataTable();
//-- Define SQL connection string from Config file
String sqlConnString = ConfigurationManager.AppSettings["SQLConnection"];
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnString, SqlBulkCopyOptions.KeepIdentity))
bulkCopy.BatchSize = 10;
bulkCopy.DestinationTableName = "dbo.DestinationTableName";
catch (Exception ex)
In the above code snippet the method WriteToServer, Copies all rows from data source to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.
Suppose the source datatable contain 20,000 rows, the SqlBulkCopy class will insert all the 20,000 rows at a time with very less time.
This is a very nice way to populate a SQL Server table from .NET applications.