.NET Core6.0 MVC+layui+SqlSugar 简单增删改查

HTML部分:

@{
    ViewData["Title"] = "用户列表";
}
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>用户列表</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 rel="stylesheet" href="~/layui/lib/layui-v2.6.3/css/layui.css" media="all">
    <link rel="stylesheet" href="~/layui/css/public.css" media="all">
    <script src="~/layui/lib/layui-v2.6.3/layui.js" charset="utf-8"></script>

    <script src="~/learun/js/jquery/jquery-2.2.0.min.js"></script>

    <script>
        //添加用户 层
        function OpenAdd() {
            var index = layer.open({
                type: 1, //0 dialog 信息框 1; page 页面层;2 iframe 内联框架层;3 loading 加载层;4 tips 贴士层
                title: "",
                content: `
<table class="layui-table">
<tr>
        <td>账号</td>
        <td><input type="text" id="txtName" class="layui-text" /></td>
    </tr>
    <tr>
        <td>类型</td>
        <td>
                    <select id="selType" style="width:175px; height:32px;">
                <option value="0">普通用户</option>
                <option value="1">管理员</option>
            </select>
        </td>
    </tr>
</table>`,
                area: ["300px", "200px"],//弹出尺寸
                btn: ["保存"],
                btn1: function (index, layero) {
                    AddUser();
                },
                cancel: function () {
                    PageList(1);//刷新列表
                }
            });
        }
        //编辑用户 层
        function OpenEdit(id, name, type) {
            var index = layer.open({
                type: 1, //0 dialog 信息框 1; page 页面层;2 iframe 内联框架层;3 loading 加载层;4 tips 贴士层
                title: "",
                content: `
<table class="layui-table">
<tr>
        <td>ID</td>
            <td><span id="spanId"><span/></td>
    </tr>
    <tr>
        <td>账号</td>
                <td><input type="text" id="txtName" class="layui-text" value="" /></td>
    </tr>
    <tr>
        <td>类型</td>
        <td>
            <select id="selType" style="width:175px; height:32px;">
                <option value="0">普通用户</option>
                <option value="1">管理员</option>
            </select>
        </td>
    </tr>
</table>`,
                area: ["500px", "250px"],//弹出尺寸
                success: function () {
                    //回绑控件
                    $("#spanId").html(id);
                    $("#txtName").val(name);
                    $("#selType").val(type);
                },
                btn: ["保存"],
                btn1: function (index, layero) {
                    EditUser();
                },
                cancel: function () {
                    PageList(1);//刷新列表
                }
            });
        }
        //删除用户 层
        function OpenDel(id, name) {
            layer.confirm('确定删除: ' + name + ' ？', {
                btn: ['确定', '取消']
            }, function (index, layero) {//btn1
                if (name.toLowerCase() != 'admin'){
                    DelUser(id);
                }
                else{
                    layer.msg("不可以删除 admin 账号！");
                }
            }, function (index) {//btn2
                PageList(1);//刷新列表
            });
        }
    </script>

</head>
<body>
    <div class="layuimini-container" id="app">
        <div class="layuimini-main">
            <!-----查询条件----->
            <div class="layui-form-item">
                <div class="layui-inline">
                    <label class="layui-form-label">用户名</label>
                    <div class="layui-input-inline">
                        <input type="text" autocomplete="off" class="layui-input" id="txtUserName">
                    </div>
                </div>
                <div class="layui-inline">
                    <label class="layui-form-label">类型</label>
                    <div class="layui-input-inline">
                        <select id="selUserType" style="width:175px; height:32px;">
                            <option value="">全部</option>
                            <option value="0">普通用户</option>
                            <option value="1">管理员</option>
                        </select>
                    </div>
                </div>

                <div class="layui-inline">
                    <button type="button" class="layui-btn layui-btn-primary" lay-submit lay-filter="data-search-btn" onclick="PageList()"><i class="layui-icon"></i> 搜 索</button>
                    <button type="button" class="layui-btn" style="margin-left:300px;" onclick="OpenAdd()">添加新用户</button>
                </div>
            </div>
            <!-----查询条件----->
            <!--表格-->
            <table id="table" class="layui-table" lay-filter="table"></table>
            <!--表格-->
        </div>
    </div>

    <script type="text/html" id="templetType">
        {{#  if(d.type === '1'){ }}
         管理员
        {{#  } else { }}
          普通用户
        {{#  } }}
    </script>
    <script type="text/html" id="tempCreateDate">
        {{#}}
        {{  d.createDate.replace('T',' ') }}
        {{#}}
    </script>
 
    <script type="text/javascript">
        //初始化
        $(function () {
            PageList();
        });
        $(function () {
            $("#selUserType").change(function () {
                PageList();
            });
        });
        //查询
        function PageList() {
            var name = $("#txtUserName").val();//查询条件
            var type = $("#selUserType").val();

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

                table.render({
                    elem: '#table'//对应着Table的ID
                    , method: 'POST'
                    , url: '/User/GetUserPage'//URL为数据接口的地址
                    , where: { //where就是ajax的data，但不包括分页条件
                        "name": name,
                        "type": type,
                    }
                    , request: {  //分页条件： page  limit
                        pageName: 'page',    //页码的参数名称，默认：page 或者 index、pageIndex
                        limitName: 'size'  //每页数据量的参数名，默认：limit 或者 size、pageSize
                    }
                    , parseData: function (res) {
                        return {
                            "code": 0,//数据类型,必须的
                            "count": res.total,//总记录数,用于分页
                            "data": res.data,//必须的
                        }
                    }
                    , cols: [[
                        { field: 'account', title: 'ID', hide: true }
                        , { field: 'name', title: '姓名', sort: true }
                        , { field: 'type', title: '类型', sort: true, templet: "#templetType" }
                        , { field: 'createBy', title: '创建者', sort: true, }
                        , { field: 'createDate', title: '创建日', sort: true, templet: "#tempCreateDate" }
                        , { title: '操作', toolbar: '#barDemo' }
                    ]],
                    page: true,//开启分页功能
                    limit: 15,//当前每页条数
                    limits: [15, 30, 50, 100],//每页条数集合
                });
            });
        }
    </script>
    <script type="text/html" id="barDemo">
        <a class="layui-btn layui-btn-xs" lay-event="edit">编辑</a>
        <a class="layui-btn layui-btn-danger layui-btn-xs" lay-event="del">删除</a>
    </script>
    <script>
        layui.use('table', function () {
            var table = layui.table;
            //监听工具条
            table.on('tool(table)', function (obj) {
                var data = obj.data;
                if (obj.event === 'edit') {
                    OpenEdit(data.account, data.name, data.type);
                }
                if (obj.event === 'del') {
                    OpenDel(data.account, data.name);
                }
            });
        });
    </script>
    <script>
        //添加用户ajax
        function AddUser() {
            var name = $("#txtName").val();
            var type = $("#selType").val();

            if (name) {
                $.ajax({
                    type: "POST",
                    url: "/User/AddUser",
                    data: {
                        "name": name,
                        "type": type,
                    },
                    success: function (result) {
                        if (result.success) {
                            layer.msg('添加成功!', {
                                icon: 1,
                                time: 1000 //1秒关闭（如果不配置，默认是3秒）
                            }, function () {
                                PageList(1);
                                layer.closeAll();
                            });
                        }
                        else {
                            layer.msg('添加失败!');
                        }
                    }
                });
            }
            else {
                layer.msg("请填写[账号]!");
            }
        }
    </script>

    <script>
        function EditUser() {
            var account = $("#spanId").html();
            var name = $("#txtName").val();
            var type = $("#selType").val();

            if (account) {
                if (name) {
                    $.ajax({
                        type: "POST",
                        url: "/User/EditUser",
                        data: {
                            "account": account,
                            "name": name,
                            "type": type,
                        },
                        success: function (result) {
                            if (result.success) {
                                layer.msg('保存成功!', {
                                    icon: 1,
                                    time: 1000 //1秒关闭（如果不配置，默认是3秒）
                                }, function () {
                                    PageList(1);
                                    layer.closeAll();
                                });
                            }
                            else {
                                layer.msg('保存失败!');
                            }
                        }
                    });
                }
                else {
                    layer.msg("请填写[账号]!");
                }
            }
        }
    </script>

    <script>
        function DelUser(id) {
            var account = id;
            if (id) {
                $.ajax({
                    type: "POST",
                    url: "/User/DelUser",
                    data: {
                        "account": account,
                    },
                    success: function (result) {
                        if (result.success) {
                            layer.msg('删除成功!', {
                                icon: 1,
                                time: 1000 //1秒关闭（如果不配置，默认是3秒）
                            }, function () {
                                PageList(1);
                                layer.closeAll();
                            });
                        }
                        else {
                            layer.msg('删除失败!');
                        }
                    }
                });
            }
            else {
                layer.msg("删除失败!!");
            }
        }
    </script>

</body>
</html>

MVC部分:

using Microsoft.AspNetCore.Mvc;
using VoltageInvoiceBLL;
using VoltageInvoiceModel;
using VoltageInvoiceUI.Models;

namespace VoltageInvoiceUI.Controllers
{
    public class UserController : BaseController
    {
        #region 页面
        public UserController()
        {
            if (string.IsNullOrWhiteSpace(LOGIN_USER_NAME) || string.IsNullOrWhiteSpace(LOGIN_USER_TYPE) || string.IsNullOrWhiteSpace(LOGIN_USER_ACCOUNT))
            {
                RedirectToAction("LogIn", "Home");//返回登录页面
            }
        }
        //列表页面
        public IActionResult Index()
        {
            return View();
        }
        #endregion

        #region 用户管理
        public IActionResult Edit()
        {
            return View();
        }
        /// <summary>
        /// 分页查询用户
        /// </summary>
        public IActionResult GetUserPage(string name, string type, int page, int size)
        {
            PageResponse result = new PageResponse();

            UserBLL bll = new UserBLL();
            int total = 0;//总记录数
            int allPage = 0;//总分页数

            var list = bll.GetUserPage(name, type, page, size, ref total);
            if (list != null && list.Count > 0)
            {
                allPage = GetAllPage(total, size);
                result.total = total;
                result.allPage = allPage;
                result.pageIndex = page;
                result.pageSize = size;
                result.data = list;
            }
            return Json(result);
        }

        /// <summary>
        /// 添加用户
        /// </summary>
        [HttpPost]
        public IActionResult AddUser(string name, string type)
        {
            RestFulClass rfc = new RestFulClass();

            Userinfo user = new Userinfo()
            {
                Account = Guid.NewGuid().ToString(),
                Name = name,
                PWD = "123456",
                TYPE = type,
                IsDeleted = false,
                TAX = "",
                CreateBy = LOGIN_USER_NAME,
                CreateDate = DateTime.Now,
            };
            try
            {
                UserBLL bll = new UserBLL();
                var b = bll.AddUser(user);

                if (b)
                    rfc.success = true;
                else
                    rfc.success = false;
            }
            catch (Exception ex)
            {
                rfc.success = false;
                rfc.message = ex.Message;
            }

            return Json(rfc);
        }
        /// <summary>
        /// 编辑用户
        /// </summary>
        [HttpPost]
        public IActionResult EditUser(string account, string name, string type)
        {
            RestFulClass rfc = new RestFulClass();

            Userinfo user = new Userinfo()
            {
                Account = account,
                Name = name,
                TYPE = type,
                UpdateBy = LOGIN_USER_NAME,
                UpdateDate = DateTime.Now,
            };

            try
            {
                UserBLL bll = new UserBLL();
                var b = bll.EditUser(user);

                if (b)
                    rfc.success = true;
                else
                    rfc.success = false;
            }
            catch (Exception ex)
            {
                rfc.success = false;
                rfc.message = ex.Message;
            }

            return Json(rfc);
        }

        /// <summary>
        /// 删除用户
        /// </summary>
        [HttpPost]
        public IActionResult DelUser(string account)
        {
            RestFulClass rfc = new RestFulClass();
            try
            {
                if (!string.IsNullOrWhiteSpace(account))
                {
                    UserBLL bll = new UserBLL();
                    var b = bll.DelUser(account);

                    if (b)
                        rfc.success = true;
                    else
                        rfc.success = false;
                }
                else
                {
                    rfc.success = false;
                    rfc.message = "缺少需删除的用户id";
                }
            }
            catch (Exception ex)
            {
                rfc.success = false;
                rfc.message = ex.Message;
            }

            return Json(rfc);
        }
        #endregion

    }
}

SqlSugar部分:

using SqlSugar;
using VoltageInvoiceDAL;
using VoltageInvoiceModel;

namespace VoltageInvoiceBLL
{
    public class UserBLL
    {
        SqlSugarHelper sugar = new SqlSugarHelper();

        #region 登录验证
        /// <summary>
        /// 登录验证
        /// </summary>
        public Userinfo CheckLogin(string name, string pwd)
        {
            var db = sugar.SqlClient();//数据库连接对象
            var user = db.Queryable<Userinfo>()
                .Where(a => a.Name.Equals(name) && a.PWD.Equals(pwd) && a.IsDeleted == false)
                .ToList().FirstOrDefault();//查询

            return user;//返回登录成功的用户对象
        }

        public Userinfo CheckUser(string account, string pwdOld)
        {
            var db = sugar.SqlClient();//数据库连接对象
            var user = db.Queryable<Userinfo>()
                .Where(a => a.Account.Equals(account) && a.PWD.Equals(pwdOld) && a.IsDeleted == false)
                .ToList().FirstOrDefault();//查询

            return user;//返回登录成功的用户对象
        }
        /// <summary>
        /// 修改密码
        /// </summary>
        public bool ChangePwd(string account, string pwdNew)
        {
            var db = sugar.SqlClient();//数据库连接对象
            string sql = $"update Userinfo set PWD='{pwdNew}' where Account='{account}'";
            int i = db.Ado.ExecuteCommand(sql);

            return i > 0;
        }

        #endregion

        #region 编辑用户
        /// <summary>
        /// 分页查询用户数据
        /// </summary>
        public List<Userinfo> GetUserPage(string name, string type, int pageNumber, int pageSize, ref int totalNumber)
        {
            var db = sugar.SqlClient();//数据库连接对象
            var list = db.Queryable<Userinfo>()
                .Where(a => a.IsDeleted == false)
                .WhereIF(!string.IsNullOrWhiteSpace(name), (a => a.Name.Contains(name)))
                .WhereIF(!string.IsNullOrWhiteSpace(type), (a => a.TYPE.Equals(type)))
                .OrderBy(a => a.CreateDate, OrderByType.Desc)
                .ToPageList(pageNumber, pageSize, ref totalNumber);

            int total = totalNumber;
            return list;
        }

        /// <summary>
        /// 新增用户
        /// </summary>
        public bool AddUser(Userinfo userinfo)
        {
            var db = sugar.SqlClient();//数据库连接对象
            var i = db.Insertable(userinfo).ExecuteCommand();

            return i > 0;
        }

        /// <summary>
        /// 编辑用户
        /// </summary>
        public bool EditUser(Userinfo userinfo)
        {
            var db = sugar.SqlClient();//数据库连接对象
            var i = db.Updateable(userinfo)
                .IgnoreColumns(a => a.Account)
                .IgnoreColumns(a => a.PWD)
                .IgnoreColumns(a => a.IsDeleted)
                .IgnoreColumns(a => a.TAX)
                .IgnoreColumns(a => a.CreateBy)
                .IgnoreColumns(a => a.CreateDate)
                .Where(a => a.Account == userinfo.Account)
                .ExecuteCommand();

            return i > 0;
        }
        /// <summary>
        /// 逻辑删除用户
        /// </summary>
        public bool DelUser(string account)
        {
            var db = sugar.SqlClient();//数据库连接对象
            string sql = $"update Userinfo set IsDeleted=1 where Account='{account}'";// IsDeleted=1 逻辑删除
            int i = db.Ado.ExecuteCommand(sql);

            return i > 0;
        }
        #endregion

    }
}

using SqlSugar;
using VoltageInvoiceTools;

namespace VoltageInvoiceDAL
{
    public class SqlSugarHelper
    {
        public string _connectionString = CustomConfigManager.GetConfig("ConnectionString:DB");//获取SQL连接字符串
        public SqlSugarClient _db = null;

        /// <summary>
        /// 构造函数(初始化)
        /// 调用方法:
        ///  var db = sugar.SqlClient();
        /// var user = db.Queryable<Userinfo>().Where(a => a.Name.Equals(uid) && a.PWD.Equals(pwd)).ToList().FirstOrDefault();
        /// </summary>
        public SqlSugarClient SqlClient()
        {
            if (string.IsNullOrEmpty(_connectionString))
                throw new ArgumentNullException("数据库连接字符串为空");

            _db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = _connectionString,//数据库连接字符串,见UI的appsettings.json内配置
                DbType = DbType.SqlServer,//数据库类型
                IsAutoCloseConnection = true,//自动断开连接
                MoreSettings = new ConnMoreSettings()
                {
                    IsWithNoLockQuery = true,//为true表式查询的时候默认会加上.With(SqlWith.NoLock)，
                    IsAutoRemoveDataCache = true//为true自动清除缓存
                }
            });
            //输入最终SQL语句...
            _db.Aop.OnLogExecuting = (sql, pars) =>
            {
                var s1 = sql;//断点打在这里看内部生成的sql语句...
            };

            return _db;
        }

    }
}

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",

  //数据库连接字符串:
  "ConnectionString": {
    "DB": "Server=.;Database=VoltageInvoiceDB;User ID=sa;Password=123456;"
  }

}