ASP.NET MVC - jQuery Ajax Datatables with Dynamic Columns

Introduction 

 

Jquery Datatables free plugin offers many useful features to integrate and customize a table with many web development platforms. Today, I shall be demonstrating the dynamic number of columns loading using server-side Ajax Jquery Datatables plugin with ASP.NET MVC5 platform.

 

 

Prerequisites

 

The following are some prerequisites before you proceed any further in this tutorial:

1. Installation of Jquery Datatebles plugin.
2. Understanding Jquery Datatebles plugin Integration with ASP.NET MVC5
3. Installation of CSVLibraryAK.
4. Knowledge of ASP.NET MVC5.
5. Knowledge of HTML.
6. Knowledge of JavaScript.
7. Knowledge of Bootstrap.
8. Knowledge of Jquery.
9. Knowledge of C# Programming.

The example code is being developed in Microsoft Visual Studio 2017 Professional. I have taken the data sample from AdventureWorks for SQL Server 2014 and Iris data-set from UCI Machine Learning Repository.

 

Let's begin now.

 

Step 1

 

Create a new MVC web project and name it "MVCAjaxDatatablesDynamicCol".  

 

Step 2

 

Now, create a controller file and name it "Controllers\HomeController.cs" and write a simple POST Index(...) method which will uploaded the provided CSV file to the server so that Jquery Datatables server-side can process the uploaded file and load th data.

 

Know that jquery data tables support the following JSON format, i.e.:

[{
 "columns": [{
  "title": "col1_name",
  "data": "col1_name"
 }, {
  "title": "col2_name",
  "data": "col2_name"
 }],
 "data": [{
  "col1_name": "col1 data",
  "col2_name": "col2 data"
 }, {
  "col1_name": "col1 data",
  "col2_name": "col2 data"
 }]
}]

Step 3

 

In "Controllers\HomeController.cs" file for server-side, Jquery Datatables is needed to work for a dynamic number of columns. We need to write necessary Ajax method i.e.

...
        public ActionResult GetData()
        {
            // Initialization.
            JsonResult result = new JsonResult();

            try
            {
                // Initialization Jquery Datatables.
                string search = Request.Form.GetValues("search[value]")[0];
                string draw = Request.Form.GetValues("draw")[0];
                string order = Request.Form.GetValues("order[0][column]")[0];
                string orderDir = Request.Form.GetValues("order[0][dir]")[0];
                int startRec = Convert.ToInt32(Request.Form.GetValues("start")[0]);
                int pageSize = Convert.ToInt32(Request.Form.GetValues("length")[0]);
...
                // Initialization.
                JqDataObj dataObj = new JqDataObj();
...
                // Load uploaded file Data.
...
                // Set Jquery Datatable Total record count.
                int totalRecords = data.Rows.Count;

                // Apply Jquery Datatables Search.
...
                // Apply Jquery Datatable Sorting.
...
                // Set Jquery Datatable Filter record count.
                int recFilter = data.Rows.Count;

                // Apply Jquery Datatable Pagination.
...
                // Set Jquery Datatable Dynamic Header Column Names.
                dataObj.columns = this.LoadColumnHeader(data);
...
                // Set Jquery Datatable Data Rows.
                dataObj.data = data;
...
                // Set Jquery Datatable resultant information.
                result = this.Json(new { draw = Convert.ToInt32(draw), recordsTotal = totalRecords, recordsFiltered = recFilter, data = JsonConvert.SerializeObject(dataObj) }, JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                // Info
                Console.Write(ex);
            }

            // Return info.
            return result;
        }
...

The above "GetData(...)" Ajax method will be called by the Jquery Datatables to load the required data into the table form.

 

Step 4

 

Now, create your view and name it "Views\Home\Index.cshtml". The view should contain a CSV file uploading component along with a table place holder for Jquery Datatables plugin to integrate with, i.e.:

...
    <!-- HTML Code to upload CSV file -->
...
    <!-- HTML Code for JQuery Datatables Plugin to integrate with -->
    <div class="row">
        <div>
           <table class="table table-striped table-bordered table-hover"
               id="TableId"
               cellspacing="0"
               align="center"
               width="100%">

           </table>
        </div>
    </div>
...

In the above code, I have simply created a table that will integrate the jquery datatables plugin with the User Interface (UI), I am skipping the HTML code to upload the CSV file.

 

Step 5

 

Finally, create the JavaScript file "Scripts\script-custom-datatable.js" which will load our dynamic number of columns into the server-side jquery datatables plugin, i.e.:

...
    $(document).ready(function ()
    {
...                    $('#TableId').DataTable(
                        {
                            "columnDefs": [
                                { "searchable": true, "orderable": true, targets: "_all" },
                                { "className": "text-center custom-middle-align", targets: "_all" }
                            ],
...
                            "processing": true,
                            "serverSide": true,
                            "ajax":
                            {
                                "url": "/Home/GetData",
                                "type": "POST",
                                "dataType": "JSON",
                                "dataSrc": function (json)
                                {
                                    // Settings.
                                    jsonObj = $.parseJSON(json.data)

                                    // Data
                                    return jsonObj.data;
                                }
                            },
                            "columns": dataObject.columns
                        });
...
    });
...

In the above code, I have configured the Jquery Datatables plugin with server-side Ajax call in order to load the dynamic columns data as uploaded by the user.

 

Step 6

 

Now, execute the project and you will be able to see the following in action, i.e.:

 

 

 

Conclusion

 

In this article, you learned to configure server-side jquery datatables plugin to support dynamic number of columns loading in ASP.NET MVC web platform. You also learned how to configure the Ajax method that supports the Jquery Datatables plugin in ASP.NET MVC.