Using Disconnected RecordsetsBy Sean Grimaldi
This article looks at a neat and not often discussed feature of ADO - disconnected Recordsets. A disconnected Recordset, as its name implies, is a Recordset that lacks a connection. This may seem a bit confusing at first - how can a Recordset lack a connection to a data store? - but will hopefully become more clear as the article progresses.
To understand the need for a disconnected Recordset, it is important to examine where ADO came from and what it was first used for. ADO first became popular in 1997, when most applications were client-server based applications. With its rich object model, ADO greatly simplified accessing and utilizing database information from a client-server application. ADO Recordsets, for example, offer a rich set of methods for filtering, navigating, saving, searching and updating data, tasks that were not nearly as easy to accomplish with ADO's predecessors.
There is a bit of a schism, though, between designing client-server applications and designing Web applications. For example, a common characteristic of client-server computing is that frequently a database connection is open the whole time the client is accessing data. Typically, every client holds an open connection. Web applications are not well suited to this two-tier client-server application architecture, however. A very popular web site can get a couple hundred million hits a day. Since most web sites are data driven to some degree, it quickly becomes apparent that the database for that site would have to support tens of millions of concurrent open connections. This is currently impractical.
A data driven web site characteristically connects to the database, gets data, and presents it. Under ADO, the data is retrieved in a recordset. A Recordset is an ADO object representing the entire set of the results of an executed SQL command. Data cannot be returned from a database as a result of a SQL statement without a connection to the database. This is apparent in the customary ways ASP code is used to return a Recordset full of data.
By default in ADO, when the Recordset is available, the connection to the database is open. While interpreted ASP code is looping through the Recordset, making decisions about what to display and response writing some HTML, the server is using limited resources holding open a connection to the database. Although holding open connections is convenient, it is not scalable enough to support the number of users a large web site could attract. Scalability is the ability for a system to serve a great number of clients with acceptable levels of performance. Since websites can grow exponentially, it is best to plan for having a scalable site. Planning for scalable code reduces hardware requirements upfront. Since the code is more scalable, less server resources are needed. As the site grows additional hardware can be added.
Enter Disconnected Recordsets
Disconnected Recordsets, first available with ADO 2.0, are the most commonly used mechanism to retrieve a Recordset and open a connection for only the necessary amount of time, thus increasing scalability. They are call disconnected because the connection to the database is closed. The collections, properties, and methods of a disconnected Recordset are still available even though the connection is closed. This frees up server resources, given that the number of open connections is limited and database locking is a non-issue.
If you have been thinking ahead, you may be wondering how creating and disposing of all those connection objects could possibly be efficient. (This is where the history lesson becomes important.) Since ADO is just a wrapper to OLE DB, the powerful and sophisticated connection pooling within OLE DB can be used. ADO does this by default so long as the connections strings are identical. That is why it is a good practice to put the database access functionality into a separate Visual Basic data access component or Visual Basic Script Class. You can ensure that the connections are as similar as possible by reusing the code in functions and just passing in the SQL statements. A popular way of retrieving a disconnected Recordset using a function can be seen below:
The developer is returned a disconnected Recordset that can be navigated, persisted, and edited.
Now that we've looked at how to create a disconnected Recordset, there are some caveats that we should address. In Part 2 we'll look at these and also examine how to create custom, disconnected Recordsets!