linq-to-sql - 从 Telerik 扩展网格获取分页、过滤并传递给存储过程

Question

我想使用 MVC 网格过滤但不想使用 linq。当前示例使用 linq 进行过滤。但是，我想将过滤选项传递给存储过程。你有例子吗？

我看过 Telerik 扩展网格的示例，在该示例中， Controller 在使用 GridActionAttribute 和 GridModel 从数据库接收到所有记录后过滤记录。

[GridAction]
    public ActionResult _Paging()
    {
        return View(new GridModel<Order>
        {
            Data = GetOrders()
        });
    }

我想要这个过滤器，在执行数据库查询之前分页信息。这样我就只会从数据库中获取页面大小的记录。不是整个数据库记录来过滤它(使用存储过程)。另外，从数据库 LINQ 或 ENTLIB DAL 获取记录的最佳做法是什么。

Answer 1

我找到了解决方案。这是我的助手类，它为我构建过滤器查询。

using Telerik.Web.Mvc;

/// <summary>
/// TODO: Update summary.
/// </summary>
public class GridBinder
{
    public int PageNumber
    {
        get { return _pageNumber; }
        set { _pageNumber = value; }
    }

    public int PageSize
    {
        get { return _pageSize; }
        set { _pageSize = value; }
    }

    public int RecordCount { get; set; }

    public SortInfo SortInfo
    {
        get { return _sortInfo; }
        set { _sortInfo = value; }
    }

    private readonly GridCommand _command;
    private int _pageNumber = 1;
    private int _pageSize = 10;
    private SortInfo _sortInfo = new SortInfo() { Direction = SortDirection.Asc, Member = string.Empty };

    public GridBinder(GridCommand command)
    {
        this._command = command;
        GetSortDescriptor();
    }

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

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

        return filters;
    }

    private static string ApplyFilter(IFilterDescriptor filter)
    {
        var filters = string.Empty;
        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
}

我只是说这个方法对我来说效果很好。我没有测试日期时间列。