事务

string strconn = 数据库连接字符串;
SqlConnection sqlConn = new SqlConnection(strconn);

SqlCommand l_scsqlCommand = new SqlCommand();
l_scsqlCommand.Connection = sqlConn;
sqlConn.Open();
SqlTransaction l_stsqlTransaction = sqlConn.BeginTransaction();//启动事务
l_scsqlCommand.Transaction = l_stsqlTransaction;
try
{
//删除HistoryMailInfo表中数据
l_scsqlCommand.CommandText = "delete from HistoryMailInfo";
l_scsqlCommand.ExecuteNonQuery();

//插入HistoryMailInfo表中数据
l_scsqlCommand.CommandText = "insert into HistoryMailInfo SELECT * from MailInfo";
l_scsqlCommand.ExecuteNonQuery();

//删除MailInfo表中数据
l_scsqlCommand.CommandText = "delete from MailInfo";
l_scsqlCommand.ExecuteNonQuery();

if (dt != null && dt.Rows.Count != 0)
{
//批量添加内容到MailInfo表
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.FireTriggers, l_stsqlTransaction);
bulkCopy.DestinationTableName = "MailInfo";
bulkCopy.BatchSize = dt.Rows.Count;
bulkCopy.WriteToServer(dt);
}

l_stsqlTransaction.Commit();//提交事务

}
catch
{
l_stsqlTransaction.Rollback();//事务回滚
throw;
}
finally
{
sqlConn.Close();
l_stsqlTransaction.Dispose();
sqlConn.Dispose();
}