[ADO.NET]SqlCommand.Transaction遇到Exception時,會變成null

[ADO.NET]SqlCommand.Transaction遇到Exception時,會變成null

環境

.NET 4.0, VS2010

原由

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

nullException
程式類似如下,


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」的錯誤!

beforeException

afterException

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

所以就將


myCommand.Transaction.Rollback();

 

改成

myTrans.Rollback();

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

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

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

image

結論

.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:從零開始的軟體開發生活

請大家繼續支持 ^_^