某某人曾经跟我说,你们做系统不就是增删查改吗。
是啊,很多时候我们就是把用户的数据同后台数据库进行增删查改的沟通。
但是,把CRUD(增删查改)做得好,可能也不是一件很简单的事,
这里要展现的是我现在能写出来最好的Demo了,水平有限,小弟在校学生一枚,还请各位看客多多指教。
Demo前台使用JQuery EasyUI 请求一般处理程序,一般处理程序调用BLL层方法,BLL==>DAL,一个简单三层的效果。
项目结构如图:
一、数据库表结构
这里只创建一个UserInfo表,表中字段有:UserID,CardID,UPwd,UName,UAge,IsDel,AddDate
二、后台处理CRUD
后台处理添加、删除(软删除)、编辑、查询(分页)。其中查询包括:分页和搜索处理所有有点麻烦,
1、先介绍数据访问层(DAL)中的方法
这里是,添加、删除、编辑、分页查询的方法。
前台查询的参数存储在NameValueCollection中,然后在DAL层中的PreSearchParameter方法中把查询参数装配到sql语句和SqlParameter中,
在DAL层中获取的数据是DataTable,然后在BLL层中转换成JSON格式的数据传给EasyUI展示。
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
1 using System; 2 using System.Text; 3 using System.Data; 4 using System.Data.SqlClient; 5 using Dedeyi.Common; 6 using System.Collections; 7 using System.Collections.Generic; 8 using System.Collections.Specialized; 9 10 namespace Dedeyi.ZSF.DAL 11 { 12 public class UserInfoDAL 13 { 14 /// <summary> 15 /// 增加一条数据 16 /// </summary> 17 public int Add(Dedeyi.ZSF.Model.UserInfo model) 18 { 19 StringBuilder strSql=new StringBuilder(); 20 strSql.Append("insert into UserInfo("); 21 strSql.Append("CardID,UPwd,UName,UAge,IsDel,AddDate)"); 22 strSql.Append(" values ("); 23 strSql.Append("@CardID,@UPwd,@UName,@UAge,@IsDel,@AddDate)"); 24 strSql.Append(";select @@IDENTITY"); 25 SqlParameter[] parameters = { 26 new SqlParameter("@CardID", SqlDbType.VarChar,16), 27 new SqlParameter("@UPwd", SqlDbType.VarChar,64), 28 new SqlParameter("@UName", SqlDbType.NVarChar,16), 29 new SqlParameter("@UAge", SqlDbType.Int,4), 30 new SqlParameter("@IsDel", SqlDbType.Bit,1), 31 new SqlParameter("@AddDate", SqlDbType.SmallDateTime)}; 32 parameters[0].Value = model.CardID; 33 parameters[1].Value = model.UPwd; 34 parameters[2].Value = model.UName; 35 parameters[3].Value = model.UAge; 36 parameters[4].Value = model.IsDel; 37 parameters[5].Value = model.AddDate; 38 39 object obj = SQLHelper.ExecuteScalar(strSql.ToString(),parameters); 40 if (obj == null) 41 { 42 return 0; 43 } 44 else 45 { 46 return Convert.ToInt32(obj); 47 } 48 49 } 50 51 /// <summary> 52 /// 删除用户 53 /// </summary> 54 /// <param name="keyid"></param> 55 public void Del(int keyid) 56 { 57 //此处软删除 58 string s = "update UserInfo set IsDel=1 where UserID="+keyid; 59 SQLHelper.ExecuteNoneQuery(s,null); 60 } 61 62 ///<summary> 63 ///更新一条数据 64 ///</summary> 65 public bool Update(Dedeyi.ZSF.Model.UserInfo model) 66 { 67 StringBuilder strSql = new StringBuilder(); 68 69 strSql.Append("update [UserInfo] set "); 70 strSql.Append(" CardID=@CardID,"); 71 strSql.Append(" UPwd=@UPwd,"); 72 strSql.Append(" UName=@UName,"); 73 strSql.Append(" UAge=@UAge "); 74 //strSql.Append(" IsDel=@IsDel,"); 75 //strSql.Append("AddDate=@AddDate"); 76 strSql.Append(" where UserID=@UserID"); 77 78 SqlParameter[] parameters = { 79 new SqlParameter("@UserID", SqlDbType.Int,4), 80 new SqlParameter("@CardID", SqlDbType.VarChar,16), 81 new SqlParameter("@UPwd", SqlDbType.VarChar,64), 82 new SqlParameter("@UName", SqlDbType.NVarChar,16), 83 new SqlParameter("@UAge", SqlDbType.Int,4), 84 //new SqlParameter("@IsDel", SqlDbType.Bit,1), 85 //new SqlParameter("@AddDate", SqlDbType.DateTime), 86 }; 87 parameters[0].Value = model.UserID; 88 parameters[1].Value = model.CardID; 89 parameters[2].Value = model.UPwd; 90 parameters[3].Value = model.UName; 91 parameters[4].Value = model.UAge; 92 //parameters[5].Value = model.IsDel; 93 //parameters[6].Value = model.AddDate; 94 95 int rows = SQLHelper.ExecuteNoneQuery(strSql.ToString(),parameters); 96 if (rows > 0) 97 { 98 return true; 99 } 100 else 101 { 102 return false; 103 } 104 } 105 106 107 //分页相关的 108 #region 109 /// <summary> 110 /// 获取条件查询数据行数 111 /// </summary> 112 /// <param name="nv">关键字,UName,UAge,在前台请求形成Hashtable作为参数时候要用到这个</param> 113 /// <returns>数据行数</returns> 114 public int GetSearchCount(NameValueCollection nv) 115 { 116 string sc; 117 SqlParameter[] ps; 118 PreSearchParameter(nv, out sc, out ps); 119 120 string sql = "select count(0) from UserInfo where " + sc; 121 122 return Convert.ToInt32(SQLHelper.ExecuteScalar(sql, ps)); 123 } 124 125 /// <summary> 126 /// 获取条件查询的分页数据 127 /// </summary> 128 /// <param name="index"></param> 129 /// <param name="size"></param> 130 /// <param name="nv">关键字,UName,UAge,在前台请求形成Hashtable作为参数时候要用到这个</param> 131 /// <returns></returns> 132 public DataTable GetSearchPage(int index, int size,NameValueCollection nv) 133 { 134 string f = "UserID,CardID,UPwd,UName,UAge,AddDate"; 135 string o = "AddDate DESC"; 136 return GetSearchePage(index,size,nv,f,o); 137 } 138 /// <summary> 139 /// 获取分页数据 140 /// </summary> 141 /// <param name="index">当前页</param> 142 /// <param name="size">每一页数据行数</param> 143 /// <param name="ht">查询条件key是字段,value是对应的值</param> 144 /// <param name="fieldList">要返回的字段</param> 145 /// <param name="orderby">排序规则不要order by ,如 AddDate desc,userid asc</param> 146 /// <returns></returns> 147 private DataTable GetSearchePage(int index, int size, NameValueCollection nv, string fieldList,string orderby) 148 { 149 string sc ; //过滤条件 150 SqlParameter[] ps; 151 PreSearchParameter(nv,out sc,out ps); 152 153 string sql = "select * from (select {0},ROW_NUMBER() over(order by {1}) as num from UserInfo where {2}) as tb"; 154 sql += " where num between {3} and {4}"; 155 156 sql = string.Format(sql, fieldList, orderby, sc, (index - 1) * size + 1, index * size); 157 158 return SQLHelper.GetDataTable(sql, ps); 159 } 160 161 /// <summary> 162 /// 把查询参数转换为sql,和SqlParameter 163 /// </summary> 164 /// <param name="ht">关键字,UName,UAge,在前台请求形成Hashtable作为参数时候要用到这个</param> 165 /// <param name="sql"></param> 166 /// <param name="paras"></param> 167 private void PreSearchParameter(NameValueCollection nv, out string sql, out SqlParameter[] paras) 168 { 169 sql = " '1'='1' and IsDel=0"; 170 List<SqlParameter> list = new List<SqlParameter>(); 171 if (!string.IsNullOrEmpty(nv["UName"])) 172 { 173 sql += " and UName like @UName"; 174 list.Add(new SqlParameter("@UName", "%" + nv["UName"] + "%")); 175 } 176 177 int tAge; 178 if (int.TryParse(nv["UAge"],out tAge)) 179 { 180 sql += " and UAge=" + nv["UAge"]; 181 } 182 paras = list.ToArray(); 183 } 184 185 #endregion 186 } 187 }
2、然后在业务逻辑层,做一些逻辑处理(JSON格式转换、添加预设值)
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
3、界面层的一般处理程序,处理一般请求
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
1 using System.Web; 2 using Dedeyi.Common; 3 using Dedeyi.ZSF.Model; 4 using Dedeyi.ZSF.BLL; 5 using System; 6 7 namespace WebApp.Handler 8 { 9 /// <summary> 10 /// UserHandler 的摘要说明 11 /// </summary> 12 public class UserHandler : IHttpHandler,System.Web.SessionState.IRequiresSessionState 13 { 14 private HttpContext context; 15 private UserInfoBLL bll =new UserInfoBLL(); 16 17 public void ProcessRequest(HttpContext context) 18 { 19 context.Response.ContentType = "text/plain"; 20 21 //权限检查 22 // 23 try 24 { 25 string sType = context.Request["reqTypes"]; 26 if (string.IsNullOrEmpty(sType)) 27 { 28 context.Response.Write("参数错误"); 29 return; 30 } 31 this.context = context; 32 33 switch (sType.ToLower()) 34 { 35 case "add": 36 Add(); 37 break; 38 case "del": 39 Del(); 40 break; 41 case "update": 42 Update(); 43 break; 44 case "search": 45 Search(); 46 break; 47 48 } 49 } 50 catch (Exception ex) 51 { 52 context.Response.Write(new ReqMsg(false,ex.Message.ToString())); 53 } 54 } 55 /// <summary> 56 /// 获取用户ID 57 /// </summary> 58 /// <returns></returns> 59 private int GetUserID() 60 { 61 string s = context.Request["uid"]; 62 return StringHelper.GetInt(s); 63 } 64 65 /// <summary> 66 /// 添加方法 67 /// </summary> 68 private void Add() 69 { 70 UserInfo user=new UserInfo(); 71 int n= RequestHelper.FormToModel<UserInfo>(user,context.Request.Form); 72 ReqMsg msg = new ReqMsg(); 73 74 msg.Success=n>2?bll.Add(user)>0:false; 75 76 context.Response.Write(msg.ToString()); 77 78 } 79 80 /// <summary> 81 /// 更新 82 /// </summary> 83 private void Update() 84 { 85 UserInfo user = new UserInfo(); 86 int n = RequestHelper.FormToModel<UserInfo>(user, context.Request.Form); 87 ReqMsg msg = new ReqMsg(); 88 89 msg.Success = n > 2 ? bll.Update(user) : false; 90 91 context.Response.Write(msg.ToString()); 92 } 93 94 /// <summary> 95 /// 删除 96 /// </summary> 97 private void Del() 98 { 99 int i = GetUserID(); 100 bll.Del(i); 101 ReqMsg msg = new ReqMsg(true, "ok"); 102 context.Response.Write(msg.ToString()); 103 } 104 /// <summary> 105 /// 查询 106 /// </summary> 107 private void Search() 108 { 109 int index = StringHelper.GetInt(context.Request["page"], 1); 110 int page = StringHelper.GetInt(context.Request["rows"], 10); 111 112 string s = bll.GetSearchPage(index,page,context.Request.Form); 113 int t = bll.GetSearchCount(context.Request.Form); 114 115 context.Response.Write(RequestHelper.ResponseGridJSON(s,t)); 116 } 117 118 public bool IsReusable 119 { 120 get 121 { 122 return false; 123 } 124 } 125 } 126 }
三、EasyUI前台展示
前台方法主要是AJAX处理增删查改请求,创建工具菜单(可能工具权限显示部分但此次不考虑),
![](https://i-blog.csdnimg.cn/blog_migrate/cdec0645add3fc3c328197dda5c76203.gif)
最终显示效果如图:
JS帮助函数:
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
1 function ChangeShortDateFormat(cellval) { 2 if (!cellval) return ''; 3 var date = new Date(parseInt(cellval.replace("/Date(", "").replace(")/", ""), 10)); 4 var month = date.getMonth() + 1 < 10 ? "0" + (date.getMonth() + 1) : date.getMonth() + 1; 5 var currentDate = date.getDate() < 10 ? "0" + date.getDate() : date.getDate(); 6 var hour = date.getHours() < 10 ? "0" + date.getHours() : date.getHours(); 7 var minu = date.getMinutes() < 10 ? "0" + date.getMinutes() : date.getMinutes(); 8 var sec = date.getSeconds() < 10 ? "0" + date.getSeconds() : date.getSeconds(); 9 10 return date.getFullYear() + "-" + month + "-" + currentDate; //+ " " + hour + ":" + minu + ":" + sec; 11 12 } 13 14 //表单序列化有转JSON格式 15 function convertArray(o) { 16 var v = {}; 17 for (var i in o) { 18 if (o[i].name != '__VIEWSTATE') { 19 if (typeof (v[o[i].name]) == 'undefined') 20 v[o[i].name] = o[i].value; 21 else 22 v[o[i].name] += "," + o[i].value; 23 } 24 } 25 return v; 26 }