Custom Paging for GridView with DropDownList using jQuery Ajax in ASP.Net

lingers
 
on Sep 14, 2021 06:09 AM
Sample_490813.zip
293 Views

I have this page with gridview and it uses jquery server side paging. I want to add jquery dropdown with Number of items to display on pages from server side on select.

My Code

<script type="text/javascript" src=" https://code.jquery.com/jquery-3.5.1.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" />
<script type="text/javascript" src="https://cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css" />
<script type="text/javascript" src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script src="ASPSnippets_Pager.min.js" type="text/javascript"></script>
<script type="text/javascript">
    $(function () {
        GetCustomers(parseInt(1));
        var checked = [];
        $('[id*=chkSelect]').on('click', function () {
            if ($(this).is(":checked")) {
                checked.push($(this).closest('tr').find('td').eq(1).html());
            } else {
                checked.pop($(this).closest('tr').find('td').eq(1).html());
            }

            $('[id*=hfCheckedIds]').val(checked.join(','));
        });
    });

    $(document).on("click", '.Pager .page', function () {
        GetCustomers(parseInt($(this).attr('page')));
    });
    var term = '';
    $(document).on("keyup", 'input[type=search]', function () {
        term = $(this).val();
        GetCustomers(parseInt(1));

    });

    $(document).on('click', '.view', function () {
        $('[id*=hfId]').val($(this).closest('tr').find('td').eq(1).html());
    });

    function SearchTerm() {
        return jQuery.trim($("input[type=search]").val());
    };

   
    var i = 0;
    function GetCustomers(pageIndex) {
        $.ajax({
            type: "POST",
            url: "usee.aspx/GetCustomers",
            data: '{searchTerm: "' + SearchTerm() + '", pageIndex: ' + pageIndex + '}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (response) {
                $('[id$=gvDetails]').prepend($("<thead></thead>").append($('[id$=gvDetails]').find("tr:first"))).DataTable().destroy();
                var xmlDoc = $.parseXML(response.d);
                var xml = $(xmlDoc);
                var customers = xml.find("job_cylinder");
                var row = $("[id$=gvDetails] tbody tr:last-child").eq(0).clone(true);
                $("[id$=gvDetails] tbody tr").not($("[id$=gvDetails] tbody tr:first-child")).remove();
                $.each(customers, function () {
                    $("td", row).eq(1).html($(this).find("id").text());
                    $("td", row).eq(2).html($(this).find("no").text());
                    $("td", row).eq(3).html($(this).find("pid").text());
                    $("td", row).eq(4).html($(this).find("posino").text());
                    $("td", row).eq(5).html($(this).find("description").text());
                    $("td", row).eq(6).html($(this).find("colour").text());
                    $("td", row).eq(7).html($(this).find("orderid").text());
                    $("td", row).eq(8).html($(this).find("deliverydate").text());
                    $("td", row).eq(9).html($(this).find("purchaseorderno").text());
                    $("td", row).eq(10).html($(this).find("supplier").text());
                    $("td", row).eq(11).html($(this).find("waybillno").text());
                    $("td", row).eq(12).html($(this).find("totalrev").text());

                    // Set color based on condition.
                    if (parseInt($(this).find("totalrev").text()) > 100) {
                        $("td", row).eq(12).css("background-color", "green")
                    } else {
                        $("td", row).eq(12).css("background-color", "") }
                    $("td", row).eq(13).html($(this).find("comment").text());
                    $("[id$=gvDetails]").append(row);
                    row = $("[id$=gvDetails] tbody tr:last-child").eq(0).clone(true);
                });
                $("[id$=gvDetails] tbody tr:first-child").remove();
                if (i != 0) {
                    $('[id$=gvDetails]').DataTable({
                        "paging": false,
                        "info": false
                    });
                } else {
                    $('[id$=gvDetails]')
                        .prepend($("<thead></thead>").append($('[id$=gvDetails]').find("tr:first")))
                        .DataTable({
                            "paging": false,
                            "info": false
                        });
                }
                  if (term != '') {
                    $('input[type=search]').val(term);
                }
                $('input[type=search]').focus();
                    i++;

                var pager = xml.find("Pager");
                $(".Pager").ASPSnippets_Pager({
                    ActiveCssClass: "current",
                    PagerCssClass: "pager",
                    PageIndex: parseInt(pager.find("PageIndex").text()),
                    PageSize: parseInt(pager.find("PageSize").text()),
                    RecordCount: parseInt(pager.find("RecordCount").text())
                });
            },
            failure: function (response) {
                alert(response.responseText);
            },
            error: function (response) {
                alert(response.responseText);
            }
        });
    }
</script>

 

<asp:DropDownList ID="DropDownList1" runat="server">
    <asp:ListItem>20</asp:ListItem>
    <asp:ListItem>50</asp:ListItem>
    <asp:ListItem>100</asp:ListItem>
</asp:DropDownList>
<br />
<br />
<asp:HiddenField ID="hfCheckedIds" runat="server" />
<asp:HiddenField ID="hfId" runat="server" />
<asp:GridView ID="gvDetails" runat="server" AutoGenerateColumns="False"
    BackColor="White" BorderColor="#CCCCCC" BorderStyle="None"
    Font-Names="Century Gothic" Font-Size="XX-Small" DataKeyNames="id"
    Width="100%" Style="margin-bottom: 0px" CssClass="grid" BorderWidth="1px" CellPadding="3">
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:CheckBox ID="chkSelect" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="id" HeaderText="stockid" />   
        <asp:BoundField DataField="no" HeaderText="Base No" />
        <asp:BoundField DataField="pid" HeaderText="Prod.Code" />
        <asp:BoundField DataField="posino" HeaderText="Film No" />
        <asp:BoundField DataField="description" HeaderText="Description" />
        <asp:BoundField DataField="colour" HeaderText="Colour" />                                          
        <asp:BoundField DataField="deliverydate" HeaderText="Date.Rec" />
        <asp:BoundField DataField="purchaseorderno" HeaderText="Order No" />
        <asp:BoundField DataField="supplier" HeaderText="Supplier" />
        <asp:BoundField DataField="waybillno" HeaderText="Waybill No" />
        <asp:BoundField DataField="totalrev" HeaderText="Total Rev" />
        <asp:BoundField DataField="comment" HeaderText="Comment" />
        <asp:TemplateField>
            <ItemTemplate>                                  
                <asp:Button CssClass="view"  Text="Unuseable" ID="Inkview" runat="server" OnClick="Inkview_Click" ForeColor="white"  BackColor="#FF6600"/>
            </ItemTemplate>                               
        </asp:TemplateField>              
    </Columns>
    <FooterStyle BackColor="White" ForeColor="#000066" />
    <HeaderStyle BackColor="#7EA9D3" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
    <RowStyle ForeColor="#000000" />
    <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="#000000" />
    <SortedAscendingCellStyle BackColor="#F1F1F1" />
    <SortedAscendingHeaderStyle BackColor="#007DBB" />
    <SortedDescendingCellStyle BackColor="#CAC9C9" />
    <SortedDescendingHeaderStyle BackColor="#00547E" />
</asp:GridView>
<br />
<div class="Pager" style="width: 100%"></div>

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Sep 14, 2021 07:38 AM

Hi lingers,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Stored Procedure

CREATE PROCEDURE [dbo].[Employees_GetEmployeesPageWise]
    @SearchTerm VARCHAR(100) = '',
    @PageIndex INT = 1,
    @PageSize INT = 20,
    @RecordCount INT OUTPUT
AS
BEGIN  
    SET NOCOUNT ON;
 
    SELECT  (ROW_NUMBER() OVER(Order By EmployeeID)) AS RowNumber,
            *
    INTO #Results
    FROM [Employees]
    WHERE (FirstName LIKE @SearchTerm + '%' OR @SearchTerm = '')   
    OR (LastName LIKE @SearchTerm + '%' OR @SearchTerm = '')
 
    SELECT @RecordCount = Count(*) FROM #Results
 
    SELECT * FROM #Results WHERE
    RowNumber BETWEEN  (@PageIndex-1)*@PageSize + 1 AND (((@PageIndex-1)*@PageSize + 1)+@PageSize)-1 OR @PageIndex = -1
 
    DROP TABLE #Results
END

HTML

<script type="text/javascript" src=" https://code.jquery.com/jquery-3.5.1.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" />
<script type="text/javascript" src="https://cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css" />
<script type="text/javascript" src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script src="ASPSnippets_Pager.min.js" type="text/javascript"></script>
<script type="text/javascript">
    $(function () {
        GetEmployees(parseInt(1));
        var checked = [];
        $('[id*=chkSelect]').on('click', function () {
            if ($(this).is(":checked")) {
                checked.push($(this).closest('tr').find('td').eq(1).html());
            } else {
                checked.pop($(this).closest('tr').find('td').eq(1).html());
            }

            $('[id*=hfCheckedIds]').val(checked.join(','));
        });
    });

    $(document).on("click", '.Pager .page', function () {
        GetEmployees(parseInt($(this).attr('page')));
    });

    var term = '';
    $(document).on("keyup", 'input[type=search]', function () {
        term = $(this).val();
        GetEmployees(parseInt(1));
    });

    $(document).on("change", '[id*=ddlPageSize]', function () {
        GetEmployees(parseInt(1));
    });

    $(document).on('click', '.view', function () {
        $('[id*=hfId]').val($(this).closest('tr').find('td').eq(1).html());
    });

    function SearchTerm() {
        return jQuery.trim($("input[type=search]").val());
    };

    var i = 0;
    function GetEmployees(pageIndex) {
        $.ajax({
            type: "POST",
            url: "Default.aspx/GetEmployees",
            data: '{searchTerm: "' + SearchTerm() + '", pageIndex: ' + pageIndex + ', pageSize: ' + $("[id*=ddlPageSize]").val() + '}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (response) {
                $('[id$=gvEmployees]').prepend($("<thead></thead>").append($('[id$=gvEmployees]').find("tr:first"))).DataTable().destroy();
                var xmlDoc = $.parseXML(response.d);
                var xml = $(xmlDoc);
                var Employees = xml.find("Employees");
                var row = $("[id$=gvEmployees] tbody tr:last-child").eq(0).clone(true);
                $("[id$=gvEmployees] tbody tr").not($("[id$=gvEmployees] tbody tr:first-child")).remove();
                $.each(Employees, function () {
                    $("td", row).eq(1).html($(this).find("EmployeeID").text());
                    $("td", row).eq(2).html($(this).find("FirstName").text());
                    $("td", row).eq(3).html($(this).find("LastName").text());
                    $("td", row).eq(4).html($(this).find("Address").text());
                    $("td", row).eq(5).html($(this).find("City").text());
                    $("td", row).eq(6).html($(this).find("Country").text());
                    $("[id$=gvEmployees]").append(row);
                    row = $("[id$=gvEmployees] tbody tr:last-child").eq(0).clone(true);
                });
                $("[id$=gvEmployees] tbody tr:first-child").remove();
                if (i != 0) {
                    $('[id$=gvEmployees]').DataTable({
                        "paging": false,
                        "info": false
                    });
                } else {
                    $('[id$=gvEmployees]')
                        .prepend($("<thead></thead>").append($('[id$=gvEmployees]').find("tr:first")))
                        .DataTable({
                            "paging": false,
                            "info": false
                        });
                }
                if (term != '') {
                    $('input[type=search]').val(term);
                }
                $('input[type=search]').focus();
                i++;

                var pager = xml.find("Pager");
                $(".Pager").ASPSnippets_Pager({
                    ActiveCssClass: "current",
                    PagerCssClass: "pager",
                    PageIndex: parseInt(pager.find("PageIndex").text()),
                    PageSize: parseInt(pager.find("PageSize").text()),
                    RecordCount: parseInt(pager.find("RecordCount").text())
                });
            },
            error: function (response) {
                alert(response.responseText);
            }
        });
    }
</script>
<asp:HiddenField ID="hfCheckedIds" runat="server" />
<asp:HiddenField ID="hfId" runat="server" />
Page Size:
<asp:DropDownList runat="server" ID="ddlPageSize">
    <asp:ListItem Text="1" Value="1" />
    <asp:ListItem Text="5" Value="5" />
    <asp:ListItem Text="10" Value="10" />
</asp:DropDownList>
<asp:GridView ID="gvEmployees" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeID" Width="100%">
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:CheckBox ID="chkSelect" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="EmployeeID" HeaderText="Id" />
        <asp:BoundField DataField="FirstName" HeaderText="First Name" />
        <asp:BoundField DataField="LastName" HeaderText="Last Name" />
        <asp:BoundField DataField="Address" HeaderText="Address" />
        <asp:BoundField DataField="City" HeaderText="City" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
        <asp:TemplateField>
            <ItemTemplate>
                <asp:Button CssClass="view" Text="Unuseable" ID="Inkview" runat="server"
                    ForeColor="white" BackColor="#FF6600" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<br />
<div class="Pager" style="width: 100%"></div>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindDummyRow();
    }
}

private void BindDummyRow()
{
    DataTable dummy = new DataTable();
    dummy.Columns.Add("EmployeeID");
    dummy.Columns.Add("FirstName");
    dummy.Columns.Add("LastName");
    dummy.Columns.Add("Address");
    dummy.Columns.Add("City");
    dummy.Columns.Add("Country");
    dummy.Rows.Add();
    gvEmployees.DataSource = dummy;
    gvEmployees.DataBind();
}

[WebMethod]
public static string GetEmployees(int pageIndex, string searchTerm, int pageSize)
{
    string query = "[Employees_GetEmployeesPageWise]";
    SqlCommand cmd = new SqlCommand(query);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@SearchTerm", searchTerm);
    cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
    cmd.Parameters.AddWithValue("@PageSize", pageSize);
    cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;

    return GetData(cmd, pageIndex, pageSize).GetXml();
}

private static DataSet GetData(SqlCommand cmd, int pageIndex, int pageSize)
{
    string strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (DataSet ds = new DataSet())
            {
                sda.Fill(ds, "Employees");
                DataTable dt = new DataTable("Pager");
                dt.Columns.Add("PageIndex");
                dt.Columns.Add("PageSize");
                dt.Columns.Add("RecordCount");
                dt.Rows.Add();
                dt.Rows[0]["PageIndex"] = pageIndex;
                dt.Rows[0]["PageSize"] = pageSize;
                dt.Rows[0]["RecordCount"] = cmd.Parameters["@RecordCount"].Value;
                ds.Tables.Add(dt);

                return ds;
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        BindDummyRow()
    End If
End Sub

Private Sub BindDummyRow()
    Dim dummy As DataTable = New DataTable()
    dummy.Columns.Add("EmployeeID")
    dummy.Columns.Add("FirstName")
    dummy.Columns.Add("LastName")
    dummy.Columns.Add("Address")
    dummy.Columns.Add("City")
    dummy.Columns.Add("Country")
    dummy.Rows.Add()
    gvEmployees.DataSource = dummy
    gvEmployees.DataBind()
End Sub

<WebMethod>
Public Shared Function GetEmployees(ByVal pageIndex As Integer, ByVal searchTerm As String, ByVal pageSize As Integer) As String
    Dim query As String = "[Employees_GetEmployeesPageWise]"
    Dim cmd As SqlCommand = New SqlCommand(query)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@SearchTerm", searchTerm)
    cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
    cmd.Parameters.AddWithValue("@PageSize", pageSize)
    cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output
    Return GetData(cmd, pageIndex, pageSize).GetXml()
End Function

Private Shared Function GetData(ByVal cmd As SqlCommand, ByVal pageIndex As Integer, ByVal pageSize As Integer) As DataSet
    Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
    Using con As SqlConnection = New SqlConnection(strConnString)
        Using sda As SqlDataAdapter = New SqlDataAdapter()
            cmd.Connection = con
            sda.SelectCommand = cmd
            Using ds As DataSet = New DataSet()
                sda.Fill(ds, "Employees")
                Dim dt As DataTable = New DataTable("Pager")
                dt.Columns.Add("PageIndex")
                dt.Columns.Add("PageSize")
                dt.Columns.Add("RecordCount")
                dt.Rows.Add()
                dt.Rows(0)("PageIndex") = pageIndex
                dt.Rows(0)("PageSize") = pageSize
                dt.Rows(0)("RecordCount") = cmd.Parameters("@RecordCount").Value
                ds.Tables.Add(dt)
                Return ds
            End Using
        End Using
    End Using
End Function

Screenshot