Export (0) Print
Expand All

Walkthrough: Creating Paged Data Access Using a Web Forms Page

Visual Studio .NET 2003

A common scenario in Web applications is to display a list — for example, a list of search results or of products from a catalog. Unless the list is short, it is typical for the list to be displayed in pages, with a way provided for users to navigate between them. This walkthrough illustrates one way to create paged output using a DataGrid Web server control.

There are a number of ways to create paged output, including:

  • Create and fill a dataset for each page. This strategy is simple, allowing you to take advantage of the built-in paging features of the DataGrid control. However, it is not very efficient, since it gets the entire table from the database each time the user moves to another page. For details, see Specifying Paging Behavior in a DataGrid Web Server Control.
  • Create a dataset and cache it. You can create a dataset and then store it (for example, in Session state). This saves the overhead of getting the table from the database each time, but still involves the overhead of storing the entire table in server memory. For small tables, this can be an efficient way to work.
  • Fetch a page a worth of data from the database as needed. This method is efficient in two ways: it transfers only the minimum amount of data required each time, and it does not require server memory to store a dataset. However, it is the most complicated to create.

This walkthrough will illustrate the last method. When you are through, you will have a page that looks like the following:

Aa728890.vbpageddataaccess(en-us,VS.71).gif

In order to complete this walkthrough, you will need:

The walkthrough is split into a number of smaller pieces:

  • Creating the Web application project and Web Forms page.
  • Adding the data components needed to get data from the database.
  • Adding the DataGrid control to the form.
  • Adding code to fetch the right data when users move from page to page.

Because this walkthrough focuses on some more advanced concepts of Web Forms pages, it assumes that you are generally familiar with the following concepts:

Creating the Project and Form

The first step is to create a Web application and a Web Forms page.

To create the project and form

  1. On the File menu, point to New, then click Project.
  2. In the New Project dialog box, do the following:
    1. In the Project Types pane, choose either Visual Basic Projects or Visual C# Projects.
    2. In the Templates pane, choose ASP.NET Web Application.
    3. In the Location box, enter the complete URL for your application, including http://, the name of the server, and the name of your project. The Web server must have IIS version 5 or later and the .NET Framework installed on it. If you have IIS on your computer, you can specify http://localhost for the server. (If you normally use a proxy server to access the Internet, you might need to configure Internet Explorer to bypass the proxy server in order to use localhost.)

      When you click OK, a new Web Forms project is created at the root of the Web server you specified. In addition, a new Web Forms page called WebForm1.aspx is displayed in the Web Forms Designer in Design view.

      Tip   If you have trouble creating a Web application project, see Web Access Failed Dialog Box.

Adding the Data Components

In this walkthrough, you will need to execute two slightly different SQL statements to get a page of data. Both statements get 10 rows at a time based on a starting ID; they differ only in the WHERE clause.

  • The statement for getting the next page of data gets 10 rows out of the table starting with the row after (that is, greater than) the last one on the current page.
  • The statement for getting the previous page gets 10 records starting with (that is, equal to) the first one on the previous page.
    Note   As an alternative to using two statements, you can create a single one and dynamically change the WHERE clause. In this walkthrough, you will use two separate statements to shorten the amount of code you have to write.

In this walkthrough you will be using the Customers table from the SQL Server Northwind database. You can use the techniques illustrated in the walkthrough for any table, subject to the following conditions:

  • The table you are accessing has a unique ID.
  • The table is ordered by the ID.
  • The SQL dialect you are using supports the TOP keyword in a Select statement. The TOP keyword allows you to specify how many rows to return. In this case, of course, you will use the TOP keyword to specify the number of rows per page.

Creating and Configuring the Connection

To begin, add a connection that points to the database you want to read from.

To create the data connection

  1. From the Data tab of the Toolbox, drag a SqlConnection object onto the page.
    Note   Because you are using SQL Server, you use the SqlConnection object, which is optimized for working with SQL Server 7.0 or later. If you are using a different database, you use the OleDbConnection object, which provides ADO.NET access to any OLE DB-compatible data source.
  2. Select the connection and in the Properties window, click the button in the ConnectionString property box.

    The list displays any existing connections and the <New Connection...> option.

  3. Unless you already have a connection to the SQL Server Northwind database, choose <New Connection...>.

    The Data Link Properties dialog box is displayed.

  4. Create a connection pointing to your SQL Server and the Northwind database, and then click OK.
    Note   You need appropriate read/write permissions on the SQL Server you are using. If SQL Server is on the same computer as IIS, it is recommended that you specify Windows integrated security when creating the connection. Otherwise, you can specify a user name and password and save that information with the connection, but doing so can compromise security. For more information, see Accessing SQL Server from a Web Application.

    The connection information you specified is saved as the connection string for your connection.

Now you need to add commands. You will need two in all, one for each SQL statement.

To create the data commands for getting pages of data

  1. From the Data tab of the Toolbox, drag two SqlCommand objects onto the page.
  2. Name one of them cmdNext and the other cmdPrevious.
  3. Select the cmdNext command and in the Properties window, click the button in the Connection property, and select the connection you made earlier. (You will need to open the Existing node.)
  4. In the CommandText property, click the ellipsis button to open the Query Builder dialog box.
  5. Use the Query Builder dialog box to construct the following SQL statement:
    SELECT TOP 10 CustomerID, CompanyName, City 
    FROM Customers 
    WHERE (CustomerID > @customerid) 
    ORDER BY CustomerID
    
  6. When you have finished, close the Query Builder dialog box.

    The SQL statement is filled into the CommandText property.

  7. Repeat Steps 3 through 5 for the cmdPrevious ecommand object, creating this query:
    SELECT TOP 10 CustomerID, CompanyName, City 
    FROM Customers
    WHERE (CustomerID >= @customerid) 
    ORDER BY CustomerID
    

You have finished adding data components to the page.

Adding the DataGrid Control

To illustrate paging, you will need a DataGrid control. For navigation, you will create Previous and Next buttons.

To add and configure DataGrid control

  1. From the Web Forms tab of the Toolbox, drag a DataGrid control onto the page.
  2. At the bottom of the Properties window, choose the AutoFormat link and select a predefined format for the grid.
  3. In the Properties window, click the Property Builder link.

    The DataGrid1 Properties dialog box appears.

  4. Click the Paging tab and set the following:
    PropertySetting
    AllowPagingChecked
    Allow custom pagingChecked
    Page size10
    Show navigation buttonsUnchecked
  5. Click OK to close the dialog box.
    Note   If you have worked with a data-bound DataGrid control before, you might expect to set the control's DataSource property. In this example, however, you set the data source in code. Similarly, you will not be setting any columns.

To add navigation buttons

  1. From the Web Forms tab of the Toolbox, drag two LinkButton controls onto the page.
  2. Name one of them btnPrevious and the other btnNext.
  3. Set the text of the buttons appropriately. For example, the text of the Next button might be Next >.

Adding Code to Fetch and Display a Page of Data

You can now add code to perform paging. The basic logic is that as users navigate, you will execute a SQL statement (using the data command objects you created) and return a data reader (SqlDataReader object) with 10 rows. You bind the DataGrid control to the data reader, and the rows are automatically displayed in the grid.

Next Page and Previous Page

The only complication in your page is in implementing navigation, and the issue is this: how do you determine which 10 records to get from the database?

The logic for the Next page is to get 10 rows starting with the row immediately following the last row in the grid. You will therefore get the ID of the last row in the grid and use it as a parameter for the cmdNext command you created earlier.

The logic for moving to the previous page is more involved. You want to get 10 rows starting (inclusively) with the first ID of the page preceding the one currently in the grid. For this, you will need to have available the ID of the previous page, and if the user continues paging backward, of the preceding pages as well.

One solution is to create a structure to store the ID of the first record of each page as that page is displayed. That way, when you return to the page, you can get its starting ID and use that as a parameter for the query to fill the page.

Because this is a Web Forms page, you cannot store page information in a variable; you must store it somewhere where it is preserved between round trips. In this walkthrough, you store information in view state, which places it into the page itself (in a hidden field). You will need to store two types of items:

  • The current page number. You will use this as an index value, and it will also tell you when the user has navigated back to the first page.
  • For each page, the ID of the first row in the grid. You will create a new item in view state for each page. The name of the item is the page number and the value of it is the ID.

For example, if you are on the third page of the grid, you will have added four items to view state, something like these:

CurrentPage=3
0=ALFKI
1=BSBEV
2=FAMIA

The following sections show you the code that you will need to implement the paging logic.

Displaying Data in the Grid

Filling the grid is the same no matter which way the user has navigated. You can therefore create a generic method that executes either the cmdNext or cmdPrevious command, depending on which one is passed to the method.

To display data in the grid

  1. Create a private variable called CurrentPage. You will set the value of this variable in other methods later.
  2. Create a method (perhaps named FillGrid) that accepts a data command (SqlCommand) as a parameter.
  3. Instantiate a data reader.
  4. Open a connection and the data command's ExecuteReader method to execute its SQL statement and fetch rows.
  5. Set the DataGrid control's DataSource property to the data reader and then bind the grid.
  6. Close the data reader and the connection.
  7. Save two values in view state:
    • The value of the CurrentPage variable.
    • The value of the ID column of the first row in the grid. Store the value in a view state item whose name is the page number. The logic looks like this:
      ' Visual Basic
      ViewState(CurrentPage.ToString()) = DataGrid1.Items(0).Cells(0).Text
      
      // C#
      ViewState[CurrentPage.ToString()] = DataGrid1.Items[0].Cells[0].Text;
      
  8. Finally, determine whether you are at the end of the table. For simplicity, in this walkthrough you will simply test whether the query returned fewer rows than the page size (10). If you have reached the end of the table, disable the Next button so that users do not try to continue.
    Note   For another suggestion about how to determine whether you are at the end of the table, see "Next Steps" later in this walkthrough.

    The following example shows how you create the CurrentPage variable:

    ' Visual Basic
    Public Class WebForm1
        Inherits System.Web.UI.Page
        Private CurrentPage As Integer
       ' more declarations here
    
    //C#
    public class WebForm1 : System.Web.UI.Page
    {
       private int CurrentPage;
       // more declarations here
    

    The following example shows what the code looks like for the complete, generic method:

    ' Visual Basic
    Private Sub FillGrid(ByVal currentSqlCommand as SqlClient.SqlCommand)
       Dim dr As SqlClient.SqlDataReader
       SqlConnection1.Open()
       dr = currentSqlCommand.ExecuteReader()
       DataGrid1.DataSource = dr
       DataGrid1.DataBind()
       dr.Close()
       SqlConnection1.Close()
       ViewState("CurrentPage") = CurrentPage
       ViewState(CurrentPage.ToString()) = DataGrid1.Items(0).Cells(0).Text
       ' Determine how many rows were filled into the grid. If it is less
       ' than the number of rows per page, there are no more rows in the 
       ' table, and the Next button should be disabled.
       If DataGrid1.Items.Count < DataGrid1.PageSize Then
           btnNext.Enabled = False
       End If
    End Sub
    
    //C#
    private void FillGrid(System.Data.SqlClient.SqlCommand currentSqlCommand)
    {
       System.Data.SqlClient.SqlDataReader dr;
       sqlConnection1.Open();
       dr = currentSqlCommand.ExecuteReader();
       DataGrid1.DataSource = dr;
       DataGrid1.DataBind();
       dr.Close();
       sqlConnection1.Close();
       ViewState["CurrentPage"] = CurrentPage;
       ViewState[CurrentPage.ToString()] = DataGrid1.Items[0].Cells[0].Text;
       // Determine how many rows were filled into the grid. If it is less
       // than the number of rows per page, there are no more rows in the 
       // table, and the Next button should be disabled.
       if (DataGrid1.Items.Count < DataGrid1.PageSize)
       {
          btnNext.Enabled = false;
       }
    }
    

Initializing the Page

The first time the page runs, you want to display the first page of data. Now that you have the generic method for filling the grid, all you need to do is set a parameter and call the method. You want to execute the cmdNext command. Set its @customerid parameter to an empty string, meaning that the query will bring back the first 10 records greater than "" — that is, the first 10 records in the table. You also want to set the page number (CurrentPage) to zero to indicate that you are on the first page.

The code looks like this:

' Visual Basic
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
   'Put user code to initialize the page here
   If Not Page.IsPostBack Then
       cmdNext.Parameters("@customerid").Value = ""
       CurrentPage = 0
       FillGrid(cmdNext)
   End If
End Sub

//C#
private void Page_Load(object sender, System.EventArgs e)
{
   // Put user code to initialize the page here
   if (!Page.IsPostBack)
   {
      cmdNext.Parameters["@customerid"].Value = "";
      CurrentPage = 0;
      FillGrid(cmdNext);
   }
}

Navigating to the Next Page

When users navigate to the next page, you again want to execute the cmdNext command using your generic method. This time, the parameter for the command is the value of the ID column of the last row in the grid.

You also need to increment the page number so that the generic FillGrid method can use the number to create another view state item for the ID. To increment the page number, you have to get the old one out of view state. When you do so, you have to cast it to an integer, since all items in view state are stored as objects.

The code for the Next button looks like this:

' Visual Basic
Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
   ' Get the page number of the page most recently displayed
   CurrentPage = CType(ViewState("CurrentPage"), Integer)
   CurrentPage += 1
   'Gets the id on current page
   Dim lastid As String = DataGrid1.Items(9).Cells(0).Text
   cmdNext.Parameters("@customerid").Value = lastid
   FillGrid(cmdNext)
End Sub

//C#
private void btnNext_Click(object sender, System.EventArgs e)
{
   // Get the page number of the page most recently displayed
   CurrentPage = (int)(ViewState["CurrentPage"]);
   CurrentPage++;
   // Gets the id on current page
   string lastid = DataGrid1.Items[9].Cells[0].Text;
   cmdNext.Parameters["@customerid"].Value = lastid;
   FillGrid(cmdNext);
}

Navigating to the Previous Page

Finally, you need to add code to move backwards. This is where you will use the information you have been storing in view state. First, you get the current page number and then decrement it. Then you look in view state, using the page number as the name, to get the ID of the first row on the page. You use that ID as the parameter for the cmdPrevious command, which you pass to the generic FillGrid method.

To prevent running off the beginning of the table, you should check that you page count is not below zero. In addition, you should enable the Next button, just in case it was disabled in previous navigation.

' Visual Basic
Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
   btnNext.Enabled = True
   CurrentPage = CType(ViewState("CurrentPage"), Integer)
   CurrentPage -= 1
   If CurrentPage >= 0 Then
       Dim firstid As String
       firstid = CType(ViewState(CurrentPage.ToString()), String)
       cmdPrevious.Parameters("@customerid").Value = firstid
       FillGrid(cmdPrevious)
   End If
End Sub

//C#
private void btnPrevious_Click(object sender, System.EventArgs e)
{
   btnNext.Enabled = true;
   CurrentPage = (int)(ViewState["CurrentPage"]);
   CurrentPage--;
   if (CurrentPage >= 0) 
   {
      string firstid;
      firstid = (string)(ViewState[CurrentPage.ToString()]);
      cmdPrevious.Parameters["@customerid"].Value = firstid;
      FillGrid(cmdPrevious);
   }
}

Testing

After adding the code, test data access in the Web Forms page.

To test the Web Forms page

  1. Save the page.
  2. In Solution Explorer, right-click the page and choose View in Browser.

    Confirm that a list of 10 customers is displayed in the grid.

  3. Click the Next and Previous buttons several times to be sure you are navigating correctly. Special tests to make are:
    • On the first page, click Previous; nothing should happen.
    • Click Next till you reach the end of the table and make sure that the Next button is disabled.

Next Steps

This walkthrough has illustrated one method of implementing paging. The scenario illustrated here was simple but complete. In real applications, many additional factors can come into play, and paging can become quite complex. Some issues to think about include:

  • To determine when you have reached the end of the file, the walkthrough uses an algorithm that is easy but not perfectly reliable. (It compares the number of returned rows against the number of rows in a page). In a real application, you would want to add a more sophisticated algorithm. One possibility is to do the following:
    1. Add another command object to the page that includes this SQL statement:
      Select Count(CustomerID) From Customers
      
    2. The first time the page runs, execute the command using the command's ExecuteScalar method to get the total number of rows in the table. Then calculate the total page count required for the table and store it in both a global variable and in view state. On subsequent posts, get the value back out of view state.
    3. In the FillGrid method, check whether the current page has reached the total page count. If so, disable the Next button.
  • The walkthrough assumes that the data is not volatile. If other users are adding rows to the table, page boundaries change, and storing the first ID of a given page is not a viable solution.
  • You can create a stored procedure to perform the work done here with SQL statements.
  • Security. For details, see Overview of Web Application Security Threats.

See Also

Setting and Getting Data Command Parameters | Executing a Data Command That Returns a Result Set

Show:
© 2014 Microsoft