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.
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.
static void Main(string args)
SqlConnection pConnection = null;
SqlTransaction pTrans = null;
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder("Connect info here");
using (SqlConnection conn = new SqlConnection(csb.ToString()))
pConnection = conn;
using (SqlTransaction trans = conn.BeginTransaction())
pTrans = trans;
//pass the transaction to the command
SqlCommand cmd = new SqlCommand("SQL TEXT HERE", conn, trans);
//pass the transaction to the next command
cmd = new SqlCommand("New SQL TEXT HERE", conn, trans);
trans.Commit(); // commit takes place here
catch (Exception exc)
if (null != pTrans)
if (null != pConnection)
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.