Encapsulate jquery table plug-in jqgrid and control jqgrid (3): Query, edit, modify, and delete

The last two articles:

Encapsulate the jquery table plug-in jqgrid and control jqgrid (1): Display

Encapsulate jquery table plug-in jqgrid and control jqgrid (2): Display

This article will encode the table's action functions, query, edit, modify and delete, and add the source code at the end of the article for your reference. We hope you can discuss it together!

1. Add attributes to the main class of the jqgrid. CS control to control the switches of these functions.

# Region Action Buttons private bool _ search = false; private bool _ add = false; private bool _ edit = false; private bool _ del = false; [description ("can be queried, default ")] public bool search {get {return _ search;} set {_ search = value;} [description (" can be added, default ")] public bool add {get {return _ add;} set {_ add = value ;}} [description ("editable, default")] public bool edit {get {return _ edit;} set {_ edit = value ;}} [description ("whether it can be deleted, no" by default)] public bool del {get {return _ del;} set {_ del = value ;}}# endregion
 

 

Correspondingly, when constructing jqgrid page JS, construct the corresponding JS script,CodeYou can see the source code by yourself.

2. After completing the control class, data is now provided with class encoding, which is also the core.

1) Query

You can use firebug to view jqgrid queries in the get method. Therefore, you can use request. querystring to obtain the value passed by the control.

The query switch of the _ search parameter control is displayed. If the value is true, the query is enabled.

This control only uses the composite query method, and uses "include", "greater than or equal", and "less than or equal" by default. The former meets the requirements of most general queries, the latter makes query processing easier

The format is {"groupop": "and", "rules": [{"field": "email", "op": "cn", "data ": "1" },{ "field": "orderno", "op": "Ge", "data": "2"}]}

When you use a composite query, the passed query value is included in the filters parameter in JSON format. Therefore, to use the value, You Need To deserialize the value in JSON format, datacontractjsonserializer is used here, which must be using system in the class. runtime. serialization. JSON, which must be referenced in the project. The core code is as follows:

// Deserialization JSON string conditions = httputility. urldecode (context. request. querystring ["filters"]. tostring (); var mstream = new memorystream (encoding. utf8.getbytes (Conditions); // solves Chinese garbled characters // 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 );
 

In addition, you need to create a new class for deserialization.

[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> items {get {return rule;} 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 ;}}}

Note that the datacontract Declaration on the class name and the declaration in the parameter, such as [datamember (name = "groupop")], are required for deserialization. You can search for details, I will not elaborate on it here

The above completes parameter analysis and cleanup in terms of query conditions, and now performs database interaction on these items

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 + = ")";}

When you notice the or condition, searchcase + = "and (1 <> 1", you can try it yourself in SQL, this can solve the problem of starting with or when splicing SQL conditions. In the first article, there is also the form of 1 = 1, which can also solve the problem of starting with and. This is taught by my master and I think it is a cool skill.

The query results are over.

 

2) edit, delete, and add

At the beginning of the design of the control, I had a headache for this editing. The fields in each table are different and the types are different. Do you use reflection? It is not flexible and troublesome. You cannot control details.

Use Entity Framework. This is a good practice. The post value is automatically obtained and the model is updated automatically. However, it depends on entity, which is not universal.

Later, I thought of xml configuration. The problem could be solved in both the flexibility of table body presentation and the data provision class.

All these three functions use the POST method to pass the values. After understanding this, it is very convenient to directly splice SQL to complete these three functions, which is very simple.

# Add region? Garó ,? Modify T ?,? Delete? Except y case "edit": String tablename = context. request. querystring ["tablename"]. tostring (); string idkey = string. empty; string Signature = context. request. form ["role"]. 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 (condition) {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

 

ArticleIt's a little long. I wrote it again slowly, but it's very busy over the past few days. Let's write it once. I hope it can be used as a reference.

Here is the source code download: http://files.cnblogs.com/bestfc/AspJqGrid.rar

Usage:

Add in Web. config

 
<Pages> <controls> <add tagprefix = "allenjqgrid" assembly = "aspjqgrid" namespace = "aspjqgrid"/> </controls> </pages>  
 
After jquery.jsand jquery.ui.css are added to the page
<Allenjqgrid: jqgrid id = "myjqgrid" runat = "server" tablename = "orders" Search = "true" scroll = "true" add = "true" Edit = "true" del = "true ""/>, OK.
The configuration file is automatically generated in the directory where the page file is located. The file is generated to determine whether a configuration file exists. If no configuration file exists, the file is read. If no configuration file exists, the file is generated and then read.