[c#] Transactions in .net

What are the best practices to do transactions in C# .Net 2.0. What are the classes that should be used? What are the pitfalls to look out for etc. All that commit and rollback stuff. I'm just starting a project where I might need to do some transactions while inserting data into the DB. Any responses or links for even basic stuff about transactions are welcome.

protected void Button1_Click(object sender, EventArgs e)
   {


       using (SqlConnection connection1 = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database.mdf;Integrated Security=True;User Instance=True"))
       {
           connection1.Open();

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

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

           try
           {
               // Execute two separate commands.
               command.CommandText =
                "insert into [doctor](drname,drspecialization,drday) values ('a','b','c')";
               command.ExecuteNonQuery();
               command.CommandText =
                "insert into [doctor](drname,drspecialization,drday) values ('x','y','z')";
               command.ExecuteNonQuery();

               // Commit the transaction.
               sqlTran.Commit();
               Label3.Text = "Both records were written to database.";
           }
           catch (Exception ex)
           {
               // Handle the exception if the transaction fails to commit.
               Label4.Text = ex.Message;


               try
               {
                   // Attempt to roll back the transaction.
                   sqlTran.Rollback();
               }
               catch (Exception exRollback)
               {
                   // Throws an InvalidOperationException if the connection 
                   // is closed or the transaction has already been rolled 
                   // back on the server.
                   Label5.Text = exRollback.Message;

               }
           }
       }


   }

You could also wrap the transaction up into it's own stored procedure and handle it that way instead of doing transactions in C# itself.

if you just need it for db-related stuff, some OR Mappers (e.g. NHibernate) support transactinos out of the box per default.

You could also wrap the transaction up into it's own stored procedure and handle it that way instead of doing transactions in C# itself.

if you just need it for db-related stuff, some OR Mappers (e.g. NHibernate) support transactinos out of the box per default.

protected void Button1_Click(object sender, EventArgs e)
   {


       using (SqlConnection connection1 = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database.mdf;Integrated Security=True;User Instance=True"))
       {
           connection1.Open();

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

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

           try
           {
               // Execute two separate commands.
               command.CommandText =
                "insert into [doctor](drname,drspecialization,drday) values ('a','b','c')";
               command.ExecuteNonQuery();
               command.CommandText =
                "insert into [doctor](drname,drspecialization,drday) values ('x','y','z')";
               command.ExecuteNonQuery();

               // Commit the transaction.
               sqlTran.Commit();
               Label3.Text = "Both records were written to database.";
           }
           catch (Exception ex)
           {
               // Handle the exception if the transaction fails to commit.
               Label4.Text = ex.Message;


               try
               {
                   // Attempt to roll back the transaction.
                   sqlTran.Rollback();
               }
               catch (Exception exRollback)
               {
                   // Throws an InvalidOperationException if the connection 
                   // is closed or the transaction has already been rolled 
                   // back on the server.
                   Label5.Text = exRollback.Message;

               }
           }
       }


   }

if you just need it for db-related stuff, some OR Mappers (e.g. NHibernate) support transactinos out of the box per default.

It also depends on what you need. For basic SQL transactions you could try doing TSQL transactions by using BEGIN TRANS and COMMIT TRANS in your code. That is the easiest way but it does have complexity and you have to be careful to commit properly (and rollback).

I would use something like

SQLTransaction trans = null;
using(trans = new SqlTransaction)
{
    ...
    Do SQL stuff here passing my trans into my various SQL executers
    ...
    trans.Commit  // May not be quite right
}

Any failure will pop you right out of the using and the transaction will always commit or rollback (depending on what you tell it to do). The biggest problem we faced was making sure it always committed. The using ensures the scope of the transaction is limited.

if you just need it for db-related stuff, some OR Mappers (e.g. NHibernate) support transactinos out of the box per default.

It also depends on what you need. For basic SQL transactions you could try doing TSQL transactions by using BEGIN TRANS and COMMIT TRANS in your code. That is the easiest way but it does have complexity and you have to be careful to commit properly (and rollback).

I would use something like

SQLTransaction trans = null;
using(trans = new SqlTransaction)
{
    ...
    Do SQL stuff here passing my trans into my various SQL executers
    ...
    trans.Commit  // May not be quite right
}

Any failure will pop you right out of the using and the transaction will always commit or rollback (depending on what you tell it to do). The biggest problem we faced was making sure it always committed. The using ensures the scope of the transaction is limited.

You could also wrap the transaction up into it's own stored procedure and handle it that way instead of doing transactions in C# itself.