paging problems when using stored procedure

[Sheldon]

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.

[Sheldon]

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

[Kiril Nikolov]

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

 

 

[Shashank]

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

[Stefan]

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

[Shashank]

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

 

 

 

 

[Stefan]

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