.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) {//btn1if (name.toLowerCase() != 'admin'){DelUser(id);}else{layer.msg("不可以删除 admin 账号！");}}, function (index) {//btn2PageList(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  limitpageName: 'page',    //页码的参数名称，默认：page 或者 index、pageIndexlimitName: '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>//添加用户ajaxfunction 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;elserfc.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;elserfc.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;elserfc.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;"}}