Database Transactions | Special Edition Using ASP.Net

Database transactions are used to control data being committed to a database. This is typically done to safeguard against invalid data (which can be caused by many factors), power failures, or system crashes. As in ADO, transactions in ADO.NET are handled at the database level so the database you are using must support transactions.

Begin , Commit , and Rollback methods are now a part of the Transaction object. Begin marks the beginning of the transaction and includes anything until the next command ”either Commit or Rollback . The code sample in Listings 9.33 and 9.34 tries to insert two records into the TitleAuthors table. If the page is executed more than once, the rollback behavior will be displayed.

Listing 9.33 Using Transactions from VB

 <%@ Import Namespace="System.Data" %>  <%@ Import Namespace="System.Data.SQL"Client %>  <html>  <body>  <script language="VB" runat="server">  Sub Cleanup(myConnection as SQLConnection )      Dim myCommand as SQLCommand      myConnection.Open()      myCommand = new SQLCommand("DELETE FROM TitleAuthor " + _        "WHERE au_id = 'BU1111' AND (title_id = 'TC7777') OR _        (title_id = '')", myConnection)      myCommand.ExecuteNonQuery()      myConnection.Close()  End Sub  Sub Page_Load(Src as Object, E as EventArgs)      Dim myConnection as new SQLConnection("server=localhost;uid=sa; pwd=;database=pubs")    Dim myStr1 as new String("Insert into TitleAuthor values " + _       "('172-32-1176','BU1111',1,100)")    Dim myStr2 as new String("Insert into TitleAuthor values " + _       "('172-32-1176','TC7777',1,100)")      Dim myCommand as new SQLCommand(myStr1, myConnection)  Dim myTransaction as new SQLTransation()      Cleanup(myConnection)   ' Delete the added rows from the database so the example will work correctly.      try        myConnection.Open()        myTransaction=myConnection.BeginTransaction()        myCommand.Transaction=myTransaction        myCommand.ExecuteNonQuery()        myCommand.CommandText = myStr2        myCommand.ExecuteNonQuery()        myTransaction.Commit()        Response.Write("Both Records written to database")      Catch myException as Exception        If NOT myTransaction IS NOTHING THEN           myTransaction.Rollback()        END IF        Response.Write(myException.ToString())        Response.Write("No records written to database.<br>If you executed        this page more than once it may exhibit the Rollback behavior.")       Finally        myConnection.Close()      End Try  End Sub  </script>  </body>  </html>

Listing 9.34 Using Transactions from C#

 <%@ Import Namespace="System.Data" %>  <%@ Import Namespace="System.Data.SqlClient" %>  <html>  <body>  <script language="C#" runat="server">  void Cleanup(SQLConnection myConnection)  {      myConnection.Open();      SQLCommand myCommand = new       SQLCommand("DELETE FROM TitleAuthor WHERE " +       au_id = 'BU1111' AND (title_id = 'TC7777') OR (title_id = '')",        myConnection);      myCommand.ExecuteNonQuery();      myConnection.Close();  }  void Page_Load(Object Sender, E as EventArgs)  {      SQLConnection myConnection = new SQLConnection ("server=localhost;uid=sa;pwd=;  database=pubs");   String myStr1 =    new String("Insert into TitleAuthor values ('172-32-1176','BU1111',1,100)");   String myStr2 =    new String("Insert into TitleAuthor values ('172-32-1176','TC7777',1,100)");      SQLCommand myCommand = new SQLCommand(myStr1, myConnection);      SQLTransaction myTransaction=null;      Cleanup(myConnection)  // Delete the added rows from the database so the example will work correctly.      try{        myConnection.Open();        myTransaction=myConnection.BeginTransaction();        myCommand.Transaction=myTransaction;        myCommand.ExecuteNonQuery();        myCommand.CommandText = myStr2;        myCommand.ExecuteNonQuery();        myConnection.CommitTransaction();         Response.Write("Both Records written to database");      }      catch(Exception myException){        if(myTransaction!=null)            myTransaction.Rollback();        Response.Write(myException.ToString());        Response.Write("No records written to database.<br>If you executed " +          "this page more than once it may exhibit the Rollback behavior.");      }      finally{        myConnection.Close();      }  }  </script>  </body>  </html>

As for the DataSet object, it contains a commit model ( AcceptChanges and RejectChanges ) but does not affect the database because it is a cached copy. To update the changes to the database, the Update method from the DataAdapter needs to be called.

You also might notice that the structure of the transaction is similar to ADO. That's because ADO.NET uses the same underlying OLEDB model as in ADO, so if you are used to using transactions in ADO, you can still use them in ADO.NET.