Ajax Binding to kendo MVC grid of Stored procedure returning unknown multiple resultset

1 posts, 0 answers
  1. Aarti
    Aarti  avatar
    20 posts
    Member since:
    Mar 2014

    Posted 14 Apr 2014 Link to this post

    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 srting 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);

            }


Back to Top