jqGrid是一套基於jQuery基礎的JavaScript的Grid套件
常被用在寫SPA(單頁應用程式)
這篇主要是寫ASP.NET MVC在使用jqGrid時
Server端用的搜尋相關程式碼
相關技術:
*Lambda Expressions (C# Programming Guide)
*LINQ (Language-Integrated Query)
回目錄
原始版本的出處:Using jqGrid’s search toolbar with multiple filters in ASP.NET MVC
以下為搜尋功能的相關修正 (沒改的檔案, 請照原來的版本照用)
以下修正過的內容曾於2013年06月13日OpenSource
並於2013年07月30日再次修正相關bug
不保證沒有遺漏掉的Bug, 如果有人在使用上有任何問題, 日後再另行修正
*檔案 Filter.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Json;
namespace MvcGrid.Models.Grid
{
[DataContract]
public class Filter
{
[DataMember]
public string groupOp { get; set; }
[DataMember]
public Rule[] rules { get; set; }
public static Filter Create(string jsonData)
{
try
{
var serializer = new DataContractJsonSerializer(typeof(Filter));
System.IO.StringReader reader = new System.IO.StringReader(jsonData);
System.IO.MemoryStream ms =
new System.IO.MemoryStream(Encoding.Unicode.GetBytes(jsonData));
return serializer.ReadObject(ms) as Filter;
}
catch
{
return null;
}
}
}
}
*檔案 WhereOperation.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Json;
namespace MvcGrid.Models.Grid
{
[DataContract]
public class Filter
{
[DataMember]
public string groupOp { get; set; }
[DataMember]
public Rule[] rules { get; set; }
public static Filter Create(string jsonData)
{
try
{
var serializer = new DataContractJsonSerializer(typeof(Filter));
System.IO.StringReader reader = new System.IO.StringReader(jsonData);
System.IO.MemoryStream ms =
new System.IO.MemoryStream(Encoding.Unicode.GetBytes(jsonData));
return serializer.ReadObject(ms) as Filter;
}
catch
{
return null;
}
}
}
}
*檔案 WhereOperation.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace MvcGrid.Models.Helpers
{
/// <summary>
/// The supported operations in where-extension
/// </summary>
public enum WhereOperation
{
//等於
[StringValue("eq")]
Equal,
//不等於
[StringValue("ne")]
NotEqual,
//小於
[StringValue("lt")]
LessThan,
//小於等於
[StringValue("le")]
LessThanOrEqual,
//大於
[StringValue("gt")]
GreaterThan,
//大於等於
[StringValue("ge")]
GreaterThanOrEqual,
//開始於
[StringValue("bw")]
BeginsWith,
//不開始於
[StringValue("bn")]
NotBeginsWith,
//在其中
[StringValue("in")]
In,
//不在其中
[StringValue("ni")]
NotIn,
//結束於="ew"
[StringValue("ew")]
EndWith,
//不結束於
[StringValue("en")]
NotEndWith,
//包含
[StringValue("cn")]
Contains,
//不包含
[StringValue("nc")]
NotContains,
//Null
[StringValue("nu")]
Null,
//不Null
[StringValue("nn")]
NotNull
}
}
*檔案 LinqExtensions.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
namespace MvcGrid.Models.Helpers
{
public static class LinqExtensions
{
/// <summary>Orders the sequence by specific column and direction.</summary>
/// <param name="query">The query.</param>
/// <param name="sortColumn">The sort column.</param>
/// <param name="ascending">if set to true [ascending].</param>
public static IQueryable<T> OrderBy<T>(this IQueryable<T> query, string sortColumn, string direction)
{
string methodName = string.Format("OrderBy{0}",
direction.ToLower() == "asc" ? "" : "Descending");
ParameterExpression parameter = Expression.Parameter(query.ElementType, "p");
MemberExpression memberAccess = null;
foreach (var property in sortColumn.Split('.'))
memberAccess = MemberExpression.Property
(memberAccess ?? (parameter as Expression), property);
LambdaExpression orderByLambda = Expression.Lambda(memberAccess, parameter);
MethodCallExpression result = Expression.Call(
typeof(Queryable),
methodName,
new[] { query.ElementType, memberAccess.Type },
query.Expression,
Expression.Quote(orderByLambda));
return query.Provider.CreateQuery<T>(result);
}
public static IQueryable<T> Where<T>(this IQueryable<T> query,
string column, object value, WhereOperation operation)
{
if (string.IsNullOrEmpty(column))
return query;
ParameterExpression parameter = Expression.Parameter(query.ElementType, "p");
MemberExpression memberAccess = null;
foreach (var property in column.Split('.'))
memberAccess = MemberExpression.Property
(memberAccess ?? (parameter as Expression), property);
//change param value type
//necessary to getting bool from string
ConstantExpression filter = Expression.Constant
(
Convert.ChangeType(value, memberAccess.Type)
);
//switch operation
Expression condition = null;
LambdaExpression lambda = null;
switch (operation)
{
//等於 equal ==
case WhereOperation.Equal:
condition = Expression.Equal(memberAccess, filter);
lambda = Expression.Lambda(condition, parameter);
break;
//不等於 not equal !=
case WhereOperation.NotEqual:
condition = Expression.NotEqual(memberAccess, filter);
lambda = Expression.Lambda(condition, parameter);
break;
//小於
case WhereOperation.LessThan:
condition = Expression.LessThan(memberAccess, filter);
lambda = Expression.Lambda(condition, parameter);
break;
//小於等於
case WhereOperation.LessThanOrEqual:
condition = Expression.LessThanOrEqual(memberAccess, filter);
lambda = Expression.Lambda(condition, parameter);
break;
//大於
case WhereOperation.GreaterThan:
condition = Expression.GreaterThan(memberAccess, filter);
lambda = Expression.Lambda(condition, parameter);
break;
//大於等於
case WhereOperation.GreaterThanOrEqual:
condition = Expression.GreaterThanOrEqual(memberAccess, filter);
lambda = Expression.Lambda(condition, parameter);
break;
//開始於
case WhereOperation.BeginsWith:
condition = Expression.Call(memberAccess,
typeof(string).GetMethod("StartsWith", new[] { typeof(string) }),
Expression.Constant(value));
lambda = Expression.Lambda(condition, parameter);
break;
//不開始於
case WhereOperation.NotBeginsWith:
condition = Expression.Call(memberAccess,
typeof(string).GetMethod("StartsWith", new[] { typeof(string) }),
Expression.Constant(value));
condition = Expression.Not(condition);
lambda = Expression.Lambda(condition, parameter);
break;
//在其中 string.Contains()
case WhereOperation.In:
condition = Expression.Call(memberAccess,
typeof(string).GetMethod("Contains", new[] { typeof(string) }),
Expression.Constant(value));
lambda = Expression.Lambda(condition, parameter);
break
;
//不在其中 string.Contains()
case WhereOperation.NotIn:
condition = Expression.Call(memberAccess,
typeof(string).GetMethod("Contains", new[] { typeof(string) }),
Expression.Constant(value));
condition = Expression.Not(condition);
lambda = Expression.Lambda(condition, parameter);
break;
//結束於
case WhereOperation.EndWith:
condition = Expression.Call(memberAccess,
typeof(string).GetMethod("EndsWith", new[] { typeof(string) }),
Expression.Constant(value));
lambda = Expression.Lambda(condition, parameter);
break;
//不結束於
case WhereOperation.NotEndWith:
condition = Expression.Call(memberAccess,
typeof(string).GetMethod("EndsWith", new[] { typeof(string) }),
Expression.Constant(value));
condition = Expression.Not(condition);
lambda = Expression.Lambda(condition, parameter);
break;
//包含 string.Contains()
case WhereOperation.Contains:
condition = Expression.Call(memberAccess,
typeof(string).GetMethod("Contains", new[] { typeof(string) }),
Expression.Constant(value));
lambda = Expression.Lambda(condition, parameter);
break;
//不包含
case WhereOperation.NotContains:
condition = Expression.Call(memberAccess,
typeof(string).GetMethod("Contains", new[] { typeof(string) }),
Expression.Constant(value));
condition = Expression.Not(condition);
lambda = Expression.Lambda(condition, parameter);
break;
//Null
case WhereOperation.Null:
condition = Expression.Call(memberAccess,
typeof(string).GetMethod("IsNullOrEmpty"),
Expression.Constant(value));
lambda = Expression.Lambda(condition, parameter);
break;
//不Null
case WhereOperation.NotNull:
condition = Expression.Call(memberAccess,
typeof(string).GetMethod("IsNullOrEmpty"),
Expression.Constant(value));
condition = Expression.Not(condition);
lambda = Expression.Lambda(condition, parameter);
break;
}
MethodCallExpression result = Expression.Call(
typeof(Queryable), "Where",
new[] { query.ElementType },
query.Expression,
lambda);
return query.Provider.CreateQuery<T>(result);
}
}
}