关于C#事务的问题

Question

          IDataReader srcReader = null;
          IDbCommand srcCommand = srcConnection.CreateCommand();
          srcCommand.CommandText = queryString;          SqlTransaction sqltran_1 = null;//定义事务
          sqltran_1 = destConnection.BeginTransaction();//事务开始
          SqlCommand destCommand = destConnection.CreateCommand();          //判断目标sql数据库的表是否存在
          SqlCommand cmd = new SqlCommand("use " + dbName + " SELECT count(*) FROM SysObjects WHERE Name ='" + destTableName + "'", destConnection);
          //cmd.Transaction = sqltran;//关联事务
          // sqltran.Commit();//事务提交
          //写入日志
          this.InsertXml(this.xLogFilePath, operators, "Success", "判断目标sql数据库的[" + destTableName + "]表是否存在");
          if (Convert.ToInt32(cmd.ExecuteScalar()) > 0)
          {              try
              {                  SqlCommand cmand = new SqlCommand("use " + dbName + " drop table " + destTableName + "", destConnection);                  cmand.Connection = destConnection;
                  cmand.Transaction = sqltran_1;                  cmand.ExecuteNonQuery();
                  sqltran_1.Commit();//
                  srcReader = InsertDataTableOraSql(destConnection, destTableName, srcReader, srcCommand, destCommand, dbName);
                  //写入日志
                  this.InsertXml(this.xLogFilePath, operators, "Success", "该表以存在，删除该表并创建新表!");
              }              catch (Exception ex)
              {
                  //写入日志
                  InsertXml(this.xLogFilePath, operators, "Err[10002]", ErrorInfo);
                  ErrorInfo = ex.ToString();
                  sqltran_1.Rollback();//事务回滚
                  //将源数据库表的数据复制到目标数据库的表中
                  throw new Exception("10002");              }
          }
          else
          {
              srcReader = InsertDataTableOraSql(destConnection, destTableName, srcReader, srcCommand, destCommand, dbName);
              //写入日志
              this.InsertXml(this.xLogFilePath, operators, "Success", destTableName + "表不存在，直接创建新表!");
          }
我如上写好事务可是老是报 如果分配给命令的连接位于本地挂起事务中，ExecuteScalar 要求命令拥有事务。命令的 Transaction 属性尚未初始化错误 不知道谁能解答一下 不甚感激！如果给ExecuteScalar也添上事务的话后面又会报错！。

Answer 1

那如何处理 我一条SQL语句用了一个我是没什么好办法啦。。请教各位啦!

Answer 2

IDataReader srcReader = null;
          IDbCommand srcCommand = srcConnection.CreateCommand();
          srcCommand.CommandText = queryString;          int count = cmd.ExecuteScalar();          SqlTransaction sqltran_1 = null;//定义事务
          sqltran_1 = destConnection.BeginTransaction();//事务开始
          SqlCommand destCommand = destConnection.CreateCommand();         //cmd.Transaction = sqltran;//关联事务
          // sqltran.Commit();//事务提交
          //写入日志
          this.InsertXml(this.xLogFilePath, operators, "Success", "判断目标sql数据库的[" + destTableName + "]表是否存在");
          if (count > 0)
          {              try
              {                  SqlCommand cmand = new SqlCommand("use " + dbName + " drop table " + destTableName + "", destConnection);                  cmand.Connection = destConnection;
                  cmand.Transaction = sqltran_1;                  cmand.ExecuteNonQuery();
                  sqltran_1.Commit();//
                  srcReader = InsertDataTableOraSql(destConnection, destTableName, srcReader, srcCommand, destCommand, dbName);
                  //写入日志
                  this.InsertXml(this.xLogFilePath, operators, "Success", "该表以存在，删除该表并创建新表!");
              }              catch (Exception ex)
              {
                  //写入日志
                  InsertXml(this.xLogFilePath, operators, "Err[10002]", ErrorInfo);
                  ErrorInfo = ex.ToString();
                  sqltran_1.Rollback();//事务回滚
                  //将源数据库表的数据复制到目标数据库的表中
                  throw new Exception("10002");              }
          }
          else
          {
              srcReader = InsertDataTableOraSql(destConnection, destTableName, srcReader, srcCommand, destCommand, dbName);
              //写入日志
              this.InsertXml(this.xLogFilePath, operators, "Success", destTableName + "表不存在，直接创建新表!");
          }

Answer 3

如果开始了SqlConnection的一个SqlTransaction，必须将所有查询与该事务相关联。
你执行查询操作的sqlCommand没有关联上事务。

Answer 4

如果开始了SqlConnection的一个SqlTransaction，必须将所有查询与该事务相关联。
你执行查询操作的SqlCommand也需要关联上所使用的SqlConnection上的事务。

Answer 5

IDataReader srcReader = null;
          IDbCommand srcCommand = srcConnection.CreateCommand();
          srcCommand.CommandText = queryString;          //判断目标sql数据库的表是否存在
          SqlCommand cmd = new SqlCommand("use " + dbName + " SELECT count(*) FROM SysObjects WHERE Name ='" + destTableName + "'", destConnection);
         int count = cmd.ExecuteScalar();
         SqlTransaction sqltran_1 = null;//定义事务
          sqltran_1 = destConnection.BeginTransaction();//事务开始
          SqlCommand destCommand = destConnection.CreateCommand();
          //cmd.Transaction = sqltran;//关联事务
          // sqltran.Commit();//事务提交
          //写入日志
          this.InsertXml(this.xLogFilePath, operators, "Success", "判断目标sql数据库的[" + destTableName + "]表是否存在");
          if (count > 0)
          {
              try
              {                  SqlCommand cmand = new SqlCommand("use " + dbName + " drop table " + destTableName + "", destConnection);                  cmand.Connection = destConnection;
                  cmand.Transaction = sqltran_1;                  cmand.ExecuteNonQuery();
                  sqltran_1.Commit();//
                  srcReader = InsertDataTableOraSql(destConnection, destTableName, srcReader, srcCommand, destCommand, dbName);
                  //写入日志
                  this.InsertXml(this.xLogFilePath, operators, "Success", "该表以存在，删除该表并创建新表!");
              }              catch (Exception ex)
              {
                  //写入日志
                  InsertXml(this.xLogFilePath, operators, "Err[10002]", ErrorInfo);
                  ErrorInfo = ex.ToString();
                  sqltran_1.Rollback();//事务回滚
                  //将源数据库表的数据复制到目标数据库的表中
                  throw new Exception("10002");              }
          }
          else
          {
              srcReader = InsertDataTableOraSql(destConnection, destTableName, srcReader, srcCommand, destCommand, dbName);
              //写入日志
              this.InsertXml(this.xLogFilePath, operators, "Success", destTableName + "表不存在，直接创建新表!");
          }