系列索引

 

 

 

 

目录

 

 

7.查询数据、编辑数据、删除数据  

查询数据

在ProcessRequest 函数下修改:

public void ProcessRequest(HttpContext context){//查找if (context.Request.Params.Get("_search") == "true"){return;}NameValueCollection forms = context.Request.Form;string strOperation = forms.Get("oper");string strResponse = string.Empty; if (strOperation == null) { //排序 if (context.Request.Params.Get("sidx") != null && !string.IsNullOrEmpty(context.Request.Params.Get("sidx").ToString()) && context.Request.Params.Get("sord") != null && !string.IsNullOrEmpty(context.Request.Params.Get("sord").ToString())) { return; } strResponse = GetJson(GetUserDT()); //load data } context.Response.Write(strResponse); } private DataTable GetUserDT() { string cmdText = "SELECT UserId, UserCode, Password FROM T_User"; SQLHelper sqlhelper = new SQLHelper(); DataTable dt = sqlhelper.Selectinfo(cmdText); return dt; } private string GetJson(DataTable dt) { JavaScriptSerializer serializer = new JavaScriptSerializer(); List
      
       
        > rows = new List
        
         
          >(); Dictionary
          
            row = null; foreach (DataRow dr in dt.Rows) { row = new Dictionary
           
            (); foreach (DataColumn col in dt.Columns) { string s = dr[col].ToString(); //特殊情况 if (col.ColumnName == "IsAdmin") { s = s == "1" ? "是" : "否"; } if (col.ColumnName == "HasWrite") { s = s == "1" ? "是" : "否"; } row.Add(col.ColumnName.Trim(), s); } rows.Add(row); } return serializer.Serialize(rows); }
           
          
         
        
       
      

 

稍加留意便可发现context.Request.Params里存有大量的参数，比如第一次加载时如下所示：

{_search=false&nd=1410940615486&rows=10&page=1&sidx=&sord=asc&CNZZDATA155540=cnzz_eid%2

53D900936271-1409219704-%2526ntime%253D1409219704&ALL_HTTP=HTTP_CONNECTION%3akeepalive%

0d%0aHTTP_ACCEPT%3aapplication%2fjson%2c+text%2fjavascript%2c+*%2f*%3b+q%3d0.01%0d%0aH

TTP_ACCEPT_ENCODING%3agzip%2cdeflate%2csdch%0d%0aHTTP_ACCEPT_LANGUAGE%3azh-

CN%2czh%3bq%3d0.8%2czh-

TW%3bq%3d0.6%0d%0aHTTP_COOKIE%3aCNZZDATA155540%3dcnzz_eid%253D900936271-1409219704-

%2526ntime%253D1409219704%0d%0aHTTP_HOST%3alocalhost%3a12590%0d%0aHTTP_REFERER%3ahttp%3a%2f%

2flocalhost%3a12590%2fJQGrid2.aspx%0d%0aHTTP_USER_AGENT%3aMozilla%2f5.0+(Windows+NT+6.1)+App

leWebKit%2f537.36+(KHTML%2c+like+Gecko)+Chrome%2f37.0.2062.120+Safari%2f537.36%0d%0aHTTP_X_R

EQUESTED_WITH%3aXMLHttpRequest%0d%0a&ALL_RAW=Connection%3a+keepalive%

0d%0aAccept%3a+application%2fjson%2c+text%2fjavascript%2c+*%2f*%3b+q%3d0.01%0d%0aAccep

t-Encoding%3a+gzip%2cdeflate%2csdch%0d%0aAccept-Language%3a+zh-CN%2czh%3bq%3d0.8%2czh-

TW%3bq%3d0.6%0d%0aCookie%3a+CNZZDATA155540%3dcnzz_eid%253D900936271-1409219704-

%2526ntime%253D1409219704%0d%0aHost%3a+localhost%3a12590%0d%0aReferer%3a+http%3a%2f%2flocalh

ost%3a12590%2fJQGrid2.aspx%0d%0aUser-

Agent%3a+Mozilla%2f5.0+(Windows+NT+6.1)+AppleWebKit%2f537.36+(KHTML%2c+like+Gecko)+Chrome%2f

37.0.2062.120+Safari%2f537.36%0d%0aX-Requested-

With%3a+XMLHttpRequest%0d%0a&APPL_MD_PATH=%2fLM%2fW3SVC%2f17%2fROOT&APPL_PHYSICAL_PATH=c%3a%

5cusers%5cjhl%5cdocuments%5cvisual+studio+2013%5cProjects%5cJqGrid%5cJqGrid%5c&AUTH_TYPE=&AU

TH_USER=&AUTH_PASSWORD=&LOGON_USER=&REMOTE_USER=&CERT_COOKIE=&CERT_FLAGS=&CERT_ISSUER=&CERT_

KEYSIZE=&CERT_SECRETKEYSIZE=&CERT_SERIALNUMBER=&CERT_SERVER_ISSUER=&CERT_SERVER_SUBJECT=&CER

T_SUBJECT=&CONTENT_LENGTH=0&CONTENT_TYPE=&GATEWAY_INTERFACE=CGI%2f1.1&HTTPS=off&HTTPS_KEYSIZE=&HTTPS_SECRETKEYSIZE=&HTTPS_SERVER_ISSUER=&HTTPS_SERVER_SUBJECT=&INSTANCE_ID=17&INSTANCE_M

ETA_PATH=%2fLM%2fW3SVC%2f17&LOCAL_ADDR=%3a%3a1&PATH_INFO=%2fWebService%2fUserHandler.ashx&PA

TH_TRANSLATED=c%3a%5cusers%5cjhl%5cdocuments%5cvisual+studio+2013%5cProjects%5cJqGrid%5cJqGr

id%5cWebService%5cUserHandler.ashx&QUERY_STRING=_search%3dfalse%26nd%3d1410940615486%26rows%

3d10%26page%3d1%26sidx%3d%26sord%3dasc&REMOTE_ADDR=%3a%3a1&REMOTE_HOST=%3a%3a1&REMOTE_PORT=1

1798&REQUEST_METHOD=GET&SCRIPT_NAME=%2fWebService%2fUserHandler.ashx&SERVER_NAME=localhost&S

ERVER_PORT=12590&SERVER_PORT_SECURE=0&SERVER_PROTOCOL=HTTP%2f1.1&SERVER_SOFTWARE=Microsoft-

IIS%2f8.0&URL=%2fWebService%2fUserHandler.ashx&HTTP_CONNECTION=keepalive&

HTTP_ACCEPT=application%2fjson%2c+text%2fjavascript%2c+*%2f*%3b+q%3d0.01&HTTP_ACCEPT_E

NCODING=gzip%2cdeflate%2csdch&HTTP_ACCEPT_LANGUAGE=zh-CN%2czh%3bq%3d0.8%2czh-

TW%3bq%3d0.6&HTTP_COOKIE=CNZZDATA155540%3dcnzz_eid%253D900936271-1409219704-

%2526ntime%253D1409219704&HTTP_HOST=localhost%3a12590&HTTP_REFERER=http%3a%2f%2flocalhost%3a

12590%2fJQGrid2.aspx&HTTP_USER_AGENT=Mozilla%2f5.0+(Windows+NT+6.1)+AppleWebKit%2f537.36+(KH

TML%2c+like+Gecko)+Chrome%2f37.0.2062.120+Safari%2f537.36&HTTP_X_REQUESTED_WITH=XMLHttpReque

st} System.Collections.Specialized.NameValueCollection {System.Web.HttpValueCollection}

搜索时_search=true

增删改时有参数oper。第一次加载时可以看出这个参数为null。此时就要加载数据了。查询出一个

datatable序列化后返回就好了，前端自会处理。

增加时oper=add

修改时oper=edit

删除时oper=del

数据加载完成后有个事件loadComplete，在这里可以进行一些操作比如遍历表格修改一些显示。

比如下面这个例子，遍历所有行，把每行的UserCode修改为行号：

loadComplete: function (xhr) {var users = $(grid_selector).jqGrid('getDataIDs');if (users) {for (var i = 0, j = users.length; i < j; i++) {ParkId = $(grid_selector).jqGrid('getCell', users[i],'UserCode'); $(grid_selector).jqGrid('setRowData', users[i], {'UserCode': i}); } } },

 

编辑数据

需要在colNames里新增一列为第一列，值自定义或为空即可，colNames: [' ', 'Id', '用户名',

'密码'],对应的，colModel里需要增加这些内容：

{name: 'myac', index: '', width: 80, fixed: true, sortable:false, resize: false,formatter: 'actions',formatoptions: {keys: true,//editbutton: true,editformbutton: true, // extraparam: { oper: 'edit' }, editOptions: { //编辑操作，这个很重要，实现编辑时传送参数 什么的。 reloadAfterSubmit: true, //editData: { // editkey: function () { // var sel_id = $(grid_selector).jqGrid('getGridParam', 'selrow'); // var value = $(grid_selector).jqGrid('getCell', sel_id, 'UserId'); // return value; // } //}, closeAfterEdit: true, afterSubmit: function (response, postdata) { if (response.responseText != "") { alert(response.responseText); $(this).jqGrid('setGridParam', { datatype: 'json' }).trigger('reloadGrid'); return [true, response.responseText] } }, }, delOptions: { //删除操作，这个很重要，实现删除时传送参数什 么的。 这处网上没有例子的。 reloadAfterSubmit: true, delData: { delId: function () { var sel_id = $(grid_selector).jqGrid('getGridParam', 'selrow'); var value = $(grid_selector).jqGrid('getCell', sel_id, 'UserId'); return value; } }, afterSubmit: function (response, postdata) { if (response.responseText != "") { alert(response.responseText); $(this).jqGrid('setGridParam', { datatype: 'json' }).trigger('reloadGrid'); return [true, response.responseText] } } }, } },

 

Formatoptions下有delbutton和editbutton，可以设置为true或false。默认为true。一般不要管就

好了。

Width只能为数字不能为百分比，grid会根据全部列自己计算比例。

Fixed表示不会随下面的滚动轴一起滚动。

Sortable表示能否排序。

Editformbutton表示是在弹出窗口中编辑还是行编辑。

editOptions和delOptions为设置编辑或删除时的一些参数。reloadAfterSubmit为操作完成后是否

重新加载表格数据，一般数据变动后必须重新加载一次的：

$(this).jqGrid('setGridParam', { datatype: 'json' }).trigger('reloadGrid');

closeAfterEdit为弹出窗体编辑时，提交后是否关闭编辑框。

editData和delData很重要，这里可以向http handler传递一些参数，比如获取行id传给处理函数：

var sel_id = $(grid_selector).jqGrid('getGridParam', 'selrow');

var value = $(grid_selector).jqGrid('getCell', sel_id, 'UserId');

以上两行代码实现的是获取选择行的'UserId'。

多个选择需要遍历：

var sel_id = [];sel_id = $(grid_selector).jqGrid('getGridParam','selarrrow');var value = '';for (var i = 0; i < sel_id.length; i++) {value = value + ',' + $(grid_selector).jqGrid('getCell', sel_id[i], 'ParkId'); } if (value.charAt(0) == ',') { value = value.substr(1); }

 

http handler处理程序：

 

if (strOperation == "edit"){user.UserId = int.Parse(forms.Get("UserId").ToString());if (CheckUserExist(user.UserCode, user.UserId.ToString())){strResponse = "用户名重复，请确认！";}else{strResponse = UpdateUser(user) ? "用户更新成功！" : "用户更新失败，请确认！";} context.Response.Write(strResponse); } /// 
 /// 更新用户信息 /// 
 ///  /// 
       
        更新成功与否
        private bool UpdateUser(User objuser) { string sql = "UPDATE T_User SET UserCode = '" + objuser.UserCode + "',Password = '" + objuser.Password + "' WHERE UserId=" + objuser.UserId; SQLHelper sqlhelper = new SQLHelper(); return sqlhelper.AddDelUpdate(sql) > 0; } private bool CheckUserExist(string UserCode, string UserId) { string sql = "select * from T_User where UserCode = '" + UserCode + "' and UserId <> " + UserId; SQLHelper sqlhelper = new SQLHelper(); DataTable dt = sqlhelper.Selectinfo(sql); return dt == null || (dt != null && dt.Rows.Count == 1); }

 

删除数据

代码参考编辑数据即可。

http handler处理程序：

 

if (strOperation == "del"){strResponse = DeleteUser(forms.Get("delId").ToString()) ? "删除成功！" :"删除失败，请确认！";}context.Response.Write(strResponse); private bool DeleteUser(string userId){bool flag = true;string[] idlist = userId.Split(','); foreach (var sid in idlist) { string sql = "DELETE FROM T_User WHERE UserId=" + userId; SQLHelper sqlhelper = new SQLHelper(); flag = sqlhelper.AddDelUpdate(sql) > 0; } return flag; }