SqlBulkCopy allows you to batch insert a lot of records from a datatable to your database without having to insert each row individually.
To get started, make sure you have imported the System.Data.SqlClient namespace.
using System.Data;
using System.Data.SqlClient;
For the sake of the example, I will be creating a datatable with some sample data inside my code. Normally this data will either come from one or more databases or even a webservice. I've added a method to my code which will simulate a datatable being returned from a database. In this scenario I'm also just using a minimal amount of columns and rows for the sake of simplicity.
private DataTable MyDataTable()
{ DataTable dt = new DataTable();
dt.Columns.Add("Id", typeof(int)); dt.Columns.Add("Name", typeof(string)); dt.Columns.Add("Surname", typeof(string)); dt.Columns.Add("DateAdded", typeof(DateTime));
dt.Rows.Add(new object[] { 1, "Stefan", "Pienaar", DateTime.Now }); dt.Rows.Add(new object[] { 1, "Pieter", "Theron", DateTime.Now }); dt.Rows.Add(new object[] { 1, "JC", "Putter", DateTime.Now });
return dt;
}
Now we're ready to batch insert the new records into our database. I've set up a similiar table in my database:
In my test case the names of the columns in the database and the datatable matches but this isn't a requirement as you will see soon enough when we set up our table mappings.
protected void Page_Load(object sender, EventArgs e)
{ if (!IsPostBack)
{ SqlBulkCopy sqlBC = new SqlBulkCopy(
ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);
// The name of my SQL table
sqlBC.DestinationTableName = "TestData";
// Set up the mappings between the columns
sqlBC.ColumnMappings.Add(
new SqlBulkCopyColumnMapping("Id", "Id")); sqlBC.ColumnMappings.Add(
new SqlBulkCopyColumnMapping("Name", "Name")); sqlBC.ColumnMappings.Add(
new SqlBulkCopyColumnMapping("Surname", "Surname")); sqlBC.ColumnMappings.Add(
new SqlBulkCopyColumnMapping("DateAdded", "DateAdded"));
// Bulk copy my datatable to the database
sqlBC.WriteToServer(MyDataTable());
}
}
After I run the page, I can open up my database table and new data has been copied. The bulk copy is much more efficient than inserting rows individually and is thus highly recommended in situations where it makes sense to use it.
