封装jQuery表格插件jqGrid，控件化jqGrid(三）：查询，编辑，修改，删除

上两篇：

本文将编码表格的动作功能，查询，编辑，修改和删除，并在文末附上源码，供大家参考，一起讨论，希望可以抛砖引玉！

一，在jqGrid.cs控件主体类中加入属性，用于控制这些功能的开关

#region 动作按钮
        private bool _search = false;
        private bool _add = false;
        private bool _edit = false;
        private bool _del = false;
        [Description("是否可查询，默认否")]
        public bool Search
        {
            get { return _search; }
            set { _search = value; }
        }
        [Description("是否可添加，默认否")]
        public bool Add
        {
            get { return _add; }
            set { _add = value; }
        }
        [Description("是否可编辑，默认否")]
        public bool Edit
        {
            get { return _edit; }
            set { _edit = value; }
        }
        [Description("是否可删除，默认否")]
        public bool Del
        {
            get { return _del; }
            set { _del = value; }
        }
        #endregion

相应的，需要在构造jqGrid页面JS时，构造相应的JS脚本，代码大家自己下源码可以看到。

二，做完了控件类，现在开始数据提供类的编码，这也是最核心的。

1）查询

可以用firebug看到jqGrid查询时，使用的是get方式，所以用Request.QueryString的方式取得控件传过来的值

看到传来的的_search参数控件查询的开关，当值为true时，查询打开

本控件仅使用复合查询的方式，并且默认使用“包含”，“大于等于”和“小于等于”，前者符合大部分的通用查询的要求，后者使得查询处理更简单些

其形式如：{"groupOp":"AND","rules":[{"field":"email","op":"cn","data":"1"},{"field":"orderno","op":"ge","data":"2"}]}

使用复合查询时，传过来的查询值是以json的方式包含在filters参数中的，因此，要使用其值，需要对其值进行JSON的反序列化，这里使用DataContractJsonSerializer，需要在类中using System.Runtime.Serialization.Json，工程中也要加入其引用，其核心代码如下：

//反序列化json
                            string conditions = HttpUtility.UrlDecode(context.Request.QueryString["filters"].ToString());
                            var mStream = new MemoryStream(Encoding.UTF8.GetBytes(conditions));     //解决中文乱码问题
                            //var mStream = new MemoryStream(Encoding.Unicode.GetBytes(conditions));
                            DataContractJsonSerializer dcjson = new DataContractJsonSerializer(typeof(jqGridFilter));
                            jqGridFilter filters = (jqGridFilter)dcjson.ReadObject(mStream);
                            //jqGridFilter filters = (jqGridFilter)dcjson.ReadObject(mStream);

另外，还需要新建一个类，用于对应反序列化

[DataContract]
    class jqGridFilter
    {
        //  {"groupOp":"AND","rules":[{"field":"email","op":"cn","data":"1"},{"field":"orderno","op":"ge","data":"2"}]}
        private string groupOp = "AND";
        private List<jqGridFilterRules> jqGridFilterRulesList;
        [DataMember(Name="groupOp")]
        public string GroupOp
        {
            get { return groupOp; }
            set { groupOp = value; }
        }
        [DataMember(Name = "rules")]
        public List<jqGridFilterRules> JqGridFilterRulesList
        {
            get { return jqGridFilterRulesList; }
            set { jqGridFilterRulesList = value; }
        }
    }
 
    [DataContract]
    class jqGridFilterRules
    {
        private string field;
        private string op;
        private string data;
        [DataMember(Name = "field")]
        public string Field
        {
            get { return field; }
            set { field = value; }
        }
        [DataMember(Name = "op")]
        public string Op
        {
            get { return op; }
            set { op = value; }
        }
        [DataMember(Name = "data")]
        public string Data
        {
            get { return data; }
            set { data = value; }
        }
    }

注意类名上的DataContract声明和参数中的形如[DataMember(Name="groupOp")]的声明，这是反序列化所必须的，具体的大家可以搜索一下，这里就不做详细说明了

以上就完成了查询条件方面的参数分析和清理，现在对这些东东进行数据库交互

if (filters.GroupOp == "AND")
                            {
                                foreach(jqGridFilterRules rules in filters.JqGridFilterRulesList)
                                {
                                    switch (rules.Op)
                                    {
                                        case "cn":
                                            searchCase += " and " + rules.Field + " like '%" + rules.Data + "%'";
                                            break;
                                        case "ge":
                                            searchCase += " and " + rules.Field + ">='" + rules.Data + "'";
                                            break;
                                        case "le":
                                            searchCase += " and " + rules.Field + "<='" + rules.Data + "'";
                                            break;
                                        default:
                                            break;
                                    }
                                }
                            }
                            else
                            {
                                searchCase += " and (1<>1";
                                foreach (jqGridFilterRules rules in filters.JqGridFilterRulesList)
                                {
                                    switch (rules.Op)
                                    {
                                        case "cn":
                                            searchCase += " or " + rules.Field + " like '%" + rules.Data + "%'";
                                            break;
                                        case "ge":
                                            searchCase += " or " + rules.Field + ">='" + rules.Data + "'";
                                            break;
                                        case "le":
                                            searchCase += " or " + rules.Field + "<='" + rules.Data + "'";
                                            break;
                                        default:
                                            break;
                                    }
                                }
                                searchCase += ")";
                            }

注意到or条件时，searchCase += " and (1<>1"这样的形式，这个小技巧，大家可以在sql中自己试试看，这样可以解决拼接SQL条件时or开头的问题，在第一篇中还有1=1的形式，同样的，也是可以解决and开头的问题。这个是我师傅教的，自认为是一个很牛比的小技巧。

查询至些就OVER了。

2）编辑，删除，添加

在设计控件之初，就为这个编辑伤脑筋，每个表的字段都不一样，类型也不一样，用反射吗，不灵活，也好麻烦，不能控件到细部

用entity framework吧，这个好，自动取到post过来的值，自动更新model，不过要依赖entity，更不通用。

后来想到配置用XML，问题一通百通，不管是表体的呈现灵活还是数据提供类的方面，都可以解决掉。

此三个功能都是使用post方式传递各项值，了解这点以后就很方便了，直接拼接SQL就可以完成这三个功能，很简单

#region 添?加ó，?修T改?，?删?除y
                    case "edit":
                        string tableName = context.Request.QueryString["tablename"].ToString();
                        string idKey = string.Empty;
                        string oper = context.Request.Form["oper"].ToString();                        
                        string xmlPath = System.AppDomain.CurrentDomain.BaseDirectory + tableName + ".xml";
                        StringBuilder sb;
                        XmlDocument xmlDoc = new XmlDocument();
                        XmlNodeList xnList = xmlDoc.SelectNodes("root//columns");
                        xmlDoc.Load(xmlPath);
                        foreach (XmlNode xn in xnList)
                        {
                            if (xn.Attributes["IsIdentity"] != null)
                            {
                                idKey = xn.Attributes["name"].Value;
                            }
                        }
 
                        switch (oper)
                        {
                            case "edit":
                                sb = new StringBuilder();
                                sb.Append("update " + tableName + " set ");
                                foreach (XmlNode xn in xnList)
                                {
                                    if (xn.Attributes["IsIdentity"] == null || xn.Attributes["IsIdentity"].Value == "False")
                                    {
                                        if (context.Request.Form[xn.Attributes["name"].Value].ToString() != string.Empty)
                                        {
                                            if (xn.Attributes["sorttype"].Value == "int" || xn.Attributes["sorttype"].Value == "float")
                                            {
                                                sb.Append(xn.Attributes["name"].Value + "=" + HttpUtility.UrlDecode(context.Request.Form[xn.Attributes["name"].Value].ToString()) + ",");
                                            }
                                            else
                                            {
                                                sb.Append(xn.Attributes["name"].Value + "='" + HttpUtility.UrlDecode(context.Request.Form[xn.Attributes["name"].Value].ToString()) + "',");
                                            }
                                        }
                                    }
                                }
                                sb.Remove(sb.Length - 1, 1);
                                sb.Append(" where id=" + context.Request.Form[idKey].ToString());
                                SqlHelper.ExecuteNonQuery(sb.ToString());
                                break;
                            case "add":
                                sb = new StringBuilder();
                                StringBuilder fieldstr = new StringBuilder();
                                StringBuilder valuestr = new StringBuilder();
                                foreach (XmlNode xn in xnList)
                                {
                                    if (xn.Attributes["IsIdentity"] == null || xn.Attributes["IsIdentity"].Value == "False")
                                    {
                                        if (context.Request.Form[xn.Attributes["name"].Value].ToString() != string.Empty)
                                        {
                                            fieldstr.Append(xn.Attributes["name"].Value + ",");
                                            if (xn.Attributes["sorttype"].Value == "int" || xn.Attributes["sorttype"].Value == "float")
                                            {
                                                valuestr.Append(HttpUtility.UrlDecode(context.Request.Form[xn.Attributes["name"].Value].ToString()) + ",");
                                            }
                                            else
                                            {
                                                valuestr.Append("'" + HttpUtility.UrlDecode(context.Request.Form[xn.Attributes["name"].Value].ToString()) + "',");
                                            }
                                        }
                                    }
                                }
                                fieldstr.Remove(fieldstr.Length - 1, 1);
                                valuestr.Remove(valuestr.Length - 1, 1);
                                sb.Append("insert into " + tableName + " (");
                                sb.Append(fieldstr.ToString());                               
                                sb.Append(") values (");
                                sb.Append(valuestr.ToString());
                                sb.Append(")");
                                SqlHelper.ExecuteNonQuery(sb.ToString());
                                break;
                            case "del":
                                string sql="delete from "+tableName+" where id="+context.Request.Form[idKey].ToString();
                                SqlHelper.ExecuteNonQuery(sql);
                                break;
                            default:
                                break;
                        }
                        #endregion

文章有点长了，本来再慢慢写的，不过这几天很忙，一次写完吧，还是那句话，希望可以抛砖引玉。

这里是源码下载：https://files.cnblogs.com/bestfc/AspJqGrid.rar

使用方法：

web.config中加入

<pages>
        <controls>
          <add tagPrefix="AllenJqGrid" assembly="AspJqGrid" namespace="AspJqGrid" />
        </controls>
      </pages>
      <httpHandlers>
        <add path="data.ashx" verb="*" type="AspJqGrid.AjaxData,AspJqGrid"/>
      </httpHandlers>

页面中加入jquery.js和jquery.ui.css后，写入
<AllenJqGrid:JqGrid ID="MyJqGrid" runat="server" TableName="orders" Search="true" Scroll="true" Add="true" Edit="true" Del="true" />，就OK了。
配置文件自动生成在页面文件所在目录，生成会判断是否有配置文件，有则读，无则生成后再读。