As any .NET developer knows, the ADO.NET approach to data access is substantially different from the ADODB predecessor. First of all, because it is disconnected and mostly based on the
DataSet concept (that involves a client-side data caching), while ADODB was normally used as a connected data access paradigm (with the exception of the so-called "disconnected recordsets"). The only way to use ADO.NET in a connected fashion is using objects like
Transaction, that are not so comfortable if you need to scroll a result set making updates to some data based on a row-oriented logic. This was a very common task when working with ADODB, and a lot of programmers coming from a Visual Studio 6.0 experience will miss the
Recordset concept: being oriented to disconnected scenarios, ADO.NET currently doesn't support features like server-side cursors, and so it doesn't expose objects similar to the
ADODB.Recordset that was very useful to implement row-based logics. Anyone prevents you from continuing to use the ADODB objects while programming on .NET, but if you want to avoid the COM interoperability overhead, this is not the right way.
In this article, I propose a class that simulates the behavior of an
ADODB.Recordset on a Microsoft SQL Server 2000 database through the use of ADO.NET "connected objects" (
DataReader,...) and of server-side cursors directly implemented in T-SQL. The proposed class is developed for SQL Server 2000, but can be easily modified to work with other RDBMSs.
How the code works
The class I wrote is named
Recordset and it tries to simulate the
ADODB.Recordset in its main functionalities. Then, it exposes methods like
Update() and so on (even if it doesn't currently expose an
AddNew() method). To support navigation and random access to rows of a result set without caching data on the client, you need to use a scrollable server-side cursor; this cursor has to be and remain open for all the duration of the connected updates. That's why, behind the scenes of the
Recordset.Open() method, a connection is open and a T-SQL cursor is created, based on a given
cnn = New SqlConnection(mConnectionString)
cmd = cnn.CreateCommand()
cmd.CommandText = "DECLARE crsr SCROLL CURSOR FOR " & mSelectString
cmd.CommandText = "OPEN crsr"
The various movements inside the
Recordset have their counterparts in the server-side T-SQL cursor, so it's not difficult to implement for the
Recordset class the following methods:
FETCH NEXT FROM crsr
FETCH PRIOR FROM crsr
FETCH FIRST FROM crsr
FETCH LAST FROM crsr
FETCH ABSOLUTE n FROM crsr
FETCH RELATIVE n FROM crsr
Recordset.Update() method, if we suppose the cursor being based on a single-table
SELECT statement, we can think to code it as a T-SQL statement like the following:
SET field1=value1, field2=value2,...
WHERE CURRENT OF crsr
In the same way (under the same single-table
SELECT statement restriction), also the
Recordset.Delete() method can be coded as:
DELETE table_name WHERE CURRENT OF crsr
Recordset.Close() method has simply to execute some cleanup code (on the server-side cursor and on the open connection):
cmd.CommandText = "CLOSE crsr"
cmd.CommandText = "DEALLOCATE crsr"
The sample application
A sample application has been written to show how to use the
It connects to the Authors table of the famous database Pubs on the local SQL Server (if you want to use another SQL Server or you don't use the deprecated "blank" password for sa, please modify the value associated to the
ConnectionString key in the App.config configuration file). The user interface of the sample application is self-explaining: each button simply tests the corresponding method of the