Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
HTML Markup
The
HTML Markup consists of:
GridView – For displaying data.
The
GridView consists of four
BoundField columns.
<div style="width:500px">
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="CustomerID" HeaderText="Customer Id" />
<asp:BoundField DataField="ContactName" HeaderText="Name" />
<asp:BoundField DataField="City" HeaderText="City" />
<asp:BoundField DataField="Country" HeaderText="Country" />
</Columns>
</asp:GridView>
</div>
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Services;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.Services
Populating the GridView with Dummy data
Inside the
Page Load event, the
GridView is populated with dummy records.
Note: The dummy
DataTable being populated has the same columns which will be returned from the SQL Query. This is done to avoid manual creation of
HTML Table. An
HTML Table can also be used.
The
jQuery DataTables plugin requires Table with
THEAD and
TBODY Tags and hence in order to render
GridView with these Tags the
UseAccessibleHeader and
HeaderRow.TableSection properties are set after the
GridView is populated with data.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
DataTable dummy = new DataTable();
dummy.Columns.Add("CustomerID");
dummy.Columns.Add("ContactName");
dummy.Columns.Add("City");
dummy.Columns.Add("Country");
dummy.Rows.Add();
gvCustomers.DataSource = dummy;
gvCustomers.DataBind();
//Required for jQuery DataTables to work.
gvCustomers.UseAccessibleHeader = true;
gvCustomers.HeaderRow.TableSection = TableRowSection.TableHeader;
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Dim dummy As DataTable = New DataTable()
dummy.Columns.Add("CustomerID")
dummy.Columns.Add("ContactName")
dummy.Columns.Add("City")
dummy.Columns.Add("Country")
dummy.Rows.Add()
gvCustomers.DataSource = dummy
gvCustomers.DataBind()
'Required for jQuery DataTables to work.
gvCustomers.UseAccessibleHeader = True
gvCustomers.HeaderRow.TableSection = TableRowSection.TableHeader
End If
End Sub
Class
The following class will be used to hold the
Customer records returned from the Database and it will be sent as
JSON to Client Side for population of
jQuery DataTable.
C#
public class Customer
{
public string CustomerID { get; set; }
public string ContactName { get; set; }
public string City { get; set; }
public string Country { get; set; }
}
VB.Net
Public Class Customer
Public Property CustomerID As String
Public Property ContactName As String
Public Property City As String
Public Property Country As String
End Class
WebMethod (PageMethod)
Inside the
WebMethod, the records from the
Customers Table are fetched using
DataReader and are inserted into a Generic List of Customer class objects.
Finally, the Generic List of Customer class objects are returned to the Client Side
jQuery AJAX function.
C#
[WebMethod]
public static List<Customer>GetCustomers()
{
List<Customer> customers = new List<Customer>();
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT CustomerID, ContactName, City, Country FROM Customers", con))
{
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
customers.Add(new Customer
{
CustomerID = sdr["CustomerID"].ToString(),
ContactName = sdr["ContactName"].ToString(),
City = sdr["City"].ToString(),
Country = sdr["Country"].ToString()
});
}
}
con.Close();
}
}
return customers;
}
VB.Net
<WebMethod>
Public Shared Function GetCustomers() As List(Of Customer)
Dim customers As List(Of Customer) = New List(Of Customer)()
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand("SELECT CustomerID, ContactName, City, Country FROM Customers", con)
con.Open()
Using sdr As SqlDataReader = cmd.ExecuteReader()
While sdr.Read()
customers.Add(New Customer With {
.CustomerID = sdr("CustomerID").ToString(),
.ContactName = sdr("ContactName").ToString(),
.City = sdr("City").ToString(),
.Country = sdr("Country").ToString()
})
End While
End Using
con.Close()
End Using
End Using
Return customers
End Function
Applying jQuery DataTables plugin to GridView
Inside the jQuery
document ready event handler, a
jQuery AJAX call is made to the
GetCustomers WebMethod (PageMethod).
Inside the Success event handler, the
GridView has been applied with
jQuery DataTables plugin and the
JSON data returned from the
WebMethod (PageMethod) is assigned to the
data property of the
jQuery DataTables plugin.
The
jQuery DataTables plugin has been assigned following properties:
bLengthChange – true
The Records per Page
DropDownList will be shown if set to True and
hidden if set to False. Default is True.
lengthMenu – Array values
The Text and Value for the Records per Page
DropDownList. It is a
multi-dimensional array. The first set is the Value parts while the second set is the Text parts.
bFilter – true
The Search Box will be displayed if set to True and hidden if set to False. Default is True.
bSort – true
The Sorting feature will be enabled if set to True and disabled if set to False. Default is True.
bPaginate – true
The Paging feature will be enabled if set to True and disabled if set to False. Default is True.
Columns – Array values
The names of the Columns to be displayed and mapped to the
JSON data are specified.
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/2.3.6/js/dataTables.min.js"></script>
<link href="https://cdn.datatables.net/2.3.6/css/dataTables.dataTables.min.css" rel="stylesheet" type="text/css" />
<script type="text/javascript">
$(function () {
$.ajax({
type: "POST",
url: "CS.aspx/GetCustomers",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnSuccess,
failure: function (response) {
alert(response.d);
},
error: function (response) {
alert(response.d);
}
});
});
function OnSuccess(response) {
$("[id*=gvCustomers]").DataTable(
{
bLengthChange: true,
lengthMenu: [[5, 10, -1], [5, 10, "All"]],
bFilter: true,
bSort: true,
bPaginate: true,
data: response.d,
columns: [{ 'data': 'CustomerID' },
{ 'data': 'ContactName' },
{ 'data': 'City' },
{ 'data': 'Country' }]
});
};
</script>
Screenshot
![Populate jQuery DataTable using WebMethod (PageMethod) and AJAX in ASP.Net]()
Browser Compatibility
* All browser logos displayed above are property of their respective owners.
Demo
Downloads
