这里主要是说明在C#中执行事务的使用方法。其代码如下:
/// <summary>
/// 执行事务
/// </summary>
/// <param name="sSqlList">一次执行的多条语句列表</param>
public void RunOldDbTransaction(string[] sSqlList)
{
sConnectionString = ConfigurationManager.AppSettings.Get("UserConnection");
OracleConnection ocConnection = new OracleConnection(sConnectionString);
ocConnection.Open();
//创建并开启事务
OracleTransaction oraTrans = ocConnection.BeginTransaction(IsolationLevel.ReadCommitted);
OracleCommand ocCommand = ocConnection.CreateCommand();
ocCommand.Transaction = oraTrans;
try
{
//循环事务中语句
foreach (string sVal in sSqlList)
{
ocCommand.CommandText = sVal;
ocCommand.ExecuteNonQuery();
}
//完成后确认事务
oraTrans.Commit();
}
catch (Exception ex)
{
//出错后回滚事务
oraTrans.Rollback();
throw ex;
}
finally
{
ocConnection.Close();
}
}
/// 执行事务
/// </summary>
/// <param name="sSqlList">一次执行的多条语句列表</param>
public void RunOldDbTransaction(string[] sSqlList)
{
sConnectionString = ConfigurationManager.AppSettings.Get("UserConnection");
OracleConnection ocConnection = new OracleConnection(sConnectionString);
ocConnection.Open();
//创建并开启事务
OracleTransaction oraTrans = ocConnection.BeginTransaction(IsolationLevel.ReadCommitted);
OracleCommand ocCommand = ocConnection.CreateCommand();
ocCommand.Transaction = oraTrans;
try
{
//循环事务中语句
foreach (string sVal in sSqlList)
{
ocCommand.CommandText = sVal;
ocCommand.ExecuteNonQuery();
}
//完成后确认事务
oraTrans.Commit();
}
catch (Exception ex)
{
//出错后回滚事务
oraTrans.Rollback();
throw ex;
}
finally
{
ocConnection.Close();
}
}
注:SQL中用法
BEGIN TRANS
DECLARE @orderDetailsError int, @productError int
//执行的语句1
DELETE FROM Order Details WHERE ID=111
//得到错误
SELECT @orderDetailsError = @@ERROR
//执行的语句2
DELETE FROM Products WHERE ProductID=112
//得到另外一个错误
SELECT @productError = @@ERROR
IF @orderDetailsError = 0 AND @productError = 0
COMMIT TRANS
ELSE
ROLLBACK TRANS
这种方法可以查阅相关sql server 帮助。
DECLARE @orderDetailsError int, @productError int
//执行的语句1
DELETE FROM Order Details WHERE ID=111
//得到错误
SELECT @orderDetailsError = @@ERROR
//执行的语句2
DELETE FROM Products WHERE ProductID=112
//得到另外一个错误
SELECT @productError = @@ERROR
IF @orderDetailsError = 0 AND @productError = 0
COMMIT TRANS
ELSE
ROLLBACK TRANS