Incorporating transaction into your C# data applications

It's real hard, though not impossible, to do any kind of efficient database work in todays programming world without employing transaction. Whenever you need ultiple things to happen at once, or cancel/undo things on your database the best way to do this is with using transactions. To do this without the native (built-in) database support in C# would not only be tedious and miserable, it would be very difficult. Fortunately Microsoft has given us ADO.NET that we can use in our C# applications. What is a transaction you ask, well a database transaction is a sequence of data transfer that are treated as a single entity, ensuring data integrity.

Database transactions are an invaluable tool to any developer for ensuring non-corrupt data and maintaining integrity in your database. Transactions allow you to rollback a sequence of events in the event that one aspect of the transaction fails and help prevent orphaned records due to the remaining data being inserted even when part of the sequence fails in some manner. I know in the past I didnt think transactions were all that important, and when something failed I would go in and manually delete all the data that by all rights shouldt have been there because part of the execution failed, so it just moved on and finished inserting data. When I discovered transactons it was a Godsend to me.

While there are many database systems that support transactions, Oracle, MySql, MSSQL, for the purpose of this article we will be focusing on SQL transactions, how they work and how to employ them. The System.Data.SqlClient Namespace offers us everything we need to employ transactions in our applications with the greatest of ease. The class we will focus on in this article is the SqlTransaction Class. So let's take a look at the code, how to incorporate transaction in your database applications.

The first thing I want to mention is that since the SqlException has no default constructors we cannot throw one when we run into problems, but we can generate one with RAISERROR on the database side.

NOTE: Some will say this is bad practice because it's required to make a round trip to the SQL Server, but it's something I've used for quite some time now and have yet to have to show any visible performance issues, so using it is completely your choice. Now on to our method for generating a SqlException:

/// <summary>
/// since SqlException has no default constructors and we cannot throw one we
/// use this method to call RAISERROR on the SQL side of things to cause a
/// SqlException
/// </summary>
private void ThrowSQlException()
{
    SqlCommand exCommand = null;
    try
    {
        //since we cannot throw a SqlException let's do it indirectly using
        //the RAISERROR function in SQL Server. Now keep in mind going this
        //route will make a round trip to the SQL database
        exCommand = new SqlCommand("raiserror('Transaction could not be found', 16,1)");
        exCommand.ExecuteNonQuery();
    }
    catch (SqlException ex)
    {
        Console.WriteLine(string.Format("An exception of type {0} has been generated. The error message generated in {1}",
            ex.GetType(),
            ex.Message));
    }
    finally
    {
        exCommand.Dispose();
    }       
}

Pretty simple, open your new SqlCommand object, call RAISERROR znd generate an error then catch it. We use a finally block so we can dispose of the resources we use for accomplishing this task.
Next we need to begin our transaction, we do this with the BeginTransaction function of the SqlConnection Class. It's advisable to make sure your connection is open, if not then open it then begin the transaction. Our BeginTransaction method returns a new SqlTransaction object to be used through out our class

/// <summary>
/// method for beginning a database transaction
/// </summary>
/// <param name="con">database connection we're working with</param>
/// <returns></returns>
public SqlTransaction BeginTransaction(SqlConnection con)
{
    try
    {
        if (con.State != ConnectionState.Open)
        {
            con.Open();
            return con.BeginTransaction();
        }
        else
        {
            return con.BeginTransaction();
        }
    }
    catch (SqlException ex)
    {
        Console.WriteLine(string.Format("An exception of type {0} was generated while beginning your transaction. The error message returned was {1}",
            ex.GetType(),
            ex.Message));
        return null;
    }
}

Now when using transactions you must commit the transaction, otherwise you can/will cause a database lock due to the process never being finalized.

NOTE: You cannot execute a rollback once you have committed your transaction, so be aware that everything worked as planned before committing it. Our method for doing the commit takes a SqlTransaction as a parameter, so that way the methon knows what transaction we're working  with (We received our transaction from the BeginTransaction method).

First and foremost we make sure the transaction we're working with isnt null, if it is we use the ThrowSqlException method so we can let the user/application know. We also make sure the transaction has a connection and that it's open. When committing a transaction (or performing a rollback) can throw an InvalidOperationException if the connection is lost or if the transaction has already been committed & rolled back). So this is how we go about this

/// <summary>
/// method for doing a commit on the specified transaction. The transaction in this case
/// is clobal to this class (for demonstrative purposes only). If it fails then we let the
/// user know and why
/// </summary>
/// <param name="trans">the SqlTransaction we're carrying around with us</param>
/// <returns></returns>
public bool CommitTransactions(SqlTransaction trans)
{
    try
    {
        //make sure our SqlTransaction still exists
        if (trans != null)
        {
            //make sure our connection for our transaction hasnt been terminated
            //and it's connection is still open
            if ((trans.Connection != null) && (trans.Connection.State == ConnectionState.Open))
                trans.Commit();
            else
            {
                ThrowSQlException();
                return false;
            }
        }
        else
        {
            //raise an error (since we cannot throw a SqlException
            ThrowSQlException();
            return false;
        }


        return true;
    }
    catch (SqlException ex)
    {
        Console.WriteLine("An exception of type {0} was encountered when trying to commit the transaction and an error message of {1} was generated",
            ex.GetType(),
            ex.Message);
        return false;
    }
    catch (InvalidOperationException ex)
    {
        Console.WriteLine(string.Format("An invalid operation was performed. The error message gereerated is {0}", ex.Message));
        return false;
    }
}

Now when using transaction if something goes wrong we need a way to roll back the transaction, especially since a lot of transaction involve cascade inserts and cascade deletes, so we dont want just part of the plan to be executed as this can cause orphaned records and remove data integrity.

When doing a rollback we check the same states as we do when committing a transaction: The transaction isnt null, the transaction has a connection and that the connection is open. If either of these fail an InvalidOperationException will be thrown/generated so we also check for that as well. So without further ado this is how we would go about performing a transaction rollback

/// <summary>
/// method for rolling back a transaction because something went wrong
/// this is to help maintain data integrity
/// </summary>
/// <param name="trans">the transaction we are doing a rollback on</param>
/// <returns></returns>
private bool RollbackTransaction(ref SqlTransaction trans)
{
    try
    {
        if (trans != null)
        {
            if ((trans.Connection != null) && (trans.Connection.State == ConnectionState.Open))
                trans.Rollback();
            else
            {
                //raise an error (since we cannot throw a SqlException
                ThrowSQlException();
                return false;
            }
        }
        else
        {
            ThrowSQlException();
            return false;
        }

        return true;
    }
    catch (SqlException ex)
    {
        Console.WriteLine(string.Format("An exception of type {0} was generated while attepting to rollback the transaction. The error message generated is {1}",
            ex.GetType(), ex.Message));
        return false;
    }
    catch (InvalidOperationException ex)
    {
        Console.WriteLine(string.Format("An invalid operation was performed. The error message gereerated is {0}", ex.Message));
        return false;
    }
    finally
    {
        if (trans.Connection.State == ConnectionState.Open)
            trans.Connection.Close();

        trans.Dispose();
    }
}

So as you can see Microsoft, when giving us ADO.NET, has made it rther easy to implement transaction into our data applications. Microsoft SQL Server isnt the only system that supports transaction. Aside from the [b]SqlTransaction[/b] Class we have:

• OleDbTransaction Class
• OracleTransaction Class
• Not built into ADO.NET you can also use MySqlException Class that's available in the MySql .Net Connector available from MySql

Next we will look at the advantages (and how to) create & implement a provider independant Data Access Layer (DAL)