首页 > 代码库 > EasyUI中datagrid实现显示、增加、 删除、 修改、 查询操作(后台代码C#)
EasyUI中datagrid实现显示、增加、 删除、 修改、 查询操作(后台代码C#)
2datagrid加载数据。代码如下所示
一、数据的显示
1新建HtmlPage2.html页面，引入相关文件。如下所示
<script src=http://www.mamicode.com/"easyui/js/jquery-1.8.2.min.js"></script> <script src=http://www.mamicode.com/"easyui/js/jquery.easyui.min.js"></script> <link href=http://www.mamicode.com/"easyui/css/themes/default/easyui.css" rel="stylesheet" /> <link href=http://www.mamicode.com/"easyui/css/themes/icon.css" rel="stylesheet" /> |
$(function () { $(‘#dg‘).datagrid({ url: ‘HtmlPage2.ashx‘,//请求数据的URL 代码附后 columns: [[ { field: ‘User_Name‘, title: ‘用户名‘, width: 100, align: ‘center‘ }, //User_Name为数据库表中的字段名称 下同 { field: ‘User_Pwd‘, title: ‘密码‘, width: 300, align: ‘center‘ }, { field: ‘User_Sex‘, title: ‘性别‘, width: 100, align: ‘center‘, formatter: function (value) { return value =http://www.mamicode.com/= 1 ?"男" : "女"; } }, { field: ‘User_Code‘, title: ‘员工号‘, width: 100, align: ‘center‘ }, { field: ‘CreateUserName‘, title: ‘创建者‘, width: 100, align: ‘center‘ }, { field: ‘Email‘, title: ‘电子邮件‘, width: 100, align: ‘center‘ }, { field: ‘ModifyUserName‘, title: ‘修改者‘, width: 100, align: ‘center‘ }, ]], width: 1066, rowStyler: function (index, row) { if (index % 2 == 0) { return ‘‘; } }, striped: true, pagination: true, rownumbers: true, singleSelect: true, pageNumber: 1, pageSize: 8, pageList: [1, 2, 4, 8, 16, 32], showFooter: true });}
<dody>
<table id="dg"></table>
</body>
============================
HtmlPage2.ashx代码：
using System.Data.SqlClient; using System.Data; using System.Text; /// <summary> /// HtmlPage2 的摘要说明 /// </summary> public class HtmlPage2 : IHttpHandler { public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/html";int page = int.Parse(context.Request.Form["page"]);//页码 int rows = int.Parse(context.Request.Form["rows"])；//页容量 InitData(context,page,rows); } private void InitData(HttpContext context,int pageindex,int pagesize) { int total; string sql =string.Format("select * from (select *,ROW_NUMBER() over (order by user_id) as id from Base_UserInfo) as tmp where tmp.id between {0}+1 and {1}",(pageindex-1)*pagesize,pagesize*pageindex); DataSet ds = new DataSet(); DataTable dt = new DataTable(); StringBuilder sb = new StringBuilder(); using (SqlConnection con = new SqlConnection("server=.;database=RM_DB;uid=sa;pwd=sa")) { con.Open(); SqlDataAdapter da = new SqlDataAdapter(sql, con); da.Fill(ds); dt = ds.Tables[0]; SqlCommand cmd = new SqlCommand("select count(*) from Base_UserInfo", con);//总行数 total =(int) cmd.ExecuteScalar(); con.Close(); } sb.Append("{\"total\":" + total); sb.Append(","); sb.Append("\"rows\":[");
//转化为Json格式 foreach (DataRow row in dt.Rows) { sb.Append("{"); foreach (DataColumn column in dt.Columns) { sb.Append("\"" + column.ColumnName + "\":\"" + row[column.ColumnName].ToString() + "\","); } sb.Remove(sb.Length - 1, 1); sb.Append("},"); } sb.Remove(sb.Length - 1, 1); sb.Append("]}"); context.Response.Write(sb.ToString()); }
}3.效果如图所示
二、查询。在body中添加如下代码。
<div class="easyui-accordion" style="width: 1066px; height: auto;"> <div title="操作栏" data-options="" style="overflow: auto; padding: 10px;"> <ul class="easyui-tree"> <div style="margin: 10px 0;"> </div> 用户名：<input required="true" id="SearchUserName"> 性别：<input required="true" id="SearchSex"> <a href="http://www.mamicode.com/#" class="easyui-linkbutton" id="search">查询</a> <a href="http://www.mamicode.com/#" class="easyui-linkbutton" id="add">新增</a> <a href="http://www.mamicode.com/#" class="easyui-linkbutton" id="delete">删除</a> <a href="http://www.mamicode.com/#" class="easyui-linkbutton" id="edit">编辑</a> </ul> </div></div>
效果图如下：
查询代码：
$("#search").click(function () { $(‘#dg‘).datagrid({ url: ‘/HtmlPage3.ashx‘,//代码附后 loadMsg: ‘正在加载中‘, columns: [[ { field: ‘User_Name‘, title: ‘用户名‘, width: 100 }, { field: ‘User_Pwd‘, title: ‘密码‘, width: 100 }, { field: ‘User_Sex‘, title: ‘性别‘, width: 100, align: ‘right‘, formatter: function (value) { return value =http://www.mamicode.com/= 1 ?"男" : "女"; } }, { field: ‘User_Code‘, title: ‘员工号‘, width: 100 }, { field: ‘CreateUserName‘, title: ‘创建者‘, width: 100 }, ]], width: 1066, queryParams: { UserName: $("#SearchUserName").val(), sex: $("#SearchSex").val(), //发送额外的参数 }, rowStyler: function (index, row) { if (index % 2 == 0) { return ‘color:#fff;‘; } }, striped: true, pagination: true, rownumbers: true, singleSelect: true, pageNumber: 1, pageSize: 8, pageList: [1, 2, 4, 8, 16, 32], showFooter: true }) })
============HtmlPage3.ashx代码
using System.Data.SqlClient; using System.Data; using System.Text; /// <summary> /// HtmlPage2 的摘要说明 /// </summary> public class HtmlPage2 : IHttpHandler { public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/html";int page = int.Parse(context.Request.Form["page"]); int rows = int.Parse(context.Request.Form["rows"]); ChaxunData(context,page,rows); } private void ChaxunData(HttpContext context,int pageindex,int pagesize) { int total; string sql = "select * from (select *,ROW_NUMBER() over (order by user_id) as id from Base_UserInfo) as tmp where 1=1 "; string tsql = string.Empty; string UserName = context.Request.Form["UserName"]; string sex = context.Request.Form["sex"]; if (!string.IsNullOrEmpty(UserName)) { sql += string.Format(" and User_Name=‘{0}‘", UserName) ; tsql += string.Format(" and User_Name=‘{0}‘", UserName); } if (!string.IsNullOrEmpty(sex)) { int s = (sex == "男") ? 1 : 0; sql += string.Format(" and User_Sex=‘{0}‘ ",s); tsql += string.Format(" and User_Sex=‘{0}‘", s); } sql += string.Format(" and tmp.id between {0}+1 and {1}", (pageindex - 1) * pagesize, pagesize * pageindex); DataSet ds = new DataSet(); DataTable dt = new DataTable(); StringBuilder sb = new StringBuilder(); using (SqlConnection con = new SqlConnection("server=.;database=RM_DB;uid=sa;pwd=sa")) { con.Open(); SqlDataAdapter da = new SqlDataAdapter(sql, con); da.Fill(ds); dt = ds.Tables[0]; SqlCommand cmd = new SqlCommand("select count(*) from Base_UserInfo where 1=1 "+tsql, con); total = (int)cmd.ExecuteScalar(); con.Close(); } sb.Append("{\"total\":" + total); sb.Append(","); sb.Append("\"rows\":["); foreach (DataRow row in dt.Rows) { sb.Append("{"); foreach (DataColumn column in dt.Columns) { sb.Append("\"" + column.ColumnName + "\":\"" + row[column.ColumnName].ToString() + "\","); } sb.Remove(sb.Length - 1, 1); sb.Append("},"); } sb.Remove(sb.Length - 1, 1); sb.Append("]}"); context.Response.Write(sb.ToString()); }
}查询结果举例：支持查询结果的分页
三、新增、修改
var isEdit=true; //初始化操作窗体 $(‘#CommonWin‘).window({ width: 300, height: 150, modal: true, minimizable: false, maximizable: false, draggable: false, resizable: false, collapsible: false, title: ‘用户操作‘, left: 400, top: 400 }).window("close");------点击新增事件------ $("#add").click(function () { isEdit = false; $(‘#CommonWin‘).window("open"); })-------修改-------- $("#edit").click(function () { isEdit = true; var $selectRow = $("#dg").datagrid("getSelected"); if ($selectRow) { $.get("/operator.ashx?type=edit&User_ID=" + $selectRow.User_ID, function (js) { alert(js.data.User_Name); $("#userid").val(js.data.User_ID); $("#username").val(js.data.User_Name); $("#userpwd").val(js.data.User_Pwd); $("#usersex").val(js.data.User_Sex==1?"男":"女"); $(‘#CommonWin‘).window("open"); }); } else { $.messager.alert(‘提示‘, ‘请选择要操作的行！‘, ‘info‘); } })------保存---- function Save() { if (isEdit) { SaveEdit(); } else { SaveAdd(); } }---------------------- function SaveAdd() { var form = $("#from1").serialize(); form += "&type=add"; $.post( "/operator.ashx",//代码附后 form, function (js) { $(‘#CommonWin‘).window("close"); $("#dg").datagrid("reload"); }) } function SaveEdit() { var form = $("#from1").serialize(); form += "&type=update"; $.post( "/operator.ashx", form, function (js) { $(‘#CommonWin‘).window("close"); $("#dg").datagrid("reload"); }) }-----------------------------body-----------<div id="CommonWin"> <form id="from1" > <table border="0" cellspacing="0" cellpadding="0" width="100%"> <tr> <td><input type="hidden" name="userid" id="userid" /></td> </tr> <tr> <td>用户名:<input type="text" name="username" id="username"></td> </tr> <tr> <td>密 码:<input type="text" name="userpwd" id="userpwd"></td> </tr> <tr> <td>性 别:<input type="text" name="usersex" id="usersex"></td> </tr> <tr><td><input type="submit" value="http://www.mamicode.com/保存" onclick="Save()"/></td></tr> </table> </form></div>operator.ashx代码：
using System.Data.SqlClient; using System.Data; /// <summary> /// _operator 的摘要说明 /// </summary> public class _operator : IHttpHandler { /// <summary> /// 当前上下文对象 /// </summary> public HttpRequest Request { get { return HttpContext.Current.Request; } } private HttpResponse Response { get { return HttpContext.Current.Response; } } public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/html"; string type=Request.Params["type"]; switch (type) { case "add": Add(); break; case "delete": Delete(); break; case "edit": Edit(); break; case "update": Update(); break; } } /// <summary> /// 得到编辑的数据 /// </summary> private void Edit() { string userid = Request.QueryString["User_ID"]; string sql = string.Format("select * from Base_UserInfo where User_ID=‘{0}‘", userid); DataTable dt = new DataTable(); DataSet ds = new DataSet(); using (SqlConnection con = new SqlConnection("server=.;database=RM_DB;uid=sa;pwd=sa")) { SqlDataAdapter da = new SqlDataAdapter(sql, con); da.Fill(ds); dt = ds.Tables[0]; } MessageShow(1, "ww", dt); } /// <summary> /// 添加 /// </summary> private void Add() { string username = Request.Form["username"]; string userpwd = Request.Form["userpwd"]; string usersex = Request.Form["usersex"]; int i = (usersex == "男") ? 1 :( usersex == "女" )? 0 : 1; string uid = Guid.NewGuid().ToString(); string sql = string.Format("insert Base_UserInfo(User_ID,User_Name,User_Pwd,User_Sex)values(‘{0}‘,‘{1}‘,‘{2}‘,{3})", uid, username, userpwd,i); using (SqlConnection con = new SqlConnection("server=.;database=RM_DB;uid=sa;pwd=sa")) { con.Open(); SqlCommand cmd = new SqlCommand(sql, con); cmd.ExecuteNonQuery(); con.Close(); MessageShow(1, "新增成功", null); } } /// <summary> /// 删除操作 /// </summary> private void Delete() { string User_ID = Request.QueryString["User_ID"];//删除的主键 string sql="delete Base_UserInfo where User_ID=@User_ID"; using(SqlConnection con=new SqlConnection("server=.;database=RM_DB;uid=sa;pwd=sa")) { con.Open(); SqlCommand cmd = new SqlCommand(sql,con); SqlParameter p = new SqlParameter("@User_ID", User_ID); cmd.Parameters.Add(p); cmd.ExecuteNonQuery(); con.Close(); MessageShow(1, "删除成功", null); } } /// <summary> /// 更新 /// </summary> private void Update() { //todo(); } /// <summary> /// 统一返回浏览器消息 /// </summary> /// <param name="s">状态</param> /// <param name="m">消息提示</param> /// <param name="d">数据</param> public void MessageShow(int s, string m, object d) { MessageInfo min = new MessageInfo(s,m,d); System.Web.Script.Serialization.JavaScriptSerializer js = new System.Web.Script.Serialization.JavaScriptSerializer(); string ToJson = js.Serialize(min); Response.Write(ToJson); } public bool IsReusable { get { return false; } } }四 删除：
前端代码 后台代码见上
//删除 $("#delete").click(function () { var $selectRow = $("#dg").datagrid("getSelected"); if ($selectRow) { $.messager.confirm(‘确认对话框‘, ‘您确定要删除么？‘, function (r) { if (r) { $.get("/operator.ashx?type=delete&User_ID=" + $selectRow.User_ID, function (js) { $("#dg").datagrid("reload"); }); } }); } else { $.messager.alert(‘提示‘, ‘请选择要操作的行！‘, ‘info‘); } });
注：数据库代码
USE [RM_DB]CREATE TABLE [dbo].[Base_UserInfo]( [User_ID] [dbo].[Name(50)] NOT NULL, [User_Code] [dbo].[Name(50)] NULL, [User_Account] [dbo].[Name(50)] NULL, [User_Pwd] [dbo].[Name(50)] NULL, [User_Name] [dbo].[Name(50)] NULL, [User_Sex] [dbo].[ID] NULL, [Title] [dbo].[Name(50)] NULL, [Email] [dbo].[Name(20)] NULL, [Theme] [dbo].[Name(50)] NULL, [Question] [dbo].[Name(50)] NULL, [AnswerQuestion] [dbo].[Name(50)] NULL, [DeleteMark] [dbo].[ID] NULL, [CreateDate] [Date] NULL, [CreateUserId] [dbo].[Name(50)] NULL, [CreateUserName] [dbo].[Name(50)] NULL, [ModifyDate] [Date] NULL, [ModifyUserId] [dbo].[Name(50)] NULL, [ModifyUserName] [dbo].[Name(50)] NULL, [User_Remark] [dbo].[Name(Max)] NULL, CONSTRAINT [PK_BASE_USERINFO] PRIMARY KEY NONCLUSTERED ( [User_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOEXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=http://www.mamicode.com/N‘用户主键‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘Base_UserInfo‘, @level2type=N‘COLUMN‘,@level2name=N‘User_ID‘>
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。