ASP.NET - jQuery Datatables Plugin With Dynamic Numbers Of Columns

In the 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 of such plugins is that sometimes it becomes difficult to integrate custom requirements with it, especially the ones that are not directly supported. For example, let us say we want to load data with a random amount of column numbers. As most of the plugins support fix number of columns to display, it becomes difficult to find a suitable workaround for these plugins to support such custom requirements. Luckily, with jQuery Datatables plugin which provides a load of features, the loading of a dynamic number of columns is very simple.

 

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.

 

Let's begin now.

 

Step 1

 

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

 

Step 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"
 }]
}]

Step 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 created 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).

 

Step 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 verified 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.

 

Step 5

 

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

 

 

 

 

 

 

Conclusion

 

In this article, we learned how to configure the jQuery Datatables plugin to support the dynamic number of columns loading in ASP.NET MVC web platform. We also saw how to import CSV data into jQuery Datatables plugin using CSVLIbraryAK library.