Using ADO.NET SqlBulkCopy with SqlTransaction - Simple Tutorial for demonstrating how to use Asp.Net SqlBulkCopy with SqlTransaction with Free Source Code

SqlBulkCopy helps you to efficiently copy data to the SQL Server database table from any other data sources. While copying large data using SqlBulkCopy, if any unavoidable error or exception occurs, it is not recommended to leave the operation in an incomplete or partially complete state. It is better to reset the whole operation to its initial state and inform the user regarding the error, otherwise the exception. So this article takes the pride to light the concept of using SqlBulkCopy along with SqlTransaction with some simple example.

SqlBulkCopy Class

The SqlBulkCopy instance can be created either by specifying connection string or a SqlConnection object. The important properties are, DestinationTableName is the name of the destination table in the SQL Server Database, and BatchSize property is used to specify the number of records to be transferred in each Batch. WriteToServer method is used to copy all the records from the source to the destination table.

SqlTransaction Class

SqlTransaction object can be created by calling the BeginTransaction method on the current SqlConnection object. This SqlTransaction object has to be used to all the subsequent operations that is need to be monitor. If the execution of statement got no error or exception, then the transaction will be committed to save the changes permanently in the database. Otherwise, the transaction is rolled back on any error. Try/Catch error handling is used to handle any errors when attempting to Commit or Rollback the transaction.

Using SqlBulkCopy with UseInternalTransaction

While creating an instance to SqlBulCopy, we can specify the transaction to be used is internal transaction. For establishing the connection with the server for this type of transaction, a connection string is enough. The syntax is given below

SqlBulkCopy sqlbulk = new SqlBulkCopy(“YourConnectionString”, SqlBulkCopyOptions.UseInternalTransaction);

UseInternalTransaction is an SqlBulkCopyOption, used to specify the type of transaction to be used while copying data. This option is quite simple and can only used for simple transfer which does not have any other data manipulations before or after the bulk copy. You can also specify the BatchSize property to transfer records batch by batch. Let us see some sample source code to that uses UseInternalTransaction option.

Example for UseInternalTransaction

We create a DataTable, with some DataRows for copy the data in it to a database table. Then an SqlBulkCopy object is created using a connection string and UseInternalTransaction as its SqlBulkCopyOptions. DestinationTableName and BatchSize are specified and then WriteToServer method is called to copy data in the DataTable to the Sql Server table “BulkCopyTable”. There are no Commit and Rollback methods called, as the SqlBulkCopy controls the transaction completely and internally. Then the BatchSize, is specified as “2”, so the SqlBulkCopy will be copy the data batch by batch, that is two records per batch, and if there is any exception incurred, the currently copying batch incurring exception will be rollback and the other batches which is copied previously will not be rollback. They will remain in the database.

Using SqlBulkCopy with SqlTransaction

Here to create the instance of SqlBulkCopy, we need a real time SqlConnection object and an instance of SqlTransaction has to be created with the connection object’s BeginTransaction method. The syntax is given below

SqlConnection conn = new SqlConnection(“YouConnectionString”);
conn.Open();

SqlTransaction transaction = conn.BeginTransaction();

SqlBulkCopy sqlbulk = new SqlBulkCopy(conn, SqlBulkCopyOptions. KeepIdentity, transaction);

So along with the connection and transaction object, we have specified the SqlBulkCopyOptions as KeepIdentity, this will preserve the source identity values. This SqlBulkCopy requires Try/Catch error handling to identify the exception and to Commit and Rollback transactions. Along with SqlBulkCopy, you can perform other data manipulations by using this same SqlTransaction object. Let us have a look at the sample source code below

Example for using SqlBulkCopy with SqlTransaction

DataTable dt = new DataTable();
dt.Columns.Add("TestId");
dt.Columns.Add("TestDesc");

DataRow drNew = dt.NewRow();
drNew[0] = "1";
drNew[1] = "Test Row 1";
dt.Rows.Add(drNew);

DataRow drNew1 = dt.NewRow();
drNew1[0] = "2";
drNew1[1] = "Test Row 2";
dt.Rows.Add(drNew1);

DataRow drNew2 = dt.NewRow();
drNew2[0] = "3";
drNew2[1] = "Test Row 3";
dt.Rows.Add(drNew2);

DataRow drNew3 = dt.NewRow();
drNew3[0] = "4";
drNew3[1] = "Test Row 4";
dt.Rows.Add(drNew3);

DataRow drNew4 = dt.NewRow();
drNew4[0] = "5";
drNew4[1] = "Test Row 5";
dt.Rows.Add(drNew4);

DataRow drNew5 = dt.NewRow();
drNew5[0] = "6";
drNew5[1] = "Test Row 6";
dt.Rows.Add(drNew5);

SqlConnection conn = new SqlConnection("YourConnectionString");
conn.Open();

SqlTransaction transaction = conn.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand("INSERT INTO BulkCopyTableDesc (BulkCopyId,BulkCopyDesc,BulkCopyFlag) Select Isnull(Max(BulkCopyId)+1,1), 'Test Bulk Copy '+Convert(VarChar(10), ceiling(rand()*10000)), 'Y' from BulkCopyTableDesc", conn);
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();

SqlBulkCopy sqlbulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, transaction);
sqlbulk.DestinationTableName = "BulkCopyTable";
sqlbulk.BatchSize = 2;
sqlbulk.WriteToServer(dt);
transaction.Commit();
lbStatus.Text = "Success";
}
catch (Exception exp)
{
lbStatus.Text = exp.Message;
transaction.Rollback();
}
finally
{
transaction.Dispose();
conn.Close();
}

In the above code, we create a DataTable with DataRows for copying purpose. Then we create SqlConnection object and SqlTransaction object. Then we are doing a data manipulation by using the same SqlConnection and SqlTransaction object. For demonstration purpose, we are inserting one record to BulkCopyTableDesc table. Then we are doing the SqlBulkCopy operation. The whole block of code is capsuled inside the Try/Catch block. So if there is no error or exceptions, then it will be committed by calling the SqlTransaction’s Commit method. If any exception occurs, then SqlTransaction Rollback method is called. Here it will rollback to the initial state, regardless of the BatchSize, all data manipulation inside this transaction will be rollbacked.

Using SqlTransaction for Complex Data Manipulations

The SqlTransaction can be very helpful to maintain consistency between the data. It is always recommeded to use SqlTransaction in large data transaction where multiple SqlCommand and SqlDataAdapter are involved, because if any SQL statement raises an error or exception, then the changes in the database must not let in halfway, rather it must be complete or it must reset to its initial state. This can be only achieved by using SqlTransaction. Let us see an example to demonstrate this.

DataTable dt = new DataTable();
dt.Columns.Add("TestId");
dt.Columns.Add("TestDesc");

DataRow drNew = dt.NewRow();
drNew[0] = "1";
drNew[1] = "Test Row 1";
dt.Rows.Add(drNew);

DataRow drNew1 = dt.NewRow();
drNew1[0] = "2";
drNew1[1] = "Test Row 2";
dt.Rows.Add(drNew1);

DataRow drNew2 = dt.NewRow();
drNew2[0] = "3";
drNew2[1] = "Test Row 3";
dt.Rows.Add(drNew2);

DataRow drNew3 = dt.NewRow();
drNew3[0] = "4";
drNew3[1] = "Test Row 4";
dt.Rows.Add(drNew3);

DataRow drNew4 = dt.NewRow();
drNew4[0] = "5";
drNew4[1] = "Test Row 5";
dt.Rows.Add(drNew4);

DataRow drNew5 = dt.NewRow();
drNew5[0] = "6";
drNew5[1] = "Test Row 6";
dt.Rows.Add(drNew5);

SqlConnection conn = new SqlConnection("YourConnectionString");
conn.Open();

SqlTransaction transaction = conn.BeginTransaction();

try {
SqlCommand cmd = new SqlCommand("INSERT INTO BulkCopyTableDesc (BulkCopyId,BulkCopyDesc,BulkCopyFlag) Select Isnull(Max(BulkCopyId)+1,1), 'Test Bulk Copy '+Convert(VarChar(10), ceiling(rand()*10000)), 'Y' from BulkCopyTableDesc", conn);
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();

SqlCommand cmdIns = new SqlCommand("Insert into BulkCopyTable (TestId, TestDesc) values (@TestId, @TestDesc)", conn);
cmdIns.Transaction = transaction;
cmdIns.Parameters.Add("@TestId", SqlDbType.Int);
cmdIns.Parameters.Add("@TestDesc", SqlDbType.VarChar, 50);
cmdIns.Prepare();

foreach (DataRow dr in dt.Rows)
{
SqlDataAdapter da = new SqlDataAdapter("Select Top 1 * from BulkCopyTableDesc Order By BulkCopyId Desc ", conn);
da.SelectCommand.Transaction = transaction;
DataTable dtSelect = new DataTable(); da.Fill(dtSelect);

cmdIns.Parameters["@TestId"].Value = dr["TestId"];
cmdIns.Parameters["@TestDesc"].Value = dr["TestDesc"].ToString() + " "
+ dtSelect.Rows[0]["BulkCopyDesc"].ToString();
cmdIns.ExecuteNonQuery();
}

transaction.Commit();
lbStatus.Text = "Success";
}

catch (Exception exp)
{
lbStatus.Text = exp.Message;
transaction.Rollback();
}
finally
{
transaction.Dispose();
conn.Close();
}

Look at the above code closely, you can identify, a single SqlTransaction instance is created and used throughout the whole SqlCommands and SqlDataAdapter data manipulations. There are two tables involved, the first SqlCommand inserts a record into BulkCopyTableDesc Table, then we create another SqlCommand to insert records to another table BulkCopyTable through looping the DataTable. Inside the foreach loop, we use SqlDataAdapter to fetch a record from the BulkCopyTableDesc table. So, all these SqlCommands and SqlDataAdapters objects are created with a single real SqlConnection object and using same SqlTransaction object as its Transaction. Sometimes, when you use SqlTransaction with looping, you may get an error as below

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

This error is due to the creation of SqlConnection object inside the for/while loop or creating SqlCommand objects inside a loop without disposing the existing SqlCommand objects. While using SqlTransaction, this error may occur due to some SqlCommand or SqlDataAdapter using other SqlConnection object or its Transaction property is not set to use the same SqlTransaction object.

You need to Login or Register to download the sample code.