Simulating Recordsets with ADO.NET

Download sources, sample and executable - 28.6 Kb

Introduction

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()
cnn.Open()
...
cmd.CommandText = "DECLARE crsr SCROLL CURSOR FOR " & mSelectString
cmd.ExecuteNonQuery()
cmd.CommandText = "OPEN crsr"
cmd.ExecuteNonQuery()

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:

Method	T-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,...
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

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.ExecuteNonQuery()
cmd.CommandText = "DEALLOCATE crsr"
cmd.ExecuteNonQuery()
cmd.Dispose()
cnn.Close()
cnn.Dispose()

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.

You must Sign In to use this message board.


Per page

Reg Connection Problem
prabakarank

20:03 25 Aug '09

Hi..i want access MS Access as Database. How can i give connection string for MS Access for your Code..
Sign In·View Thread·PermaLink

Re: Reg Connection Problem
Alberto Venditti

23:34 30 Aug '09

Hi. Sorry but this article targets Microsoft SQL Server exclusively. If you want to adopt the Recordset concept on a Microsoft Access database, my suggestion is to use Recordsets natively through ADODB. Hope this helps. AV
Sign In·View Thread·PermaLink

Please answer me for a Error
phowarso

4:09 12 Sep '07

Hello,Sir I have the following error message after running your project.Please answer me for that error.My mail is phowarso@gmail.com. "An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll". "Additional information: System error."
Sign In·View Thread·PermaLink	2.00/5

Re: Please answer me for a Error
Alberto Venditti

6:51 12 Sep '07

Hi. The exception you noticed is too general to diagnose the issue. We'll try to understand the issue by email and then eventually posting here solutions. AV
Sign In·View Thread·PermaLink

How to Concatinate Two Recordsets in Visual C++6
alisolution

20:53 11 May '07

hi I want to Concatinate values from two recordsets in to one recordset to return from method.. How can i do it ....
Sign In·View Thread·PermaLink

Re: How to Concatinate Two Recordsets in Visual C++6
Alberto Venditti

1:33 14 May '07

An ADODB Recordset object is a data structure that needs to be accessed in a sequential and connected way, and only by reaching an EOF your code can understand the Recordset ended. So, it is impossible to have a caller function unaware of the fact that the first recordset is ending and the second one is beginning (unless you read them both in memory before, for example in an array-like structure, and then you return the array itself -- this is perfectly and simply feasible in ADO.NET by concatenating two DataTables in memory and returning the resulting DataTable to the caller).

Talking about ADODB Recordset, what you probably need is to "concatenate" the two resultsets BEFORE opening them as a monolithic recordset. Look into a UNION query to do this in SQL language.

AV

Accessing mdb files
shekiman

21:48 27 Nov '06

Hallo, I search for some simple solution how to correct data in mdb files, but no success. This Alberto solution is good, but not working for mdb files... Any suggestions? Tnx. /* I'm using VB Express 8.0
Sign In·View Thread·PermaLink

Re: Accessing mdb files
Alberto Venditti

22:37 27 Nov '06

Hi. Of course, this solution works only with a true RDBMS, like SQL Server. What do you mean with "how to correct data in mdb files"? Could you please be more specific? Do you simply need to access in a read/write fashion to an MDB from a VB.NET (or anyway managed) piece of code? AV
Sign In·View Thread·PermaLink	1.00/5

Accesing MS ACCESS in remote machine
hariram28

7:46 20 Sep '06

could you help me using which method i've to access a database in other system in the network.?

My project is Seat allocation in a movie theatre. I like to inform you that this is not an online reservation. There will be five systems(LAN) in which the application will be installed and a database will reside on any one of the system. The database will be placed in anyone could anyone of these five systems. so that the application has to access the database in the other system which is in network.

and also give me suggestions about this project if u have.

Hariram

Re: Accesing MS ACCESS in remote machine
Alberto Venditti

4:25 21 Sep '06

Well, MS ACCESS MDB database files are normally accessible on a LAN network share in the same way they are on a local hard-disk; you have to connect to them by specifying in the (ADO or ADO.NET) connection string the full database file name, prefixed by the UNC path of the network share (in the form of "\\servername\sharename\dbname.mdb") or using a mapped drive letter. Of course, you need the correct permissions in order to access to the MDB in a read/write fashion.
When using an MDB remotely, you have to keep in mind that MS ACCESS is not a database server system, and that all the query processing workload is anyway done on the client machine by Jet engine. So, this solution is not suitable for large databases, high number of users, and so on.
Anyway, this is not the right place to continue this discussion, so please eventually contact me via email.
AV

Re: Accesing MS ACCESS in remote machine
hariram28

8:25 21 Sep '06

Thank you very much for your solution sir, Then suggest me what database should i have to use...? sorry i dont konow how to retreive your e-mail id , thats why i replied through this way... Hariram
Sign In·View Thread·PermaLink	2.00/5

Re: Accesing MS ACCESS in remote machine
Alberto Venditti

5:59 22 Sep '06

In order to create a real client/server application, you need a true database server. Talking about Microsoft products, the best is of course SQL Server; you can adopt for free two versions of it: MSDE (a reduced version of SQL Server 2000) or SQL Express (a reduced version of SQL Server 2005). Go here for free downloads: Microsoft SQL Server Downloads[^] Good luck, AV
Sign In·View Thread·PermaLink

Addnew( ) method
pgkdave

4:56 29 Aug '06

I'm new to VB.net and this code is very interesting for reading and updating a record set but has anyone added a Addnew method to the recordset class so that you can add a new record to the table?
Sign In·View Thread·PermaLink	1.50/5

Making the sample work
Jeppe Andreasen

23:09 2 Jun '05

Hi there! Does anyone know how I can get the reference for RECORDSET. Thanks ALOT!
Sign In·View Thread·PermaLink

Re: Making the sample work
Anonymous

9:16 5 Jun '05

What do you mean? A "reference" manual? If "yes", simply study the Microsoft ADODB technology. Otherwise, be more specific, please!
Sign In·View Thread·PermaLink

Re: Making the sample work
Anonymous

20:26 5 Jun '05

Hi there and thanks! I can't get the sample to work because in the SOLUTION EXPLORER the reference RECORDSET isn't identified.
Sign In·View Thread·PermaLink	2.00/5

How make please help me
akorolev10

3:43 20 Oct '04

I make multiuser system in NET by windows Forms and using ADO.NET How make then user visible any change of the any user's in DataBase in screen is your work "Simulating Recordsets with ADO.NET" help me to my situation or not any idea ???? Best Regard's test
Sign In·View Thread·PermaLink

This is fun and all, but is it advisable
Eric the Half-a-Bee

3:31 12 Oct '04

We've been searching for this kind of thing for a paged interface on data, but it's practically impossible to find these kind of examples on the net. Furthermore, SQL books online disadvises the use of server-side cursors for client operations for the obvious reasons stated in the previous thread. Is there anyone out there that has ideas/examples on paged browsing or cursor-like searches.
Sign In·View Thread·PermaLink

Re: This is fun and all, but is it advisable
Alberto Venditti

7:18 12 Oct '04

What do you mean exactly with the term "paged interface on data"? Just the problem of fetching a set of records in pages (for example: records 1-10, 11-20, 21-30 and so on), given a particular sort order?
Sign In·View Thread·PermaLink

Re: This is fun and all, but is it advisable
Anonymous

21:14 12 Oct '04

That's what I mean, yes. We've got a system that caches n rows, and shows 1 row, and it simulates a recordset up to a certain point. The problem we have is that when we're paging on non-unique fields things go wrong. Seeing your example, I thought there would be others that had to create a similar thing, and must've had the same problem.
Sign In·View Thread·PermaLink

Server-side cursors give poor scalability
Pete Appleton

23:30 10 Oct '04

I understand what you are trying to achieve here, but have to suggest that this is a poor way of doing it. Unsure

By using a server-side cursor you're tying up the SQL server's resources, which will dramatically reduce the application's scalability and speed; this will cause multiple contention issues, and potentially deadlocks and timeouts if a user leaves their machine - these issues are one of the reasons why the database API in .NET has changed.

Generally, it's best to use a batch update mechanism; ie, get the records, perform any row-oriented logic, and then update in a batch. SQL is designed for set-based operations, whilst VB (and the other .NET languages) are designed for single-object based operations. Using server-side cursors in SQL forces the server to perform the single-object type logic that SQL is not optimised for.

As an alternate approach, have you looked at using the ADO.NET RecordSet object and then writing back the changed data in one operation? I suspect that this will be a lot more efficient as well as more scalable.

What's a signature?

Re: Server-side cursors give poor scalability
Alberto Venditti

7:15 12 Oct '04

I agree completely with you!
Everything you said about the use of server resources, the application scalability, the contention/deadlock issues, is absolutely true.
But beginning from the title ("simulating" recordset), this article declares itself as a way to mimic the ADODB.Recordset behavior. So, it is clear that if you use this approach, you will incur in all the classic "problems" related to ADO Recordset (use of server resources, application scalability, contention/deadlock issues,...).
The only goal of this article is to find a way to "use" the old concept of Recordset without using ADODB through COM Interop.

The approach you propose ("batch update mechanism") is - of course - acceptable, but it is totally different: it's a classic offline-update, with postponed DB synchronization; it can be achieved through both DataSets in ADO.NET and disconnected Recordsets in ADODB (I didn't understand well... what do you mean talking about "ADO.NET RecorSer object"?).
But the offline-update is not suitable for all applications, because sometimes you *must* have *not* update/merging issues due to a postponed DB synchronization (typical in disconnected scenarios) and you *have to* use database server locks to guarantee the absence of conflicts due to concurrent operations. In these last cases, you have to lock records (=server resources) for all the duration of your update operation and this requirements implies of course poor scalability and less concurrency, regardless the technology you use; because in this case you are anyway *using* the database server features to guarantee connection isolation, transactional behavior, and so on.

Re: Server-side cursors give poor scalability
Pete Appleton

7:34 12 Oct '04

Thanks for your reply. Firstly, sorry for the confusion with talking about the ADO.NET RecordSet object - I meant the DataSet object Frown

Your reply makes a lot of sense, and I understand what you are trying to achieve. My main query is whether you really need pessimistic locking (which is the effect you're achieving). If you absolutely must use pessimistic locking, then the approach you've given in your article works well. In the example you gave with running through the pubs table it would be better to use optimistic locking (e.g. by using a timestamp column) and handle rejected updates in the client application, which is why I wanted to make it clear about the lack of desirability of pessimistic locking as a "general case". I do agree with you entirely that pessimistic locking is sometimes necessary though.

What's a signature?

Re: Server-side cursors give poor scalability
Nathan Allan

20:40 7 Apr '06

I think this whole "server-side cursors are bad because they blah blah..." bit is a load of hooey that everyone buys without question. There are many factors to be considered in this issue. Saying server side cursors are bad is like saying trucks are bad. Yes, for certain purposes trucks may have disadvantages compared to cars, but the reverse is true for other cases. Server-side cursors allow bounded client fetching, cross system cursor pipelining, and certain cursor configurations can perform virtually as fast as "firehose" cursors.

--
Nathan Allan (Alphora)

Re: Server-side cursors give poor scalability
BlackTigerAP

22:52 10 Oct '06

Completely DISAGREE with you!

I'm sick and tired of this microsoftish marketingish stuff.

Why? Answer is simple. LET US TO CHOOSE! Borland's guys much more smarter than guys in MS. "Disconnected" mode was in ADO for ages. Borland introduced dbExpress BEFORE MS, but left ability to choose. To develop the best solution depending on needs and requirements.

Also MS still doesn't described how to deal with LARGE amounts of data, except a lot of marketing stuff like "don't do this".

We definately need CONNECTED mode in ADO.NET. And nobody can change my mind on this.

Just now I had a small conversation with one guy. He looking for way to use "versioning" (Oracle) in client applications. But ADO.NET it's JUST IMPOSOBLE to do this! Because of "because". Because of people like you. Mad