ASP.NET MVC – How to use JQuery Datatable on Webforms

In this post we will integrate jQuery Datatable on ASP.NET MVC webforms. We will bind a HTML table from SQL data using jQuery. So let’s begin.

DOWNLOAD SOURCE CODE.

I’m using Visual studio 2017 and MS SQL Server 2014 for this example.

Features of jQuery Datable:

1. It is a Plugin for jQuery Javascript library.
2. Easy to integrate and highly flexible.
3. Inbuilt Pagination support, no need to write extra code.
4. Instant search: filters all the columns matching with supplied text.
5. Multi-column ordering – sort multiple columns at once.
6. Easy customization options : multiple themes for styling.

Project:

In your ASP.NET MVC project, create a class called Products as below:

YourMVCProject > Models > Products.cs:

namespace MVCWeb25.Models
{
public class Products
{
public string ProductName { get; set; }
public string CategoryName { get; set; }
public string QuantityPerUnit { get; set; }
public string UnitPrice { get; set; }
}

}

For this example I’m using NorthWind sample SQL database. We will fill our datatable from Product view:

Now create a new Controller with name Products as below:

YourProjectName > Controllers > ProductsController.cs:

using MVCWeb25.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web.Mvc;

namespace MVCWeb25.Controllers
{
public class ProductsController : Controller
{
public ActionResult Index()
{
return View();
}
}
}

We will now create a method for fetching our database records on our project. Create a new method named GetProducts() in ProductsController, this will return a List<Products> object.

GetProducts method:

public List<Products> GetProducts()
{

List<Products> products = new List<Products>();
try
{
String connectionString = "Data Source=192.168.0.106;Initial Catalog=NorthWind;User ID=sa;Password=789;Trusted_Connection=false;";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("Select ProductName,CategoryName,QuantityPerUnit,UnitPrice from View_ListOfProducts", con);
con.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);

DataTable dtProducts = new DataTable();

adapter.Fill(dtProducts);

foreach (DataRow dr in dtProducts.Rows)
{
products.Add(new Products
{
CategoryName = dr["CategoryName"].ToString(),
QuantityPerUnit = dr["QuantityPerUnit"].ToString(),
UnitPrice = dr["UnitPrice"].ToString(),
ProductName = dr["ProductName"].ToString()
});
}

adapter.Dispose();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
products = null;
}

return products;
}

Now create a ActionResult method to get this List object on our MVC view:

public ActionResult getProductList()
{
return Json(new { data = GetProducts() }, JsonRequestBehavior.AllowGet);
}

This will return data in JSON format of products to our javascript. After all the methods, the final controller will be like below:

ProductsController:

using MVCWeb25.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web.Mvc;

namespace MVCWeb25.Controllers
{
public class ProductsController : Controller
{
// GET: Products
public ActionResult Index()
{
return View();
}

public ActionResult getProductList()
{
return Json(new { data = GetProducts() }, JsonRequestBehavior.AllowGet);
}
public List<Products> GetProducts()
{

List<Products> products = new List<Products>();
try
{
String connectionString = "Data Source=192.168.0.106;Initial Catalog=NorthWind;User ID=sa;Password=789;Trusted_Connection=false;";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("Select ProductName,CategoryName,QuantityPerUnit,UnitPrice from View_ListOfProducts", con);
con.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);

DataTable dtProducts = new DataTable();

adapter.Fill(dtProducts);

foreach (DataRow dr in dtProducts.Rows)
{
products.Add(new Products
{
CategoryName = dr["CategoryName"].ToString(),
QuantityPerUnit = dr["QuantityPerUnit"].ToString(),
UnitPrice = dr["UnitPrice"].ToString(),
ProductName = dr["ProductName"].ToString()
});
}

adapter.Dispose();
con.Close();
con.Dispose();
}
catch (Exception ex)
{
products = null;
}

return products;
}
}
}

We will now add a View for our Index method to create jQuery datatable and display the product info.

YourProjectName > Views > Products.cshtml:

@model MVCWeb25.Models.Products

@{
Layout = null;
}

<!DOCTYPE html>

<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Products</title>
<link href="~/Content/bootstrap.css" rel="stylesheet" />
<link href="//cdn.datatables.net/1.10.20/css/jquery.dataTables.min.css" rel="stylesheet" />
<script src="~/Scripts/jquery-3.3.1.js"></script>
<script src="//cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>

<style>
.row
{
margin:2px !important;
}
</style>
</head>
<body style="padding:0px !important;margin:0px !important;width:100%;">

<div class="row">
<h2>Using Jquery Datatable in MVC .NET</h2>
<hr />
<div class="col-lg-8">
<table id="tblProducts" class="table table-striped table-bordered">
<thead>
<tr>
<th>Product</th>
<th>Category</th>
<th>Quantity per Unit</th>
<th>Unit Price</th>
</tr>
</thead>
</table>
</div>

</div>

<script>
$(document).ready(function () {
$('#tblProducts').DataTable(
{
"ajax":
{
"url": "/MVCWeb25/Products/getProductList",
"type": "GET",
"datatype": "json"
},
"columns": [
{ "data": "CategoryName" },
{ "data": "QuantityPerUnit" },
{ "data": "UnitPrice" },
{ "data": "ProductName" }
]
}
);
});
</script>
</body>
</html>

This view will be using our Model class Products.cs. Inside this MVC view we have created a HTML table with Id “tblProducts” and added only the headed section. We are adding the body of the table from the javascript section.

<script>
$(document).ready(function () {
$('#tblProducts').DataTable(
{
"ajax":
{
"url": "/MVCWeb25/Products/getProductList",
"type": "GET",
"datatype": "json"
},
"columns": [
{ "data": "CategoryName" },
{ "data": "QuantityPerUnit" },
{ "data": "UnitPrice" },
{ "data": "ProductName" }
]
}
);
});
</script>

The getProductList method gets called once our page is loaded and rows are added using json response data.

Please don’t forget to add the JQuery scripts:

<script src="~/Scripts/jquery-3.3.1.js"></script>
<script src="//cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>

Output:

You can download the source code from : ASP.NET MVC Using jQuery datatable source code download.