ASP.NET MVC: Jquery Ajax Datatables with Dynamic Columns

Jquery Datatables plugin free plan offers many useful features to integrated/customize table like view 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:

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

1. Installation of Jquery Datatebles plugin.
2. Understanding of 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.

Download Now!

Let's begin now.

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

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 datatables support 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"
 }]
}]

3) In "Controllers\HomeController.cs" file for server-side Jquery Datatables to work for dynamic numbers 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 require data into the table form.

4) Now, create your view and name it "Views\Home\Index.cshtml". The view should contain csv file uploading component along with 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 create a table that will integrate the jquery datatables plugin with the User Interface (UI), I am skiping the HTML code for uploading CSV file.

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.

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

Conclusion

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