Creating A Custom DataTable In MVC Using Entity Framework And jQuery/AJAX

Introduction

In this article, I will create a custom DataTable in MVC using Entity Framework and jQuery/AJAX. Although jQuery’s DataTable can be used easily, here I will do server-side pagination and searching.

Requirements

Visual Studio (2015 or higher)
MVC
Entity Framework
Jquery
MSSQL 2008 / 2008+

Getting Started

First, we will create an ASP.NET Web Application.

Open Visual Studio.
Select "New Project".
Select "ASP.NET Web Application".
Check "Empty" template and MVC in Core References followed by a click on OK.
Now that our project is created, we will install the Entity Framework in our project via NuGet Package installer. Right-click on the project and select "Manage NuGet Packages". Select the Browse tab and type "Entity" in the search box. Select "Install".
After that, create a new folder as Context.
Inside Models folder, create your database class as “employee”. Also, this will be our Table name in the database. The properties inside the employee class will be the columns of employee table.
1. public class employee
2. {
3. [Key] // This will make the emp_no as the primary key with auto increement by 1
4. public int emp_no { get; set; }
5. public DateTime birth_date { get; set; }
6. public string first_name { get; set; }
7. public string last_name { get; set; }
8. public string gender { get; set; }
9. public DateTime hire_date { get; set; }
10. }

Now, in the context folder that we created previously, add a new class as “dbEmployee”. This will help to communicate with the database and perform CRUD for every mentioned class in it. Here, we will use only the employee class. If you want to use more tables, then you can add the other classes too.
1. public class dbEmployee : DbContext
2. {
3. public DbSet<employee> emp { get; set; }
4. }

Download jQuery and add it inside the Scripts folder.
Add the connection string in the web.config file as below.
1. <connectionStrings>
2. <add name="dbEmployee" connectionString="{source}" providerName="System.Data.SqlClient" />
3. </connectionStrings>

The connection name will be the same as the name of the class that we added inside the Context folder. Here, we have added the dbEmployee class, so our name will be the same as that.
Inside Models folder, create a class as “FilterEmployee”. This class will be used for getting the search keys and values.
1. public class FIlterEmployee
2. {
3. public string emp_no { get; set; } = "";
4. public string first_name { get; set; }
5. public string last_name { get; set; }
6. public string gender { get; set; }
7. }

Create one more class inside Models folder “GridPagination”. This class will have the required data to make the datatable.
1. public class GridPagination
2. {
3. public int CurrentPage { get; set; }
4. public double TotalPage { get; set; } //Buttons
5. public int TotalData { get; set; } // Total count of the filtered data
6. public List<employee> Data { get; set; }
7. public int TakeCount { get; set; } = 10; // By default i am using 10 data per page
8. public FilterEmployee filters { get; set; } = new FilterEmployee(); // Search keys and value
9. }

Now, we have got all the required models.
Create a MVC 5 empty controller.
First, we will create a common function for paging and searching which will return the GridPagination as below.
1. //This is the common function used for paging and searching
2. public GridPagination FilterData(int? PageNumber, FilterEmployee filters)
3. {
4. GridPagination gridData = new GridPagination();
5. double count = 0;
6. try
7. {
8. using (dbEmployee db = new dbEmployee())
9. {
10. // Getting all the Data from Database
11. var empData = db.emp.ToList();
12. // Checking if the Page number is passed and is greater than 0 else considered as 1
13. gridData.CurrentPage = PageNumber.HasValue ? PageNumber.Value <= 0 ? 1 : PageNumber.Value : 1;
14. // Assigning the list of data to the Model's property
15. gridData.Data = empData;
16. // Assigning filters
17. gridData.filters = filters;
19. //Getting the List with the matching emp_no
20. if (!string.IsNullOrEmpty(filters.emp_no))
21. {
22. gridData.Data = gridData.Data.Where(x => x.emp_no.ToString().Contains(filters.emp_no.ToString())).ToList();
23. }
25. //Getting the List with the matching first_name
26. if (!string.IsNullOrEmpty(filters.first_name))
27. {
28. gridData.Data = gridData.Data.Where(x => x.first_name.ToLower().Contains(filters.first_name.ToLower())).ToList();
29. }
31. //Getting the List with the matching last_name
32. if (!string.IsNullOrEmpty(filters.last_name))
33. {
34. gridData.Data = gridData.Data.Where(x => x.last_name.ToLower().Contains(filters.last_name.ToLower())).ToList();
35. }
37. //Getting the List with the matching gender
38. if (!string.IsNullOrEmpty(filters.gender))
39. {
40. gridData.Data = gridData.Data.Where(x => x.gender.ToLower().Contains(filters.gender.ToLower())).ToList();
41. }
43. // If there are multiple filter key passed then the above condition will work as an operator condition
45. // Total data count after filter
46. gridData.TotalData = gridData.Data.Count();
48. // Getting the total pages
49. count = (double)gridData.TotalData / gridData.TakeCount;
50. gridData.TotalPage = (int)Math.Ceiling(count);
52. // assigning the filtered data to model
53. // This is the formula for skiping the previous page's data and taking the current page's
54. gridData.Data = gridData.Data.Skip((gridData.CurrentPage - 1) * gridData.TakeCount).Take(gridData.TakeCount).ToList();
55. }
56. }
57. catch (Exception ex)
58. {
59. gridData = new GridPagination();
60. }
61. //returning the Grid.
62. return gridData;
63. }

Now inside the Index controller, we will just pass the data to the View which we will get after calling the above function with Page number as 1 and no column filters.
1. // GET: Employee
2. public ActionResult Index()
3. {
4. // Calling FilterData function with Page number as 1 on initial load and no filter
5. return View(FilterData(1, new FilterEmployee()));
6. }

The function will return the 1st page’s employe, i.e., 1 to 10 employees.
Then, we will create an Index View to display the data.
We will use GridPagination as our model in View as we are passing that model from the controller.
1. @model CustomDataTable.Models.GridPagination
2. @{
3. ViewBag.Title = "Index";
4. Layout = "~/Views/Shared/_Layout.cshtml";
5. }
6. <h2>Index</h2>

We will create a Partial View “_EmployeeList” inside shared folder as this will make it easy to bind records while paginating and searching.
Paste the below code inside the Partial View. Add the explanation in that.
1. @model CustomDataTable.Models.GridPagination
2. @{
3. int count = ((Model.CurrentPage - 1) * Model.TakeCount) + 1; // This is the serial number Calculation
4. int TotalCounter = 0;
5. bool showFooter = true;
7. // We will show only 5 buttons on footer to paginate
8. // Because if the data is more then it will look worst with multiple buttons in footer
9. int StartButton = 1;
10. // Checking if total page is greate then 5 else we will take upto
11. // whatever the total page is
12. double EndButton = 5 > Model.TotalPage ? Model.TotalPage : 5;
13. }
15. <table class="table table-bordered">
16. <thead>
17. @*Setting the filter values in text boxes*@
18. <tr>
19. <th>Sr No.</th>
20. <th>Emp No <input type="text" id="emp_no" class="form-control col-md-2 filter-text" value="@Model.filters.emp_no" /></th>
21. <th>Firstname <input type="text" id="Firstname" class="form-control col-md-2 filter-text" value="@Model.filters.first_name" /></th>
22. <th>Lastname <input type="text" id="Lastname" class="form-control col-md-2 filter-text" value="@Model.filters.last_name" /></th>
23. <th>Gender <input type="text" id="Gender" class="form-control col-md-2 filter-text" value="@Model.filters.gender" /></th>
24. </tr>
25. </thead>
26. <tbody>
27. @if (Model != null && Model.Data != null && Model.Data.Count > 0)
28. {
29. foreach (var emp in Model.Data)
30. {
31. <tr>
32. <td>@count</td>
33. <td>@emp.emp_no</td>
34. <td>@emp.first_name</td>
35. <td>@emp.last_name</td>
36. <td>@emp.gender</td>
37. </tr>
38. count++;
39. }
40. }
41. else
42. {
43. <tr>
44. <td colspan="5" style="text-align: center;">No employee found</td>
45. </tr>
46. showFooter = false;
47. }
48. </tbody>
49. </table>
51. @*This is the calculation done for showing the buttons like next, prev, jump forward, jump backward etc*@
52. @*Jump previous will shift from current page to 2 page backward*@
53. @*e.g. if you are on the 5th page, then clicking on jump backward will shift to the 2nd page*@
54. @*Similarly jump forward will shift 2 page forward*@
55. @if (showFooter)
56. {
57. <div class="panel-footer">
58. <div class="row">
59. <div class="col col-xs-4">
60. Page @Model.CurrentPage of @Model.TotalPage |
61. Total @Model.TotalData Records
62. </div>
63. <div class="col col-xs-8">
64. <ul class="pagination hidden-xs pull-right">
65. @*if current page is 1 then there is no need of showing previous and jump backward buttons*@
66. @if (Model.CurrentPage != 1)
67. {
68. //Prevoius button
69. <li><a href="javascript:void(0);" data-page="@(Model.CurrentPage - 1)" class="@(Model.CurrentPage == 1 ? "" : "filter-page")">Prev</a></li>
72. //Start button and jump backward button's calculation
73. StartButton = Model.CurrentPage - 2 <= 0 ? 1 : Model.CurrentPage - 2;
74. EndButton = Model.CurrentPage + 2 > Model.TotalPage ? Model.TotalPage : Model.CurrentPage + 2;
75. if (Model.CurrentPage == Model.TotalPage)
76. {
77. StartButton = StartButton - 2 <= 0 ? 1 : StartButton - 2;
78. }
80. //Jump backward button
81. if (Model.CurrentPage >= 4)
82. {
83. <li><a href="javascript:void(0);" class="filter-page" data-page="@(StartButton - 1 <= 0 ? 1 : StartButton - 1)"><span><<</span></a></li>
84. }
85. }
87. @*Five buttons*@
88. @for (int i = StartButton; i <= EndButton; i++)
89. {
90. <li><a href="javascript:void(0);" class="@(Model.CurrentPage == i ? "active page" : "filter-page")" data-page="@i">@i</a></li>
91. }
93. @* Jump forward button calculation *@
94. @if (EndButton != Model.TotalPage)
95. {
96. <li><a href="javascript:void(0);" class="filter-page" data-page="@(EndButton+1>Model.TotalPage?Model.TotalPage:EndButton+1)"><span>>></span></a></li>
97. }
99. @* next button *@
100. @if (Model.CurrentPage != Model.TotalPage && Model.TotalPage > 1)
101. {
102. <li><a href="javascript:void(0);" data-page="@(Model.CurrentPage + 1)" class="@(Model.CurrentPage == TotalCounter ? "" : "filter-page")">Next</a></li>
103. }
104. </ul>
105. </div>
106. </div>
107. </div>
108. }

Now, we will render this partial view in our index page and pass the GridPagination model to the Partial View.
1. @model CustomDataTable.Models.GridPagination
2. @{
3. ViewBag.Title = "Index";
4. Layout = "~/Views/Shared/_Layout.cshtml";
5. }
6. <style>
7. .panel-table .panel-footer .pagination {
8. margin: 0;
9. }
11. /*
12. used to vertically center elements, may need modification if you're not using default sizes.
13. */
14. .panel-table .panel-footer .col {
15. line-height: 34px;
16. height: 34px;
17. }
19. .active.page {
20. background-color: blue;
21. color: white;
22. }
23. </style>
24. <div class="container" id="tbEmployee">
25. @{ Html.RenderPartial("_EmployeeList", Model);}
26. </div>

Now, run your project and see the results.
Now, for searching and pagination, we need to add some jQuery and call AJAX.
Create a JavaScript “Paginate” under Scripts folder and paste the below code.
1. var paginate = function () {
2. var that = {};
3. var emp = {};
4. var TextBox = '';
5. var PageNo = 1;
8. // All the initial events will be placed here.
9. var InitEvents = function () {
10. // every button has the common class as filter-page and common attribute data-page
11. // data-page is nothing but the page number on click if which the data will be filtered
12. $(document).on('click', '.filter-page', function () {
13. //setting the page no.
14. PageNo = $(this).data('page');
15. Pagination();
16. });
18. // every filter text box has the common class as filter-text
19. // on keyup of the text box we will call the ajax function
20. $(document).on('keyup', '.filter-text', function () {
22. //setting the page no to 1 thus on any filter change matching data will be shown from page 1
23. PageNo = 1;
24. //setting the text box id on which we will set the focusend
25. TextBox = $(this).attr('id');
26. Pagination();
27. })
28. }
30. // Common ajax call function to bind the filter data and page number and pass to the Action result of our controller via ajax call
31. var Pagination = function () {
33. // this variables emp_no,first_name,last_name,gender is the same property
34. // which we declare in class FilterEmployee
35. emp.emp_no = $('#emp_no').val();
36. emp.first_name = $('#Firstname').val();
37. emp.last_name = $('#Lastname').val();
38. emp.gender = $('#Gender').val();
39. $.ajax({
40. type: "POST",
41. url: "/Employee/PaginateData",
42. data: { pageNo: PageNo, filter: emp },
43. content: "application/json; charset=utf-8",
44. dataType: "html", //here we set datatype as html becausing we are returning partial view
45. success: function (d) {
46. // d will contain the html of partial view
47. $('#tbEmployee').html(d);
49. // setting the focus to the textbox
50. if (TextBox != '' && TextBox != null) {
51. $('#' + TextBox).focusToEnd();
52. }
53. },
54. error: function (xhr, textStatus, errorThrown) {
56. }
57. });
58. }
60. that.init = function () {
61. // to load the initial events
62. InitEvents();
63. }
65. return that;
66. }();
69. // This function will focus to the end position of the mentioned textbox id
70. (function ($) {
71. $.fn.focusToEnd = function () {
72. return this.each(function () {
73. var v = $(this).val();
74. $(this).focus().val("").val(v);
75. });
76. };
77. })(jQuery);

In your index page, add this JS reference and add the below line.
1. <script src="~/scripts/Paginate.js"></script>
2. <script type="text/javascript">
3. $(document).ready(function () {
4. // initial events binding
5. paginate.init();
6. })
7. </script>

Now, we will add ActionResult method as "PaginateData" in our "EmployeeController" to accept the AJAX request and return the Filtered Data.
1. [HttpPost]
2. public ActionResult PaginateData(int pageNo, FilterEmployee filter)
3. {
4. // This will call the FilterData function with PageNo and filter textboxes value which we passed in our Ajax request
5. return PartialView("_EmployeeList", FilterData(pageNo, filter));
6. }
That's it. Run the application and look at the results on button click and filter textbox search.