.net core后端框架实战4：数据库ORM sqlsugar集成

定义一个数据仓储实体，对标数据库表结构/// t_sys_user:数据库映射类/// TenantAttribute config 为配置存储库 data为数据存储库[Serializable] //标记序列化] //标记数据表"user"] //标记数据库/// 主键[SugarColumn(ColumnName = "Id", IsPrimaryKey = true, IsIdentity

二小姐607

1163人浏览 · 2025-03-25 14:43:16

二小姐607 · 2025-03-25 14:43:16 发布

一、引言

在前面几篇文章中，我们描述了net框架、http管道、配置、serilog日志等，对net框架进行了一个基础的描述。在本篇中，我们进入数据库操作部分。

在NET中操作数据库的几种常见方式有ADO.NET、ORM框架。ADO.NET属于底层的数据库访问技术，通过SQL语句直接操作数据库；ORM（对象关系映射）则是通过将数据库表映射为程序类，以面向对象的方式操作数据库，无需编写SQL语句。
ORM数据库访问有EF Core、Dapper、Sqlsugar等方式，sqlsugar是介于EF Core和Dapper中间的实现方式，高性能、兼容多种数据库、语法简洁。是web快速开发的优秀工具。

在前面几篇文章中，我们对 .NET 框架进行了较为全面的基础性描述，涵盖了矿建搭建、 HTTP 管道、配置管理以及 Serilog 日志等多个重要方面，勾勒除了一个基础的.NET技术生态轮廓。
本篇我们将开始net数据库操作方面的知识。在 .NET 中操作数据库有几种常见方式，其中 ADO.NET 和 ORM（对象关系映射）框架是最主要的两种。 ADO.NET 属于底层的数据库访问技术，通过直接执行 SQL 语句来操作数据库，这种方式赋予开发者极大的灵活性，但在复杂项目中可能导致大量重复性代码。相比之下，ORM 框架通过将数据库表映射为程序中的类，使开发者能够以面向对象的方式操作数据库，无需手动编写繁琐的 SQL 语句，从而提高开发效率并减少错误。
在众多 ORM 框架中，EF Core、Dapper 和 SqlSugar 是较为突出的几种。 SqlSugar 作为介于 EF Core 和 Dapper 之间的实现方式，兼具高性能、兼容多种数据库以及语法简洁等优势，是 web 快速开发的优秀工具。它在开发效率、代码可维护性和安全性方面表现出色，特别适合快速开发和中小型项目。然而，在处理复杂查询时可能需要手动优化，且存在一定的学习成本和灵活性限制。

数据库操作结构图

在这里插入图片描述

ORM框架对比

框架	发布机构	是否开源	优点	缺点
EF Core	微软（国外）	是	1. 官方支持，与.NET生态深度集成 2. 支持LINQ强类型查询 3. 跨平台兼容性好，支持多种数据库 4. 提供Code First和Database First模式	1. 复杂查询性能较低，生成的SQL不够理想 2. 对低版本数据库支持较弱 3. 学习曲线陡峭，配置复杂
Dapper	Stack Overflow（国外）	是	1. 高性能（接近原生ADO.NET） 2. 轻量级（编译后仅40KB） 3. 支持多种数据库（SQL Server、MySQL等） 4. 灵活性高，适合直接编写SQL	1. 需手动处理复杂对象关系映射 2. 事务操作需手动管理 3. 复杂查询需大量手写SQL，维护成本高
SqlSugar	果糖大数据科技团队（国产）	是	1. 高性能（优于Dapper的部分场景） 2. 多库无缝切换（支持30+数据库） 3. 简单易用，支持自动分表、多租户 4. 提供AOT兼容和低代码开发支持	1. 高级功能需学习特定语法（如分表配置） 2. 文档相对分散，部分高级特性示例较少

二、SqlSugar引入

1、依赖包安装

nuget包	功能描述
SqlSugarCore	SqlSugar依赖包

2、数据库配置

在系统配置中添加数据库连接配置,参照上篇配置文件读取系统配置

    "AppSettings": {
        //用户库连接信息
        "MySqlUserConnection": "server=127.0.0.1;Database=DBUser;Uid=system;Pwd=123456;CharSet=utf8mb4;SslMode=none",
        //业务库连接信息
        "MySqlDataConnection": "server=127.0.0.1;Database=DBData;Uid=system;Pwd=123456;CharSet=utf8mb4;SslMode=none",
        //配置库连接信息
        "MySqlConfigConnection": "server=127.0.0.1;Database=DBConfig;Uid=system;Pwd=123456;CharSet=utf8mb4;SslMode=none"
    }

3、数据库服务引入

我们使用SqlSugarScope 单例模式访问数据库，对新手更加友好。

        //在program.cs中注册服务
	    builder.Services.AddSqlsugarSetup();
        
		//编写sqlsugar注册函数
        public static void AddSqlsugarSetup(this IServiceCollection services)
        {
            //查询配置服务
            AppSettingService appSetting =services?.BuildServiceProvider()?.GetService<AppSettingService>();
            //数据库链接,修改配置里面的SqlServerConnection的字符串
            var UserConnectionString = appSetting?.MySqlUserConnection;
            var DataConnectionString = appSetting?.MySqlDataConnection;
            var ConficConnectionString = appSetting?.MySqlConfigConnection;



            //这边是SqlSugarScope用AddSingleton单例，适合多租户
            #region 创建连接参数
            var connections = new List<ConnectionConfig>() {
                new ConnectionConfig
                {
                    //数据库类型
                    DbType =  SqlSugar.DbType.MySql,
                    //连接字符串
                    ConnectionString = UserConnectionString,
                    //自动释放和关闭数据库连接
                    IsAutoCloseConnection = true,
                    //多租户唯一标识
                    ConfigId = "user"
                },
                new ConnectionConfig
                {
                    DbType = SqlSugar.DbType.MySql,
                    ConnectionString = DataConnectionString,
                    IsAutoCloseConnection = true,
                    ConfigId = "data"
                }
                ,new ConnectionConfig
                {
                    DbType = SqlSugar.DbType.MySql,
                    ConnectionString = ConficConnectionString,
                    IsAutoCloseConnection = true,
                    ConfigId = "config"
                }
            };
            #endregion

            #region 注册单例服务-SqlSugarScope
            services.AddSingleton<ISqlSugarClient>(s => {
                SqlSugarScope Db = new SqlSugarScope(connections, db => {
                    connections.ForEach(con =>
                    {
                        var tdb = db.GetConnection(con.ConfigId);
                        // 打印SQL语句
                        tdb.Aop.OnLogExecuting = (sql, parameters) =>
                        {
                            var originColor = Console.ForegroundColor;
                            if (sql.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
                                Console.ForegroundColor = ConsoleColor.Green;
                            if (sql.StartsWith("UPDATE", StringComparison.OrdinalIgnoreCase) ||
                                sql.StartsWith("INSERT", StringComparison.OrdinalIgnoreCase))
                                Console.ForegroundColor = ConsoleColor.Yellow;
                            if (sql.StartsWith("DELETE", StringComparison.OrdinalIgnoreCase))
                                Console.ForegroundColor = ConsoleColor.Red;
                            Console.WriteLine("【" + DateTime.Now + "——执行SQL】\r\n" +
                                              UtilMethods.GetSqlString(db.CurrentConnectionConfig.DbType, sql, parameters) +
                                              "\r\n");
                            Console.ForegroundColor = originColor;
                        };
                        //数据处理：增、删 和 改
                        tdb.Aop.DataExecuting = (oldValue, entityInfo) =>
                        {
                            try
                            {
                                if (entityInfo.OperationType == DataFilterType.InsertByObject)
                                {
                                }
                                else if (entityInfo.OperationType == DataFilterType.UpdateByObject)
                                {
                                }
                            }
                            catch
                            {
                            }
                        };
                        //查询事件 
                        tdb.Aop.DataExecuted = (value, entity) => { };
                        tdb.Aop.OnError = ex =>
                        {
                            if (ex.Parametres == null) return;
                            var originColor = Console.ForegroundColor;
                            Console.ForegroundColor = ConsoleColor.DarkRed;
                            var pars = db.Utilities.SerializeObject(
                                ((SugarParameter[])ex.Parametres).ToDictionary(it => it.ParameterName, it => it.Value));
                            Console.ForegroundColor = originColor;
                            Console.WriteLine("【" + DateTime.Now + "——执行SQL异常】\r\n" + pars + " \r\n");
                        };

                        //监控所有超过1秒的Sql 
                        tdb.Aop.OnLogExecuted = (sql, p) =>
                        {
                            if (db.Ado.SqlExecutionTime.TotalSeconds > 1)
                            {
                                //代码CS文件名
                                var fileName = db.Ado.SqlStackTrace.FirstFileName;
                                //代码行数
                                var fileLine = db.Ado.SqlStackTrace.FirstLine;
                                //方法名
                                var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName;
                                //db.Ado.SqlStackTrace.MyStackTraceList[1].xxx 获取上层方法的信息

                                Console.WriteLine("【" + DateTime.Now + "——执行SQL超时】\r\n" + fileName + " \r\n");
                            }

                            ;
                        };

                    });
                });
                return Db;

            });
            #endregion
        }

三、SqlSugar 实体定义

1、定义数据实体

定义一个数据仓储实体，对标数据库表结构

/// <summary>
/// t_sys_user:数据库映射类
/// TenantAttribute config 为配置存储库  data为数据存储库
/// </summary>
[Serializable]  //标记序列化
[SugarTable("t_sys_user")] //标记数据表
[TenantAttribute("user")] //标记数据库
public class TSysUser
{
	/// <summary>
	/// 主键
	/// </summary>
	[SugarColumn(ColumnName = "Id", IsPrimaryKey = true, IsIdentity = true,ColumnDescription ="自增长ID")]
    public int Id { get; set; }

	/// <summary>
	/// 用户名
	/// </summary>
	[SugarColumn(ColumnName = "Name")]
    public string Name { get; set; }

	/// <summary>
	/// 密码
	/// </summary>
	[SugarColumn(ColumnName = "PassWord")]
    public string PassWord { get; set; }

	/// <summary>
	/// 角色ID
	/// </summary>
	[SugarColumn(ColumnDescription = "角色IDs", IsJson = true)]
	public List<int> UserRolesId { get; set; }

	/// <summary>
	/// 状态(1正常，0冻结)
	/// 默认值:1
	/// </summary>
	[SugarColumn(ColumnName = "Status", DefaultValue = "1")]
    public int Status { get; set; }

	/// <summary>
	/// 备注
	/// </summary>
	[SugarColumn(ColumnName = "Remark")]
    public string Remark { get; set; }

    /// <summary>
    /// 创建时间
    /// </summary>
    [SugarColumn(ColumnName = "CreateDT",ColumnDescription ="创建时间",IsOnlyIgnoreUpdate =true)]
    public DateTime CreateDT { get; set; }
    /// <summary>
    /// 更新时间
    /// </summary>
    [SugarColumn(ColumnName = "UpdateDT", ColumnDescription = "更新时间", IsOnlyIgnoreInsert = true)]
    public DateTime UpdateDT { get; set; }

}

2、定义泛型仓储

我们定义一个数据仓储的实现类，泛型结构。
将数据结构实现封装起来，用于简化实现。示例代码仅展示查询部分，完整代码在资源中上传。

    //定义数据仓储实现类，泛型
    public class SqlSugarRepository<TEntity>where TEntity : class, new()
    {

        #region 属性

        /// <summary>
        /// 初始化 SqlSugar 客户端
        /// </summary>
        private readonly ISqlSugarClient _db;

        /// <summary>
        /// 数据库上下文
        /// </summary>
        public ISqlSugarClient Context { get; }

        /// <summary>
        /// 独立数据库上下文
        /// </summary>
        private SqlSugarScopeProvider EntityContext { get; }

        /// <summary>
        /// 实体集合
        /// </summary>
        private ISugarQueryable<TEntity> Entities => _db.AsTenant().GetConnectionScopeWithAttr<TEntity>().Queryable<TEntity>();

        /// <summary>
        /// 原生 Ado 对象
        /// </summary>
        private IAdo Ado { get; }

        #endregion

        #region 构造函数
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="db"></param>
        public SqlSugarRepository(ISqlSugarClient db)
        {
            try
            {
                Context = _db = db.AsTenant().GetConnectionScopeWithAttr<TEntity>();
                // 命名空间中包含.CreateTable 就不会创建表
                string s1 = typeof(TEntity).FullName;
                if (typeof(TEntity).FullName.Contains(".CreateTable"))
                {
                    _db.CodeFirst.InitTables<TEntity>();
                }
                EntityContext = _db.AsTenant().GetConnectionScopeWithAttr<TEntity>();
                Ado = EntityContext.Ado;
            }
            catch (Exception ex)
            {
                string s = ex.Message;
            }

        }
        #endregion

        #region 查询

        /// <summary>
        /// 获取总数
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <returns></returns>
        public int Count(Expression<Func<TEntity, bool>> whereExpression)
        {
            return Entities.Count(whereExpression);
        }

        /// <summary>
        /// 获取总数
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <returns></returns>
        public Task<int> CountAsync(Expression<Func<TEntity, bool>> whereExpression)
        {
            return Entities.CountAsync(whereExpression);
        }

        /// <summary>
        /// 检查是否存在
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <returns></returns>
        public bool Any(Expression<Func<TEntity, bool>> whereExpression)
        {
            return Entities.Any(whereExpression);
        }

        /// <summary>
        /// 检查是否存在
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <returns></returns>
        public async Task<bool> AnyAsync(Expression<Func<TEntity, bool>> whereExpression)
        {
            return await Entities.AnyAsync(whereExpression);
        }

        /// <summary>
        /// 通过主键获取实体
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public TEntity Single(dynamic Id)
        {
            return Entities.InSingle(Id);
        }

        /// <summary>
        /// 获取一个实体
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <returns></returns>
        public TEntity Single(Expression<Func<TEntity, bool>> whereExpression)
        {
            return Entities.Single(whereExpression);
        }

        /// <summary>
        /// 获取一个实体
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <returns></returns>
        public async Task<TEntity> SingleAsync(Expression<Func<TEntity, bool>> whereExpression)
        {
            return await Entities.SingleAsync(whereExpression);
        }

        /// <summary>
        /// 获取一个实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<TEntity> SingleAsync(dynamic id)
        {
            return await Entities.InSingleAsync(id);
        }

        /// <summary>
        /// 获取一个实体
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <returns></returns>
        public TEntity FirstOrDefault(Expression<Func<TEntity, bool>> whereExpression)
        {
            return Entities.First(whereExpression);
        }

        /// <summary>
        /// 获取一个实体
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <returns></returns>
        public async Task<TEntity> FirstOrDefaultAsync(Expression<Func<TEntity, bool>> whereExpression)
        {
            return await Entities.FirstAsync(whereExpression);
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public List<TEntity> ToList()
        {
            return Entities.ToList();
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <returns></returns>
        public List<TEntity> ToList(Expression<Func<TEntity, bool>> whereExpression)
        {
            return Entities.Where(whereExpression).ToList();
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <param name="orderByExpression"></param>
        /// <param name="orderByType"></param>
        /// <returns></returns>
        public List<TEntity> ToList(Expression<Func<TEntity, bool>> whereExpression,
            Expression<Func<TEntity, object>> orderByExpression = null, OrderByType orderByType = OrderByType.Asc)
        {
            return Entities.OrderByIF(orderByExpression != null, orderByExpression, orderByType).Where(whereExpression)
                .ToList();
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public Task<List<TEntity>> ToListAsync()
        {
            return Entities.ToListAsync();
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <returns></returns>
        public Task<List<TEntity>> ToListAsync(Expression<Func<TEntity, bool>> whereExpression)
        {
            return Entities.Where(whereExpression).ToListAsync();
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="whereExpression"></param>
        /// <param name="orderByExpression"></param>
        /// <param name="orderByType"></param>
        /// <returns></returns>
        public Task<List<TEntity>> ToListAsync(Expression<Func<TEntity, bool>> whereExpression,
            Expression<Func<TEntity, object>> orderByExpression = null, OrderByType orderByType = OrderByType.Asc)
        {
            return Entities.OrderByIF(orderByExpression != null, orderByExpression, orderByType).Where(whereExpression)
                .ToListAsync();
        }

        #endregion
}

3、仓储注册、实体使用

在 .NET 依赖注入（DI）中，使用 services.AddScoped(typeof(SqlSugarRepository<>)) 注册开放泛型类型时，并不会自动注入所有可能的封闭泛型类型（如 SqlSugarRepository<User>、SqlSugarRepository<TSysUser> 等）。
但依赖注入容器（如 ASP.NET Core 的 ServiceCollection）会动态处理泛型类型的解析,容器会根据已注册的开放泛型类型动态生成 SqlSugarRepository<User> 的实例，无需显式注册每个封闭泛型类型。

1.在 Program.cs 或依赖注入配置中，注册开放泛型仓储：

    //注册数据仓储（开放泛型类型）
	builder.Services.AddScoped(typeof(SqlSugarRepository<>));

2.直接在构造函数或属性中注入具体类型的仓储

	public class UserService
	{
		private readonly SqlSugarRepository<User> _userRepository;

		public UserService(SqlSugarRepository<User> userRepository)
		{
			_userRepository = userRepository; // 容器自动解析并注入
		}
	}

四、SqlSugar数据初始化

        /// <summary>
        /// 数据库初始化
        /// </summary>
        public static void InitDB()
        {
            if (appSetting.IsInitDB)  //数据库初始化标志
            {
                try
                {
                    //提取服务
                    ISqlSugarClient sugarClient = services.BuildServiceProvider().GetRequiredService<ISqlSugarClient>();
                    //创建数据库
                    sugarClient.DbMaintenance.CreateDatabase();
                    //获取仓储表对象，批量创建数据表
                    var types = GetSugarTableTypes();
                    sugarClient.CodeFirst.SetStringDefaultLength(200).InitTables(types.ToArray());
                }
                catch (Exception ex)
                {
                    throw ex;
                }

            }
        }
		
        /// <summary>
        /// 提取命名空间下的 带有SugarTable标识的类
        /// </summary>
        /// <returns></returns>
        public static List<Type> GetSugarTableTypes()
        {
            //提取此命名空间的所有定义
            var assemblies = AssemblyHelper.GetAssemblies("Mis2.Net9.WebAPI.Repositories");

            var types = new List<Type>();

            foreach (var assembly in assemblies)
            {
                // 获取所有类型
                var assemblyTypes = assembly.GetTypes();

                // 筛选出带有SugarTable特性的非抽象类
                types.AddRange(assemblyTypes.Where(type =>
                        !type.IsAbstract &&
                        type.IsDefined(typeof(SugarTable), true) // true 表示从继承层次结构中搜索特性
                ));
            }

            return types;
        }

五、时序图

六、总结

在本次技术文章中，我们深入探讨了在.NET 开发中使用 SqlSugar 实现 ORM 数据库操作的相关内容。SqlSugar 作为一种功能强大的 ORM 框架，在提升开发效率、增强代码可维护性和安全性方面具有显著优势，特别适合快速开发和中小型项目。然而，在使用过程中也需要注意其性能开销、学习成本和灵活性受限等问题。通过合理选择适用场景并充分了解其优缺点，开发人员可以更好地利用 SqlSugar 实现高效的数据库操作，提升项目整体质量和开发效率。

1、适用场景
SqlSugar 适用于快速开发和中小型项目，特别是那些需要频繁操作数据库的应用场景，例如 Web 应用和企业管理系统。
2、优点
（1）开发效率高：能够自动生成 SQL 语句，减少手动编写 SQL 的工作量，从而显著提升开发效率。
（2）代码可维护性高：通过对象模型操作数据库，使代码更易读、扩展和重构，便于后期维护。
（3）跨数据库支持*：支持多种数据库，切换数据库时仅需修改配置，降低了数据库迁移成本。
（4）安全性增强*：内置防 SQL 注入机制，有效减少安全风险，保障数据安全。
3、缺点
（1）性能开销：抽象层可能引入延迟，在处理复杂查询时可能需要手动优化，如使用原生 SQL。
（2）学习成本：使用该框架需要掌握其特定的特性和配置，如 Hibernate 的缓存配置等。
（3）灵活性受限：在极端复杂场景下，可能需要绕过 ORM 来实现特定功能。