jqgrid与asp.net webmethod和json使用排序,分页,搜索和LINQ – 但需要动态运算符

这个工作！ ..但还需要一个东西…

好吧，所以这是一个“评论”和问题。首先，是可以帮助其他人搜索asp.net webmethod / jqGrid方法的工作示例。下面的代码完全工作，发送/接收JSON参数从和到jqGrid为了有正确的分页，排序，过滤(使用单一搜索)利用LINQ ..它使用片段从这里和那里…

第二，是我的问题：
有没有人确定一个适当的方法来计算发送到codebehind的动态运算符？由于客户端可能发送“eq”(等于)，“cn”(包含)“gt”(大于)，我需要一个更好的方式动态生成一个whereClause，不仅限于我构建一个whereClause字符串“=”或“<>”，而是可以包括与动态Linq利用.Contains或.EndsWith等的能力。

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

代码，处理此截至现在(它的工作，但是有限)：

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 });
}

随着节目………

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

首先，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",@R_263_10586@l: "@R_263_10586@lpages",records: "@R_263_10586@lrecords",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','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>

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

第二，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 = @R_772_2984@,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);

            //--- associate value to field parameter
            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 @R_263_10586@lRecords = query.Count(); //--- number of @R_263_10586@l items from query
        int @R_263_10586@lPages = (int)R_8_11845@ath.Ceiling((decimal)@R_263_10586@lRecords / (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 {
            @R_263_10586@lpages = @R_263_10586@lPages,//--- number of pages
            page = pageIndex,//--- current page
            @R_263_10586@lrecords = @R_263_10586@lRecords,//--- @R_263_10586@l 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; }
}

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

第三，事实

>为了在LINQ中有动态的OrderBy子句，我不得不把一个类拖到我的AppCode文件夹，名为’Dynamic.cs’。您可以从downloading here检索文件。您将在“DynamiCQuery”文件夹中找到该文件。该文件将使您能够使用动态ORDERBY子句，因为我们不知道除了初始加载之外，我们要过滤的列。
>为了将JSON从C-sharp序列化到JS，我引入了位于这里的James Newton-King JSON.net DLL：下载后，有一个“Newtonsoft.Json.Compact.dll”，您可以添加在您的Bin文件夹中作为参考
>这是我的USING的块
使用系统;
使用System.Collections;
使用System.Collections.Generic;
使用System.Linq;
使用System.Web.UI.WebControls;
使用System.Web.services;
使用System.Linq.Dynamic;
>对于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
>对于CSS，我使用jqGrid的必需品以及jQuery UI主题：1)jquery_theme / jquery-ui-1.7.2.custom.css … 2)ui.jqgrid.css

将参数从JS获取到WebMethod的关键，而不必解析后端的非序列化字符串或者必须设置一些JS逻辑来切换不同数量的参数的方法是这个块

postData: { searchString: '',

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

希望这有助于其他人！并且感谢您有时间阅读和回复关于在运行时与操作符构建whereClause的动态方法

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;
    }
}

public virtual Expression<Func<T,bool>> CreateExpression<T>(String searchField,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",Expression.Constant(searchString));
                    break;
                case "ew":
                    exp = Expression.Call(propertyAccess,typeof(String).GetMethod("EndsWith",Expression.Constant(searchString));
                    break;
                case "gt":
                    exp = Expression.GreaterThan(propertyAccess,Expression.Constant(searchString,propertyAccess.TypE));
                    break;
                case "ge":
                    exp = Expression.GreaterThanOrequal(propertyAccess,propertyAccess.TypE));
                    break;
                case "lt":
                    exp = Expression.LessThan(propertyAccess,propertyAccess.TypE));
                    break;
                case "le":
                    exp = Expression.LessThanOrequal(propertyAccess,propertyAccess.TypE));
                    break;
                case "eq":
                    exp = Expression.Equal(propertyAccess,Expression.Constant(searchString.ToType(propertyAccess.TypE),propertyAccess.TypE));
                    break;
                case "ne":
                    exp = Expression.NotEqual(propertyAccess,propertyAccess.TypE));
                    break;
                default:
                    return null;
            }

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

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