How to use transactions in .NET

This is a very high level example of using transactions with ADO.NET objects in C#. Please refer the inline comments for instructions.

private void TransactionExample()
{
    SqlTransaction transaction = null;
    SqlConnection con = null;

    // they will be used to decide whether to commit or rollback the transaction
    bool query1 = false;
    bool query2 = false;

    try
    {
        con = new SqlConnection(CONNECTION_STRING);
        con.Open();

        // begin the transaction
        transaction = con.BeginTransaction();

        // Run some queries in DB
        using (SqlCommand cmd = con.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = " your query"

            // assosiate this command with transaction
            cmd.Transaction = transaction;

            query1 = cmd.ExecuteNonQuery() == 1;
        }

        // Uncomment the line below to test if transaction works
        // throw new Exception("Transaction test!");

        // Run more queries
        using (SqlCommand cmd = con.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = " your query" ;

            // assosiate this command with transaction
            cmd.Transaction = transaction;

            query2 = cmd.ExecuteNonQuery() == 1;
        }

        if (query1 & amp; & query2)
 {
            // Commit transaction because both queries run successfully
            transaction.Commit();
        }
    }
    catch
    {
        // Don't run queries in DB because there is an error
        transaction.Rollback();
    }
    finally
    {
        con.Close();
    }
}

For more details, check this great article out!

Leave a Reply