jQuery DataTable Integration - Dynamic Columns and Records

• Download source - 6.5 KB

Introduction

Personally, experience after long time engage with financial /non-financial/corporate industries. There are several pages where we need to use data table/grid. Each page has similarities except columns/table header names and respective rows/records values.

In most cases, we must develop many pages separately in a project with different data source Model/Entity. I mean to say, we can easily visualize that there are multiple pages available along with DataTable grid and each grid has bind with separate data source or entity in any large scale project.

Finally, we are doing various operations, e.g., Global Search (Search Text in every Column, Search Text in Individual Column, Sorting records on specific column, Pagination and Exporting data as report).

Using the following architecture, we can quickly bind or initialize jQuery DataTable - we just require to supply raw select SQL query and it will show grid with data.

Following architecture will be able to handle dynamic columns and respective rows. So, I have tried to make dynamic jQuery Datatable which populates grid basis on return json result.

Logic/Idea: How to Dynamically Bind Jquery DataTables Using Anonymous JSON Result ?

Dynamic binding of grid means, I am going to populate data entity from any kind of select raw SQL query and then return anonymous json result. Before initializing of Datatable, we need to collect N numbers of columns or decide how many number of columns are available from json result.

I have found two challenges:

1. Entity framework doesn't support dynamically/anonymous object after supplying raw SQL query. Entity framework needs specific return types/model while we are going to supply raw select SQL query for database operations. So I have prepared a custom dynamic extension method whose job is to easily execute any raw select SQL query without knowing its return type. The name of this custom function is DynamicSqlQuery() in this article.
2. We have json result using JsonConvert.SerializeObject() method. It will serialize model data to Json result. This result is going to consume from client side while I am going to initialize jQuery DataTables. We have json result but don't have column details because result has been prepared from dynamic object. We need all columns from these json results. I have taken just the first records and put in array and then after initializing the table. (You can see it GenerateQuery() function in client side implementation).

Benefits

• Using this approach, you don't require every time to create separate model/class to bind entity result.
• It saves a lot of time and less line numbers of code and is easy to maintain.
• Once grid has been initialized, we can perform multiple operations easily, e.g., Global Text Searching in any columns, Search Text in Individual Column, Sorting of column and Export data as report.

Limitations

• It is client side based jQuery DataTable initialization so it is not advisable to use more bulky/extensive records, otherwise it will take lots to time to initialize the grid. All records are retrieved from SQL in IIS server at a time not chunked data like pagination records.

Guideline: Step by Step for Integration

I have separated steps in majorly two parts as follows:

1. SERVER SIDE: Entity Framework (Code First Approach) should able to execute Raw SQL Query. I have prepared an extension method and called it as DynamicQueryBuilder(). It will help to return anonymous data from SQL Server by supplying raw SQL query. I have passed entity/data model to JsonConvert.SerializeObject() which helps to serialize json formatted result set.

2. CLIENT SIDE: jQuery DataTables should able to display dynamic column and respective records. Clients(Browser) request for json result from server. Determine N number of columns once the result has been received from Ajax request. I have prepared dynamically HTML table's columns header using jQuery.

Procedure/Steps

I have used a pictorial representation as well as self explanatory code which will help us and is easy to understand architecture of this approach.

Step 1: Install Entity Framework using NuGet command Install-Package EntityFramework

Step 2: Set Connection String for Your Database in Web.Config

Step 3: Enable Migration for Code First Approach

I have taken multiple tables for generating separate SQL native/raw SQL query.

ScientistData and InventionData are Entity/tables for sample illustration.

public class ScientistData
   {
       public int Id { get; set; }
       public string Name { get; set; }
       public string Duration { get; set; }
       public string Description { get; set; }
   }
   public class InventionData
   {
       public int Id { get; set; }
       public string Inventor { get; set; }
       public string Invention { get; set; }
   }

Prepare for Context for Database Table Migration

public class QueryContext : DbContext
    {
        public QueryContext() : base("name=QueryManager") { }
        public DbSet<ScientistData> ScientistData { get; set; }
        public DbSet<InventionData> InventionData { get; set; }
    }

Trigger NuGet Command for Enable Migration as Well as Table Creation

I have taken multiple tables for generating a separate SQL native/raw SQL query.

1. Enable-Migrations
2. Add-Migration InitialCreate
3. Update-Database

Finally, it creates database tables.

Step 4: Create a Custom Extension Method which Supports to Execute Raw Native SQL Query

public static class DynamicQueryBuilder
   {
       public static IEnumerable DynamicSqlQuery
            (this Database database, string sql, params object[] parameters)
       {
           TypeBuilder builder = createTypeBuilder(
                   "MyDynamicAssembly", "MyDynamicModule", "MyDynamicType");
           using (System.Data.IDbCommand command = database.Connection.CreateCommand())
           {
               try
               {
                   database.Connection.Open();
                   command.CommandText = sql;
                   command.CommandTimeout = command.Connection.ConnectionTimeout;
                   foreach (var param in parameters)
                   {
                       command.Parameters.Add(param);
                   }

                   using (System.Data.IDataReader reader = command.ExecuteReader())
                   {
                       var schema = reader.GetSchemaTable();

                       foreach (System.Data.DataRow row in schema.Rows)
                       {
                           string name = (string)row["ColumnName"];
                           //var a=row.ItemArray.Select(d=>d.)
                           Type type = (Type)row["DataType"];
                           if (type != typeof(string) && (bool)row.ItemArray
                                          [schema.Columns.IndexOf("AllowDbNull")])
                           {
                               type = typeof(Nullable<>).MakeGenericType(type);
                           }
                           createAutoImplementedProperty(builder, name, type);
                       }
                   }
               }
               finally
               {
                   database.Connection.Close();
                   command.Parameters.Clear();
               }
           }

           Type resultType = builder.CreateType();

           return database.SqlQuery(resultType, sql, parameters);
       }

       private static TypeBuilder createTypeBuilder(
           string assemblyName, string moduleName, string typeName)
       {
           TypeBuilder typeBuilder = AppDomain
               .CurrentDomain
               .DefineDynamicAssembly(new AssemblyName(assemblyName),
                                      AssemblyBuilderAccess.Run)
               .DefineDynamicModule(moduleName)
               .DefineType(typeName, TypeAttributes.Public);
           typeBuilder.DefineDefaultConstructor(MethodAttributes.Public);
           return typeBuilder;
       }

       private static void createAutoImplementedProperty(
           TypeBuilder builder, string propertyName, Type propertyType)
       {
           const string PrivateFieldPrefix = "m_";
           const string GetterPrefix = "get_";
           const string SetterPrefix = "set_";

           // Generate the field.
           FieldBuilder fieldBuilder = builder.DefineField(
               string.Concat(PrivateFieldPrefix, propertyName),
                             propertyType, FieldAttributes.Private);

           // Generate the property
           PropertyBuilder propertyBuilder = builder.DefineProperty(
               propertyName, System.Reflection.PropertyAttributes.HasDefault, propertyType, null);

           // Property getter and setter attributes.
           MethodAttributes propertyMethodAttributes =
               MethodAttributes.Public | MethodAttributes.SpecialName |
               MethodAttributes.HideBySig;

           // Define the getter method.
           MethodBuilder getterMethod = builder.DefineMethod(
               string.Concat(GetterPrefix, propertyName),
               propertyMethodAttributes, propertyType, Type.EmptyTypes);

           // Emit the IL code.
           // ldarg.0
           // ldfld,_field
           // ret
           ILGenerator getterILCode = getterMethod.GetILGenerator();
           getterILCode.Emit(OpCodes.Ldarg_0);
           getterILCode.Emit(OpCodes.Ldfld, fieldBuilder);
           getterILCode.Emit(OpCodes.Ret);

           // Define the setter method.
           MethodBuilder setterMethod = builder.DefineMethod(
               string.Concat(SetterPrefix, propertyName),
               propertyMethodAttributes, null, new Type[] { propertyType });

           // Emit the IL code.
           // ldarg.0
           // ldarg.1
           // stfld,_field
           // ret
           ILGenerator setterILCode = setterMethod.GetILGenerator();
           setterILCode.Emit(OpCodes.Ldarg_0);
           setterILCode.Emit(OpCodes.Ldarg_1);
           setterILCode.Emit(OpCodes.Stfld, fieldBuilder);
           setterILCode.Emit(OpCodes.Ret);

           propertyBuilder.SetGetMethod(getterMethod);
           propertyBuilder.SetSetMethod(setterMethod);
       }
   }

Step 5: Prepare Helper Method which will Return Results from Database Operations

Following SQL query returns all columns values as follows:

//Essential to execute Raw Sql Query
 public object ExecuteRawSql(string Query)
 {
     return this.Database.DynamicSqlQuery(Query);
 }
 //Helpful to return all Table object from Database.
 //Essential to make intellisense for Query Builder
 public List<string> TableObject()
 {
     return this.Database.SqlQuery<string>("SELECT TABLE_SCHEMA+'.'+
     TABLE_NAME as TableObject FROM INFORMATION_SCHEMA.TABLES order by TABLE_NAME").ToList();
 }

ExecuteRawSql Custom extension method whose return type is object which does not support current entity framework.

Step 6: Setup jQuery Datatable as Following Basic Structure

Note: It is highly recommended to handle table header based on json result after Ajax response.

var GenerateQuery = function () {
        $("#resultContainer").css("display", "none");        
        var Payload = { 'Query': $("#Query").val() };
        $.ajax({
            url: '/QueryManager/GenerateResult',
            type: 'POST',
            data: Payload,
            success: function (response) {
                if (response.status != true) {
                    console.log("Exception", response);
                    alert('There is some error has occured!. 
                           Please see in console for details of error');
                }
                else {
                    $("#resultContainer").css("display", "block");
                    //Handling JQuery Datatable Dynamically...
                    //Check Data Table has if already initialize then need to destroy first!
                    if ($.fn.DataTable.isDataTable('#example')) {
                        $('#example').DataTable().destroy();
                        $('#example').empty();
                    }
                    //Listing Columns (Table Header) from json ajax response
                    var Columns = [];
                    var TableHeader = "<thead><tr>";
                    $.each(response.result[0], function (key, value) {
                        Columns.push({ "data": key })
                        TableHeader += "<th>" + key + "</th>"
                    });
                    TableHeader += "</thead></tr>";
                    $("#example").append(TableHeader);
                    $('#example').dataTable({
                        "oLanguage": {
                            "sLengthMenu": "_MENU_ &nbsp;"
                        },
                        "data": response.result,
                        "columns": Columns,
                        "JQueryUI": true,
                        dom: 'Bfrtip',
                        dom: 'lBfrtip',
                    });                    
                }
            }
        });
    }

Need to call Ajax request with supplied dynamic raw SQL query and server will respond with data/result accordingly.

1. Prepare table header basis on json result of Ajax response.
2. Destroy or uninitialize the jquery datatable if already initialized table.
3. Initialize jquery datatable with Ajax response

Result/Output: You can write Raw SQL Query and Execute it!

Points of Interest

• The main challenge that I have found while binding dynamic json result to jquery datatable is the need to create table header using script.
• There is no dynamic return type supporting in Entity Framework (Version 6.0.0.0) right now, so you need to build dynamic executable custom extension method.
• This kind of utility saves a lot of time while we prepare or initialize Jquery Datatable, you only require to pass SQL select query. It also works if we execute Stored Procedure (Stored procedure result/outcome should n-records/select like query result). It will work like web based query SQL manager or editor.