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

Discussion in 'ASP .Net' started by Donald Adams, Apr 17, 2007.

  1. Donald Adams

    Donald Adams Guest

    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
    Donald Adams, Apr 17, 2007
    #1
    1. Advertising

  2. "Donald Adams" <> wrote in message
    news:...
    > 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.
    --

    John Saunders [MVP]
    John Saunders [MVP], Apr 17, 2007
    #2
    1. Advertising

  3. Donald Adams

    Donald Adams Guest

    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.com> wrote in message
    news:...
    > "Donald Adams" <> wrote in message
    > news:...
    >> 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.
    > --
    >
    > John Saunders [MVP]
    >
    >
    Donald Adams, Apr 18, 2007
    #3
  4. "Donald Adams" <> wrote in message
    news:...
    > 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]
    John Saunders [MVP], Apr 18, 2007
    #4
  5. Donald Adams

    Donald Adams Guest

    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.com> wrote in message
    news:...
    > "Donald Adams" <> wrote in message
    > news:...
    >> 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]
    >
    >
    Donald Adams, Apr 19, 2007
    #5
  6. "Donald Adams" <> wrote in message
    news:...
    >
    > 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]
    John Saunders [MVP], Apr 20, 2007
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?UGF0cmljay5PLklnZQ==?=

    DataSet paging vs Datareader paging

    =?Utf-8?B?UGF0cmljay5PLklnZQ==?=, Oct 8, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    10,651
    Scott Allen
    Oct 8, 2004
  2. wh1974
    Replies:
    0
    Views:
    2,415
    wh1974
    Jan 12, 2005
  3. Patrick Kowalzick
    Replies:
    5
    Views:
    457
    Patrick Kowalzick
    Mar 14, 2006
  4. Mike Dearman
    Replies:
    1
    Views:
    182
    Mike Dearman
    Jun 23, 2004
  5. Leo Violette
    Replies:
    0
    Views:
    1,000
    Leo Violette
    Apr 17, 2009
Loading...

Share This Page