I have data returned from SQL stored procedure in the following format. The reason is that new columns can be added to table at a later stage and that also needs to be displayed on the grid.
public class DynamicCars
{
public List<string> ColumnList { get; set; }
public List<List<string>> ColumnValues { get; set; }
}
ColumnList contains the title of column headers. Ex : {'Id', 'Name'}
ColumnValues contain the values as List<List<String>>. Ex: { {'1','xxx'}, {'2','yyy'} }
I want to use this json data to create a dynamic grid. By dynamic I mean I should be able to use the ColumList to define the columns headers.
Any help in this direction is deeply appreciated. Thanks!
1 Answer, 1 is accepted
I would suggest to check the following demos in our code library for more information about dynamic Grid columns building:
Vladimir Iliev
Telerik
Rank 1
I added my DataTable to an existing model instead of just using the DataTable as the model. If you do not initialize the DataTable when creating the view, you will get five extra columns in your view. At first I just tried to hide them, which worked, but when trying to sort an error will occur in your controller and the grid will go blank.
Hope this helps someone, as it was driving me crazy.
Rank 1
Here is an example of some Json that I am trying to bind the grid to:
{ "Data": [ { "FirstName": "Jason", "LastName": "Vetter2", "NetId": "", "Id": 5, "er1.ChildRenderedSummary1": "OVCR2" } ], "TotalRecords": 1, "FieldTypes": { "FirstName": { "type": "string" }, "LastName": { "type": "string" }, "NetId": { "type": "string" }, "Id": { "type": "numeric" }, "er1.ChildRenderedSummary1": { "type": "string" } }, "FieldMapping": [ { "field": "FirstName", "title": "First Name", "width": 100 }, { "field": "LastName", "title": "Last Name", "width": 100 }, { "field": "NetId", "title": "Net Id", "width": 100 }, { "field": "Id", "title": "Id", "width": 100 }, { "field": "er1.ChildRenderedSummary1", "title": "Department", "width": 100 } ]}Note that the Json contains 2 extra elements above and beyond the Data and Total Records:
FieldTypes and FieldMapping.
The idea is that the json returned can actually define what columns will be present. The type, title and underlying field (as I will be using remote datasource with server paging and sorting).
In my page with the grid, this is what I was hoping I could do:
<script type="text/javascript"> $(document).ready(function () { $("#grid").kendoGrid({ dataSource: { transport: { read: { dataType: "json" } }, schema: { model: { fields: "FieldTypes" }, data: "Data", total: "TotalRecords" }, pageSize: 20, serverPaging: true, serverFiltering: true, serverSorting: false }, height: 600, sortable: true, filterable: true, columnMenu: true, pageable: true, reorderable: true, columns: "FieldMapping" }); });</script>Is this even possible?
Please check the following example which demonstrates how to auto-generate Grid columns and it's dataSource model definition from the data received from the server:
Regards,
Vladimir Iliev
Telerik
Rank 1
Hi Vladimir,
Noticed something when trying this example. Kendo break if the JSON key has a space in its name.
For example, try editing the link you provided and change all the "Name" to "User Name" and kendo throws an error.
Its still valid JSON, so is there a way to get this work?
Thanks,
Rank 1
Also how would one modify the date column.
I just have Month value and would like to display it that way but it parses it to a full date.
example set:
{
"Month": "January 2015",
"AA Meetings": 17,
"Group Meetings": 2,
"No Signature Meetings": 0,
"Other Meetings": 2,
"Sponsor Meetings": 5,
"Support Group Attendance": 1
}
Please check the answers of your questions below:
1) Field names can contain only valid JavaScript identifiers out of the box.
2) You should update the "parse" and "generateModel" functions in order to parse this dates.
Regards,
Vladimir Iliev
Telerik
Rank 1
Hi,
This is a great start for me, I have the auto generatde columns working. However I would like to take this a step further.
I actually need the autogenerated columns to be grouped together in sets of three.
So the same as this but including the multi headers as per this example. There can be many of these groups of columns and the column headers need to change as per the data from the received JSON. Is this possible?
cheers
Basically it's possible, however currently we have no such example which we can provide.
Regards,
Vladimir Iliev
Telerik
Rank 1
Hi,
Some sort of a clue on how to go about it would be useful..!?!
I am on the 30 day trial, if I can't get this working I can't recommend to my managers that this is the right tool to use..
As a starting point you can modify the method where the model is generated to also fill a collection of the columns that will be rendered. Also you can use counter inside that function to get each third column and build the "group" option of the dataSource.
Regards,
Vladimir Iliev
Telerik
Rank 1
Rank 1
It's hard to tell if that is what grouping does as every 'edit this example' button on the grouping page does absolutely nothing when you click run in the Dojo, I have tried this in both IE10 and the latest Chrome so I can't even play around with your existing examples to try and get this working. How hard would it be to create a small example of this??
It seems that you are looking for a way to build multi-column headers for the grid - if this is the case you can check the following demo which shows how to configure the grid correctly:
Also as this thread is out of the original topic, may I kindly ask you to open a new support thread for the DataSource? In this way it is much easier to follow and concentrate on the particular issue which usually leads to its faster resolving.
Regards,
Vladimir Iliev
Telerik
Rank 1
That is correct, as I said in the initial question, I needed dynamic and fixed columns and then to have those columns grouped.
This question was a natural extension to the previous as it built upon that exact code one so seemed a good place to put it.
Anyway, I have managed to do this by myself in the end. Here is the generate columns function I created in case anybody else will find it helpful:
function generateColumns(gridData) { var columns = []; var groupedColumns = []; var columnIndex = 0; var groupedColumnIndex = 0 for (var dataItem in gridData) { var colTitle = dataItem; var colGroupTitle = "" var _loc = dataItem.search("_"); if (_loc > 0) { colTitle = dataItem.substring(_loc + 1, dataItem.length); var colGroupTitle = dataItem.substring(0, _loc); } switch (columnIndex) { case 0: columns.push({ field: dataItem, title: colTitle, width: 150, locked: true, headerAttributes: { style: "text-align: center" }, attributes: { style: "text-align: center" } }); break; case 1: columns.push({ field: dataItem, title: colTitle, width: 400, locked: true, headerAttributes: { style: "text-align: left" }, attributes: { style: "text-align: left" } }); break; case 2: columns.push({ field: dataItem, title: colTitle, width: 100, locked: true, headerAttributes: { style: "text-align: center" }, attributes: { style: "text-align: center" } }); break; default: groupedColumns.push({ field: dataItem, title: colTitle, width: 100, locked: false, headerAttributes: { style: "text-align: center" }, attributes: { style: "text-align: center" } }); if (groupedColumnIndex < 2) { groupedColumnIndex += 1 } else { columns.push({ title: colGroupTitle, columns: groupedColumns, headerAttributes: { style: "text-align: center" }, attributes: { style: "text-align: center" } }); groupedColumns = []; groupedColumnIndex = 0; }; } columnIndex += 1; } return columns;}It can be seen in my Dojo file here: http://dojo.telerik.com/@Briant/aFuMA
Cheers
Rank 2
Iron
Veteran
Iron
I also have a dynamic grid populated from a CSV file and using the header row as the column title and field name.
My solution was to just wrap the fieldname with "[" "]" which allowed the user to use anything for the column titles.
I'm now here trying to get my date format handling working! :)
Rob
function generateColumnsImport(fieldnamedetail) { var columns = []; for (var i = 0, l = fieldnamedetail.length; i < l; i++) { var fieldname = fieldnamedetail[i]; column = { field: "['" + fieldname + "']", title: fieldname }; columns.push(column); } return columns; }
Rank 1
Regards,
Vladimir Iliev
Telerik
Rank 1
cshtml code
---------------------------
@model System.Data.DataTable
<html>
<head>
<link href="~/Content/kendo.common.min.css" rel="stylesheet" />
<link href="~/Content/kendo.default.min.css" rel="stylesheet" />
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/kendo.all.min.js"></script>
<title></title>
</head>
<body>
@(Html.Kendo().Grid<dynamic>()
.Name("gridnew")
.Columns(columns =>
{
foreach (System.Data.DataColumn column in Model.Columns)
{
columns.Bound(column.ColumnName);
}
})
.Sortable()
.Scrollable(scrollable => scrollable.Virtual(true))
.Pageable()
.HtmlAttributes(new { style = "height:430px;" })
.DataSource(dataSource => dataSource
.Ajax()
.PageSize(100)
.Read(read => read.Action("MsgContentDetails_Read", "Home"))
)
)
</body>
</html>
controller code
-----------------------------
using Kendo.Mvc.Extensions;
using Kendo.Mvc.UI;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace kendo_grid_with_web_api_and_ondemand.Controllers
{
public class HomeController : Controller
{
Service1 obj = new Service1();
public ActionResult Index()
{
return View();
}
public ActionResult About()
{
ViewBag.Message = "Your application description page.";
return View();
}
public ActionResult Contact()
{
ViewBag.Message = "Your contact page.";
return View();
}
public ActionResult KendogridLazyLoading()
{
return View();
}
public ActionResult KendoGridDynamicColumnBinding()
{
DataTable messageContents = MessageDetails();
return View(messageContents);
}
public ActionResult ClientDetails_Read([DataSourceRequest]DataSourceRequest request)
{
using (var client = new ClientEntities())
{
IQueryable<ClientMaster> clientdetails = client.ClientMasters;
DataSourceResult result = clientdetails.ToDataSourceResult(request);
return Json(result,JsonRequestBehavior.AllowGet);
}
}
public ActionResult ClientDetailswithWcf_Read([DataSourceRequest]DataSourceRequest request)
{
DataSourceResult result = obj.GetClientDetails().ToDataSourceResult(request);
return Json(result,JsonRequestBehavior.AllowGet);
}
public ActionResult MsgDetails_Read([DataSourceRequest]DataSourceRequest request)
{
using (var client = new WC3OnlineEntities())
{
IQueryable<tblCVMsgContent> msgcontents = client.tblCVMsgContents;
DataSourceResult result = msgcontents.ToDataSourceResult(request);
return Json(result, JsonRequestBehavior.AllowGet);
}
}
public ActionResult MsgContentDetails_Read([DataSourceRequest]DataSourceRequest request)
{
DataTable messageContents = MessageDetails();
return Json(messageContents.ToDataSourceResult(request), JsonRequestBehavior.AllowGet);
}
public DataTable MessageDetails()
{
var connection = ConfigurationManager.ConnectionStrings["WC3Entities"].ConnectionString;
using (var dataAdapter = new SqlDataAdapter("SELECT MsgNum,MsgDesc,MsgFileNm from tblCVMsgContent", connection))
{
var dataTable = new DataTable();
dataAdapter.Fill(dataTable);
dataAdapter.FillSchema(dataTable, SchemaType.Mapped);
return dataTable;
}
}
}
}
Here MessageDetails() method two times getting called. I want everything should be in generic. Means I want to show all the columns and properties from db. It should be more dynamic and generic. Could you please help me?​​
Please note that as a general practice it is accepted to ask different questions in separate support threads. In this way it is much easier to follow and concentrate on the particular issue which usually leads to its faster resolving. That why I would ask you again to open a new support thread (or forum post) and elaborate more on the exact scenario that you have.
Regards,
Vladimir Iliev
Telerik
Rank 1
hello,
we have a basic webform ​application, we don't use web service and mvc and we need a kendo ui Grid. is there is a way for having Kendo Grid in web form without using web service or we need web servie?
I wright this code in front end:
$(document).ready(function () {
debugger
var people = rest();
$("#grid").kendoGrid({
dataSource:
{
data: people,
pageSize: 25
},
height: 550,
groupable: true,
scrollable: true,
sortable: true,
filterable: {
extra: false,
operators: {
string: {
startswith: "Starts with",
eq: "Is equal to",
neq: "Is not equal to"
}
}
},
pageable: {
input: true,
numeric: false
},
columns: [{ title: "First Name", field: "CompanyName" },
{ title: "Last Name", field: "ContactTitle" },
{ title: "Email", field: "ContactName" }]
});
});
function rest() {
debugger
alert(1);
$.ajax({
type: 'POST',
url: "UIkundo.aspx/GetEvents",
dataType: 'json',
contentType: 'application/json; charset=utf-8',
serverPaging: true,
success: function (msg) {
debugger
alert(2);
return msg.d;
}
});
}
and for back end I write this function:
[WebMethod(EnableSession = true)]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public static string GetEvents()
{
//Insert Date Into Session Table , string p_quantity, string p_po_detail_price, string p_received_date
try
{
DataTable dt = new DataTable();
DataRow _row;
dt.Columns.Add(new DataColumn("CompanyName", typeof(string)));
dt.Columns.Add(new DataColumn("ContactTitle", typeof(string)));
dt.Columns.Add(new DataColumn("ContactName", typeof(string)));
_row = dt.NewRow();
//add values to each rows
_row["CompanyName"] = "test";
_row["ContactTitle"] = "test";
_row["ContactName"] = "test";
dt.Rows.Add(_row);
_row = dt.NewRow();
//add values to each rows
_row["CompanyName"] = "test1";
_row["ContactTitle"] = "test1";
_row["ContactName"] = "test1";
dt.Rows.Add(_row);
_row = dt.NewRow();
//add values to each rows
_row["CompanyName"] = "test2";
_row["ContactTitle"] = "test2";
_row["ContactName"] = "test2";
dt.Rows.Add(_row);
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<string, object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
return serializer.Serialize(rows);
}
catch (Exception ex)
{
throw ex;
}
}
he return values, but doesn't display this value in kendo Grid.
can any one help me please?
I need this as soon as possible
thanks a lot and best regards​
Basically using a web service on the server side is not required - you can use web method or load the data from local array on the client side.
After inspecting the provided code it appears that you are using custom Ajax request to load the data from the server side - in this case I would suggest to move the jQuery "ajax" method options to the dataSource "transport.read" option of the Grid to load the data correctly.
If you still experience difficulties loading the Grid with data after the above change, please open a new support thicket or forum post and provide runable example where the issue is reproduced?
Regards,
Vladimir Iliev
Telerik