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=; 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. |