TransactionScope在MySQL中如何使用?

[火星大能猫]

写了一个最简单的测试代码

  t_provinceDAL dal = new t_provinceDAL();
            using (TransactionScope tran = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 0, 10, 0)))
            {
                bool rt = false;
                rt = dal.add("121113");
                rt = dal.delete(11);
                tran.Complete();
            }


//t_provinceDAL 
 public bool add(string pname)
        {
            string strSql = "insert into t_province(pname) values(@pname);";
            return DBHelperMySQL.ExecuteNonQuery(strSql, new MySqlParameter("@pname", pname)) > 0;
        }

        public bool delete(int id)
        {
            string strSql = "delete from t_province where id = " + id.ToString();
            return DBHelperMySQL.ExecuteNonQuery(strSql) > 0;
        }

//DBHelper
 public static int ExecuteNonQuery(string commandText, params MySqlParameter[] commandParameters)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            // Create a command and prepare it for execution
            MySqlCommand cmd = new MySqlCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, null, CommandType.Text, commandText, commandParameters, out mustCloseConnection);

            // Finally, execute the command
            int retval = cmd.ExecuteNonQuery();

            // Detach the SqlParameters from the command object, so they can be used again
            cmd.Parameters.Clear();
            return retval;
        }


 private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType commandType, string cmdText, MySqlParameter[] cmdParms, out bool mustCloseConnection)
        {
            mustCloseConnection = false;
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {


                foreach (MySqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

报错
第一段执行成功了 也没有提交,这是正确的
到delete就报错了
提示
Multiple simultaneous connections or connections with different connection strings inside the same transaction are not currently supported.

[圣殿骑士18]

你的数据库是不是不在本地。 使用TransactionScope有诸多限制，在TransactionScope内新建多个connection可能会被识别为分布式事务。 而分布式事务又需要做一些dts配置什么的。 用普通事务就行了

public void CustomTransactionScope(DelegateTransaction delegateTransaction)
        {
            MySqlConnection conn = new MySqlConnection(this._connectionString);
            conn.Open();

            MySqlTransaction trans = conn.BeginTransaction();

            try
            {
                delegateTransaction(trans);
                trans.Commit();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                conn.Dispose();

                throw ex;
            }
        }

[火星大能猫]

引用 1 楼 daixf_csdn 的回复:

你的数据库是不是不在本地。 使用TransactionScope有诸多限制，在TransactionScope内新建多个connection可能会被识别为分布式事务。 而分布式事务又需要做一些dts配置什么的。 用普通事务就行了

public void CustomTransactionScope(DelegateTransaction delegateTransaction)
        {
            MySqlConnection conn = new MySqlConnection(this._connectionString);
            conn.Open();

            MySqlTransaction trans = conn.BeginTransaction();

            try
            {
                delegateTransaction(trans);
                trans.Commit();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                conn.Dispose();

                throw ex;
            }
        }

已解决 DBHelper写的有问题 没有及时关闭connection