How to bind Kendo Grid with System.Data.DataTable at runtime

Question

I need to open a Kendo Grid in popup on Button Click. On button click server returns DataTable which has dynamics column (i.e. 1 to n no. of columns).On button click i get DataTable using jQuery ajax call. But I am unable to bind that data. How to resolve this issue

Answer 1

Try like this,

Script

<script type="text/javascript">

    $(function () {
        schmebind();
    });

    function schmebind() {
        var schemaModel = {};
        var type;
        var IsEditable = false;
        var dateColumnArray = [];

        $.each(JaiminField, function (index, da) {
            type = (da.type == undefined || da.type == 'number' || da.type == 'date') ? 'string' : da.type;
            schemaModel[da.field] = { type: type, editable: false };
            if (da.type == 'date') {
                dateColumnArray.push(da.field);
            }
        });

        var schemaModelNew = kendo.data.Model.define({
            id: "$id",
            fields: schemaModel,
            nullable: true
        });

        var knownOutagesDataSource = new kendo.data.DataSource({
            data: Jaimin,
            pageSize: 10,
            batch: true,
            schema: {
                model: schemaModelNew
            }
        });

        CreateGrid("#DynamicGrid", knownOutagesDataSource, dateColumnArray, null, null)
    }



    function CreateGrid(targetId, data, columnModel, detailTemplate, detailInit) {
        $(targetId).kendoGrid({
            dataSource: data,
            selectable: "row",
            detailTemplate: detailTemplate,
            detailInit: detailInit,
            columns: columnModel,
            filterable: false,
            sortable: true,
            dataBound: function (e) {
                if (e.sender._data.length == 0) {
                    var mgs, col;
                    mgs = "No results found for";
                    col = 9;
                    var contentDiv = this.wrapper.children(".k-grid-content"),
                 dataTable = contentDiv.children("table");
                    if (!dataTable.find("tr").length) {
                        dataTable.children("tbody").append("<tr><td colspan='" + col + "'><div style='color:red;width:500px'>" + mgs + "</div></td></tr>");
                        if (navigator.userAgent.match(/MSIE ([0-9]+)\./)) {
                            dataTable.width(this.wrapper.children(".k-grid-header").find("table").width());
                            contentDiv.scrollLeft(1);
                        }
                    }
                }
            },
            pageable: {
                previousNext: true,
                numeric: false,
                messages: {
                    empty: "No data"
                }
            },
            navigatable: true,
            scrollable: true,
            resizable: true
        });
    }
</script>

View

<div id="DynamicGrid">
</div>

Json File

var Jaimin = [
{ $id: "3", ActiveFlag: "1", CreatedBy: "Admin", TriageTypeId: 3, TriageTypeKey: "DATAUSAGE_PROVISIONING", TriageTypeName: "Data usage Provisioning", TriageTypeOrder: 3 },
{ $id: "4", ActiveFlag: "1", CreatedBy: "Admin", TriageTypeId: 4, TriageTypeKey: "DATAUSAGE_PROVISIONING", TriageTypeName: "Device Issues", TriageTypeOrder: 4 },
{ $id: "5", ActiveFlag: "1", CreatedBy: "Admin", TriageTypeId: 5, TriageTypeKey: "DATAUSAGE_PROVISIONING", TriageTypeName: "Known Outages", TriageTypeOrder: 5 },
{ $id: "6", ActiveFlag: "1", CreatedBy: "Admin", TriageTypeId: 6, TriageTypeKey: "DATAUSAGE_PROVISIONING", TriageTypeName: "Coverage", TriageTypeOrder: 6 },
{ $id: "7", ActiveFlag: "1", CreatedBy: "Admin", TriageTypeId: 7, TriageTypeKey: "DATAUSAGE_PROVISIONING", TriageTypeName: "Known Issues", TriageTypeOrder: 7 },
{ $id: "8", ActiveFlag: "1", CreatedBy: "Admin", TriageTypeId: 8, TriageTypeKey: "DATAUSAGE_PROVISIONING", TriageTypeName: "SIVR", TriageTypeOrder: 8 },
{ $id: "9", ActiveFlag: "1", CreatedBy: "Admin", TriageTypeId: 9, TriageTypeKey: "DATAUSAGE_PROVISIONING", TriageTypeName: "Grand Central - Device", TriageTypeOrder: 9 },
{ $id: "10", ActiveFlag: "1", CreatedBy: "Admin", TriageTypeId: 10, TriageTypeKey: "DATAUSAGE_PROVISIONING", TriageTypeName: "Grand Central - Coverage", TriageTypeOrder: 10 },
{ $id: "11", ActiveFlag: "1", CreatedBy: "Admin", TriageTypeId: 11, TriageTypeKey: "DATAUSAGE_PROVISIONING", TriageTypeName: "Grand Central - Account", TriageTypeOrder: 11 },
{ $id: "12", ActiveFlag: "0", CreatedBy: "Admin", TriageTypeId: 12, TriageTypeKey: "DATAUSAGE_PROVISIONING", TriageTypeName: "Network Type", TriageTypeOrder: 12 },
{ $id: "13", ActiveFlag: "1", CreatedBy: "Admin", TriageTypeId: 13, TriageTypeKey: "DATAUSAGE_PROVISIONING", TriageTypeName: "Known outages", TriageTypeOrder: 13 },
{ $id: "14", ActiveFlag: "1", CreatedBy: "Admin", TriageTypeId: 14, TriageTypeKey: "DATAUSAGE_PROVISIONING", TriageTypeName: "Local Site Issues", TriageTypeOrder: 14 },
{ $id: "15", ActiveFlag: "1", CreatedBy: "Admin", TriageTypeId: 15, TriageTypeKey: "DATAUSAGE_PROVISIONING", TriageTypeName: "Previous Complaints", TriageTypeOrder: 15 },
{ $id: "16", ActiveFlag: "1", CreatedBy: "Admin", TriageTypeId: 16, TriageTypeKey: "DATAUSAGE_PROVISIONING", TriageTypeName: "IMEI blocked", TriageTypeOrder: 16 },
{ $id: "17", ActiveFlag: "1", CreatedBy: "Admin", TriageTypeId: 17, TriageTypeKey: "DATAUSAGE_PROVISIONING", TriageTypeName: "High Speed Data Throttled", TriageTypeOrder: 17 },
{ $id: "18", ActiveFlag: "1", CreatedBy: "Admin", TriageTypeId: 18, TriageTypeKey: "DATAUSAGE_PROVISIONING", TriageTypeName: "Tethering / Hotspot Blocked", TriageTypeOrder: 18 },
{ $id: "19", ActiveFlag: "1", CreatedBy: "Admin", TriageTypeId: 19, TriageTypeKey: "DATAUSAGE_PROVISIONING", TriageTypeName: "Retrieving on-device diagnostics", TriageTypeOrder: 19 },
{ $id: "20", ActiveFlag: "1", CreatedBy: "Admin", TriageTypeId: 20, TriageTypeKey: "DATAUSAGE_PROVISIONING", TriageTypeName: "Retrieving Device Settings", TriageTypeOrder: 20 },
{ $id: "21", ActiveFlag: "1", CreatedBy: "Admin", TriageTypeId: 21, TriageTypeKey: "DATAUSAGE_PROVISIONING", TriageTypeName: "Scanning Applications", TriageTypeOrder: 21 },
{ $id: "22", ActiveFlag: "1", CreatedBy: "Admin", TriageTypeId: 22, TriageTypeKey: "DATAUSAGE_PROVISIONING", TriageTypeName: "Checking Software Version", TriageTypeOrder: 22}]



var JaiminField = [
{ field: "$id", type: "int" },
{ field: "ActiveFlag", type: "int" },
{ field: "CreatedBy", type: "string" },
{ field: "TriageTypeId", type: "int" },
{ field: "TriageTypeKey", type: "string" },
{ field: "TriageTypeName", type: "string" },
{ field: "TriageTypeOrder", type: "number" }
]

Demo : http://jsbin.com/honavive/1

If you have any concern then let me know.

Answer 2

First, you need to serialize the DataTableusing Newtonsoft.Json library. This will create the data as json string in key value pairs.

Once done, on client side bind the data to kendo grid and generate the column using the first row of data.

columns: generateColumns(gridData[0])


function generateColumns(firstRow)
{
 var colums = [];

        for (var property in firstRow) {
            var col = {
                field: property,
                title: property,
                width: 0,
template:""//specify your template here,
attributes:''//specify your td attributes here
               }
            colums.push(col);
        }

        return colums;
}

Please let me know if this help you. I can update your jsfiddle if you have any.

Answer 3

It's actually quite simple and quick too, see below:

@model  System.Data.DataTable

@(Html.Kendo().Grid(Model)
            .Name("DayViewGrid")
            .Columns(columns =>
              {
                  foreach (System.Data.DataColumn column in Model.Columns)
                  {
                        columns.Bound(column.ColumnName).Title(column.Caption);
                  }
              }
            )
            .Scrollable(scr => scr.Height(600))
            .DataSource(dataSource => dataSource
                .Ajax()
                .Model(model =>
                     {
                         model.Id(Model.Columns[0].ColumnName);

                         foreach (System.Data.DataColumn column in Model.Columns)
                         {
                            if(column.Ordinal > 0)
                                model.Field(column.ColumnName, column.DataType);
                         }
                     }
                 )

            )
           )

Please let me know if this is not what you were looking for?