current version of ADO.NET doesn't support distributed database transaction, consider to write a System.EnterpriseServices.ServicedComponent component and use DTC/COM+
可以使用asp.net页面级事务,msdn中的例子:public void RunSqlTransaction(string myConnString) { SqlConnection myConnection = new SqlConnection(myConnString); myConnection.Open(); SqlCommand myCommand = new SqlCommand(); SqlTransaction myTrans; // Start a local transaction myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted,"SampleTransaction"); // Must assign both transaction object and connection // to Command object for a pending local transaction myCommand.Connection = myConnection; myCommand.Transaction = myTrans; try { myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"; myCommand.ExecuteNonQuery(); myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"; myCommand.ExecuteNonQuery(); myTrans.Commit(); Console.WriteLine("Both records are written to database."); } catch(Exception e) { myTrans.Rollback("SampleTransaction"); Console.WriteLine(e.ToString()); Console.WriteLine("Neither record was written to database."); } finally { myConnection.Close(); } }
{
SqlConnection myConnection = new SqlConnection(myConnString);
myConnection.Open(); SqlCommand myCommand = new SqlCommand();
SqlTransaction myTrans; // Start a local transaction
myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted,"SampleTransaction");
// Must assign both transaction object and connection
// to Command object for a pending local transaction
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans; try
{
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
myCommand.ExecuteNonQuery();
myTrans.Commit();
Console.WriteLine("Both records are written to database.");
}
catch(Exception e)
{
myTrans.Rollback("SampleTransaction");
Console.WriteLine(e.ToString());
Console.WriteLine("Neither record was written to database.");
}
finally
{
myConnection.Close();
}
}
可能的解决方法:(假定A表的主键是B表的外键)
1、是在数据库中删除两个表的Relation,这样先删除哪个表的数据应该都会成功,但会在B表中留下孤儿数据,或导致A表中的数据不完整(A表中某些记录在B表中找不到关联记录了)
2.调整删除的先后顺序,并运用事务处理。即在删除A表数据时,先删除B表所有关联记录后,再删除A表的记录,只有两个表同时删除成功才commit事务,否则Rollback;
3.当然若A、B表有Relation的话,真正删除的事务怎么设计,还需要根据设计的业务逻辑来定。