使用Dynamic LINQ实现Ext Grid的远程排序

来源：互联网发布：北京和隆优化待遇编辑：程序博客网时间：2024/06/11 07:16

要实现Ext Grid的远程排序其实很简单，只要修改查询语句的排序关键字就可以了，但是，如果你的项目是使用Linq进行开发的，会发现动态修改排序关键字并不是那么容易的事，解决办法就是使用LINQ Dynamic Query Library。LINQ Dynamic Query Library是一个很实用很强大的库函数，通过该库，可以轻松实现一些需要通过动态参数实现的Linq查询。

本文将通过一个实例演示如何使用LINQ Dynamic Query Library实现Ext Grid的远程排序。

LINQ DynamicQuery Library可以在VS2008的例程里找到，也可以从以下链接下载：

VB Dynamic Query Library (included in the /Language Samples/LINQ Samples/DynamicQuery directory)
C# Dynamic Query Library (included in the /LinqSamples/DynamicQuery directory)

本例子将使用SQL Server的“NORTHWND”样例数据库。Ext Grid显示的是Employees表的数据。

以下是客户端的完整代码：

<html>

<head>

</head>

</div>

var app={}

Ext.onReady(function(){

Ext.BLANK_IMAGE_URL='lib/ext/resources/images/default/s.gif';

Ext.QuickTips.init();

Ext.form.Field.prototype.msgTarget = 'side';

Ext.Msg.minWidth=300;

app.store=new Ext.data.Store({

url:'employees_action.ashx?act=list',

baseParams:{},

reader:new Ext.data.JsonReader({

totalProperty: "results",

root:"rows",

id:"id"

},[{name: 'id',type:'int'},{name:'lastname'},{name:'firstname'},

{name:'title'},{name:'titleofcourtesy'},{name:'city'},

{name:'address'},{name:'region'},{name:'postalcode'},{name:'homephone'},{name:'country'},

{name:'birthdate',type: 'date',dateFormat:'Y-m-d'},

{name:'hiredate',type: 'date',dateFormat:'Y-m-d'}

]),

remoteSort: true

}) //store

app.pageToolbar=new Ext.PagingToolbar({

pageSize:3,displayInfo:true,store:app.store

});

app.grid=new Ext.grid.GridPanel({layout:'fit',

store:app.store, autoExpandColumn:2,tbar:app.pageToolbar,

columns:

[

{id:'id',header: "ID",width:80,dataIndex:'id',sortable: true},

{header: "FirstName",width:80, dataIndex:'firstname',sortable: true},

{header: "LastName",width:80, dataIndex:'lastname',sortable: true},

{header: "Title",width:80, dataIndex:'title',sortable: true},

{header: "Title of Courtesy",width:80, dataIndex:'titleofcourtesy',sortable: true},

{header: "City",width:80, dataIndex:'city',sortable: true},

{header: "Region",width:80, dataIndex:'region',sortable: true},

{header: "Country",width:80, dataIndex:'country',sortable: true},

{header: "Postalcode",width:80, dataIndex:'postalcode',sortable: true},

{header: "Homephone",width:80, dataIndex:'homephone',sortable: true},

{header: "Birthdate", width: 120,dataIndex:'birthdate',sortable: true,renderer:Ext.util.Format.dateRenderer('Y-m-d')},

{header: "Hiredate", width: 120,dataIndex:'hiredate',sortable: true,renderer:Ext.util.Format.dateRenderer('Y-m-d')}

]

})

var viewport = new Ext.Viewport({layout:'fit',items:[app.grid]});

app.store.load();

setTimeout(function(){

Ext.get('loading').remove();

Ext.get('loading-mask').fadeOut({remove:true});

}, 250);

})//onReady

</script>

</body>

</html>

代码很简单，定义了一个Store、PagetoolBar和Grid。因为Employees表数据只有9条，所以设置了每页3条数据。在Store定义中将remoteSort设置为true，说明数据要实现远程排序。Grid的每一列都将sortable属性设置为true，说明都可以通过单击Grid的列标题实现排序。

以下是服务器端的完整代码：

<%@ WebHandler Language="C#" Class="employees_action" Debug="true" %>

using System;

using System.Web;

using System.Linq;

using System.Linq.Dynamic;

using System.Collections;

using System.Collections.Generic;

using System.Web.Security;

using LitJson;

public class employees_action : IHttpHandler

{

public void ProcessRequest (HttpContext context) {

string action = context.Request.Params["act"];

string outputStr = "";

if (action == null) action = "";

switch (action.ToLower())

{

case "list":

outputStr = List(context);

break;

default:

outputStr = HDQ.Functions.WriteJsonResult(false, "错误的操作类型！");

break;

}

context.Response.ContentType = "text/javascript";

context.Response.Write(outputStr);

}

public bool IsReusable {

get {

return false;

}

private string List(HttpContext context)

{

int limit=0;

int.TryParse(context.Request.Params["limit"], out limit);

if (limit == 0) limit = 3;

int start=0;

int.TryParse(context.Request.Params["start"], out start);

string orderColumn = context.Request.Params["sort"];

string orderBy = context.Request.Params["dir"] == "ASC" ? "" : "descending";

switch (orderColumn)

{

case "id":

orderColumn = "EmployeeID";

break;

case "lastname":

orderColumn = "LastName";

break;

case "firstname":

orderColumn = "FirstName";

break;

case "title":

orderColumn = "Title";

break;

case "titleofcourtesy":

orderColumn = "TitleOfCourtesy";

break;

case "birthdate":

orderColumn = "BirthDate";

break;

case "hiredate":

orderColumn = "HireDate";

break;

case "address":

orderColumn = "Address";

break;

case "city":

orderColumn = "City";

break;

case "region":

orderColumn = "Region";

break;

case "postalcode":

orderColumn = "PostalCode";

break;

case "country":

orderColumn = "Country";

break;

case "homephone":

orderColumn = "HomePhone";

break;

default:

orderColumn = "EmployeeID";

break;

}

DBDemosDataContext dc = new DBDemosDataContext();

int recordCount=0;

JsonWriter jw = new JsonWriter();

jw.WriteObjectStart();

jw.WritePropertyName("rows");

jw.WriteArrayStart();

recordCount = dc.Employees.Count();

if (start > recordCount) start = 0;

var q=dc.Employees.OrderBy(orderColumn + " " + orderBy).Skip(start).Take(limit);

foreach (var c in q)

{

jw.WriteObjectStart();

jw.WritePropertyName("id");

jw.Write(c.EmployeeID);

jw.WritePropertyName("firstname");

jw.Write(c.FirstName);

jw.WritePropertyName("lastname");

jw.Write(c.LastName);

jw.WritePropertyName("title");

jw.Write(c.Title);

jw.WritePropertyName("titleofcourtesy");

jw.Write(c.TitleOfCourtesy);

jw.WritePropertyName("address");

jw.Write(c.Address);

jw.WritePropertyName("city");

jw.Write(c.City);

jw.WritePropertyName("region");

jw.Write(c.Region);

jw.WritePropertyName("country");

jw.Write(c.Country);

jw.WritePropertyName("postalcode");

jw.Write(c.PostalCode);

jw.WritePropertyName("homephone");

jw.Write(c.HomePhone);

jw.WritePropertyName("birthdate");

jw.Write(c.BirthDate == null ? "" : Convert.ToDateTime(c.BirthDate).ToString("yyyy-MM-dd"));

jw.WritePropertyName("hiredate");

jw.Write(c.HireDate == null ? "" : Convert.ToDateTime(c.HireDate).ToString("yyyy-MM-dd"));

jw.WriteObjectEnd();

}

jw.WriteArrayEnd();

jw.WritePropertyName("results");

jw.Write(recordCount.ToString());

jw.WriteObjectEnd();

return jw.ToString();

}

代码中ProcessRequest方法根据提交的参数action执行对应的方法。本文主要是执行List方法。

在List方法的开头首先获取了客户端提交的几个参数，参数对应的说明请看下表：

参数

说明

limit

每页总数，本例子是3

start

提取数据开始位置

sort

要排序的列

dir

排序顺序

获取数据后需要对排序的列名和顺序做一下转换，以下语句就是实现排序顺序的转换：

string orderBy = context.Request.Params["dir"] == "ASC" ? "" : "descending";

列名的转换则通过switch语句实现。如果在客户端定义的列名与数据库的真实列名相同，也可以不实施转换。不过，出于安全考虑，建议无论如何，还是要实行转换。

转换完成后，就可以定义查询语句了，相当的简单：

var q=dc.Employees.OrderBy(orderColumn + " " + orderBy).Skip(start).Take(limit);

将列名变量和顺序变量组合成字符串作为OrderBy方法的参数就可以了。LINQ Dynamic QueryLibrary会自动重新生成Linq语句执行。

后面的代码就是将查询结果组合成Json格式数据输出。

如果不使用LINQ Dynamic Query Library，远程排序的实现最直接的方法就是使用switch语句，根据提交的列和排序顺序写不同的Linq语句，就不如本例的代码那么简洁了。

以下是本例程的代码下载地址：

http://download.csdn.net/source/1212462