Begin-Commit Transaction in .NET

Coding C

Hi, i am facing a problem plz if someone can help me. in visual basic i normally used to do con.begintrans any no. of execute commands con.committrans on error i can do con.rollbacktrans is there something in .net which relates to this technique. thanks.:) Nitin...

coolestCoder

Hi Nitin, Please see the following link for the solution http://windowssdk.msdn.microsoft.com/en-us/library/2k2hy99x.aspx[^]

"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder

Anant Y. Kulkarni

Guffa

Here is an example from MSDN:

using (SqlConnection connection = new SqlConnection(connectString))
{
connection.Open();

// Start a local transaction.
SqlTransaction sqlTran = connection.BeginTransaction();

// Enlist the command in the current transaction.
SqlCommand command = connection.CreateCommand();
command.Transaction = sqlTran;

try
{
    command.CommandText =
      "INSERT INTO Production.ScrapReason(Name) VALUES('Wrong size')";
    command.ExecuteNonQuery();
    command.CommandText =
      "INSERT INTO Production.ScrapReason(Name) VALUES('Wrong color')";
    command.ExecuteNonQuery();
    sqlTran.Commit();
    Console.WriteLine("Both records were written to database.");
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
    Console.WriteLine("Neither record was written to database.");
    sqlTran.Rollback();
}

}

--- b { font-weight: normal; }

Guffa

Spooky. It's the second time today that I post the exact same answer at the same time as someone else. :~

--- b { font-weight: normal; }

Coding C

Hi Guffa! thanks for reply. its very usefull. thank You.:) Nitin...

Dustin Metzgar

The link and code provided in earlier responses do work well, but they are old ways of performing transactions. .Net 2.0 has a thing called TransactionScope in the System.Transactions namespace.

using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection connection = new SqlConnection(connectString))
{
connection.Open();

    SqlCommand command = connection.CreateCommand();
    try
    {
        command.CommandText =
          "INSERT INTO Production.ScrapReason(Name) VALUES('Wrong size')";
        command.ExecuteNonQuery();
        command.CommandText =
          "INSERT INTO Production.ScrapReason(Name) VALUES('Wrong color')";
        command.ExecuteNonQuery();
        scope.Complete();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
        Console.WriteLine("Neither record was written to database.");
    }
}

}

TransactionScope has the ability to nest transactions and will also automatically upgrade a transaction into a distributed transaction if you start talking to two or more different databases. And when .Net 3 and WCF come along, WCF services will also be able to participate in the transaction.

Logifusion[^] If not entertaining, write your Congressman.

Coding C

Hi Dustin Metzgar , thanks for better way. thanq you very much.:) Regards. Nitin...