How to do data paging in a web service, like asp page

[Donald Adams]

Hi,

I will have both web and win clients and would like to page my data. I
could not find out how the datagrid control does it's paging though I did
find some sample code that says they do it this way, but I can't see these
methods as public.

BookmarksDataSetTableAdapters.BookmarksTableAdapter bookTA = new
BookmarksDataSetTableAdapters.BookmarksTableAdapter();
BookmarkList1.DataSource = bookTA.GetAllBookmarksWrtUser(
User.Identity.Name, pageIndex, numRows, ref totalBookmarks);
TagDataSetTableAdapters.TagsTableAdapter tagTA = new
TagDataSetTableAdapters.TagsTableAdapter();
TagList1.DataSource = tagTA.GetPopularTags(DateTime.Now.AddDays(-30),
User.Identity.Name);
recentTagsList.DataSource = tagTA.GetRecentTags( DateTime.Now.AddDays(-30),
User.Identity.Name);

What library/methods can I use in .Net 2.0 to do paging of SQL data in a web
service?
Can I not just specify my select like when I create a SqlDataSource and have
a class wrap it and give me the paging and sorting abilities?
A code sample would be most helpful.


Thanks in advance,
Donald Adams

 

[John Saunders [MVP]]

Hi,

I will have both web and win clients and would like to page my data. I
could not find out how the datagrid control does it's paging though I did
find some sample code that says they do it this way, but I can't see these
methods as public.

BookmarksDataSetTableAdapters.BookmarksTableAdapter bookTA = new
BookmarksDataSetTableAdapters.BookmarksTableAdapter();
BookmarkList1.DataSource = bookTA.GetAllBookmarksWrtUser(
User.Identity.Name, pageIndex, numRows, ref totalBookmarks);
TagDataSetTableAdapters.TagsTableAdapter tagTA = new
TagDataSetTableAdapters.TagsTableAdapter();
TagList1.DataSource = tagTA.GetPopularTags(DateTime.Now.AddDays(-30),
User.Identity.Name);
recentTagsList.DataSource = tagTA.GetRecentTags(
DateTime.Now.AddDays(-30), User.Identity.Name);

What library/methods can I use in .Net 2.0 to do paging of SQL data in a
web service?
Can I not just specify my select like when I create a SqlDataSource and
have a class wrap it and give me the paging and sorting abilities?
A code sample would be most helpful.

I don't recall that the datagrid does anything special about paging, other
than having an event which fired when the user clicks one of the paging
buttons. All the paging work is performed by deciding which data to present
to the datagrid, and then calling DataBind.

A better point of departure would be for you to decide what you want the web
service calls to look like. In particular, how would the client of your web
service indicate that it wanted the next page? You also need to consider
whether you need this web service to be stateful, which is something one
usually tries to avoid with a web service. You'll need to decide whether to
allow the clients to page backwards, or to position to any arbitrary row of
data. You have to decide what to do if the client issues a new query, and
then tries to get the next page from the previous query, etc.

So, before you worry about implementation details, you've got a bit of
design work to do. I apologize if you've already done that design work, but
your post didn't indicate that.

 

[Donald Adams]

I've implemented paging for a web/webservice back in .net 1.1 days.. but it
wasn't as efficient as .Net 2.0's. In .net 2.0 all I have to do with my
Data Source object is provide a SQL statement like the following:
SELECT * FROM TableA
and tell the datagrid I want to do paging. The rest is magic I'd like to
know, because they do it efficiently.
My client app. handles how the page # is changed by first,back,forward,last,
and direct to page # commands.
All it needs is to know how many pages there are.
So as in the code previously posted all that needs to be sent to the
webservice is the pageIndex(page#), and a column to sort by. I'll need both
the total # of pages and the page of data in a dataset returned.

I've here they use the PagedDataSource class, but there are no samples on
how to use it in the MSDN docs and the docs say it's for a data bound
control. I just want the data, I will deal with it myself.

,,,Donald

 

[John Saunders [MVP]]

I've implemented paging for a web/webservice back in .net 1.1 days.. but
it wasn't as efficient as .Net 2.0's. In .net 2.0 all I have to do with
my Data Source object is provide a SQL statement like the following:
SELECT * FROM TableA
and tell the datagrid I want to do paging. The rest is magic I'd like to
know, because they do it efficiently.

I wonder how you know whether it is done efficiently? Have you measured?
Have you tried it with 100,000 rows of data?

I don't know of any particularly great way of doing what you want, and I
don't think that there's a whole lot of magic going on - it's just that it's
going on behind the scenes. After executing the query, the data source no
doubt reads the result set into a DataSet or some other such object. It then
passes the requested data, starting at row pageSize * pageOffset and for
pageSize pages, to the grid. It might get a bit fancy in terms of optimizing
memory usage in the forward-only scenario, but it's still pretty much
reading all the data and returning it.

Sorry to be the bearer of bad news.

 

[Donald Adams]

I've got 2,037,890 rows (88620 pages, 23 items/page) that I created by
sending our company's Router's SysLog data to SQL DB. It takes 10 secs to
display a page using my solution in .Net 1.1.
In .Net 2.0 it takes... wow... it takes forever. You're right. Thanks for
pushing me to test.
I guess I should not trust Scott Gu's word that the paging is efficient. I
thought they were wrapping my SQL statement with some statements I saw once
that aids is paging in the DB.
I hope they get it right with LINQ, or I'll have to write it all by myself
then too.

Well, since I have to do it all myself, anyone have any better ideas?

Here's the way I've been doing it:
I have a config file that I hit depending on the type of data I want. I
execute the SQL statement according to the paging type here is a sample
config file

<?xml version="1.0"?>
<Root>
<Process is="NetLogWeb">
<Method is="PageData">
<Param is="WebBase.View.Xml.Style">NetLogWeb</Param>
<Param is="WebBase.Client.Method.Name">SetDocNL</Param>
<Param is="WebBase.Client.Task.Lid.Name">Main</Param>
<Param is="WebBase.Server.DataSet.Name">TransPak</Param>
<Param is="SQL.Database.Table.Name">SysLogRaw</Param>
<Param is="SQL.Database.Package.Size">4096</Param>
<Param is="SQL.Database.Table.Record.Size">23</Param>
<Param is="SQL.Database.Table.Record.Key">Id</Param>
<Param is="SQL.Database.Table.Record.Key.LastNew">Created</Param>
<Param is="SQL.Database.Script.RowCount">USE Transfer;
SELECT COUNT(*)
FROM SysLogRaw
WHERE (Id&gt;'' {0})
</Param>
<Param is="SQL.Database.Script.First">USE Transfer;
SELECT TOP {0} Id, Created,Command, FromIP, FromPort, ToIP, ToPort, Text
FROM SysLogRaw WHERE (Id&gt;'' {1})
ORDER BY Id
</Param>
<Param is="SQL.Database.Script.Next">USE Transfer;
SELECT Id, Created,Command, FromIP, FromPort, ToIP,ToPort, Text
FROM SysLogRaw
WHERE Id IN (SELECT TOP {0} Id FROM SysLogRaw WHERE Id &gt; '{1}' {2} ORDER
BY Id)
ORDER BY Id
</Param>
<Param is="SQL.Database.Script.Prev">USE Transfer;
SELECT Id, Created,Command, FromIP, FromPort, ToIP,ToPort, Text
FROM SysLogRaw
WHERE Id IN (SELECT TOP {0} Id FROM SysLogRaw WHERE Id &lt; '{1}' {2} ORDER
BY Id DESC)
ORDER BY Id
</Param>
<Param is="SQL.Database.Script.Last">USE Transfer;
SELECT Id, Created, Command, FromIP, FromPort, ToIP, ToPort, Text
FROM SysLogRaw
WHERE Id IN (SELECT TOP {0} Id FROM SysLogRaw WHERE Id &gt;'' {1} ORDER BY
Id DESC)
ORDER BY Id
</Param>
<Param is="SQL.Database.Script.JumpPage">USE Transfer;
SELECT Id, Created, Command, FromIP, FromPort, ToIP, ToPort, Text FROM
SysLogRaw
WHERE Id IN (SELECT TOP {0} Id FROM SysLogRaw
WHERE Id IN (SELECT TOP {1} Id FROM SysLogRaw
WHERE Id&gt;='0' {2}
ORDER BY Id) ORDER BY SysLogRaw.Id DESC) ORDER BY SysLogRaw.Id
</Param>
</Method>
</Process>
</Root>



,,,Donald

 

[John Saunders [MVP]]

I've got 2,037,890 rows (88620 pages, 23 items/page) that I created by
sending our company's Router's SysLog data to SQL DB. It takes 10 secs to
display a page using my solution in .Net 1.1.
In .Net 2.0 it takes... wow... it takes forever. You're right. Thanks
for pushing me to test.
I guess I should not trust Scott Gu's word that the paging is efficient.
I thought they were wrapping my SQL statement with some statements I saw
once that aids is paging in the DB.
I hope they get it right with LINQ, or I'll have to write it all by myself
then too.

Well, since I have to do it all myself, anyone have any better ideas?

You might have the client cooperate with the server to get this done:

Step 1) Client calls GetFirstPage("SortKey", "SortDirection"); This returns
one page's worth of data
Step 2) The client calls GetNextPage("SortKey", "SortDirection",
"LastSortKeyValue"); The client gets LastSortKeyValue from the key in the
last row of data. The server returns a page's worth of data, adding "TOP
@pageSize" and "WHERE SortKey > @LastSortKeyValue" to the query.
Step 3) The client receives the data, but doesn't display all of it.
Instead, it filters out those it is currently displaying, based on their
primary key. The rest of the new data are then displayed
Step 4) Repeat step 2

Variations on this might handle the ability to jump forward and backwards,
and by more than one page at a time.

The bottom line is that a query is going to happen and rows are going to be
returned. Those rows can stay on the server until needed on the client, at
the cost of memory and the cost of maintaining so much state. Or, you can be
less stateful and let the client do its share, since it's the client that
wants all this data paged.

Good luck.