封装jQuery表格插件jqGrid,控件化jqGrid(三):查询,编辑,修改,删除

上两篇:

封装jQuery表格插件jqGrid,控件化jqGrid(一):显示

封装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

 

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

这里是源码下载:http://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了。

配置文件自动生成在页面文件所在目录,生成会判断是否有配置文件,有则读,无则生成后再读。 

posted @ 2010-06-09 12:43  黑曜石  阅读(12271)  评论(21编辑  收藏  举报