How to do virtual paging with ASP.NET, AJAX and SQL Server 2005

This sample provides a way to avoid full postback of the page, just updating the grid that contains the data.

The first step is create the stored procedure, for that I will use CTE (Common Table Expressions), which is a new feature of SQL Server 2005. The stored procedure will query the table from one specific row to other, it will receive a parameter to filter rows and it will return the count of rows. The stored procedure is the following:

CREATE PROCEDURE [Production].[GetProductsByName]
    @Name nvarchar(50),
    @RowSince int,
    @RowUntil int,
    @RowCount int OUTPUT
AS
SET NOCOUNT ON;
WITH [ProductCte] AS (
    SELECT 
        [RowNumber] = ROW_NUMBER() OVER(ORDER BY [Name]),
        [ProductID],
        [Name],
        [ProductNumber],
        [StandardCost],
        [ListPrice]
     FROM 
        [Production].[Product]
     WHERE 
        [Name] LIKE @Name
)
SELECT 
    [ProductID],
    [Name],
    [ProductNumber],
    [StandardCost],
    [ListPrice]
FROM 
    [ProductCte]
WHERE 
    [RowNumber] BETWEEN @RowSince AND @RowUntil
ORDER BY 
    [Name]

SET @RowCount = (SELECT COUNT(*)
                   FROM [Production].[Product]
                  WHERE [Name] LIKE @Name)

The most important above is the function ROW_NUMBER() which generate a virtual row number based in the sort of the column "Name".

Then I will implement two classes in order to retrieve the rows from the database.

The first class is a DTO which is named "Product" and the second one is "ProductService", it has two methods called "GetByName" and "CountByName" that do the work. "GetByName" receive name, maximunRows and startRowIndex as parameters and returns an ICollection<Product> with the page corresponding to the sent parameters.

The names maximunRows and startRowIndex are the names by default that the ASP.NET ObjectDataSource use, the names can be changed using the properties MaximumRowsParameterName and StartRowIndexParameterName.

The method "CountByName" has to receive exactly the same parameters that "GetByName" receive (except maximunRows and startRowIndex), in this case it only receives de parameter name.

Here is the code for the two methods:

int recordCount;

public int CountByName(string name)
{
    return recordCount;
}

public ICollection<Product> GetByName(string name, int maximumRows,
        int startRowIndex)
{
    using (DbCommand command = database.GetStoredProcCommand(
            "Production.GetProductsByName"))
    {
        database.AddInParameter(command, "Name", DbType.String,
                ParseLike(name) + "%");
        database.AddInParameter(command, "RowSince", DbType.Int32,
                startRowIndex + 1);
        database.AddInParameter(command, "RowUntil", DbType.Int32,
                startRowIndex + maximumRows);
        database.AddOutParameter(command, "RowCount", DbType.Int32,
                sizeof(int));
        ICollection<Product> products = new List<Product>();
        using (IDataReader reader = database.ExecuteReader(command))
        {
            while (reader.Read())
            {
                products.Add(GetProductFromRecord(reader));
            }
        }
        recordCount = (int)database.GetParameterValue(command, "RowCount");
        return products;
    }
}

The last thing to do is the ASP page where we will need a TextBox (it will be the name parameter), an ObjectDataSource (it will be bind to the class "ProductService"), a Button (it will call the method without postback) and a GridView (it will show the data).

Finally we will need add a ScriptManager and an UpdatePanel from ASP.NET AJAX. It's very important that the GridView and the Button be inside the UpdatePanel in order to support light postbacks. After that, we add an AsyncPostBackTrigger in the Triggers collection of the UpdatePanel related with the click event of the Button.

The code for ASP.NET:

<asp:ScriptManager ID="scriptManager" runat="server" />
<asp:ObjectDataSource ID="productsDataSource" runat="server"
    SelectMethod="GetByName"  SelectCountMethod="CountByName"
    TypeName="ProductService" EnablePaging="True">
    <SelectParameters> 
        <asp:ControlParameter ControlID="name" Name="name" 
            PropertyName="Text" Type="String" /> 
    </SelectParameters> 
</asp:ObjectDataSource>
<asp:UpdatePanel ID="updatePanel" runat="server">  
    <ContentTemplate>  
        <div>  
            <asp:Label ID="nameLabel" runat="server"
                Text="Products begining with:" />  
            <asp:TextBox ID="name" runat="server" />  
            <asp:Button ID="search" runat="server" Text="Search"
                OnClick="OnSearchClick" />  
        </div>  
        <asp:GridView ID="productsView" runat="server"
            DataSourceID="productsDataSource"  Width="100%"
            AllowPaging="true" AutoGenerateColumns="False"
            EmptyDataText="There are not records in the result set."
            DataKeyNames="ProductId" PagerSettings-Mode="NumericFirstLast" 
            PagerSettings-Position="TopAndBottom">  
            <Columns>  
                <asp:BoundField DataField="Name"
                    HeaderText="Name" />  
                <asp:BoundField DataField="ProductNumber"
                    HeaderText="Number" /> 
                <asp:BoundField DataField="StandardCost"
                    HeaderText="Cost" ItemStyle-HorizontalAlign="Right" /> 
                <asp:BoundField DataField="ListPrice"
                    HeaderText="Price"  ItemStyle-HorizontalAlign="Right" /> 
            </Columns>  
        </asp:GridView> 
    </ContentTemplate>
    <Triggers> 
        <asp:AsyncPostBackTrigger ControlID="search" EventName="Click" />  
    </Triggers>
</asp:UpdatePanel>

I attached the solution, in order to run it, you will need Enterprise Library 3.1 and Adventure Works Database Samples.

You will need create the stored procedure wich is attached to the solution. The complete sample is here.