Get Paging,Filtering from Telerik extension grid and pass to stored procedure

Question

I would like to use the MVC grid filtering but don't want to use linq. Current examples use linq to do the filtering. However, I want to pass the filtering options to a stored procedure. Do you have an example?

I have seen example of Telerik extension grid where inside controller it filter records after receiving all the records from database using GridActionAttribute and GridModel.

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

I want this filter, paging information before executing database query. so that i will get only pagesize records from database. not whole database records to filter it(using storedproc). also, what is the best practice to get records from database LINQ or ENTLIB DAL.

Answer 1

I got the solution for this. here is my helper class which build filter query for me.

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
}

And i just call this method's working fine for me. I have not tested for datetime column.

Answer 2

Consider using Custom Ajax binding. Check this part of the documentation and this demo to get the idea.