Control Panel Forum Hosting Help ASP.NET 4 & SQL 2008 Hosting Resources Notice

Total members: 149203
Average new registrations per day (in last 7 days): 122

New Registration: Open

Register Now

Learn Windows 7: A solution for :Service Unavailable Error - Could Not Load All ISAPI Filters for Site/service Here I would like to share you a guide on resolving, Service Unavailable Error - Could Not Load All ISAPI Filters for Site/service.

Want to become a DBA ? SQL Server 2008 training courses - by AspSpider team

Home » Resources » Paging in DataList

ASP.NET	ADO.NET	Web Services	Remoting	Visual Studio 2005
Error Bank	Interview Questions	Tips & Tricks	XML	HTML
Jscript/Javascript	IIS	Windows		General

Submit Resource or code snippet ... and get surprise gifts ASP.NET web hosting

Win Digital camera, ASP.NET Books, Free softwares!!

Paging in DataList

18 Oct, 2009

Author: Mohammad Zorba

Summary
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).

.NET Classes used :

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.
Code for Navigation Links.



    
        

        

        

        

        
    

                Runat=server 
        Font-Name=verdana
        Font-Size=10pt /> 
         
                ID=hrefFirst
        onserverclick=ShowFirst 
        runat=server><<<
         
                ID=hrefPrevious
        onserverclick=ShowPrevious 
        runat=server><<
         
                ID=hrefNext
        onserverclick=ShowNext
        runat=server>>
         
                ID=hrefLast
        onserverclick=ShowLast 
        runat=server>>>

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.

Feedbacks about this page from members:

- No Feedbacks yet !! -

Submit Feedback View All Feedbacks