Transactions

Sometimes when implementing access to a data base you need an all or nothing solution, what I mean is lets say you need to run a couple of transactions sequentially and if one of them fails you need to roll them all back. To accomplish this we use the Transactionscope class, take a look at the program below:

using System;

using System.Data.SqlClient;

using System.Transactions;

class Program

{

    private const string ConnectionString = @"

            Data Source=SSRAP7;

            Initial Catalog=PPLTest;

            Integrated Security=True";

    static void Main(string[] args)

    {

        using (var conn = new SqlConnection(ConnectionString))

        {

            try

            {

                conn.Open();

                AddPerson("Pawel", "Chooch", "1-31-1984", conn);

                new SqlCommand("Delete FROM Person", conn).ExecuteNonQuery();

                AddPerson("Tomek", "Chooch", "8-28-1988", conn);

                AddPerson("Magda", "Tivonuk", "6-28-1984", conn);

                AddPerson("Marin", "Smartec", "Failed", conn);

            }

            catch (SqlException sqlEx)

            {

                Console.WriteLine(sqlEx.Message);

            }

            finally

            {

                conn.Close();

            }

        }

        using (var conn = new SqlConnection(ConnectionString))

        {

            try

            {

                conn.Open();

                var cmnd1 = new SqlCommand(@"SELECT * FROM Person", conn);

                var reader = cmnd1.ExecuteReader();

                while (reader.Read())

                {

                    Console.WriteLine("{0}) {1} {2} Born {3}",

                        reader[0], reader[1], reader[2], reader[3]);

                }

            }

            catch (SqlException sqlEx)

            {

                Console.WriteLine(sqlEx.Message);

            }

            finally

            {

                conn.Close();

            }

        }

    }

    public static int AddPerson(string fn, string ln, string bdate, SqlConnection conn)

    {

        var cmnd = new SqlCommand(@"

            INSERT INTO Person ([FirstName],[LastName],[BirthDate])

            Values(@FirstName, @LastName, @BirthDate)", conn);

        cmnd.Parameters.AddRange(new SqlParameter[] {

                new SqlParameter("@FirstName", fn),

                new SqlParameter("@LastName", ln),

                new SqlParameter("@BirthDate", bdate),

            });

        return cmnd.ExecuteNonQuery();

    }

}

pretty strait forward, we run this:

• add one person to the table 
• delete all people in the table
• Add two more successfully
• Fail on the last insert because of a datatime parse error

our output screen looks like

now lets say you needed an all or nothing either all of the transactions complete or none of them do. take a look at the modified code

using System;

using System.Data.SqlClient;

using System.Transactions;

class Program

{

    private const string ConnectionString = @"

            Data Source=SSRAP7;

            Initial Catalog=PPLTest;

            Integrated Security=True";

    static void Main(string[] args)

    {

        try

        {

            using (var ts = new TransactionScope())

            {

                using (var conn = new SqlConnection(ConnectionString))

                {

                    try

                    {

                        conn.Open();

                        AddPerson("Pawel", "Chooch", "1-31-1984", conn);

                        new SqlCommand("Delete FROM Person", conn).ExecuteNonQuery();

                        AddPerson("Tomek", "Chooch", "8-28-1988", conn);

                        AddPerson("Magda", "Tivonuk", "6-28-1984", conn);

                        AddPerson("Marin", "Smartec", "Failed", conn);

                    }

                    catch (SqlException sqlEx)

                    {

                        Console.WriteLine(sqlEx.Message);

                    }

                    finally

                    {

                        conn.Close();

                    }

                }

                ts.Complete();

            }

        }

        catch (TransactionAbortedException ex)

        {

            Console.WriteLine("TransactionAbortedException Message: {0}", ex.Message);

        }

        catch (ApplicationException ex)

        {

            Console.WriteLine("ApplicationException Message: {0}", ex.Message);

        }

        using (var conn = new SqlConnection(ConnectionString))

        {

            try

            {

                conn.Open();

                var cmnd1 = new SqlCommand(@"SELECT * FROM Person", conn);

                var reader = cmnd1.ExecuteReader();

                while (reader.Read())

                {

                    Console.WriteLine("{0}) {1} {2} Born {3}",

                        reader[0], reader[1], reader[2], reader[3]);

                }

            }

            catch (SqlException sqlEx)

            {

                Console.WriteLine(sqlEx.Message);

            }

            finally

            {

                conn.Close();

            }

        }

    }

    public static int AddPerson(string fn, string ln, string bdate, SqlConnection conn)

    {

        var cmnd = new SqlCommand(@"

            INSERT INTO Person ([FirstName],[LastName],[BirthDate])

            Values(@FirstName, @LastName, @BirthDate)", conn);

        cmnd.Parameters.AddRange(new SqlParameter[] {

                new SqlParameter("@FirstName", fn),

                new SqlParameter("@LastName", ln),

                new SqlParameter("@BirthDate", bdate),

            });

        return cmnd.ExecuteNonQuery();

    }

}

This time if you run the code above you'll notice that the people listed are the same ones as before, meaning that despite the fact that only one of the quires failed, none of them where committed to the database.