Published: Wednesday, August 29, 2001
Custom ASP.NET Datagrid Paging With Exact Count
By Dimitrios Markatos
| About this Custom Paging... |
|---|
|
In a previous article, not the same as the custom paging mentioned in Paging
Database Results in ASP.NET, the author (Scott Mitchell) mentioned one downside
of the default paging behavior of the DataGrid Web control was that, for each page, all of the database
records to be paged through were returned to the ASP.NET Web page, even if only a small portion of them were
displayed. Furthermore, the author mentioned that this potential bottleneck could be overcome with custom paging.
The custom paging the author was referring to was the custom paging supported by the DataGrid Web control.
This article looks at how to implement one's own custom paging - this form of custom paging still suffers from
the same limitation as the default DataGrid paging.
|
Introduction
Anybody in the DB world knows what paging database results is and its effect. From the time I had started
getting into good old classic ASP, I was intrigued with the ability to divide large sets of data into
sections of x records per page. One thing that I didn't like about paging is that it seemed sites
incorporated just a < Prev and Next > link on the search results page. I
wasn't satisfied with such a lackluster paging technique, and from there I searched high
and low on every ASP Web site I could find to see if there was code to show more advanced paging options, such
as how many pages were remaining to be paged through, or, if the next page was the last page of results,
how many records were on that last page. Unfortunately, I
couldn't find any such code, so I had set out to do it myself. (To see the proposed paging enhancements I
like, check out the live demo
for this article...)
I have since coded a number of techniques for advanced paging in classic ASP, but my latest challenge
has been to incorporate the same paging techniques in ASP.NET! (For more information on ASP.NET, be sure to
visit the ASP.NET Article Index.)
Pssst, don't ask me to talk about redoing my app in Beta 1, and upon upgrading to Beta 2 was horrified that
my code needed to be redone.... by the way, the code in this article is all Beta 2 compliant.
Now, if anyone has looked into the Microsoft .NET SDK and Quickstart
samples you will find custom paging samples, but it's the usual next and prev stuff. Now let's see how we
can kick this paging up a notch and tell us more detail about our data output.
Accessing our Data
The first step is of course to query our database, and send our data into our datagrid. The first
thing we should concern ourselves with in any .Net page is that we import the necessary
namespaces for our app. In this case, as with most data access apps, I'm importing
System.Data and System.Data.SqlClient for SQL Server. If you use MS Access or another
database then System.Data.OleDb namespace and associated classes will work just fine, providing
you modify the connection variables and data adapters.
This importing of namespaces is all done before our script tags like so:
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SqlClient" %>
|
Now, within our server-side script tags we include our object-oriented stuff – our Subroutine to
access our database and bind our result set into our ASP.NET Datagrid. This subroutine, BindSQL()
needs to first create all of our variables that we'll use:
Sub BindSQL()
Dim MyConnection As SqlConnection
Dim DS as DataSet
Dim MyCommand As SqlDataAdapter
Dim RcdCount As Integer
'Our SQL string
Dim sqlStr As String = "SELECT titles.title, authors.au_lname, " & _
"authors.au_fname, titles.price " & _
"FROM authors INNER JOIN titleauthor ON " & _
"authors.au_id = titleauthor.au_id " & _
"INNER JOIN titles ON " & _
"titleauthor.title_id = titles.title_id"
'The connection to our database
Dim strConn As String = "server=(local);uid=sa;pwd=;" & _
"database=pubs;Trusted_Connection=yes;"
|
Next we need to instantiate our connection and command object, and the fill our DataSet object with the
results of the SQL query:
...
'Open up our connection with our connection object
MyConnection = New SQLConnection(strConn)
'To execute our Sql Statement and provide our active connection
MyCommand = NewSqlDataAdapter(sqlStr, MyConnection)
'Create instance of DataSet object and fill our predetermined
'datagrid with it and we name it
DS = new DataSet()
MyCommand.Fill(DS, "pubs")
...
|
Now comes the one part that we'll used for our custom paging – the record count, and you'll
see it quite different than our classic ASP way.
RcdCount = DS.Tables("pubs").Rows.Count.ToString()
|
Now that we have this total count of the records in the DataSet, we'll save it to a global variable, since
we'll want to access it from other subroutines. The variable ResultCount should be defined
in global-scope, as an Integer. (See the complete source later on in this article to note how to create
global-scoped variables)
Next, we display the number of records found in a label control:
RecordCount.Text = "<b><font color=red>" & RcdCount & "</font> records found"
|
Finally, at this point, we can bind our DataSet to the DataGrid and display a label illustrating what page
of results we're currently viewing:
which will display :
Pubs.DataSource = DS
Pubs.Databind()
lblPageCount.Text = "Page " & Pubs.CurrentPageIndex + 1 & " of " & Pubs.PageCount
|
At this point, we need to determine if we need to show the Next/Prev links, as well as the First Page/Last Page
links:
'Do we want to show the prev/First Page buttons?
If Pubs.CurrentPageIndex <> 0 Then
Call Prev_Buttons()
Firstbutton.Visible = true
Prevbutton.Visible = true
Else
Firstbutton.Visible = false
Prevbutton.Visible = false
End If
'Do we want to show the Next/Last Page buttons?
If Pubs.CurrentPageIndex <> (Pubs.PageCount-1) then
Call Next_Buttons()
NextButton.Visible = true
Lastbutton.Visible = true
Else
NextButton.Visible = false
Lastbutton.Visible = False
End If
End Sub
|
That concludes our BindSQL() subroutine, which is a bit lengthy. Don't worry, that's, by far,
the most complex piece of our ASP.NET Web page! We still have three more short server-side subroutines to
examine, but let's first look at the HTML portion of our ASP.NET Web page, which we'll do in
Part 2.
Read Part 2!
