Paging Database Results in ASP.NET (Prt1)(转载：http:

2022-07-15 80酷酷网 80kuku.com

　　For More Information on ASP.NET
This article examines how to create a databound listbox using ASP.NET. For more information on ASP.NET be sure to check out the articles in the ASP.NET Article Index. The code in this article is based on the Beta 2 version of ASP.NET. To learn more about the free ASP.NET Beta 2, be sure to check out this FAQ.

Introduction
One of the most common tasks developers are faced with when working with data-driven Web sites is the need to page data. Most data is only worthwhile if it can easily be digested by a human, so a data-driven Web site needs to present data in an easy-to-read format. In situations where a large chunk of data is presented to the user, it helps to break up this information into multiple pages.

Paging data is nothing new, just about every search engine and eCommerce site employs the technique. If you wonder over to Google and search on ASP you'll get back over five million results! Imagine if Google attempted to show all five million matches on one Web page! Instead, to make the information digestable by human eyes (i.e., yours), Google presents the results in chunks of ten records per page.

In this article we will look at how to implement paging database results using ASP.NET. It is surprisingly simple, requiring just a few lines of code!

Database Paging in Classic ASP
Paging in classic ASP was possible via a number of means. One of the most common ways was to use the paging properties provided in the ADO Recordset object. Even when using these properties, developers still were required to write a lot of code to handle paging correctly. (For more information on paging results using this method in classic ASP, be sure to read this article.) Other methods were available as well, such as using a stored procedure as well client-side script techniques. However, all of these methods required much code and, usually, a hapless intermixing of HTML and script code.

Database Paging in ASP.NET
Fortunately, paging database results in ASP.NET is much cleaner and much easier with ASP.NET than with classic ASP. In this article we'll look at using a DataGrid Web control to implement paging. The beautiful thing about the DataGrid Web control is that it handles paging itself, meaning the amount of actual code we have to write is very little indeed. First things first, though, let's look at an ASP.NET Web page that binds a DataSet to a DataGrid Web control. (We'll then examine how to page these results.)

Below you can see what the HTML portion of our ASP.NET page needs to look like. At absolute minimum, it just needs to contain a DataGrid control. I have set some properties in the DataGrid control to enhance its appearance; these ehancements, however, are optional:

<html>
<body>
    <asp:datagrid id="dgPopularFAQs" runat="server" BorderWidth="0"
       CellPadding="2" Width="100%"
       Font-Name="Verdana"
       Font-Size="Smaller"

       HeaderStyle-HorizontalAlign="Center"
       HeaderStyle-Font-Bold="True"
       HeaderStyle-BackColor="Navy"
       HeaderStyle-ForeColor="White"

       AlternatingItemStyle-BackColor="#dddddd">
    </asp:datagrid>
</body>
</html>

Next, we need to write code that will query our database, populating a DataSet with the results of some SQL query. Once we have this populated DataSet, we'll bind the DataSet to the DataGrid Web control, dgPopularFAQs. The code for connecting to a Microsoft SQL Server 7.0 or greater database can be seen below. (If you are using a different database, change all instances of Sql below to OleDb (i.e., change Dim myDA as New SqlDataAdapter() to Dim myDA as New OleDbDataAdapter()).)

<% Import Namespace="System.Data" %>
<% Import Namespace="System.Data.SqlClient" %>
<script language="vb" runat="server">
  Sub Page_Load(sender as Object, e as EventArgs)
    BindData()
  End Sub


  Sub BindData()
    '1. Create a connection
    Dim myConnection as New SqlConnection(<i>ConnectionString</i>)

    '2. Create the command object, passing in the SQL string
    Const strSQL as String = "SELECT FAQID, Description, DateEntered, ViewCount " & _
                             "FROM tblFAQ ORDER BY FAQID"
    Dim myCommand as New SqlCommand(strSQL, myConnection)

    '3. Create the DataAdapter
    Dim myDA as New SqlDataAdapter()
    myDA.SelectCommand = myCommand

    '4. Populate the DataSet
    Dim myDS as New DataSet()
    myDA.Fill(myDS)

    '5. Set the datagrid's datasource to the dataset and databind
    dgPopularFAQs.DataSource = myDS
    dgPopularFAQs.DataBind()
  End Sub
</script>

... HTML section omitted for brevity ...

[View a live demo!]

Note that the ConnectionString property should be a valid connection string for the SQL database. For example: server=IPAddress;uid=userName;pwd=password;database=databaseName. (Of course, the OleDb connection string is slightly different; refer to the documentation for more information.) Note that we are performing five steps here:

Connect to the database.

Create the command object based upon the SQL query we wish to run and the connection object we wish to run it on.

Create the DataAdapter. This object is needed to fill a DataSet with the database results.

Populate the DataSet object with the results from the database query.

Bind the DataSet to the DataGrid Web control.
Note that with ASP.NET it's that easy to display database information in a visually appealing format. Do take a moment to view the live demo and note how nice the DataGrid control appears. Also note that with this technique there is no shameless mixing of HTML and script code, as would be the case in a classic ASP page.

In Part 2 we'll look at how to improve the data display of the DataGrid Web control by having the data paged. As you'll see in Part 2, this only requires a few lines of code!