Paging in DataList: ASP Alliance


Paging in DataList

page

by Jesudas Chinnathampi (Das)
Feedback

Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 47908/ 42

Paging in DataList

Written on: July 23rd, 2002.

Introduction

In my previous article, we discussed how to Sort data in a Datalist. Today, we will learn how we can add the paging feature for the datalist. It should be noted that, like DataGrid, DataList does not support inbuilt paging mechanism. The essence of this article is how we make use of the object SqlDataAdapter. This object has a method called, Fill which is used to add or refresh the rows in a DataSet. Actually the method Fill is overloaded. We will be mainly concentrating the one which takes four arguments. The four arguments are DataSet, startRecord, maxRecords and the TableName. Second and third arguments are integer. Where as the TableName is the table name. So, if we say objDV.Fill(objDS, 0, 5, "sales") The dataset will be filled with 5 records and the starting position will be from the first record. First, we are bringing the entire records from the table sales, and then we filter those with the help of startRecord and maxRecords.

For our example, we will consider the table, Sales from the pubs database (SQL Server 2000).

Things that we will be learning in this article.

How to populate a DataList?
How to build the User Interface for Paging in a DataList?
How to make user of the Fill method of SqlDataAdapter?
An alternate solution for the Fill method!

Populating the DataList

For our example, we will take the table SALES in the PUBS Database. Since stored procedures are very much better than inline query, we use a stored procedure called, sp_das_sales_sel, which contains a single SQL statement. The SQL statement would be Select * from pubs.dbo.sales. And finally, we need to bind the DataView to the DataList web server control.

How to build the User Interface for Paging in a DataList?

Apart from the DataList web server control, we will provide user with four hyperlinks for navigation. When the user clicks on any othe four navigation links, we will invoke a server side method which will pulls out the proper records. We will also show the total number of records, total pages and the current page number.

How it works?

Important Note: The name of the file that I have used in the href property is "datalistpaging.aspx". You should replace this with your aspx filename, unless you keep the filename as myself.

We have four hyperlinks for each navigation. On each of these, we have a OnServerClick event which will be fired immediately when the user clicks the link. For example, when the user click the link, <<, which is for the first page, the method ShowFirst will be invoked. All we have to do in the ShowFirst method is to set the current record position to zero. And ofcourse, we have to bind the datalist. Let us take a look at the methods, ShowFirst and DataBind.

ShowFirst and the DataBind method.

Public Sub ShowFirst(ByVal s As Object, ByVal e As EventArgs)
    intCurrIndex.Text = "0"
    DataBind()
End Sub

Private Sub DataBind()
    Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    Dim objDA As New SqlDataAdapter("exec sp_das_sales_sel", objConn)
    Dim objDS As New DataSet()

    If Not Page.IsPostBack() Then
        objDA.Fill(objDS)
        intRecordCount.Text = CStr(objDS.Tables(0).Rows.Count)
        objDS = Nothing
        objDS = New DataSet()
    End If

    objDA.Fill (objDS, Cint(intCurrIndex.Text), CInt(intPageSize.Text), "Sales")

    dList.DataSource = objDS.Tables(0).DefaultView
    dList.DataBind()
    objConn.Close()
    PrintStatus()
End Sub

How it works?

In the ShowFirst method, we are setting the value of intCurrIndex.Text to be zero. intCurrIndex is a hidden label control which keeps track of the current record number. We also do have two more hidden label controls. They are intPageSize and intRecordCount.

In the method, DataBind you can see that, we get the total record count. We are storing this count in the hidden label web server control called, intRecordCount. Then we use the Fill method to retrieve the current page.

An alternate solution for the Fill method!

The major disadvantage of our logic is that, if we have 1000 records in the table, we are bringing all those to our ASPX pages. All we need is the records for our current page. This can be achived by modifying our stored procedure. We should have an identity column in our table. Then, we should pass the starting position and the number of records to be retrieved to the stored procedure. By this way, we will just be bringing in the needed records, which will decrease the network traffic and throughput time.