Click here to Skip to main content
13,398,090 members (40,552 online)
Click here to Skip to main content
Add your own
alternative version


39 bookmarked
Posted 2 Oct 2004

Simulating Recordsets with ADO.NET

, 2 Oct 2004
Rate this:
Please Sign up or sign in to vote.
A proposed class to simulate the ADODB.Recordset behavior in .NET.


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 DataReader, Command, and 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" (Connection, Command, 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 Open(), Close(), MoveNext(), MovePrevious(), MoveFirst(), MoveLast(), 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 SELECT expression:

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:

MethodT-SQL equivalent
MoveNext()FETCH NEXT FROM crsr
MovePrevious()FETCH PRIOR FROM crsr
MoveFirst()FETCH FIRST FROM crsr
MoveLast()FETCH LAST FROM crsr
MoveAbsolute(n)FETCH ABSOLUTE n FROM crsr
MoveRelative(n)FETCH RELATIVE n FROM crsr

For the 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:

UPDATE table_name
SET field1=value1, field2=value2,...

In the same way (under the same single-table SELECT statement restriction), also the Recordset.Delete() method can be coded as:


Finally, the 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 Recordset class.

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 Recordset class.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Alberto Venditti
Technical Lead
Italy Italy
I was born in 1970.

My first computer experience dates back to early 80s, with a Sinclair ZX81.
From that time on, as many "friends" say, my IT-illness has increased year by year.

I graduated in Electronic Engineering and earned the following Microsoft certifications:
MCP, MCT, MCDBA, MCSD, MCAD, MCSD for .NET (early achiever).

I worked in IT as a developer, a teacher, a consultant, a technical writer, a technical leader.
IT knowledge applied to real life is my primary interest and focus.

You may also be interested in...

Comments and Discussions

Questiongestione eccezioni Pin
Member 1196192819-Sep-15 6:28
memberMember 1196192819-Sep-15 6:28 
Questiongestione eccezioni Pin
Member 1196192819-Sep-15 6:28
memberMember 1196192819-Sep-15 6:28 
AnswerRe: gestione eccezioni Pin
Alberto Venditti22-Sep-15 2:36
memberAlberto Venditti22-Sep-15 2:36 
QuestionSimulate propertie Pin
marianoescriu19-May-14 6:34
membermarianoescriu19-May-14 6:34 
GeneralReg Connection Problem Pin
prabakarank25-Aug-09 20:03
memberprabakarank25-Aug-09 20:03 
GeneralRe: Reg Connection Problem Pin
Alberto Venditti30-Aug-09 23:34
memberAlberto Venditti30-Aug-09 23:34 
GeneralPlease answer me for a Error Pin
phowarso12-Sep-07 4:09
memberphowarso12-Sep-07 4:09 
GeneralRe: Please answer me for a Error Pin
Alberto Venditti12-Sep-07 6:51
memberAlberto Venditti12-Sep-07 6:51 
QuestionHow to Concatinate Two Recordsets in Visual C++6 Pin
alisolution11-May-07 20:53
memberalisolution11-May-07 20:53 
AnswerRe: How to Concatinate Two Recordsets in Visual C++6 Pin
Alberto Venditti14-May-07 1:33
memberAlberto Venditti14-May-07 1:33 
QuestionAccessing mdb files Pin
shekiman27-Nov-06 21:48
membershekiman27-Nov-06 21:48 
AnswerRe: Accessing mdb files Pin
Alberto Venditti27-Nov-06 22:37
memberAlberto Venditti27-Nov-06 22:37 
GeneralAccesing MS ACCESS in remote machine Pin
hariram2820-Sep-06 7:46
memberhariram2820-Sep-06 7:46 
GeneralRe: Accesing MS ACCESS in remote machine Pin
Alberto Venditti21-Sep-06 4:25
memberAlberto Venditti21-Sep-06 4:25 
GeneralRe: Accesing MS ACCESS in remote machine Pin
hariram2821-Sep-06 8:25
memberhariram2821-Sep-06 8:25 
GeneralRe: Accesing MS ACCESS in remote machine Pin
Alberto Venditti22-Sep-06 5:59
memberAlberto Venditti22-Sep-06 5:59 
QuestionAddnew( ) method Pin
pgkdave29-Aug-06 4:56
memberpgkdave29-Aug-06 4:56 
GeneralMaking the sample work Pin
Jeppe Andreasen2-Jun-05 23:09
memberJeppe Andreasen2-Jun-05 23:09 
GeneralRe: Making the sample work Pin
Anonymous5-Jun-05 9:16
sussAnonymous5-Jun-05 9:16 
GeneralRe: Making the sample work Pin
Anonymous5-Jun-05 20:26
sussAnonymous5-Jun-05 20:26 
QuestionHow make please help me Pin
akorolev1020-Oct-04 3:43
memberakorolev1020-Oct-04 3:43 
GeneralThis is fun and all, but is it advisable Pin
Eric the Half-a-Bee12-Oct-04 3:31
sussEric the Half-a-Bee12-Oct-04 3:31 
GeneralRe: This is fun and all, but is it advisable Pin
Alberto Venditti12-Oct-04 7:18
memberAlberto Venditti12-Oct-04 7:18 
GeneralRe: This is fun and all, but is it advisable Pin
Anonymous12-Oct-04 21:14
sussAnonymous12-Oct-04 21:14 
GeneralServer-side cursors give poor scalability Pin
Pete Appleton10-Oct-04 23:30
memberPete Appleton10-Oct-04 23:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.180216.1 | Last Updated 2 Oct 2004
Article Copyright 2004 by Alberto Venditti
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid