转自：http://www.cnblogs.com/dedeyi/archive/2013/04/22/3035057.html
某某人曾经跟我说，你们做系统不就是增删查改吗。
是啊，很多时候我们就是把用户的数据同后台数据库进行增删查改的沟通。
但是，把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展示。
using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Dedeyi.Common;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized; namespace Dedeyi.ZSF.DAL
{
public class UserInfoDAL
{
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Dedeyi.ZSF.Model.UserInfo model)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("insert into UserInfo(");
strSql.Append("CardID,UPwd,UName,UAge,IsDel,AddDate)");
strSql.Append(" values (");
strSql.Append("@CardID,@UPwd,@UName,@UAge,@IsDel,@AddDate)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters = {
new SqlParameter("@CardID", SqlDbType.VarChar,),
new SqlParameter("@UPwd", SqlDbType.VarChar,),
new SqlParameter("@UName", SqlDbType.NVarChar,),
new SqlParameter("@UAge", SqlDbType.Int,),
new SqlParameter("@IsDel", SqlDbType.Bit,),
new SqlParameter("@AddDate", SqlDbType.SmallDateTime)};
parameters[].Value = model.CardID;
parameters[].Value = model.UPwd;
parameters[].Value = model.UName;
parameters[].Value = model.UAge;
parameters[].Value = model.IsDel;
parameters[].Value = model.AddDate; object obj = SQLHelper.ExecuteScalar(strSql.ToString(),parameters);
if (obj == null)
{
return ;
}
else
{
return Convert.ToInt32(obj);
} } /// <summary>
/// 删除用户
/// </summary>
/// <param name="keyid"></param>
public void Del(int keyid)
{
//此处软删除
string s = "update UserInfo set IsDel=1 where UserID="+keyid;
SQLHelper.ExecuteNoneQuery(s,null);
} ///<summary>
///更新一条数据
///</summary>
public bool Update(Dedeyi.ZSF.Model.UserInfo model)
{
StringBuilder strSql = new StringBuilder(); strSql.Append("update [UserInfo] set ");
strSql.Append(" CardID=@CardID,");
strSql.Append(" UPwd=@UPwd,");
strSql.Append(" UName=@UName,");
strSql.Append(" UAge=@UAge ");
//strSql.Append(" IsDel=@IsDel,");
//strSql.Append("AddDate=@AddDate");
strSql.Append(" where UserID=@UserID"); SqlParameter[] parameters = {
new SqlParameter("@UserID", SqlDbType.Int,),
new SqlParameter("@CardID", SqlDbType.VarChar,),
new SqlParameter("@UPwd", SqlDbType.VarChar,),
new SqlParameter("@UName", SqlDbType.NVarChar,),
new SqlParameter("@UAge", SqlDbType.Int,),
//new SqlParameter("@IsDel", SqlDbType.Bit,1),
//new SqlParameter("@AddDate", SqlDbType.DateTime),
};
parameters[].Value = model.UserID;
parameters[].Value = model.CardID;
parameters[].Value = model.UPwd;
parameters[].Value = model.UName;
parameters[].Value = model.UAge;
//parameters[5].Value = model.IsDel;
//parameters[6].Value = model.AddDate; int rows = SQLHelper.ExecuteNoneQuery(strSql.ToString(),parameters);
if (rows > )
{
return true;
}
else
{
return false;
}
} //分页相关的
#region
/// <summary>
/// 获取条件查询数据行数
/// </summary>
/// <param name="nv">关键字,UName,UAge,在前台请求形成Hashtable作为参数时候要用到这个</param>
/// <returns>数据行数</returns>
public int GetSearchCount(NameValueCollection nv)
{
string sc;
SqlParameter[] ps;
PreSearchParameter(nv, out sc, out ps); string sql = "select count(0) from UserInfo where " + sc; return Convert.ToInt32(SQLHelper.ExecuteScalar(sql, ps));
} /// <summary>
/// 获取条件查询的分页数据
/// </summary>
/// <param name="index"></param>
/// <param name="size"></param>
/// <param name="nv">关键字,UName,UAge,在前台请求形成Hashtable作为参数时候要用到这个</param>
/// <returns></returns>
public DataTable GetSearchPage(int index, int size,NameValueCollection nv)
{
string f = "UserID,CardID,UPwd,UName,UAge,AddDate";
string o = "AddDate DESC";
return GetSearchePage(index,size,nv,f,o);
}
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="index">当前页</param>
/// <param name="size">每一页数据行数</param>
/// <param name="ht">查询条件key是字段，value是对应的值</param>
/// <param name="fieldList">要返回的字段</param>
/// <param name="orderby">排序规则不要order by ,如 AddDate desc,userid asc</param>
/// <returns></returns>
private DataTable GetSearchePage(int index, int size, NameValueCollection nv, string fieldList,string orderby)
{
string sc ; //过滤条件
SqlParameter[] ps;
PreSearchParameter(nv,out sc,out ps); string sql = "select * from (select {0},ROW_NUMBER() over(order by {1}) as num from UserInfo where {2}) as tb";
sql += " where num between {3} and {4}"; sql = string.Format(sql, fieldList, orderby, sc, (index - ) * size + , index * size); return SQLHelper.GetDataTable(sql, ps);
} /// <summary>
/// 把查询参数转换为sql，和SqlParameter
/// </summary>
/// <param name="ht">关键字,UName,UAge,在前台请求形成Hashtable作为参数时候要用到这个</param>
/// <param name="sql"></param>
/// <param name="paras"></param>
private void PreSearchParameter(NameValueCollection nv, out string sql, out SqlParameter[] paras)
{
sql = " '1'='1' and IsDel=0";
List<SqlParameter> list = new List<SqlParameter>();
if (!string.IsNullOrEmpty(nv["UName"]))
{
sql += " and UName like @UName";
list.Add(new SqlParameter("@UName", "%" + nv["UName"] + "%"));
} int tAge;
if (int.TryParse(nv["UAge"],out tAge))
{
sql += " and UAge=" + nv["UAge"];
}
paras = list.ToArray();
} #endregion
}
}
2、然后在业务逻辑层，做一些逻辑处理(JSON格式转换、添加预设值)
using Dedeyi.ZSF.DAL;
using Dedeyi.ZSF.Model;
using Dedeyi.Common;
using System;
using System.Collections.Specialized; namespace Dedeyi.ZSF.BLL
{
public class UserInfoBLL
{
UserInfoDAL dal=new UserInfoDAL(); /// <summary>
/// 添加
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public int Add(UserInfo user)
{
user.AddDate = DateTime.Now;
user.IsDel = false; return dal.Add(user);
} /// <summary>
/// 删除用户
/// </summary>
/// <param name="keyid"></param>
public void Del(int keyid)
{
dal.Del(keyid);
} ///<summary>
///更新一条数据
///</summary>
public bool Update(Dedeyi.ZSF.Model.UserInfo model)
{
return dal.Update(model);
} //分页相关的
#region
/// <summary>
/// 获取条件查询数据行数
/// </summary>
/// <param name="ht">关键字,UName,UAge,在前台请求形成Hashtable作为参数时候要用到这个</param>
/// <returns>数据行数</returns>
public int GetSearchCount(NameValueCollection ht)
{
return dal.GetSearchCount(ht);
} /// <summary>
/// 获取条件查询的分页数据
/// </summary>
/// <param name="index"></param>
/// <param name="size"></param>
/// <param name="ht">关键字,UName,UAge,在前台请求形成Hashtable作为参数时候要用到这个</param>
/// <returns>json格式的数据集</returns>
public string GetSearchPage(int index, int size,NameValueCollection ht)
{
return JSONHelper.DataTableToJson(dal.GetSearchPage(index,size,ht));
} #endregion }
}
3、界面层的一般处理程序，处理一般请求
using System.Web;
using Dedeyi.Common;
using Dedeyi.ZSF.Model;
using Dedeyi.ZSF.BLL;
using System; namespace WebApp.Handler
{
/// <summary>
/// UserHandler 的摘要说明
/// </summary>
public class UserHandler : IHttpHandler,System.Web.SessionState.IRequiresSessionState
{
private HttpContext context;
private UserInfoBLL bll =new UserInfoBLL(); public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain"; //权限检查
//
try
{
string sType = context.Request["reqTypes"];
if (string.IsNullOrEmpty(sType))
{
context.Response.Write("参数错误");
return;
}
this.context = context; switch (sType.ToLower())
{
case "add":
Add();
break;
case "del":
Del();
break;
case "update":
Update();
break;
case "search":
Search();
break; }
}
catch (Exception ex)
{
context.Response.Write(new ReqMsg(false,ex.Message.ToString()));
}
}
/// <summary>
/// 获取用户ID
/// </summary>
/// <returns></returns>
private int GetUserID()
{
string s = context.Request["uid"];
return StringHelper.GetInt(s);
} /// <summary>
/// 添加方法
/// </summary>
private void Add()
{
UserInfo user=new UserInfo();
int n= RequestHelper.FormToModel<UserInfo>(user,context.Request.Form);
ReqMsg msg = new ReqMsg(); msg.Success=n>?bll.Add(user)>:false; context.Response.Write(msg.ToString()); } /// <summary>
/// 更新
/// </summary>
private void Update()
{
UserInfo user = new UserInfo();
int n = RequestHelper.FormToModel<UserInfo>(user, context.Request.Form);
ReqMsg msg = new ReqMsg(); msg.Success = n > ? bll.Update(user) : false; context.Response.Write(msg.ToString());
} /// <summary>
/// 删除
/// </summary>
private void Del()
{
int i = GetUserID();
bll.Del(i);
ReqMsg msg = new ReqMsg(true, "ok");
context.Response.Write(msg.ToString());
}
/// <summary>
/// 查询
/// </summary>
private void Search()
{
int index = StringHelper.GetInt(context.Request["page"], );
int page = StringHelper.GetInt(context.Request["rows"], ); string s = bll.GetSearchPage(index,page,context.Request.Form);
int t = bll.GetSearchCount(context.Request.Form); context.Response.Write(RequestHelper.ResponseGridJSON(s,t));
} public bool IsReusable
{
get
{
return false;
}
}
}
}
三、EasyUI前台展示
前台方法主要是AJAX处理增删查改请求，创建工具菜单(可能工具权限显示部分但此次不考虑)，
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApp.Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link href="Scripts/EasyUI/themes/icon.css" rel="stylesheet" type="text/css" />
<link href="Scripts/EasyUI/themes/default/easyui.css" rel="stylesheet" type="text/css" />
<script src="Scripts/EasyUI/jquery-1.8.0.min.js" type="text/javascript"></script>
<script src="Scripts/EasyUI/jquery.easyui.min.js" type="text/javascript"></script>
<script src="Scripts/EasyUI/easyui-lang-zh_CN.js" type="text/javascript"></script>
<script src="Scripts/Common.js" type="text/javascript"></script> <script type="text/javascript">
var actionURL="handler/UserHandler.ashx";
var CRUD =
{
Init: function () {
$('#dataTB').datagrid(
{
title: '用户列表',
iconCls: 'icon-reload',
width: ,
//height: size.height,
nowrap: false,
collapsible: false,
url: actionURL,
loadMsg: '正在加载……',
singleSelect: true,
striped: true,
pagesize: ,
pagination: true,
queryParams: convertArray($('#fmSearch').serializeArray()),
toolbar: CreateToolBar(), columns: [[
{ title: '编号', field: 'UserID', width: , align: 'center' },
{ title: '卡号', field: 'CardID', width: , align: 'center' },
{ title: '姓名', field: 'UName', width: , align: 'center' },
{ title: '密码', field: 'UPwd', width: , align: 'center' },
{ title: '年龄', field: 'UAge', width: , align: 'center' },
{ title: '添加时间', field: 'AddDate', width: ,
formatter: function (val, rec) {
return ChangeDateFormat(val);
} }
]]
});
},
Add: function () {
$('#dialogAdd').dialog('open');
$('#txt_editType').val('add'); //标示为添加
},
Del: function () {
var r = $('#dataTB').datagrid('getSelected');
if (r) {
$.messager.confirm('删除提示', '你确认删除这一条数据吗？', function (d) {
if (d) {
$.post(actionURL, { "reqTypes": "del", "uid": r["UserID"] }, function (d) {
var res = $.parseJSON(d);
if (res.Success) {
$.messager.show(
{
title: '系统提示',
msg: '删除成功！',
timeout:
});
$('#dataTB').datagrid('reload', convertArray($('#fmSearch').serializeArray()));
} else {
$.messager.alert('错误', res.Msg);
}
});
}
});
} else {
$.messager.alert('提示', '请选择一行数据!');
} },
Update: function () {
var r = $('#dataTB').datagrid('getSelected');
if (r == null) { $.messager.alert('提示', '请选择一行数据'); return; };
$('#dialogAdd').dialog('open');
$('#fmAdd').form('load', r);
$('#txt_editType').val('update');
}
}; function InitForm() {
$('#dialogAdd').dialog(
{
closed: true,
title:'添加用户',
width:,
height:
});
} function CreateToolBar() {
var a = [];
var isReflesh = true;
var isAdd = true;
var isDel = true;
var isUpdate = true;
if (isReflesh) {
a.push({
text: '刷新',
iconCls: 'icon-reload',
handler: function () { CRUD.Init();
}
}); a.push('-'); } if (isAdd) {
a.push({
text: '添加',
iconCls: 'icon-add',
handler: function () {
CRUD.Add();
}
}); a.push('-');
} if (isUpdate) {
a.push({
text: '编辑',
iconCls: 'icon-edit',
handler: function () {
CRUD.Update();
}
}); a.push('-');
} if (isDel) {
a.push({
text: '删除',
iconCls: 'icon-cut',
handler: function () { CRUD.Del();
}
}); a.push('-');
}
return a;
}
</script>
<script type="text/javascript"> $(function () { CRUD.Init(); InitForm(); //查询事件
$('#a_search').click(function () {
$('#dataTB').datagrid('reload', convertArray($('#fmSearch').serializeArray())); });
//确定添加
$('#a_AddOK').click(function () {
if ($('#fmAdd').form('validate')) {
var send = convertArray($("#fmAdd").serializeArray());
$.post(actionURL, send, function (d) {
$('#dataTB').datagrid('reload', convertArray($('#fmSearch').serializeArray()));
var res = $.parseJSON(d);
if (res.Success) {
$.messager.alert('提示', '操作成功');
$('#fmAdd').form('clear');
} else {
$.messager.alert('错误', res.Msg);
}
});
} }); //取消
$('#a_AddCancle').click(function () {
$('#fmAdd').form('clear');
$('#dialogAdd').dialog('close'); }) });
</script> </head>
<body>
<div id="dialogAdd" >
<form id="fmAdd" style="text-align:center;">
<br /><br />
<input type="hidden" name="reqTypes" id="txt_editType" />
<input type="hidden" name="UserID" id="txt_editID"/>
用户名<input type="text" name="UName" required="true" class="easyui-validatebox" /><br /><br />
卡号<input type="text" name="CardID" /><br /><br />
年龄<input type="text" name="UAge" /><br /><br />
密码<input type="text" name="UPwd" /><br /><br />
<a class="easyui-linkbutton" id="a_AddOK">确定</a>
<a class="easyui-linkbutton" id="a_AddCancle">取消</a>
</form>
</div>
<div>
<form id="fmSearch">
<input type="hidden" name="reqTypes" value="search" />
用户名<input type="text" name="UName" />
年龄<input type="text" name="UAge" />
<a class="easyui-linkbutton" id="a_search">查询</a>
</form>
</div>
<table id="dataTB"></table>
</body>
</html>
最终显示效果如图：
JS帮助函数：
function ChangeShortDateFormat(cellval) {
if (!cellval) return '';
var date = new Date(parseInt(cellval.replace("/Date(", "").replace(")/", ""), ));
var month = date.getMonth() + < ? "" + (date.getMonth() + ) : date.getMonth() + ;
var currentDate = date.getDate() < ? "" + date.getDate() : date.getDate();
var hour = date.getHours() < ? "" + date.getHours() : date.getHours();
var minu = date.getMinutes() < ? "" + date.getMinutes() : date.getMinutes();
var sec = date.getSeconds() < ? "" + date.getSeconds() : date.getSeconds();
return date.getFullYear() + "-" + month + "-" + currentDate; //+ " " + hour + ":" + minu + ":" + sec;
}
//表单序列化有转JSON格式
function convertArray(o) {
var v = {};
for (var i in o) {
if (o[i].name != '__VIEWSTATE') {
if (typeof (v[o[i].name]) == 'undefined')
v[o[i].name] = o[i].value;
else
v[o[i].name] += "," + o[i].value;
}
}
return v;
}********谢谢支持，感谢批评建议，努力学习中******************************
Demo 的 下载链接