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

  1. Visual Studio (2015 or higher)
  2. MVC
  3. Entity Framework
  4. Jquery
  5. MSSQL 2008 / 2008+

Getting Started

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

  1. Open Visual Studio.

    creating a Custom Datatable in MVC using Entity Framework and Jquery / Ajax

  2. Select "New Project".
  3. Select "ASP.NET Web Application".
  4. Check "Empty" template and MVC in Core References followed by a click on OK.

    creating a Custom Datatable in MVC using Entity Framework and Jquery / Ajax

    creating a Custom Datatable in MVC using Entity Framework and Jquery / Ajax

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

    creating a Custom Datatable in MVC using Entity Framework and Jquery / Ajax

  6. After that, create a new folder as Context.
  7. 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. }
    creating a Custom Datatable in MVC using Entity Framework and Jquery / Ajax
  1. 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. }
    creating a Custom Datatable in MVC using Entity Framework and Jquery / Ajax
  1. Download jQuery and add it inside the Scripts folder.

    creating a Custom Datatable in MVC using Entity Framework and Jquery / Ajax

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

    creating a Custom Datatable in MVC using Entity Framework and Jquery / Ajax

  2. 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. }
    creating a Custom Datatable in MVC using Entity Framework and Jquery / Ajax
  1. 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. }
    creating a Custom Datatable in MVC using Entity Framework and Jquery / Ajax
  1. Now, we have got all the required models.
  2. Create a MVC 5 empty controller.
  3. 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. }
    creating a Custom Datatable in MVC using Entity Framework and Jquery / Ajax

    creating a Custom Datatable in MVC using Entity Framework and Jquery / Ajax
  1. 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. }
    creating a Custom Datatable in MVC using Entity Framework and Jquery / Ajax
  1. The function will return the 1st page’s employe, i.e., 1 to 10 employees.
  2. Then, we will create an Index View to display the data.
  3. 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>
  1. We will create a Partial View “_EmployeeList” inside shared folder as this will make it easy to bind records while paginating and searching.
  2. 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. }
    creating a Custom Datatable in MVC using Entity Framework and Jquery / Ajax

    creating a Custom Datatable in MVC using Entity Framework and Jquery / Ajax

    creating a Custom Datatable in MVC using Entity Framework and Jquery / Ajax

    creating a Custom Datatable in MVC using Entity Framework and Jquery / Ajax
  1. 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>
    creating a Custom Datatable in MVC using Entity Framework and Jquery / Ajax
  1. Now, run your project and see the results.
    creating a Custom Datatable in MVC using Entity Framework and Jquery / Ajax

  2. Now, for searching and pagination, we need to add some jQuery and call AJAX.
  3. 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);
  1. 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>
  1. 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. }
  3. That's it. Run the application and look at the results on button click and filter textbox search.