TransactionScope()事务的问题

TransactionScope()事务的问题

using (TransactionScope ts = new TransactionScope())
{
   using (SqlConnection conn = new SqlConnection(@"xxx"))
   {
      conn.Open();

      DataSet dataSet = new DataSet();
      SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(@"SELECT * FROM testtable", conn);
      SqlCommandBuilder builder = new SqlCommandBuilder(sqlDataAdapter);
      sqlDataAdapter.Fill(dataSet);

      foreach (DataRow dataRow in dataSet.Tables[0].Rows)
      {
         dataRow["flag"] = 1;
         Thread.Sleep(120000); =>模拟程序很长时间的操作

      }

      sqlDataAdapter.Update(dataSet);
   }
   ts.Complete();
}=>这里发生了错误,错误信息:已中止的事务

 

请教的微软工程师，得到的答案是：

 在connection string中指定"Transaction Binding = Explicit Unbind"来避免当超时时数据被错误的提交。如有必要，还可以指定适当的超时时间。Transaction Binding的默认参数是Implicit Unbind，这应该是在设计时出于向后兼容的一些考虑。目前如果使用TransactionScope，建议将Transaction Binding都设为Explicit Unbind。

 

控制与登记的 System.Transactions 事务关联的连接。

可能的值包括：

Transaction Binding=Implicit Unbind;

Transaction Binding=Explicit Unbind;

Implicit Unbind 可使连接在事务结束时从事务中分离。分离后，连接上的其他请求将以自动提交模式执行。
在事务处于活动状态的情况下执行请求时，不会检查 System.Transactions.Transaction.Current 属性。
事务结束后，其他请求将以自动提交模式执行。

Explicit Unbind 可使连接保持连接到事务，直到连接关闭或调用显式 SqlConnection.TransactionEnlist(null)。
如果 Transaction.Current 不是登记的事务或登记的事务未处于活动状态，则引发 InvalidOperationException。

又：

using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required,new TimeSpan(1,1,0)))    //超时时间为1小时1分钟

 

传统的事务写法:

using (SqlConnection conn = new SqlConnection(@"xxx"))
{
    conn.Open();
                
    SqlCommand sqlCommand = new SqlCommand(@"SELECT * FROM testtable", conn);
    sqlCommand.Transaction = conn.BeginTransaction();

    try
    {
        DataSet dataSet = new DataSet();
        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
        SqlCommandBuilder builder = new SqlCommandBuilder(sqlDataAdapter);
        sqlDataAdapter.Fill(dataSet);

        foreach (DataRow dataRow in dataSet.Tables[0].Rows)
        {
             dataRow["flag"] = 1;
         }

         sqlDataAdapter.Update(dataSet);
         sqlCommand.Transaction.Commit();
   }
   catch
   {
         sqlCommand.Transaction.Rollback();
   }
}

 

 

 

 

SqlConnection   myConnection   =   new   SqlConnection(DataBaseOperation.strSqlConDataBaseOperation);
myConnection.Open();
SqlTransaction   myTrans;
myTrans   =   myConnection.BeginTransaction();

SqlDataAdapter   sqldataadapter   =   new   SqlDataAdapter( "Select   *   From   ProPlaDocuments_details ",myConnection);
SqlCommandBuilder   myCommandBuilder   =   new   SqlCommandBuilder(sqldataadapter);

sqldataadapter.SelectCommand   =   new   SqlCommand( "Select   *   From   ProPlaDocuments_details ",   myConnection,   myTrans);
sqldataadapter.InsertCommand   =   new   SqlCommand( " ",   myConnection,   myTrans);
sqldataadapter.UpdateCommand   =   new   SqlCommand( " ",   myConnection,   myTrans);
sqldataadapter.DeleteCommand   =   new   SqlCommand( " ",   myConnection,   myTrans);
sqldataadapter.InsertCommand   =   myCommandBuilder.GetInsertCommand();
sqldataadapter.UpdateCommand   =   myCommandBuilder.GetUpdateCommand();
sqldataadapter.DeleteCommand   =   myCommandBuilder.GetDeleteCommand();

try
{
sqldataadapter.Update(dTableProPlaDocuments_details);
myTrans.Commit();
}
catch...