Advanced Database Topics

In the following sections, we explore three advanced database topics. You learn how to execute database commands in the context of a transaction. You also learn how to modify the behavior of a database command by specifying a CommandBehavior . Finally, you learn how to retrieve schema information from a database table.

Executing Database Commands in a Transaction

A database transaction is a series of statements that either succeed or fail as a whole. The standard example of a transaction is transferring money between two bank accounts. If one of the following two statements fail, it would be better if both statements fail:

 Update BankAccountA Set Balance = Balance - 9999.99 WHERE Customer='Smith' Update BankAccountB Set Balance = Balance + 9999.99 WHERE Customer='Smith' 

The first statement removes $9,999.99 from the balance of bank account A. The second statement adds this amount to the balance of bank account B.

If the first statement executes, but the second statement fails, the customer is going to be very unhappy. If the first statement fails, but the second statement executes, the bank is going to be very unhappy . To keep both parties happy, it would be better to execute these statements as a transaction.

There are three approaches that you can take to creating transactions. You can create transactions at the database level, at the level of ADO.NET, or at the level of a whole ASP.NET page.

Creating a Database Transaction

Microsoft SQL Server itself supports transactions through the BEGIN TRANSACTION , COMMIT TRANSACTION , and ROLLBACK TRANSACTION statements. For example, the following SQL Stored procedure updates Account A and Account B in a transaction:

 Create Procedure UpdateAccounts As BEGIN TRANSACTION Update AccountA Set Balance = Balance - 999.99 WHERE Customer='Smith' Update AccountB Set Balance = Balance + 999.99 WHERE Customer='Smith' COMMIT TRANSACTION 

If someone unplugs your database server after the first update statement but before the second update statement, the first statement will be automatically rolled back (when the server restarts). In other words, any modifications made by the first statement will be undone. By using transactions in this way, you can prevent your tables from containing inconsistent data.

Creating an ADO.NET Transaction

ADO.NET also supports transactions through the Connection and Transaction classes. You create a new transaction with the BeginTransaction method of the Connection class. You can then associate the transaction with multiple commands with the Transaction property of the Command class.

For example, the page in Listing 9.27 contains two commands that are executed within a single transaction. If either command fails, both commands will fail.

Listing 9.27 SqlTransaction.aspx

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <% Dim objTransaction As SqlTransaction Dim conBank As SqlConnection Dim strUpdateAccountA As String Dim strUpdateAccountB As String Dim cmdUpdateAccountA As SqlCommand Dim cmdUpdateAccountB As SqlCommand ' Initialize objects conBank = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;database=Bank" ) strUpdateAccountA = "Update AccountA Set Balance = Balance - 999.99 WHERE Customer='Smith'" cmdUpdateAccountA = New SqlCommand( strUpdateAccountA, conBank ) strUpdateAccountB = "Update AccountB Set Balance = Balance + 999.99 WHERE Customer='Smith'" cmdUpdateAccountB = New SqlCommand( strUpdateAccountB, conBank ) ' Open connection conBank.Open() ' Retrieve transaction from connection objTransaction = conBank.BeginTransaction ' Assign Transaction to commands cmdUpdateAccountA.Transaction = objTransaction cmdUpdateAccountB.Transaction = objTransaction ' Try executing both commands Try   cmdUpdateAccountA.ExecuteNonQuery()   cmdUpdateAccountB.ExecuteNonQuery()   ' Commit the transaction   objTransaction.Commit   Response.Write( "Transaction Successful!" ) Catch ex As Exception   objTransaction.RollBack   Response.Write( "Transaction Failed!" ) Finally   conBank.Close() End Try %> 

The C# version of this code can be found on the CD-ROM.

Creating an ASP.NET Page Transaction

Finally, you can create a transaction at the level of an ASP.NET page. You can enroll an ASP.NET page in a transaction by adding one of the following page directives to the ASP.NET page:

• Disabled ” Transactions are disabled for the page. This is the default value.

• NotSupported ” Indicates that the page does not execute within a transaction.

• Supported ” If a transaction already exists, the page will execute within the context of the transaction. However, it will not create a new transaction.

• Required ” If a transaction already exists, the page will execute within the context of the transaction. If a transaction does not exist, it will create a new one.

• RequiresNew ” Creates a new transaction for each request.

After you enable transactions for an ASP.NET page, you can use two methods from the ContextUtil class to explicitly commit or roll back a transaction: the SetComplete and SetAbort methods. The page in Listing 9.28 illustrates how to use these methods:

Listing 9.28 ASPTransaction.aspx

 <%@ Transaction="RequiresNew" %> <%@ Import Namespace="System.EnterpriseServices" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <% Dim conBank As SqlConnection Dim strUpdateAccountA As String Dim strUpdateAccountB As String Dim cmdUpdateAccountA As SqlCommand Dim cmdUpdateAccountB As SqlCommand ' Initialize objects conBank = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;database=Bank" ) strUpdateAccountA = "Update AccountA Set Balance = Balance - 999.99 WHERE Customer='Smith'" cmdUpdateAccountA = New SqlCommand( strUpdateAccountA, conBank ) strUpdateAccountB = "Update AccountB Set Balance = Balance + 999.99 WHERE Customer='Smith'" cmdUpdateAccountB = New SqlCommand( strUpdateAccountB, conBank ) ' Open connection conBank.Open() ' Try executing both commands Try   cmdUpdateAccountA.ExecuteNonQuery()   cmdUpdateAccountB.ExecuteNonQuery()   ' Commit the transaction   ContextUtil.SetComplete()   Response.Write( "Transaction Successful!" ) Catch ex As Exception   ContextUtil.SetAbort()   Response.Write( "Transaction Failed!" ) Finally   conBank.Close() End Try %> 

The C# version of this code can be found on the CD-ROM.

In Listing 9.28, if the two SQL Update commands succeed then the SetComplete() method of the ContextUtil class is called. Otherwise, the SetAbort() method is called. To use these methods, you must import the System.EnterpriseServices namespace.

Specifying a Command Behavior

When you call the ExecuteReader() method of the Command object you can pass an optional CommandBehavior parameter. By supplying the CommandBehavior parameter, you can gain greater control over how the ExecuteReader() method retrieves data from a database.

The CommandBehavior enumeration has the following values:

• CloseConnection ” Automatically closes an open database connection after the DataReader is closed.

• KeyInfo ” Retrieves column and primary key with the data. Executes the query with the FOR BROWSE clause.

• SchemaOnly ” Retrieves column and table schema information without retrieving data.

• SequentialAccess ” Enables access to database columns that contain a large amount of information.

• SingleResult ” Optimizes the command to retrieve only a single result.

• SingleRow ” Optimizes the command to retrieve only a single row. If multiple rows are returned, additional rows are discarded.

The CloseConnection command behavior is useful when you want to return a DataReader from a function. For example, the page in Listing 9.29 uses the CloseConnection behavior when returning the contents of the Authors table from the GetAuthors () function (see Figure 9.9).

Listing 9.29 SqlCloseConnection.aspx

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Dim conPubs As SqlConnection Sub Page_Load   Dim dtrAuthors As SqlDataReader   dtrAuthors = GetAuthors()   While dtrAuthors.Read     lblAuthors.Text &= "<li>"     lblAuthors.Text &= dtrAuthors( "au_lname" )   End While   dtrAuthors.Close()   lblAuthors.Text &= "<hr> Connection is:" & conPubs.State.ToString() End Sub Function GetAuthors() As SqlDataReader   Dim cmdAuthors As SqlCommand   conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;database=pubs" )   cmdAuthors = New SqlCommand( "select * from Authors", conPubs )   conPubs.Open()   Return cmdAuthors.ExecuteReader( CommandBehavior.CloseConnection ) End Function </Script> <html> <head><title>SqlCloseConnection.aspx</title></head> <body> <asp:Label   id="lblAuthors"   Runat="Server" /> </body> </html> 

The C# version of this code can be found on the CD-ROM.

Figure 9.9. The CloseConnection CommandBehavior .

By including the CommandBehavior.CloseConnection parameter in the function Return statement, you can automatically close the database connection when the Close() method is called on the SqlDataReader . In Listing 9.29, the state of the database connection is displayed by retrieving the value of the State property of the SqlConnection class.

You also can use the CommandBehavior parameter to improve the performance of your queries. For example, the CommandBehavior.SingleRow value can improve the performance of a query when the query retrieves a single row of data from a database table. A good situation to use this value is when you need to retrieve information on a single title from the Titles database table.

NOTE

Don't confuse the ExecuteScalar() method with the CommandBehavior.SingleRow value. ExecuteScalar() returns a single result (the value of the first column from the first row). The CommandBehavior.SingleRow value indicates that a complete row should be returned (the value of all columns from the first row returned).

Retrieving Table Schema Information

If you need to retrieve information about the columns contained in a database table, then you can use the GetSchemaTable () method of the DataReader class. The GetSchemaTable () method returns an instance of the DataTable class.

NOTE

The DataTable class is discussed in detail in Chapter 12, "Working with DataSets."

Typically, you'll use the GetSchemaTable() method in conjunction with the CommandBehavior.KeyInfo or CommandBehavior.SchemaOnly values from the CommandBehavior enumeration (see the previous section).

For example, the page in Listing 9.30 retrieves information on all the columns in the Authors table (see Figure 9.10).

Listing 9.30 SqlGetSchemaTable.aspx

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script runat="Server"> Sub Page_Load   Dim conPubs As SqlConnection   Dim cmdSelect As SqlCommand   Dim dtrSchema As SqlDataReader   Dim dtblSchema As DataTable   conPubs = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;database=pubs" )   cmdSelect = New SqlCommand( "Select * from Authors", conPubs )   conPubs.Open   dtrSchema = cmdSelect.ExecuteReader( CommandBehavior.KeyInfo Or CommandBehavior.SchemaOnly )   dtblSchema = dtrSchema.GetSchemaTable()   dgrdSchema.DataSource = dtblSchema   dgrdSchema.DataBind()   dtrSchema.Close   conPubs.Close End Sub </Script> <html> <head><title>SqlGetSchemaTable.aspx</title></head> <body> <asp:DataGrid   id="dgrdSchema"   CellPadding="4"   HeaderStyle-BackColor="lightgreen"   Runat="Server" /> </body> </html> 

The C# version of this code can be found on the CD-ROM.

Figure 9.10. The Authors table schema.

The page in Listing 9.30 displays information about a number of the properties of each of the columns in the Authors table. For example, the size of each column is displayed. Also, the identity, key, and autoincrement columns are identified.