Description
Hello viewers, anybody in the Programming world would know that paging database results and its effect. The day from the beginning I started my asp journey my first bitter experience is writing a program and display the record set results with paging format. So at last I was successful in writing Program for this. When I started upgrading my self for ASP.Net my mind first went on paging program in ASP.Net so after experiencing the success on this task on ASP.Net here I am furnishing the code and I feel it is helpful to the people like me.
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 previous 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. Import the Necessary Namespaces which are required like apps and sqlclient(for Ms-Sql Server). Here I have considered Pubs database in MS-Sql server-2000. 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 knowledge – our Subroutine to access our database and bind our result set to our ASP.NET Datagrid. This subroutine, rs_bind_sql() creates all of our variables that we'll use:
Sub rs_bind_sql() ' Begin Sub routine
Dim MyConnection As SqlConnection
Dim DS As DataSet
Dim MyCommand As SqlDataAdapter
Dim RcdCount As Integer
'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=(localsql);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
DSet = New DataSet()
MyCommand.Fill(DSet, "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 = DSet.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.
ResultCount = RcdCount
'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:
'To Display Previous/Next 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
'To Display 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 'End Subroutine
That concludes our rs_bind_sql() subroutine.
HTML CODE
In this HTML version we need some label controls to be placed on the form to display information related such as the number/type of page we are viewing, the total records,number of records that are getting displayed etc.,
-
And also place a Datagridcontrol on the form
-
Make sure The Datagrid is binding the database results
-
Finally, we need a series of LinkButton Web controls, to display our Prev/First Page and Next/Last Page links
<html>
<body>
'For our recordcount and pagecount
<asp:Label ID="lblPageCount" runat="server" /><br>
<asp:Label ID="RecordCount" runat="server" />
<form id="Form1" runat="server">
<asp:DataGrid ID="Pubs" runat="server" AllowPaging="True" AllowCustomPaging="False"
PageSize="10" PagerStyle-Visible="False" />
<%-- Display the First Page/Previous Page buttons --%>
<asp:LinkButton ID="Firstbutton" Text="<< 1st Page" CommandArgument="0" runat="server"
OnClick="PagerButtonClick" />
<asp:LinkButton ID="Prevbutton" Text="" CommandArgument="prev" runat="server"
OnClick="PagerButtonClick" />
<%-- Display the Next Page/Last Page buttons --%>
<asp:LinkButton ID="Nextbutton" Text="" CommandArgument="next" runat="server"
OnClick="PagerButtonClick" />
<asp:LinkButton ID="Lastbutton" Text="Last Page >>" CommandArgument="last" runat="server"
OnClick="PagerButtonClick" />
<br>
<br>
<br>
<br>
Change Pagesize
<asp:DropDownList ID="ps" runat="server">
<asp:ListItem>4</asp:ListItem>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>7</asp:ListItem>
<asp:ListItem Selected>10</asp:ListItem>
<asp:ListItem>12</asp:ListItem>
<asp:ListItem>15</asp:ListItem>
<asp:ListItem>22</asp:ListItem>
</asp:DropDownList>
<asp:Button ID="Button1" Text="Change Pagesize" runat="server" OnClick="RePage" />
</form>
</body>
</html>
For DataGrid Web control we set the PagerStyle's Visible property to False. This is because we are implementing our own paging solution, and don't want to use the default paging style supported by the DataGrid Web control. (For more information on paging database results using the DataGrid's built-in functionality, be sure to read: Paging Database Results in ASP.NET!) Also note that the four LinkButton controls all specify the server-side subroutine PagerButtonClick as the sub to be called when they are clicked; similarly, the "Change Pagesize" button has the RePage subroutine defined as its OnClick event handler.
The event handler for the four LinkButtons (PagerButtonClick) must display the appropriate page of data, be it the next page, the previous page, the first page or the last page. Which page to display, of course, depends on what LinkButton the user clicks. The PagerButtonClick (shown below) uses the CommandArgument passed in from the LinkButton Web controls to determine which control was clicked, and then takes the appropriate action.
Protected Sub PagerButtonClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Lastbutton.Click, Firstbutton.Click, Prevbutton.Click, Nextbutton.Click
'used by external paging UI
Dim arg As String = sender.CommandArgument
Select Case arg
Case "next"
'The next Button was Clicked
If (Pubs.CurrentPageIndex < (Pubs.PageCount - 1)) Then
Pubs.CurrentPageIndex += 1
End If
Case "prev"
'The prev button was clicked
If (Pubs.CurrentPageIndex > 0) Then
Pubs.CurrentPageIndex -= 1
End If
Case "last"
'The Last Page button was clicked
Pubs.CurrentPageIndex = (Pubs.PageCount - 1)
Case Else
'The First Page button was clicked
Pubs.CurrentPageIndex = Convert.ToInt32(arg)
End Select
'Now, bind the data!
rs_bind_sql()
End Sub
The RePage event handler, which is called when the "Change Pagesize" button is clicked, simply resets the DataGrid's CurrentPageIndex property back to 0 and rebinds the database data:
Protected Sub RePage(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Pubs.CurrentPageIndex = 0
rs_bind_sql()
End Sub
Finally, the last two server-side subroutines are two major helper subroutines, Next_Buttons() and Prev_Buttons(), which display the correct text for each of the LinkButtons. These two subs, which are called from rs_bind_sql(), can be seen below:
Sub Prev_Buttons()
Dim PrevSet As String
If Pubs.CurrentPageIndex + 1 <> 1 And ResultCount <> -1 Then
PrevSet = Pubs.PageSize
PrevButton.Text = ("< Prev " & PrevSet)
If Pubs.CurrentPageIndex + 1 = Pubs.PageCount Then
FirstButton.Text = ("<< 1st Page")
End If
End If
End Sub
Sub Next_Buttons()
Dim NextSet As String
If Pubs.CurrentPageIndex + 1 < Pubs.PageCount Then
NextSet = Pubs.PageSize
Nextbutton.Text = ("Next " & NextSet & " >")
End If
If Pubs.CurrentPageIndex + 1 = Pubs.PageCount - 1 Then
Dim EndCount As Integer
EndCount = ResultCount - (Pubs.PageSize * (Pubs.CurrentPageIndex + 1))
NextButton.Text = ("Next " & EndCount & " >")
End If
End Sub