Member Avatar for 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......

  • 2 Contributors
  • 4 Replies
  • 529 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by bhagawatshinde

Dani AI

Generated

Asking for a single atomic change across two tables is exactly what transactions are for. already showed the explicit SqlTransaction pattern (bind the transaction to the connection and pass it to each command). Two additional, practical points that help in real apps:

  • Use TransactionScope for a simpler, ambient transaction model. It automatically rolls back unless you call Complete(); no explicit Rollback() is required. This is handy at the business-layer level.
using (var scope = new System.Transactions.TransactionScope())
{
    // open connection(s), execute commands
    // when everything succeeds:
    scope.Complete(); // otherwise dispose = rollback
}
  • Use savepoints when you want to undo only part of a transaction (SQL Server supports SqlTransaction.Save(name) and Rollback(name)). That lets you undo the second-table insert without losing the first, then continue or commit.
trans.Save("BeforeTable2");
// do table2 insert
// on failure:
trans.Rollback("BeforeTable2");
// later: trans.Commit();

Practical tips and gotchas: always parameterize commands and keep transactions short to avoid locks and timeouts. Wrap Rollback() itself in a try/catch because rollback can fail if the connection is broken — if rollback fails, log the failure and assume the DB will clean up the orphaned transaction. In ASP.NET avoid holding open transactions across requests; open late, close early. For multi-database or resource scenarios, TransactionScope can escalate to a distributed transaction (MSDTC) — test that scenario in your environment.

About using(...): it guarantees Dispose() runs even on exceptions (equivalent to try/finally calling Dispose). That’s why using is commonly used for connections/commands/transactions — it ensures cleanup without manual code.

Recommended Answers

All 4 Replies

Member Avatar for 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();
            }
         }
      }
   }
}
Member Avatar for bhagawatshinde

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

Member Avatar for 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.

Edited by thines01 because: n/a
Member Avatar for bhagawatshinde

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.