jQuery EasyUI+ashx实现数据库的CIUD操作
来源:互联网 发布:php精粹 编辑:程序博客网 时间:2024/06/05 07:45
jQuery EasyUI+ashx实现数据库的CIUD操作
对上一个小项目做一个回顾总结,涉及到了jQuery EasyUI+ashx实现数据库的CIUD操作,和大家分享一下。基本思路是用easyui做前端,ashx做后端,中间使用json格式交换数据,其中json主要使用Newtonsoft.Json来序列化和反序列化,为简单起见,后端没有分层,数据都是靠拼接sql,使用一个简单封装的DBHelper来时间数据库的操作。
1、数据库表tb_Provider结构
- CREATE TABLE [dbo].[tb_Provider](
- [PrID] [int] IDENTITY(1,1) NOT NULL,
- [PrName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
- [PrPeople] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
- [PrPhone] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
- [PrFax] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
- [PrRemark] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
- [Editer] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
- [EditDate] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
- CONSTRAINT [PK_tb_Provider] PRIMARY KEY CLUSTERED
- (
- [PrID] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
其中PrID是整形自动增长的主关键字,其他字段都为字符串类型。
2、对应的实体Provider.cs
- using System;
- using System.Data;
- using System.Configuration;
- using System.Linq;
- using System.Web;
- using System.Web.Security;
- using System.Web.UI;
- using System.Web.UI.HtmlControls;
- using System.Web.UI.WebControls;
- using System.Web.UI.WebControls.WebParts;
- using System.Xml.Linq;
- /// <summary>
- ///Provider 的摘要说明
- /// </summary>
- public class Provider
- {
- public Provider()
- {
- //
- //TODO: 在此处添加构造函数逻辑
- //
- }
- public int PrID { get; set; }
- public string PrName { get; set; }
- public string PrPeople { get; set; }
- public string PrPhone { get; set; }
- public string PrFax { get; set; }
- public string PrRemark { get; set; }
- public string Editer { get; set; }
- public string EditDate { get; set; }
- }
3、前端界面Provider.aspx
- <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Provider.aspx.cs" Inherits="sys_Provider" %>
- <!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 id="Head1" runat="server">
- <title>供应商管理</title>
- <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
- <link rel="stylesheet" href="easyui/themes/default/easyui.css" type="text/css" media="screen" />
- <link rel="stylesheet" href="easyui/themes/icon.css" type="text/css" media="screen" />
- <link rel="stylesheet" href="css/main.css" type="text/css" media="screen" />
- <script src="easyui/jquery-1.7.2.min.js" type="text/javascript"></script>
- <script src="easyui/jquery.easyui.min.js" type="text/javascript"></script>
- <script src="easyui/locale/easyui-lang-zh_CN.js" type="text/javascript"></script>
- <script src="js/myValid.js" type="text/javascript"></script>
- <style type="text/css">
- input,textarea{
- width:200px;
- border:1px solid #ccc;
- padding:2px;
- font-size:12px;
- }
- .grid{ width: 100%; font-size:12px; border:1px solid #8DB2E3; border-collapse: collapse}
- .grid td,.grid th{ border:1px solid #8DB2E3;padding:3px; }
- </style>
- <script src="js/Provider.js" type="text/javascript"></script>
- </head>
- <body class="easyui-layout">
- <div region="center" style="padding:5px;" border="false">
- <table id="tt" fit="true">
- </table>
- <div id="data-window" class="easyui-window" data-options="title:'供应商信息设置',iconCls:'icon-edit',modal:true,maximizable:false,minimizable:false"
- style="background:#fafafa;width:410px;height:300px;padding:5px;">
- <div class="easyui-layout" data-options="fit:true">
- <div data-options="region:'center',border:false" style="padding:10px;background:#fff;border:1px solid #ccc;">
- <form method="post">
- <table class="grid">
- <tr>
- <td colspan="2"><input id="PrID" name="PrID" type="hidden" />
- </td>
- </tr>
- <tr>
- <td>供应商名称:
- </td>
- <td><input name="PrName" class="easyui-validatebox" data-options="required:true"></input>
- </td>
- </tr>
- <tr>
- <td>联系人:
- </td>
- <td><input name="PrPeople" class="easyui-validatebox" data-options="required:true"></input>
- </td>
- </tr>
- <tr>
- <td>联系电话:
- </td>
- <td><input name="PrPhone" class="easyui-validatebox" data-options="required:true,validType:'tel'"></input>
- </td>
- </tr>
- <tr>
- <td>传真:
- </td>
- <td><input name="PrFax" class="easyui-validatebox" data-options="validType:'tel'"></input>
- </td>
- </tr>
- <tr>
- <td>备注:
- </td>
- <td>
- <textarea name="PrRemark" class="easyui-validatebox" style="height:42px;font-size:12px;" ></textarea>
- </td>
- </tr>
- </table>
- </form>
- </div>
- <div data-options="region:'south',border:false" style="text-align:right;padding:5px 0;">
- <a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-save'" onclick="saveData()" id="btn-save">保存</a>
- <a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-cancel'" onclick="closeWindow()" id="btn-cancel">取消</a>
- </div>
- </div>
- </div>
- <div id="search" title="搜索" iconCls="icon-search" modal="true" maximizable="false" minimizable="false" style="background:#fafafa;width:380px;height:100px;">
- <div style="padding:20px 20px 20px 20px;">
- <input id="ss"></input>
- <div id="mm" style="width:120px">
- <div data-options="name:'PrName',iconCls:'icon-ok'">供应商名称</div>
- <div data-options="name:'PrPeople'">联系人</div>
- </div>
- </div>
- </div>
- </div>
- </body>
- </html>
其中页面主要有一个datagrid(table tt)和两个window,myValid.js是一个自定义表单验证,详情可以google疯狂秀才,Provider.js在下面。
4、前端JS脚本Provider.js
- $(function(){
- grid = $('#tt').datagrid({
- pageSize:15,
- pageList:[10,15,20,30],
- fit: true,//自动大小
- rownumbers:true,//行号
- url:'ashx/ProviderHandler.ashx',
- //loadMsg:'数据装载中......',
- singleSelect:true,//单行选取
- pagination:true,//显示分页
- frozenColumns:[[
- {field:'PrID',title:'编号',width:40},
- {field:'PrName',title:'供应商名称',width:100,sortable:true},
- {field:'PrPeople',title:'联系人',width:100},
- {field:'PrPhone',title:'联系电话',width:100},
- {field:'PrFax',title:'传真',width:100}
- ]],
- columns:[[
- {field:'PrRemark',title:'备注',width:100},
- {field:'Editer',title:'修改人',width:100},
- {field:'EditDate',title:'修改日期',width:100}
- ]],
- toolbar:[{
- text:'新增',
- iconCls:'icon-add',
- handler:newData
- },'-',{
- text:'修改',
- iconCls:'icon-edit',
- handler:editData
- },'-',{
- text:'删除',
- iconCls:'icon-remove',
- handler:delData
- },'-',{
- text:'查询',
- iconCls:'icon-search',
- handler:newSearch
- },'-',{
- text:'刷新',
- iconCls:'icon-reload',
- handler:reLoad
- }]
- });
- //设置分页控件
- var p = grid.datagrid('getPager');
- $(p).pagination({
- pageSize: 15,//每页显示的记录条数,默认为10
- pageList: [10,15,20,30]//可以设置每页记录条数的列表
- });
- win = $('#data-window').window({
- closed:true
- });
- form = win.find('form');
- $('#ss').searchbox({
- width:300,
- searcher:function(value,name){
- //alert(value + "," + name);
- grid.datagrid('load', { "searchKey": name, "searchValue": value });
- //alert(value + "," + name);
- },
- menu:'#mm',
- prompt:'请输入关键字'
- });
- search = $('#search').window({
- closed:true
- });
- });
- var grid;
- var win;
- var form;
- var search;
- //显示搜索窗口
- function newSearch(){
- search.window('open');
- }
- //重新加载datagrid
- function reLoad(){
- grid.datagrid('load', {url:'ashx/ProviderHandler.ashx'});
- }
- //显示新增数据窗口
- function newData(){
- win.window('open');
- form.form('clear');
- form.url = 'ashx/ProviderActionHandler.ashx?action=add';
- }
- //显示编辑数据窗口
- function editData(){
- var row = grid.datagrid('getSelected');
- if (row){
- win.window('open');
- form.form('load',row);
- form.url = 'ashx/ProviderActionHandler.ashx?action=edit';
- } else {
- $.messager.show({
- title:'警告',
- msg:'请先选择数据记录。'
- });
- }
- }
- //保存新增或者修改的数据
- function saveData(){
- if(form.form('validate'))
- {
- var query=createParam();
- $.post(form.url, query,
- function(result) {
- if (result.status==1) {
- grid.datagrid('reload');
- win.window('close');
- $.messager.show({
- title:'提示',
- msg:'数据记录保存成功。'
- });
- } else {
- $.messager.alert('错误',result.msg,'error');
- }
- },'json');
- }
- }
- //删除记录,参数是记录的主关键字
- function delData(){
- var row = grid.datagrid('getSelected');
- if (row){
- $.messager.confirm('Confirm', '您确定要删除该数据吗?', function(r) {
- if (r){
- $.post('ashx/ProviderActionHandler.ashx?action=del', { id: row.PrID },
- function(result) {
- if (result.status==1) {
- grid.datagrid('reload');
- } else {
- $.messager.alert('错误',result.msg,'error');
- }
- },
- 'json');
- }
- });
- } else {
- $.messager.show({
- title:'警告',
- msg:'请先选择数据记录。'
- });
- }
- }
- function closeWindow(){
- win.window('close');
- }
- //将表单序列化为json
- function createParam()
- {
- var query = form.serializeArray();
- query = convertArray(query);
- return "json=" + JSON.stringify(query);
- }
- //主要是推荐这个函数。它将jquery系列化后的值转为name:value的形式。
- 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;
- }
懂easyui的肯定都能看懂,其中主要是进行了数据的增加、修改、删除以及查询。
5、后端查询ProviderHandler.ashx
- <%@ WebHandler Language="C#" Class="ProviderHandler" %>
- using System;
- using System.Web;
- using System.Text;
- using System.Collections;
- using System.Data;
- public class ProviderHandler : IHttpHandler
- {
- public void ProcessRequest(HttpContext context)
- {
- int pageSize = Convert.ToInt32(context.Request["rows"]);//通过这个获取得到pageSize
- int pageNum = Convert.ToInt32(context.Request["page"]);//通过这个获取得到pageNum
- string keyname = context.Request["searchKey"];//查询的关键字
- string keyvalue = context.Request["searchValue"];//查询的字段
- var sort = context.Request["sort"];//排序字段
- var order = context.Request["order"];//升序降序
- string condition = " where 1=1";//查询条件
- if (keyname != null)
- {
- condition += " and " + keyname + " like '%" + keyvalue + "%'";
- }
- string sqlcount = "select count(*) from tb_Provider" + condition;
- string sql = "select * from tb_Provider" + condition;
- string mysort = "PrID";//排序情况
- if (sort != null)
- {
- mysort = sort + " " + order;
- }
- int count = DBHelper.GetScalar(sqlcount);
- DataTable dt = DBHelper.ExecuteProcQuery("proc_QueryPagination", sql, pageNum, pageSize, "PrID", mysort);//存储过程
- Hashtable ht = new Hashtable();
- ht.Add("total", count);
- ht.Add("rows", dt);
- string strJson = Newtonsoft.Json.JsonConvert.SerializeObject(ht);//序列化datatable
- context.Response.Clear();
- context.Response.ContentEncoding = Encoding.UTF8;
- context.Response.ContentType = "application/json";
- context.Response.Write(strJson);
- context.Response.Flush();
- context.Response.End();
- }
- public bool IsReusable
- {
- get
- {
- return false;
- }
- }
- }
主要通过context.Request获取前端传过来的参数使用分页存储过程proc_QueryPagination查询数据,再使用Newtonsoft.Json序列化后返回给前台。
6、用于分页查询的存储过程proc_QueryPagination
- CREATE PROCEDURE [dbo].[proc_QueryPagination]
- (
- @SQL nVARCHAR(4000), --不带排序语句的SQL语句
- @Page int, --页码
- @RecsPerPage int, --每页容纳的记录数
- @ID VARCHAR(255), --需要排序的不重复的ID号
- @Sort VARCHAR(255) --排序字段及规则
- )
- AS
- DECLARE @Str nVARCHAR(4000)
- SET @Str='SELECT TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM
- ('+@SQL+') T WHERE T.'+@ID+' NOT IN (SELECT TOP '+CAST((@RecsPerPage*(@Page-1))
- AS VARCHAR(20))+' '+@ID+' FROM ('+@SQL+') T9 ORDER BY '+@Sort+') ORDER BY '+@Sort
- PRINT @Str
- EXEC sp_ExecuteSql @Str
7、后端增删改ProviderActionHandler.ashx
- <%@ WebHandler Language="C#" Class="ProviderActionHandler" %>
- using System;
- using System.Web;
- using System.Text;
- using System.Data;
- using System.Web.UI.WebControls;
- using System.Web.SessionState;
- using Newtonsoft.Json;
- using Newtonsoft.Json.Converters;
- public class ProviderActionHandler : IHttpHandler, IRequiresSessionState
- {
- public void ProcessRequest(HttpContext context)
- {
- context.Response.ContentType = "text/plain";
- var action = context.Request["action"];
- switch (action)
- {
- case "add":
- {
- add(context);
- break;
- }
- case "edit":
- {
- edit(context);
- break;
- }
- case "del":
- del(context);
- break;
- default:
- break;
- }
- }
- private void add(HttpContext context)
- {
- var json = HttpContext.Current.Request["json"];
- var jsetting = new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore };
- Provider single = (Provider)JsonConvert.DeserializeObject<Provider>(json, jsetting);//反序列化
- Admin admin = (Admin)context.Session["AdminUser"];
- string sql = "insert into tb_Provider(PrName,PrPeople,PrPhone,PrFax,PrRemark,Editer,EditDate) values('" + single.PrName
- + "','" + single.PrPeople + "','" + single.PrPhone + "','" + single.PrFax + "','" + single.PrRemark
- + "','" + admin.UserName + "','" + DateTime.Now.ToString() + "')";
- result rs = new result();
- if (DBHelper.ExecuteCommand(sql) == 1)
- {
- rs.status = 1;
- rs.msg = "添加成功!";
- }
- else
- {
- rs.status = 0;
- rs.msg = "服务器繁忙,请稍后再试!";
- }
- rs.item = "[]";
- string strJson = JsonConvert.SerializeObject(rs);
- context.Response.Clear();
- context.Response.ContentEncoding = Encoding.UTF8;
- context.Response.ContentType = "application/json";
- context.Response.Write(strJson);
- context.Response.Flush();
- context.Response.End();
- }
- private void edit(HttpContext context)
- {
- var json = HttpContext.Current.Request["json"];
- var jsetting = new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore };
- Provider single = (Provider)JsonConvert.DeserializeObject<Provider>(json, jsetting);//反序列化
- Admin admin = (Admin)context.Session["AdminUser"];
- string sql = "update tb_Provider set PrName='" + single.PrName
- + "',PrPeople='" + single.PrPeople + "',PrPhone='" + single.PrPhone + "',PrFax='" + single.PrFax + "',PrRemark='" + single.PrRemark
- + "',Editer='" + admin.UserName + "',EditDate='" + DateTime.Now.ToString() + "' where PrID=" + single.PrID;
- result rs = new result();
- if (DBHelper.ExecuteCommand(sql) == 1)
- {
- rs.status = 1;
- rs.msg = "修改成功!";
- }
- else
- {
- rs.status = 0;
- rs.msg = "服务器繁忙,请稍后再试!";
- }
- rs.item = "[]";
- string strJson = JsonConvert.SerializeObject(rs);
- context.Response.Clear();
- context.Response.ContentEncoding = Encoding.UTF8;
- context.Response.ContentType = "application/json";
- context.Response.Write(strJson);
- context.Response.Flush();
- context.Response.End();
- }
- private void del(HttpContext context)
- {
- var id = HttpContext.Current.Request["id"];
- string sql = "delete from tb_Provider where PrID='" + id + "'";
- result rs = new result();
- if (DBHelper.ExecuteCommand(sql) == 1)
- {
- rs.status = 1;
- rs.msg = "删除成功!";
- }
- else
- {
- rs.status = 0;
- rs.msg = "服务器繁忙,请稍后再试!";
- }
- rs.item = "[]";
- string strJson = JsonConvert.SerializeObject(rs);
- context.Response.Clear();
- context.Response.ContentEncoding = Encoding.UTF8;
- context.Response.ContentType = "application/json";
- context.Response.Write(strJson);
- context.Response.Flush();
- context.Response.End();
- }
- public bool IsReusable
- {
- get
- {
- return false;
- }
- }
- }
主要实现增删改功能,其中增加修改时,用到了Newtonsoft.Json反序列化json。
8、实现效果
9、总结
如果你跟我一样,美工很烂的话,使用easyui来做界面会是一个不错的选择,我的学习线路是javascript-》jquery-》easyui,当然同类的界面框还有DWZ等的也都是不错的选择。
- jQuery EasyUI+ashx实现数据库的CIUD操作
- jQuery EasyUI+ashx实现数据库的CIUD操作
- jQuery EasyUI+ashx实现数据库的CIUD操作
- JQuery之自定义属性、与ashx通信实现数据库操作
- jquery easyui+ashx+三层框架实现增删改查
- ASP.NET使用EasyUI-DataGrid + ashx + JQuery Ajax:实现数据的增删查改,查询和分页!
- Jquery+ashx实现Ajax
- Jquery+ashx实现Ajax
- Jquery+ashx实现Ajax
- jQuery EasyUI和PHP实现数据的CURD操作
- jquery+ajax+ashx。ashx的使用方法
- easyui datagrid+ashx实现动态生成列
- Easyui datagrid+ashx 实现动态生成列
- jQuery getJSON() + .ashx 实现分页
- jquery easyui实现datagrid表格向数据库中进行增加,修改和删除操作
- jQuery easyUI分页的实现
- jQuery getJSON() + .ashx + jquery.pager 实现分页
- Html + ASHX +Jquery 插入数据库 操作简单示例之留言加验证码
- java整合Flex4
- 如何获得 Windows 操作系统的版本
- Apache Axis相关开发知识点
- Android 手势识别控件 GestureOverlayView
- 守望幸福
- jQuery EasyUI+ashx实现数据库的CIUD操作
- leetcode题目:Clone Graph
- Nginx静态文件处理
- 动态链接库中函数的地址确定---PLT和GOT
- Python里的string 和 unicode
- 屏蔽CDockablePane右击鼠标触发事件弹出右键菜单
- 软件开发项目-文档编写标准化
- Android 百度离线地图MKOfflineMap 多次进入地图页面闪退(个人)
- Python里的string 和 unicode --2