<%@ Page %>

Adding Paging Functionality To DataList

Introduction

ASP.NET DataList web control allows to display data in flexible way. One of the feature it lacks as compared with DataGrid is paging. This article explains you how to add such functionality to the DataList.

Create new web project

Start by creating a new web project in VB.NET in VS.NET. (Even if you are not using VS.NET things will be pretty same.). Add a new web form called WebForm1 to the project.

Adding a DataList

Now, add a DataList web control on the web form. Arrange DataList template as per your requirement. In the footer template of the DataList add two Link Buttons. Set the CommandName of the Link Buttons to prev and next respectively. We will use these buttons to navigate across the DataList.

Form level variables

We will add one constant and one variable at the form level. The constant will hold page size i.e. number of records per page (3 in our case) and the variable will hold total number of pages possible for given query.

Const pagesize As Integer = 3
Dim pagecount As Integer

Create a sub to calculate total pages

Next, we will write a sub routine that will calculate total number of pages possible for our SELECT query and then store the value in Viewstate. We store the value in the viewstate so that next time we need not make a database trip again.

Public Sub SetTotalPages()
If viewstate("pagecount") Is Nothing Then
	Dim cnn As New 
	SqlConnection(Global.GetConnectionString)

	Dim cmd As SqlCommand = 
	New SqlCommand("SELECT Count(*) FROM employees", cnn)
	cnn.Open()
	Dim reccount As Integer = 
	cmd.ExecuteScalar().ToString()

	If reccount Mod pagesize = 0 Then
		pagecount = reccount / pagesize
	Else
		pagecount = CInt(reccount / pagesize) + 1
	End If
	viewstate("pagecount") = pagecount
	cnn.Close()
Else
	pagecount = viewstate("pagecount")
End If
End Sub

Here, we connect with the database and get count of records for the table. Remember that if you want to display data based on some WHERE condition that condition will need to add here as well. Then we calculate the possible page count using MOD operator and store the value in the view state.

Write a sub to bind the DataList

Let us write another sub routine that will actually bind our DataList with the data.

Public Sub Bindgrid(ByVal pageno As Integer)
	Dim cnn As New SqlConnection("connstr")
	Dim startrec As Integer
	If pageno = 1 Then
		startrec = 0
	Else
		startrec = (pageno - 1) * pagesize
	End If
	Dim da As New SqlDataAdapter
	("select top " & pagesize & " * from employees 
	where employeeid>" & startrec, cnn)
	Dim ds As New DataSet()
	da.Fill(ds, "table1")
	DataList1.DataSource = ds
	DataList1.DataBind()
End Sub

In above code replace your own connection string in place of "connstr". Here, we accept the page no. to be displayed and find the starting record for that page. For this we select TOP n based on the page size. Using TOP clause saves you from the problem of missing record numbers. We then bind the DataList as usual.

Page Load Event

Let us now see how the Page_Load event looks like:

Private Sub Page_Load
(ByVal sender As System.Object, 
ByVal e As System.EventArgs)
Handles MyBase.Load
	If Not Page.IsPostBack Then
		viewstate("curpage") = 1
		Bindgrid(1)
	End If
	SetTotalPages()
End Sub

Here, we have called BindGrid with page no equal to 1. We also set a viewstate item called current page. We will use this item later in our code. It basically keeps track of current page.

Handle Link Button Click events

Our link buttons are part of the DataList. Instead of directly writing event handlers on them, we will use DataList control's ItemCommand event for our purpose.

Private Sub DataList1_ItemCommand
(ByVal source As Object, 
ByVal e As DataListCommandEventArgs) 
Handles DataList1.ItemCommand

Dim curpage As Integer
Select Case e.CommandName
	Case "prev"
		curpage = viewstate("curpage")
		If curpage > 1 Then
			curpage = curpage - 1
		Else
			curpage = 1
		End If
		viewstate("curpage") = curpage
		Bindgrid(curpage)

	Case "next"
		curpage = viewstate("curpage")
		If curpage < pagecount Then
			curpage = curpage + 1
		Else
			curpage = pagecount
		End If
		viewstate("curpage") = curpage
		Bindgrid(curpage)

End Select
End Sub

Here, we check the CommandName prperty and based on it either navigate backwards or forward. We also update the curpage item from the viewstate.

Running the web form

Now compile and run your application. You should have DataList with pager bar in the footer. Try navigating using the links and test for various page size values.

Summary

DataList web control built-in paging feature. In this article we saw how to add this feature on our own. By doing this we also fetch only required rows.