Using jQuery DataTables with GridView in ASPNet

In this article I will explain with an example, how to use jQuery DataTables with GridView in ASP.Net with C# and VB.Net.

This article will explain how to implement Paging, Sorting, Filtering and Searching in GridView on Client Side using jQuery DataTables 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.

Download and install Northwind Database

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.

<asp:GridView ID="GridView1" runat="server" CssClass="display compact" AutoGenerateColumns="false">

<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.

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

VB.Net

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Populating GridView from database

Inside the Page Load event of the page, the GridView is populated with records from the Customers table of the Northwind database.

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.

protected void Page_Load(object sender, EventArgs e)

{

if (!this.IsPostBack)

{

this.BindGrid();

}

private void BindGrid()

{

string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

using (SqlConnection con = new SqlConnection(constr))

{

using (SqlDataAdapter sda = new SqlDataAdapter("SELECT CustomerID, ContactName, City, Country FROM Customers", con))

{

using (DataTable dt = new DataTable())

{

sda.Fill(dt);

GridView1.DataSource = dt;

GridView1.DataBind();

}

//Required for jQuery DataTables to work.

GridView1.UseAccessibleHeader = true;

GridView1.HeaderRow.TableSection = TableRowSection.TableHeader;

}

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

If Not Me.IsPostBack Then

Me.BindGrid()

End If

End Sub

Private Sub BindGrid()

Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

Using con As New SqlConnection(constr)

Using sda As New SqlDataAdapter("SELECT CustomerID, ContactName, City, Country FROM Customers", con)

Using dt As New DataTable()

sda.Fill(dt)

GridView1.DataSource = dt

GridView1.DataBind()

End Using

'Required for jQuery DataTables to work.

GridView1.UseAccessibleHeader = True

GridView1.HeaderRow.TableSection = TableRowSection.TableHeader

End Sub

Applying jQuery DataTables plugin to GridView

Inside the jQuery document ready event handler, the GridView has been applied with 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.