六、 事务
1. 本地事务
事务只能在打开的数据库连接上工作。using (SqlConnection conn = new SqlConnection(
"Data Source=localhost;Initial Catalog=SettingKnowledgeManagement;Integrated Security=True"))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand("select * from tb_user", conn, tran);
// 或:
// cmd.Transaction = tran;
try
{
tran.Commit();
}
catch (Exception e1)
{
try
{
tran.Rollback();
// output e1.Message
}
catch (Exception e2)
{
// output e2.Message
}
}
}
2. 分布事务
.Net Framework通过“微软分布式事务处理协调器(MSDTC)”对分布式事务提供支持。TransactionScope实现,但不是ADO.NET的类,要添加引用System.Transactions.dll。提示:在运行分布式事务时,遇到错误“MSDTC on server ‘servername’ is unavailable”。指Distributed Transaction Controller服务没有运行,启动该服务即可。
using (TransactionScope envelope = new TransactionScope())
{
using (SqlConnection sourceConn = new SqlConnection("connection string"))
{
using (SqlConnection destConn = new SqlConnection("connection string"))
{
try
{
sourceConn.Open();
sourceCmd.ExecuteNonQuery();
destConn.Open();
destCmd.ExecuteNonQuery();
envelope.Complete();
}
catch (Exception ex)
{
// Output "Error transferring funds: " + ex.Message;
}
}
}
}