EasyUI的增删查改(后台ASP.NET)

转自：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,16),
                    new SqlParameter("@UPwd", SqlDbType.VarChar,64),
                    new SqlParameter("@UName", SqlDbType.NVarChar,16),
                    new SqlParameter("@UAge", SqlDbType.Int,4),
                    new SqlParameter("@IsDel", SqlDbType.Bit,1),
                    new SqlParameter("@AddDate", SqlDbType.SmallDateTime)};
            parameters[0].Value = model.CardID;
            parameters[1].Value = model.UPwd;
            parameters[2].Value = model.UName;
            parameters[3].Value = model.UAge;
            parameters[4].Value = model.IsDel;
            parameters[5].Value = model.AddDate;

            object obj = SQLHelper.ExecuteScalar(strSql.ToString(),parameters);
            if (obj == null)
            {
                return 0;
            }
            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,4),
                new SqlParameter("@CardID", SqlDbType.VarChar,16),
                new SqlParameter("@UPwd", SqlDbType.VarChar,64),
                new SqlParameter("@UName", SqlDbType.NVarChar,16),
                new SqlParameter("@UAge", SqlDbType.Int,4),
                //new SqlParameter("@IsDel", SqlDbType.Bit,1),
                //new SqlParameter("@AddDate", SqlDbType.DateTime),
            };
            parameters[0].Value = model.UserID;
            parameters[1].Value = model.CardID;
            parameters[2].Value = model.UPwd;
            parameters[3].Value = model.UName;
            parameters[4].Value = model.UAge;
            //parameters[5].Value = model.IsDel;
            //parameters[6].Value = model.AddDate;

            int rows = SQLHelper.ExecuteNoneQuery(strSql.ToString(),parameters);
            if (rows > 0)
            {
                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 - 1) * size + 1, 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>2?bll.Add(user)>0: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 > 2 ? 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"], 1);
            int page = StringHelper.GetInt(context.Request["rows"], 10);

            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',
                      800,
                     //height: size.height,
                     nowrap: false,
                     collapsible: false,
                     url: actionURL,
                     loadMsg: '正在加载……',
                     singleSelect: true,
                     striped: true,
                     pagesize: 10,
                     pagination: true,
                     queryParams: convertArray($('#fmSearch').serializeArray()),
                     toolbar: CreateToolBar(),

                     columns: [[
                        { title: '编号', field: 'UserID',  80, align: 'center' },
                        { title: '卡号', field: 'CardID',  100, align: 'center' },
                        { title: '姓名', field: 'UName',  160, align: 'center' },
                        { title: '密码', field: 'UPwd',  160, align: 'center' },
                        { title: '年龄', field: 'UAge',  50, align: 'center' },
                        { title: '添加时间', field: 'AddDate',  150,
                            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: 1500
                                    });
                                    $('#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:'添加用户',
                300,
                height:300
            });
         }

        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(")/", ""), 10));
    var month = date.getMonth() + 1 < 10 ? "0" + (date.getMonth() + 1) : date.getMonth() + 1;
    var currentDate = date.getDate() < 10 ? "0" + date.getDate() : date.getDate();
    var hour = date.getHours() < 10 ? "0" + date.getHours() : date.getHours();
    var minu = date.getMinutes() < 10 ? "0" + date.getMinutes() : date.getMinutes();
    var sec = date.getSeconds() < 10 ? "0" + 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 的 下载链接