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.

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.

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

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.

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.

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).

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.