c# - 具有多个查询的事务

Question

我有一个 Mysql 类来进行这样的查询。

protected MySqlConnection _connection = null;
public bool Connect()
    {
        try
        {
            this._connection = new MySqlConnection(this._connectionString);
            this._connection.Open();
            return true;
        }
        catch (MySqlException ex)
        {
            //throw new MySQLException(ex.Message, ex.Number);
            return false;
        }
    }

public MySQLParameters Prepare(MySqlCommand command)
    {
        try
        {
            command.Connection = this._connection;
            command.CommandType = CommandType.Text;
            return new MySQLParameters(command);
        }
        catch (MySqlException ex)
        {
            throw new MySQLException(ex.Message, ex.Number);
        }
    }

public void Query(MySqlCommand command)
    {
        try
        {
            command.Connection = this._connection;
            command.CommandType = CommandType.Text;
            command.ExecuteNonQuery();
        }
        catch (MySqlException ex)
        {
            throw new MySQLException(ex.Message, ex.Number);
        }
    }
public DataSet QueryResult(MySqlCommand command)
    {

        try
        {
            DataSet dataSet = new DataSet();
            command.Connection = this._connection;
            command.CommandType = CommandType.Text;
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command);
            dataAdapter.Fill(dataSet);
            return dataSet;
        }
        catch (MySqlException ex)
        {
            throw new MySQLException(ex.Message, ex.Number);
        }
    }

现在我想将其与事务一起使用

但我不知道在哪里添加事务提交和回滚

因为现在我像这样使用我的类。

RemoteMySQL mySql = new RemoteMySQL();
mySql.Connect();
string sql = "INSERT INTO `table1` SET ";
sql += "`name` = @name, ";
sql += "`lastname` = @lastname;";
MySQLParameters parameters = mySql.Prepare(sql);
parameters["name"] = name;
parameters["lastname"] = lastname;
mySql.Query(parameters.GetCommand());

sql = "SELECT LAST_INSERT_ID();";
ds = mySql.QueryResult(sql);
customerId = int.Parse(ds.Tables[0].Rows[0][0].ToString());

sql = "INSERT INTO `table2` SET ";
sql += "`customer_id` = @customer_id, ";
sql += "`item` = @item, ";
sql += "`detail` = @detail;";
parameters = mySql.Prepare(sql);
parameters["customer_id"] = customerId;
parameters["item"] = item;
parameters["detail"] = detail;
mySql.Query(parameters.GetCommand());

// I want to commit here
// If can't insert to table2 will rollback table1 too.

我看看http://zetcode.com/db/mysqlcsharptutorial/ (关于交易)

但仍然不知道如何使用我的代码(在哪里放置提交和回滚)

感谢您的帮助。

Answer 1

应以与 SQLConnection 相同的方式使用

public void Query(MySqlCommand command)
    {
        var transaction = this._connection.BeginTransaction("SampleTransaction");
        try
        {
            command.Connection = this._connection;
            command.CommandType = CommandType.Text;
            command.Transaction = transaction;
            command.ExecuteNonQuery();
            transaction.Commit();
        }
        catch (MySqlException ex)
        {
            transaction.Rollback();
            throw new MySQLException(ex.Message, ex.Number);
        }
    }