public override void ProcessRequests(List<RequestBinder> requestBinders)
{
SqlConnection connection = CreateConnection();
SqlTransaction transaction = connection.BeginTransaction();
bool isCommittingTransaction = false; //表示是否执行了Commit语句
try
{
//这里做一些数据库操作,如Insert, Delete, Update
isCommittingTransaction = true;
transaction.Commit();
}
catch
{
if (!isCommittingTransaction)
{
transaction.Rollback();
}
throw;
}
finally
{
transaction.Dispose();
transaction = null;
CloseConnection(connection);
}
}
我之前写存储过程时,都会在Commit之前先对任何一个可能出错的操作进行判断,如果出错了,就在Commit之前立即RollBack。比如下面这个例子,我是从微软的MemberShip中随便找了个存储过程:CREATE PROCEDURE [dbo].[aspnet_Membership_UpdateUser]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@Email nvarchar(256),
@Comment ntext,
@IsApproved bit,
@LastLoginDate datetime,
@LastActivityDate datetime,
@UniqueEmail int,
@CurrentTimeUtc datetime
AS
BEGIN
DECLARE @UserId uniqueidentifier
DECLARE @ApplicationId uniqueidentifier
SELECT @UserId = NULL
SELECT @UserId = u.UserId, @ApplicationId = a.ApplicationId
FROM dbo.aspnet_Users u, dbo.aspnet_Applications a, dbo.aspnet_Membership m
WHERE LoweredUserName = LOWER(@UserName) AND
u.ApplicationId = a.ApplicationId AND
LOWER(@ApplicationName) = a.LoweredApplicationName AND
u.UserId = m.UserId IF (@UserId IS NULL)
RETURN(1) IF (@UniqueEmail = 1)
BEGIN
IF (EXISTS (SELECT *
FROM dbo.aspnet_Membership WITH (UPDLOCK, HOLDLOCK)
WHERE ApplicationId = @ApplicationId AND @UserId <> UserId AND LoweredEmail = LOWER(@Email)))
BEGIN
RETURN(7)
END
END DECLARE @TranStarted bit
SET @TranStarted = 0 IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0 UPDATE dbo.aspnet_Users WITH (ROWLOCK)
SET
LastActivityDate = @LastActivityDate
WHERE
@UserId = UserId IF( @@ERROR <> 0 )
GOTO Cleanup UPDATE dbo.aspnet_Membership WITH (ROWLOCK)
SET
Email = @Email,
LoweredEmail = LOWER(@Email),
Comment = @Comment,
IsApproved = @IsApproved,
LastLoginDate = @LastLoginDate
WHERE
@UserId = UserId IF( @@ERROR <> 0 )
GOTO Cleanup IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END RETURN 0Cleanup: IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END RETURN -1
END
那么我想在C#中也应该这样做,所以我加了一个变量isCommittingTransaction用来判断是否执行了Commit操作。因为我发现,如果异常是由于执行了Commit操作后发生的,那么在执行RollBack时会出现“此 SqlTransaction 已完成;它再也无法使用。”这样的错误。这样做对吗?另外,我看到网上有人说,如果是在执行Commit函数时出错的,那么出错时根本不需要进行RollBack操作,因为Commit函数内部会自动RollBack,是这样的吗?
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open(); SqlCommand command = connection.CreateCommand();
SqlTransaction transaction; // Start a local transaction.
transaction = connection.BeginTransaction("SampleTransaction"); // Must assign both transaction object and connection
// to Command object for a pending local transaction
command.Connection = connection;
command.Transaction = transaction; try
{
command.CommandText =
"Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
command.ExecuteNonQuery();
command.CommandText =
"Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
command.ExecuteNonQuery(); // Attempt to commit the transaction.
transaction.Commit();
Console.WriteLine("Both records are written to database.");
}
catch (Exception ex)
{
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message); // Attempt to roll back the transaction.
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
}
}
}
2. catch块一般是
catch (Exception ex)
{
// Handle the exception if the transaction fails to commit try
{
// Attempt to roll back the transaction.
transaction.Rollback();
}
catch (Exception exRollback)
{
// Throws an InvalidOperationException if the connection
// is closed or the transaction has already been rolled
// back on the server. }
}因为出错未必一定是事务的错误