Published: Saturday, July 21, 2001
Paging Database Results in ASP.NET
By Scott Mitchell
| 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!
Read Part 2!
