Bulk Insert into SQL Server using SqlBulkCopy

Transferring data from one source to another is common practice in software development. This operation is preformed in many different scenarios which includes migration of the old system to the new system, backing up the data and collecting data from different publishers. ASP.NET 2.0 includes the SqlBulkCopy class that helps to copy the data from different data sources to the SQL SERVER database.

Reliable transaction are maintained by using SqlTransaction object to BulkCopy. In-case of error whole transaction will be role-backed.

Example :

SqlTransaction myTransaction;

DataTable FinalTable = (DataTable)Session[“Data”];

using (SqlConnection cn = Get_Connection())

{

myTransaction = cn.BeginTransaction();

try

{

using (SqlBulkCopy copy = new SqlBulkCopy(cn, SqlBulkCopyOptions.Default, myTransaction))

{

copy.ColumnMappings.Add(0, 1); //(SOURCE TABLE,DESTINATION TABLE)

copy.ColumnMappings.Add(2, 2); // (“Product Name”,”Name”)

copy.ColumnMappings.Add(3, 3);

copy.ColumnMappings.Add(4, 4);

copy.ColumnMappings.Add(5, 5);

copy.ColumnMappings.Add(6, 6);

copy.ColumnMappings.Add(7, 7);

copy.ColumnMappings.Add(8, 8);

copy.ColumnMappings.Add(9, 9);

//copy.BatchSize = 1000;

copy.DestinationTableName = “dbo.Products”;

copy.WriteToServer(FinalTable); // writing Source Data to sqlserver table

myTransaction.Commit();

}

}

catch (Exception ex)

{

myTransaction.Rollback();

}

finally

{

cn.Close();

}

}

(or)

public bool BulkInsert(DataTable dataTable)
{

if (dataTable != null && dataTable.Rows.Count > 0)
{
using (SqlConnection sqlConnection = new SqlConnection(Database.Connection_String))
{
sqlConnection.Open();

SqlBulkCopy objSqlBulkCopy = new SqlBulkCopy(sqlConnection) { DestinationTableName = “DestinationTableName” };

foreach (DataColumn dataColumn in dataTable.Columns)
objSqlBulkCopy.ColumnMappings.Add(dataColumn.ColumnName, dataColumn.ColumnName);

objSqlBulkCopy.WriteToServer(dataTable);
return true;
}
}
return false;
}

Advertisements