newbie vb6 ADO to vb.net ado.net, paging

[Peter Row]

Hi,

I am converting an existing VB6 webclass app to VB.NET ADO.NET and I'm
having a problem coming up with solution
for paging the records returned from my stored procedures.

As I understand it from the help I can use a dataadapters fill() method to
specify a page size and tha page I want.
However this will only return 1 page worth of data. Currently in my web
pages I provide next/back links and a
<SELECT> list of page numbers. If I use the dataadapters fill() method to do
the paging how will my code be able
to workout how many pages there are all together?

I looked at:
http://msdn.microsoft.com/library/d...pguide/html/cpconpagingthroughqueryresult.asp
.... but that doesn't seem to fit what I want to do.

In ADO it was simple now it seems to be a lot harder. I realise that ADO.NET
is built for performance and so doesn't
come with as many built in features but some examples of ADO to ADO.NET in
the area of paging would of been handy.

Anybody done the same leap from ADO --> ADO.NET and done paging?

Regards,
Peter

 

[William \(Bill\) Vaughn]

So, when you call Fill, I assume you're setting the "StartRecord", and
"MaxRecords" arguments. Consider that when SQL Server executes a query (even
a stored procedure) it returns a rowset based on the parameters provided.
The Fill arguments simply "step over" the rows returned to bring back N
(MaxRecords) rows starting at a given point "StartRecord". To me, this is
not a particularly effective way to "page" large rowsets.
So, how does a program page through large rowsets? Well, in my books I
describe techniques that work around small rowsets (a few hundred rows).
Since SQL Server does not support a persisted rowset state (other than
server-side cursors), you have to manage this paging yourself. Even if it
did, the state would be managed on a connection basis and in an ASP
environment, you can't depend on connection state between page invocations.
I suggest creating parameter-driven SPs that execute "BETWEEN" queries that
accept the starting position from the previous query (that's easily held in
the ViewState between invocations) and ending position being set as the
number of rows you wish to work with on each "page". These are most easily
managed if the rowset has a unique key that permits easy navigation.

hth

--
____________________________________
Bill Vaughn
MVP, hRD
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.
__________________________________

 

[Peter Row]

Hi,

I don't need to manage any state between requests in an ASP environment.
If more records are returned in between ASP requests all that would happen
is that my dynamically created page would have a <SELECT> with additional
page numbers in. The data presented on each of the paged pages presented on
the web page might differ slightly due to the change in the data but this is
not a problem in my situation.

I agree that pulling a massive dataset across would be inefficent, however I
am also dealing with hundreds of records not thousands and so am paging in
(user)
selectable sizes of 10, 25, 50, 100.

I use nothing but SPs. However your suggestion relies on the records being
in
a certain order. An ORDER BY clause is expensive even if it's on the primary
key,
and lets face it when using a numeric primary key you typically don't want
to order
by this any way. Hence the ORDER BY becomes more expensive when doing it
on another column. The BETWEEN idea would probably be more efficent but I
would then have to execute another SP which did the same query but without
the BETWEEN to work out how many records there are in total. And by the time
you take into account several INNER JOINs etc... it probably would have been
quicker just to get everything and then select one page.

In ADO.COM+ paging was a built in mechanism (admitedly where you had to
have returned all the data to be paged). All I had to do was tell it the
Page size,
what page I wanted and then a simple GetRows method call. Sounds inefficent
but in the situation I am coding for it was fast enough. I might implement
the
old ADO method in ADO.NET and put a to-do note in to try and come up with
a better way after the initial port is done.

Regards,
Peter