Transaction in SQL Server, ADO.Net and LINQ (1)

Transactions in SQL Server and ADO.NET

Sometimes we need multiple SQL statements to be executed all or none. We need to make sure that if one statement failed none of the others is executed. We can do this manually by checking whether any fail and if a statement fail we undo the previous statements. This approach isn't efficient because it is time consuming and more importantly it since databases are multi-users, then a user can access the data at the instance just after the failing of the statement and before undoing the action.

The solution is using transactions.

In Transact-SQL, we have the following commands:

1. BEGIN TRANSACTION ,
2. COMMIT TRANSACTION ==> to commit the transaction to the database, 
3. ROLLBACK TRANSACTION ==> to undo any previous commands.

To use transaction in coding using ADO.Net we have the following class

• System.Data.SqlClient.SqlTransaction 

Let's see the following example code to show how to use it.

I have a Database called Bookie, with a table called Books(BookID,BookName,ImageURL,BookCost).

            SqlConnection db = new SqlConnection("ConnectionString Here");
      SqlTransaction transaction;

         db.Open();
      transaction = db.BeginTransaction();          LINE 1
      try 
      {
         new SqlCommand("INSERT INTO Books " +
            "(BookName,BookCost) VALUES ('nb1',0);", db, transaction)
            .ExecuteNonQuery();
         new SqlCommand("INSERT INTO Books " +
            "(BookName,BookCost) VALUES ('nb2',10);", db, transaction)
            .ExecuteNonQuery();
         new SqlCommand("INSERT INTO Books " +
            "(BookName,BookCost) VALUES ('nb3',20);", db, transaction)
            .ExecuteNonQuery();
         transaction.Commit();              LINE 2
      }  
      catch (SqlException sqlError) 
      {
          Response.Write(sqlError.Message);
         transaction.Rollback();
      }
      db.Close();

In the previous code, we  first create an object of SqlTransaction.
After opening the connection, we BeginTransaction as in LINE 1. This makes any SqlCommand executed on the connection, executed within a transaction. So it won't be applied to the database unless CommitTransaction is executed. If we forget this line, nothing happens.

Note that when creating a SqlCommand we used the constructor overload that takes the string, SqlConnection, SqlTransaction. If we forgot to add the transaction to the SqlCommand, an exception occurs as in the following image.

Then we work with ADO commands normally, doing all needed db manipulation inside a try block.

The previous example works fine, with no exceptions occurring, and 3 rows are added when LINE 2 is executed as this commands order the engines to commit all changes done and write in permanently to the db.

Look at this code:
SqlConnection db = new SqlConnection("ConnectionString Here");
      SqlTransaction transaction;

         db.Open();
      transaction = db.BeginTransaction();          LINE 1
      try 
      {
         new SqlCommand("INSERT INTO Books " +
            "(BookName,BookCost) VALUES ('nb1',0);", db, transaction)
            .ExecuteNonQuery();
         new SqlCommand("INSERT INTO Books " +
            "(BookName,BookCost) VALUES ('nb2',10);", db, transaction)
            .ExecuteNonQuery();
         new SqlCommand("INSERT INTO Books " +
            "(BookName,BookCost) VALUES ('nb3','no cost');", db, transaction)
            .ExecuteNonQuery();                LINE 3
         transaction.Commit();                 LINE 2
      } 
      catch (SqlException sqlError) 
      {
          Response.Write(sqlError.Message);
         transaction.Rollback();               LINE 4
      }
      db.Close();

Now, in LINE 3 an exception will occur due to incorrect format, since BookCost is a decimal and provided value is a string 'no cost'.

When executing this, we get an exception and the catch block is executed printing a message to the page.

Cannot convert a char value to money. The char value has incorrect syntax.

then the transaction is rolled back as in LINE 4 and no changes happen to the db.

Behavior of the transaction depends on the isolation level.  The default value is READ COMMITTED. It specifies that statements cannot read data that has been modified but not committed by other transactions. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data.


LINQ to SQL supports  distinct transaction models.
When SubmitChanges is called, if the Transaction property is set to a (IDbTransaction) transaction, the SubmitChanges call is executed in the context of the same transaction.

It is your responsibility to commit or rollback the transaction after successful execution of the transaction. The connection corresponding to the transaction must match the connection used for constructing the DataContext. An exception is thrown if a different connection is used.

Thanks,

Nerdy Geeky J!

References:
http://www.codeproject.com/Articles/10223/Using-Transactions-in-ADO-NET
http://technet.microsoft.com/en-us/library/ms173763.aspx