In this article I will explain a simple tutorial with an example, how to use jQuery DataTable for displaying data from Database in ASP.Net with C# and VB.Net.
The jQuery DataTable will display data from SQL Server Database using jQuery AJAX and WebMethod in ASP.Net with C# and VB.Net.
Database
Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.
HTML Markup
The following HTML Markup consists of an ASP.Net GridView with four BoundField columns.
The GridView is wrapped inside a DIV with fixed width in order to set the width of the jQuery DataTable.
<div style="width: 500px">
<asp:GridView ID="gvCustomers" runat="server" CssClass="display compact" 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
ImportsSystem.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)
The following WebMethod (PageMethod) will handle calls from the jQuery AJAX function.
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/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
<link href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.css" rel="stylesheet" type="text/css" />
<script type="text/javascript">
$(function () {
$.ajax({
type: "POST",
url: "Default.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
![Simple jQuery DataTables Tutorial with example in ASP.Net]()
Browser Compatibility
The above code has been tested in the following browsers.
![Internet Explorer]()
![FireFox]()
![Chrome]()
![Safari]()
![Opera]()
* All browser logos displayed above are property of their respective owners.
Demo
Downloads
