Saturday, January 24, 2015

SQLBulkCopy using C#.NET

Lets create a table to hold the data:

CREATE TABLE [dbo].[tbl_BulkCopy] 
(
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [Column1] [varchar](100) NOT NULL,
      [Column2] [varchar](100) NOT NULL
);

Now lets work on the C#.Net code behind file. 

We need to add a reference to the following namespace:

using System.Data.SqlClient;
using System.Configuration;

System.Configuration namespace reference is required if you are trying to read config key value.

Creating a sample method loadData() to implement the SqlBulkCopy functionality.

void loadData()
{
  string strConnString =   ConfigurationManager.ConnectionStrings["DBConn"].ToString();

  DataTable oDataTable = new DataTable();

 oDataTable.Columns.Add(new DataColumn("Column1", typeof(string));
 oDataTable.Columns.Add(new DataColumn("Column2", typeof(string));

 for(int i=0;i<50000;i++)
 {
   DataRow oDataRow = oDataTable.NewRow();
   oDataRow["Column1"] = "column1_"+i.ToString();
   oDataRow["Column2"] = "column2_"+i.ToString();

   oDataTable.Rows.Add(oDataRow);
 }

  using(SqlBulkCopy oSqlBulkCopy=new SqlBulkCopy(strConnString))
  {
    oSqlBulkCopy.BatchSize=10000;
    oSqlBulkCopy.BulkCopyTimeout = 10000;
    oSqlBulkCopy.ColumnMappings.Add("column1","column1");
    oSqlBulkCopy.ColumnMappings.Add("column2","column2");
    oSqlBulkCopy.DestinationTableName="tbl_BulkCopy";
    oSqlBulkCopy.WriteToServer(oDataTable);
  }
}


loadData() method can be called on any Button click method to save the data.