上文，我们使用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:;" οnclick="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="#" οnclick="initcx();return false;" id="a_cx" class="easyui-linkbutton" iconcls="icon-search">查询</a>
</td>
<td valign="middle" nowrap align="right">
<a href="#" οnclick="OpenWin();return false;" id="a_add" class="easyui-linkbutton" iconcls="icon-add">增加</a>
<a href="#" οnclick="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" οnclick="$('#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)
)
AS
declare @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几十行的幸福时光了吧，后面更加复杂，没有几千行都处理不了一个增删改查，不知道为什么简单的问题越来越复杂。
4707
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
