ASP.NET GridView In VB.NET

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