mysql transactions in asp.net ?

Question

Hi,

Can you guys let me know the way of handling transactions in asp.net?

i.e. I have a few queries (Present in different functions and called under various situations) that have to be executed as a whole. So, how should I go about it?

Not sure of the syntax and the method/practice for writing the statements in .net (commit, rollback etc).

Kindly let me know. Also, plz point me to some good articles if possible. Thanks!!!

Answer 1

I recommend using TransactionScope, because you can use it no mater what DB you are using. You can even do distributed transactions (operations against multiple databases within the same transaction) with it.

You can refer to a link for a code example, but in general, you do this:

try
{
    using (TransactionScope scope = new TransactionScope())
    {
        using (MySqlConnection connection1 = new MySqlConnection (connectionString))
        {
            // Opening the connection automatically enlists it in the 
            // TransactionScope as a lightweight transaction.
            connection1.Open();

            // create the DB commands and perform the DB operations
            .
            .
            .

            // The Complete method commits the transaction. If an exception has been thrown,
            // Complete is not called and the transaction is rolled back.
            scope.Complete();    
        }
    }
}
catch (Exception e)
{
    // something went wrong, handle the exception accordingly. Note
    // that since we did not call TransactionScope.Complete, nothing
    // gets committed to the DB.
}

Answer 2

Here's another starter for TransactionScope: Implementing an Implicit Transaction using Transaction Scope

Answer 3

If its a local transaction you can also use ado.net's transaction object. TransactionScope will handle distributed transactions if needed but requires MSDTC to be configured if a transaction is promoted to a distributed transaction.

http://msdn.microsoft.com/en-us/library/2k2hy99x.aspx

Both are in the System.Transactions Namespace http://msdn.microsoft.com/en-us/library/system.transactions.aspx

Answer 4

Don't know much about TransactionScope, but I just use the normal IDbTransaction like this:

IDbConnection conn = null;
IDbCommand cmd = null;
IDbTransaction tran = null;

try
{
    conn = DatabaseUtil.GetConnection(); //Get the connection somehow
    cmd = conn.CreateCommand();

    tran = conn.BeginTransaction();
    cmd.Transaction = tran;


    //Do your DB Work

    tran.Commit();
}
catch (SystemException ex)
{
    tran.Rollback();
}
finally
{
    if (conn != null) conn.Close();
}

With the IDb classes you are DB independent too to a certain degree.