Kendo MVC Grid Ajax custom binding to dynamic resultset

[Aarti]

Hi,

I have a stored procedure which returns  2 result sets. First result-set is collection of Columns ,data types, column order, Is column sortable and is column filterable. 
Second resultset is actual data for the columns which are there in first resultset. So in short my stored procedure returns the metadata for the columns and their values. I dont know in advance what will be my columns list. At code level stored procedure is  returning a dataset with 2 tables inside it.  Now I want to bind the kendo grid with the second table of dataset and first table of dataset will give me information about the columns which are suppose to have sort ,filter criteria. My problem is  i dont know the columns in advance still i execute the stored procedure then how can i use DataSourecRequest object to find out custom sorting and filtering stuff.
e.g. 
private CriteriaDto GetCriteriaDtoValues(DataSourceRequest request)
        {
           var criteriaDto = new DocumentHistoryCriteriaDto { Filters = new Dictionary<string, string>() };
            if (request.PageSize == 0)
            {
                request.PageSize = 20;
                criteriaDto.PageSize = 20;
            }
            else
            {
                criteriaDto.PageSize = request.PageSize;
            }
            if (request.Sorts != null && request.Sorts.Count > 0)
            {
                foreach (SortDescriptor sortDescriptor in request.Sorts)
                {
                    if (sortDescriptor.SortDirection == ListSortDirection.Ascending)
                    {
                        switch (sortDescriptor.Member)
                        {
                            case "ActivityDate": //This is hard coded column just for example but by this time i dont knwo what are the columns there in resultset
                                criteriaDto.SortBy = "ActivityDate";
                                criteriaDto.IsSortAcending = true;
                                currentSortType = criteriaDto.SortBy;
                                break;
                            case "RecordId": ////This is hard coded column just for example but by this time i dont knwo what are the columns there in resultset
                                criteriaDto.SortBy = "RecordId";
                                criteriaDto.IsSortAcending = true;
                                currentSortType = criteriaDto.SortBy;
                                break;                            
                        }
                    }
                    else
                    {
                        switch (sortDescriptor.Member)
                        {
                            case "ActivityDate": //This is hard coded column just for example but by this time i dont knwo what are the columns there in resultset
                                criteriaDto.SortBy = "ActivityDate";
                                criteriaDto.IsSortAcending = false;
                                currentSortType = criteriaDto.SortBy;
                                break;
                            case "RecordId": //This is hard coded column just for example but by this time i dont knwo what are the columns there in resultset
                                criteriaDto.SortBy = "RecordId";
                                criteriaDto.IsSortAcending = false;
                                currentSortType = criteriaDto.SortBy;
                                break;                            
                        }

                    }
                }
            }
            else
            {
                criteriaDto.SortBy = "ActivityDate"; //This is hard coded column just for example but by this time i dont know what are the columns there in resultset
                criteriaDto.IsSortAcending = true;
                currentSortType = criteriaDto.SortBy;
            }
            if (request.Filters != null && request.Filters.Count > 0)
            {
                foreach (FilterDescriptor filterDescriptor in request.Filters)
                {
                    switch (filterDescriptor.Member)
                    {
                        case "RecordId": //This is hard coded column just for example but by this time i dont know what are the columns there in resultset
                            criteriaDto.Filters.Add("LoanNo", filterDescriptor.Value.ToString());
                            break;
                    }

                }
            }
            if (request.Page > 0)
            {
                criteriaDto.PageNumber = request.Page;
            }
            return criteriaDto;

        }  

Also I have written below ajax method which is calling the above code before it calls the Store procedure. Can you provide some exmaple on how I can actually
pass the data set's data table collection to view in MVC  and How can i bind it to grid for 2nd table in dataset and how can i make use of first table
to decide which columns to sort and filter using DataSourceRequest object

 public ActionResult View_ByRecordId_Read([DataSourceRequest] DataSourceRequest request)
{
        
DataSet dsModel;
int  total=0;
int RecordId = 2;
string RecordValue = "0000224374";
 int timeFrame = 50;

 criteriaDto = this.GetCriteriaDtoValues(request);  //So here it calls this method to decide which columns got sort and filer criteria but actually by this time i dint get my procedure called so how can i figure it out which columns are meant to be for sorting and filtering.

dsModel = this._Service.GetDocumentView (userDto.Id,RecordId,RecordValue,timeFrame,criteriaDto,Guid.NewGuid());  //This is calling stored procedure and returning a dataset with 2 tables inside it.

if (dsModel != null && dsModel.Tables.Count > 0)        {
 bool tryParse=int.TryParse(dsModel.Tables[1].Rows[0]["TotalCount"].ToString(),out  total);    }

            var  result = new DataSourceResult() { Data = dsModel.Tables, Total = total };

            return Json(result);

        }

[Aarti]

Hi,

I am able to get stored procedure resultset into list of objects which look like as below.

 public class DocumentViewModel
    {
        public virtual int RowNum { get; set; }
        public virtual int TotalCount { get; set; }
        public virtual int DocumentId { get; set; }
        public virtual Dictionary<Column, string> IDocumentViewDynamicColumnAndValuesList { get; set; } //this is the dictionary of dynmic columns  SO i want my grid to have columns like RowNum,TotalCount,Documentid,all the keys of dictioanry as columns  and documenThumbnail
        public virtual Guid DocumentThumbnail { get; set; }

    }
    public class Column
    {
        public virtual int ColumnOrder { get; set; }
        public virtual string DataType { get; set; }
        public virtual string ColumnName { get; set; }
        public virtual bool IsSortable { get; set; }
        public virtual bool IsFilterable { get; set; }
    }

I have written below controller method to populate above object with the resultset of stored procedure
 private ActionResult GetGridData(DataSourceRequest request)
        {
            int total = 0;
            int RecordId = 2;
            string RecordValue = "0000224374";
            int timeframe = 50;
            Guid ProfileId = Guid.NewGuid();
            string previousSortType = string.Empty;
            List<DocumentViewModel> model = this.DocumentService.GetDocumentView(userDto.Id, RecordId, RecordValue, timeframe, criteriaDto, ProfileId); //This call populates the DocumentViewModel object with all columns and values
            if (model != null && model.Count > 0)
            {
                total = model.FirstOrDefault(m => m.TotalCount != null).TotalCount;
            }
            var result = new DataSourceResult() { Data = model, Total = total };
            return Json(result);
        }

hence I get the Json result of List<DocumentViewModel> But now I dont understand how I can create the kendo grid and bind this result to it using ajax binding because  DocumentViewModel object contains a dicitionary of  dynamic column names and column values

Can someone please reply to my post.  I want to know how can i do ajax custom binding to kendo Grid with dynamic columns whose names and values i dont know till runtime . 

[Alexander Popov]

Hi Aarti,

Binding a Grid to data set with unknown number of columns (and their types) is currently not supported out of the box. I would recommend checking this Code Library project, which demonstrates how this scenario could be handled using DataTables.

Regards,
Alexander Popov
Telerik

 

 

[Aarti]

Hi

Thank you for your response. I am able to bind the grid for dyanmic resultset with the help of Expando object. But now problem is  i cant see the data in grid. I can see only columns. Also when I tried to troubleshoot i found  on the first request of page it never calls ajax action method.. it just calls controller action method which stores the column names in viewbag object . then view uses that viewbag to generate the columns for grid. i have configured kendo grid to call ajax method but it doesn't calls that ajax action method but when i click on pagination it calls the ajax action method but doesn't display any data in grid. i can see the data in json. below is my code.

 public ActionResult View_ByRecordId(int id)  //this is controller action which sends no of columns in viewbag to view
        {
            int total = 0;
            int RecordId = 2;
            string RecordValue = "0000224374";
            int timeframe = 50;
            Guid ProfileId = Guid.NewGuid();        
            var criteriaDto = new IInsightDocumentViewCriteriaDto();//this.GetCriteriaDtoValues();
            criteriaDto.PageNumber = 1;
            criteriaDto.PageSize = 5;
            var model = this.DocumentService.GetDocumentView(userDto.Id, RecordId, RecordValue, timeframe, criteriaDto, ProfileId);
            var myModel = model.FirstOrDefault();
            if (myModel != null)
            {
                if (Session["ColumnShema"] == null)
                    Session["ColumnShema"] = myModel.DocumentViewColumnsList.Keys;
            }
            var colmns = myModel.DocumentViewColumnsList.Keys.ToList();
            ViewBag.ColumnSchema =(colmns); /// Here i get list of columns with their data types
            return View();
        }

below is my view

@model IEnumerable<dynamic>

@{
    ViewBag.Title = "Insight View_ByRecordId";
    Layout = "~/Views/Shared/Layouts/Layout.cshtml";
}


<table id="DocViewGridTable">
    <tr>
        <td style="padding: 10px">
            <b>ID: </b> Some ID
        </td>
        <td><b>FileName:</b> Some FileName</td>
    </tr>
    <tr>
        <td style="padding:10px" colspan="2">
            <div id="GridWrap">
              @(Html.Kendo().Grid(Model)
                          .Name("grid")
                          //.EnableCustomBinding(true)
                          //.BindTo(Model)
                          .Columns(columns =>
                          {
                              columns.Bound("RowNum");
                              columns.Bound("TotalCount");
                              columns.Bound("DocumentId");
                              
                              if (ViewBag.ColumnSchema != null)
                              {
                                  List<Column> columnList = ViewBag.ColumnSchema;
                                  foreach (var col in columnList)
                                  {
                                      columns.Bound(col.ColumnName);
                                  }
                                  columns.Bound("Document Thumbnail");
                              }
                          })
                          .Pageable(page => page.PageSizes(true).Refresh(true))
                          .Sortable()
                          //.Filterable()
                          .DataSource(dataSource => dataSource.Ajax()
                          //.Model(m =>
                          //{
                          //    if (ViewBag.ColumnSchema != null)
                          //    {
                          //        List<Column> columnList = ViewBag.ColumnSchema;
                          //        foreach (var column in columnList)
                          //        {
                          //            m.Field(column.ColumnName, typeof(object));
                          //        }
                          //    }
                          //})
                         .Read(read => read.Action("View_Read", "View"))
                         .PageSize(5)
                        // .ServerOperation(true)
                          )
                         .Scrollable(scrollable => scrollable.Height("auto"))                )
                </div>
        </td>
    </tr>
</table>


 And below is my ajax action method for custom binding which is never been called on first request. also it gets called only once when i click on pageNumber drop down on grid pager but it doesnt show any data in grid also for second click it doesnt get called.
 Can you please help out me here 

public ActionResult View_Read([DataSourceRequest] DataSourceRequest request)
        {
            int total = 0;
            int RecordId = 2;
            string RecordValue = "0000224374";
            int timeframe = 50;
            Guid ProfileId = Guid.NewGuid();
            string previousSortType = string.Empty;
             IInsightDocumentViewCriteriaDto criteriaDto = this.GetCriteriaDtoValues(request);
            var model = this.DocumentService.GetDocumentView(userDto.Id, RecordId, RecordValue, timeframe, criteriaDto, ProfileId);
            //List<DynamicObject> lstDynamicObjects = new List<DynamicObject>();
            var lstDynamicObjects = new List<System.Dynamic.ExpandoObject>();
            foreach (var DVM in model)
            {
                Dictionary<string, object> propertyDictionary = new Dictionary<string, object>();
                propertyDictionary.Add("RowNum", DVM.RowNum);
                propertyDictionary.Add("TotalCount", DVM.TotalCount);
                propertyDictionary.Add("DocumentId", DVM.DocumentId);
                foreach (var columnDict in DVM.InsightDocumentViewColumnsList)
                {
                    object value = columnDict.Value;
                    switch (columnDict.Key.DataType)
                    {
                        case "datetime2":
                            DateTime parsedDate;
                            bool myValue = DateTime.TryParseExact(value.ToString(), "MM-dd-yyyy", null,
                                DateTimeStyles.None, out parsedDate);
                            value = parsedDate;
                            break;
                    }
                   propertyDictionary.Add(columnDict.Key.ColumnName, value);
                }
                
                propertyDictionary.Add("DocumentThumbnail", DVM.DocumentThumbnail);
                //var dyn = GetDynamicObject(propertyDictionary);
                var dyn=new ExpandoObject();
                dyn.AddRange(propertyDictionary);
                lstDynamicObjects.Add(dyn);
            }
            var mymodel = model.FirstOrDefault();
            total =Convert.ToInt32(mymodel.TotalCount);
            var result = new DataSourceResult() { Data = lstDynamicObjects, Total = total };
            var serializer = new JavaScriptSerializer();
            serializer.RegisterConverters(new JavaScriptConverter[] { new ExpandoJSONConverter() });
            var json = serializer.Serialize(result);

            return new MyJsonResult(json);      
            //return Json(result);
        }

[Aarti]

Hi

I am able to achieve it with dynamic columns. Now i can see data in a grid.