By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,301 Members | 3,575 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,301 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.BookmarksTableAdapte r bookTA = new
BookmarksDataSetTableAdapters.BookmarksTableAdapte r();
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

Apr 17 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
"Donald Adams" <BD******@hotmail.comwrote in message
news:EC**********************************@microsof t.com...
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.BookmarksTableAdapte r bookTA = new
BookmarksDataSetTableAdapters.BookmarksTableAdapte r();
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.
--

John Saunders [MVP]
Apr 17 '07 #2

P: n/a
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]" <john.saunders at trizetto.comwrote in message
news:O3**************@TK2MSFTNGP03.phx.gbl...
"Donald Adams" <BD******@hotmail.comwrote in message
news:EC**********************************@microsof t.com...
>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.BookmarksTableAdapt er bookTA = new
BookmarksDataSetTableAdapters.BookmarksTableAdapt er();
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.
--

John Saunders [MVP]

Apr 18 '07 #3

P: n/a
"Donald Adams" <BD******@hotmail.comwrote in message
news:B2**********************************@microsof t.com...
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.
--

John Saunders [MVP]
Apr 18 '07 #4

P: n/a

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]" <john.saunders at trizetto.comwrote in message
news:eq**************@TK2MSFTNGP04.phx.gbl...
"Donald Adams" <BD******@hotmail.comwrote in message
news:B2**********************************@microsof t.com...
>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.
--

John Saunders [MVP]

Apr 19 '07 #5

P: n/a
"Donald Adams" <BD******@hotmail.comwrote in message
news:E4**********************************@microsof t.com...
>
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.
--

John Saunders [MVP]
Apr 20 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.