Http://www.codeproject.com/vb/net/SimulatingRecordsets.asp
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 isDisconnectedAnd mostly based onDataSet
Concept (that involves a client-side data caching), while ADODB was normally used asConnectedData 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 likeDataReader
,Command
, AndTransaction
, 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 missRecordset
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 toADODB.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 ofADODB.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 namedRecordset
And it tries to simulateADODB.Recordset
In its main functionalities. Then, it exposes methods likeOpen()
,Close()
,MoveNext()
,MovePrevious()
,MoveFirst()
,MoveLast()
,Update()
And so on (even if it doesn't currently exposeAddNew()
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 ofRecordset.Open()
Method, a connection is open and a T-SQL cursor is created, based on a givenSELECT
Expression:
cnn = <span class="vb-keyword">New</span> SqlConnection(mConnectionString)cmd = cnn.CreateCommand()cnn.Open()...cmd.CommandText = <span class="vb-string">"DECLARE crsr SCROLL CURSOR FOR "</span> & mSelectStringcmd.ExecuteNonQuery()cmd.CommandText = <span class="vb-string">"OPEN crsr"</span>cmd.ExecuteNonQuery() |
The various movements insideRecordset
Have their counterparts in the server-side T-SQL cursor, so it's not difficult to implement forRecordset
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 |
ForRecordset.Update()
Method, if we suppose the cursor being based on a single-tableSELECT
Statement, we can think to code it as a T-SQL statement like the following:
<span class="vb-function">UPDATE</span> TABLE_NAME<span class="vb-function">SET</span> field1=value1, field2=value2,...<span class="vb-function">WHERE</span> CURRENT OF crsr |
In the same way (under the same single-tableSELECT
Statement restriction), alsoRecordset.Delete()
Method can be coded:
<span class="vb-function">DELETE</span> TABLE_NAME <span class="vb-function">WHERE</span> CURRENT OF crsr |
Finally,Recordset.Close()
Method has simply to execute some cleanup Code (on the server-side cursor and on the open connection ):
cmd.CommandText = <span class="vb-string">"CLOSE crsr"</span>cmd.ExecuteNonQuery()cmd.CommandText = <span class="vb-string">"DEALLOCATE crsr"</span>cmd.ExecuteNonQuery()cmd.Dispose()cnn.Close()cnn.Dispose() |