Here, I am going to demonstrate a sample on
GridView Custom Paging. Default GridView paging works best when you deal with
limited pages. If there are more pages then, the performance suffers. In this
case direct jump to desire page is a good alternative.
Creating Table
Create New table based on below structure and
add some data in it. The table looks like the below figure.
Figure1
Creating a Stored procedure
Now I am going to design a stored procedure to
get the Customer records from the tables employee.
ALTER PROCEDURE [dbo].[GetCustomersPageWise]
@PageIndex
INT = 1
,@PageSize INT = 10
,@RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER
(
ORDER BY [employeeID]
ASC
)AS RowNumber
,[employeeID]
,[employeeName]
,[employeeCompany]
INTO #Results
FROM [employee]
SELECT @RecordCount = COUNT(*)
FROM #Results
SELECT * FROM #Results
WHERE RowNumber
BETWEEN(@PageIndex -1) * @PageSize + 1
AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE
#Results
END
GridView
Drag and drop a ASP.Net GridView control, a
DropDownList control and a repeater controlto the Asp.Net web page. Following
piece of code will do it for you.
<div>
PageSize:
<asp:DropDownList ID="ddlPageSize" runat="server" AutoPostBack="true" OnSelectedIndexChanged="PageSize_Changed">
<asp:ListItem Text="10" Value="10" />
<asp:ListItem Text="25" Value="25" />
<asp:ListItem Text="50" Value="50" />
</asp:DropDownList>
<hr />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField HeaderText="employeeId" DataField="employeeId" />
<asp:BoundField HeaderText="employeeName" DataField="employeeName" />
<asp:BoundField HeaderText="employeeCompany" DataField="employeeCompany" />
</Columns>
</asp:GridView>
<br />
<asp:Repeater ID="rptPager" runat="server">
<ItemTemplate>
<asp:LinkButton ID="lnkPage" runat="server" Text = '<%#Eval("Text")
%>' CommandArgument = '<%#
Eval("Value") %>' Enabled = '<%#
Eval("Enabled") %>' OnClick = "Page_Changed"></asp:LinkButton>
</ItemTemplate>
</asp:Repeater>
</div>
Web form looks like below figure2.
Figure2
Now double click on the page and add the
following code.
Imports
System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page
Protected Sub
Page_Load(ByVal sender
As Object, ByVal
e As System.EventArgs)
Handles Me.Load
Me.GetCustomersPageWise(1)
End Sub
Private Sub
GetCustomersPageWise(ByVal pageIndex
As Integer)
'string constring =
ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
Using con As New SqlConnection("Data
Source=.; uid=sa; pwd=Password$2; database=custom paging")
Using cmd As New SqlCommand("GetCustomersPageWise",
con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@PageIndex",
pageIndex)
cmd.Parameters.AddWithValue("@PageSize",
Integer.Parse(ddlPageSize.SelectedValue))
cmd.Parameters.Add("@RecordCount",
SqlDbType.Int, 4)
cmd.Parameters("@RecordCount").Direction =
ParameterDirection.Output
con.Open()
Dim idr As IDataReader = cmd.ExecuteReader()
GridView1.DataSource = idr
GridView1.DataBind()
idr.Close()
con.Close()
Dim recordCount As Integer = Convert.ToInt32(cmd.Parameters("@RecordCount").Value)
Me.PopulatePager(recordCount, pageIndex)
End Using
End Using
End Sub
Private Sub
PopulatePager(ByVal recordCount
As Integer,
ByVal currentPage As Integer)
Dim dblPageCount As Double = CDbl(CDec(recordCount)
/ Decimal.Parse(ddlPageSize.SelectedValue))
Dim pageCount As Integer = CInt(Math.Ceiling(dblPageCount))
Dim pages As New List(Of ListItem)()
If pageCount > 0 Then
pages.Add(New ListItem("First",
"1", currentPage > 1))
For i As Integer = 1 To
pageCount
pages.Add(New ListItem(i.ToString(), i.ToString(), i <>
currentPage))
Next
pages.Add(New ListItem("Last",
pageCount.ToString(), currentPage < pageCount))
End If
rptPager.DataSource
= pages
rptPager.DataBind()
End Sub
Protected Sub
PageSize_Changed(ByVal sender
As Object,
ByVal e As EventArgs)
Me.GetCustomersPageWise(1)
End Sub
Protected Sub
Page_Changed(ByVal sender
As Object,
ByVal e As EventArgs)
Dim pageIndex As Integer = Integer.Parse(TryCast(sender,
LinkButton).CommandArgument)
Me.GetCustomersPageWise(pageIndex)
End Sub
End Class
Now run the application and test it.
Figure3
Now select data with DropDownList which is by
default display 10 records. Now selecting 25 records.
Figure4
Now click on the last from the below of the
GridView.
Figure5