Understanding New Objects in the ADO.NET Object Model

In this section, you will learn about some additional objects that are new in the ADO.NET object model. These include transactions, exceptions, and errors.

Transactions

Sometimes your application must coordinate two separate database operations—for example, you might want to delete an entry from one table and add it to a different one. In this case, you want to be sure that if an error occurs during either operation, both operations are cancelled. It would be a problem for most applications if the record was deleted from the first table and then because an error occurred it did not get added to the second one. The information would be lost. You want to be assured that if an error occurs during any part of your processing, all the operations that are running within the same transaction are cancelled, or rolled back. If all operations are able to complete successfully, then you want to commit those changes to the database permanently.

If you were trying to do this yourself, you would have to write a lot of code to buffer the temporary results and perhaps undo your changes. Fortunately, you do not have to worry about this. Several options are available to .NET Framework programmers for transaction management.

In Chapter 2, “Creating and Managing Serviced Components,” we discussed the capability of .NET Enterprise Services to manage distributed transactions. These are useful if your transactions involve multiple databases or database servers. If you need to handle only local transactions, such as multiple operations on different tables in the same database, then you can use the ADO.NET Transaction class to handle this for you. (A third option is to use the transaction control statements in Transact-SQL when you are writing stored procedures.)

In earlier versions of ADO, transactions were managed by using methods of the Connection object. This is not the case in ADO.NET. There is now a Transaction class. The SqlTransaction object or the OleDbTransaction object is first created by calling the Connection.BeginTransaction method. All commands that participate in the transaction must use the same connection. A common way of using transactions is to place a call to the Transaction.Commit method at the end of the procedure, following all of the database operations, and to place a call to the Transaction.Rollback method in your error handler. If a runtime error occurs, the entire transaction will be rolled back. If all database operations complete without runtime errors, then the transaction will be committed and the changes will be made permanent in the database. Table 5.8 lists the properties and methods of the Transaction object, and Table 5.9 lists the enumerated values for the IsolationLevel property.

Table 5.8: Properties and Methods of SqlTransaction and OleDbTransaction

Public Properties	Description
Connection	Provides a reference to the Connection object associated with the transaction.
IsolationLevel	Specifies the isolation level for this transaction. Isolation levels are listed in Table 5.9.
Public Methods	Description
Commit	Commits the database transaction.
Dispose	Releases the unmanaged resources used by the Transaction object and optionally releases the managed resources.
Rollback	Rolls back (cancels) a transaction from a pending state.
SqlTransaction only	Description
Save	Creates a named savepoint that can be used to roll back a portion of the transaction.

Table 5.9: Enumeration Values of the IsolationLevel Property

Level	Description
Serializable	The greatest level of isolation, preventing other users from updating or inserting rows into the resultset until the transaction is complete.
RepeatableRead	Locks are placed on all data that is used in a query, preventing other users from updating the data. Prevents nonrepeatable reads but phantom rows are still possible.
ReadCommitted	Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data.
ReadUncommitted	A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored.
Chaos	The pending changes from more highly isolated transactions cannot be overwritten.
Unspecified	A different isolation level than the one specified is being used, but the level cannot be determined.

The Save method is available only for the SqlTransaction object. This takes advantage of a capability of Microsoft SQL Server to roll back to a specific point in a complex transaction. The IsolationLevel property can be set to request that the database server place a high level of isolation, or protection, against other users changing (or even reading) the same data that your transaction is working with, until your transaction completes. The interaction between your code and the database server’s internal mechanisms for determining how locks are held on the data can be quite complex and can affect your application’s performance. You should test this carefully in each individual situation to determine the optimal setting.

Listing 5.9 shows a procedure that uses ADO.NET transactions along with error-handling code. This example extends the code from Exercise 5.3.

Listing 5.9: ADO.NET Transactions

Public Sub UpdateTwoTables()    Dim myConn As SqlConnection = New SqlConnection()    myConn.ConnectionString = _      "Data Source=localhost; Initial " & _      "Catalog=pubs; Integrated Security=SSPI; "    Dim myTrans As SqlTransaction    Try       myConn.Open()       myTrans = myConn.BeginTransaction()       Dim myProc As SqlCommand = _          New SqlCommand("InsertNewStore", myConn)       myProc.CommandType = CommandType.StoredProcedure       myProc.Transaction = myTrans       myProc.Parameters.Add("@storeid", _          SqlDbType.Char, 4).Value = txtID.Text       myProc.Parameters.Add("@storename", _          SqlDbType.VarChar, 40).Value = txtName.Text       myProc.Parameters.Add("@storeaddress", _          SqlDbType.VarChar, 40).Value = txtAddress.Text       myProc.Parameters.Add("@city", _          SqlDbType.VarChar, 20).Value = txtCity.Text       myProc.Parameters.Add("@state", _          SqlDbType.Char, 2).Value = txtState.Text       myProc.Parameters.Add("@zip", _          SqlDbType.Char, 5).Value = txtZip.Text       myProc.ExecuteNonQuery()       Dim mySecondProc As SqlCommand = _          New SqlCommand("InsertStoreSales", myConn)       mySecondProc.CommandType = CommandType.StoredProcedure       mySecondProc.Transaction = myTrans       mySecondProc.Parameters.Add("@storeid", _          SqlDbType.Char, 4).Value = txtID.Text       mySecondProc.Parameters.Add("@ordernumber", _          SqlDbType.VarChar, 20).Value = txtNum.Text       mySecondProc.Parameters.Add("@orderdate", _          SqlDbType.DateTime).Value = txtDate.Text       mySecondProc.Parameters.Add("@qty",          SqlDbType.Int).Value = txtQty.Text       mySecondProc.Parameters.Add("@payment", _          SqlDbType.VarChar, 12).Value = txtPay.Text       mySecondProc.Parameters.Add("@titleid", _          SqlDbType.VarChar, 6).Value = txtTitle.Text       mySecondProc.ExecuteNonQuery()       myTrans.Commit()    Catch e As Exception       myTrans.Rollback()       'additional error handling here    Finally       myConn.Close()    End Try End Sub

In this example, we have two stored procedures, the InsertNewStore procedure from Exercise 5.3 and a new one called InsertStoreSales for inserting data into the Sales table. We need to make sure that we can successfully complete the first operation, adding the new store, before we try to insert sales information for that store ID. The statements that show the use of the Transaction object and the error-handling code are shown in bold. Notice that we start with the SqlConnection.BeginTransaction method. Then we must set the Transaction property to reference the newly created SqlTransaction object. After the second stored procedure call is the SqlTransaction.Commit method call. If both stored procedures are executed correctly, we are ready to make our changes permanent. The Catch block of the error handler contains the call to SqlTransaction.Rollback. If a runtime error occurred, neither statement’s results would be written to the database. In the Finally block of the error handler, we can close the connection. Code that is in the Finally block will execute whether an error occurred or not, so we know for sure that our connection to the database will always be terminated at the end of the procedure, no matter what the outcome.

Understanding the Exception Class and the Error Class

If an error occurs when you are executing a statement against the database, the database server will send the error information to the .NET data provider. This error information might consist of one or more messages. The .NET data provider will raise an exception that can be caught by error-handling code in your procedures.

The Exception object has an Errors collection. By iterating through it, you can examine all the messages that the database server has sent. For the Exception object itself, and each Error object in the Errors collection, you can examine several properties that give you information about the problem that occurred at the database server. Table 5.10 lists the properties for the Exception and Error objects.

Table 5.10: Properties of SqlException, SqlError, OleDbException, and OleDbError

SqlException and SqlError	Description
Class	Gets the severity level of the error returned from the SQL Server .NET data provider.
LineNumber	Gets the line number within the Transact-SQL command batch or stored procedure that generated the error.
Message	Gets the text describing the error.
Number	Gets a number that identifies the type of error.
Procedure	Gets the name of the stored procedure or Remote Procedure Call (RPC) that generated the error.
Server	Gets the name of the computer running an instance of SQL Server that generated the error.
Source	Gets the name of the provider that generated the error.
State	Gets a numeric error code from SQL Server that represents an error, warning, or no data found message. For more information, see SQL Server Books Online.
OleDbException	Description
ErrorCode	Gets the HRESULT of the error.
Message	Gets the text describing the error.
Source	Gets the name of the OLE DB provider that generated the error.
OleDbError	Description
Message	Gets a short description of the error.
NativeError	Gets the database-specific error information.
Source	Gets the name of the provider that generated the error.
SQLState	Gets the five-character error code following the ANSI SQL standard for the database.
SqlException and OleDbException Only	Description
Errors	Gets a collection of one or more Error objects that give detailed information about exceptions generated by the.NET data provider.

There is a significant difference between the properties that are available for the SqlClient data provider and those available for the OleDb data provider. In Table 5.10, notice that the OleDbException object and OleDbError object have different sets of properties. However, the SqlException object and SqlError object have identical properties (except for the SqlException.Errors collection). If you ask for the properties of the SqlException object, you will see the same values as the properties of the first SqlError in its Errors collection.

Exercise 5.5 adds error-handling code to the StoredProcedureExamples project that you created in Exercise 5.3.

Exercise 5.5: Adding Error Handling

1. Open the Visual Studio .NET project called StoredProcedureExamples that you created in Exercise 5.3.

2. Add an error handler to the Page_Load event procedure in AddStore.aspx.vb. Remove the If Then Else block at the end of the procedure. Place the instruction to redirect back to the default.aspx page directly after the call to execute the stored procedure. Your code should look like this:

   recsAdded = myProc.ExecuteNonQuery() Response.Redirect("default.aspx")

3. Add a Try statement immediately before the call and open the connection. Add Catch, Finally, and End Try statements at the end of the procedure. Add the code to examine the SqlException and Errors collection in the Catch block. Add the instruction to close the connection in the Finally block. Here is what your code should look like (bold lines indicate code that you need to add):

   Private Sub Page_Load(ByVal sender As System.Object, _    ByVal e As System.EventArgs) Handles MyBase.Load    If Page.IsPostBack Then       Dim recsAdded As Integer       Dim myConn As SqlConnection = New SqlConnection()       Dim myProc As SqlCommand = _          New SqlCommand("InsertNewStore", myConn)       myProc.CommandType = CommandType.StoredProcedure       myConn.ConnectionString = _          "Data Source=localhost; Initial " & _          "Catalog=pubs; Integrated Security=SSPI; "       Try          myConn.Open()          myProc.Parameters.Add("@storeid", _             SqlDbType.Char, 4).Value = txtID.Text          myProc.Parameters.Add("@storename", _             SqlDbType.VarChar, 40).Value = txtName.Text          myProc.Parameters.Add("@storeaddress", _             SqlDbType.VarChar, 40).Value = txtAddress.Text          myProc.Parameters.Add("@city", _             SqlDbType.VarChar, 20).Value = txtCity.Text          myProc.Parameters.Add("@state", _             SqlDbType.Char, 2).Value = txtState.Text          myProc.Parameters.Add("@zip", _             SqlDbType.Char, 5).Value = txtZip.Text          recsAdded = myProc.ExecuteNonQuery()          Response.Redirect("default.aspx")       Catch ex As SqlException          Dim myErrors As SqlErrorCollection = ex.Errors          Response.Write("Class: " & ex.Class & "<BR>")          Response.Write("Error #" & ex.Number & " " & _             ex.Message & _             " on line " & ex.LineNumber & "<BR>")          Response.Write("Error reported by " & _             ex.Source & _             " while connected to " & ex.Server & "<BR>")          Response.Write("Errors collection contains " & _             myErrors.Count & " items:<BR>")          Dim err As SqlError             For Each err In myErrors                Response.Write("Class: " & _                   err.Class & "<BR>")                Response.Write("Error #" & _                   err.Number & " " & err.Message & _                   " on line " & err.LineNumber & "<BR>")                Response.Write("Error reported by " & _                   err.Source & _                   " while connected to " & err.Server & "<BR>")             Next       Finally          myConn.Close()       End Try    End If End Sub

4. Save and test your work. When you enter the data to add a new store, use a store ID number that already exists in the database. This will cause an error because you are not allowed to have duplicate values in the primary key column. The error messages will be written directly to the web page and should look like the following.

   Warning 

   This example is designed to teach you about the error information that you will receive from ADO.NET. In a real production application, you would never display this kind of detailed information to the users of your web pages. Chapter 8, “Testing and Debugging”, will explain how to log error information safely. Error messages that are displayed to users, should general in nature and should not include information such as database table and field names or server names.