• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

Custom Paging for Grid View

Hi,
Can somebody please help me with implmenting custom pagination for my gridview using sql server ROW_NUMBER() .my current code looks like this.i am going to have a dropdown list with numbers like 10,20,30  etc..to choose from .my pagination should look like 1234....Next with hyperlinks.I  saw so many articles ,but they are all implemented either using table adapters or using different format to choose pages.
My ASPX looks like below
  <asp:Label ID="Label1" runat="server" Text="ResultsPerPage"></asp:Label>
                    <asp:DropDownList ID="cmbResultsPerPage" runat="server">
                        <asp:ListItem Value="10">10</asp:ListItem>
                        <asp:ListItem Value="20">20</asp:ListItem>
                        <asp:ListItem Value="30">30</asp:ListItem>
                    </asp:DropDownList>
<asp:GridView ID="gvEmployees" runat="Server" Width="96%" GridLines="none" ShowHeader="false" AutoGenerateColumns="false" AllowPaging="True">
                    <RowStyle HorizontalAlign="left" />
                    <Columns>
                            <asp:TemplateField>
                                <ItemTemplate>
                                    <asp:CheckBox ID="EmployeeSelector" runat="server" />
                                </ItemTemplate>
                            </asp:TemplateField>
                        <asp:BoundField ItemStyle-Width="25%" DataField="FirstName" />
                        <asp:BoundField ItemStyle-Width="25%" DataField="LastName" />
                        <asp:BoundField ItemStyle-Width="25%" DataField="Organization" />
                       
                    </Columns>
                </asp:GridView>
 
My ASPx.vb Code behind looks like below
 
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
 
     
        Me.gvEmployees.DataSource = Biz.BAL.GetEmployeesByFilter("", "")
        Me.gvEmployees.DataBind()
    End Sub
 
    Protected Sub btnGo_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGo.Click
        Me.gvEmployees.DataSource = Biz.BAL.GetEmployeesByFilter(Me.cmbFilterType.SelectedItem.Value, Me.txtFilterValue.Text)
        Me.gvEmployees.DataBind()
    End Sub
 
my BIZ layer looks like below
 
 Public Class BAL
        Public Shared Function GetEmployeesByFilter(ByVal filtertype As String, ByVal filtervalue As String) As Employee()
            Return Data.DAL.GetEmployeesByFilter(filtertype, filtervalue)
        End Function
 
      
    End Class
 
My DAL Code looks like below
 
 Public Shared Function GetEmployeesByFilter(ByVal filtertype As String, ByVal filtervalue As String) As Employee()
            ''SQL text used for simplicity...recommend using stored procedures though 
            'Dim sql As String = "SELECT FirstName,LastName,Organization FROM Employee"
            'If Not [String].IsNullOrEmpty(filtertype) Then
            '    '  sql += " WHERE Country = '" & country & "'"
            '    sql += " WHERE " & filtertype & " = '" & filtervalue & "'"
            'End If
            Dim conn As DbConnection = GetDBConnection()
 
            Dim cmd As New SqlCommand("uspGetEmployee", conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@FilterType", filtertype)
            cmd.Parameters.AddWithValue("@FilterValue", filtervalue)
 
            conn.Open()
 
            'Dim cmd As DbCommand = conn.CreateCommand()
            'cmd.CommandText = sql
 
 
            Dim reader As DbDataReader = cmd.ExecuteReader()
            Dim empls As New List(Of Employee)()
            While reader.Read()
                Dim empl As New Employee()
                empl.FirstName = reader("FirstName").ToString()
                empl.LastName = reader("LastName").ToString()
                empl.Organization = reader("Organization").ToString()
 
                empls.Add(empl)
            End While
            reader.Close()
            conn.Close()
            Return empls.ToArray()
        End Function
 
My Stored Procedure Looks like below
 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspGetEmployee]
 @FilterType [nvarchar](50),
 @FilterValue[nvarchar](50)
   
AS
BEGIN
    SET NOCOUNT ON;
       
      if @FilterValue = '' or @filterValue is null
      SELECT [Employee].[FirstName], [Employee].[LastName], [Employee].[Organization]
                    FROM [Employee]
      else
 
 
      EXEC ( 'SELECT [Employee].[FirstName], [Employee].[LastName], [Employee].[Organization]
            FROM [Employee] where ' + @FilterType + ' = ''' +@FilterValue + '''' )
END

Open in new window

0
nagdotnet
Asked:
nagdotnet
1 Solution
 
GiftsonDJohnCommented:
0
 
Anurag ThakurCommented:
here is a link to one of the articles i have used myself for implememting custom pagination in a gridveiw control
http://www.4guysfromrolla.com/articles/031506-1.aspx
0
 
nagdotnetAuthor Commented:
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now