亂馬客

環境

.NET 4.0, VS2010

原由

同事反應使用Ado.NET交易發生失敗時，會有NullReferenceException跑出來!

程式類似如下，

SqlConnection myConnection = new SqlConnection(@"Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;");
myConnection.Open();
// Start a local transaction.
SqlTransaction myTrans = myConnection.BeginTransaction();
// Enlist the command in the current transaction.
SqlCommand myCommand = myConnection.CreateCommand();
myCommand.Transaction = myTrans;
try
{
    myCommand.CommandText = "Insert into t1 (c1) VALUES (1)";
    myCommand.ExecuteNonQuery();
    myCommand.CommandText = "Insert into t1 (c1) VALUES ('xx1')";
    myCommand.ExecuteNonQuery();
    Console.WriteLine("Both records are written to database.");
}
catch (Exception ex)
{
    myCommand.Transaction.Rollback();
}
finally
{
    myConnection.Close();
}

 

建立DB Table

CREATE TABLE t1(c1 INT)

發生Exception時，myCommand.Transaction會被清成null，所以叫它來Rollback自然就會出現「NullReferenceException」的錯誤!

但是，這時，myTrans並不會被清成null哦!

所以就將

myCommand.Transaction.Rollback();

 

改成

myTrans.Rollback();

心想，這種明顯的錯誤，不可能沒有被測試出來呀!

因為該程式是從.net 1.1升級到.net 4.0，會不會是.net 4.0才有問題呢!

於是把程式放到VS2003上去Run，myCommand.Transaction沒有被清成null。

結論

.net 1.1，遇到Exception時，myCommand.Transaction 不會 被清成null。
.net 1.1以上(.net 2.0, .net 4.0)，遇到Exception時，myCommand.Transaction 會 被清成null。
所以要Call Rollback時，還是Call自已建立的交易物件，別用sqlCommand.Transaction。 

SqlConnection myConnection = new SqlConnection(@"Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;");
myConnection.Open();
// Start a local transaction.
SqlTransaction myTrans = myConnection.BeginTransaction();
// Enlist the command in the current transaction.
SqlCommand myCommand = myConnection.CreateCommand();
myCommand.Transaction = myTrans;
try
{
    myCommand.CommandText = "Insert into t1 (c1) VALUES (1)";
    myCommand.ExecuteNonQuery();
    myCommand.CommandText = "Insert into t1 (c1) VALUES ('xx1')";
    myCommand.ExecuteNonQuery();
    Console.WriteLine("Both records are written to database.");
}
catch (Exception ex)
{
     myTrans.Rollback();
}
finally
{
    myConnection.Close();
}

 

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活」

請大家繼續支持 ^_^