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......

Recommended Answers

All 4 Replies

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();
            }
         }
      }
   }
}

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

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.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.