Sunday, December 25, 2011

ASP.NET Gridview with custom paging and sorting.

When displaying large amounts of data it's often best to only display a portion of the data, allowing the user to step through the data ten or so records at a time. Additionally, the end user's experience can be enhanced if they are able to sort the data by one of the columns.
While default paging was easy to implement, it carried with it a performance cost since all records to be paged through were being returned from the database. That is, if the DataGrid was paging through a total of 1,000 records, showing 10 records per page, on each and every page request all 1,000 records would be returned from the database, but only the 10 appropriate ones would be displayed.
Custom paging solved this performance issue by requiring the page developer to tell the DataGrid exactly how many total records were being paged through as well as returning the precise subset of records to display on the page. The following example use ObjectDataSource for DAL and Microsoft.Practices.EnterpriseLibrary.Data.dll (download here) to shorten code.

Gridview with custom paging and sorting.
//Here is server control.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
    AllowPaging="True" AllowSorting="True" AlternatingRowStyle-Wrap="True" 
    DataSourceID="ObjectDataSource1" EmptyDataText="There is no data.">
    <RowStyle BackColor="#EFF3FB" />
    <Columns>
        <asp:BoundField HeaderText="No." DataField="No" />
        <asp:BoundField HeaderText="CustomerID" DataField="CustomerID" SortExpression="CustomerID"/>
        <asp:BoundField HeaderText="CompanyName" DataField="CompanyName" SortExpression="CompanyName" />
        <asp:BoundField HeaderText="ContactName" DataField="ContactName" SortExpression="ContactName" />
        <asp:BoundField HeaderText="ContactTitle" DataField="ContactTitle" SortExpression="ContactTitle" />
    </Columns>
    <AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GridDataPage"
    TypeName="DAL" SelectCountMethod="DataRowCount" 
    SortParameterName="SortExpression" EnablePaging="True">
    <SelectParameters>
        <asp:Parameter Name="maximumRows" Type="Int32" />
        <asp:Parameter Name="startRowIndex" Type="Int32" />
        <asp:Parameter Name="SortExpression" Type="String" />
    </SelectParameters>
</asp:ObjectDataSource>
Here is the connection string in web.config and it use sample "Northwind" db (download here) and "Customers" table.
<connectionStrings>
    <add name="sqlConn" providerName="System.Data.SqlClient"  connectionString="Server='.\SQLEXPRESS';uid='xxxxxx';pwd='xxxxxxxx';Database='Northwind';Pooling=False;"/>
</connectionStrings>
Here is the underlying DAL class for ObjectDataSource.
using System.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;
public class DAL
{
    // Methods
    private static Database getDatabase()
    {
        Database database = null;
        try
        {
            database = DatabaseFactory.CreateDatabase("sqlConn");
        }
        catch (Exception ex)
        {
            //handle exception here.
        }
        return database;
    }
    public static DataSet GridDataPage(int maximumRows, int startRowIndex, string SortExpression)
    {
        DataSet ds = new DataSet();
        if (startRowIndex >= maximumRows)
        {
            startRowIndex++;
        }
        try
        {
            ds = getGridData(maximumRows, startRowIndex, SortExpression);
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                startRowIndex = (startRowIndex > 0) ? startRowIndex : 1;
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    ds.Tables[0].Rows[i][0] = Convert.ToString((int)(i + startRowIndex));
                }
                HttpContext.Current.Items["rowCount"] = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
                return ds;
            }
            HttpContext.Current.Items["rowCount"] = 0;
        }
        catch (Exception ex)
        {
            //handle exception here.
        }
        return ds;
    }
    public static DataSet getGridData(int maximumRows, int startRowIndex, string SortExpression)
    {
        DataSet ds = new DataSet();
        try
        {
            ds = getDatabase().ExecuteDataSet("GetCustomers", new object[] { maximumRows, startRowIndex, SortExpression });
        }
        catch (Exception ex)
        {
            ds = null;
            //handle exception here.
        }
        return ds;
    }
    public static int DataRowCount(int maximumRows, int startRowIndex, string SortExpression)
    {
        return (int)HttpContext.Current.Items["rowCount"];
    }
}
//Here is transact-sql.
USE [Northwind]
GO
ALTER PROCEDURE [dbo].[GetCustomers]
    @pageSize INT = NULL,
    @pageStart INT = NULL,
    @orderBy nvarchar(200) = NULL
AS
BEGIN

DECLARE @sqlPopulate VARCHAR(2000)
IF @pageSize IS NULL or @pageSize = 0
    SET @pageSize = 10;
IF @pageStart IS NULL
    SET @pageStart = 0;
IF @orderBy IS NULL OR @orderBy = '' 
    SET @orderBy = 'CustomerID DESC';
IF @pageStart = 0
BEGIN
    SET @sqlPopulate = 'SELECT '' '' AS No, CustomerID, CompanyName, ContactName, ContactTitle ' +
        + ' FROM dbo.Customers WHERE CustomerID IN '+
        '(SELECT top '+ CAST(@pageSize AS VARCHAR(10)) +' CustomerID FROM dbo.Customers ';

    SET @sqlPopulate = @sqlPopulate + ' ORDER BY ' + @orderBy +
        ') ORDER BY ' + @orderBy;
END
ELSE
BEGIN
    SET @sqlPopulate = 'SELECT '' '' AS No, CustomerID, CompanyName, ContactName, ContactTitle ' +
        ' FROM Customers WHERE CustomerID IN '+
        '(SELECT top '+ CAST(@pageSize AS VARCHAR(10)) + ' CustomerID FROM Customers WHERE CustomerID NOT IN '+
            '(SELECT top '+ CAST((@pageStart-1) AS VARCHAR(10)) +' CustomerID FROM Customers ';

    SET @sqlPopulate = @sqlPopulate + ' ORDER BY ' + @orderBy +
            ') ORDER BY ' + @orderBy + ') ORDER BY ' + @orderBy;
END
EXEC(@sqlPopulate)

SET @sqlPopulate = '';
SET @sqlPopulate = 'SELECT COUNT(*) FROM Customers';
EXEC (@sqlPopulate)
END 
Useful link:
Microsoft Enterprise Library

Similar Article

That's it. Enjoy!

No comments:

Post a Comment