C#easyui实现增删改查

        上文，我们使用pb，c#WinForm，c#WebForm分别实现了增删改查，本文，我们C#easyui来实现一下。首先，说明一点，WebForm和easyui基本是一样，代码基本雷同，就是一个使用了服务器控件，一个使用easyui，排版有点不一样，服务器端的代码没有什么变化。数据库仍然采用sqlserver，表仍然采用table_test，字段不说了，参考前文。

首先，在vs中创建一个ASP.NET空网站，wsjquery

其次，和上文一样，创建数据库操作类db.cs

这次，该文件增加了操作存储过程的两个函数，因为easyui需要分页显示，本文通过存储过程实现sqlserver的分页。

using System;using System.Data;using System.Collections.Generic;using System.Linq;using System.Web;using System.Data.SqlClient;/// <summary>///db 的摘要说明/// </summary>public class db{    public static string connectionstring = "server=localhost;database=db;uid=sa;pwd=password";    public db()    {////TODO: 在此处添加构造函数逻辑//    }    public static SqlConnection OpenDB()    {        try        {            SqlConnection oConn = new SqlConnection(connectionstring);            oConn.Open();            return oConn;        }        catch        {            throw;        }    }    public static void CloseDB(SqlConnection oConn)    {        try        {            oConn.Close();        }        catch        {            throw;        }    }    public static DataSet Execute(string strCommandString)    {        try        {            SqlConnection oConn = OpenDB();            DataSet oDataSet = new DataSet();            SqlDataAdapter oDataAdapter = new SqlDataAdapter(strCommandString, oConn);            oDataAdapter.Fill(oDataSet);            CloseDB(oConn);            return oDataSet;        }        catch        {            throw;        }    }    public static int ExecuteNonQuery(string strCommandString)    {        int li_count = 0;        SqlConnection oConn = null;        SqlCommand oComm = null;        try        {            oConn = new SqlConnection(connectionstring);            oComm = new SqlCommand();            oConn.Open();            oComm.Connection = oConn;            oComm.CommandText = strCommandString;            li_count = oComm.ExecuteNonQuery();            return li_count;        }        catch        {            return 0;        }        finally        {            if (oConn != null) oConn.Close();        }    }    public static int ExecuteReid(string strCommandString)    {        int li_count = 0;        SqlConnection oConn = null;        SqlCommand oComm = null;        try        {            oConn = new SqlConnection(connectionstring);            oComm = new SqlCommand();            oConn.Open();            oComm.Connection = oConn;            oComm.CommandText = strCommandString;            li_count = Convert.ToInt32(oComm.ExecuteScalar().ToString());            return li_count;        }        catch        {            return 0;        }        finally        {            if (oConn != null) oConn.Close();        }    }    /// <summary>    /// 执行存储过程    /// </summary>    /// <param name="storedProcName">存储过程名</param>    /// <param name="parameters">存储过程参数</param>    /// <param name="tableName">DataSet结果中的表名</param>    /// <returns>DataSet</returns>    public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)    {        using (SqlConnection connection = new SqlConnection(connectionstring))        {            DataSet dataSet = new DataSet();            connection.Open();            new SqlDataAdapter { SelectCommand = BuildQueryCommand(connection, storedProcName, parameters) }.Fill(dataSet, tableName);            connection.Close();            return dataSet;        }    }    /// <summary>    /// 构建 SqlCommand 对象，返回结果集    /// </summary>    /// <param name="connection">数据库连接</param>    /// <param name="storedProcName">存储过程名</param>    /// <param name="parameters">存储过程参数</param>    /// <returns>SqlCommand</returns>    private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)    {        SqlCommand command = new SqlCommand(storedProcName, connection)        {            CommandType = CommandType.StoredProcedure        };        foreach (SqlParameter parameter in parameters)        {            if (parameter != null)            {                if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null))                {                    parameter.Value = DBNull.Value;                }                command.Parameters.Add(parameter);            }        }        return command;    }}

第三步：引入easyui的文件，排版default.aspx

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %><!DOCTYPE><html xmlns="http://www.w3.org/1999/xhtml"><head>    <title></title>    <link href="css/demo.css" type="text/css" rel="stylesheet">    <link rel="stylesheet" type="text/css" href="css/demo.css">    <link rel="stylesheet" type="text/css" href="jquery/themes/default/easyui.css">     <link rel="stylesheet" type="text/css" href="jquery/themes/icon.css">        <script type="text/javascript" src="jquery/jquery-1.8.0.min.js"></script>    <script type="text/javascript" src="jquery/jquery.easyui.min.js"></script>    <script type="text/javascript" src="jquery/locale/easyui-lang-zh_CN.js"></script><script type="text/javascript">    $(function () {        initgrid();    });    function initgrid() {        $('#t_dg').datagrid({            url: location.href,            idField: 'dg_dbid',            height: 420,            striped: true,            loadMsg: '数据正在加载,请耐心的等待...',            remoteSort: true,            singleSelect: true,            sortName: 'dg_dbid',            sortOrder: 'desc',            columns: [[                      { field: 'cbx', checkbox: true },                      { field: 'dg_xm1', title: '姓名', width: 200, align: 'center', sortable: true },                      { field: 'dg_xm2', title: '性别', width: 100, align: 'center', sortable: true },                      { field: 'dg_xm3', title: '电话', width: 200, align: 'center' },                                           { field: 'dg_dbid', title: '操作', width: 100, align: 'center',                         formatter: function (value, rec) {                             return '<a style="color:blue" href="javascript:;" onclick="EditData(' + value + ');$(this).parent().click();return false;">修改</a>';                            }                        }                ]],            queryParams: { "action": "query" },            pagination: true,            pageNumber: 1,             pageSize: 10,            rownumbers: true        });    }    function initcx() {        var cx = $("#txt_cx").val();        $('#t_dg').datagrid('options').queryParams.cx = cx;             $('#t_dg').datagrid('reload');    }    function OpenWin() {        $("#edit").dialog("open");        $("#edit-buttons a:first").attr("onclick", "Add(0); return false;");        $("#xm1").val("");        $("#xm3").val("");    }    //提交按钮事件    function Add(uid) {        if (!$("#form1").form("validate")) {            return;        }        var json = $('#form1').serialize();                  $.post(location.href, json, function (data) {            $.messager.alert('提示', data, 'info', function () {                if (data.indexOf("成功") > 0) {                    $("#t_dg").datagrid("reload");                    $("#edit").dialog("close");                }            });        });    }    //修改链接 事件    function EditData(uid) {        $("#edit").dialog("open");        $("#btn_add").attr("onclick", "Add(" + uid + "); return false;")        $.post(location.href, { "action": "queryone", "dbid": uid }, function (data) {            var dataObj = eval("(" + data + ")"); //转换为json对象            $("#form1").form('load', dataObj);                    });    }    //删除按钮事件    function DelData(id) {        $.messager.confirm('提示', '确认删除？', function (r) {            if (r) {                var selected = "";                if (id <= 0) {                    $($('#t_dg').datagrid('getSelections')).each(function () {                        selected += this.dg_dbid + ",";                    });                    selected = selected.substr(0, selected.length - 1);                    if (selected == "") {                        $.messager.alert('提示', '请选择要删除的数据！', 'info');                        return;                    }                }                else {                    selected = id;                }                $.post(location.href, { "action": "del", "cbx_select": selected }, function (data) {                    $.messager.alert('提示', data, 'info', function () { $("#t_dg").datagrid("reload"); });                });            }        });    }    window.onload = function () {        var searchcol = document.getElementsByName("searchcol");        var data = $('#searchcol').combobox('getData');        if (data.length > 0) {            $('#searchcol').combobox('select', data[0].mc);        }    }    </script> </head><body>    <div id="lay" class="easyui-layout" fit=true>   <div region="center" title="信息主页" iconCls="icon-main" style="padding:5px" >         <table id="t_tool" cellspacing="2px" cellpadding="0" border="0" style="width: 100%">             <tr>                  <td style="height: 3px" colspan="2"></td>             </tr>             <tr>                  <td valign="middle" nowrap align="left" class=spannone>                      查询条件：<input  id="txt_cx" name="txt_cx" type="text" />                                            <a href="#" onclick="initcx();return false;" id="a_cx" class="easyui-linkbutton" iconcls="icon-search">查询</a>                                       </td>                  <td valign="middle" nowrap align="right">                        <a href="#" onclick="OpenWin();return false;" id="a_add" class="easyui-linkbutton" iconcls="icon-add">增加</a>                        <a href="#" onclick="DelData(0);return false;" id="a_del" class="easyui-linkbutton" iconcls="icon-cancel">删除</a>                   </td>              </tr>              <tr>                  <td style="height: 3px" colspan="2"></td>             </tr>         </table>                <table id="t_dg"></table>                </div>    </div>        <div id="edit" class="easyui-dialog" title="信息维护" style="width: 400px; height: 200px;" modal="true" closed="true" buttons="#edit-buttons">         <form id="form1" name="form1" method="post">            <input id="action"  name="action" value="update" type="hidden"  />        <input id="dbid"  name="dbid" type="hidden"  />            <table id="Table2" cellspacing="2" cellpadding="1" width="95%" border="0" align="center" class="px12">                 <tr>                    <td style="height: 5px" colspan="2"></td>                </tr> <TR>    <td width="20%" height="20" class="tdlabel"><span class="spanerror">*</span>姓名：</td>    <TD width="80%"><input  id="xm1" name="xm1" type="text" class="easyui-validatebox"  required="true" maxlength="20" /></TD></TR>                        <TR>                    <td width="20%" height="20" class="tdlabel">性别：</td>    <TD width="80%">        <input type="radio" checked="checked"  name="xm2" value="男" />男                         <input type="radio" name="xm2" value="女" />女                               </TD>                    </TR><TR>    <td width="20%" height="20" class="tdlabel">电话：</td>    <TD width="80%">       <input id="xm3" name="xm3" type="text" class="easyui-validatebox"  maxlength="100" /></TD></TR>   </TABLE>          </form>    </div>      <div id="edit-buttons">        <a id="btn_add"   class="easyui-linkbutton">提交</a>         <a id="btn_close"  class="easyui-linkbutton" onclick="$('#edit').dialog('close');return false;">取消</a>    </div> </body></html>

第四步：配置好demo.css

.px12 {font-family: "宋体", Arial;font-size: 12px;text-decoration: none;color: #000000;}.tdlabel{background-color:Silver;text-align:right;}

第五步：编写default.aspx.cs文件

using System;using System.Data;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data.SqlClient;using System.Text;public partial class _Default : System.Web.UI.Page {    protected void Page_Load(object sender, EventArgs e)    {        string action = "";        if (Request.Form["action"] != "")            action = Request.Form["action"];        switch (action)        {            case "query"://查询数据                QueryData();                break;            case "queryone"://查询指定dbid 的数据，修改时用                QueryOneData();                break;            case "update"://提交数据，添加或修改                UpdateData();                break;            case "del"://删除数据                DelData();                break;            default:                break;        }       }    /// <summary>    /// 查询数据    /// </summary>    private void QueryData()    {        int page = Request.Form["page"] != "" ? Convert.ToInt32(Request.Form["page"]) : 0;        int size = Request.Form["rows"] != "" ? Convert.ToInt32(Request.Form["rows"]) : 0;        string sort = Request.Form["sort"] != "" ? Request.Form["sort"] : "";        string order = Request.Form["order"] != "" ? Request.Form["order"] : "";        if (page < 1) return;        string orderField = sort.Replace("dg_", "");        Boolean lb_orderby = false;        if (order.Equals("asc")) lb_orderby = true;        string strWhere = GetWhere();        DataSet ds = GetList("table_test", "*", orderField, size, page, false, lb_orderby, strWhere);        DataSet ds2 = GetList("table_test", "*", orderField, size, page, true, lb_orderby, strWhere);        int count = Convert.ToInt16(ds2.Tables[0].Rows[0][0]);//获取总数        string strJSON = CreateJsonParameters(ds.Tables[0], true, count);              Response.Write(strJSON);        Response.End();    }    #region 查询数据    /// <summary>    /// 组合搜索条件    /// </summary>    /// <returns></returns>    private string GetWhere()    {        string ls_where = "1=1";            string cx = Request.Form["cx"] != "" ? Request.Form["cx"] : string.Empty;        if ( cx != "")        {            ls_where = "  (xm1 like '%" + cx + "%') or (xm2 like '%" + cx + "%') or (xm3 like '%" + cx + "%')";             }        return ls_where;    }    #endregion    public static DataSet GetList(string tableName, string getFields, string orderName, int pageSize, int pageIndex, bool isGetCount, bool orderType, string strWhere)    {        SqlParameter[] parameters = {                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),                    new SqlParameter("@strGetFields", SqlDbType.VarChar, 1000),                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),                    new SqlParameter("@PageSize", SqlDbType.Int),                    new SqlParameter("@PageIndex", SqlDbType.Int),                    new SqlParameter("@doCount", SqlDbType.Bit),                    new SqlParameter("@OrderType", SqlDbType.Bit),                    new SqlParameter("@strWhere", SqlDbType.VarChar, 1500)                                                 };        parameters[0].Value = tableName;        parameters[1].Value = getFields;        parameters[2].Value = orderName;        parameters[3].Value = pageSize;        parameters[4].Value = pageIndex;        parameters[5].Value = isGetCount ? 1 : 0;        parameters[6].Value = orderType ? 1 : 0;        parameters[7].Value = strWhere;        return db.RunProcedure("pro_pageList", parameters, "ds");    }    private void UpdateData()    {        int dbid = Request.Form["dbid"] != "" ? Convert.ToInt32(Request.Form["dbid"]) : 0;        HttpContext context = HttpContext.Current;        string writeMsg = "操作失败！";        int li_ret;        if (dbid < 1)        {            string ls_sql = "INSERT INTO table_test ( xm1,xm2,xm3 )  VALUES (  @@xm1, @@xm2, @@xm3 ); SELECT dbid FROM table_test WHERE (dbid = @@IDENTITY)";            ls_sql = ls_sql.Replace("@@xm1", "'" + Request.Form["xm1"] + "'");            ls_sql = ls_sql.Replace("@@xm2", "'" + Request.Form["xm2"] + "'");            ls_sql = ls_sql.Replace("@@xm3", "'" + Request.Form["xm3"] + "'");            li_ret = db.ExecuteReid(ls_sql);            if (li_ret > 0)            {                writeMsg = "新增成功！";            }            else            {                writeMsg = "新增失败！";            }                  }        else        {            string ls_sql = "update table_test set xm1=@@xm1,xm2=@@xm2,xm3=@@xm3  where dbid = @@dbid";            ls_sql = ls_sql.Replace("@@dbid", "'" + Request.Form["dbid"] + "'");            ls_sql = ls_sql.Replace("@@xm1", "'" + Request.Form["xm1"] + "'");            ls_sql = ls_sql.Replace("@@xm2", "'" + Request.Form["xm2"] + "'");            ls_sql = ls_sql.Replace("@@xm3", "'" + Request.Form["xm3"] +"'");            li_ret = db.ExecuteNonQuery(ls_sql);            if (li_ret > 0)            {                writeMsg = "修改成功！";            }            else            {                writeMsg = "修改失败！";            }        }        Response.Clear();        Response.Write(writeMsg);        Response.End();    }     #region 删除指定dbid 的数据    /// <summary>    /// 删除数据    /// </summary>    private void DelData()    {        string writeMsg = "删除失败！";        string selectID = Request.Form["cbx_select"] != "" ? Request.Form["cbx_select"] : "";        if (selectID != string.Empty && selectID != "0")        {            string ls_sql = "delete table_test where dbid = " + selectID;            if (db.ExecuteNonQuery(ls_sql) > 0)            {                writeMsg = "删除成功！";            }            else            {                writeMsg = "删除失败！";            }        }        Response.Clear();        Response.Write(writeMsg);        Response.End();    }    #endregion    #region 查询指定ID 的数据    /// <summary>    /// 获取指定ID的数据    /// </summary>    private void QueryOneData()    {        int ll_dbid = Request.Form["dbid"] != "" ? Convert.ToInt32(Request.Form["dbid"]) : 0;        DataSet ds = db.Execute("select xm1,xm2,xm3,dbid from table_test where dbid =" + ll_dbid);        string strJSON = CreateJsonOne(ds.Tables[0], false);        Response.Clear();        Response.Write(strJSON);        Response.End();    }    #endregion    public static string CreateJsonOne(DataTable dt, bool displayCount)    {        StringBuilder JsonString = new StringBuilder();        //Exception Handling                if (dt != null && dt.Rows.Count > 0)        {            for (int i = 0; i < dt.Rows.Count; i++)            {                JsonString.Append("{ ");                for (int j = 0; j < dt.Columns.Count; j++)                {                    if (j < dt.Columns.Count - 1)                    {                        JsonString.Append(dt.Columns[j].ColumnName.ToString().ToLower() + ":" + "\"" + dt.Rows[i][j].ToString() + "\",");                    }                    else if (j == dt.Columns.Count - 1)                    {                        JsonString.Append(dt.Columns[j].ColumnName.ToString().ToLower() + ":" + "\"" + dt.Rows[i][j].ToString() + "\"");                    }                }                if (i == dt.Rows.Count - 1)                {                    JsonString.Append("} ");                }                else                {                    JsonString.Append("}, ");                }            }            return JsonString.ToString();        }        else        {            return null;        }    }    /// <summary>    /// 将DataTable中的数据转换成JSON格式    /// </summary>    /// <param name="dt">数据源DataTable</param>    /// <param name="displayCount">是否输出数据总条数</param>    /// <param name="totalcount">JSON中显示的数据总条数</param>    /// <returns></returns>    public static string CreateJsonParameters(DataTable dt, bool displayCount, int totalcount)    {        StringBuilder JsonString = new StringBuilder();        //Exception Handling                if (dt != null)        {            JsonString.Append("{ ");            JsonString.Append("\"rows\":[ ");            for (int i = 0; i < dt.Rows.Count; i++)            {                JsonString.Append("{ ");                for (int j = 0; j < dt.Columns.Count; j++)                {                    if (j < dt.Columns.Count - 1)                    {                        //if (dt.Rows[i][j] == DBNull.Value) continue;                        if (dt.Columns[j].DataType == typeof(bool))                        {                            JsonString.Append("\"dg_" + dt.Columns[j].ColumnName.ToLower() + "\":" +                                              dt.Rows[i][j].ToString().ToLower() + ",");                        }                        else if (dt.Columns[j].DataType == typeof(string))                        {                            JsonString.Append("\"dg_" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" +                                              dt.Rows[i][j].ToString().Replace("\"", "\\\"") + "\",");                        }                        else                        {                            JsonString.Append("\"dg_" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" + dt.Rows[i][j] + "\",");                        }                    }                    else if (j == dt.Columns.Count - 1)                    {                        //if (dt.Rows[i][j] == DBNull.Value) continue;                        if (dt.Columns[j].DataType == typeof(bool))                        {                            JsonString.Append("\"dg_" + dt.Columns[j].ColumnName.ToLower() + "\":" +                                              dt.Rows[i][j].ToString().ToLower());                        }                        else if (dt.Columns[j].DataType == typeof(string))                        {                            JsonString.Append("\"dg_" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" +                                              dt.Rows[i][j].ToString().Replace("\"", "\\\"") + "\"");                        }                        else                        {                            JsonString.Append("\"dg_" + dt.Columns[j].ColumnName.ToLower() + "\":" + "\"" + dt.Rows[i][j] + "\"");                        }                    }                }                /*end Of String*/                if (i == dt.Rows.Count - 1)                {                    JsonString.Append("} ");                }                else                {                    JsonString.Append("}, ");                }            }            JsonString.Append("]");            if (displayCount)            {                JsonString.Append(",");                JsonString.Append("\"total\":");                JsonString.Append(totalcount);            }            JsonString.Append("}");            return JsonString.ToString().Replace("\n", "");        }        else        {            return null;        }    }}

第六步：数据库增加存储过程，用于分页

CREATE PROCEDURE [dbo].[pro_pageList](@tblName   varchar(255),       -- 表名@strGetFields varchar(1000) = '*',  -- 需要返回的列@fldName varchar(255)='',      -- 排序的字段名@PageSize   int = 40,          -- 页尺寸@PageIndex  int = 1,           -- 页码@doCount  bit = 0,   -- 返回记录总数, 非 0 值则返回@OrderType bit = 0,  -- 设置排序类型, 非 0 值则降序@strWhere  varchar(1500)=''  -- 查询条件 (注意: 不要加 where))ASdeclare @strSQL   varchar(5000)       -- 主语句declare @strTmp   varchar(110)        -- 临时变量declare @strOrder varchar(400)        -- 排序类型if @doCount != 0    begin       if @strWhere !=''           set @strSQL = 'select count(*) as Total from ' + @tblName + ' where '+@strWhere       else           set @strSQL = 'select count(*) as Total from ' + @tblName     end  --以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况else    begin        if @OrderType != 0             begin                 set @strTmp = '<(select min'                 set @strOrder = ' order by ' + @fldName +' desc'                  end         else   --如果@OrderType不是0，就执行降序，这句很重要！             begin                set @strTmp = '>(select max'                set @strOrder = ' order by ' + @fldName +' asc'             end          if @PageIndex = 1  --如果是第一页就执行下面的代码，这样会加快执行速度               begin                   if @strWhere != ''                          set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder                  else                        set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from '+ @tblName + ' '+ @strOrder                           end           else     --以下代码赋予了@strSQL以真正执行的SQL代码                begin                                 set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from '                      + @tblName + ' where ' + @fldName + '' + @strTmp + '('+ @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '+                         @fldName + ' from ' + @tblName + '' + @strOrder + ') as tblTmp)'+ @strOrder                     if @strWhere != ''                             set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from '                              + @tblName + ' where ' + @fldName + '' + @strTmp + '('                              + @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '                              + @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' '                              + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder                end    end   exec (@strSQL)

总结

         通过上面的代码，我们可以看出C#easyui实现增删改查，前端采用jquery来获取数据，传递给服务器，服务器端得到数据将处理结果通过josn或者text的方式传到前端 ，jquery接收到处理结果后再展示给客户。也是十分的简单，就是对http协议要有深入的理解，不过，和上文的三种编程模式还是不同的，需要深刻的理解，融汇贯通。

         通过esayui来处理增删改查，代码终于突破1000行了，有点怀念pb几十行的幸福时光了吧，后面更加复杂，没有几千行都处理不了一个增删改查，不知道为什么简单的问题越来越复杂。