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.
Figure 1
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.
Figure 2
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) HandlesMe.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.
Figure 3
Now select data with DropDownList which is by default display 10 records. Now selecting 25 records.
Figure 4
Now click on the last from the below of the GridView.
Figure 5