Using TransactionScope for SQL commands

TransactionScope for SQL commands not working as expected? This article describes shortly how to use TransactionScope and also one of the most easy-to-make errors.   

A transaction is a unit of work. It may consist of one or more database operations that you want to execute as a whole: if all operations are successful, the changes can be made permanent; if one of them fails, none of the changes should be performed.  

System.Transactions.TransactionScope is the class you need for this purpose. Let’s assume that you have a database with 2 tables: Users and Books with a one-to-many relationship (one user can have one or more books).  

When you first add a user, you also want to add a book for that user. If inserting a book fails, you don’t want the user to be added either. You can do that with the following code:  

try
        {
            SqlConnection myConnection = new SqlConnection(Convert.ToString(ConfigurationManager.ConnectionStrings["connstr"]));
            using (TransactionScope ts = new TransactionScope())
            {
                myConnection.Open();
 
                SqlCommand cmd1 = new SqlCommand("insert into Users (Name) values (@newUser)", myConnection);
                cmd1.Parameters.Add(new SqlParameter("newUser", newUser));
                cmd1.ExecuteNonQuery();
 
                SqlCommand cmd2 = new SqlCommand("insert into Books (BookName, UserID) values (@bookName, @UserID)", myConnection);
                cmd2.Parameters.Add(new SqlParameter("bookName", "book1"));
                cmd2.Parameters.Add(new SqlParameter("UserID", "BOGUS ID")); //the sql command should fail
                cmd2.ExecuteNonQuery();
                ts.Complete();
            }
        }
        catch(Exception x)
        {
            Console.WriteLine("Cannot complete transaction");
            throw x;
        }

I have set the userID in cmd2 to a bogus value intentionally for it to fail. You will notice that the first command successful, but after trying & failing to execute the second command, no changes are made in the Users table. 

If you set a breakpoint in your application on the second command and then run a select query on the Users table using a SQL Management Studio Express for example, the query will be in a waiting state because the table is locked by the transaction. It will only be available again after the transaction is finished (either completed or rolledback). 

Very important: Always remember to open the connection within the TransactionScope block! If you move the line myConnection.Open() outside the block, the database changes will be commited even if one command fails. So it will not behave as a transaction and it will not let you know about it !

Finally, there’s another very important peculiarity of what does Cialis that brings it so high above its alternatives. It is the only med that is available in two versions – one intended for use on as-needed basis and one intended for daily use. As you might know, Viagra and Levitra only come in the latter of these two forms and should be consumed shortly before expected sexual activity to ensure best effect. Daily Cialis, in its turn, contains low doses of Tadalafil, which allows to build its concentration up in your system gradually over time and maintain it on acceptable levels, which, consequently, makes it possible for you to enjoy sex at any moment without having to time it.