Click here to Skip to main content
15,867,686 members
Articles / Web Development / HTML

jQuery DataTable with AJAX on ASP.NET

Rate me:
Please Sign up or sign in to vote.
4.64/5 (9 votes)
15 Aug 2016CPOL4 min read 62.7K   1.8K   7   3
We are examine to customize AJAX interface custom search elements

Introduction

Every web developer uses a few jQuery components. The biggest problem is customization. Today,  we will be looking at a jQuery DataTable custom search elements on ajax interface. We are built this project on ASP.NET MVC.

Background

jQuery DataTable gives ajax interface to us for custom ajax operations. But it wants to give back some values. Ajax sends some values to the server and gets the result back. This process is a little complicated.

Developing project

You should create an ASP.NET MVC project (or use the sample project). It is typically, no required authanticate etc. Now, create a simple model object to generate a datatable. Right click Models folder and select "Add>Class" menu elements. Use "Student.cs" for a class name. This class contains the following lines:

namespace DataTableSample.Models
{
    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string SurName { get; set; }
        public string ClassRoom { get; set; }
    }
}

We should create a controller with the name DTSampleController.cs. You have to click the Controllers folder on Solution Explorer and follow "Add>Controller..." menu items. Select "MVC 5 Controller - Empty" on follow window and click "Add" button. Give "DTSampleController" for name.

Here is the class shown below:

namespace DataTableSample.Controllers
{
    public class DTSampleController : Controller
    {
        // GET: DTSample
        public ActionResult Index()
        {
            return View();
        }
    }
}

We are using the index method for the presentation datatable in view. Create a view for the index method. However, we dont give a method because we using the student model during ajax operations.

Ok, we are in the view screen. Before proceeding we must add the jquery.datatables package in NuGet or you can do it manually if you prefer. After initializing jquery.datatables in the project, insert references Views/Sahred/_Layout.cshtml as shown below in bold:

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>@ViewBag.Title - My ASP.NET Application</title>
   @Styles.Render("~/Content/css")
   @Scripts.Render("~/bundles/modernizr")
   <link href="~/Content/DataTables/css/jquery.dataTables.min.css" rel="stylesheet" />
</head>
<body>
<div class="navbar navbar-inverse navbar-fixed-top">
<div class="container">
<div class="navbar-header">
<button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
   @Html.ActionLink("Application name", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })
   </div>
<div class="navbar-collapse collapse">
<ul class="nav navbar-nav">
<li>@Html.ActionLink("Home", "Index", "Home")</li>
<li>@Html.ActionLink("About", "About", "Home")</li>
<li>@Html.ActionLink("Contact", "Contact", "Home")</li>
</ul>
</div>
</div>
</div>
<div class="container body-content">
   @RenderBody()
   <hr />
<footer>
<p>&copy; @DateTime.Now.Year - My ASP.NET Application</p>
</footer>
</div>
 @Scripts.Render("~/bundles/jquery")
   @Scripts.Render("~/bundles/bootstrap")
   <script src="~/Scripts/DataTables/jquery.dataTables.min.js"></script>
   @RenderSection("scripts", required: false)
   </body>
</html>

Then come back to index.cshtml. Create an HTML table as shown below for students model

 @{
 ViewBag.Title = "Index";
 }

<div>
  <table id="demoTable">
    <thead>
      <tr>
        <td>Name</td>
        <td>Surname</td>
        <td>Classroom</td>
      </tr>
    </thead>
  </table>
</div>

Note that there is no tbody tag. This is because AJAX is going to generate it. Now write datatable scripts under the page.

@section scripts {
 <script type="text/javascript">
   $(document).ready(function () {
     $("#demoTable").dataTable();
   });
 </script>
 }

We have to run page and control. Its gives us the below image:

Image 1

Your display should match the above image.

Next we create a method in the controller for ajax operations. Our idea is to create sample data, filter it and present it. You should use real data model in the real world.

public ActionResult Students()
 {
   List<Models.Student> students = new List<Models.Student>();
   students.Add(new Models.Student { Id = 1, Name = "Mike", SurName = "Mikey", ClassRoom = "8A" });
   students.Add(new Models.Student { Id = 2, Name = "John", SurName = "Salary", ClassRoom = "8B" });
   students.Add(new Models.Student { Id = 3, Name = "Steve", SurName = "Brown", ClassRoom = "7A" });

   string filterName = Request.QueryString["name"];
   string filterSurName = Request.QueryString["surname"];
   string filterClassroom = Request.QueryString["classroom"]; 

   var result = from s in students
   where (string.IsNullOrEmpty(filterName) || s.Name.Equals(filterName))
   && (string.IsNullOrEmpty(filterSurName) || s.SurName.Equals(filterSurName))
   && (string.IsNullOrEmpty(filterClassroom) || s.ClassRoom.Equals(filterClassroom))
   select s;
   var model = result.ToList();
   return Json(model, JsonRequestBehavior.AllowGet);
 }

Ok call the page in the browser.

Image 2

If you see page as shown, everything is OK. After we are going to do a small test for filter. Add the following to the URL: ?name=Mike.

Image 3

Complete the test. Now write ajax interface in the index.cshtml.

@section scripts {
    <script type="text/javascript">
        var filterValues = {};
        $(document).ready(function () {
            $("#demoTable").dataTable({
                serverSide: true,
                bFilter: false,
                columns: [
                    { data: "Name" },
                    { data: "SurName" },
                    { data: "ClassRoom" }
                ],
                ajax: function (data, callback, settings) {
                    filterValues.draw = data.draw;
                    filterValues.start = data.start;
                    filterValues.length = data.length;
                    $.ajax({
                        url: '/DTSample/Students',
                        method: 'GET',
                        data: filterValues
                    }).done(callback);
                }
            });
        });
    </script>
}

Looking at the code:

  • filterValues send values to server side.
  • serverSide: true is required to execute operations over the server.
  • bFilter: false disables client side filtering.
  • columns parses column data to html table columns sequentinally.
  • ajax: function(data, callback, settings) ajax operation customization. data is containing datatable values, callback for given data to render, setting is a special values
  • filterValues.draw this is a sequence number that is only for post back. You see this in the datatable models. from give the data variable
  • filterValues.start From the data variable for start record number. Do not post back. Use only server side paging
  • filterValues.length From the data variable for length of records for server side paging. Do not post back.

Our students model is only list data, but datatable uses its own format. It need elements.

draw is a JSON element and is numeric, recordsTotal is a JSON element and is numeric for totalRecords, recordsFiltered is a JSON element and is numeric for totalRecords after filter, data is a JSON array and contains our student objects

Now create a model for DataTable and name it DataTable in the class file. Create a class in the Models folder as shown below

namespace DataTableSample.Models
{
    public class DataTable
    {
        public int draw { get; set; }
        public int recordsTotal { get; set; }
        public int recordsFiltered { get; set; }
        public Student[] data { get; set; }
    }
}

After changing the Students method in controller for accords

public ActionResult Students()
   {
   Models.DataTable dataTable = new Models.DataTable();
   dataTable.draw = int.Parse(Request.QueryString["draw"]);

   List<Models.Student> students = new List<Models.Student>();
   students.Add(new Models.Student { Id = 1, Name = "Mike", SurName = "Mikey", ClassRoom = "8A" });
   students.Add(new Models.Student { Id = 2, Name = "John", SurName = "Salary", ClassRoom = "8B" });
   students.Add(new Models.Student { Id = 3, Name = "Steve", SurName = "Brown", ClassRoom = "7A" });

   string filterName = Request.QueryString["name"];
   string filterSurName = Request.QueryString["surname"];
   string filterClassroom = Request.QueryString["classroom"];

   var result = from s in students
   where (string.IsNullOrEmpty(filterName) || s.Name.Equals(filterName))
   && (string.IsNullOrEmpty(filterSurName) || s.SurName.Equals(filterSurName))
   && (string.IsNullOrEmpty(filterClassroom) || s.ClassRoom.Equals(filterClassroom))   select s;

   dataTable.data = result.ToArray();
   dataTable.recordsTotal = students.Count;
   dataTable.recordsFiltered = result.Count();
   return Json(dataTable, JsonRequestBehavior.AllowGet);
   }

Now our model datatable. Remember, draw value send to back.

  • recodsTotal takes the value of all student
  • recordsFiltered takes the value on filtered students.

Our student list is small, therefore we don't use paging. But you have to use start and length values from querystring in real world projects. OK, let's look at the page:

Image 4

Now add custom filter element on page and write code.

<div class="container" style="margin-top: 10px;">
    <div class="row">
        <div class="col-md-6">
            <div class="well form-horizontal">
                <div class="form-group">
                    <label class="control-label col-md-4">Name</label>
                    <div class="col-md-8">
                        <input type="text" id="filterName" value="" class="form-control" />
                    </div>
                </div>
                <div class="form-group">
                    <label class="control-label col-md-4">SurName</label>
                    <div class="col-md-8">
                        <input type="text" id="filterSurName" value="" class="form-control" />
                    </div>
                </div>
                <div class="form-group">
                    <label class="control-label col-md-4">ClassRoom</label>
                    <div class="col-md-8">
                        <input type="text" id="filterClassRoom" value="" class="form-control" />
                    </div>
                </div>
                <div class="form-group">
                    <label class="control-label col-md-4">&nbsp;</label>
                    <div class="col-md-8">
                        <button type="button" class="btn btn-default" id="filterBtn">Filter</button>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <table id="demoTable">
        <thead>
            <tr>
                <td>Name</td>
                <td>Surname</td>
                <td>Classroom</td>
            </tr>
        </thead>
    </table>
</div>

@section scripts {
    <script type="text/javascript">
        var filterValues = {};
        $(document).ready(function () {
            var refDataTable = $("#demoTable").dataTable({
                serverSide: true,
                bFilter: false,
                columns: [
                    { data: "Name" },
                    { data: "SurName" },
                    { data: "ClassRoom" }
                ],
                ajax: function (data, callback, settings) {
                    filterValues.draw = data.draw;
                    filterValues.start = data.start;
                    filterValues.length = data.length;
                    $.ajax({
                        url: '/DTSample/Students',
                        method: 'GET',
                        data: filterValues
                    }).done(callback);
                }
            });
            $("#filterBtn").click(function () {
                filterValues.name = $("#filterName").val();
                filterValues.surname = $("#filterSurName").val();
                filterValues.classroom = $("#filterClassRoom").val();
                refDataTable.fnDraw();
            });
        });
    </script>
}

Create form elements and add binding button to the click event. We are creating a reference for later use (var refDataTable = ... show as bold upon). fnDraw function (or same name draw) update table.

Now we approaching the final test.

Image 5

Congratulations. It works fine.

Notices

You have to update jQuery 1.12.3 and under version.

More

If you want to learn more, visit https://datatables.net/reference/api/

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Turkey Turkey
Senior software developer working over 15+ years. Familiar wide range development experience as database, service, game, multiplatform etc applications. Have turkish blog is http://halityurttas.com.tr for looking somethings.

Comments and Discussions

 
QuestionHow to filter search? Pin
gcogco109-Feb-20 21:45
gcogco109-Feb-20 21:45 
QuestionPaging not working. Pin
Member 1105428123-Sep-16 20:29
Member 1105428123-Sep-16 20:29 
AnswerRe: Paging not working. Pin
Halit Yurttaş30-Sep-16 22:37
Halit Yurttaş30-Sep-16 22:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.