.NET Core SqlSugar

标签：Core layer Name true db NET data ID SqlSugar

概念：

1.官方文档：https://www.donet5.com/Home/Doc?typeId=1180

2.在vs studio中导包 SqlSugarCore

创建模型类：

1.vs studio2022中选择项目

2.选择6.0

3.projram.cs

using SqlSugar;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllersWithViews();

//取消大小写
builder.Services.AddMvc().AddJsonOptions(options => options.JsonSerializerOptions.PropertyNamingPolicy = null);

//注册SqlSugar

#region sqlsuger
builder.Services.AddSingleton<ISqlSugarClient>(s =>
{
    //Scope用SqlSugarClient
    SqlSugarScope sqlSugar = new SqlSugarScope(new ConnectionConfig()
    {
        DbType = SqlSugar.DbType.SqlServer, //对应的数据库类型
        ConnectionString = builder.Configuration.GetConnectionString("SqlServerConnection"),//对应的连接数据库字符串
        IsAutoCloseConnection = true,
    },
   db =>
   {
       //每次上下文都会执行

       //获取IOC对象不要求在一个上下文
       //var log=s.GetService<Log>()

       //获取IOC对象要求在一个上下文
       //var appServive = s.GetService<IHttpContextAccessor>();
       //var log= appServive?.HttpContext?.RequestServices.GetService<Log>();

       db.Aop.OnLogExecuting = (sql, pars) =>
       {

       };
   });
    return sqlSugar;
});
#endregion

var app = builder.Build();

// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
    // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
    app.UseHsts();
}

app.UseHttpsRedirection();
app.UseStaticFiles();

app.UseRouting();

app.UseAuthorization();

app.MapControllerRoute(
    name: "default",
    pattern: "{controller=Users}/{action=Index}/{id?}"); //切换运行  controller代表的是控制器   action代表视图

app.Run();

4.appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "SqlServerConnection": "Server=MPCOMPUTER;Database=Test;User Id=sa;Password=123456;Encrypt=True;TrustServerCertificate=True;"
  },
  "AllowedHosts": "*"
}

5.wwwroat

1.引用js以及css

2.打开layui

https://www.layuiweb.com/demo/button.html

6.HomeController

using Microsoft.AspNetCore.Mvc;
using NETMySQLCore02.Models;
using SqlSugar;
using System.Diagnostics;

namespace NETMySQLCore02.Controllers
{
    public class HomeController : Controller
    {
        private readonly ILogger<HomeController> _logger;

        private readonly ISqlSugarClient db;

        public HomeController(ILogger<HomeController> logger,ISqlSugarClient _db)
        {
            _logger = logger;
            db = _db;
        }

        public IActionResult Index()
        {
            //db.DbFirst.IsCreateAttribute().StringNullable().CreateClassFile("文件路径", "NETMySQLCore02.Models");//创建实体
            return View();
        }

        public IActionResult Privacy()
        {
            return View();
        }

        [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]
        public IActionResult Error()
        {
            return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
        }
    }
}

查询表格：

1.新建控制器并创建视图

1.控制器

using Microsoft.AspNetCore.Mvc;
using NETMySQLCore02.Models;
using SqlSugar;

namespace NETMySQLCore02.Controllers
{
    public class UsersController : Controller
    {
        //定义一个db
        private readonly ISqlSugarClient db;

        /// <summary>
        /// 定义一个构造方法
        /// </summary>
        /// <param name="_db"></param>
        public UsersController(ISqlSugarClient _db)
        {
            db = _db;
        }

        public IActionResult Index()
        {
            return View();
        }

    }
}

2.创建视图（增删改查）

@{
    Layout = null;
}

<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>.NET Core增删改查--sqlsuger</title>
    <link href="~/layui-main/dist/css/layui.css" rel="stylesheet" />

</head>
<body>

    @* 部分查询  表单 *@
    <form class="layui-form" lay-filter="example" style="margin-top:20px">
        <div class="layui-form-item">
            <div class="layui-inline">
                <label class="layui-form-label">姓名</label>
                <div class="layui-input-inline">
                    <input type="text" name="U_Name" placeholder="请输入姓名" class="layui-input">
                </div>
                <div class="layui-inline">
                    <label class="layui-form-label">性别</label>
                    <div class="layui-input-block">
                        <input type="radio" name="U_Sex" value="全部" title="全部" checked="">
                        <input type="radio" name="U_Sex" value="男" title="男">
                        <input type="radio" name="U_Sex" value="女" title="女">
                    </div>
                </div>
                <div class="layui-inline">
                    <label class="layui-form-label">部门</label>
                    <div class="layui-input-inline">
                        <select name="D_ID" lay-search="">
                            <option value="0">--请选择部门--</option>
                            @foreach (var department in ViewData["Departments"] as List<Departments>)
                            {
                                <option value="@department.D_ID">@department.D_Name</option>
                            }
                        </select>
                    </div>
                </div>
                <div class="layui-inline">
                    <div class="layui-input-inline" style="width:400px">
                        <button type="button" lay-submit="" lay-filter="demo1" class="layui-btn layui-btn-normal">查询</button>
                        <button type="button" class="layui-btn layui-btn-danger" onclick="Add()">添加</button>
                    </div>
                </div>
            </div>
        </div>
    </form>

    @* 表格 *@
    <table class="layui-hide" id="test"></table>

    @* 添加表单 *@
    <form class="layui-form" action="" lay-filter="AddUser" id="MyModel" hidden="hidden">
        <input type="hidden" name="U_ID" placeholder="请输入编号" class="layui-input">
        <div class="layui-form-item">
            <label class="layui-form-label">姓名</label>
            <div class="layui-input-block">
                <input type="text" name="U_Name" placeholder="请输入姓名" class="layui-input">
            </div>
        </div>
        <div class="layui-form-item">
            <label class="layui-form-label">密码</label>
            <div class="layui-input-block">
                <input type="text" name="U_Pwd" placeholder="请输入密码" class="layui-input">
            </div>
        </div>
        <div class="layui-form-item">
            <label class="layui-form-label">性别</label>
            <div class="layui-input-block">
                <input type="radio" name="U_Sex" value="男" title="男" checked="">
                <input type="radio" name="U_Sex" value="女" title="女">
            </div>
        </div>
        <div class="layui-form-item">
            <label class="layui-form-label">年龄</label>
            <div class="layui-input-block">
                <input type="text" name="U_Age" placeholder="请输入年龄" class="layui-input">
            </div>
        </div>
        <div class="layui-form-item">
            <label class="layui-form-label">电话</label>
            <div class="layui-input-block">
                <input type="text" name="U_Tel" placeholder="请输入电话" class="layui-input">
            </div>
        </div>
        <div class="layui-inline">
            <label class="layui-form-label">部门</label>
            <div class="layui-input-inline">
                <select name="D_ID" lay-search="" id="DeptId">
                    <option value="0">--请选择部门--</option>
                    @foreach (var department in ViewData["Departments"] as List<Departments>)
                    {
                        <option value="@department.D_ID">@department.D_Name</option>
                    }
                </select>
            </div>
        </div>
        <div class="layui-form-item">
            <div class="layui-input-block">
                <button type="button" class="layui-btn" lay-submit="" lay-filter="demo2" style="margin-top:10px">保存</button>
            </div>
        </div>
    </form>

    @* 操作按钮 *@
    <script type="text/html" id="barDemo">
        <a class="layui-btn" lay-event="edit">编辑</a>
        <a class="layui-btn layui-btn-danger" lay-event="del">删除</a>
    </script>

    @* 添加引用 layui *@
    <script src="~/lib/jquery/dist/jquery.min.js"></script>
    <script src="~/layui-main/dist/layui.js"></script>

    <script>
        var layer;
        layui.use(['table', 'layer', 'form'], function () {
            var table = layui.table;  //layui和.NET Core一样，用啥就要引用啥
            layer = layui.layer;
            var form = layui.form;//提交表单
            table.render({
                elem: '#test',
                url: '/Users/IndexDG',
                // cellMinWidth: 80, //全局定义常规单元格的最小宽度，layui 2.2.1 新增
                cols: [[
                    { type: 'checkbox' },
                    { field: 'U_ID', width: 100, title: '编号', sort: true, align: 'center' },
                    { field: 'U_Name', width: 200, title: '用户名', align: 'center' },
                    { field: 'U_Pwd', width: 100, title: '密码', align: 'center' },
                    {
                        field: 'U_Sex', width: 200, title: '性别', sort: true, align: 'center',
                        templet: function (d) {
                            if (d.U_Sex == "男")
                                return '<span style="color:blue;">男</span>'
                            else
                                return '<span style="color:red;">女</span>'
                        }
                    },
                    { field: 'U_Age', width: 200, title: '年龄', sort: true, align: 'center' },
                    { field: 'U_Tel', width: 150, title: '电话', sort: true, align: 'center' },
                    { field: 'D_Name', width: 200, title: '部门', sort: true, align: 'center' },
                    { fixed: 'right', field: '-', title: '操作', toolbar: '#barDemo', width: 250 }
                ]],
                page: true
            });

            
             //监听提交(部分查询)
         form.on('submit(demo1)', function(data){
         // layer.alert(JSON.stringify(data.field), {
         //   title: '最终的提交信息'
         // })
                table.reload('test', {
                    where: { //设定异步数据接口的额外参数，任意设
                     U_Name: data.field.U_Name,
                     U_Sex: data.field.U_Sex,
                     D_ID: data.field.D_ID
                    }
                    , page: {
                        curr: 1 //重新从第 1 页开始
                    }
                }); //只重载数据
               return false; //阻止刷新
         });

            //监听提交（保存）
            form.on('submit(demo2)', function (data) {
                // layer.alert(JSON.stringify(data.field), {
                //   title: '最终的提交信息'
                // })
                //使用ajax
                $.ajax({
                type: "post",
                url: "/Users/AddUser",
                dataType: "json",
                data: data.field,
                success: function (data) {
                if (data) {
                   layer.msg('保存成功！', { icon: 1, time: 0 });
                   setTimeout(function () {
                       // 关闭弹出层（如果有弹出层的话  关闭所有的）
                       parent.layer.closeAll();
                       // 刷新表格
                       table.reload('test');
                       // window.location.href = "/Users/Index";
                   }, 2000); // 3000 毫秒（3 秒）后关闭弹窗并刷新表格
                } else {
                   layer.msg('保存失败！', { icon: 2, time: 2000 });
                   }
                }
                })
                return false; //阻止刷新
            });

            //监听行工具事件
            table.on('tool(test)', function (obj) {
                var data = obj.data; //当前点击的一行值 类似bootstrap中的formatter中的row参数，获取当前行的值
                if (obj.event === 'del') {
                    layer.confirm('你确定要删除吗？', function (index) {
                        // obj.del();
                        // layer.close(index);
                        $.getJSON("/Users/DeleteUser", { U_ID: data.U_ID }, function (data) {
                            layer.msg('删除成功！', { icon: 1, time: 0 });
                            setTimeout(function () {
                                // 关闭弹出层（如果有弹出层的话  关闭所有的）
                                parent.layer.closeAll();
                                // 刷新表格
                                window.location.href = "/Users/Index";
                            }, 3000); // 3000 毫秒（3 秒）后关闭弹窗并刷新表格
                        })
                    });
                } else if (obj.event === 'edit') {
                    // 在此处输入 layer 的任意代码
                    layer.open({
                        type: 1, // page 层类型
                        area: ['600px', '400px'],
                        title: '修改页面',
                        shade: 0.6, // 遮罩透明度
                        shadeClose: true, // 点击遮罩区域，关闭弹层
                        maxmin: true, // 允许全屏最小化
                        anim: 0, // 0-6 的动画形式，-1 不开启
                        content: $("#MyModel")
                    });
                    $.getJSON("/Users/GetUsersByID", { U_ID: data.U_ID }, function (data) {
                        form.val('AddUser', data);
                        form.render(); //复选框、单选按钮、下拉框可用  重新加载

                    })
                }
            });

        });

        //添加
        function Add() {
            // 在此处输入 layer 的任意代码
            layer.open({
                type: 1, // page 层类型
                area: ['600px', '500px'],
                title: '添加页面',
                shade: 0.6, // 遮罩透明度
                shadeClose: true, // 点击遮罩区域，关闭弹层
                maxmin: true, // 允许全屏最小化
                anim: 0, // 0-6 的动画形式，-1 不开启
                content: $("#MyModel")
            });
        }

    </script>
</body>
</html>

2.查询部分查询

 /// <summary>
 /// 主视图  
 /// </summary>
 /// <returns></returns>
 public IActionResult Index()
 {
     // 绑定下拉框数据
     ViewData["Departments"] = db.Queryable<Models.Departments>().ToList();
     return View();
 }

 /// <summary>
 /// 查询表格  部分查询
 /// </summary>
 /// <param name="limit">页数</param>
 /// <param name="page">页码</param>
 /// <returns></returns>
 public async Task<IActionResult> IndexDG(int limit=10,int page=1,string U_Name = "", string U_Sex = "全部", int D_ID = 0)
 {
     var query = db.Queryable<Models.Users>()
        .InnerJoin<Models.Departments>((u, d) => u.D_ID == d.D_ID);
     //姓名
     if (!string.IsNullOrEmpty(U_Name))
     {
         query = query.Where(u => u.U_Name.Contains(U_Name));
     }
     //性别
     if (U_Sex != "全部")
     {
         query = query.Where(u => u.U_Sex == U_Sex);
     }
     //部门
     if (D_ID != 0)
     {
         query = query.Where(u => u.D_ID == D_ID);
     }
     var list = query.Select((u, d) => new UserView
     {
         U_ID = u.U_ID,
         U_Name = u.U_Name,
         U_Pwd = u.U_Pwd,
         U_Age = u.U_Age,
         U_Sex = u.U_Sex,
         U_Tel = u.U_Tel,
         D_Name = d.D_Name
     }, true).ToList();
     //var list = db.Queryable<Models.Users>()
     //           .InnerJoin<Models.Departments>((u,d) => u.D_ID == d.D_ID)//多个条件用&&
     //           .Select((u,d) => new UserView
     //           {
     //               U_ID = u.U_ID,
     //               U_Name = u.U_Name,
     //               U_Pwd = u.U_Pwd,
     //               U_Age = u.U_Age,
     //               U_Sex = u.U_Sex,
     //               U_Tel = u.U_Tel,
     //               D_Name = d.D_Name
     //           },true).ToList(); //UserList是一个新建的类

     var json = new
     {
         code = "0",
         msg = "",
         data = list.OrderBy(u => u.U_ID).Skip((page - 1) * limit).Take(limit),
         count = list.Count
     };
     return Json(json);
 }

3.添加修改（在同一个表单中）

  /// <summary>
  /// 添加操作  修改操作
  /// </summary>
  /// <param name="u"></param>
  /// <returns></returns>
  public async Task<IActionResult> AddUser(Models.Users u)
  {
      //添加
      if (u.U_ID == 0)
      {
          db.Insertable(u).ExecuteReturnEntity();
      }
      //修改
      else
      {
          db.Updateable(u).ExecuteCommand();
      }
      return Json(true);
  }

  /// <summary>
  /// 找到要修改的编号  FirstOrDefaultAsync
  /// </summary>
  /// <param name="U_ID"></param>
  /// <returns></returns>
  public async Task<IActionResult> GetUsersByID(int U_ID)
  {
      var user = await db.Queryable<Models.Users>().InnerJoin<Models.Departments>((u, d) => u.D_ID == d.D_ID).Select((u, d) => new UserView
      {
          U_ID = u.U_ID,
          U_Name = u.U_Name,
          U_Pwd = u.U_Pwd,
          U_Age = u.U_Age,
          U_Sex = u.U_Sex,
          U_Tel = u.U_Tel,
          D_Name = d.D_Name,
          D_ID = d.D_ID
      }, true).FirstAsync(u => u.U_ID == U_ID);
      return Json(user);
  }

4.删除操作

  /// <summary>
  /// 删除操作
  /// </summary>
  /// <param name="id"></param>
  /// <returns></returns>
  public async Task<IActionResult> DeleteUser(int U_ID)
  {
      await db.Deleteable<Models.Users>().Where(u => u.U_ID == U_ID).ExecuteCommandAsync();
      return Json(true);
  }

标签：Core,layer,Name,true,db,NET,data,ID,SqlSugar
From： https://blog.csdn.net/MxxJxF/article/details/143061357

概念：

创建模型类：

1.vs studio2022中选择项目

2.选择6.0

3.projram.cs

4.appsettings.json

5.wwwroat

6.HomeController

查询表格：

1.新建控制器并创建视图

2.查询部分查询

3.添加修改（在同一个表单中）

4.删除操作

相关文章

赞助商

阅读排行

.NET Core SqlSugar

概念：

创建模型类：

1.vs studio2022中选择项目

2.选择6.0

3.projram.cs

4.appsettings.json

5.wwwroat

6.HomeController

查询表格：

1.新建控制器并创建视图

2.查询 部分查询

3.添加 修改 （在同一个表单中）

4.删除操作

相关文章

赞助商

阅读排行

2.查询部分查询

3.添加修改（在同一个表单中）