CODEWITHSUNDEEP
c#mysqltransactionsado.net
Aaron J Spetner
Aaron J Spetner

Reputation: 2155

.NET MySqlCommand.Transaction - what is its purpose?

When using the ADO.Net driver for MySQL in .NET, is it necessary to assign a transaction to the MySqlCommand object (for example, using oCmd.Transaction = oTran) or is it enough that the MySqlConnection object has an open transaction? In the code below I begin a transaction on the connection, run two queries with separate MySqlCommand objects without assigning a transaction and then roll back the transaction. In this example, neither UPDATE is committed to the database.

MySqlConnection oConn = new MySqlConnection("Server=spet-il-cent-02;Port=3306;Database=test;Uid=test;Pwd=test123;CharSet=utf8;");
oConn.Open();
MySqlTransaction oTran = oConn.BeginTransaction();
MySqlCommand oCmd = oConn.CreateCommand();

oCmd.CommandText = "UPDATE testing SET testBalance = testBalance + 10 WHERE testID = 1";
oCmd.ExecuteNonQuery();

oCmd = oConn.CreateCommand();
oCmd.CommandText = "UPDATE testing SET testBalance = testBalance - 10 WHERE testID = 2";
oCmd.ExecuteNonQuery();

oTran.Rollback();

oConn.Close();

When checking the Transaction property of oCmd at runtime, I see that it is null.

Obviously, if I call oTran.Commit() then both UPDATE statements are committed. So what is the purpose of the Transaction property of the MySqlCommand object? Is it to allow more than one concurrent transaction on a single connection (where different commands would be bound to different transactions and could be rolled back or committed irrespective of each other)?

Upvotes: 0

Views: 3515

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 416059

For a single statement, you can pretend the property doesn't exist, along with the Commit() and Rollback() methods. If the property is not there, individual statements are auto-committed. In the code from the question, it would be possible to run a query in the brief span between when testID=1 is updated and testID=2 is updated, and the Rollback() method won't accomplish anything.

To take advantage of the MySqlTransaction object, you need to do this:

using (var Conn = new MySqlConnection("Server=spet-il-cent-02;Port=3306;Database=test;Uid=test;Pwd=test123;CharSet=utf8;"))
{
    Conn.Open();
    MySqlTransation Tran = Conn.BeginTransaction();

    using (var Cmd = new MySqlCommand("UPDATE testing SET testBalance = testBalance + 10 WHERE testID = 1", Conn))
    {
        Cmd.Transaction = Tran;
        Cmd.ExecuteNonQuery();
    }
    using (var Cmd = new MySqlCommand("UPDATE testing SET testBalance = testBalance + 10 WHERE testID = 2", Conn))
    {
        Cmd.Transaction = Tran;
        Cmd.ExecuteNonQuery();
    }
    Tran.Rollback();
}

Or even better:

using (var Conn = new MySqlConnection("Server=spet-il-cent-02;Port=3306;Database=test;Uid=test;Pwd=test123;CharSet=utf8;"))
using (var Cmd = new MySqlCommand("UPDATE testing SET testBalance = testBalance + 10 WHERE testID = @testID", Conn))
{
    Conn.Open();
    Cmd.Transaction = Conn.BeginTransaction();

    Cmd.Parameteres.Add("@testID", MySqlDbType.Int32).Value = 1;
    Cmd.ExecuteNonQuery();
    Cmd.Parameters["testID"].Value = 2; //I can't remember at the moment if you need the "@" here or not
    Cmd.ExecuteNonQuery();

    Cmd.Transaction.Rollback();
}

You need transactions mainly when you want to guarantee multiple operations are atomic. This works when you assign the transaction to each instance of the command object individually, each instance of the command uses the same connection, and the connection is held open for the duration of the transaction.

Additionally, you can put multiple statements into a single command object:

string sql = 
  "BEGIN;" + 
  "UPDATE testing SET testBalance = testBalance + 10 WHERE testID = 1;" +
  "UPDATE testing SET testBalance = testBalance - 10 WHERE testID = 2;" +
  "COMMIT;";

using (var Conn = new MySqlConnection("Server=spet-il-cent-02;Port=3306;Database=test;Uid=test;Pwd=test123;CharSet=utf8;"))
using (var Cmd = new MySqlCommand(sql, Conn))
{
    Conn.Open();
    Cmd.ExecuteNonQuery();
}

This is the preferred method, but sometimes the nature of your code will prevent it, and you'll have to use the MySqlTransaction object instead.

Upvotes: 1

Related Questions