I want to implement server side paging/sorting/etc using [DataSourceRequest] DataSourceRequest request
and ToDataSourceResult
我想使用[DataSourceRequest] DataSourceRequest请求和ToDataSourceResult实现服务器端分页/排序/等
I've done this hundreds of times using LINQ and EF, however, this current project requires us to utilized stored procedures, called by context.Database.SqlQuery
我已经使用LINQ和EF完成了数百次,但是,这个当前项目要求我们使用由context.Database.SqlQuery调用的存储过程。
This works fine, except in the case where I need to pass parameters into the stored procedure as well, which results in the error:
这工作正常,除了我需要将参数传递到存储过程的情况,这导致错误:
The SqlParameter is already contained by another SqlParameterCollection.
SqlParameter已包含在另一个SqlParameterCollection中。
I assume the reason for this is that Kendo's ToDataSourceResult
is also trying to attach SQL parameters to the data
object, which C# doesn't like.
我假设这是因为Kendo的ToDataSourceResult也试图将SQL参数附加到C#不喜欢的数据对象上。
Some solutions I saw online were to call .ToList()
, which does work, however, it sort of defeats to purpose, as it will still query all results.
我在网上看到的一些解决方案是调用.ToList(),它确实有效,但它会失败,因为它仍会查询所有结果。
var data = context.Database.SqlQuery<TicketVm>("usp_FleetRentedEquipments @analyst",
new SqlParameter("@analyst", analyst));
return data.ToDataSourceResult(request);
1 个解决方案
#1
0
I've just done it like this (note: I'm using Dapper and EF)
我刚刚这样做了(注意:我正在使用Dapper和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
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
}
#1
0
I've just done it like this (note: I'm using Dapper and EF)
我刚刚这样做了(注意:我正在使用Dapper和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
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
}