How to use BiginTrance,commit and rollback in c#

[bhagawatshinde]

Hi Guys,
How to use bigin trance, commit and rollback in c#, suppose i have inserted values in two tables while inserting i am getting error in table 2 but table 1 has inserted values successfully so in catch how i can rollback changes.....after successful insertion i want to commit the changes.

Thanks in advance......

[thines01]

The transaction will be bound to the connection and passed to each command.
Issue .Commit() when you're ready and your commands have succeeded.
You can set up a reference to the connection and to the transaction, so if the even fails, you can rollback in the Exception handler.

using System;
using System.Data.SqlClient;

namespace DW_413277_CS_CON
{
   class Program
   {
      static void Main(string[] args)
      {
         SqlConnection pConnection = null;
         SqlTransaction pTrans = null;

         try
         {
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder("Connect info here");
            using (SqlConnection conn = new SqlConnection(csb.ToString()))
            {
               conn.Open();
               pConnection = conn;
               using (SqlTransaction trans = conn.BeginTransaction())
               {
                  pTrans = trans;
                  //pass the transaction to the command
                  SqlCommand cmd = new SqlCommand("SQL TEXT HERE", conn, trans);
                  cmd.ExecuteNonQuery();

                  //pass the transaction to the next command
                  cmd = new SqlCommand("New SQL TEXT HERE", conn, trans);
                  cmd.ExecuteNonQuery();

                  trans.Commit(); // commit takes place here
               }
               //
               conn.Close();
            }
         }
         catch (Exception exc)
         {
            Console.WriteLine(exc.Message);

            if (null != pTrans)
            {
               Console.WriteLine("Rolling back...");
               pTrans.Rollback();
            }

            if (null != pConnection)
            {
               pConnection.Close();
            }
         }
      }
   }
}

[bhagawatshinde]

Hi thines01, Thanks really wonderful explaination...can u tell me what is the exact use of using () in c#

[thines01]

It encapsulates the use into a particular scope.
If the outer part fails, it skips all of the code on the inside.
So, if the connection cannot be made, the code on the inside of the braces is not executed.
It is usually wrapped around things that are derived from IDisposable.

Any class instance defined in the parameter list of a 'using' keyword has its Dispose() member called on exit of its following code block.

[bhagawatshinde]

Once again thanks..... thines01, u r gr8.