paging problems when using stored procedure

7 posts, 0 answers
  1. Sheldon
    Sheldon avatar
    12 posts
    Member since:
    Oct 2013

    Posted 10 Jul 2014 Link to this post

    I am not sure if this is specifically related to stored procedure, but at this point it the only thing I can think of.   I am still pretty green when it comes to Kendo, so perhaps I am doing something silly.

    The scenario is pretty simple.

    I have a stored procedure that retrieves product information.   This result set will never be that big, so I pass the data in the model to load the grid initially.

    Once loaded, I want t use Ajax to manage the grid.   I add a read method called "Get" which should return a Json result converted into the form of the original request using the .ToDataSourceResult .  The Get method calls the same stored procedure that my Index method did to retrieve data.

    The grid initially loads fine, and when I click on a page number the "Get" method is fired as expected

    What happens next is that the grid disappears and is replaced with a text represenation of the Json results from the Get method.

    When I trace the code I also note that the request object in the Get method has a count of 0 which makes me suspcious.

    Here is my code:

    CONTROLLER:

    public ActionResult Index()
    {
        var results = db.GetLanaugeProductInfo("MX", 11660).ToList();

        return View(results);
    }

    public JsonResult Get([DataSourceRequest]DataSourceRequest request)
    {
         var results = db.GetLanaugeProductInfo("MX", 11660).ToList();
        
         return this.Json(results.ToDataSourceResult(request),JsonRequestBehavior.AllowGet);
    }

    VIEW

    @model IEnumerable<NewGlobalProductCatalogue.Models.usp_IPCGetProductInfo_Result>
    @{
    ViewBag.Title = "Index";
    }
    <h2>Index</h2>
    @{string lang = "EN";}
    @(Html.Kendo().Grid(Model)
          .Name("ProductInfo_" + lang)
          .Columns(columns =>
           {
                columns.Bound(c => c.ColumnName);
                columns.Bound(c => c.FieldName);
                columns.Bound(c => c.MasterDataValue);
                columns.Command(command => { command.Edit(); });
           })
           .DataSource(d => d
    .       Ajax()
           .Read(r => r.Action("Get", "Product"))
           .Model(m =>
           {
                 m.Id(p => p.FieldID);
                 m.Field(p => p.ColumnName).Editable(false);
                 m.Field(p => p.FieldName).Editable(false);
                 m.Field(p => p.MasterDataValue).Editable(true);
            })
           .Update(u => u.Action("Update", "Product"))
          )
         .Pageable()
         .Editable(e => e.Mode(GridEditMode.InLine))
    )


    I've attached a couple of screen shots.   The first shows the grid after it is first loaded.  The second shows the raw data I get back when I click on page 2.

    Hopefully someone will be able to tell me what is going on here, and point me in the right direction.

    Many thanks.
  2. Sheldon
    Sheldon avatar
    12 posts
    Member since:
    Oct 2013

    Posted 11 Jul 2014 in reply to Sheldon Link to this post

    Never mind.   Solved it myself.

    Turns out that when I reviewed the bundles for MVC5 that are required, I wasn't loading all the correct ones.

    I didn't have 

    aspnetmvc.min.js or
    kendo.bootstap.min.css



  3. Kiril Nikolov
    Admin
    Kiril Nikolov avatar
    2596 posts

    Posted 14 Jul 2014 Link to this post

    Hi Sheldon,

    I am happy to hear that the problem is resolved.

    In case you have any further questions, please do not hesitate to contact us.

    Regards,
    Kiril Nikolov
    Telerik
     
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
     
  4. Shashank
    Shashank avatar
    4 posts
    Member since:
    Dec 2016

    Posted 06 Jul 2017 in reply to Kiril Nikolov Link to this post

    Hi Kiril,

    I have 1 Query regarding above post : 

    I have +2 lakh records in Database and i am pulling all of them using Stored Proc and then applying

    .ToDataSourceResult(request)

    which is by default doing filtering, sorting, paging etc... and working great.

     

    but I don't want to pull and impose heavy load on database instead I want it limited to 50 records but in that case i can apply filtering (startswith, contains, isnull etc...) with those only 50 records, that's what i don't want.

    I want filtering to work with all those +2 lakh records and then return 50 per page items

    can you guide some help??

    --Thanks

  5. Stefan
    Admin
    Stefan avatar
    2913 posts

    Posted 10 Jul 2017 Link to this post

    Hello Shashank,

    This is expected because when all of the data is not available the operations made by the ToDataSourceResult method will be made only on the retrieved data.

    In this scenario, I can suggest manually filtering the data by making a query to the database only for the specific items based on the filter criteria.

    Regards,
    Stefan
    Progress Telerik
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  6. Shashank
    Shashank avatar
    4 posts
    Member since:
    Dec 2016

    Posted 10 Jul 2017 in reply to Stefan Link to this post

    Thanks Stefan...

    You got my problem, can you guide me how I beak down this

    [Kendo.Mvc.UI.DataSourceRequest request] datasourcerequest into some thing like this as output to pass it as a string formatted string into database to stored procedure.

    EX: '(' colname1 'startwith' colvalue1 and/or colname1 'endswith' colvalue2 ')' and

    '(' colname2 'startwith' colvalue1 and/or colname2 'endswith' colvalue2 ')' and so on....

     

    I basically want 3 things separately formatting I will do my self:

    1. colname,

    2.colvalue,

    3.operator for 1,2 along with logical operator and repeat point 1,2,3 and so on...

     

    Much needed help not getting it anywhere on net specially for composite filter with logical one,

     

    So far what I have done :

     

    Extensible Method:

    //For Kendo Filtering composite and single filter Value
    public static List<Kendo.Mvc.FilterDescriptor> ToFilterDescriptor(this IList<Kendo.Mvc.IFilterDescriptor> filters)
    {
    var result = new List<Kendo.Mvc.FilterDescriptor>();
    if (filters.Any())
    {
    foreach (var filter in filters)
    {
    var descriptor = filter as Kendo.Mvc.FilterDescriptor;
    if (descriptor != null)
    {
    result.Add(descriptor);
    }
    else
    {
    var compositeFilterDescriptor = filter as Kendo.Mvc.CompositeFilterDescriptor;
    if (compositeFilterDescriptor != null)
    {
    result.AddRange(compositeFilterDescriptor.FilterDescriptors.ToFilterDescriptor());
    }
    }
    }
    }
    return result;
    }

     

    public ActionResult Read([DataSourceRequest] DataSourceRequest request, string filterType = "Full")
            {
    //filteration string formatting
    if (request.Filters.Any())
                    {
                        foreach (var fdc in request.Filters.ToFilterDescriptor())
                        {
                            _filtercol = fdc.Member.ToString();
                            _filterdata = fdc.ConvertedValue.ToString();
                            _filteroperator = fdc.Operator.ToString();
                            _finalFilterAppend.Append(_filtercol + "||" + _filteroperator + "||" + _filterdata + "#");
                        }
                    }
    //pass this to Stored Proc
    resultSet = getStoredProcData(_finalFilterAppend.ToString().TrimEnd('#'));
    var result = new DataSourceResult()
                {
                    Data = resultSet,
                    Total = TotalRe
                };
    return json;
    }

     

    this only works for single line command with 'and' operator only,

    I guess you got my point now..

     

    Kindly help

    -- Thanks

     

     

     

     

  7. Stefan
    Admin
    Stefan avatar
    2913 posts

    Posted 12 Jul 2017 Link to this post

    Hello Shashank,

    Thank you for the additional information.

    As the scenario is custom I can suggest a few steps to help testing where the issue is coming from.

    1) Just before making a request to the database, check the build expression which is stored in _finalFilterAppend. Observe if the build expression is valid and in the expected format.

    2) If the expression is not valid, check which part of its is not in the correct place and try if it can be placed on the correct one by changing the logic in the foreach loop.

    3) If the expression is valid, but still the result is not correct, please try passing the same expression as a hard coded string to check if this will make a difference.

    If all of this does not help determine the issue, please provide an example of how one expression should look like with dummy column names and filter values.

    Regards,
    Stefan
    Progress Telerik
    Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Back to Top