jqGrid LINQ和匿名类型
本文关键字：类型 LINQ jqGrid | 更新日期: 2023-09-27 18:06:58
jqGrid采用以下JSON格式:
{
"total": "5",
"page": "2",
"records": "55",
"rows" : [
{"id" :"21", "cell" :["cell11", "cell12", "cell13"]},
{"id" :"22", "cell" :["cell21", "cell22", "cell23"]},
...
{"id" :"30", "cell" :["cell31", "cell32", "cell33"]},
]
}
我试图使一个方法尽可能可重用通过AJAX传递数据回jqGrid。
var result = new
{
total = (int) Math.Ceiling((double) totalCount/PageSize),
page = PageIndex,
records = totalCount,
rows = data.Select((d, id) => new {id, cell = d.SerializeGridParameters()}).ToArray()
};
正如您所看到的，目前我没有额外的努力就成功地添加了索引，但是我在字段数据方面遇到了麻烦。
到目前为止，我通过使用一个接口来处理它:public interface IGridParameterListable
{
List<string> SerializeGridParameters();
}
对于我的数据(这是一个IEnumerable<T> where T : IGridParameterListable)。
事情是我宁愿有一个通用的方法，只是盲目地将对象属性值转换为List<string> ..
听起来不太可爱，我知道，我愿意接受其他的想法。
我希望尽可能避免在客户端和服务器端重复网格的数据结构。
这不是答案，只是一些源代码
如果有人想要我的jqGrid的ASP实现。. NET WebForms，鉴于它很难实现，我将把代码贴在这里。
首先，一些JSON类:public class GridFilter
{
public string groupOp { get; set; }
public GridRule[] rules { get; set; }
}
public class GridRule
{
public string field { get; set; }
public string op { get; set; }
public string data { get; set; }
}
public class GridSettings
{
public bool IsSearch { get; set; }
public int PageSize { get; set; }
public int PageIndex { get; set; }
public string SortColumn { get; set; }
public string SortOrder { get; set; }
public GridFilter Where { get; set; }
}
前端代码，我改变了一些，所以它允许我发布到服务只有一个参数，填满了网格信息。另外，我不太关心单个过滤器，所以我只支持多个过滤器模式(在我看来，这是严格更好的)。
<table id="UsersGrid"></table>
<div id="UsersGridPager"></div>
<script type="text/javascript">
$(document).ready(function () {
$('#UsersGrid').jqGrid({
colNames: ['ID', 'Online', 'Computer', 'IP', 'User'],
colModel: [
{ name: 'ID', width: 100, index: 'ID', searchoptions: { sopt: ['eq', 'ne']} },
{ name: 'IsOnline', width: 100, index: 'IsOnline', searchoptions: { sopt: ['eq', 'ne']} },
{ name: 'Name', index: 'Name', searchoptions: { sopt: ['eq', 'ne', 'cn']} },
{ name: 'IP', index: 'IP', searchoptions: { sopt: ['eq', 'ne', 'cn']} },
{ name: 'User', index: 'User', searchoptions: { sopt: ['eq', 'ne', 'cn']} }
],
jsonReader: {
root: function (json) { return JSON.parse(json.d).rows; },
page: function (json) { return JSON.parse(json.d).page; },
total: function (json) { return JSON.parse(json.d).total; },
records: function (json) { return JSON.parse(json.d).records; }
},
serializeGridData: jqGridSettings,
caption: "Usuarios",
emptyrecords: "No se encontraron usuarios",
url: "/GridTest/GridTestService.asmx/GetData",
ajaxGridOptions: { contentType: 'application/json; charset=utf-8' },
datatype: 'json',
mtype: 'POST',
height: 250,
rowNum: 10,
rowList: [10, 25, 50],
rownumbers: true,
autowidth: true,
pager: "#UsersGridPager"
}).navGrid("#UsersGridPager",
{
refresh: true,
add: false,
edit: false,
del: false,
search: true
},
{},
{},
{},
{
sopt: ["eq", "ne", "cn"],
multipleSearch: true,
showQuery: true
}
);
function jqGridSettings(p) {
var settings = {
grid: {
PageIndex: p.page,
PageSize: p.rows,
IsSearch: p._search,
SortColumn: p.sidx,
SortOrder: p.sord,
Where: jqGridFilters(p.filters)
}
};
return JSON.stringify(settings);
}
function jqGridFilters(json) {
var filters = {};
if (!json) {
return;
}
if (!json.length) {
return;
}
var parsed = JSON.parse(json);
if (!!parsed.rules) {
filters = parsed;
}
return filters;
}
});
现在，对于实际的实现…首先，我们将需要几个LINQ扩展方法，用于对数据进行排序。
public static IQueryable<T> OrderBy<T>(this IQueryable<T> query, string sortColumn, string direction)
{
if (string.IsNullOrEmpty(sortColumn))
return query;
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, string 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
LambdaExpression lambda = null;
switch (operation)
{
case "eq": // equal
{
lambda = Expression.Lambda(Expression.Equal(memberAccess, filter), parameter);
break;
}
case "ne": // not equal
{
lambda = Expression.Lambda(Expression.NotEqual(memberAccess, filter), parameter);
break;
}
case "cn": // contains
{
Expression condition = Expression.Call(memberAccess,
typeof (string).GetMethod("Contains"),
Expression.Constant(value.ToString()));
lambda = Expression.Lambda(condition, parameter);
break;
}
}
var result = Expression.Call(
typeof(Queryable), "Where",
new[] { query.ElementType },
query.Expression,
lambda);
return query.Provider.CreateQuery<T>(result);
}
搜索的实际实现，出于偏好，我把它放在我的GridSettings类中，作为一个成员方法，但它也可以是IQueryable扩展方法。
public string SerializeQuery<T>(IQueryable<T> query, Func<T, List<string>> select)
{
//filtering
if (IsSearch && Where.rules != null)
{
if (Where.groupOp == "AND") // TODO: INSENSITIVE EQUALS, Y un enum GridGroupOperation.And.Name()
{
foreach (var rule in Where.rules)
query = query.Where(rule.field, rule.data, rule.op);
}
else if (Where.groupOp == "OR") // TODO: INSENSITIVE EQUALS, Y un enum GridGroupOperation.Or.Name()
{
var temp = (new List<T>()).AsQueryable();
foreach (var rule in Where.rules)
{
var t = query.Where(rule.field, rule.data, rule.op);
temp = temp.Concat(t);
}
//remove repeat records
query = temp.Distinct();
}
}
//sorting
query = query.OrderBy(SortColumn, SortOrder);
//count
var totalCount = query.Count();
//paging
var data = query.Skip((PageIndex - 1) * PageSize).Take(PageSize);
//convert to grid format
var result = new
{
total = (int)Math.Ceiling((double)totalCount / PageSize),
page = PageIndex,
records = totalCount,
rows = data.Select((d, id) => new { id, cell = select(d) }).ToArray()
};
return JsonConvert.SerializeObject(result);
}
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ToolboxItem(false)]
[ScriptService]
public class GridTestService : System.Web.Services.WebService
{
[WebMethod]
[ScriptMethod]
public string GetData(GridSettings grid)
{
var query = new FakeComputersRepository().Computers();
var response = grid.SerializeQuery(query, d => new List<string>
{
d.ID.ToString(),
d.IsOnline.ToString(),
d.Name,
d.IP,
d.User
});
return response;
}
}
正如您所看到的，我只是获取数据，选择我想要显示的列(这些列必须与客户端网格中的列相同且顺序相同)，这几乎就是它了。这个想法是为了使其尽可能可重用。
Nico，这是我见过的使用asp.net webforms实现jqgrid的最佳方法。通过向SerializeQuery方法添加第三个参数，我做了一个小而重要的改进:一个实际行id的列表，以便我们可以将它们添加到结果中。如果没有这个列表，发送给客户机的行id实际上只是包含行数据的列表中项目的索引，而不是来自数据库行的实际id。在需要在jqgrid中启用编辑和删除的场景中，这是不可用的。
public string SerializeQuery<T>(IQueryable<T> query, Func<T, List<string>> select, Func<T, List<int>> ids)
{
//filtering
if (IsSearch && Where.rules != null)
{
if (Where.groupOp == "AND") // TODO: INSENSITIVE EQUALS, Y un enum GridGroupOperation.And.Name()
{
foreach (var rule in Where.rules)
query = query.Where(rule.field, rule.data, rule.op);
}
else if (Where.groupOp == "OR") // TODO: INSENSITIVE EQUALS, Y un enum GridGroupOperation.Or.Name()
{
var temp = (new List<T>()).AsQueryable();
foreach (var rule in Where.rules)
{
var t = query.Where(rule.field, rule.data, rule.op);
temp = temp.Concat(t);
}
//remove repeat records
query = temp.Distinct();
}
}
//sorting
query = query.OrderBy(SortColumn, SortOrder);
//count
var totalCount = query.Count();
//paging
var data = query.Skip((PageIndex - 1) * PageSize).Take(PageSize);
//convert to grid format
var result = new
{
total = (int)Math.Ceiling((double)totalCount / PageSize),
page = PageIndex,
records = totalCount,
rows = data.Select((d) => new {
id = ids(d),
cell = select(d)
}).ToArray()
};
return JsonConvert.SerializeObject(result);
}
这可能是更好的选择。
public string SerializeQuery<T>(IQueryable<T> query, Func<T, List<string>> select)
{
// stuff ...
var result = new
{
total = (int)Math.Ceiling((double)totalCount / PageSize),
page = PageIndex,
records = totalCount,
rows = data.Select((d, id) => new { id, cell = select(d) }).ToArray()
};
// stuff ...
}
我消除了对接口的需求，并将转换转移到每个特定网格的查询实现上。在这个例子中:
[WebMethod]
[ScriptMethod]
public string GetData(GridSettings grid)
{
var query = new FakeComputersRepository().Computers();
var response = grid.SerializeQuery(query, d => new List<string>
{
d.ID.ToString(),
d.IsOnline.ToString(),
d.Name,
d.IP,
d.User
});
return response;
}
我觉得好一点了。还有其他的想法吗?