Home   |   QuickStart Welcome   |   ASP.NET   |   Web Services   |   How Do I...?   
  |   I want my samples in...      

How Do I...? Common Tasks QuickStart Tutorial

ADO.NET: Use Database Transactions



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.
		

    SqlConnection myConnection = new SqlConnection("server=(local)\\SQLExpress;Integrated Security=SSPI;database=northwind");
    SqlCommand myCommand = new SqlCommand();
    SqlTransaction myTrans;
    
    // 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;
    
    try
    {
      // 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();
    }
    
C#


The example shows that if either insert fails, both are rolled back to their original states. If each succeeds, then the transaction is committed.

As in classic ADO, you can control transactions through the connection object. In fact, when you use the OleDbConnection, it uses the same underlying OLE DB transaction model. Thus, if you were able to commit transactions against your database with ADO classic, you can still commit them with ADO.NET. The following code example demonstrates using the SqlConnection and SqlCommand to insert two records into "Region" table. If either fails, then the changes are rolled back.

The DataSet object also has a commit model (AcceptChanges,RejectChanges) but it does not affect the database. The commit model is for the cache of data in the DataSet alone. To submit the Data from the DataSet into the database, use the Update method on the SqlDataAdapter.
VB DBTransactionsWithACommand.aspx
Run Sample View Source





Microsoft .NET Framework SDK QuickStart Tutorials Version 2.0
Copyright � 2004 Microsoft Corporation. All rights reserved.