Optimize Large Recordset Custom Paging using ObjectDataSource, ListView, DataPager ASP .Net Controls and Row_number() SQL Server function

Introduction
Retrieve parts of recordset on web request is a very common task. Trivial method using SqlDataSource as Data Connector and GridView or List View with Data Pager Control to view recordset and its page.

Web request resulting million records will degrade performance even though developer break down onto pages. According to some experiments there is a better way than usual method to do web paging.
Please check these links http://www.4guysfromrolla.com/articles/031506-1.aspx, http://www.4guysfromrolla.com/webtech/042606-1.shtml

The best performance using record ranking method and fortunately start from SQL Server 2005 it has record ranking function Row_Number().
In earlier post about SQL Server Row_Number() function, I have explained how to code and the function’s result on sql.

To do custom paging on web, we need ObjectDataSource because it has attributes like EnablePaging, SelectMethod, SelectCountMethod, MaximumRowsParameterName, etc which are enabled us to write a code / class that handles web paging behaviour.
We can write any database operation on methods inside ObjectDataSource so that makes ObjectDataSource flexible.
Other common component on ASP .Net like SqlDataSource is only work for SQL Server and does not has default custom paging capabilities.

Implementation
In this example, I use AdventureWorks database on SQL Server 2005 also I am using VS 2010 Express. The Web project shows Store Type Customer Name, Territory and Sales Info.
Here is the example code (Default.aspx):

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
         <asp:ListView ID="lstview" runat="server" DataSourceID="store_customer">
        <LayoutTemplate>
            <table>
            <thead>
                <tr>
                    <th>Store Name</th>
                    <th>Territory Name</th>
                    <th>Sales Value</th>
                </tr>
            </thead>
             <asp:PlaceHolder ID="itemplaceholder" runat="server"></asp:PlaceHolder>
            </table>
        </LayoutTemplate>
        <ItemTemplate>
            <tr>
            <td><%# Eval("StoreName") %></td><td><%# Eval("TerritoryName")%></td>
            <td><%# Eval("SalesValue")%></td>
            </tr>
        </ItemTemplate>
    </asp:ListView>
    <asp:DataPager id="dtpager" runat="server" PagedControlID="lstview" PageSize="10">
        <Fields>
            <asp:NextPreviousPagerField ButtonType="Link" ShowFirstPageButton="true"
              ShowNextPageButton="false" ShowPreviousPageButton="false" FirstPageText="First" />
            <asp:NumericPagerField ButtonType="Link" ButtonCount="10" />
            <asp:NextPreviousPagerField ButtonType="Link" ShowLastPageButton="true"
               ShowNextPageButton="false" ShowPreviousPageButton="false" LastPageText="Last" />
        </Fields>
    </asp:DataPager>
        <asp:ObjectDataSource ID="store_customer" runat="server" TypeName="Customer" SelectMethod="GetStoreCustomer" EnablePaging="true"
           StartRowIndexParameterName="startrows" MaximumRowsParameterName="pagesize" SelectCountMethod="GetStoreCount" >

        </asp:ObjectDataSource>

    </div>
    </form>
</body>
</html>

So here I use ListView Control to show result. In general, ListView has DataSourceID attribute to relate this to specific data container ID which is our ObjectDataSource.
It also has LayoutTemplate tag that contains grand design of output html source from ListView control. ItemTemplate tag contains design of output html of recordset items value.

AspDataPager Control creates page numbers. It has PagedControlID to relate AspDataPager to specific ListView ID, PageSize to control how many records are displayed on page.
Under Fields Tag we can control the output of page numbers.

ObjectDataSource has several attributes like ID attribute, TypeName is a Class Name in our code, SelectMethod is a code method name of sql select statement, EnablePaging to set paging or not, StartRowIndexParameterName is a start record number parameter included as argument on Select method,
MaximumRowsParameterName is a pagesize parameter, SelectCountMethod is a method name to calculate count of records.

Create a class to work with database and write below code (under App_Code folder, Customer.cs):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

public class Customer
{
    private string connectionstring;
	public Customer()
	{
        connectionstring = @"Data Source=.\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=True";

	}

    public DataTable GetStoreCustomer(int startrows, int pagesize)
    {
        int endrows;
        //startrows starts from zero
        startrows = startrows + 1;
        endrows = startrows + (pagesize-1);

        SqlConnection conn = new SqlConnection(connectionstring);
        conn.Open();
        String query;
         query = "select * from ( " +
                "select row_number() over(order by Sales.Store.Name) as rowno, Sales.Store.Name as StoreName, Sales.SalesTerritory.Name as TerritoryName, sum(Sales.SalesOrderHeader.TotalDue) as SalesValue from Sales.Customer " +
                "inner join Sales.Store on Sales.Customer.CustomerID = Sales.Store.CustomerID " +
                "inner join Sales.SalesTerritory on Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID " +
                "inner join Sales.SalesOrderHeader on Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID " +
                "where CustomerType='S' " +
                "group by Sales.Store.Name,Sales.SalesTerritory.Name " +
                ") as temp where rowno between " + startrows + " and " + endrows +
                "order by StoreName";

        SqlDataAdapter da = new SqlDataAdapter(query, conn);
        DataTable dt = new DataTable();
        da.Fill(dt);
        da.Dispose();
        conn.Close();
        return dt;
    }

    public int GetStoreCount()
    {
        SqlConnection conn = new SqlConnection(connectionstring);
        conn.Open();
        String query = "Select count(*) from Sales.Customer " +
                "inner join Sales.Store on Sales.Customer.CustomerID = Sales.Store.CustomerID " +
                "inner join Sales.SalesTerritory on Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID " +
                "inner join Sales.SalesOrderHeader on Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID " +
                "where CustomerType='S'";

        SqlCommand comm = new SqlCommand(query, conn);
        Int32 cnt = Convert.ToInt32(comm.ExecuteScalar());
        comm.Dispose();
        conn.Close();
        return cnt;
    }
}

Customer Class Name is a ObjectDataSource Attribute TypeName.
This Customer class has GetStoreCustomer method that return DataTable. Inside this method we have sql code row_number() and record number filter. GetStoreCustomer method name must exactly same on ObjectDataSource SelectMethod attribute.
startrows argument is ObjectDataSource’s StartRowIndexParameterName and pagesize argument is MaximumRowsParameterName.
GetStoreCount is ObjectDataSource’s SelectCountMethod.

AspDataPager PageSize Value automatically relate with ObjectDataSource MaximumRowsParameterName. So those arguments in GetStoreCustomer is supplied automatically, we don’t need to specify those values on code.

Try to run the project.

Regards,
Agung Gugiaji

Advertisements

2 responses to “Optimize Large Recordset Custom Paging using ObjectDataSource, ListView, DataPager ASP .Net Controls and Row_number() SQL Server function

  1. Pingback: Cancelling DataAdapter.Fill Method On The Fly By Throwing An Error | Enlighten Application Developer Journals

  2. it’s work perfect but how can I send another parameter to query for example categoryID=@cID on the example project?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s