Creating your own DataGrid Paging Control

[mendhak]

Personally, I am not very fond of the default paging control that the DataGrid has. Instead, I'd like something like this:

http://www.vbforums.com/attachment.php?attachmentid=38556&stc=1

So that the user can enter the page number and go directly. Of course, not everyone would want the same thing, but it really depends upon your needs.

To start with, create a new web user control in your project, call it PagingControl.ascx.

In the HTML:


<table cellSpacing="0" cellPadding="0" width="100%" border="0">
<tr>
<td align="center" id="tdFooterDisplay" runat="server" height="5px">
<asp:Label ID="lblCurrentPageNumber" Runat="server"></asp:Label>
</td>
</tr>
<tr>
<td align="right"><asp:label id="lblPage" Runat="server"></asp:label>&nbsp;<asp:textbox id="txtPageNumber" Runat="server" Width="20"></asp:textbox>&nbsp;<asp:label id="lblOf" Runat="server"></asp:label>&nbsp;<asp:label id="lblTotalPages" Runat="server"></asp:label>
<asp:button id="btnGo" Runat="server"></asp:button>
<asp:RegularExpressionValidator Text="*" ControlToValidate="txtPageNumber" id="RegularExpressionValidator1" runat="server"
ValidationExpression="\d"></asp:RegularExpressionValidator></td>
</tr>
</table>



In its codebehind,



Imports System.Text.RegularExpressions



That's the only Imports required, you'll see why later.

Then,




Public Delegate Sub PageChangedEventHandler(ByVal sender As Object, ByVal e As DataNavigatorEventArgs)



Public Event PageChanged As PageChangedEventHandler





Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here


Me.btnGo.CssClass = Me.CSSButtonClass
Me.txtPageNumber.CssClass = Me.CSSTextBoxClass
Me.tdFooterDisplay.Attributes.Add("class", Me.CSSFooterDisplayClass)



Me.lblOf.Text = "Of"
Me.lblPage.Text = "Page"
Me.btnGo.Text = "Go"



If Not Page.IsPostBack Then
Me.txtPageNumber.Text = Me.CurrentPage.ToString
Me.lblTotalPages.Text = Me.TotalPages.ToString
Me.lblCurrentPageNumber.Text = Me.CurrentPage.ToString.Trim
End If



End Sub




'Protected Sub OnPageChangedButton(ByVal sender As Object, ByVal e As EventArgs)
' Dim args As DataNavigatorEventArgs = New DataNavigatorEventArgs
' args.CurrentPage = Integer.Parse(txtPageNumber.Text)
' args.TotalPages = Integer.Parse(lblTotalPages.Text)
' OnPageChanged(args)

'End Sub

Protected Sub OnPageChanged(ByVal args As DataNavigatorEventArgs)

RaiseEvent PageChanged(Me, args)

End Sub



Private _intCurrentPage As Integer = 1
Private _intTotalPages As Integer
Private _strButtonCSSClass As String
Private _strTextBoxCSSClass As String
Private _strCSSFooterDisplayClass As String



Public Property CSSFooterDisplayClass() As String
Get
Return _strCSSFooterDisplayClass
End Get
Set(ByVal Value As String)
_strCSSFooterDisplayClass = Value
End Set
End Property


Public Property CurrentPage() As Integer
Get
Return _intCurrentPage
End Get
Set(ByVal Value As Integer)
_intCurrentPage = Value

End Set
End Property

Public Property TotalPages() As Integer
Get
Return _intTotalPages

End Get
Set(ByVal Value As Integer)
_intTotalPages = Value

End Set
End Property

Public Property CSSButtonClass() As String
Get
Return _strButtonCSSClass
End Get
Set(ByVal Value As String)
_strButtonCSSClass = Value
End Set
End Property

Public Property CSSTextBoxClass() As String
Get
Return _strTextBoxCSSClass
End Get
Set(ByVal Value As String)
_strTextBoxCSSClass = Value
End Set
End Property


Private Sub btnGo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGo.Click

Dim args As New DataNavigatorEventArgs
args.CurrentPage = Integer.Parse(Me.txtPageNumber.Text)
args.TotalPages = Integer.Parse(Me.lblTotalPages.Text)
OnPageChanged(args)
Me.lblCurrentPageNumber.Text = args.CurrentPage.ToString()
End Sub


That constitutes the code for your PagingControl class. You also need to create a DataNavigatorEventArgs class:



Public Class DataNavigatorEventArgs
Inherits EventArgs


Private m_iCurrentPage As Integer
Private m_iTotalPages As Integer

Public Sub New()

End Sub

Public Property CurrentPage() As Integer
Get
Return m_iCurrentPage
End Get
Set(ByVal Value As Integer)
m_iCurrentPage = Value
End Set
End Property

Public Property TotalPages() As Integer
Get
Return m_iTotalPages
End Get
Set(ByVal Value As Integer)
m_iTotalPages = Value
End Set
End Property


End Class


That's it. Your paging control is now ready. Use it as shown in the next post



Keywords: mendhak custom datagrid paging pineapples

[mendhak]

On my page, I placed a datagrid and below that, the paging control.


<table width="100%" cellpadding="0" cellspacing="0" border="0">
<tr>
<td>
<asp:DataGrid id="DataGrid1" runat="server" AllowPaging="True" Width="100%">
<PagerStyle Visible="False" NextPageText="" PrevPageText=""></PagerStyle>
</asp:DataGrid>
<uc1:PagingControl id="PagingControl1" runat="server"></uc1:PagingControl>
</td>
</tr>
</table>


In the codebehind,


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here


Me.PagingControl1.CSSTextBoxClass = "iStyle"
Me.PagingControl1.CSSButtonClass = "buttonStyle"
Me.PagingControl1.CSSFooterDisplayClass = "dgFooterStyle"


If Not Page.IsPostBack Then
bindgrid()
End If

End Sub



Private Sub bindgrid()
Dim totalCount As Integer

Dim con As SqlConnection = New SqlConnection("data source=xxxxxx;initial catalog=pubs;Uid=xxxxxxxxx;pwd=xxxxxxxxx;packet size=4096;")
Dim cmd As SqlCommand = New SqlCommand("Get_Employees", con)
cmd.CommandType = CommandType.StoredProcedure

Dim param As SqlParameter = cmd.Parameters.Add("@CurrentPage", SqlDbType.Int)
param.Direction = ParameterDirection.Input
param.Value = PagingControl1.CurrentPage

param = cmd.Parameters.Add("@PageSize", SqlDbType.Int)
param.Direction = ParameterDirection.Input
param.Value = 10

param = cmd.Parameters.Add("@TotalRecords", SqlDbType.Int)
param.Direction = ParameterDirection.Output

Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim ds As DataSet = New DataSet
da.Fill(ds)
totalCount = CInt(cmd.Parameters("@TotalRecords").Value)

DataGrid1.DataSource = ds
DataGrid1.DataBind()

If (totalCount Mod 10) = 0 Then
PagingControl1.TotalPages = totalCount / 10
Else
PagingControl1.TotalPages = (totalCount / 10) + 1
End If



da.Dispose()
cmd.Dispose()
con.Close()
con.Dispose()
End Sub

Private Sub PagingControl1_PageChanged(ByVal sender As Object, ByVal e As DataNavigatorEventArgs) Handles PagingControl1.PageChanged

Me.PagingControl1.CurrentPage = e.CurrentPage
bindgrid()


End Sub


Be sure to set the AllowPaging property of the datagrid to true, and the PagerStyle.Visible property to false.

[mendhak]

And finally, this is what the GET_EMPLOYEES SP looks like:


CREATE PROCEDURE Get_Employees( @CurrentPage int,
@PageSize int,
@TotalRecords int OUTPUT)

AS

-- Turn off count return.
Set NoCount On

-- Declare variables.
Declare @FirstRec int
Declare @LastRec int

-- Initialize variables.
Set @FirstRec = (@CurrentPage - 1) * @PageSize
Set @LastRec = (@CurrentPage * @PageSize + 1)

-- Create a temp table to hold the current page of data
-- Add an ID column to count the records
Create Table #TempTable
(
EmpId int IDENTITY PRIMARY KEY,
fname varchar(20),
lname varchar(30),
pub_id char(4),
hire_date datetime
)

--Fill the temp table with the reminders
Insert Into #TempTable
(
fname,
lname,
pub_id,
hire_date
)
Select fname,
lname,
pub_id,
hire_date
From employee
Order By lname

--Select one page of data based on the record numbers above
Select fname,
lname,
pub_id,
hire_date
From #TempTable
Where EmpId > @FirstRec
And EmpId < @LastRec

--Return the total number of records available as an output parameter
Select @TotalRecords = Count(*)
From #TempTable
GO