009 ADO.Net Transaction Processing

ADO.NET transaction processing

                    ADO.NET  allow us a group of command  to be  executed in against a single connection. Consider the situation when  all command , need to be executed successfully or ,if  any  error occure during any command execution, all transaction need to be rolled back . Not a single  record mismatch can be happen. 


                               ADO.NET transaction processing is very useful for real life use of software. For example Banking software, Ticket booking software etc.For Ticket booking software , first step to make payment , 2nd step  to book a ticket. There are two stage to book a ticket. Ticket booking will be complete if two step completed successfully. Payment is made but ticket is not booked make a System Wrong. Transaction processing allowed only commit data when both successful. 

ADO.NET transaction processing follow ACID property. 

Atomic : The whole transaction is considered as a single work , atomic unit of processing,either  perform or not perform at all. 

Consistency : Data relation ship is maintained correctly. No data can be insert , update, delete breaking the data relationship rule. 

Isolation : Transaction should not make update visible two other client , until committed. 

Durability : Once a change is made, it is permanent. 


Step of do a transaction processing

1) Create a instance of a connection .
2) Open  connection .
3) Create instance of a transaction .
4) Create a command and execute against transaction .
5) Repeat the step (4) if there is more command .
6) If all command execution success, then commit transaction.
7) If any command get error, rollback the transaction.
8)Close connection.

Example 1 (A simple Transaction Processing)

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.SqlClient;

namespace ConsoleApplication1

{

        class Program

        {

                 static void Main(string[] args)

                 {

                          string conString = "Your Conncetion String ";

                          SqlConnection con = new SqlConnection(conString);

                          SqlTransaction trans;

                          con.Open();

                          trans = con.BeginTransaction();

                          try

                              {

                                      new SqlCommand("INSERT INTO tbl_student(Name,Roll,Class) " +

                                                                 "VALUES('Student22',123,2);", con, trans)

                                    .ExecuteNonQuery();

                                     trans.Commit();

                              }

                             catch (SqlException sqlError)

                             {

                                       trans.Rollback();

                             }

                           con.Close();

                 }

         }

}

Different kind of isolation level can be applicable during the transaction processing. Isolation level is how  database current changes handheld. 

ReadUncommitted : In this kind of associate level, uncommitted data can be read, this is termed as  dirty read. 

ReadCommitted : This kind of isolation level,  prevent reading data before committed. But does not prevent  record from insert, update, delete  from other clients. This is the default isolation of a ADO.NET transaction processing. 

Chaos : It is similar like read uncommitted. But it consider the pending transaction while writing the in the  database.It make sure that different transaction is not overwritten.

RepeatableRead :  Lock read till the transaction is completed . 

Serializable : Lock entire DatSet being read till the transaction is completed.

Transaction processing is important in the application where  multiple client may try to change the database at the same time. With the growing of client , the situation will be worse and worse .

Web application work on internet, it is not guaranteed that internet will be always available, unavailability of internet may occur error during a transaction  also. 

There is a chance that during on transaction a client close this application in between. 

There are several such reason  where an application experience exception during data processing. Transaction processing ensure  to overcome  this kind of problem. 

                             Sometimes an application is distributed, multiple server and multiple database, database need to be synchronized among them. For a large real time application , distributed application is necessary. To handle transaction processing on this kind of system,  
System.EnterpriseServices  namespace is used.

Example 2(A Transaction Processing with multiple command)

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.SqlClient;

namespace ConsoleApplication1

{

      class Program

     {

           static void Main(string[] args)

             {

                      string conString = "You Connection String";

                      SqlConnection con = new SqlConnection(conString);

                      SqlTransaction trans;

                      con.Open();

                      trans = con.BeginTransaction();

                      try

                     {

                          new SqlCommand("INSERT INTO tbl_student(Name,Roll,Class) " +

                          "VALUES('Student15',123,2);", con, trans)

                        .ExecuteNonQuery();

                        new SqlCommand("INSERT INTO tbl_student(Name,Roll,Class) " +

                       "VALUES('Student16',123,2);", con, trans)

                     .ExecuteNonQuery();

                     new SqlCommand("INSERT INTO tbl_student(Name,Roll,Class) " +

                     "VALUES('Student16',123,2);", con, trans)

                    .ExecuteNonQuery();

                    new SqlCommand("INSERT INTO tbl_student(Name,Roll,Class) " +

                   "VALUES('Student17',123,2);", con, trans)

                   .ExecuteNonQuery();

                    trans.Commit();

                   Console.WriteLine("All Data Inserted and Commiter Sucessfully");

                }

               catch (SqlException sqlError)

               {

                    trans.Rollback();

                    Console.WriteLine("Error Occured !! Transaction have been roll backed");

               }

              con.Close();

             }

       }

}

Output 

All Data Inserted and Commiter Sucessfully