C#+layui 表格实现增删改查

最新推荐文章于 2023-12-22 16:53:57 发布

优秀是不可能的

最新推荐文章于 2023-12-22 16:53:57 发布

阅读量2.3k

点赞数 4

分类专栏： C# 文章标签： c# layui 一般处理程序

本文链接：https://blog.csdn.net/golden_stone_fish/article/details/121882573

版权

C# 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

C#+layui 表格实现增删改查

layui 前端代码

layui table 界面

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>layui</title>
    <meta name="renderer" content="webkit">
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1" />
    <script src="jquery-3.4.1.min.js"></script>
    <link href="public.css" rel="stylesheet" />
    <link href="layui/css/layui.css" rel="stylesheet" media="all" />
    <script src="layui/layui.js"></script>
</head>
<body>
<div class="layuimini-container">
    <div class="layuimini-main">
        <fieldset class="table-search-fieldset">
            <legend>搜索信息</legend>
            <div style="margin: 10px 10px 10px 10px">
                <form class="layui-form layui-form-pane">
                    <div class="layui-form-item">
                        <div class="layui-inline">
                            <label class="layui-form-label">用户姓名</label>
                            <div class="layui-input-inline">
                                <input type="text" name="username" autocomplete="off" class="layui-input">
                            </div>
                        </div>
                        <div class="layui-inline">
                            <button type="submit" class="layui-btn layui-btn-primary"  lay-submit lay-filter="data-search-btn"><i class="layui-icon"></i> 搜 索</button>
                        </div>
                    </div>
                </form>
            </div>
        </fieldset>

        <!--表头工具栏-->
        <script type="text/html" id="toolbarDemo">
            <div class="layui-btn-container">
                <button class="layui-btn layui-btn-normal layui-btn-sm data-add-btn" lay-event="add"> 添加 </button>
                <button class="layui-btn layui-btn-sm layui-btn-danger data-delete-btn" lay-event="delete"> 删除 </button>
            </div>
        </script>

        <!--表格容器-->
        <table class="layui-hide" id="currentTableId" lay-filter="currentTableFilter"></table>

        <!--行内工具栏-->
        <script type="text/html" id="currentTableBar">
            <a class="layui-btn layui-btn-normal layui-btn-xs data-count-edit" lay-event="edit">编辑</a>
            <a class="layui-btn layui-btn-xs layui-btn-danger data-count-delete" lay-event="delete">删除</a>
        </script>

    </div>
</div>

<script>

    //刷新表格函数
    function reloadTable() {
        layui.table.reload('currentTableId', {
            page: {
                curr: 1
            }
            , where: {
                type: "select"
            }
        });
    }

    function formatDateTime(d)
    {
        var date = new Date(d);
        return date.getFullYear() + "-" + (date.getMonth() + 1) + "-" + (date.getDate() + 1);
    }

    layui.use(['form', 'table'], function () {
        var $ = layui.jquery,
            form = layui.form,
            table = layui.table;

        table.render({
            elem: '#currentTableId',
            url: 'UserHandler.ashx',
            toolbar: '#toolbarDemo',  // 表头工具栏
            defaultToolbar: ['filter', 'exports', 'print', {
                title: '提示',
                layEvent: 'LAYTABLE_TIPS',
                icon: 'layui-icon-tips'
            }],
            cols: [[
                {type: "checkbox", width: 50},
                { field: 'Id', width: 80, title: 'ID', sort: true},
                { field: 'UserName', width: 80, title: '用户名'},
                { field: 'UserPwd', width: 80, title: '密码'},
                { field: 'UserSex', width: 80, title: '性别'},
                { field: 'Birthday', title: '生日', minWidth: 150,
                templet: "<div>{{layui.util.toDateString(d.sbj_start, 'yyyy-MM-dd')}}</div>"},
                {title: '操作', minWidth: 150, toolbar: '#currentTableBar', align: "center"}
            ]],
            limits: [10, 15, 20, 25, 50, 100],
            limit: 15,
            page: true,
            skin: 'line',
            where: { type: "select" }, //查询时要传递的参数
            parseData: function (res) { //res 即为原始返回的数据
                return {
                    "code": res.status, //解析接口状态
                    "msg": res.message, //解析提示文本
                    "count": res.total, //解析数据长度
                    "data": res.data //解析数据列表
                };
            }
        });

        // 监听搜索操作
        form.on('submit(data-search-btn)', function (data) {
            //data是表单提交时，提交的所有信息，field 是获取所有的字段的信息
            var result = JSON.stringify(data.field);
            //执行搜索重载
            table.reload('currentTableId', {
                page: {
                    curr: 1
                }
                , where: {
                    searchParams: result,
                    type:"select"
                }
            }, 'data');
            return false;
        });

        /**
         * toolbar监听事件
         */
        table.on('toolbar(currentTableFilter)', function (obj)
        {
            if (obj.event === 'add')// 监听添加操作
            {  
                var index = layer.open({
                    title: '添加用户',
                    type: 2,
                    shade: 0.2,
                    maxmin:true,
                    shadeClose: true,
                    area: ['100%', '100%'],
                    content: 'add.html',
                });
        }
            else if (obj.event === 'delete')// 监听删除操作
            {  
                var checkStatus = table.checkStatus('currentTableId')
                    , data = checkStatus.data;

                if (data.length==0)
                {
                    var index = layer.alert('请选择一行进行删除',
                    {
                        title: '提示'
                    }, function ()
                    {
                        // 关闭弹出层
                        layer.close(index);
                    });
                    return;
                }

                //获取选中的所有Id
                var idArray = new Array();
                for (var i = 0; i < data.length; i++)
                {
                    idArray.push(data[i].Id);
                }
               
                $.ajax({
                    url: "UserHandler.ashx",
                    method: "POST",
                    data: { id: JSON.stringify(idArray), type: "delete" },
                    success: function (msg)
                    {
                        var index = layer.alert(msg, {
                            title: '提示'
                        }, function () {
                            // 关闭弹出层
                            layer.close(index);
                            if (msg=="删除成功")
                            {
                                //调用表格重载方法
                                reloadTable();
                            }
                        });
                    }
                });
            }
        });
        table.on('tool(currentTableFilter)', function (obj) {
            //获取选中行的数据
            var data = obj.data;
            if (obj.event === 'edit') {
                var index = layer.open({
                    title: '编辑用户',
                    type: 2,
                    shade: 0.2,
                    maxmin:true,
                    shadeClose: true,
                    area: ['100%', '100%'],
                    content: 'edit.html',
                    success: function (layero, index)
                    {
                        //向layer页面传值，传值主要代码
                        var body = layer.getChildFrame('body', index);
                        //获取name名为Id的控件进行赋值
                        body.find("[name='Id']").val(data.Id);
                        body.find("[name='UserName']").val(data.UserName);
                        body.find("input[name='UserSex'][value='" + data.UserSex +"']").attr("checked", data.UserSex);
                        body.find("[name='UserPwd']").val(data.UserPwd);
                        body.find("[name='Birthday']").val(formatDateTime(data.Birthday));
                        //得到iframe页的窗口对象，执行iframe页的方法：iframeWin.method();
                        var iframeWin = window[layero.find('iframe')[0]['name']];
                        //执行表单重新渲染的方法
                        iframeWin.myResend();
                    }
                });
                return false;
            }
            else if (obj.event === 'delete') {
                layer.confirm('真的删除行吗', function (index) {
                    var idArray = [obj.data.Id];
                    $.ajax({
                        url: "UserHandler.ashx",
                        method: "POST",
                        data: { id: JSON.stringify(idArray), type: "delete" },
                        success: function (msg) {
                            var index = layer.alert(msg, {
                                title: '提示'
                            }, function () {
                                // 关闭弹出层
                                layer.close(index);
                                if (msg == "删除成功") {
                                    //调用表格重载方法
                                    reloadTable();
                                }
                            });
                        }
                    });
                    //关闭弹窗
                    layer.close(index);
                });
            }
        });

    });
</script>

</body>
</html>

layui add界面

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>layui</title>
    <meta name="renderer" content="webkit">
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
    <link href="layui/css/layui.css" rel="stylesheet" />
    <link href="public.css" rel="stylesheet" />
    <script src="layui/layui.js"></script>
    <style>
        body {
            background-color: #ffffff;
        }
    </style>
</head>

<body>
<div class="layui-form layuimini-form">
    <div class="layui-form layuimini-form">
        
        <div class="layui-form-item">
            <label class="layui-form-label required">用户名</label>
            <div class="layui-input-block">
                <input type="text" id="UserName" name="UserName" lay-verify="required" lay-reqtext="用户名不能为空" placeholder="请输入用户名" value="" class="layui-input">
                <tip>填写自己管理账号的名称。</tip>
            </div>
        </div>
        <div class="layui-form-item">
            <label class="layui-form-label required">性别</label>
            <div class="layui-input-block">
                <input type="radio" name="UserSex" value="男" title="男" checked>
                <input type="radio" name="UserSex" value="女" title="女">
            </div>
        </div>
        <div class="layui-form-item">
            <label class="layui-form-label required">密码</label>
            <div class="layui-input-block">
                <input type="password" id="UserPwd" name="UserPwd" lay-verify="required" lay-reqtext="密码不能为空" placeholder="请输入密码" value="" class="layui-input">
            </div>
        </div>
        <div class="layui-form-item">
            <label class="layui-form-label">出生日期</label>
            <div class="layui-input-block">
                <input type="text" id="Birthday" name="Birthday" placeholder="请选择出生日期" value="" class="layui-input">
            </div>
        </div>

        <div class="layui-form-item">
            <div class="layui-input-block">
                <button class="layui-btn layui-btn-normal" lay-submit lay-filter="saveBtn">确认保存</button>
            </div>
        </div>
    </div>
 </div>

<script>
    layui.use(['form'], function () {
        var form = layui.form,
            layer = layui.layer,
            $ = layui.$;

        var laydate = layui.laydate;

        //实例化时间控件
        laydate.render({
            elem: '#Birthday',
            format: 'yyyy-MM-dd',
            value: new Date(),
            isInitValue: true
        });

        //监听提交
        form.on('submit(saveBtn)', function (data)
        {
            $.ajax({
                url: "UserHandler.ashx",
                method: "POST",
                data: { user: JSON.stringify(data.field), type: "insert" },
                success: function (msg)
                {
                    var index = layer.alert(msg, {
                        title: '执行信息'
                    }, function () {
                        // 关闭弹出层
                        layer.close(index);
                        var iframeIndex = parent.layer.getFrameIndex(window.name);
                        parent.layer.close(iframeIndex);
                        if (msg=="添加成功")
                        {
                            //重载表格
                            parent.reloadTable();
                        }
                    });
                }
            });

            

            return false;
        });

    });                                            
</script>
</body>
</html>

layui edit 界面

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>layui</title>
    <meta name="renderer" content="webkit">
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
    <link href="public.css" rel="stylesheet" />
    <script src="jquery-3.4.1.min.js"></script>
    <link href="layui/css/layui.css" rel="stylesheet" />
    <script src="layui/layui.js"></script>
    <style>
        body {
            background-color: #ffffff;
        }
    </style>
</head>
<body>
    <form class="layui-form"  lay-filter="example" id="example">
        <div class="layui-form layuimini-form">
            <div class="layui-form-item">
                <label class="layui-form-label required">编号</label>
                <div class="layui-input-block">
                    <input type="text" name="Id" readonly="readonly" class="layui-input">
                    <tip>填写自己管理账号的名称。</tip>
                </div>
            </div>
            <div class="layui-form-item">
                <label class="layui-form-label required">用户名</label>
                <div class="layui-input-block">
                    <input type="text" id="UserName" name="UserName" lay-verify="required" lay-reqtext="用户名不能为空" placeholder="请输入用户名" value="" class="layui-input">
                    <tip>填写自己管理账号的名称。</tip>
                </div>
            </div>
            <div class="layui-form-item">
                <label class="layui-form-label required">性别</label>
                <div class="layui-input-block">
                    <input type="radio" name="UserSex" value="男" title="男" checked>
                    <input type="radio" name="UserSex" value="女" title="女">
                </div>
            </div>
            <div class="layui-form-item">
                <label class="layui-form-label required">密码</label>
                <div class="layui-input-block">
                    <input type="password"  id="UserPwd" name="UserPwd" lay-verify="required" lay-reqtext="密码不能为空" placeholder="请输入密码" value="" class="layui-input">
                </div>
            </div>
            <div class="layui-form-item">
                <label class="layui-form-label">出生日期</label>
                <div class="layui-input-block">
                    <input type="text" id="Birthday" name="Birthday" placeholder="请选择出生日期" value="" class="layui-input">
                </div>
            </div>


            <div class="layui-form-item">
                <div class="layui-input-block">
                    <button class="layui-btn layui-btn-normal" lay-submit lay-filter="saveBtn">确认保存</button>
                </div>
            </div>
        </div>
    </form>

<script>

    function myResend()
    {
        layui.use(['form'], function ()
        {
            var form = layui.form;
            form.render();//重新渲染
        });
    }

    function formatDateTime(d)
    {
        var date = new Date(d);
        return date.getFullYear() + "-" + (date.getMonth() + 1) + "-" + (date.getDate() + 1);
    }

    layui.use(['form', 'laydate'], function () {
        var form = layui.form,
            layer = layui.layer,
            $ = layui.$;
        var laydate = layui.laydate;

        //实例化时间控件
        laydate.render({
            elem: '#Birthday',
            format: 'yyyy-MM-dd',
            value: new Date(),
            isInitValue: true
        });

        //监听提交
        form.on('submit(saveBtn)', function (data)
        {
            $.ajax({
                url: "UserHandler.ashx",
                method: "POST",
                data: { user: JSON.stringify(data.field), type: "edit" },
                success: function (msg)
                {
                    var index = layer.alert(msg, {
                        title: '执行信息'
                    }, function () {
                        // 关闭弹出层
                        layer.close(index);
                        var iframeIndex = parent.layer.getFrameIndex(window.name);
                        parent.layer.close(iframeIndex);
                        //判断是否执行成功了
                        if (msg=="修改成功")
                        {
                            //执行表格重载
                            parent.reloadTable();
                        }
                    });
                }
            });


          

            return false;
        });

    });
</script>
</body>
</html>

C#后台代码

DBHelper类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace layui表格使用
{
    public class DBHelper
    {

        /// <summary>
        /// 链接字符串
        /// </summary>
      static string connStr=ConfigurationManager.ConnectionStrings["connString"].ToString();

     
        /// <summary>
        /// 执行数据的增删改
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="para">参数</param>
        /// <returns>返回受影响的行数</returns>
        public static int getExecuteNonQuery(string sql)
        {
            //获取数据库链接对象
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                //打开数据库
                conn.Open();
                //创建数据库语句执行对象
                SqlCommand cmd = new SqlCommand(sql, conn);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

        }
        /// <summary>
        /// 查詢n張表的一個集合  
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="para"></param>
        /// <returns>返回表的集合</returns>
        public static DataSet getDataSet(string sql)
        {
            //获取数据库链接对象
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            try
            {

                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet dataset = new DataSet();
                adapter.Fill(dataset);
                return dataset;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
        /// <summary>
        /// 返回一个DateReader 对象，读取数据,读取完成调用close 关闭
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlDataReader getDataReader(string sql)
        {
            //获取数据库链接对象
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);


                //CommandBehavior.CloseConnection，表示你关闭dataReader   时,同时也把与它相关联的Connection连接也一起关闭
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw ex;
            }


        }

        /// <summary>
        ///  返回执行结果的第一行第一列
        /// </summary>
        /// <param name="sql"></param>
        public static object getExecuteScalar(string sql)
        {
            ///获取链接对象
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                return cmd.ExecuteScalar();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
        /// <summary>
        /// 事务操作
        /// </summary>
        /// <param name="listSql"></param>
        /// <returns></returns>
        public static int ExcuteMoreMethod(List<string> listSql)
        {
            SqlConnection connection = new SqlConnection(connStr);
            connection.Open();
            using (SqlTransaction tran = connection.BeginTransaction())//开启事务
            {

                try
                {
                    foreach (string sql in listSql)
                    {
                        SqlCommand command = new SqlCommand(sql, connection);
                        command.Transaction = tran;
                        command.ExecuteNonQuery();
                    }
                    tran.Commit();//提交事务
                    return 1;
                }
                catch
                {
                    tran.Rollback();//回滚事务
                    return 0;
                }
                finally {
                    connection.Close();
                }
            }
        }


    }
}

一般处理程序

using LinqKit;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace layui表格使用
{
    /// <summary>
    /// UserHandler 的摘要说明
    /// </summary>
    public class UserHandler : IHttpHandler
    {
        string result="";
        public void ProcessRequest(HttpContext context)
        {
            //获取请求类型
            string type = context.Request["type"];

            switch (type)
            {
                case "select": HandleSelect(context);break;
                case "insert": HandleInsert(context); break;
                case "edit": HandleEdit(context); break;
                case "delete": HandleDelete(context); break;
            }
        }

        private void HandleDelete(HttpContext context)
        { 
            //获取所有的Id
            string ids= context.Request["id"];
            //将JSON数组转为的数组
            JArray array = JArray.Parse(ids);
           
            try
            {
                using (TestEntities db = new TestEntities()) 
                {
                   
                    foreach (var item in array)
                    {
                      //实例化要删除的对象
                      User  user= new User { Id=Convert.ToInt32(item.ToString()) };
                      db.User.Attach(user);
                      db.User.Remove(user);
                    }
                    //保存提交
                    db.SaveChanges();
                    result = "删除成功";
                }
            }
            catch(Exception ex) 
            {
                result = "删除失败"+ex.Message;
            }
            context.Response.Write(result);
        
        }

        /// <summary>
        /// 处理编辑
        /// </summary>
        /// <param name="context"></param>
        private void HandleEdit(HttpContext context)
        {
            
            //获取参数
            string parameter = context.Request["user"];
            //解析Json对象
            var userInfo = JsonConvert.DeserializeObject<User>(parameter);
            //使用EF框架进行增删改查
            using (TestEntities db= new TestEntities()) 
            {
                try
                {
                    //查询要修改的对象
                    User user = db.User.Where(u => u.Id == userInfo.Id).FirstOrDefault();
                    //修改此对象的属性，EF框架会帮我们生成对应的SQL语句，进行修改
                    user.UserName = userInfo.UserName;
                    user.UserPwd = userInfo.UserPwd;
                    user.UserSex = user.UserSex;
                    user.Birthday = user.Birthday;
                    //保存修改
                    db.SaveChanges();
                    result = "修改成功";
                }
                catch 
                {
                    result = "修改失败";
                }
                context.Response.Write(result);
            }
        }

        /// <summary>
        /// 处理添加
        /// </summary>
        /// <param name="context"></param>
        private void HandleInsert(HttpContext context)
        {
            //获取参数
            string parameter = context.Request["user"];
            //解析Json对象，转换为User对象
            var userInfo = JsonConvert.DeserializeObject<User>(parameter);
            //使用EF框架进行增删改查
            using (TestEntities db = new TestEntities())
            {
                try
                {
                    //把获取的信息添加到EF中，并标记为添加
                    db.User.Add(userInfo);
                    //保存修改
                    db.SaveChanges();

                    result = "添加成功";
                }
                catch
                {
                    result = "添加失败";
                }
                context.Response.Write(result);
            }
        }

        /// <summary>
        /// 处理查询
        /// </summary>
        /// <param name="context"></param>
        private void HandleSelect(HttpContext context)
        {
            //获取所有参数
            int page = Convert.ToInt32(context.Request["page"]);
            int limit= Convert.ToInt32(context.Request["limit"]);
            
            //获取查询参数(json)
            string searchParams = context.Request["searchParams"];
            string username=null;
            if (!string.IsNullOrEmpty(searchParams))
            {
                //将Json对象转为一个JObject
                JObject jObj =  JObject.Parse(searchParams);
                //获取username的值
                username = jObj["username"].ToString();
            }
            TestEntities db = new TestEntities();
            //使用表达式树
            Expression<Func<User, bool>> expr = PredicateBuilder.New<User>(true);
            if (!string.IsNullOrEmpty(username))
            {
                expr = expr.And(x => x.UserName == username);
            }
            //使用拼凑好的条件进行查询
            var query = db.User.Where(expr);
            //查询之后进行分页,此时还没有给数据库发送指令                                                                 
             query= query.OrderBy(s=>s.Id).Skip((page - 1) * limit).Take(limit);
           // 此时给数据库发送了一条指令，将数据取回来
            var userList = query.ToList();
            //实例化匿名对象
            var json = new
            {
                status = 0,
                message = "查询成功",
                count = userList.Count,
                data = userList
            };
            //将对象转换为json字符串
           context.Response.Write(JsonConvert.SerializeObject(json));
           //释放对象
           db.Dispose();
        }

     
    }
}