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

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

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

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

 

 

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

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

<html>

<head>

  <title></title>

  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" xmlns="" />

  <link rel="stylesheet" type="text/css" href="lib/ext/resources/css/ext-all.css" />

  <link rel="stylesheet" type="text/css" href="css/application.css" />

</head>

  <script type="text/javascript" src="lib/ext/ext-base.js"></script>

  <script type="text/javascript" src="lib/ext/ext-all.js"></script>

  <script type="text/javascript" src="lib/ext/locale/ext-lang-zh_CN.js"></script>

<body scroll="no">

  <div id="loading-mask"></div>

  <div id="loading">

    <div class="loading-indicator"><img alt="" src="lib/ext/resources/images/default/shared/large-loading.gif" width="32" height="32" style="margin-right:8px;" align="absmiddle"/>正在加载...</div>

  </div>

  <script type="text/javascript">

 

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 Query Library会自动重新生成Linq语句执行。

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

 

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

 

 

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

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

posted @ 2009-04-15 17:23  程序员天下  阅读(278)  评论(0)  编辑  收藏  举报