How To: Page Records Using AJAX

J.D. Meier, Alex Homer, David Hill, Jason Taylor, Prashant Bansode, Lonnie Wall, Rob Boucher Jr, Akshay Bogawat.

Applies To

  • ASP.NET 3.5
  • ASP.NET AJAX
  • SQL Server 2008 or SQL Server Express 2008

Summary

This How To article shows you how to create an AJAX-enabled page in a Web site that can display sets of a specified number of rows of data from a query that may contain a very large number of rows. It uses a stored procedure in SQL Server 2008 to extract just the required subset of rows from the total returned by the query, and displays these rows in a GridView control inside an AJAX UpdatePanel control. The GridView control displays links to fetch each page of rows, and loads and displays the selected page of rows without requiring a postback to the server.

Contents

  • Objectives
  • Overview
  • Summary of Steps
  • Step 1 - Create a Web Site and Prepare the Database
  • Step 2 - Create a Component to Expose the Data Items
  • Step 3 - Create a Data Access Component and a Method that Returns the Total Number of Rows
  • Step 4 - Create a Method in the Data Access Component that Exposes the Data Rows
  • Step 5 - Add and Configure an ObjectDataSource Control
  • Step 6 - Add an UpdatePanel and GridView Control
  • Additional Resources

Objectives

  • Learn how to create a SQL Server query that returns a specified subset or page of rows from a query that may select a very large number of rows.
  • Learn how to expose data as a generic list of objects that you can expose through an* ObjectDataSource* control using a data access component.
  • Learn how to use an ObjectDataSource control to execute the methods of the data access object and expose the specified page of rows.
  • Learn how to use an AJAX UpdatePanel control and a GridView control to display the pages of rows without requiring a postback to the server.

Overview

Create a stored procedure that returns specific pages of rows from a query that may return a large number of rows. Create a data access component that can extract the rows and a count of the total number of rows for a specific query. In this example, the query returns all of the rows in a specified table but you can add criteria to select rows by modifying the code. Use an ObjectDataSource control to execute the methods of the data access component and extract and expose the individual pages of rows. The* ObjectDataSource* control automatically passes the required "start" and "maximum number of rows" parameters to the data access component method that extracts the rows. Use a GridView control inside an AJAX UpdatePanel control to display the page of rows exposed by the ObjectDataSource control. The GridView control automatically manages the current page position and displays links to the other pages. The AJAX UpdatePanel control allows the GridView control to render the selected page of rows using a partial page refresh without posting the page back to the server.

Summary of Steps

  • Step 1 - Create a Web Site and Prepare the Database
  • Step 2 - Create a Component to Expose the Data Items
  • Step 3 - Create a Data Access Component and a Method that Returns the Total Number of Rows
  • Step 4 - Create a Method in the Data Access Component that Exposes the Data Rows
  • Step 5 - Add and Configure an ObjectDataSource Control
  • Step 6 - Add an UpdatePanel and GridView Control

Step 1 – Create a Web Site and Prepare the Database

This step shows you how to create a Web application project and select or create a suitable database table containing sufficient rows to see the paging feature in action. You can use any suitable existing table that contains 50 or more rows, or create a new table in an existing database.
  1. In Visual Studio 2008, click the File menu, point to New, and click Web site.
  2. In the New Web site dialog box, click ASP.NET Web Site, select a location for the new site, select the language you want to use, and then click OK.
  3. Open Server Explorer, right-click Data Connections, and click Add Connection. In the Add Connection dialog box, select a SQL Server or SQL Server Express database server and either:
    1. Select an existing database that contains a table with at least 50 rows in it. The rows must have an ID or key column of type Integer (int) and a String (varchar or nvarchar) column.
    2. Select an existing database and create a new table named Lists within it that contains a column named ListID of type int as the primary key, and a column named Name of type nvarchar(50).
  4. Create a new stored procedure named GetPagedLists in the database. The following listing shows the complete procedure. It uses the names of the columns ListID and Name specified in the previous step. If you are selecting rows from a different table you must change the column names to match those in your table:
CREATE PROCEDURE dbo.GetPagedLists 
  @StartRowIndex INT, 
  @NumRows INT 
AS
BEGIN 
  WITH ListEntries AS ( 
    SELECT ROW_NUMBER() OVER (ORDER BY ListID ASC)AS Row, ListID, [Name] 
      FROM Lists)

  SELECT ListID, [Name] FROM ListEntries 
    WHERE Row between @StartRowIndex and @StartRowIndex+@NumRows
END 
  1. Save the stored procedure in your database.
  2. If you created a new table, you must populate it with rows for the example. Add the following stored procedure to your database and execute it to create 100 rows:
CREATE PROCEDURE dbo.PopulateListsTable
AS
DECLARE @Loop int
SELECT @Loop = 0
WHILE (@Loop < 100)
  BEGIN
    INSERT INTO Lists(ListID, Name) 
      VALUES (@Loop, 'Item ' + CAST(@Loop AS nvarchar(3)))
    SELECT @Loop = @Loop + 1
  END
  1. In Visual Studio Solution Explorer, double-click the file Web.config to open it in the editor.
  2. Find the connectionStrings section and add a connection string for your database. For example, if your database is named ListDatabase located in the local SQL Server Express instance, your connection string will look like the following:
<connectionStrings>
  <add name="AJAXPagingSample" 
            connectionString="Data Source=.\SQLEXPRESS; Initial Catalog=ListDatabase;
                                              Integrated Security=True;"
            providerName="System.Data.SqlClient"/>
</connectionStrings>
  1. Save and close the Web.config file.

In this step you have created a Web site and a stored procedure that extracts a specific page of rows from a database table. In this example, the stored procedure assumes that all the rows in the table will be returned by the query. However, if required, you can add parameters to the stored procedure that you will populate in the data access layer to extract specific subsets of rows from the table. Finally, you added a connection string to the Web.config file that the data access component will use to access the database.

Step 2 – Create a Component to Expose the Data Items

This step shows you how to create a component that will expose each of the items extracted from the database so that the ObjectDataSource can expose them to the GridView control.
  1. In Solution Explorer, right-click the project item, point to Add ASP.NET Folder, and click App_Code.
  2. Right-click the new App_Code folder and click Add New Item. In the Add New Item dialog box, click Class, change the name to ListItem.cs or ListItem.vb (depending on your chosen language) and then click Add.
  3. Create the definition of a ListItem object that exposes two fields, an ID and a name. The class exposes properties for each field and a constructor that allows code to create instances of the object, as shown in the following code:
*C#*
using System;

public class ListItem 
{

  // Fields
  private int _listId;
  private string _name;

  // Properties
  public int ListId 
  {
    get { return _listId; }
    set { _listId = value; }
  }

  public string Name 
  {
    get { return _name; }
    set { _name = value; }
  }

  // Constructor
  public ListItem(int listid, string name) 
  {
    this.Name = name;
    this.ListId = listid;
  }
}

*Visual Basic*
Imports System

Public Class ListItem

  ' Fields
	  Private _listId As Integer
	  Private _name As String

	  ' Properties
	  Public Property ListId() As Integer
    Get
      Return _listId
    End Get
    Set
	      _listId = value
    End Set
	  End Property

	  Public Property Name() As String
	    Get
	      Return _name
    End Get
    Set
	      _name = value
    End Set
	  End Property

  ' Constructor
  Public Sub New(ByVal listid As Integer, ByVal name As String)
    Me.Name = name
    Me.ListId = listid
  End Sub

End Class
  1. Save and close the ListItem file.

In this step you have created the ListItem class that defines the individual objects or data items for the ObjectDataSource to expose. The individual data items consist of two values, an ID and a name. When you create the methods to extract data from the database in the next step, you will map two columns to the two properties of the ListItem class.

Step 3 – Create a Data Access Component and a Method that Returns the Total Number of Rows

This step shows you how to create a data access component containing a method that returns the total number of rows that the query you use to fetch the rows would return when not using paging. This value is required to allow the ObjectDataSource and the GridView control to interact and display the appropriate links to other pages of data.
  1. In Solution Explorer, right-click the App_Code folder and click Add New Item. In the Add New Item dialog box, click Class, change the name to DAL.cs or DAL.vb (depending on your chosen language) and then click Add.
  2. In the new class, import the namespaces you will need as shown in the following code:
*C#*
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Collections.Generic;

public class DAL
{
}

*Visual Basic*
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration
Imports System.Collections.Generic

Public Class DAL
End Class
  1. Create a method named GetListCount that returns the total number of rows in the query. In this example, you will return the total number of rows in the table, though you can pass parameters to the method if you want to select subsets of rows. The following code shows the completed method, assuming you are using a database table named Lists containing a column named ListID. If you are using a different table, specify the column name containing the ID for the rows instead:
*C#*
public int GetListCount() 
{
  string sql = "SELECT Count(ListID) As TotalRows FROM Lists";
  int rowCount = 0;
  using (SqlConnection con = new SqlConnection(
                                                   WebConfigurationManager.ConnectionStrings[
                                                   "AJAXPagingSample"].ConnectionString))
  {
    SqlCommand cmd = new SqlCommand(sql, con);
    con.Open();
    rowCount = (int)cmd.ExecuteScalar();
  }
  return rowCount;
}

*Visual Basic*
Public Function GetListCount() As Integer
  Dim sql As String = "SELECT Count(ListID) As TotalRows FROM Lists"
  Dim rowCount As Integer = 0
  Using con As New SqlConnection(WebConfigurationManager.ConnectionStrings( _
                                   "AJAXPagingSample").ConnectionString)
    Dim cmd As New SqlCommand(sql, con)
    con.Open()
    rowCount = DirectCast(cmd.ExecuteScalar(), Integer)
  End Using
  Return rowCount
End Function
  1. Save the DAL file.

In this step you have created a data access layer class that contains a method named GetListCount that will return the total number of rows returned by the query. The example uses a SQL statement to access the database, though you can implement it as a stored procedure in the database if you prefer.

Step 4 – Create a Method in the Data Access Component that Exposes the Data Rows

This step shows you how to create a method in the data access component that calls the stored procedure to fetch the required subset of rows, and return them as a generic list if items that the ObjectDataSource can expose to the GridView control. In this example, the query will return a subset of all the rows in the table, though you can pass parameters to the method if you want to select subsets of rows.
  1. In the DAL class you created in the previous step, create a method that executes the stored procedure you wrote in step 1. The stored procedure will return just the required page of rows from the database. Access these rows using a DataReader, as shown in the following code:
*C#*
public List<ListItem> GetListItems(int startRowIndex, int maximumRows) 
{
  List<ListItem> pagedList = new List<ListItem>();
  string proc = "dbo.GetPagedLists";
  using (SqlConnection con = new SqlConnection(
                                                   WebConfigurationManager.ConnectionStrings[
                                                  "AJAXPagingSample"].ConnectionString))
  {
    SqlCommand cmd = new SqlCommand(proc, con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@StartRowIndex", startRowIndex));
    cmd.Parameters.Add(new SqlParameter("@NumRows", maximumRows));
    con.Open();
    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

*Visual Basic*
Public Function GetListItems(ByVal startRowIndex As Integer, _
                                                    ByVal maximumRows As Integer) As List(Of ListItem)
  Dim pagedList As New List(Of ListItem)()
  Dim proc As String = "dbo.GetPagedLists"
  Using con As New SqlConnection(WebConfigurationManager.ConnectionStrings( _
                                   "AJAXPagingSample").ConnectionString)
    Dim cmd As New SqlCommand(proc, con)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add(New SqlParameter("@StartRowIndex", startRowIndex))
    cmd.Parameters.Add(New SqlParameter("@NumRows", maximumRows))
    con.Open()
    Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
  1. To complete the procedure, copy the data from the DataReader into the generic list of ListItem objects, and return this from the method, as shown in the following code:
*C#*
    while (dr.Read()) 
    {
      pagedList.Add(new ListItem(dr.GetInt32(0), dr.GetString(1)));
    }
    dr.Close();
  }
  return pagedList;
}

*Visual Basic*
      While dr.Read()
        pagedList.Add(New ListItem(dr.GetInt32(0), dr.GetString(1)))
      End While
      dr.Close()
    End Using
    Return pagedList
  End Function
  1. Save and close the DAL file.

In this step you have created a method named GetListItems that will return just a specified subset or page of rows from the total number of rows that the query would return. The ObjectDataSource will automatically populate the two parameters of the method with the start index of the currently selected page of the results, and the maximum number of items to return for that page.

Step 5 – Add and Configure an ObjectDataSource Control

This step shows you how to add an ObjectDataSource to the page and configure it to use the data access component methods you created in the previous steps.
  1. In Solution Explorer, double-click Default.aspx to open it in the editor, and then click the Design tab at the bottom of the editor to open the page in design view.
  2. Drag an ObjectDataSource control from the Data section of the Toolbox and drop in into the div section on the page.
  3. In the ObjectDataSource Tasks pop-up menu, click Configure Data Source.
  4. In the Choose a Business Object page of the Wizard, select DAL in the drop-down list and click Next.
  5. In the SELECT tab of the Define Data Methods page, select the GetListItems method in the drop-down list and click Next.
  6. In the Define Parameters page, leave all the settings at their default and just click Finish. Do not define any parameters, as the two discovered by the Wizard have specific names that will cause them to be populated automatically by the ObjectDataSource control.
  7. In the Visual Studio editor window, click the Source tab to switch to source view.
  8. Locate the definition of the* ObjectDataSource* control and delete the complete SelectParameters element and all its content.
  9. Place the insertion point at the end of the opening asp:ObjectDataSource element, before the closing ">", and type a single space. In the IntelliSense list, double-click EnablePaging, and type ="true". Then type another space, double-click SelectCountMethod in the IntelliSense list, and type ="GetListCount". Your definition of the ObjectDataSource control should now look like the following:
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
          SelectMethod="GetListItems" TypeName="DAL" EnablePaging="true"
          SelectCountMethod="GetListCount">
</asp:ObjectDataSource>

In this step, you have defined an ObjectDataSource control that will extract rows from the data access layer you created in previous steps. The ObjectDataSource control, in conjunction with a GridView control, supports paging of objects when you also provide a method that returns a count of the total number of objects returned by the query. It automatically passes the "start index" and the "maximum number of rows to return" values as parameters to the method you define for the SelectCountMethod attribute of the ObjectDataSource control.

Step 6 – Add an UpdatePanel and GridView Control

This step shows you how to create an AJAX-enabled page that loads the data using an ObjectDataSource control, and displays it using a GridView control.
  1. With Default.aspx open in the editor, click the Design tab at the bottom of the editor to open the page in design view.
  2. Drag a ScriptManager control from the AJAX section of the Toolbox and drop in onto the page.
  3. Drag an UpdatePanel control from the AJAX section of the Toolbox and drop in onto the page below the ScriptManager control.
  4. Drag a GridView control from the Data section of the Toolbox and drop it into the <ContentTemplate> element of the UpdatePanel control.
  5. In the GridView Tasks pop-up menu, select ObjectDataSource1 from the Choose data Source drop-down list, and set the Enable Paging checkbox.
  6. If you want to display more or less rows that the default of 10 per page, place the insertion point at the end of the opening asp:GridView element, before the closing ">", and type a single space. In the IntelliSense list, double-click PageSize and set the value to the required number of rows per page.
  7. Save the file and press F5 to run the application.

In this step, you added the user interface controls to the Web page and connected the GridView control to the ObjectDataSource control. The GridView control and the ObjectDataSource control manage the paging of the total number of rows returned by the query. However, because you enabled paging on the ObjectDataSource control and specified a method that returns the total number of (unpaged) rows, the ObjectDataSource will pass the two parameters that specify the start index and the maximum number of rows to return to the data access layer method. This method executes a stored procedure that selects only the appropriate subset of all the rows that the query would normally return. The GridView control is located on an AJAX UpdatePanel control, which - in conjunction with the ScriptManager control - allows the page to refresh just the GridView control without requiring a complete page reload.

Additional Resources

Last edited Dec 17, 2008 at 9:50 PM by prashantbansode, version 1

Comments

No comments yet.