simple row at a time data access

  • Thread starter Thread starter Steve Richter
  • Start date Start date
S

Steve Richter

I would like to step thru an database table one row at a time. Forward
and backward. In a C# .NET program. In general, which .NET class is
used to to this?

I am thinking my prefered approach is to somehow use "DECLARE ...
CURSOR" and "FETCH NEXT", but what do I know. Is it possible to
execute FETCH NEXT in ADO.NET code?

I would use the .NET DataSet concept, but I am not sure if a
DataAdapter and DataSet can page backwards in a table, in the event
that the user wants to page up in a table.

thanks,

-Steve
 
Steve said:
I would like to step thru an database table one row at a time. Forward
and backward. In a C# .NET program. In general, which .NET class is
used to to this?

I am thinking my prefered approach is to somehow use "DECLARE ...
CURSOR" and "FETCH NEXT", but what do I know. Is it possible to
execute FETCH NEXT in ADO.NET code?

I would use the .NET DataSet concept, but I am not sure if a
DataAdapter and DataSet can page backwards in a table, in the event
that the user wants to page up in a table.
Click to expand...

I think I have the answer to my question. Use "select top @pagesize ...
where Key > @Key_of_last_row_of_previous_page" to read the next page of
rows into the data set. To page up in the data set is kind of
questionable. Use "order by Key DESC" in conjunction with "select top
@pagesize".

Any better ideas are welcome. Would really like to have a database
cursor object, then just tell sqlserver to return the next or previous
page worth of rows from the table.

-Steve
 
I included a discussion of how to create server-side cursors in the sessions
I gave at DevConnections. It's not that hard, but there are some issues.
I dug up one of my articles (that I don't think was ever published) that
describes how to do this and posted it to my website (see
http://www.betav.com/Files/Content/whitepapers.htm) .

hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
William said:
I included a discussion of how to create server-side cursors in the sessions
I gave at DevConnections. It's not that hard, but there are some issues.
I dug up one of my articles (that I don't think was ever published) that
describes how to do this and posted it to my website (see
http://www.betav.com/Files/Content/whitepapers.htm) .
Click to expand...

( well written article! )
hth
Click to expand...

It helps a lot. If I understand correctly, the server makes a copy of
all the rows within the scope of the cursor. If my code creates a
cursor over a large table:
DECLARE table_cursor CURSOR FOR SELECT * FROM MassiveTable

SqlServer will copy every row in MassiveTable into a temporary table
that exists while the connection is open? If this is the case then
there does not seem to be much sense in using server side cursors.

-Steve
 
Precisely. In a typical client/server rig that uses a server-side cursor,
the server should not be asked to create a copy of the table. A server-side
cursor should return a few dozen rows at most filtered to see a specifically
focused set of rows--no more. As discussed in my books since the first
Hitchhiker's Guide, server-side cursor management is done best with a fork,
not a dump truck. You create a server-side cursor to get a "view" of rows (a
set of pointers in fact) that are likely to change while the user is working
with the data. No, the entire row is not copied to the keyset cursor--just a
pointer. (A static cursor is another matter and does not make sense given
the ability of ADO.NET to give you roughly the same thing) As you navigate
to a row (and fetch it) using the key the server returns the latest data.

Without getting into membership issues, managing a server-side cursor can
take quite a bit of forethought and planning. ADO classic did much of this
for you. It cached rows on the client (cachesize), and while the data in the
cache got stale, you could refresh the cache on demand. If you take on the
task in ADO.NET, you'll need to do your own caching if you want to improve
performance or leave caching aside if you want the freshest data on each
fetch.

No, server-side cursors don't make sense if you're simply executing a SELECT
* FROM AllMyCustomers.

Consider that MS left server-side cursors behind for a reason. There are not
that many good uses for them. That number is not zero by any means, but in
order to justify their use, you really need to understand how they work so
they can be implemented properly.

hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top