JQGrid Dynamic Reading of Colmodel and ColNames fr

Question

We are replacing older grid with JQGrid. 

We want to bind grid from code behind including ColModel and ColNames. Column names and column model will be decided run time.

We tried out non-working:

1. Using HttpHandler + jQuery Ajax
2. Using WebMethods + jQuery Ajax

There is not a single working application on internet. Can anyone help out?

System: ASPX web form which load list of records from backend.

We used all the options provided here on Stackoverflow, but no luck.

Answer 1

I have recently implemented a similar functionality in one of my projects using similar tools (ASPX WebForms, WebMethods and jQuery AJAX).

Be sure to read and understand the comments in VB.NET and JavaScript to avoid any errors.

'INCLUDE NAMESPACES REQUIRED FOR WEBMETHOD IMPLEMENTATION
Imports System.Web.Services
Imports System.Web.Script.Services

'WEB METHOD FOR AJAX CALL RETURNING JSON
<WebMethod()> _
<ScriptMethod(ResponseFormat:=ResponseFormat.Json)> _
Public Shared Function getJSONData(ByVal iRows As String) As String
    '...
    'YOUR DB LOGIC HERE
    'LOAD YOUR DATA INTO A DATASET NAMED ds
    '...
    Dim dict As New Dictionary(Of String, Object)
    For Each dt As DataTable In ds.Tables
        Dim rows As New List(Of Dictionary(Of String, Object))()
        Dim stDataType As String, stAlign As String, inWidth As Integer
        Dim row As New Dictionary(Of String, Object)()

        'PREPARE colModel
        For Each col As DataColumn In dt.Columns
            row = New Dictionary(Of String, Object)()
            row.Add("index", col.ColumnName)
            row.Add("name", col.ColumnName)
            row.Add("label", col.ColumnName.Replace("_", " "))
            row.Add("resizeable", True)
            row.Add("search", True)
            row.Add("sortable", True)
            'GET COLUMN DATA TYPE, COLUMN ALIGNMENT AND COLUMN WIDTH
            Select Case col.DataType.ToString
                Case "Byte", "Int16", "Int32", "Integer", "Int64", "System.Byte", "System.Int16", "System.Int32", "System.Integer", "System.Int64"
                    stDataType = "integer" : stAlign = "right" : inWidth = 80
                Case "money", "System.Single", "System.Decimal", "System.Double"
                    stDataType = "number" : stAlign = "right" : inWidth = 80
                Case "String", "System.String", "System.Byte", "System.Char"
                    stDataType = "" : stAlign = "" : inWidth = 175
                Case "bit", "Boolean", "System.Boolean"
                    row.Add("formatter", "checkbox")
                    stDataType = "" : stAlign = "center" : inWidth = 100
                Case "datetime", "System.DateTime"
                    Select Case col.ColumnName
                        Case "Updated_On"
                            'dd/MM/yyyy HH:mm:ss FOR SPECIFIED COLUMNS DATE WITH TIME
                            row.Add("template", "dateTimeFormat")
                        Case Else
                            'dd/MM/yyyy FOR ALL OTHER COLUMNS DATE ONLY
                            row.Add("template", "dateOnlyFormat")
                    End Select
                    row.Add("formatter", "date")
                    stDataType = "date" : stAlign = "right" : inWidth = 150
                Case Else
                    stDataType = "" : stAlign = "" : inWidth = 200
            End Select
            If stDataType.Length > 0 Then
                row.Add("sorttype", stDataType)
                row.Add("searchtype", stDataType) ' &  ",searchrules:"{""required":true, "number":true, "maxValue":13}}
            End If
            If stAlign.Length > 0 Then row.Add("align", stAlign)
            row.Add("width", inWidth)
            rows.Add(row)
        Next
        't_Articles USED AS TABLENAME FOR THIS EXAMPLE
        dict.Add(dt.TableName & "_ColModel", rows)

        'GET DATA
        rows = New List(Of Dictionary(Of String, Object))()
        For Each dr As DataRow In dt.Rows
            row = New Dictionary(Of String, Object)()
            For Each col As DataColumn In dt.Columns
                Select Case col.DataType.ToString
                    Case "datetime", "System.DateTime"
                        Select Case col.ColumnName
                            Case "Updated_On"
                                'FOR SPECIFIED COLUMNS DATE WITH TIME
                                row.Add(col.ColumnName, Format(dr(col), "dd/MM/yyyy HH:mm:ss"))
                            Case Else
                                'ALL OTHER COLUMNS DATE ONLY
                                row.Add(col.ColumnName, Format(dr(col), "dd/MM/yyyy"))
                        End Select
                    Case Else
                        row.Add(col.ColumnName, dr(col).ToString())
                End Select
            Next
            rows.Add(row)
        Next
        dict.Add(dt.TableName & "_Data", rows)
    Next

    Dim serializer As New JavaScriptSerializer
    Dim stResult As String = ""
    stResult = serializer.Serialize(dict)
    Return stResult
End Function

AJAX call in ASPX

<script type="text/javascript">
    $(document).ready(function () {
        var sPath = window.location.pathname;
        var sPage = sPath.substring(sPath.lastIndexOf('/') + 1);
        var dateTemplateMapping = {
            "dateOnlyFormat": { formatoptions: { srcformat: "d-m-Y", newformat: "d/m/Y"} },
            "dateTimeFormat": { formatoptions: { srcformat: "d-m-Y H:i:s", newformat: "d/m/Y H:i:s"} }
        };
        $.ajax({
            type: "POST",
            url: sPage + '/getJSONData',
            //PASS ANY PARAMETERS TO WEBMETHOD, IF REQUIRED
            data: "{'iRows':'0'}",
            contentType: 'application/json; charset=utf-8',
            dataType: "json",
            //FILTER JSON FOR INITIAL d IN OUTPUT PRO .NET 3.5
            dataFilter: function (data) {
                var resultData = eval('(' + data + ')');
                if (resultData.hasOwnProperty('d')) {
                    return resultData.d;
                } else {
                    return resultData;
                }
            },
            success: function (resultData) {
                //REPLACE t_Articles WITH THE TABLE NAME USED IN DATASET FOR ADDING THE TABLE
                var colD = resultData.t_Articles_Data;
                var colM = resultData.t_Articles_ColModel;
                var i, cm;
                //APPLY DATE, DATETIME FORMATTER
                for (i = 0; i < colM.length; i++) {
                    cm = colM[i];
                    if (cm.hasOwnProperty("template") && dateTemplateMapping.hasOwnProperty(cm.template)) {
                        cm.template = dateTemplateMapping[cm.template];
                    }
                }
                $("#DataGridTable").jqGrid({
                    datatype: 'local',
                    data: colD,
                    colModel: colM,
                    multiselect: false,
                    pager: jQuery('#DataGridPager'),
                    loadComplete: function () {
                        //alert("ALL IS WELL");
                    }
                });
                //ANY OTHER JQGRID SETTINGS, IF REQUIRED
                jQuery("#DataGridTable").jqGrid('navGrid', '#DataGridPager', { del: false, add: false, edit: false }, {}, {}, {}, { multipleSearch: true });
            },
            error: function (x, e) {
                alert("Error with AJAX callback\nreadyState: " + x.readyState + "\nstatus: " + x.status + "\nmsg: " + e.msg);
            }
        });
    });
</script>

HTML markup for jqGrid

<table id="DataGridTable">
    <tr>
        <td><img src="/design/camera-loader.gif" alt="Loading ..."/></td>
    </tr>
</table>
<div id="DataGridPager"></div>

Implemented with .NET Framework 4.0, jqGrid 4.6.0, jQuery 1.9.1