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();


  using(SqlBulkCopy oSqlBulkCopy=new SqlBulkCopy(strConnString))
    oSqlBulkCopy.BulkCopyTimeout = 10000;

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

1 comment :

  1. If you are mapping the same names of columns, you can use this instead:

    foreach (DataColumn col in oDataTable.Columns)
    copy.ColumnMappings.Add(col.ColumnName, col.ColumnName);