Expanded and Improved Data Access Features

Expanded and Improved Data Access Features

To understand what the topics covered in this chapter are really about, how they fit together, and what kinds of problems they are designed to solve, you need to think about how existing data access methods are used when all the data an application needs resides in a single database and when it is in more than one database. These issues are becoming more common as applications have to work with multiple sources of data.

While the existing techniques from version 1.x of ADO.NET can easily accomplish the required tasks, both ease of access and performance can be improved. In particular, when the data sources are not local (as is becoming increasingly common in modern client-side applications and multiple-server Web farms), the procedural synchronous nature of current data access techniques can cause processing bottlenecks. You have to consider the individual steps that the code must go through to access the data when more than one query to the database is required.

Accessing a Single Data Source

In the simplest case, where data comes from a single server, the same connection can be used for all the commands that need to be executed to read or update data. Taking a DataSet as an example, the commands would be those required to fetch data from the database (the SelectCommand) and to update the data (the InsertCommand, UpdateCommand, and DeleteCommand). Figure shows a simple scenario.

1. Multiple commands to the same database through a single connection


This process supports only synchronous access to the data. You can't read data through the connection while concurrently updating rows, and you can't execute concurrent insert, delete, and update operations. When the Fill method is called, no other access is possible through the connection until the method completes. And the Update operation automatically executes the commands sequentially when pushing updates back into the database. In most cases, however, this behavior is perfectly satisfactory and matches the requirements of the application.

Executing Multiple Commands Sequentially

An alternative situation is when you use several object instances to access data (here multiple DataReader instances, but it could be a mix of DataReader and DataSet instances) over the same connection. Again, only one of these can be executing a query over the single connection at a time. But because the ADO.NET code you write will usually call methods on the DataReader or other objects sequentially and will block on each method call until it is complete, there is no problem with using the same connection. Figure shows this scenario.

2. Multiple commands for separate DataReader instances through a single connection


In this case, however, there are two areas where performance and usability issues can arise:

  1. If you open a rowset with a DataReader over the connection, you must close it before you attempt to open another DataReader or execute another command—if not, you'll get a "Connection is busy . . ." error. To be able to open more than one rowset concurrently in ADO.NET version 1.x requires that each command have its own separate connection to the database. Because the number of database connections available is limited and they are expensive in terms of resource usage, this often isn't a feasible approach.

  2. If one of the commands you're executing takes a long time to return results, the code in the application will block and wait until the query process is complete before it can execute the next command. So your code may be standing idle when it could be doing something else in the meantime.

The Requirements for Asynchronous Execution

It would be nice to be able to start the three commands together, allowing them to execute asynchronously and then handling the results as they are returned. That way the data or the results can be processed and/or displayed as they are received—making the applications appear more responsive and usable. But that requires two changes to the existing classes to make it work, and (not coincidentally) these are two of the new features in ADO.NET that we're covering in this chapter.

  1. The MARS feature in ADO.NET 2.0 allows you to have more than one rowset or command open over a single connection. In other words, you can open a rowset with a DataReader and then send other commands to the database over the same connection while the DataReader is open. You can even have more than one rowset open in separate DataReader instances at the same time and read from either one as required.

  2. The introduction of asynchronous processing in ASP.NET 2.0 for the Command class allows you to start more than one command executing concurrently and then wait until one or all of them have completed. You can also open connections asynchronously, though only a single connection request can be outstanding at any one time.

The combination of the two techniques means that, using only a single database connection, you can execute multiple commands concurrently and have the rowsets they return open at the same time. And, of course, update commands can also be executed over the same connection at the same time, even when rowsets are open on that connection. This provides a huge opportunity for increased performance and responsiveness for your applications and Web pages. However, bear in mind that there are scenarios, such as when working with multiple separate data sources, when MARS is not directly useful.

Accessing Multiple Data Sources

When it comes to accessing multiple different data sources in an application, you have no option but to use separate connections. Of course, a connection can be to only a single database. So, for multiple data access operations against separate databases, you end up with something like the situation shown in Figure.

3. Multiple commands to different databases through separate connections


The MARS feature may not provide any advantage in this situation because each command will use its own dedicated connection. However, it may be that you have some hybrid scenario, where some commands are to the same database (in which case they can share a connection) while others are to a different database through a different connection (see Figure).

4. A mix of commands and connections for different databases


Here, MARS can provide the opportunity for improved access, for example, being able to open a rowset with a DataReader and then execute commands against the same database while the DataReader is open. But what is also useful, in both of these scenarios (Figures 3.3 and 3.4), is the ability to execute commands asynchronously. For example, you can start execution of the command that fetches the rows for the DataReader, then—while it is running—execute the Fill method for the DataSet.

Opening Connections Asynchronously

In ADO.NET 2.0 it's now also possible to open connections asynchronously. This offers even more opportunity to increase efficiency, especially when access to different data sources is required. It means that your code can continue to execute while waiting for a connection to be established—particularly useful when connecting to a remote data source rather than a local one.

When multiple connections are required, a common scenario is for the code to open these one by one. In fact, there's no alternative to this in ADO.NET 1.x. Being able to open each one asynchronously means that you can start to retrieve or update the data over that connection while waiting for other connections to open. You may even end up actually using fewer connections because you may be able to close some and return them to the connection pool before the last one has opened. Running the same code synchronously would mean holding all of the connections open until all of the processing completes.

Of course, you can open the connection asynchronously in situations where you need only one connection, but the advantage is not as obvious unless you start the opening process at the beginning of the code and then do some other work while you wait for it to open. The danger here is that you may hold on to the connection for longer than necessary, robbing other applications (and other instances of this application) of available connections and producing no overall advantage.

     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows