SqlSugar的使用

SqlSugar的介绍和安装

安装

Nuget包管理器搜索sql sugar，安装SqlSugarCore

安装后，会显示在项目依赖项中

IOC的使用，注入ISqlSugarClient接口

1、新建一个注册服务的扩展类

namespace WebAPI.Config {
    /// <summary>
    /// 扩展类
    /// </summary>
    public static class HostBuilderExtend {
        public static void Register(this WebApplicationBuilder builder) {
           
        }
    }
}

2、在Program.cs中，build之前调用此扩展方法

using WebAPI.Config;

//...
builder.Register();
var app = builder.Build();

3、推荐使用第三方的IOC注册，Autofac

两个全部安装

4、在Program.cs中，进行注册

AOP：面向切面编程

using Autofac;
using Autofac.Extensions.DependencyInjection;
using SqlSugar;

namespace WebAPI.Config {
    /// <summary>
    /// 扩展类
    /// </summary>
    public static class HostBuilderExtend {
        public static void Register(this WebApplicationBuilder builder) {
            //使用Autofac替换一下
            builder.Host.UseServiceProviderFactory(new AutofacServiceProviderFactory());
            builder.Host.ConfigureContainer<ContainerBuilder>(builder => {
                //实现注册逻辑
                #region 注册sqlsugar
                    //输入#region按tag键，就会自动补齐尾标签
                    builder.Register<ISqlSugarClient>(context => {
                    SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() {
                        //连接字符串，改为自己的
                        ConnectionString = "Data Source=DESKTOP-2DUF58Q\SQLEXPRESS;Initial Catalog=ZhaoxiAdminDb1;Persist Security Info=True;User ID=sa;Password=123456",
                        //数据库类型
                        DbType = DbType.SqlServer,
                        //是否自动关闭连接
                        IsAutoCloseConnection = true
                        });
                    //下面是SqlSugar AOP的使用
                    //支持sql语句的输出，方便排查问题
                    db.Aop.OnLogExecuted = (sql, par) => {
                        Console.WriteLine("\r\n");
                        Console.WriteLine($"{DateTime.Now.ToString("yyyyMMdd HH:mm:ss")}，Sql语句：{sql}");
                        Console.WriteLine("===========================================================================");
                    };

                    return db;
                });
                #endregion
                });
        }
    }
}

实体模型

1、新建类库项目，及相关实体类

2、在类库项目中，引入SqlSugarCore包

<ItemGroup>
  <PackageReference Include="SqlSugarCore" Version="5.1.3.23" />
</ItemGroup>

3、将类库生成一下，并在webApi项目中引用

基类

public class IBase
{
    /// <summary>
    /// 主键
    /// </summary>
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public long Id { get; set; }
}

public class IEntity: IBase
    {
        /// <summary>
        /// 描述
        /// </summary>
        [SugarColumn(IsNullable = true)]
        public string Description { get; set; }
        /// <summary>
        /// 创建人Id
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public long CreateUserId { get; set; }
        /// <summary>
        /// 创建日期
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public DateTime CreateDate { get; set; }
        /// <summary>
        /// 修改人Id
        /// </summary>
        [SugarColumn(IsNullable = true)]
        public long ModifyUserId { get; set; }
        /// <summary>
        /// 修改日期
        /// </summary>
        [SugarColumn(IsNullable = true)]
        public DateTime? ModifyDate { get; set; }
        /// <summary>
        /// 是否删除
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public int IsDeleted { get; set; }
    }

实体类

菜单表

public class Menu : IEntity
    {
        /// <summary>
        /// 名称
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public string Name { get; set; }
        /// <summary>
        /// 路由地址
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public string Index { get; set; }
        /// <summary>
        /// 项目中的页面路径
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public string FilePath { get; set; }
        /// <summary>
        /// 父级
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public long ParentId { get; set; }
        /// <summary>
        /// 排序
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public int Order { get; set; }
        /// <summary>
        /// 是否启用（0=未启用，1=启用）
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public bool IsEnable { get; set; }
    }

角色表

public class Role : IEntity
    {
        /// <summary>
        /// 名称
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public string Name { get; set; }
        /// <summary>
        /// 排序
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public int Order { get; set; }
        /// <summary>
        /// 是否启用（0=未启用，1=启用）
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public bool IsEnable { get; set; }
    }

用户表

public class Users: IEntity
    {
        /// <summary>
        /// 用户名
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public string Name { get; set; }
        /// <summary>
        /// 昵称
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public string NickName { get; set; }
        /// <summary>
        /// 密码
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public string Password { get; set; }
        /// <summary>
        /// 用户类型（0=超级管理员，1=普通用户）
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public int UserType { get; set; }
        /// <summary>
        /// 是否启用（0=未启用，1=启用）
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public bool IsEnable { get; set; } 

    }   

菜单-角色关系

public class MenuRoleRelation : IBase
    {
        /// <summary>
        /// 菜单主键
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public long MenuId { get; set; }
        /// <summary>
        /// 角色主键
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public long RoleId { get; set; }
    }

用户-角色关系

public class UserRoleRelation : IBase
    {
        /// <summary>
        /// 用户主键
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public long UserId { get; set; }
        /// <summary>
        /// 角色主键
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public long RoleId { get; set; }
    }

DTO

角色

public class RoleAdd
    {
        public string Name { get; set; }
        public int Order { get; set; }
        public bool IsEnable { get; set; }
        public string Description { get; set; }
    }

public class RoleEdit
    {
        public long Id { get; set; }
        public string Name { get; set; }
        public int Order { get; set; }
        public bool IsEnable { get; set; }
        public string Description { get; set; }
    }

public class RoleReq
    {
        public string Name { get; set; } 
        public bool IsEnable { get; set; }
        public string Description { get; set; }
        public int PageIndex { get; set; }
        public int PageSize { get; set; }
    }

public class RoleRes
    {
        public long Id { get; set; }
        /// <summary>
        /// 名称
        /// </summary> 
        public string Name { get; set; }
        /// <summary>
        /// 排序
        /// </summary> 
        public int Order { get; set; }
        /// <summary>
        /// 是否启用（0=未启用，1=启用）
        /// </summary> 
        public bool IsEnable { get; set; } 
        /// <summary>
        /// 描述
        /// </summary> 
        public string Description { get; set; }
        /// <summary>
        /// 创建人Id
        /// </summary> 
        public string CreateUserId { get; set; }
        /// <summary>
        /// 创建日期
        /// </summary> 
        public DateTime CreateDate { get; set; }
        /// <summary>
        /// 修改人Id
        /// </summary> 
        public string ModifyUserId { get; set; }
        /// <summary>
        /// 修改日期
        /// </summary> 
        public DateTime? ModifyDate { get; set; }
        /// <summary>
        /// 是否删除
        /// </summary> 
        public int IsDeleted { get; set; }
    }

用户

public class UserAdd
    {
        public string Name { get; set; }
        public string NickName { get; set; }
        public string Password { get; set; }
        public bool IsEnable { get; set; }
        public string Description { get; set; }
    }

public class UserEdit
    {
        public long Id { get; set; }
        public string Name { get; set; }
        public string NickName { get; set; }
        public string Password { get; set; }
        public bool IsEnable { get; set; }
        public string Description { get; set; }
    }

public class UserReq
    {
        public string Name { get; set; }
        public string NickName { get; set; } 
        public int UserType { get; set; }
        public bool IsEnable { get; set; }
        public string Description { get; set; }
        public int PageIndex { get; set; }
        public int PageSize { get; set; }
    }

public class UserRes
    {
        /// <summary>
        /// 主键
        /// </summary>
        public long Id { get; set; }
        /// <summary>
        /// 用户名
        /// </summary> 
        public string Name { get; set; }
        /// <summary>
        /// 昵称
        /// </summary> 
        public string NickName { get; set; }
        /// <summary>
        /// 密码
        /// </summary>
        public string Password { get; set; }
        /// <summary>
        /// 用户类型
        /// </summary>
        public int UserType { get; set; }
        /// <summary>
        /// 角色名
        /// </summary>
        public string RoleName { get; set; }
        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime CreateDate { get; set; }
        /// <summary>
        /// 是否启用
        /// </summary>
        public bool IsEnable { get; set; }
        /// <summary>
        /// 描述
        /// </summary>
        public string Description { get; set; }
    }

菜单

public class MenuAdd
{
    public string Name { get; set; }
    public string Index { get; set; }
    public string FilePath { get; set; }
    public long ParentId { get; set; }
    public int Order { get; set; }
    public bool IsEnable { get; set; }
    public string Description { get; set; }

}

public class MenuEdit
    {
        public long Id { get; set; }
        public string Name { get; set; }
        public string Index { get; set; }
        public string FilePath { get; set; }
        public long ParentId { get; set; }
        public int Order { get; set; }
        public bool IsEnable { get; set; }
        public string Description { get; set; }
    }

public class MenuReq
    {
        public string Name { get; set; }
        public string Index { get; set; }
        public string FilePath { get; set; }
        public long ParentId { get; set; }
        public string Description { get; set; }
        public int PageIndex { get; set; }
        public int PageSize { get; set; }
    }

public class MenuRes
    {
        [SugarColumn(IsTreeKey = true)]
        public long Id { get; set; }
        public string Name { get; set; }
        public string Index { get; set; }
        public string FilePath { get; set; }
        public long ParentId { get; set; }
        public string ParentName { get; set; }
        public int Order { get; set; }
        public bool IsEnable { get; set; }
        public string Description { get; set; }
        public DateTime CreateDate { get; set; }
        /// <summary>
        /// 子级
        /// </summary>
        [SqlSugar.SugarColumn(IsIgnore = true)]
        public List<MenuRes> Children { get; set; }
    }

创建数据库及表

1、在webapi项目中，新建一个控制器，用来初始化数据库

创建数据库很简单，一行代码搞定。创建表，是结合反射自动读取实体类。

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Model.Entitys;
using SqlSugar;
using System.Reflection;

namespace WebAPI.Controllers {
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class ToolController : ControllerBase {
        public ISqlSugarClient _db;
        public ToolController(ISqlSugarClient db) {
            _db = db;
        }
        [HttpGet]
        public string InitDateBase() {
            string res = "OK";
            //如果不存在则创建数据库
            _db.DbMaintenance.CreateDatabase();
            //创建表
            string nspace = "Model.Entitys";
            //通过反射读取我们想要的类
            Type[] ass = Assembly.LoadFrom(AppContext.BaseDirectory + "Model.dll").GetTypes().Where(p => p.Namespace == nspace).ToArray();
            //表示不超过200个表
            _db.CodeFirst.SetStringDefaultLength(200).InitTables(ass);
            //初始化炒鸡管理员和菜单
            Users user = new Users() {
                Name = "admin",
                NickName = "炒鸡管理员",
                Password = "123456",
                UserType = 0,
                IsEnable = true,
                Description = "数据库初始化时默认添加的炒鸡管理员",
                CreateDate = DateTime.Now,
                CreateUserId = 0,
                IsDeleted = 0
            };
            long userId = _db.Insertable(user).ExecuteReturnBigIdentity();
            Menu menuRoot = new Menu() {
                Name = "菜单管理",
                Index = "menumanager",
                FilePath = "../views/admin/menu/MenuManager",
                ParentId = 0,
                Order = 0,
                IsEnable = true,
                Description = "数据库初始化时默认添加的默认菜单",
                CreateDate = DateTime.Now,
                CreateUserId = userId,
                IsDeleted = 0
            };
            _db.Insertable(menuRoot).ExecuteReturnBigIdentity();
            return res;
        }

        [HttpGet]
        public string Test() {
            return "OK";
        }
    }
}

2、创建成功后，可以在数据库中看到

会创建一个主数据库文件、日志文件，路径如下（默认路径）

初始化的表数据

SELECT *  FROM [VeloAdmin].[dbo].[Menu]

SELECT *  FROM [VeloAdmin].[dbo].[Users]

数据库连接串的坑

连接串的错误

1、代码创建数据库，连接串不能有AttachDbFilename属性，要使用Initial Catalog属性

连接串

Data Source=LAPTOP-RV9GJL14;AttachDbFilename=D:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\VeloAdmin.MDF;Integrated Security=False;Connect Timeout=30;User Id =velo;Password =velo1234

错误提示：

SqlSugar.SqlSugarException:“中文提示 : 建库失败，库名存在关键字，请换一个名字

English Message : Failed to create the database. The database name has a keyword. Please change the name”

2、Initial Catalog属性不要有文件路径，只需要数据库名即可

连接串

"Data Source=LAPTOP-RV9GJL14;Initial Catalog=D:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\VeloAdmin;Integrated Security=False;Connect Timeout=30;User Id =velo;Password =velo1234"

错误提示：

System.Text.RegularExpressions.RegexParseException:“Invalid pattern 'D:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\VeloAdmin' at offset 18. Unrecognized escape sequence \M.”

3、正确的连接串

Data Source=LAPTOP-RV9GJL14;Initial Catalog=VeloAdmin;Integrated Security=False;Connect Timeout=30;User Id =velo;Password =velo1234