George Kosmidis

Microsoft MVP | Speaks of Azure, AI & .NET | Founder of Munich .NET
Building tomorrow @

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

by George Kosmidis / Published 9 years and 6 months ago, modified 4 years ago

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 Groups { get; set; }
    public Dictionary Bind { get; set; }

The following action simply populates the three properties of our model:

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();//Comma separated values

    //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 );
        dyn.Add( column.ColumnName, t );
    Model.Bind = dyn;

    return View( Model );

And finally, all comes together for the grid in the View:

    .Name( "WhateverQueryGrid" )
    .Columns( columns => {
        //Define the columns
        foreach ( var c in Model.Bind )
            columns.Bound( c.Value, c.Key );
    } )
    .DataSource( dataSource => dataSource
        .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 );
        } )
    .Sortable( s => s.AllowUnsort( true ) )
    .Filterable( ftb => ftb.Mode( GridFilterMode.Menu ) )
        .Pageable( pageable => pageable
        .Refresh( true )
        .PageSizes( true )
        .ButtonCount( 5 )

This page is open source. Noticed a typo? Or something unclear?
Edit Page Create Issue Discuss
Microsoft MVP - George Kosmidis
Azure Architecture Icons - SVGs, PNGs and libraries