ASP.NET: Jquery Datatables Plugin with Dynamic Numbers of Columns

In web development environment loading data on a table sort of visualization is a challenge in itself, but, thankfully, many cool Jquery based plugins are available free of cost that are easy to integrate and resolve data loading as a form of an attractive feature-oriented rich design table. Jquery Datatables is one such plugin with many features to offer free of cost and is easily integrated with any web development language. The disadvantage with these plugins are that sometimes it become difficult to integrate custom requirements especially which are not directly supported. For example, let say we want to load data with random amount of column numbers, as most of the plugin support fix number of columns to display, it becomes difficult to find the suitable work around these plugins to support such custom requirements. Luckily, with Jquery Datatables plugin which provides tons load of features to explore, loading dynamic number of columns is very simple to integrate.

Today, I shall be demonstrating the dynamic number of columns loading using 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 2014and Iris data-set from UCI Machine Learning Repository.

Download Now!

Let's begin now.

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

2) Now, create a controller file and name it "Controllers\HomeController.cs" and write a simple POST Index(...) method which will load random CSV files, convert the loaded data into JSON and finally, pass the target JSON to your view's hidden field  i.e.

        #region POST: /Home/Index

        /// <summary>
        /// POST: /Home/Index
        /// </summary>
        /// <param name="model">Model parameter</param>
        /// <returns>Return - Response information</returns>
        [HttpPost]
        [AllowAnonymous]
        [ValidateAntiForgeryToken]
        public ActionResult Index(HomeViewModel model)
        {
            ...

            try
            {
                // Verification
                if (ModelState.IsValid)
                {
                    ...

                    // Uploading file.

                    ...

                    // Impot CSV file.
                    datatableObj = CSVLibraryAK.Import(importFilePath, model.HasHeader);
                     
                    // Prepare JSON object.

                    ...
  
                    // Data
                    model.DataObject = JsonConvert.SerializeObject(dataObj);
                }
            }
            catch (Exception ex)
            {
                // Info
                Console.Write(ex);
            }

            // Info
            return this.View(model);
        }

In the above POST Index(...) method, I have first stored the uploaded file then import the data then I have created a require JSON format object that is supported by jquery datatables and finally, I have passed the JSON data back to the view into a hidden input field property of my model.

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) Now, create your view and name it "Views\Home\Index.cshtml". The view is required to have a hidden input field and table for jquery datatable i.e.

...

<div style="display:none;" class="row">
    <div class="form-group">
        @Html.HiddenFor(m => m.DataObject, new { @id = "dataObjId", placeholder = Html.DisplayNameFor(m => m.DataObject), @class = "form-control" })
    </div>
</div>


<hr />

...

    <div class="row">
        <div>
            <table class="table table-striped table-bordered table-hover"
                   id="TableId"
                   cellspacing="0"
                   align="center"
                   width="100%">
            </table>
        </div>
    </div>
    <hr />

...

In the above code, I have simply create a hidden input field which will hold the JSON data and a table that will integrate the jquery datatables plugin with the User Interface (UI).

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

$(document).ready(function ()
{
    ...

    // Verification.
    if (obj)
    {
        ...

        // Datatable settings.
        $('#TableId').DataTable(
            {
                "data": dataObject.data,
                "columns": dataObject.columns
            });
    }
});

In the above code, I have first verify that whether my hidden input field contains JSON data or not then if it contains the JSON data then I have initiated jquery datatables with basic settings and with the settings of the JSON response data.

5) 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 jquery datatables plugin to support dynamic number of columns loading in ASP.NET MVC web platform. You will also learn to import CSV data into jquery datatables plugin using CSVLIbraryAK library.