jqgrid使用asp.net webmethod和json处理排序、分页、搜索和LINQ——但需要动态操作符

THIS WORKS! .. but still needs one more thing...

这个工作!. .但还有一件事……

Okay, so this is both a "comment" and question. First, is the working example that may help others in search of a asp.net webmethod / jqGrid approach. The code below completely works for sending/receiving JSON parameters from and to jqGrid in order to have correct paging, sorting, filtering (with single search only) utilizing LINQ.. it uses pieces from here and there...

这既是一个评论又是一个问题。首先，是可以帮助其他人搜索asp.net webmethod / jqGrid方法的工作示例。下面的代码完全用于从jqGrid和jqGrid发送/接收JSON参数，以便使用LINQ进行正确的分页、排序、过滤(仅使用一次搜索)。它到处都是碎片……

Second, is my question: Has anyone determined an appropriate method for accounting for dynamic operators being sent to the codebehind? Since the client can potentially send "eq" (equal), "cn" (contains) "gt" (greater than), I need a better way of dynamically generating a whereclause that isn't just limited to me building a whereclause string with "=" or "<>", but rather can encompass that along with Dynamic Linq's ability to utilize .Contains or .EndsWith, etc.

第二，我的问题是:是否有人确定了一种合适的方法来计算发送到代码背后的动态操作符?由于客户端可以潜在地发送“eq”(等)、“cn”(包含)“gt”(大于)，我需要一种更好的方法来动态生成where，这不仅限于我使用“=”或“<>”构建where lause字符串，而且还可以包含动态Linq使用. contains或. endswith等功能。

I may need some sort of predicate builder function ..

我可能需要某种谓词构造函数。

code that handles this as of now (which works, but is limited):

现在处理这个问题的代码(工作，但是是有限的):

if (isSearch) {
    searchOper = getOperator(searchOper); // need to associate correct operator to value sent from jqGrid
    string whereClause = String.Format("{0} {1} {2}", searchField, searchOper, "@" + searchField);

    //--- associate value to field parameter
    Dictionary<string, object> param = new Dictionary<string, object>();
    param.Add("@" + searchField, searchString);

    query = query.Where(whereClause, new object[1] { param });
}

On with the show.........

的显示.........

==================================================

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

First, THE JAVASCRIPT

<script type="text/javascript">
$(document).ready(function() {

    var grid = $("#grid");

    $("#grid").jqGrid({
        // setup custom parameter names to pass to server
        prmNames: { 
            search: "isSearch", 
            nd: null, 
            rows: "numRows", 
            page: "page", 
            sort: "sortField", 
            order: "sortOrder"
        },
        // add by default to avoid webmethod parameter conflicts
        postData: { searchString: '', searchField: '', searchOper: '' },
        // setup ajax call to webmethod
        datatype: function(postdata) {    
            $(".loading").show(); // make sure we can see loader text
            $.ajax({
                url: 'PageName.aspx/getGridData',  
                type: "POST",  
                contentType: "application/json; charset=utf-8",  
                data: JSON.stringify(postdata),
                dataType: "json",
                success: function(data, st) {
                    if (st == "success") {
                        var grid = $("#grid")[0];
                        grid.addJSONData(JSON.parse(data.d));
                    }
                },
                error: function() {
                    alert("Error with AJAX callback");
                }
            }); 
        },
        // this is what jqGrid is looking for in json callback
        jsonReader: {  
            root: "rows",
            page: "page",
            total: "totalpages",
            records: "totalrecords",
            cell: "cell",
            id: "id", //index of the column with the PK in it 
            userdata: "userdata",
            repeatitems: true
        },
        colNames: ['Id', 'First Name', 'Last Name'],   
        colModel: [
            { name: 'id', index: 'id', width: 55, search: false },
            { name: 'fname', index: 'fname', width: 200, searchoptions: { sopt: ['eq', 'ne', 'cn']} },
            { name: 'lname', index: 'lname', width: 200, searchoptions: { sopt: ['eq', 'ne', 'cn']} }
        ],  
        rowNum: 10,  
        rowList: [10, 20, 30],
        pager: jQuery("#pager"),
        sortname: "fname",   
        sortorder: "asc",
        viewrecords: true,
        caption: "Grid Title Here",
    gridComplete: function() {
        $(".loading").hide();
    }
    }).jqGrid('navGrid', '#pager', { edit: false, add: false, del: false },
    {}, // default settings for edit
    {}, // add
    {}, // delete
    { closeOnEscape: true, closeAfterSearch: true}, //search
    {}
)
});
</script>

==================================================

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

Second, THE C# WEBMETHOD

[WebMethod]
public static string getGridData(int? numRows, int? page, string sortField, string sortOrder, bool isSearch, string searchField, string searchString, string searchOper) {
    string result = null;

    MyDataContext db = null;
    try {
        //--- retrieve the data
        db = new MyDataContext("my connection string path");  
        var query = from u in db.TBL_USERs
                    select new User {
                        id = u.REF_ID, 
                        lname = u.LAST_NAME, 
                        fname = u.FIRST_NAME
                    };

        //--- determine if this is a search filter
        if (isSearch) {
            searchOper = getOperator(searchOper); // need to associate correct operator to value sent from jqGrid
            string whereClause = String.Format("{0} {1} {2}", searchField, searchOper, "@" + searchField);

            //--- associate value to field parameter
            Dictionary<string, object> param = new Dictionary<string, object>();
            param.Add("@" + searchField, searchString);

            query = query.Where(whereClause, new object[1] { param });
        }

        //--- setup calculations
        int pageIndex = page ?? 1; //--- current page
        int pageSize = numRows ?? 10; //--- number of rows to show per page
        int totalRecords = query.Count(); //--- number of total items from query
        int totalPages = (int)Math.Ceiling((decimal)totalRecords / (decimal)pageSize); //--- number of pages

        //--- filter dataset for paging and sorting
        IQueryable<User> orderedRecords = query.OrderBy(sortfield);
        IEnumerable<User> sortedRecords = orderedRecords.ToList();
        if (sortorder == "desc") sortedRecords= sortedRecords.Reverse();
        sortedRecords = sortedRecords
          .Skip((pageIndex - 1) * pageSize) //--- page the data
          .Take(pageSize);

        //--- format json
        var jsonData = new {
            totalpages = totalPages, //--- number of pages
            page = pageIndex, //--- current page
            totalrecords = totalRecords, //--- total items
            rows = (
                from row in sortedRecords
                select new {
                    i = row.id,
                    cell = new string[] {
                        row.id.ToString(), row.fname, row.lname 
                    }
                }
           ).ToArray()
        };

        result = Newtonsoft.Json.JsonConvert.SerializeObject(jsonData);

    } catch (Exception ex) {
        Debug.WriteLine(ex);
    } finally {
        if (db != null) db.Dispose();
    }

    return result;
}

/* === User Object =========================== */
public class User {
    public int id { get; set; }
    public string lname { get; set; }
    public string fname { get; set; }
}

==================================================

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

Third, NECESSITIES

1. In order to have dynamic OrderBy clauses in the LINQ, I had to pull in a class to my AppCode folder called 'Dynamic.cs'. You can retrieve the file from downloading here. You will find the file in the "DynamicQuery" folder. That file will give you the ability to utilized dynamic ORDERBY clause since we don't know what column we're filtering by except on the initial load.

   为了在LINQ中包含动态OrderBy子句，我必须将一个类拉到名为“Dynamic.cs”的AppCode文件夹中。您可以从这里下载文件。您将在“DynamicQuery”文件夹中找到该文件。该文件将使您能够使用dynamic ORDERBY子句，因为除了初始负载外，我们不知道要过滤哪个列。

2. To serialize the JSON back from the C-sharp to the JS, I incorporated the James Newton-King JSON.net DLL found here : http://json.codeplex.com/releases/view/37810. After downloading, there is a "Newtonsoft.Json.Compact.dll" which you can add in your Bin folder as a reference

   为了将JSON从C-sharp序列化回JS，我将这里找到的James Newton-King JSON.net DLL: http://json.codeplex.com/releases/view/37810合并在一起。下载后，有一个“Newtonsoft.Json.Compact”。dll"，你可以添加到你的Bin文件夹作为参考

3. Here's my USING's block using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Web.UI.WebControls; using System.Web.Services; using System.Linq.Dynamic;

   这是我使用的block使用系统;使用System.Collections;使用System.Collections.Generic;使用来;使用System.Web.UI.WebControls;使用System.Web.Services;使用System.Linq.Dynamic;

4. For the Javascript references, I'm using the following scripts in respective order in case that helps some folks: 1) jquery-1.3.2.min.js ... 2) jquery-ui-1.7.2.custom.min.js ... 3) json.min.js ... 4) i18n/grid.locale-en.js ... 5) jquery.jqGrid.min.js

   对于Javascript引用，我按顺序使用以下脚本，以帮助某些人:1)jquery-1.3.2 min。js……2)jquery-ui-1.7.2.custom.min。js……3)json.min。js……4)i18n / grid.locale-en。js……5)jquery.jqGrid.min.js

5. For the CSS, I'm using jqGrid's necessities as well as the jQuery UI Theme: 1) jquery_theme/jquery-ui-1.7.2.custom.css ... 2) ui.jqgrid.css

   对于CSS，我使用jqGrid的必需品以及jQuery UI主题:1)jquery_theme/ jquery_ui -1.7.2.custom。css……2)ui.jqgrid.css

The key to getting the parameters from the JS to the WebMethod without having to parse an unserialized string on the backend or having to setup some JS logic to switch methods for different numbers of parameters was this block

要将参数从JS中获取到WebMethod，而不需要解析后端上的未序列化字符串，也不需要设置一些JS逻辑来为不同数量的参数切换方法，关键是这个块

postData: { searchString: '', searchField: '', searchOper: '' },

Those parameters will still be set correctly when you actually do a search and then reset to empty when you "reset" or want the grid to not do any filtering

当您实际执行搜索时，这些参数仍将被正确设置，当您“重置”或希望网格不做任何过滤时，这些参数将被重置为空

Hope this helps some others!!!! And thanks if you have time to read and reply regarding the dynamic approach to building the whereclause with operators at runtime

希望这能帮助别人!!!!如果您有时间来阅读和回复关于在运行时使用操作符构建where eclause的动态方法，谢谢

2 个解决方案

#1

Consider this extension method, that converts a string into a MemberExpression:

考虑这个扩展方法，它将字符串转换为MemberExpression:

public static class StringExtensions
{
    public static MemberExpression ToMemberExpression(this string source, ParameterExpression p)
    {
        if (p == null)
            throw new ArgumentNullException("p");

        string[] properties = source.Split('.');

        Expression expression = p;
        Type type = p.Type;

        foreach (var prop in properties)
        {
            var property = type.GetProperty(prop);
            if (property == null)
                throw new ArgumentException("Invalid expression", "source");

            expression = Expression.MakeMemberAccess(expression, property);
            type = property.PropertyType;
        }

        return (MemberExpression)expression;
    }
}

The method below converts the strings that you have into an Lambda Expression, that you can use to filter a Linq query. It is a generic method, with T as the domain entity.

下面的方法将您所拥有的字符串转换为Lambda表达式，您可以使用它来过滤Linq查询。它是一个泛型方法，以T为域实体。

    public virtual Expression<Func<T, bool>> CreateExpression<T>(string searchField, string searchString, string searchOper)
    {
        Expression exp = null;
        var p = Expression.Parameter(typeof(T), "p");

        try
        {
            Expression propertyAccess = searchField.ToExpression(p);

            switch (searchOper)
            {
                case "bw":
                    exp = Expression.Call(propertyAccess, typeof(string).GetMethod("StartsWith", new Type[] { typeof(string) }), Expression.Constant(searchString));
                    break;
                case "cn":
                    exp = Expression.Call(propertyAccess, typeof(string).GetMethod("Contains", new Type[] { typeof(string) }), Expression.Constant(searchString));
                    break;
                case "ew":
                    exp = Expression.Call(propertyAccess, typeof(string).GetMethod("EndsWith", new Type[] { typeof(string) }), Expression.Constant(searchString));
                    break;
                case "gt":
                    exp = Expression.GreaterThan(propertyAccess, Expression.Constant(searchString, propertyAccess.Type));
                    break;
                case "ge":
                    exp = Expression.GreaterThanOrEqual(propertyAccess, Expression.Constant(searchString, propertyAccess.Type));
                    break;
                case "lt":
                    exp = Expression.LessThan(propertyAccess, Expression.Constant(searchString, propertyAccess.Type));
                    break;
                case "le":
                    exp = Expression.LessThanOrEqual(propertyAccess, Expression.Constant(searchString, propertyAccess.Type));
                    break;
                case "eq":
                    exp = Expression.Equal(propertyAccess, Expression.Constant(searchString.ToType(propertyAccess.Type), propertyAccess.Type));
                    break;
                case "ne":
                    exp = Expression.NotEqual(propertyAccess, Expression.Constant(searchString, propertyAccess.Type));
                    break;
                default:
                    return null;
            }

            return (Expression<Func<T, bool>>)Expression.Lambda(exp, p);
        }
        catch
        {
            return null;
        }
    }

So, you can use it like this:

你可以这样使用它:

db.TBL_USERs.Where(CreateExpression<TBL_USER>("LAST_NAME", "Costa", "eq"));

#2

Give this article a peep. It is focused on using jqgrid in MVC but you can extract the relevant information.

把这篇文章看一下。它关注于在MVC中使用jqgrid，但是您可以提取相关信息。