基于SqlSugar的数据库访问处理的封装，支持多数据库并使之适应于实际业务开发中

2022 年 3 月 8 日
筆記
SqlSugar

在我的各种开发框架中，数据访问有的基于微软企业库，有的基于EFCore的实体框架，两者各有其应用场景，不过多的去比较。最近在使用SqlSugar的时候，觉得这个数据访问处理的组件确实很灵活，据说性能也是很不错，于是用来整合测试一下，它对多种关系型数据库如SqlServer、Oracle、Mysql、SQLite、PostgreSQL都很容易提供支持，通过特性标注的方式，可以很好的实现数据访问的处理，这点很类似EfCore的处理，同时SqlSugar又提供很灵活的SQL处理操作。因此多花了一些时间，把SqlSugar的数据访问操作进行一定的封装处理，最后使之适应更广泛的应用。在这个处理过程中，我编写一些单元测试用来测试其相关功能，并编写了几个模块的Winform界面进行测试，效果还是很不错，借此总结分享一下。

1、SQLSugar的相关介绍

SqlSugar是一款老牌 .NET 开源ORM框架，由果糖大数据科技团队维护和更新，使用文档可以参考官方文档：//www.donet5.com/Home/Doc, GitHub的地址是：//github.com/donet5/SqlSugar

优点：简单易用、功能齐全、高性能、轻量级，支持数据库：MySql、SqlServer、Sqlite、Oracle 、 postgresql、达梦、人大金仓。

由于它是ORM的框架组件，因此标识性的处理也是常规的操作，一般的SQLSugar对数据实体对象提供和数据库信息的标注处理。

如对于数据库表的标注：

[SugarTable("TB_DictData")]
public class DictDataInfo
{
}

以及对字段信息主键的标注

        /// <summary>
        /// 编号
        /// </summary>
        [SugarColumn(IsPrimaryKey = true)]
        public virtual string ID { get; set; }

或者是自增字段的标注处理

    public class Person 
    {
        //数据库字段
        [SqlSugar.SugarColumn(IsPrimaryKey =true,IsIdentity =true)]
        public int Id { get; set; }

而有些字段，和数据库字段是没有对应关系的，可以设置忽略标识，如下所示。

    public class Person 
    {
        //数据库字段
        [SqlSugar.SugarColumn(IsPrimaryKey =true,IsIdentity =true)]
        public int Id { get; set; }

        public int SexId { get; set; }

        //非数据库字段
        [SqlSugar.SugarColumn(IsIgnore =true)]
        public string SexName { get; set; }

        .......

定义了这些实体和数据库关系后，我们操作数据库，可以使用 SqlSugarClient 或者 SqlSugarScope 对数据库进行增、删、查、改等功能，SqlSugarClient 和SqlSugarScope 几乎一样，两者差异之处，是后者使用单例（单件）模式，如果我们的对象也是单件模式，就考虑使用SqlSugarScope 对象操作数据库。

例如我们创建一个SqlSugarClient的对象实例，用它来操作数据库获取信息。

var db = new SqlSugarClient(new ConnectionConfig()
{
    DbType = DbType.SqlServer,
    ConnectionString = connectionString,
    InitKeyType = InitKeyType.Attribute,
    IsAutoCloseConnection = true,
    AopEvents = new AopEvents
    {
        OnLogExecuting = (sql, p) =>
        {
            Log.Information(sql);
            Log.Information(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value)));
        }
    }
});

那接下来，我们就可以利用db来进行数据的增删改查处理操作了。

     //查询表的所有
     var list = db.Queryable<Student>().ToList();
      
     //插入
     db.Insertable(new Student() { SchoolId = 1, Name = "jack" }).ExecuteCommand();
      
     //更新
     db.Updateable(new Student() { Id = 1, SchoolId = 2, Name = "jack2" }).ExecuteCommand();
      
     //删除
     db.Deleteable<Student>().Where(it => it.Id == 1).ExecuteCommand();

一般来说，我们可能倾向于把操作封装为一个函数处理，如下所示

/// <summary>
/// 保存数据到数据库
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<bool> SaveData(LongVoiceResultDto dto)
{
    bool result = false;
    if(dto != null)
    {
        using(var db = CreateDb())
        {
            var info = new ConsultationInfo();
            info.DiscernStatus = dto.taskId;
            info.OperateStatus = "未识别";
            if (dto.data != null && dto.data.speechResult != null)
            {
                if (dto.data.statusCode == 3)
                {
                    info.OperateStatus = "已识别";
                }
                var speechResult = dto.data.speechResult;
                info.DiscernText = speechResult.resultText;
            }

            result = await db.Insertable(info).ExecuteCommandAsync() > 0;
        }
    }
    return result;
}

从上面的代码来看，我们定义好实体信息后，就可以直接用SqlSugarClient的对象实例来处理数据库信息了，过程非常简单高效，特别对于一些简单的单表操作，非常简洁。

2、SQLSugar的基类封装

上面的简单代码，我们可以看到SqlSugarClient的对象实例的快捷操作数据库操作，非常方便。

不过一般来说，对于一个成熟的项目，我们一般是要尽可能的重用一些处理代码，并提供最大程度的简化封装。因此我们在实际使用来开发项目的时候，需要对 SqlSugar数据库的处理进行一定的封装操作，以期最大程度的优化代码。

首先我们定义一个对象用来承载数据库SqlSugarScope(或者SqlSugarClient)实例的信息，用于数据访问的基类上下文方便使用的目的。

    /// <summary>
    /// 数据库上下文信息
    /// </summary>
    public class DbContext
    {
        /// <summary>
        /// 数据库类型。
        /// </summary>
        public DbType DbType { get; set; }
        /// <summary>
        /// 连接字符串。
        /// </summary>
        public string ConnectionString { get; set; }
        /// <summary>
        /// 数据库类型。
        /// </summary>
        public SqlSugarScope Client { get; set; }

        public DbContext()
        {
            //默认采用配置项名
            //appSettings/DefaultDb 配置项为指定连接字符串的name
            var dbConfigName = ConfigurationManager.AppSettings["DefaultDb"];
            Init(dbConfigName);
        }

        public DbContext(string dbConfigName)
        {
            Init(dbConfigName);
        }

我们为了方便配置不同的数据库信息，因此通过定义一个默认的键 DefaultDb 来确定具体使用那个连接字符串。如下是我们的数据库连接字符串。

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <!--Sqlserver数据库的连接字符串-->
    <add name="sqlserver" providerName="System.Data.SqlClient" connectionString="Persist Security Info=False;Data Source=(local);Initial Catalog=WinFramework;Integrated Security=SSPI" />
    <!--MySQL数据库的连接字符串-->
    <add name="mysql" providerName="MySql.Data.MySqlClient" connectionString="Server=localhost;Database=winframework;Uid=root;Pwd=123456;SslMode=none" />
    <!--sqlite数据库字符串，路径符号|DataDirectory|代表当前运行目录-->
    <add name="sqlite" providerName="System.Data.SQLite" connectionString="Data Source=|DataDirectory|\WinFramework.db;Version=3;" />
    <!--PostgreSQL数据库的连接字符串-->
    <add name="npgsql" providerName="Npgsql" connectionString="Server=localhost;Port=5432;Database=winframework;User Id=postgres;Password=123456" />
    <!--不受驱动影响，32位64位均可使用-->
    <add name="oracle" providerName="OracleManaged" connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)));User ID=win;Password=win" />
    <!--达梦数据库的连接字符串-->
    <add name="Dm" providerName="Dm" connectionString="Server=localhost;User ID=SYSDBA;PWD=SYSDBA;Database=WINFRAMEWORK;" />
  </connectionStrings>
  
  <appSettings>
    <!--指定默认的数据库类型，如果不指定则使用第一个连接字符串-->
    <add key="DefaultDb" value="sqlserver" />
  </appSettings>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />
  </startup>
</configuration>

其中我们通过连接字符串中的 providerName 的类别来确定具体使用那种数据库类型。

       /// <summary>
        /// 根据链接字符串的providerName决定那种数据库类型
        /// </summary>
        /// <param name="setting"></param>
        /// <returns></returns>
        private DbType GetSugarDbType(ConnectionStringSettings setting)
        {
            DbType dbType = DbType.SqlServer; //默认值
            var providerName = setting.ProviderName;
            if (providerName != null)
            {
                //数据库providerName：SqlClient MySqlClient SQLite OracleManaged/OracleClient Npgsql
                if (providerName.EndsWith(".SqlClient", StringComparison.OrdinalIgnoreCase))
                {
                    dbType = DbType.SqlServer;
                }
                else if (providerName.EndsWith(".MySqlClient", StringComparison.OrdinalIgnoreCase))
                {
                    dbType = DbType.MySql;
                }
                else if (providerName.EndsWith(".SQLite", StringComparison.OrdinalIgnoreCase))
                {
                    dbType = DbType.Sqlite;
                }
                else if (providerName.EndsWith("OracleManaged", StringComparison.OrdinalIgnoreCase))
                {
                    dbType = DbType.Oracle;
                }
                else if (providerName.EndsWith(".OracleClient", StringComparison.OrdinalIgnoreCase))
                {
                    dbType = DbType.Oracle;
                }
                else if (providerName.EndsWith("Npgsql", StringComparison.OrdinalIgnoreCase))
                {
                    dbType = DbType.PostgreSQL;
                }
                else if (providerName.EndsWith("Dm", StringComparison.OrdinalIgnoreCase))
                {
                    dbType = DbType.Dm;
                }
            }
            return dbType;
        }

这样我们就可以动态设置数据库的配置信息了，我们可以使用配置信息，初始化数据库操作实例的代码逻辑。

数据库上下文对象处理好后，我们就来设计我们的数据库操作基类对象了，基类对象需要基于实体信息来定义一些常规的CRUD接口，并应最大程度的提供一些重写或者设置处理。

    /// <summary>
    /// 基于SqlSugar的数据库访问操作的基类对象
    /// </summary>
    /// <typeparam name="TEntity">定义映射的实体类</typeparam>
    /// <typeparam name="TKey">主键的类型，如int，string等</typeparam>
    /// <typeparam name="TGetListInput">或者分页信息的条件对象</typeparam>
    public class MyCrudService<TEntity, TKey, TGetListInput>
        where TEntity : class, new()
        where TGetListInput : IPagedAndSortedResultRequest
    {
        /// <summary>
        /// 数据库上下文信息
        /// </summary>
        protected DbContext dbContent;

        /// <summary>
        /// 简化SugarClient 的 ADO对象
        /// </summary>
        protected IAdo Ado
        {
            get
            {
                return dbContent.Client.Ado;
            }
        }
        
        /// <summary>
        /// 实体对象处理类
        /// </summary>
        protected SimpleClient<TEntity> EntityDb
        {
            get
            {
                return dbContent.Client.GetSimpleClient<TEntity>();
            }
        }
        
        /// <summary>
        /// 数据库配置名称，默认为空。
        /// 可在子类指定不同的配置名称，用于访问不同的数据库
        /// </summary>
        public string DbConfigName { get; set; }

        public MyCrudService()
        {
            dbContent = new DbContext();
        }

我们看到基类提供一些SqlSugarClient对象的应用，以方便对数据的处理操作。

我们看看获取所有，以及根据Lamda条件表达式获取列表的操作代码，非常方便的。

        /// <summary>
        /// 获取所有记录
        /// </summary>
        public virtual async Task<ListResultDto<TEntity>> GetAllAsync()
        {
            var list = await EntityDb.GetListAsync();
            return new ListResultDto<TEntity>()
            {
                Items = list
            };
        }
        /// <summary>
        /// 根据条件，获取所有记录
        /// </summary>
        public virtual async Task<ListResultDto<TEntity>> GetAllAsync(Expression<Func<TEntity, bool>> input, string orderBy = null)
        {
            var query = EntityDb.AsQueryable().Where(input);
            query = query.OrderByIF(!string.IsNullOrEmpty(orderBy), orderBy);

            var list = await query.ToListAsync();
            return new ListResultDto<TEntity>()
            {
                Items = list
            };
        }

由于本身的SqlSugarClient/SqlSugarScope提供了很多接口函数，因此我们的基类只需要在它的基础上进行一些简单的封装即可，如删除处理代码。

        /// <summary>
        /// 删除指定ID的对象
        /// </summary>
        /// <param name="id">记录ID</param>
        /// <returns></returns>
        public virtual async Task<bool> DeleteAsync(TEntity input)
        {
            return await EntityDb.DeleteAsync(input);
        }

        /// <summary>
        /// 根据指定条件，删除集合
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public virtual async Task<bool> DeleteAsync(Expression<Func<TEntity, bool>> input)
        {
            var result = await EntityDb.DeleteAsync(input);
            return result;
        }

        /// <summary>
        /// 删除指定ID的对象
        /// </summary>
        /// <param name="id">记录ID</param>
        /// <returns></returns>
        public virtual async Task<bool> DeleteByIdAsync(TKey id)
        {
            return await EntityDb.DeleteByIdAsync(id);
        }
        /// <summary>
        /// 删除集合
        /// </summary>
        /// <param name="input">删除条件集合</param>
        /// <returns></returns>
        public async virtual Task<bool> DeleteByIdsAsync(IEnumerable<TKey> input)
        {
            dynamic ids = input.ToArray();
            return await  EntityDb.DeleteByIdsAsync(ids);
        }

上面删除，可以根据实体类，Lamda条件表达式，主键或者主键列表等，简单封装一下就可以了。

根据相关的数据操作需要，我们为该基类定义很多常规通用的基类接口，包含很多常规的CRUD等的方法，列出一个列表方便参考即可。

3、SQLSugar数据访问的单元测试

为了对不同数据库类型的不同操作进行检查，看其是否能够正常工作，我们需要编写一些测试的代码用于检查我们基类函数封装的有效性，只有对每一个基类接口进行测试了，才能够放心的使用。

为了编写单元测试，我们需要为几个表编写对应的实体类和相应的服务类（继承自SQLSugar的数据访问基类），我们可以使用代码生成工具Database2Sharp来快速生成实体类代码，如下所示。

生成代码直接显示在代码工具上，可以复制下来使用。

后面有空会调整一下代码生成工具Database2Sharp，把SQLSugar的ORM实体类和基于CRUD基类的服务类一并生成代码出来就完美了（和其他项目开发一样，快速生成项目代码即可）。

完成了实体类信息的处理后，我们来继承一下基类服务类并重写查询条件处理和列表排序的函数即可，如下代码所示。

   /// <summary>
    /// 应用层服务接口实现
    /// </summary>
    public class DictDataService : MyCrudService<DictDataInfo, string, DictDataPagedDto>
    {
        /// <summary>
        /// 自定义条件处理
        /// </summary>
        /// <param name="input">查询条件Dto</param>
        /// <returns></returns>
        protected override ISugarQueryable<DictDataInfo> CreateFilteredQueryAsync(DictDataPagedDto input)
        {
            var query = base.CreateFilteredQueryAsync(input);

            query = query
                .WhereIF(!input.Name.IsNullOrWhiteSpace(), t => t.Name.Contains(input.Name))
                .WhereIF(!string.IsNullOrEmpty(input.Remark), t => t.Remark.Contains(input.Remark))
                .WhereIF(!string.IsNullOrEmpty(input.Value), t => t.Value == input.Value)
                .WhereIF(!string.IsNullOrEmpty(input.DictType_ID), t => t.DictType_ID == input.DictType_ID);

            return query;
        }

        /// <summary>
        /// 自定义排序处理
        /// </summary>
        /// <param name="query">可查询LINQ</param>
        /// <param name="input">查询条件Dto</param>
        /// <returns></returns>
        protected override ISugarQueryable<DictDataInfo> ApplySorting(ISugarQueryable<DictDataInfo> query, DictDataPagedDto input)
        {
            return base.ApplySorting(query, input).OrderBy(s => s.DictType_ID).OrderBy(s => s.Seq);

            //先按第一个字段排序，然后再按第二字段排序
            //return base.ApplySorting(query, input).OrderBy(s=>s.DictData_ID).OrderBy(s => s.Seq);
        }
    }

其中 CreateFilteredQueryAsync 代码是重写构建查询条件处理的逻辑，而ApplySorting函数用于指定列表的排序规则。

有了代码生成工具的辅助，因此我们编写一些单元测试函数用于测试，编写单元测试也是非常方便的事情。

代码的单元测试，编写如下所示。

    [TestClass]
    public class UnitTest1
    {
        /// <summary>
        /// 测试查找记录
        /// </summary>
        /// <returns></returns>
        [TestMethod]
        public async Task TestMethod1()

创建单元测试项目，并指定测试类为[Testclass]以及测试方法[TestMethod]即可，测试方法我们根据实际要求编写覆盖所有方法的测试即可。

例如我对于测试返回列表和单体数据的接口，编写单元代码如下所示。

    [TestClass]
    public class UnitTest1
    {
        /// <summary>
        /// 测试查找记录
        /// </summary>
        /// <returns></returns>
        [TestMethod]
        public async Task TestMethod1()
        {
            var input = new DictTypePagedDto()
            {
                Name = "客户"
            };

            //可以使用BLLFactory工厂类处理
            var service = BLLFactory<DictTypeService>.Instance;//new DictTypeService();
            var count = await service.CountAsync(s=> true);
            Assert.AreNotEqual(0, count);

            var list = await service.GetAllAsync();
            Assert.IsNotNull(list);
            Assert.IsNotNull(list.Items);
            Assert.IsTrue(list.Items.Count > 0);

            list = await service.GetListAsync(input);
            Assert.IsNotNull(list);
            Assert.IsNotNull(list.Items);
            Assert.IsTrue(list.Items.Count > 0);

            var ids = list.Items.Select(s => { return s.ID; }).Take(2);
            list = await service.GetAllByIdsAsync(ids);
            Assert.IsNotNull(list);
            Assert.IsNotNull(list.Items);
            Assert.IsTrue(list.Items.Count > 0);


            var id = list.Items[0].ID;
            var info = await service.GetAsync(id);
            Assert.IsNotNull(info);
            Assert.AreEqual(id, info.ID);
           
            info = await service.GetFirstAsync(s => true);
            Assert.IsNotNull(info);

            await Task.CompletedTask;
        }

测试增删改查的接口的单元测试代码如下所示。

        /// <summary>
        /// 测试增删改查
        /// </summary>
        /// <returns></returns>
        [TestMethod]
        public async Task TestMethod2()
        {
            var info = new DictTypeInfo()
            {
                ID = Guid.NewGuid().ToString(),
                Code = "test",
                Name = "test",
                Remark = "test",
                PID = "-1", 
                Seq = "001"
            };

            var service = new DictTypeService();
            var insert = await service.InsertAsync(info);
            Assert.IsTrue(insert);

            info.Name = "test2";
            var update = await service.UpdateAsync(info);
            Assert.IsTrue(update);

            var deleted = await service.DeleteByIdAsync(info.ID);
            Assert.IsTrue(deleted);

            var entity = await service.GetAsync(info.ID);
            Assert.IsNull(entity);
        }

测试对SQL语句执行过程的单元测试代码如下

       /// <summary>
        /// 测试执行语句的处理
        /// </summary>
        /// <returns></returns>
        [TestMethod]
        public async Task TestMethod3()
        {
            var service = new DictTypeService();
            var sql = string.Format("Select * from TB_DictType");

            var table = service.SqlTable(sql);
            Assert.IsNotNull(table);
            Assert.IsTrue(table.Rows.Count > 0);

            var ds = service.SqlDataSet(sql);
            Assert.IsNotNull(ds);
            Assert.IsTrue(ds.Tables.Count > 0);

            sql = string.Format("Select Name from TB_DictType");
            var list = service.SqlValueList(sql);
            Assert.IsNotNull(list);

            //完全没有执行任何更新、插入，返回-1
            var result = service.SqlExecute(sql);
            Assert.IsTrue(result == -1);

            await Task.CompletedTask;
        }

测试数据库参数化及多数据库切换处理的单元测试代码如下所示。

       /// <summary>
        /// 测试数据库参数化及多数据处理
        /// </summary>
        /// <returns></returns>
        [TestMethod]
        public async Task TestMethod4()
        {
            var service = new DictTypeService();
            var sql = string.Format("Select * from TB_DictType Where PID = @pid");
            var parameters = new List<SugarParameter>() { new SugarParameter("pid", "-1") };

            //默认SQLServer数据库
            var table = service.SqlTable(sql, parameters);
            Console.WriteLine(table.Rows.Count);
            Assert.IsNotNull(table);
            Assert.IsTrue(table.Rows.Count > 0);

            //切换到MySQL数据库
            service.SetDbConfigName("mysql");
            var list = service.SqlQuery(sql, parameters);
            Assert.IsNotNull(list);
            Assert.IsNotNull(list.Items);
            Assert.IsTrue(list.Items.Count > 0);

            //切换到SQLITE数据库
            service.SetDbConfigName("sqlite");
            var list2 = service.SqlQuery(sql, parameters);
            Assert.IsNotNull(list2);
            Assert.IsNotNull(list2.Items);
            Assert.IsTrue(list2.Items.Count > 0);

            //切换到npgsql数据库
            service.SetDbConfigName("npgsql");
            var list3 = service.SqlQuery(sql, parameters);
            Assert.IsNotNull(list3);
            Assert.IsNotNull(list3.Items);
            Assert.IsTrue(list3.Items.Count > 0);

            await Task.CompletedTask;
        }

在开发机器上安装几个不同的关系数据库，用于测试，并准备好数据库文件导入。

在单元测试项目中右键运行测试，如下图所示。

全部测试通过，这几个单元测试覆盖了我们的所有方法的测试了。

以上就是我们基于SqlSugar的ORM处理的封装，并提供了丰富的基类接口和弹性化的实体类泛型约束，因此子类代码非常简洁，只需要实现条件查询和排序的处理即可，因此代码生成的时候，会更加简便。

通过上面的预演，我们基本上了解了SqlSugar的使用操作，确实非常方便，特别是我们基于代码生成工具的辅助开发之后，会更加省事快捷，使用基于强大的CRUD基类，我们子类的代码更少，更容易维护。

而对于一些多表关联的操作，我们可以在子类额外定义一些处理函数即可。

Tags: SqlSugar