Thursday, September 27, 2012
sqlbulkcopy with transaction
string connstring = _hsconnstring.Substring(1, (_myconnstring.Length - 1));
using (SqlConnection destinationConnection = new SqlConnection((connstring)))
{
destinationConnection.Open();
using (SqlTransaction transaction = destinationConnection.BeginTransaction())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection,SqlBulkCopyOptions.Default,transaction))
{
try
{
bulkCopy.DestinationTableName = "myfirsttable";
bulkCopy.WriteToServer(outTable1);
bulkCopy.DestinationTableName = "mysecondtable";
bulkCopy.WriteToServer(outTable2);
bulkCopy.DestinationTableName = "mythirdtable";
bulkCopy.WriteToServer(outTable3);
bulkCopy.DestinationTableName = "myfouthtable";
bulkCopy.WriteToServer(outTable4);
bulkCopy.DestinationTableName = "myfifthtable";
bulkCopy.WriteToServer(outTable5);
bulkCopy.Close();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
Utilities.HandleError("Error at data bulk insert: ", ex);
throw ex;
}
}
}
}
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment