Complex Jquery Datatable with Database State Saving & Basic Use of jQuery Templates with ASP.NET C# 4.5, Entity Framework

• Download sample - 4 MB

Introduction

There are times when we are trying to implement a client side grid in our own way in the web page and stuck with various requirements. There are various ways of implementation of client-side grid like like JQery DataTable , JQGrid etc. In this technology offering, we have provided a complete guidance to customize a complex JQuery DataTable with the Database State Saving feature. There is no intention to compare the features & performance of JQuery DataTable with its other forms.<o:p>

Background

The JQuery DataTable is an API which is free and details can be found here.

Assumption

I have attached the complete sample project with documentation here. To run this code, the following prerequisites must me met.

• The code is written in Visual Studio 2012 and SQL Express (Obviously this can be transformed to SQL Server 2008 R2)
• The JQuery DataTable is an API which is free and details can be found here . My intention is to use its features and my ideas to perform some activities very easily; like….
  • Add a CheckBox for each Row.
  • Add Read Unread Flag.
  • Add Hyperlinks.
  • Add Conditional Images
  • Show/Hide Column.
  • Store Values in Hidden Columns
  • Split Group Headers … and many more
• Store the JQuery DataTable State in the SQL Server which must behave like a Session and maintain its State.
• Some filters are added to make the example more realistic .
• Developer must have a knowledge about ASP.NET in C#, Generic SQL and JQuery with StyleSheet.
• I have used User ID as “” (Blank) in this sample application. But developers may use my AD Groups utility to leverage the Active Directory user.

 

Using the code

Usually you may think that JQuery DataTable has given a feature stated below to store the state of the same internally. Then why do we need all these. Below is the explanation.

As per the Documentation if you use the following code this should store the state of the JQuery DataTable internally.

$(document).ready(function () {

    $('#TableData').dataTable({ "bStateSave": true });

});

Now there are situations (requirements) which I have faced where this will not solve the purpose (Note: JQuery DataTables uses Cookies in User’s machine to store it’s state as per the Documentation – See Here)….. like-

• There are users machine where Cookies are disabled for security purpose.
• Sometimes Cookies are not deleted and if you close and reopen the webpage instead of reloading the JQuery DataTable from scratch it will load the state from the Cookie i.e. if the user closes the application after applying some filter and navigate to 4^th page, this will remain as it is when reopen.
• Cookies are restrictive about Storing Capacity. Each of them able to store 4096 bytes (See this) which may be limitation in some scenario.
• Sometimes instead of using Cookies local-storage may be useful but that has a size limitation too.

That’s why we have leveraged the State Saving feature in SQL Server and access the same through JQuery AJAX Call.

Again the credit for building jQuery datatable goes to SpryMedia Ltd © 2007-2014

DataTables is MIT licensed.

Here the intention is to create a small website which will show the student details with their marks with some filter criterion in a page. User can navigate to and from the page. But when it is going to the subordinate pages user will get the same state of the page upon back by Breadcrumb/Back Button/IE Back Button. Furthermore there are various tricks present to fulfil small requirements. The columns we want to show here…

• A checkbox column
• A read unread flag
• ID
• Name (Link which will redirect to a different page)
• Age
• Address
• City
• Country
• Marks for all four subjects
• Comments
• A hidden column for id

To leverage  here I have used jQuery Template in the small filter table which will provide a basic idea about the same.

Here I will show you how to code so that the little tweaks will make all the differences.

General datatable formation

In general the datatable can be formed by the following code snippet declared in between document.ready…

$(document).ready(function () {
       $('#TableData').dataTable();
});

Considering the id of the main HTML table is “TableData” where the data will be populated.

Passing value to AJAX from datatable

The datatable can be populated with static HTML data. But here to make it more generic I have populated the data from a generic C# handler with AJAX. AT the time of calling the data the following parameters will send the dynamic data to the Handler which will be used to populate the DataTable. Below is the code snippet depicting the way of sending data…

 

$(document).ready(function () {
       $('#TableData').dataTable({
              "sPaginationType": "full_numbers",
              "bAutoWidth": false,
              "bFilter": true,
              "bServerSide": true,
              "bProcessing": true,
              "bDestroy": true,
              "iDisplayStart": parseInt($('#hdDisplayStart').val()),
              "iDisplayLength": parseInt($('#hdDisplayLength').val()),
              "iSortCol_0": parseInt($('#hdSortColumnIndex').val()),
              "sSortDir_0": $('#hdSortingDirection').val(),
              "sAjaxSource": '/Handler/GridDataHandler.ashx',
              "fnServerParams": function (aoData) {
                    aoData.push({ "name": "PASSFAILSTATUS", "value": GetSelectedPassFailStatus() });
                    aoData.push({ "name": "PASSFAILCOUNT", "value": GetSelectedPassFailCount() });
                    aoData.push({ "name": "AGE", "value": GetSelectedAge() });
              }
        });
});

Here to maintain the state I have used a hidden variable which will store its current position internally. Now here the variables defined by DataTable itself but the input values can be user defined…

• "bFilter" à Visibility of the filter textbox.
• "bAutoWidth" à Width of the datatable.
• "bProcessing" à Showing Processing Image/Message while DataTable is loading.
• "bDestroy" à Dispose unutilized resources.
• "sPaginationType" à The type of pagination.
• "bServerSide" à Server side Processing will be on
• "iDisplayStart" à The starting position of the current page data.
• "iDisplayLength" à The length of the current page data.
• "iSortCol_0" à The column number which will be used to sort.
• "sSortDir_0" à The direction of the sort.
• "sAjaxSource" à The source path of the Handler / Web Method

Now to add user defined variable need to leverage “fnServerParam” method where all the parameters will be passed by calculating the actual value from the function called.

In the C# Handler the input parameters will be received and saved in the following way….

 

int iDisplayStart = ToInt(HttpContext.Current.Request.Params["iDisplayStart"]);
int iDisplayLength = ToInt(HttpContext.Current.Request.Params["iDisplayLength"]);
int iSortColumn = ToInt(HttpContext.Current.Request.Params["iSortCol_0"]);
string sortDir = HttpContext.Current.Request.Params["sSortDir_0"];
int sEcho = Convert.ToInt32(HttpContext.Current.Request.Params["sEcho"]);
string rawSearch = HttpContext.Current.Request.Params["sSearch"];
string passFailstatus = HttpContext.Current.Request.Params["PASSFAILSTATUS"];
string passFailCount = HttpContext.Current.Request.Params["PASSFAILCOUNT"];
string ageStatus = HttpContext.Current.Request.Params["AGE"];

Update hidden variables with current page state

The hidden variables have to be updated on every user action in the grid pagination and that can be done by including the logic in the “fnDrawCallBack” method in the following way where SetHiddenPageVaribale is a generic method where you can write your own logic and CheckBoxListState will collect the ids and build a list for all checked rows…

"fnDrawCallback": function () {
     SetHiddenPageValuesFromState( this.fnPagingInfo().iStart, this.fnPagingInfo().iLength, this.fnSettings().aaSorting[0][0], this.fnSettings().aaSorting[0][1]);
     CheckCheckboxListState();}

Now to get all the internal DataTable variables need to define the following code internally outside document.ready() method call…

//Set the Datatable properties for future use
$.fn.dataTableExt.oApi.fnPagingInfo = function ( oSettings )
{
    return {
        "iStart":         oSettings._iDisplayStart,
        "iEnd":           oSettings.fnDisplayEnd(),
        "iLength":        oSettings._iDisplayLength,
        "iTotal":         oSettings.fnRecordsTotal(),
        "iFilteredTotal": oSettings.fnRecordsDisplay(),
        "iPage":          oSettings._iDisplayLength === -1 ? 0 : Math.ceil( oSettings._iDisplayStart / oSettings._iDisplayLength ),
        "iTotalPages":    oSettings._iDisplayLength === -1 ? 0 : Math.ceil( oSettings.fnRecordsDisplay() / oSettings._iDisplayLength )
    };
};

Store the checked checkbox list

Firstly declare the following two variables globally…

var isCheckBoxFired = false;
var globalCheckedList = '';

Then at the checkbox column call the following javascript to intimidate event. To generate the event write the following under document.ready and function separately. Also check if all the checkbox of that page are checked then check the header check box.

$("body").on("click", ".myCheckBox", function() {
     StudentIdListCheckBoxChecked();
});

function StudentIdListCheckBoxChecked()
{           
      isCheckBoxFired = true;
      CheckStudentIdListCheckAllCheckbox();
}

function CheckStudentIdListCheckAllCheckbox()
{
      var IsAllCheckBoxChecked = true;
      $('#TableData').find('tbody').find('tr').each(function (i, el) {
          var $chkObj = $(this).find(':checkbox');
          if(!$chkObj.prop('checked')){IsAllCheckBoxChecked = false;}
      });
      if(IsAllCheckBoxChecked)
          $('#chkbxCheck').prop('checked', true);
      else
          $('#chkbxCheck').prop('checked', false);
 }

Finally write the following events to build the complete list

"fnPreDrawCallback": function( oSettings ) {
    if(isCheckBoxFired){
        buildCheckboxList();
        isCheckBoxFired = false;
    }
}

Make the data row bold if unread else regular (row formatting)

Use the following method of declaration at the datatable which will handle the row format styling …

"fnRowCallback": function (nRow, aData, iDisplayIndex, iDisplayIndexFull) {
    if (aData[1].indexOf('Unread') >= 0)
        $(nRow).css('font-weight', 'bold');
    else
        $(nRow).css('font-weight', 'normal');
}

Change the processing text or image

Add the following variable at the time of defining the datatable which will change the image / text when the datatable is loading / refreshing the data…

"oLanguage": {
    "sProcessing": "<input type=\"image\" alt=\"Unread\" id=\"ajaxLoadImg\" src=\"../Images\/ajax-loader.gif\" />"}

Here I have used an image showing loading.

Declaring the column defination

Now I want to create the following column definition…

• A checkbox column (To check or uncheck used in Export to excel)
• A read unread Image (To flag if the data is read or unread)
• ID (Static column showing Student Id)
• Name (Link which will redirect to Student details page)
• Age (Static column showing Student Age)
• Address (Static column showing Student Address)
• City (Static column showing Student City)
• Country (Static column showing Student Country)
• Selective Science
  • Is Passed in Maths (An image indicating if passed or failed)
  • Is Passed in Biology (An image indicating if passed or failed)
• Pure Science
  • Is Passed in Physics (An image indicating if passed or failed)
  • Is Passed in Chemistry (An image indicating if passed or failed)
• Comments (Static column showing comments [if any])
• A hidden column for id (Used for redirection to detailed page)

To do this first we have to declare the HTML with the Table headers (See how the merge header has been created)…

<table cellpadding="0" cellspacing="0" border="0" class="dataTable" id="TableData">
    <thead>
        <tr>
            <th rowspan="2"><input type="checkbox" id="chkbxCheck" onclick="ToggleCheckboxes(this)" /></th>
            <th rowspan="2">!</th>
            <th rowspan="2" style="width: 30px"><u>Student ID</u></th>
            <th rowspan="2"><u>Student Name</u></th>
            <th rowspan="2" style="max-width: 100px"><u>Age</u></th>
            <th rowspan="2" style="width: 30px"><u>Address</u></th>
            <th rowspan="2"><u>City</u></th>
            <th rowspan="2" style="width: 30px"><u>Country</u></th>
            <th colspan="2" style="text-align: center; border-right: 1px solid black;"> <u>Pure Science</u></th>
            <th colspan="2" style="text-align: center"><u>Selective Science</u></th>
            <th rowspan="2"><u>Information</u></th>
            <th rowspan="2">StudentId</th>
         </tr>
         <tr>
            <th rowspan="1" style="width: 30px"><u>Result for<br />Maths</u></th>
            <th rowspan="1"><u>Result for<br />Biology</u></th>
            <th rowspan="1"><u>Result for<br />Physics</u></th>
            <th rowspan="1"><u>Result for<br />Chemistry</u></th>
         </tr>
      </thead>
      <tbody>
          <tr>
             <td colspan="14" class="dataTables_empty">Loading data from server<br />
                 <br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br />
             </td>
          </tr>
      </tbody>
      <tfoot>
      </tfoot>
  </table>

Now in the datatable definition add the column details. In the definition there are various property has been used e.g.

“sName”: Column internal field name

“sWidth”: Width of the column

“bSearchable”: Can be searched or not

Etc. Please see the DataTable documentation for the details. Here in the Demo Application you can view the implementation.

Datatable state saving in the database

Now as an example from the datatable I have allowed user to go to the details page and user can be able to back to the summary page again in three ways…

1. Breadcrumb upper level click
2. ASP Back button Click
3. IE back button click

Only when the user will go to the Student Details (i.e. down level) it will store the state. But if the user goes to Home then the state will be refreshed as it is in the upper level and assuming a fresh start required. In all three cases it will repopulate the state from database. Here is the complete algorithm…

When user is leaving the Summary page by clicking  the name of the student all the required filter values will be stored in the database with their individual variable name and Module number. (Module number is provided to give a flexibility where application has same filter for different modules and want to save the state for individual modules). Now Another scenario is the when the user chose some filters from the summary and update the data then the filter will not save with the default values.”varSaveMode” here is actually defining those two scenarios; Regular à When leaving the page & UpdateSummary à When updating filters.  Here is the AJAX call which will save the data

//Save user settings based on the parameters
function SaveUserStudentListSession(varSaveMode) {
    switch(varSaveMode)
    {
        case 'Regular':
            break;
        case 'UpdateSummary':
            $('#hdDisplayStart').val('0');
            $('#hdDisplayLength').val('10');
            $('#hdSortingDirection').val('DESC');
            $('#hdSortColumnIndex').val('0');
            $('#hdSummaryResult').val(GetSelectedPassFailCount());
            $('#hdStudentResults').val(GetSelectedPassFailStatus());
            $('#hdStudentAges').val(GetSelectedAge());
            $('#hdUniqueSession').val('');
            globalCheckedList = '';
            break;
     };
     AjaxCallSaveSession('StudentListiDisplayStart', $('#hdDisplayStart').val());
     AjaxCallSaveSession('StudentListiDisplayLength', $('#hdDisplayLength').val());
     AjaxCallSaveSession('StudentListiSortColDirection', $('#hdSortingDirection').val());
     AjaxCallSaveSession('StudentListiSortColIndex', $('#hdSortColumnIndex').val());
     AjaxCallSaveSession('StudentListPassFailCountChecked', $('#hdSummaryResult').val());
     AjaxCallSaveSession('StudentListPassFailOnlyChecked', $('#hdStudentResults').val());
     AjaxCallSaveSession('StudentListAgeChecked', $('#hdStudentAges').val());
     AjaxCallSaveSession('StudentListUniqueSessionId', $('#hdUniqueSession').val());
     AjaxCallSaveSession('StudentListUserCheckedIDs', globalCheckedList.replace(/\'/g, ''));
}

function AjaxCallSaveSession(varName, varValue)
{
    $.ajax({
        type: "POST",               
        url: "SampleDataTable.aspx/SaveSessionVariables",
        contentType: "application/json; charset=utf-8",
        data: "{'varName': '" + varName + "','varValue': '" + varValue + "'}",
        dataType: "json",
        success: AjaxSucceeded,
        error: AjaxFailed
    });
}

//dummy funtion for success
function AjaxSucceeded() {
}

//Any AJAX failure method
function AjaxFailed(err) {
    alert(err.responseText);
}

Now to repopulate  it will actually get all the data from the DB with the following AJAX call & populate the controls…

//Call the AJAX to get the last saved criterion by the user
function LoadUserStudentListSession()
{
    $.ajax({
        type: "POST",
        url: "SampleDataTable.aspx/LoadSessionVariables",
        contentType: "application/json; charset=utf-8",
        data: "{}",
        dataType: "json",
        success: DisplayUserStudentListVariables,
        error: AjaxFailed
    });
}

Now DisplayUserStudentListVariables function will check if the stored session id is same with the current session id. If yes then it will populate the data to all the hidden fields and eventually to the controls and load the datatable.  With this unique session-id it will be able to identify if this is a new session or an existing session.

From the breadcrumb when the user goes back to the Home page it will  insert blank value to this Unique Session ID variable so that next time the page will behave as freshly loaded.

Additional item (jQuery template)

Here as an additional topic I have used jQuery Template in the summary table. This is a very useful plugin for dynamic table generation. It’s a jQuery official plugin. To use it first we need to include jQuery.tmpl.js in the application and add a reference in the header. Then we need to add the generic template definition in the header…

<script type="text/javascript" src="../Scripts/jquery.tmpl.js"></script>
<script type="text/x-jquery-tmpl" id="StudentSummaryTemplate">
        <tr>
            <td align="left">
                <input type="checkbox" value="${SubjectType}" id="P${SubjectType}" checked="checked" class="clsException" onclick="SummaryCheckBoxChecked()" />${SubjectType}</td>
            <td>${TotalPassed}</td>
            <td>${TotalFailed}</td>
        </tr>
    </script>

Here the database field names are  described as ${}. Now declare a table where this table will be populated…

<table class="dataTable" id="StudentSummaryTable">
    <thead>
        <tr>
            <th>
                <input type="checkbox" id="SummaryAllChkbx" checked="checked" onclick="toggleSummaryGridCheckBoxes(this.checked)" /></th>
            <th>Number of Students Passed</th>
            <th>Number of Students Failed</th>
        </tr>
    </thead>
    <tbody id="divStudentSummaryTable"></tbody>
    <tfoot></tfoot>
</table>

Finally through the AJAX call populate the data…

//Calling the AJAX to load the summary filter data
function LoadSummaryTable() {
    $("#divStudentSummaryTable").empty();
    $("#divStudentSummaryTable").html("<center><img id='imgLoading' src ='../Images/ajax-loader.gif' width='170' height='215' /></center>");
    var studentPassFail = $("input[type='radio'].excpStatus:checked").val();
    $.ajax({
        type: "POST",
        async: false,
        url: "SampleDataTable.aspx/StudentSummaryData",
        contentType: "application/json; charset=utf-8",
        data: '{}',
        dataType: "json",
        success: DisplaySummary,
        error: AjaxFailed
    });
}

//Success method of LoadSummaryTable where it populates the table with the result.
function DisplaySummary(result) {
    Products = eval(result.d);
    productCount = Products.length;
    if (productCount == 0)
        $("#divStudentSummaryTable").html("No records found for the selection specified.");
    else {
        $("#divStudentSummaryTable").empty();
        $("#StudentSummaryTemplate").tmpl(Products).appendTo($("#divStudentSummaryTable"));
    }
}

Points of Interest

History

NA