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.

Logging

In the previous post we used the trace class to implement simple logging to a text file, when you're working in dev and no one needs to see these logs then it makes sense.but if you're creating an application that is complex and will need to be trouble shot, then maybe logging and tracing to a text file isn't an option. what you should do instead is implement logging to the system log files. we can use the EevntLog Class to implement just such functionality.

using System;

using System.Collections.Generic;

using System.Diagnostics;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace Logging

{

    class Person

    {

        static int runningID = 0;

        public int Id { get; private set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }

        public Person(string FirstName, string LastName)

        {

            Id = runningID++;

            this.FirstName = FirstName;

            this.LastName = LastName;

        }

        public override string ToString()

        {

            return string.Format("{0} {1} {2}",Id,FirstName,LastName);

        }

    }

   

    class Program

    {

        static void Main(string[] args)

        {

            if (!EventLog.SourceExists("Person Maker"))

                EventLog.CreateEventSource("Person Maker", "Application");

            int selection =-1;

            var people = new SortedList<int,Person>();

            do{

                do

                {

                    Console.WriteLine("1) Add Person\n2) Remove Person\n3) list People\n0) Exit");

                } while (!int.TryParse(Console.ReadLine(), out selection));

               

                switch(selection)

                {

                    case 1:

                        var p = CreatePerson();

                        people.Add(p.Id, p);

                        break;

                    case 2:

                        RemovePerson(ref people);

                        break;

                    case 3:

                        ListPeople(people);

                        break;

                }

            }while(selection != 0);

        }

        public static Person CreatePerson()

        {

            Console.WriteLine("enter in a persons first Name");

            string fname = Console.ReadLine();

            Console.WriteLine("enter in a persons last name");

            string lname = Console.ReadLine();

            var p = new Person(fname, lname);

            var m = string.Format("create person {0}", p.ToString());

            EventLog.WriteEntry("Person Maker", m , EventLogEntryType.Information, 1001);

            return p;

        }

        public static bool RemovePerson(ref SortedList<int,Person> people)

        {

            string LogMsg = string.Empty;

            int logID = -1;

            var logType = EventLogEntryType.Information;

            if (people.Count > 0)

            {

                foreach(var p in people)

                {

                    Console.WriteLine(p.Value.ToString());

                }

                int key;

                Console.WriteLine("Enter in the id of the person to delete");

                string keySTR = Console.ReadLine();

                if (int.TryParse(keySTR, out key))

                {

                    if (people.ContainsKey(key))

                    {

                        int index = people.IndexOfKey(key);

                        people.RemoveAt(index);

                        return true;

                    }

                    LogMsg = "Couldn't delete form list, no such key: " + key;

                    logID = 1002;

                    logType = EventLogEntryType.Error;

                }

                else

                {

                    LogMsg = "Couldn't delete form list, not a valid key: " + keySTR;

                    logID = 1002;

                    logType = EventLogEntryType.Error;

                }

               

            }

            else

            {

                LogMsg = "Couldn't delete form list, list is empty";

                logID = 1001;

                logType = EventLogEntryType.Information;

            }

            EventLog.WriteEntry("Person Maker", LogMsg, logType, logID);

           

            return false;

        }

        public static void ListPeople(SortedList<int,Person> people)

        {

            if (people.Count > 0)

                foreach (var p in people)

                    Console.WriteLine(p.Value.ToString());

            else

                EventLog.WriteEntry("Person Maker", "Listed People", EventLogEntryType.Information , 1002);

        }

    }

  

}

now if we run the above, more or less very similar to our previous example with the tracing

what we did was basically

• created user paul chooch
• created user magda tyvonok
• deleted a user by a key that doesn't exist

now all this gets logged to our system logs, if we open the ones up for application we'll see

a much more robust solution then logging to a text file.