Database Paging

[Brian Scott]

Hi,

another question that I hope someone can help me out with. I have started
usin Paging with a Windows Form Datagrid in .Net. What I want to know is is
there a method to dynamically page the query from the database in the same
fashion as Enterprise Manager. I can page x rows at a time but I want to
retrieve the number of rows the user scrolls down on the datagrid.

Thanks.

 

[Frans Bouma [C# MVP]]

Hi,

another question that I hope someone can help me out with. I have started
usin Paging with a Windows Form Datagrid in .Net. What I want to know is is
there a method to dynamically page the query from the database in the same
fashion as Enterprise Manager. I can page x rows at a time but I want to
retrieve the number of rows the user scrolls down on the datagrid.

THere are several ways to do that.

One, and likely the method EnterpriseManager uses, is simply skip
records till you arrive at the first record of the page you want to load.

Another, more efficient, method is using a temp table. You create a
temp table with the same columns as the resultset you want to return,
with 1 extra column: Rownum and that column is the PK and an identity
column.

you then perform an INSERT INTO into the temptable using the select
statement which gives the results you want to page through, and add a
TOP statement to the SELECT so it only returns Pagesize * pagenumber
records.

Then you select the page of records from the temptable using the Rownum
column and you're done.

FB

--

 

[Cor Ligthert]

Brian,

I once tried it using a "Top" an "Order By" and a "Where" in the Select
string.

It is so slow, that you probably don't want to use that.

Cor