Database transactions are used to control data commitment to databases. For example, in standard account procedures, it is necessary to debit one account and credit another at the same time. Since computers break down on occasion (power outages, network outages, and so on) there is the potential for one record to be updated or added, but not the other. To avoid these situations, transactions are used. Transactions in ADO.NET are, just as in ADO, handled at the database level: your database must support transactions.

There are three basic commands for transactions: BeginTransaction, Commit, and Rollback. BeginTransaction marks the beginning of a transaction. Anything that happens between the BeginTransaction and the next command (either Rollback or Commit) is considered part of the transaction. The following code example demonstrates using transactions.

 Code Example:

    SqlConnection myConnection = new SqlConnection("server=(local)\\SQLExpress;Integrated Security=SSPI;database=northwind");
    SqlCommand myCommand = new SqlCommand();
    SqlTransaction myTrans;

    try
    {    
    // Open the connection.
    myConnection.Open();
    
    // Assign the connection property.
    myCommand.Connection  = myConnection;
    
    // Begin the transaction.
    myTrans = myConnection.BeginTransaction();
    
    // Assign transaction object for a pending local transaction
    myCommand.Transaction = myTrans;
    
      // Restore database to near its original condition so sample will work correctly.
      myCommand.CommandText = "DELETE FROM Region WHERE (RegionID = 100) OR (RegionID = 101)";
      myCommand.ExecuteNonQuery();
    
      // Insert the first record.
      myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'MidWestern')";
      myCommand.ExecuteNonQuery();
    
      // Insert the second record.
      myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'MidEastern')";
      myCommand.ExecuteNonQuery();
    
      myTrans.Commit();
      Console.WriteLine("Both Records are written to the database!");
    }
    catch(Exception e)
    {
      myTrans.Rollback();
      Console.WriteLine(e.ToString());
      Console.WriteLine("Neither record is written to the database!");
    }
    finally
    {
      myConnection.Close();
    }
    

This code has one major bug in it, namely that the connection is opened before the try block. Thus, if an exception is thrown (for example, by the call to BeginTransaction()), you will not correctly close the connection.

Secondly, in ADO.NET 2 you should also consider using the TransactionScope type to facilitate using transactions.

Thanks for the Notification I will correct it