Transactions in ADO.Net ( TransactionScope Class )

A transaction is a logical unit of a work. There are few ways in implementing transactions.  Either you can implement it in your T-SQL Statements or inside of the program logic. There are two ways in implementing in the .net programming code you can use DbTransaction Object or TransactionScope.

I would implement here TransactionScope class.

It creates a standard transaction called a “local lightweight transaction” that is automatically promoted to a full-fledged distributed transaction if required. You can use even multiple connection objects if you have to update tables in two databases.

First have to import System.Transactions namespace.

        public void CreateTransactionScope()

        {

            SqlCommand ObjCmd = new SqlCommand();

            try

            {

                  // TransactionScope object defined here

                using (TransactionScope Scope = new TransactionScope()) 
                {

                    // _ConnectionString is the Class globally defined string value for the
                    database connection string

                    using (SqlConnection Sqlcon = new SqlConnection(_ConnectionString))

                    {

                        Sqlcon.Open(); // Opening the connection

                        // Assign Connection Object to SQLCommand Object

                        ObjCmd.Connection = Sqlcon;

                        //SQL Statement  to execute

                        ObjCmd.CommandText = "INSERT INTO TBL_EMOPLOYEES VALUES('"+ "001"
                                       +"', '"+ "Pathum Tiranga" +"','"+ "01/01/2011" +"')";

                        ObjCmd.CommandType = CommandType.Text;   

                        //Execution happen here

                        ObjCmd.ExecuteNonQuery();

                    }

                    /* At the ending of the Scope of the Transaction Scope commiting the
                      transaction was done 

                     If couldn't reach this statement all above datachanges happened to one
                      or more tables will be RolledBack

                     */

                    Scope.Complete();

                }

            }

            /* If an exception is thrown within the TransactionScope object’s using block,

               the transaction aborts, and all work is rolled back. */

            catch (Exception ex)

            {

                throw ex;

            }

        }

When you create a TransactionScope object in a using block, the TransactionScope object assigns a transaction to this created connection, so you don’t need to add anything to your code to enlist this connection into the transaction.