mysql transactions in asp.net?

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!!!

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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.
}

Method 2

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

Method 3

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.

Method 4

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0