Paging, Sorting, Searching In ASP.NET by JQuery DataTable ~ Microsoft .Net Expert Call@+91-9555484663

Paging, Sorting, Searching In ASP.NET by JQuery DataTable

Nowadays it is most popular way to apply client side searching, sorting and pagination (client/server side) through ajax/html request from any tabular format.

--Database.............................

USE [NORTHWND]

/****** Object: Table [dbo].[Customers] Script Date: 26-11-2016 21:37:43 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Customers](

[CustomerID] [nchar](5) NOT NULL,

[CompanyName] [nvarchar](40) NOT NULL,

[ContactName] [nvarchar](30) NULL,

[ContactTitle] [nvarchar](30) NULL,

[Address] [nvarchar](60) NULL,

[City] [nvarchar](15) NULL,

[Region] [nvarchar](15) NULL,

[PostalCode] [nvarchar](10) NULL,

[Country] [nvarchar](15) NULL,

[Phone] [nvarchar](24) NULL,

[Fax] [nvarchar](24) NULL,

CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED

(

[CustomerID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

First we will take WebForm in Asp.net Application which name is Default.aspx

Code for Default.aspx.....................

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<title>JQuery Data Table in Asp.Net</title>

$(document).ready(function () {

// Setup - add a text input to each footer cell

$('#example tfoot th').each(function () {

var title = $(this).text();

$(this).html('<input type="text" placeholder="Search ' + title + '" />');

});

// DataTable

var table = $('#example').DataTable();

// Apply the search

table.columns().every(function () {

var that = this;

$('input', this.footer()).on('keyup change', function () {

if (that.search() !== this.value) {

that

.search(this.value)

.draw();

}

});

</script>

.paging_full_numbers span.paginate_button {

background-color: #fff;

}

.paging_full_numbers span.paginate_button:hover {

background-color: #ccc;

}

.paging_full_numbers span.paginate_active {

background-color: #99B3FF;

}

</style>

</head>

<body>

<table class="table table-striped table-bordered " style="font-family: Serif;"

border="1px" id="example" >

<thead>

<tr>

<th>CustomerID</th>

<th>CompanyName</th>

<th>Contact Name</th>

<th>Address</th>

<%-- <th> Region</th>

<th>PostalCode</th>

<th>Country</th>--%>

<th>Actions</th>

</tr>

</thead>

<tfoot>

<tr>

<th>CustomerID</th>

<th>CompanyName</th>

<th>Contact Name</th>

<th>Address</th>

<%--<th> Region</th>

<th>PostalCode</th>

<th>Country</th>--%>

<th>Actions</th>

</tr>

</tfoot>

</tbody>

</table>

</div>

</form>

</body>

</html>

Code for Default.aspx.cs.....................

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Configuration;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindDataTable();

}

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString);

public void BindDataTable()

{

con.Open();

SqlCommand cmd = new SqlCommand("SELECT [CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax]FROM[dbo].[Customers]", con);

SqlDataReader dr = cmd.ExecuteReader();

String result = "";

Int32 i = 1;

while (dr.Read())

{

result += "<tr>";

result += " <td>" +i.ToString() +"</td>";

result += " <td>" + dr["CustomerID"] + "</td>";

result += " <td>" + dr["CompanyName"] + "</td>";

result += " <td>" + dr["ContactName"] + "</td>";

result += " <td>" + dr["Address"] + "</td>";

result += " <td>" + dr["City"] + "</td>";

//result += " <td class=\"center\">" + dr["Region"] + "</td>";

//result += " <td class=\"center\">" + dr["PostalCode"] + "</td>";

//result += " <td class=\"center\">" + dr["Country"] + "</td>";

result += " <td class=\"center\">";

result += " <a class=\"btn btn-info\" href=\"Edit.aspx?ID=" + dr[0] + "\">";

result += " <i class=\"icon-edit icon-white\"></i> ";

result += " Edit ";

result += " </a>";

result += " <a class=\"btn btn-info\" href=\"Delete.aspx?ID=" + dr[0] + "\">";

result += " <i class=\"icon-edit icon-white\"></i> ";

result += " Delete ";

result += " </a>";

result += " </td>";

result += " </tr>";

tlist.InnerHtml = result;

i++;

}

con.Close();

}

Result

<!DOCTYPE html>
<html>
<head>
    <title></title>
    <meta charset="utf-8" />

    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>

    <link href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css" rel="stylesheet" />
    <style type="text/css">
        tfoot input {
            width: 100%;
            padding: 3px;
            box-sizing: border-box;
        }
    </style>




    <script type="text/javascript">

        $(document).ready(function () {
            // Setup - add a text input to each footer cell
            $('#example tfoot th').each(function () {
                var title = $(this).text();
                $(this).html('<input type="text" placeholder="Search ' + title + '" />');
            });

            // DataTable
            var table = $('#example').DataTable();

            // Apply the search
            table.columns().every(function () {
                var that = this;

                $('input', this.footer()).on('keyup change', function () {
                    if (that.search() !== this.value) {
                        that
                            .search(this.value)
                            .draw();
                    }
                });
            });
        });
    </script>
</head>
<body>
    <table id="example" class="display" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </tfoot>
        <tbody>
            <tr>
                <td>Kush Tiwari</td>
                <td>.Net Developer</td>
                <td>Hard Shell Pvt Ltd</td>
                <td>29</td>
                <td>2016/11/24</td>
                <td>50000</td>
            </tr>

            <tr>
                <td>Udayan Maiti</td>
                <td>Sr .Net Developer</td>
                <td>Hard Shell Pvt Ltd</td>
                <td>31</td>
                <td>2015/11/24</td>
                <td>67000</td>
            </tr>

........................................................

............................................
</tbody>
</table>
</body>
</html>

4 comments:

Unknown26 November 2016 at 09:24
Nice Code
Unknown13 April 2017 at 02:24
Right,Good to see these helpful information here Asp.net Application Company .Thanks lots for sharing them with us.
Unknown18 July 2017 at 04:37
Very informative post!

Biztalk Online Training Hyderabad
Inwizards27 July 2017 at 01:15
I just see the post i am so happy to the communication science post of information's.So I have really enjoyed and reading your blogs for these posts.Any way I’ll be replay for your great thinks and I hope you post again soon.
.Net Development Services
Hire .Net Developers India

Microsoft .Net Expert Call@+91-9555484663

Saturday 26 November 2016