Telerik MVC Grid with Dynamic Columns (OR) Binding Grid to Dynamic DataTable

You may come across a requirement where you need to create a grid with dynamic columns. In such a case our model is may not fixed, it is getting change at runtime, in other words you can say it’s dynamic. In such cases it’s really a headache to bind such data to a single grid. This can be achieved by binding DataTable to the grid.  

Here is how we can achieve this.

1. First, we have to get all our data in one DataTable.

      a.  We can convert list into DataTable, one can use below method to convert list into DataTable

        public static DataTable ConvertListToDataTable<T>(List<T> items)

        {

            DataTable dataTable = new DataTable(typeof(T).Name); 

            //Get all the properties

            PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

            foreach (PropertyInfo prop in Props)

            {

                //Setting column names as Property names

                dataTable.Columns.Add(prop.Name);

            } 

            //Fill data into DataTable

            foreach (var item in items)

            {

                var values = new object[Props.Length];

                for (int i = 0; i < Props.Length; i++)

                {

                    //inserting property values to datatable rows

                    values[i] = Props[i].GetValue(item, null);

                }

                dataTable.Rows.Add(values);

            }

            //put a breakpoint here and check datatable

            return dataTable;

       }

(OR)

      b.  We can directly get datatable from database.

        private DataTable GetDataTable()

        {

            var connection = ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString;

            using (var dataAdapter = new SqlDataAdapter("SELECT * from TableName", connection))

            {

                var dataTable = new DataTable(); 

                dataAdapter.Fill(dataTable);

                dataAdapter.FillSchema(dataTable, SchemaType.Mapped);

                return dataTable;              

            }

        }

2. Then write a method to read data for grid, this method will be called when data requested.

        public ActionResult Read([DataSourceRequest] DataSourceRequest request)

        {

            DataTable Data = GetDataTable();

            return Json(products.ToDataSourceResult(request));

        }

3. And at the end we need to create telerik mvc grid in our view, code snippet is as follows:-

@model System.Data.DataTable

@(Html.Kendo().Grid<dynamic>()

    .Name("gridDataView")

    .Columns(columns =>

    {

        foreach (System.Data.DataColumn column in Model.Columns)

        {

            //You can write additional validation on columns if needed, as shown

            if (column.DataType.FullName == "System.DateTime")

            {

                var c = columns.Bound(column.ColumnName).Format("{0:MM/dd/yyyy}");

            }

            else

            {

                var c = columns.Bound(column.ColumnName);

            }

            //Can have footar Template

            if (column.ColumnName == "UnitPrice")

            {

                c.ClientFooterTemplate("sum:#:sum#").ClientGroupFooterTemplate("sum:#:sum#");

            }

        }

        //Can Have EDIT/DELETE/UPDATE buttons

        //columns.Command(cmd=>cmd.Edit());

    })

    .Pageable()

    .Sortable()

    //.Editable(ed=>ed.Mode(GridEditMode.PopUp))

    .Editable(editable => editable.Mode(GridEditMode.InLine))

    .Filterable()

    .Groupable()

    .DataSource(dataSource => dataSource

        .Ajax()       

        .Model(model =>

            {

                var id = Model.PrimaryKey[0].ColumnName;

                model.Id(id);

                foreach (System.Data.DataColumn column in Model.Columns)

                {

                    var field = model.Field(column.ColumnName, column.DataType);

                    if (column.ColumnName == id)

                    {

                        field.Editable(false);

                    }

                   

                }               

            })

        .Aggregates(a=>

        {

            a.Add("UnitPrice", typeof(decimal?)).Sum();

        })

        .Read(read => read.Action("Read", "Home"))

        //.Update(update => update.Action("Update", "Home"))

    )

)

We are done!