ASP.NET MVC 5 with Razor Kendo UI: Dynamic Grid Creation – Columns, Ordering, Grouping and Paging

The requirements were simple! A grid that will load a whatever query, grouped by whatever columns, with filters enabled, paging and everything. Easy task with ASP.NET Telerik grids but not easy at all with Kendo UI!

Since we can’t have a ViewModel or Model for the grid (we don’t know what columns a whatever query has!), I came up with a rather different ViewModel that holds descriptions for columns, groups etc…:

    public class WhatEverQueryModel {
        public DataTable Data { get; set; }
        public List<string> Groups { get; set; }
        public Dictionary<string, System.Type> Bind { get; set; }
    }

The action simple populates those three previous properties:

public ActionResult Index( int id ) {

    //Get query details (query id as argument)
    var queryDetails = GetQueryDetails( id );
    var conString = GetConString( queryDetails.ConString.username, queryDetails.ConString.password, queryDetails.ConString.db, queryDetails.ConString.server );
    var SQL = queryDetails.Query;

    var Model  = new ViewModels.WhatEverQueryModel();
    Model.Data = GetQueryData( conString, SQL );//Execute query using conString
    Model.Groups = queryDetails.GroupBy.Split( ',' ).Reverse().ToList<string>();//Comma separated values

    //Column description: Name and Type
    var dyn = new Dictionary<string, System.Type>();
    foreach ( System.Data.DataColumn column in Model.Data.Columns ) {
        var t = System.Type.GetType( column.DataType.FullName );
        dyn.Add( column.ColumnName, t );
    }
    Model.Bind = dyn;

    return View( Model );
}    

Finally, all comes to the grid in the View:

@(Html.Kendo().Grid<dynamic>()
    .Name( "WhateverQueryGrid" )
    .Columns( columns => {
        //Define the columns
        foreach ( var c in Model.Bind )
            columns.Bound( c.Value, c.Key );
    } )
    .DataSource( dataSource => dataSource
        .Ajax()
        .Model( model => {
            //Define the model
            foreach ( System.Data.DataColumn column in Model.Data.Columns ) {
                model.Field( column.ColumnName, column.DataType );
            }
        } )
        //Unfortunately you need an ajax call that will execute and return the same data of the same query. 
        //Alternatively you can save the data from the Index action to a session variable and restore it in Grid_Read,
        //but I would probably suggest a TOP 1 query at first, and a paged call in Grid_Read, depending on the number of rows returned...
        //This code does neither, so you should probably implement a solution for the two calls...!
        .Read( read => read.Action( "Grid_Read", "WhateverQueryController" ) )
        .Group( group => {
            //Define the grids
            foreach ( var g in Model.Groups )
                if ( Model.Bind.Keys.Contains( g ) )
                    group.Add( g, Model.Bind.Where( x => x.Key == g ).First().Value );
        } )
    )
    .Groupable()
    .Sortable( s => s.AllowUnsort( true ) )
    .Filterable( ftb => ftb.Mode( GridFilterMode.Menu ) )
        .Pageable( pageable => pageable
        .Refresh( true )
        .PageSizes( true )
        .ButtonCount( 5 ) 
    )
)
Advertisements

5 thoughts on “ASP.NET MVC 5 with Razor Kendo UI: Dynamic Grid Creation – Columns, Ordering, Grouping and Paging

  1. Pingback: Telerik mvc grid, columns.bound to dictionary value. or "dynamic property in model" | DL-UAT

  2. I have a big problem with Kendo Grid dynamic.
    in the first load, I load to Grid 3 column. After that, with condition by startDate and EndDate, my query returned 11 columns with 4 records. But, Grid only update data with 4 records and 3 column ? What’s wrong ?

    Here is controller :

    public ActionResult Index(string startdate, string ends)
            {
                if (startdate != null &amp;&amp; startdate != "")
                {
                    sDate = Convert.ToDateTime(startdate).ToString("yyyy-MM-dd");
                    eDate = Convert.ToDateTime(ends).ToString("yyyy-MM-dd");
                }
                else
                {
                    sDate = DateTime.Now.ToString("yyyy-MM-dd");
                    eDate = DateTime.Now.ToString("yyyy-MM-dd");
                }
                SQL = "p_MPA_FinalReportTotalOutput '" + sDate + "','" + eDate + "'";
                var Model = new Reports();
                Model.Data = db.Getdata(SQL);
    
                //Column description: Name and Type
                var dyn = new Dictionary();
                foreach (System.Data.DataColumn column in Model.Data.Columns)
                {
                    var t = System.Type.GetType("System.String");
                    //var t = System.Type.GetType(column.DataType.FullName);
                    dyn.Add(column.ColumnName, t);
                }
                Model.Bind = dyn;
    
                return View(Model);
            }
    
            public JsonResult GET([DataSourceRequest]DataSourceRequest request, string startdate, string ends)
            {
                var Model = new Reports();
                if (startdate != null &amp;&amp; startdate != "")
                {
                    sDate = Convert.ToDateTime(startdate).ToString("yyyy-MM-dd");
                    eDate = Convert.ToDateTime(ends).ToString("yyyy-MM-dd");
                }
                else
                {
                    sDate = DateTime.Now.ToString("yyyy-MM-dd");
                    eDate = DateTime.Now.ToString("yyyy-MM-dd");
                }
                SQL = "p_MPA_FinalReportTotalOutput '" + sDate + "','" + eDate + "'";
                // Index(sDate, eDate); 
                Model.Data = db.Getdata(SQL);//Execute query using conString
    
                //Column description: Name and Type
                var dyn = new Dictionary();
                foreach (System.Data.DataColumn column in Model.Data.Columns)
                {
                    //var t =  System.Type.GetType(column.DataType.FullName);
                    var t = System.Type.GetType("System.String");
                    dyn.Add(column.ColumnName, t);
                }
                Model.Bind = dyn;
    
                return Json(Model.Data.ToDataSourceResult(request));
            }
    

    In the View :

    @(Html.Kendo().Grid()
        .Name( "WhateverQueryGrid" )
        .DataSource( dataSource =&gt; dataSource
            .Ajax()
            .AutoSync(true)
            .Read(read =&gt; read.Action("GET", "Report").Data("PassParam"))
            .Model(model =&gt;
            {
                //Define the model
                foreach (System.Data.DataColumn column in Model.Data.Columns)
                {
                    model.Field(column.ColumnName, column.DataType);
                }
            })
            //Unfortunately you need an ajax call that will execute and return the same data of the same query. 
            //Alternatively you can save the data from the Index action to a session variable and restore it in Grid_Read,
            //but I would probably suggest a TOP 1 query at first, and a paged call in Grid_Read, depending on the number of rows returned...
            //This code does neither, so you should probably implement a solution for the two calls...!
            //.Read( read =&gt; read.Action( "Grid_Read", "WhateverQueryController" ))
            .PageSize(10)
        )
        .Columns( columns =&gt; {
            //Define the columns
           
            foreach (var c in Model.Bind)
            {
                columns.Bound(c.Value, c.Key);
                
            }
        } )
        .Groupable()
        .Sortable( s =&gt; s.AllowUnsort( true ) )
        .Filterable( ftb =&gt; ftb.Mode( GridFilterMode.Menu))
            .Pageable( pageable =&gt; pageable
            .Refresh( true )
            .PageSizes( true )
            //.ButtonCount( 5 ) 
        )
        .Selectable()
        .Groupable()
        .ToolBar(toolbar =&gt; {
            toolbar.Excel();
            toolbar.Pdf();
        })
    )
    

    –> Button event click()

    $(".refesh").click(function () {
            $("#WhateverQueryGrid").data("kendoGrid").dataSource.read();
        });
    
        function PassParam() {
            var start = $("#start").data("kendoDatePicker").value();
            var end = $("#end").data("kendoDatePicker").value();
            return {
                startdate: start,
                ends: end
            };
        }
    
    Reply
    • Second call is an ajax call for data only. You cannot change the number of columns without reloading the grid.
      My suggestion is to have all the columns from the beginning and then hide/show what ever you want…

      Reply
  3. This worked really well. Thank you!
    To avoid loading the data twice is very straightforward. You don’t need to send the datatable in the WhatEverQueryModel. The Bind property of the WhatEverQueryModel suffices to create the grid.Model, so you can remove the datatable from WhatEverQueryModel altogether.
    EG:
    .Model(model =>
    {
    //Define the model dynamically
    foreach (var column in Model.Bind)
    {
    model.Field(column.Key, column.Value);
    }
    })

    Again thanks!

    Reply
  4. Hi,

    We need to dynamically generate the grid columns and data. The details grid is in partial view and is populated using jquery. Our requirement is to get multiple objects as return type.

    onClickDetails: function (e) {

    $.ajax({
    url: “/Area/Controller/GetItems”,
    type: ‘GET’,
    data: { selectedPackageId: selectedTradePackageId }
    }).done(function (result) {

    //Result holds Dynamic Model Generated.
    // Result holds another object
    var obj1 = result
    var scriptTemplate = kendo.template($(“#scriptTemplate”).html());
    $(“#section”).html(scriptTemplate(object1)); $(“#grid1”).data(“kendoGrid”).dataSource.data(result.GridModel);
    });

    }

    Partial View (Can’t we load without calling read)

            
                
                      #= Name #

                             
               
            

    @(Html.Kendo().Grid()
    .Name(“grid1”)
    .Columns(columns =>
    {
    //Define the columns
    foreach (var c in Model.Bind)
    columns.Bound(c.Value, c.Key);

    })
    .DataSource(datasource => datasource
    .Ajax()
    .Model(model =>
    {
    foreach (System.Data.DataColumn column in Model.Data.Columns)
    {
    model.Field(column.ColumnName, column.DataType);
    }
    })
    )
    .Pageable().DataSource(dataSource => dataSource.Ajax().ServerOperation(false)) //Enable paging
    )*

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s