views:

1028

answers:

0

I am trying to achieve the following using the Datalist and Linq to Sql:

  1. Paged Datalist
  2. Efficient paging i.e if there are 3000 records only retrieve the records needed for the current page.
  3. Have the page number displayed in the querystring to allow pages to be cached.

However, when I look at the sql which is outputted it is doing multiple data calls for each page (see below).

So my questions are:

  1. Why are there 2 calls for the number of records?
  2. Why is the first 10 records returned initially?
  3. How can I set this up to only have 2 datacalls?

My Code

<asp:ListView ID="lv" runat="server" DataSourceID="ds">
 <LayoutTemplate>
   <asp:PlaceHolder runat="server" ID="itemPlaceHolder" />
 </LayoutTemplate>
 <ItemTemplate>
  <p>
   <b>
    <asp:Literal ID="Literal2" runat="server" Text='<%# Eval("Submitted") %>' />
   </b>
   <br />
   <asp:Literal ID="Literal1" runat="server" Text='<%# Eval("Text") %>' />
  </p>
 </ItemTemplate>

</asp:ListView>

<asp:DataPager runat="server" ID="dp" PagedControlID="lv" QueryStringField="page">
<Fields>

 <asp:NextPreviousPagerField ButtonType="Button" ShowFirstPageButton="True" 
  ShowNextPageButton="true" ShowPreviousPageButton="False" />
 <asp:NumericPagerField />
 <asp:NextPreviousPagerField ButtonType="Button" ShowLastPageButton="True" 
  ShowNextPageButton="true" ShowPreviousPageButton="False" />

</Fields>
</asp:DataPager>

This is the ouputted sql for page 8 SELECT COUNT(*) AS [value] FROM [dbo].[Entries] AS [t0]

SELECT TOP (10) [t0].[Submitted], [t0].[Text] FROM [dbo].[Entries] AS [t0] ORDER BY [t0].[Submitted]

SELECT COUNT(*) AS [value] FROM [dbo].[Entries] AS [t0]

SELECT [t1].[Submitted], [t1].[Text] FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [t0].[Submitted]) AS [ROW_NUMBER], [t0].[Submitted], [t0].[Text] FROM [dbo].[Entries] AS [t0] ) AS [t1] WHERE [t1].[ROW_NUMBER] BETWEEN 70 + 1 AND 70 + 10 ORDER BY [t1].[ROW_NUMBER]