Bulk Copy with Transaction Control in Asp.net c#

 Sample Code for Bulk Copy with Transaction Control in Asp.net c#

sqlConnection conn = db.CreateConnection();  // Create a new connection

// For Command cmd

db.AddInParameter(cmd, "@CFirstName", DbType.String, dt.Rows[i][j].ToString()); j++;

// adding new parameter Blah Blah Blah

db.AddInParameter(cmd, "@CLastName", DbType.String, dt.Rows[i][j].ToString()); j++;

// adding new parameter Blah Blah Blah

db.AddInParameter(cmd, "@CTitle", DbType.String, dt.Rows[i][j].ToString()); j++;

//adding new parameter Blah Blah Blah

SQLTransaction trx = conn.BeginTransaction(); // Begin Transcation

cmd.Transaction = trx; // Adding transcation to command

try

{

db.ExecuteNonQuery(cmd, trx); // passing transcation to sql

datatable dtdata = dataList();

System.Data.SqlClient.SqlBulkCopy oSqlBulkCopy = new System.Data.SqlClient.SqlBulkCopy((SqlConnection)trx.Connection, SqlBulkCopyOptions.TableLock, (SqlTransaction)trx);

oSqlBulkCopy.DestinationTableName = "EscalationIssues";

oSqlBulkCopy.WriteToServer(dtdata);

trx.Commit();// Commit transcation

conn.Close();

oSqlBulkCopy.Close();

}

Catch(sqlexception sqlex)

{

Trx.rollback(); // Roll back Transcation

}

Public datatable dataList()

{

DataTable dtdata = new DataTable();

dtdata.Columns.Add("EscalationIssueId", typeof(Guid));  //Primary key

dtdata.Columns.Add("EscalationId", typeof(Int32));

dtdata.Columns.Add("IssueId", typeof(Int32));

dtdata.Columns.Add("CreatedOn", typeof(DateTime));

dtdata.Columns.Add("CreatedBy", typeof(string));

for (int k = 0; k < 10; k++)

{

DataRow dr = dtdata.NewRow();

dr["EscalationIssueId"] = new Guid();

dr["EscalationId"] = Convert.ToInt32(escaltionID);

dr["IssueId"] = Convert.ToInt32(lsulist[k]);

dr["CreatedOn"] = DateTime.Now;

dr["CreatedBy"] = "Arun Aravind";

dtdata.Rows.Add(dr);

}

}

}

If u had any trouble just ask, Happy to help u :)
Stay Tune... Have a nice day... 'N happy Coding :)