Figure 7-7: Processing multiple result sets.
Once you have a physical channel set up between your client and the database, you can start preparing and executing commands. The ADO.NET object model provides two types of command objects the traditional one-off command and the data adapter. The one-off command executes a SQL command or a stored procedure and returns a sort of cursor. Using that, you then scroll through the rows and read data. While the cursor is in use, the connection is busy and open. The data adapter, on the other hand, is a more powerful object that internally uses a command and a cursor. It retrieves and loads the data into a data container class DataSet or DataTable. The client application can then process the data while disconnected from the source.
We'll cover container classes and data adapters in the next chapter. Let's focus on one-off commands, paying particular attention to SQL Server commands.
The SqlCommand class represents a SQL Server statement or stored procedure. It is a cloneable and sealed class that implements the IDbCommand interface. In ADO.NET 2.0, it derives from DbCommand which, in turn, implements the interface. A command executes in the context of a connection and, optionally, a transaction. This situation is reflected by the constructors available in the SqlCommand class:
public SqlCommand(); public SqlCommand(string); public SqlCommand(string, SqlConnection); public SqlCommand(string, SqlConnection, SqlTransaction);
The string argument denotes the text of the command to execute (and it can be a stored procedure name), whereas the SqlConnection parameter is the connection object to use. Finally, if specified, the SqlTransaction parameter represents the transactional context in which the command has to run. ADO.NET command objects never implicitly open a connection. The connection must be explicitly assigned to the command by the programmer and opened and closed with direct operations. The same holds true for the transaction.
Table 7-10 shows the attributes that make up a command in the.NET data provider for SQL Server.
| Property | IDbCommand Interface | Description |
|---|---|---|
| CommandText | Yes | Gets or sets the statement or the stored procedure name to execute. |
| CommandTimeout | Yes | Gets or sets the seconds to wait while trying to execute the command. The default is 30. |
| CommandType | Yes | Gets or sets how the CommandText property is to be interpreted. Set to Text by default, which means the CommandText property contains the text of the command. |
| Connection | Yes | Gets or sets the connection object used by the command. It is null by default. |
| Notification | Gets or sets the SqlNotificationRequest object bound to the command. This property requires SQL Server 2005. | |
| NotificationAutoEnlist | Indicates whether the command will automatically enlist the SQL Server 2005 notification service. This property requires SQL Server 2005. | |
| Parameters | Yes | Gets the collection of parameters associated with the command. |
| Transaction | Yes | Gets or sets the transaction within which the command executes. The transaction must be connected to the same connection as the command. |
| UpdatedRowSource | Yes | Gets or sets how query command results are applied to the row being updated. The value of this property is used only when the command runs within the Update method of the data adapter. Acceptable values are in the UpdateRowSource enumeration. |
Commands can be associated with parameters, and each parameter is rendered using a provider-specific object. For the SQL Server managed provider, the parameter class is SqlParameter. The command type determines the role of the CommandText property. The possible values for CommandType are:
Text. The default setting, which indicates the property contains Transact-SQL text to execute directly.
StoredProcedure. Indicates that the content of the property is intended to be the name of a stored procedure contained in the current database.
TableDirect. Indicates the property contains a comma-separated list containing the names of the tables to access. All rows and columns of the tables will be returned. It is supported only by the data provider for OLE DB.
To execute a stored procedure, you need the following:
using (SqlConnection conn = new SqlConnection(ConnString)) { SqlCommand cmd = new SqlCommand(sprocName, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Connection.Open(); cmd.ExecuteNonQuery(); } In ADO.NET 2.0, commands have two main new features asynchronous executors and support for notification services. We'll cover both later.
Table 7-11 details the methods available for the CommandText class.
| Property | IDbCommand Interface | Description |
|---|---|---|
| BeginExecuteNonQuery | Executes a nonquery command in a nonblocking manner. Not supported in ADO.NET 1.x. | |
| BeginExecuteReader | Executes a query command in a nonblocking manner. Not supported in ADO.NET 1.x. | |
| BeginExecuteXmlReader | Executes an XML query command in a nonblocking manner. Not supported in ADO.NET 1.x. | |
| Cancel | Yes | Attempts to cancel the execution of the command. No exception is generated if the attempt fails. |
| CreateParameter | Yes | Creates a new instance of a SqlParameter object. |
| EndExecuteNonQuery | Completes a nonquery command executed asynchronously. Not supported in ADO.NET 1.x. | |
| EndExecuteReader | Completes a query command executed asynchronously. Not supported in ADO.NET 1.x. | |
| EndExecuteXmlReader | Completes an XML query command executed asynchronously. Not supported in ADO.NET 1.x. | |
| ExecuteNonQuery | Yes | Executes a nonquery command, and returns the number of rows affected. |
| ExecuteReader | Yes | Executes a query, and returns a read-only cursor the data reader to the data. |
| ExecuteScalar | Yes | Executes a query, and returns the value in the 0,0 position (first column of first row) in the result set. Extra data is ignored. |
| ExecuteXmlReader | Executes a query that returns XML data and builds an XmlReader object. | |
| Prepare | Yes | Creates a prepared version of the command in an instance of SQL Server. |
| ResetCommandTimeout | Resets the command timeout to the default. |
Parameterized commands define their own arguments using instances of the SqlParameter class. Parameters have a name, value, type, direction, and size. In some cases, parameters can also be associated with a source column. A parameter is associated with a command by using the Parameters collection:
SqlParameter parm = new SqlParameter(); parm.ParameterName = "@employeeid"; parm.DbType = DbType.Int32; parm.Direction = ParameterDirection.Input; cmd.Parameters.Add(parm);
The following SQL statement uses a parameter:
SELECT * FROM employees WHERE employeeid=@employeeid
The .NET data provider for SQL Server identifies parameters by name, using the @ symbol to prefix them. In this way, the order in which parameters are associated with the command is not critical.
| Note | Named parameters are supported by the managed provider for Oracle but not by the providers for OLE DB and ODBC data sources. The OLE DB and ODBC data sources use positional parameters identified with the question mark (?) placeholder. The order of parameters is important. |
As Table 7-11 shows, a SqlCommand object can be executed either synchronously or asynchronously. Let's focus on synchronous execution, which is supported on all .NET platforms. Execution can happen in four different ways: ExecuteNonQuery, ExecuteReader, ExecuteScalar, and ExecuteXmlReader. The various executors work in much the same way, but they differ in the return values. Typically, you use the ExecuteNonQuery method to perform update operations such as those associated with UPDATE, INSERT, and DELETE statements. In these cases, the return value is the number of rows affected by the command. For other types of statements, such as SET or CREATE, the return value is -1.
The ExecuteReader method is expected to work with query commands, and returns a data reader object an instance of the SqlDataReader class. The data reader is a sort of read-only, forward-only cursor that client code scrolls and reads from. If you execute an update statement through ExecuteReader, the command is successfully executed but no affected rows are returned. We'll return to data readers in a moment.
The ExecuteScalar method helps considerably when you have to retrieve a single value. It works great with SELECT COUNT statements or for commands that retrieve aggregate values. If you call the method on a regular query statement, only the value in the first column of the first row is read and all the rest is discarded. Using ExecuteScalar results in more compact code than you'd get by executing the command and manually retrieving the value in the topleft corner of the rowset.
These three executor methods are common to all command objects. The SqlCommand class also features the ExecuteXmlReader method. It executes a command that returns XML data and builds an XML reader so that the client application can easily navigate through the XML tree. The ExecuteXmlReader method is ideal to use with query commands that end with the FOR XML clause or with commands that query for text fields filled with XML data. Note that while the XmlReader object is in use, the underlying connection is busy.
The data reader class is specific to a DBMS and works like a firehose-style cursor. It allows you to scroll through and read one or more result sets generated by a command. The data reader operates in a connected way and moves in a forward-only direction. A data reader is instantiated during the execution of the ExecuteReader method. The results are stored in a buffer located on the client and are made available to the reader.
By using the data reader object, you access data one record at a time as soon as it becomes available. An approach based on the data reader is effective both in terms of system overhead and performance. Only one record is cached at any time, and there's no wait time to have the entire result set loaded in memory.
Table 7-12 shows the properties of the SqlDataReader class that is, the data reader class for SQL Server.
| Property | Description |
|---|---|
| Depth | Indicates the depth of nesting for the current row. For the SqlDataReader class, it always returns 0. |
| FieldCount | Gets the number of columns in the current row. |
| HasRows | Gets a value that indicates whether the data reader contains one or more rows. Not supported in ADO.NET 1.0. |
| IsClosed | Gets a value that indicates whether the data reader is closed. |
| Item | Indexer property, gets the value of a column in the original format. |
| RecordsAffected | Gets the number of rows modified by the execution of a batch command. |
The Depth property is meant to indicate the level of nesting for the current row. The depth of the outermost table is always 0; the depth of inner tables grows by one. Most data readers, including the SqlDataReader and OracleDataReader classes, do not support multiple levels of nesting so that the Depth property always returns 0.
The RecordsAffected property is not set until all rows are read and the data reader is closed. The default value of RecordsAffected is -1. Note that IsClosed and RecordsAffected are the only properties you can invoke on a closed data reader.
Table 7-13 lists the methods of the SQL Server data reader class.
| Methods | Description |
|---|---|
| Close | Closes the reader object. Note that closing the reader does not automatically close the underlying connection. |
| GetBoolean | Gets the value of the specified column as a Boolean. |
| GetByte | Gets the value of the specified column as a byte. |
| GetBytes | Reads a stream of bytes from the specified column into a buffer. You can specify an offset both for reading and writing. |
| GetChar | Gets the value of the specified column as a single character. |
| GetChars | Reads a stream of characters from the specified column into a buffer. You can specify an offset both for reading and writing. |
| GetDataTypeName | Gets the name of the back-end data type in the specified column. |
| GetDateTime | Gets the value of the specified column as a DateTime object. |
| GetDecimal | Gets the value of the specified column as a decimal. |
| GetDouble | Gets the value of the specified column as a double-precision floating-point number. |
| GetFieldType | Gets the Type object for the data in the specified column. |
| GetFloat | Gets the value of the specified column as a single-precision floating-point number. |
| GetGuid | Gets the value of the specified column as a globally unique identifier (GUID). |
| GetInt16 | Gets the value of the specified column as a 16-bit integer. |
| GetInt32 | Gets the value of the specified column as a 32-bit integer. |
| GetInt64 | Gets the value of the specified column as a 64-bit integer. |
| GetName | Gets the name of the specified column. |
| GetOrdinal | Given the name of the column, returns its ordinal number. |
| GetSchemaTable | Returns a DataTable object that describes the metadata for the columns managed by the reader. |
| GetString | Gets the value of the specified column as a string. |
| GetValue | Gets the value of the specified column in its original format. |
| GetValues | Copies the values of all columns in the supplied array of objects. |
| IsDbNull | Indicates whether the column contains null values. The type for a null column is System.DBNull. |
| NextResult | Moves the data reader pointer to the beginning of the next result set, if any. |
| Read | Moves the data reader pointer to the next record, if any. |
The SQL Server data reader also features a variety of other DBMS-specific get methods. They include methods such as GetSqlDouble, GetSqlMoney, GetSqlDecimal, and so on. The difference between the GetXXX and GetSqlXXX methods is in the return type. With the GetXXX methods, a base .NET Framework type is returned; with the GetSqlXXX methods, a .NET Framework wrapper for a SQL Server type is returned such as SqlDouble, SqlMoney, or SqlDecimal. The SQL Server types belong to the SqlDbType enumeration.
All the GetXXX methods that return a value from a column identify the column through a 0-based index. Note that the methods don't even attempt a conversion; they simply return data as is and just make a cast to the specified type. If the actual value and the type are not compatible, an exception is thrown.
| Note | The GetBytes method is useful to read large fields one step at a time. However, the method can also be used to obtain the length in bytes of the data in the column. To get this information, pass a buffer that is a null reference and the return value of the method will contain the length. |
The key thing to remember when using a data reader is that you're working while connected. The data reader represents the fastest way to read data out of a source, but you should read your data as soon as possible and then release the connection. One row is available at a time, and you must move through the result set by using the Read method. The following code snippet illustrates the typical loop you implement to read all the records of a query:
using (SqlConnection conn = new SqlConnection(connString)) { string cmdText = "SELECT * FROM customers"; SqlCommand cmd = new SqlCommand(cmdText, conn); cmd.Connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) CustomerList.Items.Add(reader["companyname"].ToString()); reader.Close(); } You have no need to explicitly move the pointer ahead and no need to check for the end of the file. The Read method returns false if there are no more records to read. A data reader is great if you need to consume data by processing the records in some way. If you need to cache values for later use, the data reader is not appropriate. You need a container object in this case, as we'll see in Chapter 8.
| Note | Although accessing row fields by name is easy to read and understand, it is not the fastest approach. Internally, in fact, the data reader needs to resolve the name to a 0-based index. If you provide the index directly, you get slightly faster code: const int Customers_CustomerID = 0; ... Response.Write(reader[Customers_CustomerID].ToString()); The preceding code shows that using constants turns out to be a good compromise between speed and readability. |
When calling the ExecuteReader method on a command object on any command object, regardless of the underlying DBMS you can require a particular working mode known as a command behavior. ExecuteReader has a second overload that takes an argument of type CommandBehavior:
cmd.ExecuteReader(CommandBehavior.CloseConnection);
CommandBehavior is an enumeration. Its values are listed in Table 7-14.
| Behavior | Description |
|---|---|
| CloseConnection | Automatically closes the connection when the data reader is closed. |
| Default | No special behavior is required. Setting this option is functionally equivalent to calling ExecuteReader without parameters. |
| KeyInfo | The query returns only column metadata and primary key information. The query is executed without any locking on the selected rows. |
| SchemaOnly | The query returns only column metadata and does not put any lock on the database rows. |
| SequentialAccess | Enables the reader to load data as a sequential stream. This behavior works in conjunction with methods such as GetBytes and GetChars, which can be used to read bytes or characters having a limited buffer size for the data being returned. |
| SingleResult | The query is expected to return only the first result set. |
| SingleRow | The query is expected to return a single row. |
The sequential access mode applies to all columns in the returned result set. This means you can access columns only in the order in which they appear in the result set. For example, you cannot read column 2 before column 1. More exactly, if you read or move past a given location, you can no longer read or move back. Combined with the GetBytes method, sequential access can be helpful in cases in which you must read BLOBs with a limited buffer.
| Note | You can also specify SingleRow when executing queries that are expected to return multiple result sets. In this case, all the generated result sets are correctly returned, but each result set has a single row. SingleRow and SingleResult serve the purpose of letting the underlying provider machinery know about the expected results so that some internal optimization can optionally be made. |
The data reader is not a publicly creatable object. It does have a constructor, but not one that is callable from within user applications. The data reader constructor is marked as internal and can be invoked only from classes defined in the same assembly System.Data. The data reader is implicitly instantiated when the ExecuteReader method is called. Opening and closing the reader are operations distinct from instantiation and must be explicitly invoked by the application. The Read method advances the internal pointer to the next readable record in the current result set. The Read method returns a Boolean value indicating whether more records can be read. While records are being read, the connection is busy and no operation other than closing can be performed on the connection object.
The data reader and the connection are distinct objects and should be managed and closed independently. Both objects provide a Close method that should be called twice once on the data reader (first) and once on the connection. When the CloseConnection behavior is required, closing the data reader also closes the underlying connection. In addition, the data reader's Close method fills in the values for any command output parameters and sets the RecordsAffected property.
| Tip | Because of the extra work Close always performs on a data reader class, closing a reader with success can sometimes be expensive, especially in cases of long-running and complicated queries. In situations in which you need to squeeze out every bit of performance, and where the return values and number of records affected are not significant, you can invoke the Cancel method of the associated SqlCommand object instead of closing the reader. Cancel aborts the operation and closes the reader faster. Aside from this, you're still responsible for properly closing the underlying connection. |
Depending on the syntax of the query, multiple result sets can be returned. By default, the data reader is positioned on the first of them. You use the Read method to scroll through the various records in the current result set. When the last record is found, the Read method returns false and does not advance further. To move to the next result set, you should use the NextResult method. The method returns false if there are no more result sets to read. The following code shows how to access all records in all returned result sets:
using (SqlConnection conn = new SqlConnection(connString)) { string cmdText = Query.Text; SqlCommand cmd = new SqlCommand(cmdText, conn); cmd.Connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); do { // Move through the first result set while (reader.Read()) sb.AppendFormat("{0}, {1}<br/>", reader[0], reader[1]); // Separate result sets sb.Append("<hr />"); } while (reader.NextResult()); reader.Close(); } // Display results in the page Results.Text = sb.ToString(); Figure 7-7 shows the output generated by the sample page based on this code.
Figure 7-7: Processing multiple result sets.
| Note | The .NET Framework version 1.1 extends the programming interface of data readers by adding the HasRows method, which returns a Boolean value indicating whether there are more rows to read. However, the method does not tell anything about the number of rows available. Similarly, there is no method or trick for knowing in advance how many result sets have been returned. |
A database operation is normally a synchronous operation the caller regains control of the application only after the interaction with the database is completed. This approach can lead to performance and scalability issues in lengthy operations a common scenario when you interact with a DBMS. The .NET Framework 1.x supports asynchronous operations, but the model is implemented around user-level code. In other words, you can implement your own procedures asynchronously and connect to databases and run commands as part of the code, but connection management and command execution remain atomic operations that execute synchronously.
The .NET data provider for SQL Server in ADO.NET 2.0 provides true asynchronous support for executing commands. This offers a performance advantage because you can perform other actions until the command completes. However, this is not the only benefit. The support for asynchronous operations is built into the SqlCommand class and is limited to executing nonquery commands and getting a reader or an XML reader. You can use three different approaches to build commands that work asynchronously: nonblocking, polling, and callback.
To enable asynchronous commands, you must set the new Async attribute to true in the connection string. You'll receive an exception if any of the asynchronous methods are called over a connection that doesn't have asynchronous capabilities explicitly turned on. Enabling asynchronous commands does have a cost in terms of overall performance; for this reason, you're better off using the Async keyword only with connection objects that execute asynchronous operations only.
If you need both synchronous and asynchronous commands, employ different connections wherever possible. Note, though, that you can still call synchronous methods over connections enabled to support asynchronous operations. However, you'll only end up using more resources than needed and experience a performance degradation.
| Note | Asynchronous commands are not implemented by creating a new thread and blocking execution on it. Among other things, ADO.NET is not thread-safe and blocking threads would be a serious performance hit. When asynchronous commands are enabled, ADO.NET opens the TCP socket to the database in overlapped mode and binds it to the I/O completion port. In light of this, synchronous operations execute as the emulation of asynchronous operations, and this explains why they're more expensive than asynchronous-enabled connections. |
Nonblocking commands are the simplest case of asynchronous commands. The code starts the operation and continues executing other unrelated methods; then it comes back to get the results. Whatever the model of choice happens to be, the first step of an asynchronous command is calling one of the BeginExecuteXXX methods. For example, if you want to execute a reading command, you call BeginExecuteReader:
// Start a non-blocking execution IAsyncResult iar = cmd.BeginExecuteReader(); // Do something else meanwhile ... // Block the execution until done SqlDataReader reader = cmd.EndExecuteReader(iar); // Process data here ... ProcessData(reader);
The BeginExecuteReader function returns an IAsyncResult object you will use later to complete the call. Note that EndExecuteReader is called to finish the operation and will block execution until the ongoing command terminates. The EndExecuteReader function will automatically sync up the command with the rest of the application, blocking the code whenever the results of the command are not ready.
As an alternative to the aforementioned approach, the client code might want to check the status of a running asynchronous operation and poll for completion. The following code illustrates the polling option with a query statement:
// Executes a query statement IAsyncResult iar = cmd.BeginExecuteReader(); do { // Do something here } while (!iar.IsCompleted); // Sync up SqlDataReader reader = cmd.EndExecuteReader(iar); ProcessData(reader); It is important to note that if iar.IsCompleted returns true, the EndExecuteReader method will not block the application.
The third option for nonblocking commands has the client code start the database operation and continue without waiting. Later on, when the operation is done, it receives a call. In this case, you pass a delegate to a BeginExecuteXXX method and any information that constitutes the state of the particular call. The state is any information you want to pass to the callback function. In this case, you pass the command object:
// Begin executing the command IAsyncResult ar = cmd.BeginExecuteReader( new AsyncCallback(ProcessData), cmd);
After initiating the asynchronous operation, you can forget about it and do any other work. The specified callback function is invoked at the end of the operation. The callback must have the following layout:
public void ProcessData(IAsyncResult ar) { // Retrieve the context of the call SqlCommand cmd = (SqlCommand) iar.AsyncState; // Complete the async operation SqlDataReader reader = cmd.EndExecuteReader(iar); ... } The context of the call you specified as the second argument to BeingExecuteReader is packed in the AsyncState property of the IAsyncResult object.
| Note | The callback will be called in a thread-pool thread, which is likely to be different from the thread that initiated the operation. Proper thread synchronization might be needed, depending on the application. This also poses a problem with the user interface of applications, especially Windows Forms applications. Ensuring that the UI is refreshed in the right thread is up to you. Windows Forms controls and forms provide mechanisms for deciding if the correct thread is currently executing and for accessing the correct thread if it isn't. You should consult the MSDN documentation or a good Windows Forms programming book for more information regarding multithreaded Windows Forms programming. Note that if you fail to use the threading model correctly, your application will almost certainly lock up and quite possibly even crash. |
Having asynchronous commands available is not necessarily a good reason for using them without due forethought. Let's examine a couple of scenarios where asynchronous commands are useful for building better Web pages. The first scenario we'll consider is the execution of multiple SQL statements in parallel, either against the same or different database servers.
Imagine that your page displays information about a particular customer both personal and accounting data. The former block of data comes from the client's database; the latter is excerpted from the accounting database. You can fire both queries at the same time and have them execute in parallel on distinct machines thus benefiting from true parallelism. Here's an example:
protected void QueryButton_Click(object sender, EventArgs e) { string custID = CustomerList.SelectedValue; using (SqlConnection conn1 = new SqlConnection(ConnString1)) using (SqlConnection conn2 = new SqlConnection(ConnString2)) { // Fire the first command: get customer info SqlCommand cmd1 = new SqlCommand(CustomerInfoCmd, conn1); cmd1.Parameters.Add("@customerid", SqlDbType.Char, 5).Value = custID; conn1.Open(); IAsyncResult arCustomerInfo = cmd1.BeginExecuteReader(); // Fire the second command: get order info SqlCommand cmd2 = new SqlCommand(CustomerOrderHistory, conn2); cmd2.CommandType = CommandType.StoredProcedure; cmd2.Parameters.Add("@customerid", SqlDbType.Char, 5).Value = custID; conn2.Open(); IAsyncResult arOrdersInfo = cmd2.BeginExecuteReader(); // Prepare wait objects to sync up WaitHandle[] handles = new WaitHandle[2]; handles[0] = arCustomerInfo.AsyncWaitHandle; handles[1] = arOrdersInfo.AsyncWaitHandle; SqlDataReader reader; // Wait for all commands to terminate (no longer than 5 secs) for (int i=0; i<2; i++) { StringBuilder builder = new StringBuilder(); int index = WaitHandle.WaitAny(handles, 5000, false); if (index == WaitHandle.WaitTimeout) throw new Exception("Timeout expired"); if (index == 0) { // Customer info reader = cmd1.EndExecuteReader(arCustomerInfo); if (!reader.Read()) continue; builder.AppendFormat("{0}<br>", reader["companyname"]); builder.AppendFormat("{0}<br>", reader["address"]); builder.AppendFormat("{0}<br>", reader["country"]); Info.Text = builder.ToString(); reader.Close(); } if (index == 1) { // Orders info reader = cmd2.EndExecuteReader(arOrdersInfo); gridOrders.DataSource = reader; gridOrders.DataBind(); reader.Close(); } } } } The page fires the two commands and then sits waiting for the first command to terminate. The AsyncWaitHandle object of each IAsyncResult is stored in an array and passed to the WaitAny method of the WaitHandle class. WaitAny signals out when any of the commands terminates, but the surrounding for statement reiterates the wait until all pending commands terminate. You could have more easily opted for the WaitAll method. In this case, though, you can process results as they become available. This fact ensures a performance gain, especially for long-running stored procedures.
| Note | You can implement the same behavior in ADO.NET 1.x without asynchronous commands by simply assigning each command to a different thread either a user-defined one or one from the thread pool. In this case, though, each command would have blocked a thread. Blocking threads is fine for client-side applications, but it might compromise scalability in server-side applications such as ASP.NET applications. |
Imagine a data-driven ASP.NET page that employs long-running, synchronous commands. The more the page is requested, the more likely it is that a large share of system threads are blocked while waiting for the database to return results. The paradoxical effect of this is that the Web server is virtually idle (with almost no CPU and network usage) but can't accept new requests because it has very few threads available.
To address this problem, since version 1.0 ASP.NET supports asynchronous HTTP handlers that is, a special breed of page classes that implement the IHttpAsyncHandler interface instead of IHttpHandler. Asynchronous HTTP handlers take care of a request and produce a response in an asynchronous manner. In the .NET Framework 2.0, asynchronous handlers can combine with asynchronous commands to boost data-driven pages.
The IHttpAsyncHandler interface counts BeginProcessRequest and EndProcessRequest methods. In the former method, you connect to the database and kick off the query. BeginProcessRequest receives a callback function directly from ASP.NET; the same callback is used to detect the completion of the asynchronous command.
When BeginProcessRequest returns, the page gives the control back to ASP.NET as if it was served. ASP.NET is now free to reuse the thread to process another request while the database server proceeds. When the query is complete, the signaling mechanism ends up invoking the EndProcessRequest method, although not necessarily on the same thread as the rest of the page, so to speak. The EndProcessRequest method is where you simply collect the data and render the page out.
We'll cover asynchronous handlers in my other book on this subject, Programming Microsoft ASP.NET 2.0 Applications: Advanced Topics.
| Note | A fair number of methods work synchronously even in the context of asynchronous commands. The list includes BeginXXX methods and most methods of the data reader class, such as GetXXX methods Read, Close, and Dispose. |
In ADO.NET, you can choose between two types of transactions: local and distributed. A local transaction involves a single resource typically, the database you're connected to. You begin the transaction, you attach one or more commands to its context, and decide whether the whole operation was successful or whether it failed. The transaction is then committed or rolled back accordingly. This approach is functionally similar to simply running a SQL stored procedure that groups a few commands under the same transaction. Using ADO.NET code makes it more flexible but doesn't change the final effect.
A distributed transaction spans multiple heterogeneous resources and ensures that if the entire transaction is committed or rolled back, all modifications made at the various steps are committed or rolled back as well. A distributed transaction requires a Transaction Processing (TP) monitor. The Distributed Transaction Coordinator (DTC) is the TP monitor for Microsoft Windows 2000 and later.
In ADO.NET 1.x, you manage a local transaction through a bunch of database-specific transaction objects for example, SqlTransaction for SQL Server transactions. You begin the transaction, associate commands to it, and decide the outcome. For distributed transactions, you need Enterprise Services and serviced components. You can enlist database connections to Enterprise Services DTC managed transactions by using the aforementioned EnlistDistributedTransaction method on the connection class.
In ADO.NET 2.0, local and distributed transactions can also be managed (more easily, actually) through the new classes defined in the System.Transactions namespace specifically, with the TransactionScope class.
You start a new local transaction through the BeginTransaction method of the connection class. You can give the transaction a name and an isolation level. The method maps to the SQL Server implementation of BEGIN TRANSACTION. The following code snippet shows the typical flow of a transactional piece of code:
SqlTransaction tran; tran = conn.BeginTransaction(); SqlCommand cmd1 = new SqlCommand(cmdText1); cmd1.Connection = conn; cmd1.Transaction = tran; ... SqlCommand cmd2 = new SqlCommand(cmdText2); cmd2.Connection = conn; cmd2.Transaction = tran; ... try { cmd1.ExecuteNonQuery(); cmd2.ExecuteNonQuery(); tran.Commit(); } catch { tran.Rollback(); } finally { conn.Close(); } The newly created transaction object operates on the same connection represented by the connection object you used to create it. To add commands to the transaction, you set the Transaction property of command objects. Note that if you set the Transaction property of a command to a transaction object that is not connected to the same connection, an exception will be thrown as you attempt to execute a statement. Once all the commands have terminated, you call the Commit method of the transaction object to complete the transaction, or you call the Rollback method to cancel the transaction and undo all changes.
The isolation level of a transaction indicates the locking behavior for the connection. Common values are: ReadCommitted (default), ReadUncommitted, RepeatableRead, and Serializable. Imagine a situation in which one transaction changes a value that a second transaction might need to read. ReadCommitted locks the row and prevents the second transaction from reading until the change is committed. ReadUncommitted doesn't hold locks, thus improving the overall performance. In doing so, though, it allows the second transaction to read a modified row before the original change is committed or rolled back. This is a "dirty read" because if the first transaction rolls the change back, the read value is invalid and there's nothing you can do about it. (Of course, you set ReadUncommitted only if dirty reads are not a problem in your scenario.) Note also that disallowing dirty reads also decreases overall system concurrency.
Imagine one transaction reads a committed row; next, another transaction modifies or deletes the row and commits the change. At this point, if the first transaction attempts to read the row again, it will obtain different results. To prevent this, you set the isolation level to RepeatableRead, which prevents further updates and dirty reads but not other operations that can generate phantom rows. Imagine that a transaction runs a query; next, another transaction does something that modifies the results of the previous query. When the first transaction ends, it returns an inconsistent result to the client. The Serializable level prevents concurrent transactions from updating or inserting rows until a given transaction is complete. Table 7-15 summarizes the isolation levels.
| Level | Dirty Reads | Nonrepeatable | Phantom Rows |
|---|---|---|---|
| ReadUncommitted | Yes | Yes | Yes |
| ReadCommitted | No | Yes | Yes |
| RepeatableRead | No | No | Yes |
| Serializable | No | No | No |
The highest isolation level, Serializable, provides a high degree of protection against concurrent transactions, but it requires that each transaction complete before any other transaction is allowed to work on the database.
The isolation level can be changed at any time and remains in effect until explicitly changed. If changed during a transaction, the server is expected to apply the new locking level to all statements remaining.
You terminate a transaction explicitly by using the Commit or Rollback method. The SqlTransaction class supports named savepoints in the transaction that can be used to roll back a portion of the transaction. Named savepoints exploit a specific SQL Server feature the SAVE TRANSACTION statement.
This approach to local transactions is only possible in ADO.NET 1.x and is, of course, fully supported in ADO.NET 2.0. Let's explore alternative approaches.
The preceding code based on BeginTransaction ties you to a specific database and requires you to start a new transaction to wrap a few database commands. What if you need to work with distinct databases and then, say, send a message to a message queue? In ADO.NET 1.x, you typically create a distributed transaction in Enterprise Services. In ADO.NET 2.0, you can perform both local and distributed transactions through a new object TransactionScope. Here's the code:
using (TransactionScope ts = new TransactionScope()) { using (SqlConnection conn = new SqlConnection(ConnString)) { SqlCommand cmd = new SqlCommand(cmdText, conn); cmd.Connection.Open(); try { cmd.ExecuteNonQuery(); } catch (SqlException ex) { // Error handling code goes here lblMessage.Text = ex.Message; } } // Must call to complete; otherwise abort ts.Complete(); } The connection object is defined within the scope of the transaction, so it automatically participates in the transaction. The only thing left to do is commit the transaction, which you do by placing a call to the method Complete. If you omit that call, the transaction fails and rolls back no matter what really happened with the command or commands. Needless to say, any exceptions will abort the transaction.
| Important | You must guarantee that the TransactionScope object will be disposed of. By design, the transaction scope commits or rolls back on disposal. Waiting for the garbage collector to kick in and dispose of the transaction scope can be expensive because distributed transactions have a one-minute timeout by default. Keeping multiple databases locked for up to a minute is an excellent scalability killer. Calling TransactionScope.Dispose manually in the code might not be enough, as it won't be called in case of exceptions. You should either opt for a using statement or a try/catch/finally block. |
Let's consider a transaction that includes operations on different databases the Northwind database of SQL Server 2000 and a custom MyData.mdf file managed through SQL Server 2005 Express. The file is available in the app_Data directory of the sample project. The sample table we're interested in here can be created with the following command:
CREATE TABLE Numbers (ID int, Text varchar(50))
You create a unique and all-encompassing TransactionScope instance and run the various commands, even on different connections. You track the outcome of the various operations and call Complete if all went fine. Here's an example:
bool canCommit = true; using (TransactionScope ts = new TransactionScope()) { // ********************************************************* // Update Northwind on SQL Server 2000 using (SqlConnection conn = new SqlConnection(ConnString)) { SqlCommand cmd = new SqlCommand(UpdateCmd, conn); cmd.Connection.Open(); try { cmd.ExecuteNonQuery(); } catch (SqlException ex) { canCommit &= false; } } // ********************************************************* // Update Numbers on SQL Server 2005 using (SqlConnection conn = new SqlConnection(ConnString05)) { SqlCommand cmd = new SqlCommand(InsertCmd, conn); cmd.Connection.Open(); try { cmd.ExecuteNonQuery(); } catch (SqlException ex) { canCommit &= false; } } // Must call to complete; otherwise abort if (canCommit) ts.Complete(); } If an error occurs, say, on the SQL Server 2005 database, any changes successfully entered on the SQL Server 2000 database are automatically rolled back.
TransactionScope is a convenience class that supports the dispose pattern, and internally it simply sets the current transaction, plus it has some state to track scoping. By wrapping everything in a TransactionScope object, you're pretty much done, as the object takes care of everything else for you. For example, it determines whether you need a local or distributed transaction, enlists any necessary distributed resources, and proceeds with local processing otherwise. As the code reaches a point where it won't be running locally, TransactionScope escalates to DTC as appropriate.
Which objects can be enlisted with a transaction? Anything that implements the required interface ITransaction can be enlisted. ADO.NET 2.0 ships all standard data providers with support for System.Transactions. MSMQ works in compatibility mode.
When some code invokes the Complete method, it indicates that all operations in the scope are completed successfully. Note that the method does not physically terminate the distributed transaction, as the commit operation will still happen on TransactionScope disposal. However, after calling the method, you can no longer use the distributed transaction.
| Note | There are a number of differences between System.Transactions and Enterprise Services as far as distributed transactions are concerned. First, System.Transactions is a transaction framework designed specifically for the managed environment, so it fits more naturally into .NET applications. Of course, internally the classes of the System.Transactions namespace might end up delegating some work to DTC and COM+, but that is nothing more than an implementation detail. Another important difference between the two is the existence of a lightweight transaction manager implemented on the managed side that allows for a number of optimizations, including presenting several enlistments as only one for DTC and support for promotable transactions. |
If your code uses TransactionScope, there's no need for a connection object to explicitly enlist in a transaction. However, if needed, the EnlistTransaction method provides you with exactly that capability.
Manually enlisting connections into distributed transactions is a feature already available in ADO.NET 1.1 through the EnlistDistributedTransaction method of the connection class. The method manually enlists the connection into a transaction being managed by the Enterprise Services DTC. In this case, you work with a distributed transaction that is defined elsewhere and takes direct advantage of the DTC.
| Note | EnlistDistributedTransaction is useful when you have pooled business objects with an open connection. In this case, enlistment occurs only when the connection is opened. If the object participates in multiple transactions, the connection for that object is not reopened and therefore has no way to automatically enlist in new transactions. In this case, you can disable automatic transaction enlistment and enlist the connection explicitly by using EnlistDistributedTransaction. |
The .NET data provider for SQL Server also has new features that are tied to the enhancements in SQL Server 2005. SQL Server 2005 introduces significant enhancements in various areas, including data-type support, query dependency and notification, and multiple active result sets (MARS).
SQL Server 2005 supports any CLR types. In addition to default types, you can store into and retrieve from SQL Server tables any object that is a valid .NET type. This includes both system types such as a Point and user-defined classes. This extended set of capabilities is reflected in the ADO.NET 2.0 provider for SQL Server.
CLR types appear as objects to the data reader, and parameters to commands can be instances of CLR types. The following code snippet demonstrates how to retrieve a value from the MyCustomers table that corresponds to an instance of user-defined Customer class:
string cmdText = "SELECT CustomerData FROM MyCustomers"; SqlConnection conn = new SqlConnection(connStr); SqlCommand cmd = new SqlCommand(cmdText, conn); cmd.Connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while(reader.Read()) { Customer cust = (Customer) reader[0]; // Do some work } cmd.Connection.Close(); A SQL Server 2005 user-defined type is stored as a binary stream of bytes. The get accessor of the data reader gets the bytes and deserializes them to a valid instance of the original class. The reverse process (serialization) takes place when a user-defined object is placed in a SQL Server column.
SQL Server 2005 natively supports the XML data type, which means you can store XML data in columns. At first glance, this feature seems to be nothing new because XML data is plain text and to store XML data in a column you only need the column to accept text. Native XML support in SQL Server 2005, however, means something different you can declare the type of a given column as native XML, not plain text adapted to indicate markup text.
In ADO.NET 1.x, the ExecuteXmlReader method allows you to process the results of a query as an XML stream. The method builds an XmlTextReader object on top of the data coming from SQL Server. Therefore, for the method to work, the entire result set must be XML. Scenarios in which this method is useful include when the FOR XML clause is appended or when you query for a scalar value that happens to be XML text.
In ADO.NET 2.0, when SQL Server 2005 is up and running, you can obtain an XmlTextReader object for each table cell (row, column) whose type is XML. You obtain a SqlDataReader object and have it return XML to you using the new GetSqlXml method. The following code snippet provides a useful example:
string cmdText = " SELECT * FROM MyCustomers"; SqlCommand cmd = new SqlCommand(cmdText, conn); SqlDataReader reader = cmd.ExecuteReader(); while(reader.Read()) { // Assume that field #3 contains XML data // Get data and do some work SqlXml xml = reader.GetSqlXml(3); ProcessData(xml.Value); } The SqlXml class represents the XML data type. The Value property of the class returns the XML text as a string.
Applications that display volatile data or maintain a cache would benefit from friendly server notification whenever their data changes. SQL Server 2005 offers this feature it notifies client applications about dynamic changes in the result set generated by a given query. Suppose your application manages the results of a query. If you register for a notification, your application is informed if something happens at the SQL Server level that modifies the result set generated by that query. This means that if a record originally selected by your query is updated or deleted, or if a new record is added that meets the criteria of the query, you're notified. Note, though, the notification reaches your application only if it is still up and running which poses a clear issue with ASP.NET pages. But let's move forward one step at a time.
The SQL Server provider in ADO.NET 2.0 provides two ways to use this notification feature and two related classes SqlDependency and SqlNotificationRequest. SqlNotificationRequest is a lower-level class that exposes server-side functionality, allowing you to execute a command with a notification request. When a T-SQL statement is executed in SQL Server 2005, the notification mechanism keeps track of the query, and if it detects a change that might cause the result set to change, it sends a message to a queue. A queue is a new SQL Server 2005 database object that you create and manage with a new set of T-SQL statements. How the queue is polled and how the message is interpreted is strictly application-specific.
The SqlDependency class provides a high-level abstraction of the notification mechanism and allows you to set an application-level dependency on the query so that changes in the server can be immediately communicated to the client application through an event. The following code binds a command to a SQL dependency:
SqlCommand cmd = new SqlCommand("SELECT * FROM Employees", conn); SqlDependency dep = new SqlDependency(cmd); dep.OnChange += new OnChangeEventHandler(OnDependencyChanged); SqlDataReader reader = cmd.ExecuteReader(); The OnChange event on the SqlDependency class fires whenever the class detects a change that affects the result set of the command. Here's a typical handler:
void OnDependencyChanged(object sender, SqlNotificationsEventArgs e) { ... } When the underlying machinery detects a change, it fires the event to the application.
As mentioned, using notifications in this way is not particularly interesting from an ASP.NET perspective because the page returns immediately after running the query. However, the caching API of ASP.NET 2.0 provides a similar feature that automatically tracks the results of a query via the ASP.NET cache. What you have in ASP.NET 2.0 is a custom type of cache dependency that monitors the results of a query for both SQL Server 2000 and SQL Server 2005, although in radically different ways. You create a dependency on a command or a table, and place it in the ASP.NET Cache object. The cache item will be invalidated as soon as a change in the monitored command or table is detected. If a SQL Server 2000 instance is involved, you can detect changes to only one of the tables touched by the query; if SQL Server 2005 is involved, you get finer control and can track changes to the result set of the query. We'll cover ASP.NET caching in great detail in Chapter 14.
Version 1.x of the SQL Server managed provider, along with the SQL Server ODBC driver, supports only one active result set per connection. The (unmanaged) OLE DB provider and the outermost ADO library appear to support multiple active result sets, but this is an illusion. In OLE DB, the effect is obtained by opening additional and nonpooled connections.
In SQL Server 2005, the multiple active result set (MARS) feature is natively implemented and allows an application to have more than one SqlDataReader open on a connection, each started from a separate command. Having more than one data reader open on a single connection offers a potential performance improvement because multiple readers are much less expensive than multiple connections. At the same time, MARS adds some hidden peroperation costs that are a result of its implementation. Considering the tradeoffs and making a thoughtful decision is up to you.
The canonical use of MARS is when you get a data reader to walk through a result set while using another command on the same connection to issue update statements to the database. The following code demonstrates a sample page that walks through a data reader and updates the current record using a second command. If you try this approach in ADO.NET 1.x, or in ADO.NET 2.0 with MARS disabled, you get an exception complaining that the data reader associated with this connection is open and should be closed first.
using (SqlConnection conn = new SqlConnection(connString)) { SqlCommand cmd1 = new SqlCommand("SELECT * FROM employees", conn); cmd1.Connection.Open(); SqlDataReader reader = cmd1.ExecuteReader(); // Walks the data reader while (reader.Read()) { // Reverses the first name string firstNameReversed = reader["firstname"].ToString(); char[] buf = firstNameReversed.ToCharArray(); Array.Reverse(buf); firstNameReversed = new string(buf); // Set the new first name on the same connection int id = (int)reader["employeeid"]; SqlCommand cmd2 = new SqlCommand( "UPDATE employees SET firstname=@newFirstName WHERE employeeid=@empID", conn); cmd2.Parameters.AddWithValue("@newFirstName", firstNameReversed); cmd2.Parameters.AddWithValue("empID", id); cmd2.ExecuteNonQuery(); } reader.Close(); // Get a new reader to refresh the UI grid.DataSource = cmd1.ExecuteReader(); grid.DataBind(); cmd1.Connection.Close(); } Note that for MARS to work, you must use a distinct SqlCommand object, as shown in the following code. If you use a third command object to re-execute the query to get up-to-date records, there's no need to close the reader explicitly.
Another big benefit of MARS is that, if you're engaged in a transaction, it lets you execute code in the same isolation-level scope of the original connection. You won't get this benefit if you open a second connection under the covers.
In ADO.NET 2.0, the MARS feature is enabled by default when SQL Server 2005 is the database server. To disable MARS, you set the MultipleActiveResultSets attribute to false in the connection string. There are some hidden costs associated with MARS. First, MARS requires the continuous creation of SqlCommand objects. To deal with this issue, a pool of command objects is constituted and maintained. Second, there is a cost in the network layer as a result of multiplexing the I/O stream of data. Most of these costs are structural, and you should not expect a great performance improvement by disabling the MARS feature. So what's the purpose of the MultipleActiveResultSets attribute? The attribute appears mostly for backward compatibility. In this way, applications that expect an exception when more than one result set is used can continue working.
| Note | MARS-like behavior is available in the .NET Framework 2.0 versions of the OLE DB and Oracle managed providers. The Oracle provider doesn't support the MARS attribute on the connection string, but it enables the feature automatically. The OLE DB provider doesn't support the connection string attribute either it simulates multiple result sets when you connect to earlier versions of SQL Server or when the MDAC 9.0 library is not available. When you operate through OLE DB on a version of SQL Server 2005 equipped with MDAC 9.0, multiple result sets are active and natively implemented. |
