基於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