Server-side kendo swapping with stored procedures

Question

I want to implement server side swapping / sorting / etc with [DataSourceRequest] DataSourceRequest requestandToDataSourceResult

I have done this hundreds of times using LINQ and EF, however this current project requires us to use stored procedures called context.Database.SqlQuery

This works fine, except when I need to also pass parameters to a stored procedure, which leads to an error:

SqlParameter is already contained by another SqlParameterCollection.

I guess the reason for this is that Kendo is ToDataSourceResultalso trying to bind SQL parameters to an object datathat C # doesn't like.

Some of the solutions that I saw on the Internet should have called .ToList()what really works, but it seems like a hit on the target, because it will still ask for all the results.

var data = context.Database.SqlQuery<TicketVm>("usp_FleetRentedEquipments @analyst",
                                               new SqlParameter("@analyst", analyst));
return data.ToDataSourceResult(request);

Answer 1

I just did this (note: I use Dapper and EF)

var gridBinder = new GridBinder(request);

var filters = gridBinder.GetFilterDescriptor();

var sorting = gridBinder.SortInfo.Member.HasValue() ? string.Format("{0} {1}", gridBinder.SortInfo.Member, gridBinder.SortInfo.Direction) : "";

var p = new DynamicParameters();
p.Add("@Page", gridBinder.PageNumber, DbType.Int32, ParameterDirection.Input);
p.Add("@PageSize", gridBinder.PageSize, DbType.Int32, ParameterDirection.Input);
p.Add("@Filter", filters);
p.Add("@SortOrder", sorting);
p.Add("@TotalRowCount", dbType: DbType.Int32, direction: ParameterDirection.Output);

var data = _db.Connection().Query<IndexVm>("dbo.People_GetAll", p, commandType: System.Data.CommandType.StoredProcedure, transaction: _db.Database.CurrentTransaction.GetDbTransaction());

gridBinder.RecordCount = data.FirstOrDefault().TotalRowCount;

return Json(new DataSourceResult
{
    Total = gridBinder.RecordCount,
    Data = data
});

Gridbinder

public class GridBinder
{
    public int PageNumber { get; set; } = 1;

    public int PageSize { get; set; } = 10;

    public int RecordCount { get; set; }

    public SortInfo SortInfo { get; set; } = new SortInfo() { Direction = SortDirection.Asc, Member = string.Empty };

    private readonly DataSourceRequest _command;

    public GridBinder(DataSourceRequest command)
    {
        _command = command;
        PageNumber = command.Page;
        PageSize = command.PageSize;
        GetSortDescriptor();
    }

    private void GetSortDescriptor()
    {
        foreach (SortDescriptor descriptor in _command.Sorts)
        {
            SortInfo.Member = descriptor.Member;
            SortInfo.Direction = descriptor.SortDirection == ListSortDirection.Ascending ? SortDirection.Asc : SortDirection.Desc;
        }
    }

    public string GetFilterDescriptor()
    {
        string filters = string.Empty;
        foreach (IFilterDescriptor filter in _command.Filters)
        {
            filters += ApplyFilter(filter);
        }

        return filters;
    }

    private static string ApplyFilter(IFilterDescriptor filter)
    {
        var filters = "";
        if (filter is CompositeFilterDescriptor)
        {
            filters += "(";
            var compositeFilterDescriptor = (CompositeFilterDescriptor)filter;
            foreach (IFilterDescriptor childFilter in compositeFilterDescriptor.FilterDescriptors)
            {
                filters += ApplyFilter(childFilter);
                filters += " " + compositeFilterDescriptor.LogicalOperator.ToString() + " ";
            }
        }
        else
        {
            string filterDescriptor = "{0} {1} {2}";
            var descriptor = (FilterDescriptor)filter;
            if (descriptor.Operator == FilterOperator.StartsWith)
            {
                filterDescriptor = string.Format(filterDescriptor, descriptor.Member, "LIKE", "'" + descriptor.Value + "%'");
            }
            else if (descriptor.Operator == FilterOperator.EndsWith)
            {
                filterDescriptor = string.Format(filterDescriptor, descriptor.Member, "LIKE", "'%" + descriptor.Value + "'");
            }
            else if (descriptor.Operator == FilterOperator.Contains)
            {
                filterDescriptor = string.Format(filterDescriptor, descriptor.Member, "LIKE", "'%" + descriptor.Value + "%'");
            }
            else if (descriptor.Operator == FilterOperator.DoesNotContain)
            {
                filterDescriptor = string.Format(filterDescriptor, descriptor.Member, "NOT LIKE", "'%" + descriptor.Value + "%'");
            }
            else if (descriptor.Operator == FilterOperator.IsEqualTo)
            {
                filterDescriptor = string.Format(filterDescriptor, descriptor.Member, "=", "'" + descriptor.Value + "'");
            }
            else if (descriptor.Operator == FilterOperator.IsNotEqualTo)
            {
                filterDescriptor = string.Format(filterDescriptor, descriptor.Member, "<>", "'" + descriptor.Value + "'");
            }
            else if (descriptor.Operator == FilterOperator.IsGreaterThan)
            {
                filterDescriptor = string.Format(filterDescriptor, descriptor.Member, ">", "'" + descriptor.Value + "'");
            }
            else if (descriptor.Operator == FilterOperator.IsGreaterThanOrEqualTo)
            {
                filterDescriptor = string.Format(filterDescriptor, descriptor.Member, ">=", "'" + descriptor.Value + "'");
            }
            else if (descriptor.Operator == FilterOperator.IsLessThan)
            {
                filterDescriptor = string.Format(filterDescriptor, descriptor.Member, "<", "'" + descriptor.Value + "'");
            }
            else if (descriptor.Operator == FilterOperator.IsLessThanOrEqualTo)
            {
                filterDescriptor = string.Format(filterDescriptor, descriptor.Member, "<=", "'" + descriptor.Value + "'");
            }

            filters = filterDescriptor;
        }

        filters = filters.EndsWith("And ") == true ? filters.Substring(0, filters.Length - 4) + ")" : filters;
        filters = filters.EndsWith("Or ") == true ? filters.Substring(0, filters.Length - 4) + ")" : filters;

        return filters;
    }
}
public class SortInfo
{
    public string Member { get; set; }
    public SortDirection Direction { get; set; }
}

public enum SortDirection
{
    Asc, Desc
}

Answer 2

Inside the ToDataSourceResult method, the query is used at least twice - once when calling Count () and once to retrieve data. This causes an error when using the SQL parameter set twice. Most likely you can only do this:

var data = context.Database.SqlQuery<TicketVm>($"usp_FleetRentedEquipments {analyst}");
return data.ToDataSourceResult(request);

And be very careful, the “analyst” is not an SQL injection.