How to dynamically page through a large result set in ASP.NET by using SQL Server stored procedures in Visual C# .NET

Summary

This step-by-step article describes how to use SQL stored procedures to dynamically page through a large result set in Microsoft ASP.NET.

back to the top

Methods of implementation

There are three main paging techniques that you can use in ASP.NET to display a limited subset of a result set and to scroll through this limited subset.

These techniques, together with their advantages and disadvantages, are as follows:
  1. The first technique is to select the whole result set each time, and to discard the records that you do not want to display. This is the least efficient paging technique.
  2. The second technique is useful if the result set is expensive to generate. In this technique, you store the result set (or the primary key values) in a cache, either by using a session variable in the ASP.NET process, or in another table in the database, and then read the appropriate rows from the result set. This storage-intensive technique works best if the result set is rather small because it is a good idea to implement a mechanism to time-out the data. Another disadvantage of this technique is that the data can become stale.
  3. The third technique is to dynamically change the query to select only the records that are required for the next page of data. This technique is demonstrated by using the sample code that is presented in this article. The salient features of this technique are as follows:
    • You can dynamically set the page size.
    • You can filter the data.
    • You use stored procedures instead of dynamic SQL to restrict access to the tables.
    • The result is sorted on multiple fields and not on the primary key.
    • The sort fields can contain duplicates.
back to the top

Paging by dynamically altering the SQL query

The following sample application illustrates paging through student records that are sorted by student name, last name, first name, and middle initial. The page size can vary between 25, 100, and 500 records.

Note Users with slower connections can select a smaller page size, while those on a local area network (LAN) can select a larger page size.

back to the top

Create a table schema

The following SQL query creates the basic table schema. The SchoolID field and the AreaID field are used for filtering results to a particular school or to a particular school district. To create the table schema, follow these steps:
  1. Click Start.
  2. Point to Programs, point to
    Microsoft SQL Server, and then click Query Analyzer.
  3. Select
    sqlServername in the
    SQL Server box. Where sqlServername is the name of your SQL Server server
  4. Type you loginName and you password in the corresponding text boxes, and then click OK.
  5. On the Query tab, click Change Database.
  6. Select the Northwind database, and then click OK.
  7. Copy the following code to the Query text box:
    CREATE TABLE [dbo].[Students] (
    [StudentID] [int] IDENTITY (1, 1) NOT NULL ,
    [AreaID] [int] NULL ,
    [SchoolID] [int] NULL ,
    [TeacherID] [int] NULL ,
    [FirstName] [varchar] (20) NOT NULL ,
    [MI] [varchar] (5) NULL ,
    [LastName] [varchar] (20) NOT NULL ,
    [Address] [varchar] (50) NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[Students] WITH NOCHECK ADD
    CONSTRAINT [PK_Students] PRIMARY KEY NONCLUSTERED
    (
    [StudentID]
    ) ON [PRIMARY]
    GO
  8. On the Query tab, click
    Execute.
back to the top

Create stored procedures

  1. To create the NextStudentPage stored procedure, copy the following code to the
    Query text box of Query Analyzer:
    CREATE PROCEDURE NextStudentPage

    @SID INT,
    @PageSize INT = 1,
    @AreaID INT = NULL,
    @SchoolID INT = NULL
    AS

    DECLARE @LName VARCHAR(20)
    DECLARE @FName VARCHAR(20)
    DECLARE @MI VARCHAR(5)

    /*
    Locate additional parameter values for the last row of the current page.

    You must do this because an ASP.NET Datagrid control only stores
    key values and does not store additional field values that you need.
    */

    IF @SID IS NULL
    SELECT @LName='', @FName='', @MI='', @SID=0
    ELSE
    SELECT TOP 1 @LName = LastName, @FName = FirstName, @MI = MI
    FROM STUDENTS
    WHERE StudentID = @SID

    /* RESTRICT THE NUMBER OF ROWS RETURNED. */

    SET ROWCOUNT 25
    IF @PageSize=2 SET ROWCOUNT 100
    IF @PageSize=3 SET ROWCOUNT 500

    /* Select the next page of data. */

    SELECT * FROM STUDENTS
    WHERE ((LastName>@LName)
    OR (LastName=@LName AND FirstName>@FName)
    OR (LastName=@LName AND FirstName=@FName AND MI>@MI)
    OR (LastName=@LName AND FirstName=@FName AND MI=@MI AND StudentID>@SID))
    AND (@AreaID IS NULL OR AreaID=@AreaID)
    AND (@SchoolID IS NULL OR SchoolID=@SchoolID)
    ORDER BY LastName, FirstName, MI, StudentID

    /* TURN OFF THE ROWCOUNT LIMIT. */

    SET ROWCOUNT 0
  2. On the Query tab, click
    Execute.
  3. To create the PrevStudentPage stored procedure, copy the following code to the
    Query text box of Query Analyzer:
    CREATE PROCEDURE PrevStudentPage

    @SID INT,
    @PageSize INT = 1,
    @AreaID INT = NULL,
    @SchoolID INT = NULL
    AS

    DECLARE @LName VARCHAR(20)
    DECLARE @FName VARCHAR(20)
    DECLARE @MI VARCHAR(5)

    /*
    Locate additional parameter values for the first row of the current page.

    You must do this because an ASP.NET Datagrid control only stores
    key values and does not store additional field values that you need.
    */

    IF @SID IS NULL
    /* SELECT A VALUE BEYOND THE LAST RECORD. */
    SELECT TOP 1 @LName = LastName, @FName = FirstName, @MI = MI, @SID = StudentID + 1
    FROM STUDENTS
    ORDER BY LastName DESC, FirstName DESC, MI DESC, StudentID DESC
    ELSE
    SELECT TOP 1 @LName = LastName, @FName = FirstName, @MI = MI
    FROM STUDENTS
    WHERE StudentID = @SID

    /* Restrict the number of rows returned. */

    SET ROWCOUNT 25
    IF @PageSize=2 SET ROWCOUNT 100
    IF @PageSize=3 SET ROWCOUNT 500

    /* Select the previous page of data - This returns in descending order. */

    SELECT * INTO #TempStudent FROM STUDENTS
    WHERE ((LastName<@LName)
    OR (LastName=@LName AND FirstName<@FName)
    OR (LastName=@LName AND FirstName=@FName AND MI<@MI)
    OR (LastName=@LName AND FirstName=@FName AND MI=@MI AND StudentID<@SID))
    AND (@AreaID IS NULL OR AreaID=@AreaID)
    AND (@SchoolID IS NULL OR SchoolID=@SchoolID)
    ORDER BY LastName DESC, FirstName DESC, MI DESC, StudentID DESC

    /* Reorder the records in ascending order. */

    SELECT * FROM #TempStudent ORDER BY LastName, FirstName, MI, StudentID

    /* Clean up the database. */

    SET ROWCOUNT 0
    DROP TABLE #TempStudent
  4. On the Query tab, click
    Execute.
back to the top

Create an ASP.NET Web application

  1. Start Microsoft Visual Studio .NET.
  2. On the File menu, point to New, and then click Project.
  3. Click Visual C# Projects under Project Types, and then click ASP.NET Web Application under Templates.
  4. Name the project PagingTest. By default, WebForm1.aspx is created.
  5. Right-click WebForm1.aspx, and then click View HTML Source.
  6. Replace the existing code with the following code:
    <%@ Page language="c#" Codebehind="WebForm1.aspx.cs" AutoEventWireup="false" Inherits="PagingTest.WebForm1" %>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
    <HTML>
    <HEAD>
    <title>WebForm1</title>
    </HEAD>
    <body MS_POSITIONING="GridLayout">
    <form id="Form1" method="post" runat="server">
    <asp:button id="Button1" style="Z-INDEX: 101; LEFT: 309px;
    POSITION: absolute; TOP: 233px" runat="server" Text="Button"></asp:button>
    <asp:label id="Label1" style="Z-INDEX: 102; LEFT: 310px;
    POSITION: absolute; TOP: 190px" runat="server">Click Button to add Records</asp:label>
    </form>
    </body>
    </HTML>
  7. Right-click WebForm1.aspx, and then click View Code.
  8. Replace the existing code with the following code:
    using System;
    using System.Collections;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Web;
    using System.Web.SessionState;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.HtmlControls;
    using System.Data.SqlClient;

    namespace PagingTest
    {
    /// <summary>
    /// Summary description for WebForm1.
    /// </summary>
    public class WebForm1 : System.Web.UI.Page
    {

    protected System.Web.UI.WebControls.Button Button1;
    protected System.Data.SqlClient.SqlConnection sqlConnection1;
    string [] Fname = new string[5] {"Jhon","Martin","Rob","Leo","Amey"};
    string [] Lname = new string[5] {"Thompson","McMillan","Rob","King","Starr"};
    string [] MI = new string[5] {"M","R","B","K","P"};
    int AreaIDv;
    int SchoolIDv;
    int TeacherIDv;
    string Addressv;
    string insertCmd ="";
    SqlCommand myCommand;
    protected System.Web.UI.WebControls.Label Label1;
    SqlConnection Conn;

    private void Page_Load(object sender, System.EventArgs e)
    {
    Label1.Visible=true;
    //string Fname1=Fname[2];
    }

    #region Web Form Designer generated code
    override protected void OnInit(EventArgs e)
    {
    //
    // CODEGEN: The ASP.NET Web Form Designer needs this call.
    //
    InitializeComponent();
    base.OnInit(e);
    }

    /// <summary>
    /// Required method for Designer support - do not modify
    /// the contents of this method with the code editor.
    /// </summary>
    private void InitializeComponent()
    {
    this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
    this.Button1.Click += new System.EventHandler(this.Button1_Click);
    //
    // sqlConnection1
    //
    this.sqlConnection1.ConnectionString = "data source= Servername;initial catalog=pubs;" +
    "persist security info=False;user id=sa;packet size=4096";
    this.Load += new System.EventHandler(this.Page_Load);

    }
    #endregion

    private void Button1_Click(object sender, System.EventArgs e)
    {
    Conn = new SqlConnection("server= Servername;uid=sa;pwd=sa;database=Northwind;");
    Conn.Open();
    for(int i=0;i<1000;i++)
    {
    Random rn = new Random();
    int j=0;
    j = rn.Next(1,5);
    AreaIDv = rn.Next(1,10);
    TeacherIDv=rn.Next(10,20);
    SchoolIDv=rn.Next(1,10);
    Addressv = rn.Next(101, 1999) + " " + " St.";

    insertCmd = "insert into Students (FirstName,LastName,MI,AreaID,SchoolID,TeacherID,Address)"+
    "values ('"+Fname[j]+"','"+Lname[j]+"','"+MI[j]+"',"+AreaIDv+","+SchoolIDv+","+
    ""+TeacherIDv+",'"+Addressv+"')";

    myCommand = new SqlCommand(insertCmd, Conn);
    myCommand.ExecuteNonQuery();
    }
    Conn.Close();
    Label1.Text="Records are Added in Database";
    }
    }
    }
  9. On the Build menu, click Build Solution.
  10. In Solution Explorer, right-click
    PagingTest, point to
    Add, and then click Add Web Form.
  11. Type Paging.aspx in the
    Name text box, and then click
    Open.
  12. Right-click Paging.aspx, and then click View HTML Source.
  13. Replace the existing code with the following code:
    <%@ Page language="c#" Codebehind="Paging.aspx.cs" AutoEventWireup="false" Inherits="PagingTest.WebForm2" %>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
    <HTML>
    <HEAD>
    <title>Paging</title>
    </HEAD>
    <body MS_POSITIONING="GridLayout">
    <form id="WebForm2" method="post" runat="server">
    <asp:datagrid id="DataGrid1" style="Z-INDEX: 100; LEFT: 186px;
    POSITION: absolute; TOP: 265px" runat="server"></asp:datagrid>
    <asp:label id="Label3" style="Z-INDEX: 111; LEFT: 5px;
    POSITION: absolute; TOP: 117px" runat="server">
    Enter 2 - for Page length 100 lines . 3 for Page length 500 lines </asp:label>
    <asp:label id="Label2" style="Z-INDEX: 110; LEFT: 7px;
    POSITION: absolute; TOP: 91px" runat="server">
    Enter School Code to Filter</asp:label>
    <asp:textbox id="txtArea" style="Z-INDEX: 101; LEFT: 211px;
    POSITION: absolute; TOP: 38px" runat="server" Width="52px"></asp:textbox>
    <asp:textbox id="txtSchool" style="Z-INDEX: 102; LEFT: 210px;
    POSITION: absolute; TOP: 85px" runat="server" Width="52px"></asp:textbox>
    <asp:textbox id="txtPageSize" style="Z-INDEX: 103; LEFT: 209px;
    POSITION: absolute; TOP: 144px" runat="server" Width="54px"></asp:textbox>
    <asp:label id="lblEOF" style="Z-INDEX: 104; LEFT: 344px;
    POSITION: absolute; TOP: 225px" runat="server">
    No records available for this query</asp:label>
    <asp:button id="btnFirst" style="Z-INDEX: 105; LEFT: 176px;
    POSITION: absolute; TOP: 175px" runat="server" Text="ButtonFirst"></asp:button>
    <asp:button id="btnNext" style="Z-INDEX: 106; LEFT: 293px;
    POSITION: absolute; TOP: 177px" runat="server" Text="ButtonNext"></asp:button>
    <asp:button id="btnPrev" style="Z-INDEX: 107; LEFT: 419px;
    POSITION: absolute; TOP: 176px" runat="server" Text="ButtonPrev"></asp:button>
    <asp:button id="btnLast" style="Z-INDEX: 108; LEFT: 551px;
    POSITION: absolute; TOP: 178px" runat="server" Text="ButtonLast"></asp:button>
    <asp:Label id="Label1" style="Z-INDEX: 109; LEFT: 8px;
    POSITION: absolute; TOP: 35px" runat="server">Enter Area Code to Filter</asp:Label>
    </form>
    </body>
    </HTML>
  14. Right-click Paging.aspx, and then click View Code.
  15. Replace the existing code with the following code:
    using System;
    using System.Collections;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Web;
    using System.Web.SessionState;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.HtmlControls;
    using System.Data.SqlClient;

    namespace PagingTest
    {
    /// <summary>
    /// Summary description for Paging.
    /// </summary>
    public class Paging : System.Web.UI.Page
    {
    protected System.Web.UI.WebControls.TextBox txtArea;
    protected System.Web.UI.WebControls.TextBox txtSchool;
    protected System.Web.UI.WebControls.TextBox txtPageSize;
    protected System.Web.UI.WebControls.Label lblEOF;
    protected System.Web.UI.WebControls.Button btnFirst;
    protected System.Web.UI.WebControls.Button btnNext;
    protected System.Web.UI.WebControls.Button btnPrev;
    protected System.Web.UI.WebControls.Button btnLast;
    protected System.Web.UI.WebControls.DataGrid DataGrid1;

    SqlConnection cnNwind;
    SqlCommand cmdNext ;
    protected System.Web.UI.WebControls.Label Label1;
    protected System.Web.UI.WebControls.Label Label2;
    protected System.Web.UI.WebControls.Label Label3;
    SqlCommand cmdPrev ;

    private void Page_Load(object sender, System.EventArgs e)
    {
    // Put user code to initialize the page here
    if (this.IsPostBack == false) btnFirst_Click(null,null);
    }

    private void btnFirst_Click(object sender, System.EventArgs e)
    {
    cmdNext.Parameters["@SID"].Value = System.DBNull.Value;
    cmdNext.Parameters["@PageSize"].Value = FixNumber(txtPageSize.Text);
    cmdNext.Parameters["@AreaID"].Value = FixNumber(txtArea.Text);
    cmdNext.Parameters["@SchoolID"].Value = FixNumber(txtSchool.Text);
    RetrieveData(cmdNext);
    }

    private void btnPrev_Click(object sender, System.EventArgs e)
    {
    if (DataGrid1.DataKeys.Count == 0)
    cmdPrev.Parameters["@SID"].Value = System.DBNull.Value;
    else
    cmdPrev.Parameters["@SID"].Value = (int)(DataGrid1.DataKeys[0]);

    cmdPrev.Parameters["@PageSize"].Value = FixNumber(txtPageSize.Text);
    cmdPrev.Parameters["@AreaID"].Value = FixNumber(txtArea.Text);
    cmdPrev.Parameters["@SchoolID"].Value = FixNumber(txtSchool.Text);
    RetrieveData(cmdPrev);
    }

    private void btnNext_Click(object sender, System.EventArgs e)
    {
    int Count;
    Count = DataGrid1.DataKeys.Count;

    if (Count == 0)
    cmdNext.Parameters["@SID"].Value = System.DBNull.Value;
    else
    cmdNext.Parameters["@SID"].Value = (int)(DataGrid1.DataKeys[Count - 1]);

    cmdNext.Parameters["@PageSize"].Value = FixNumber(txtPageSize.Text);
    cmdNext.Parameters["@AreaID"].Value = FixNumber(txtArea.Text);
    cmdNext.Parameters["@SchoolID"].Value = FixNumber(txtSchool.Text);
    RetrieveData(cmdNext);
    }

    private void btnLast_Click(object sender, System.EventArgs e)
    {
    cmdPrev.Parameters["@SID"].Value = System.DBNull.Value;
    cmdPrev.Parameters["@PageSize"].Value = FixNumber(txtPageSize.Text);
    cmdPrev.Parameters["@AreaID"].Value = FixNumber(txtArea.Text);
    cmdPrev.Parameters["@SchoolID"].Value = FixNumber(txtSchool.Text);
    RetrieveData(cmdPrev);
    }

    private object FixNumber(string inValue)
    {
    int Value;
    try
    {
    Value = int.Parse(inValue);
    return Value;
    }
    catch
    {
    return System.DBNull.Value;
    }
    }

    private void RetrieveData(SqlCommand cmd)
    {
    SqlDataReader dr;
    try
    {
    cnNwind.Open();
    dr = cmd.ExecuteReader();
    DataGrid1.DataSource=dr;
    DataGrid1.DataKeyField="StudentID";
    DataGrid1.DataBind();
    if (DataGrid1.Items.Count>0)
    {
    DataGrid1.Visible=true;
    lblEOF.Visible=false;
    }
    else
    {
    DataGrid1.Visible=false;
    lblEOF.Visible=true;
    }
    dr.Close();
    }
    catch(Exception e1)
    {
    Response.Write(e1.ToString());
    // display error message in a label control
    // must be made invisible in the try block
    }
    finally
    {
    if (cnNwind.State != ConnectionState.Closed) cnNwind.Close();
    }
    }

    #region Web Form Designer generated code
    override protected void OnInit(EventArgs e)
    {
    //
    // CODEGEN: The ASP.NET Web Form Designer needs this call.
    //
    InitializeComponent();
    base.OnInit(e);
    }

    /// <summary>
    /// Required method for Designer support - do not modify
    /// the contents of this method with the code editor.
    /// </summary>
    private void InitializeComponent()
    {
    this.cnNwind = new System.Data.SqlClient.SqlConnection();
    this.cmdNext = new System.Data.SqlClient.SqlCommand();
    this.cmdPrev = new System.Data.SqlClient.SqlCommand();
    //
    // cnNwind
    //
    this.cnNwind.ConnectionString = "server=servername ;uid=sa;pwd=sa;database=Northwind;";
    //
    // cmdNext
    //
    this.cmdNext.CommandText = "[NextStudentPage]";
    this.cmdNext.CommandType = System.Data.CommandType.StoredProcedure;
    this.cmdNext.Connection = this.cnNwind;
    this.cmdNext.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
    System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false,
    ((System.Byte)(10)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
    this.cmdNext.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SID",
    System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false,
    ((System.Byte)(10)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
    this.cmdNext.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PageSize",
    System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false,
    ((System.Byte)(10)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
    this.cmdNext.Parameters.Add(new System.Data.SqlClient.SqlParameter("@AreaID",
    System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false,
    ((System.Byte)(10)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
    this.cmdNext.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SchoolID",
    System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false,
    ((System.Byte)(10)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
    //
    // cmdPrev
    //
    this.cmdPrev.CommandText = "[PrevStudentPage]";
    this.cmdPrev.CommandType = System.Data.CommandType.StoredProcedure;
    this.cmdPrev.Connection = this.cnNwind;
    this.cmdPrev.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
    System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false,
    ((System.Byte)(10)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
    this.cmdPrev.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SID",
    System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false,
    ((System.Byte)(10)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
    this.cmdPrev.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PageSize",
    System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false,
    ((System.Byte)(10)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
    this.cmdPrev.Parameters.Add(new System.Data.SqlClient.SqlParameter("@AreaID",
    System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false,
    ((System.Byte)(10)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
    this.cmdPrev.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SchoolID",
    System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false,
    ((System.Byte)(10)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
    this.btnFirst.Click += new System.EventHandler(this.btnFirst_Click);
    this.btnNext.Click += new System.EventHandler(this.btnNext_Click);
    this.btnPrev.Click += new System.EventHandler(this.btnPrev_Click);
    this.btnLast.Click += new System.EventHandler(this.btnLast_Click);
    this.Load += new System.EventHandler(this.Page_Load);

    }

    #endregion

    }
    }
  16. On the Build menu, click Build Solution.
back to the top

Populate the database

  1. Locate the WebForm1.aspx Web page by using the following URL:
    http://localhost/PagingTest/WebForm1.aspx
  2. Click Button1 to insert 1000 records in the Students table that you created in the Northwind database.
back to the top

Test dynamic paging

Locate the Paging.aspx Web page by using the following URL:
http://localhost/PagingTest/Paging.aspx
Notice that you can dynamically page through the returned records.

back to the top

References

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

318131 How to page through a query result for better performance

back to the top
Propriedades

ID do Artigo: 829142 - Última Revisão: 23 de mar de 2009 - Revisão: 1

Comentários