基于SqlSugar數據庫訪問技術研究與實踐應用

陳景光，于文學，張 鵬，胡選仲

(中機試驗裝備股份有限公司，吉林 長春 130103)

1 引 言

目前，眾多傳統行業面臨著“互聯網+”的轉型，應用互聯網技術對傳統行業進行產品升級，進而提高市場競爭力。數據庫是互聯網技術的基礎，通過對其存儲的海量數據信息進行篩選、分析與決策，對傳統行業產品進行賦能，實現智能化、明星化。數據庫系統是轉型過程的關鍵一環，熟練的數據庫訪問技術成為軟件開發人員必備的一項專業技能。

ADO.NET是由Microsoft公司開發的數據訪問接口，建議開發者在.NET編程環境中優先使用該數據訪問接口[1]。ADO.NET數據訪問接口的缺點是需要開發者足夠了解SQL語句，通過執行SQL語句對數據庫進行訪問操作。為了避免由于繁瑣的SQL語句帶來的項目開發效率低下、不同類型數據庫的應用程序可拓展性以及數據庫訪問層與業務邏輯層耦合性高等諸多問題，開發者可使用對象關系映射(ORM)框架操作數據庫，將SQL語句抽象化處理，通過創建實例對象、調用接口函數等方法輕松實現數據庫訪問功能。

目前，主流的開源ORM框架有Entity Framework(EF)、Dapper、SqlSugar等。EF是重量級ORM，支持所有流行的數據庫，面向接口進行開發設計，可擴充性強，對事務、緩存有良好的封裝及配置[2]?？墒?，EF自動生成的SQL查詢執行效率低，對于大數據量、高負載的應用場景需慎重考慮。同時，由于EF屬于重量級ORM，其功能全面，這導致學習成本較高。Dapper是輕量級ORM，支持MySql、Sqlite、Oracle、SqlServer等一系列的數據庫，其執行效率高，應用簡單，但Dapper屬于半自動ORM，實體類需要自行構造。綜合上述情況，本文介紹的SqlSugar技術可彌補EF、Dapper的不足，同時兼顧簡單易用、靈活拓展的技術特點。

2 SqlSugar技術介紹

2.1 功能概述

SqlSugar基于.NET框架進行設計、開發，支持MySql、SqlServer、Sqlite、Oracle、Access等主流數據庫，同時支持其他數據庫進行自定義擴展，擁有DbFirst、CodeFirst工具類，具有鏈式查詢、更新、刪除、插入、自動分表、復雜模型查詢、數據庫讀寫分離等實用功能。SqlSugar技術通過Emit動態中間語言動態編譯到程序集，完成實體綁定，性能可達到原生水平。

2.2 總體框架

SqlSugar總體框架如圖1所示，按層級關系可劃分為應用層、服務層和基礎層。應用層為實體類SqlSugarClient；服務層有插入、刪除、更新、查詢功能(Insertable、Deleteable、Updateable、Queryable)以及CodeFirst、DbFirst功能；基礎層包括EntityMaintenance、DbMaintenance、SqlBuild、AdoProvider，將服務層程序自動轉換成SQL語句，通過IDbCommand、IDataReader訪問接口與數據源進行信息交互，完成數據庫訪問操作。

圖1 SqlSugar總體框架

2.3 SqlSugar抽象類簡介

Queryable：數據查詢類，具備基礎查詢、多表查詢、分組查詢、分頁查詢功能，同時支持SqlFunc函數和Lambda解析。

Insertable：數據插入類，具備單條插入、批量插入、指定插入列、排除插入列等功能。

Updateable：數據更新類，具備單條更新、批量更新、指定更新列、排除更新列、根據實體更新、根據Lambda更新等功能。

Deleteable：數據刪除類，具備根據主鍵刪除、單條刪除、條件刪除、表達式刪除等功能。

CodeFirst：代碼先行類，支持通過類生成數據庫表字段、表主鍵、表自增列等數據庫相關信息，支持表的備份和字段名稱的修改。

DbFirst：數據先行類，根據數據庫相關信息創建實體類，支持模型自定義，可以生成全部表的實體，也可以指定表的實體。

AdoProvider：支持SqlQuery一系列原生SQL操作，支持事務、存儲過程輸出參數等功能。

EntityMaintenance：實體對象維護類，獲取實體類的相關信息。

DbMaintenance：數據庫維護類，支持表備份、庫備份、添加表、添加列、獲取表信息、根據表獲取主鍵等相關數據庫層面操作。

3 SqlSugar技術應用實例

3.1 創建開發環境

打開VS開發環境，新建一個以.NET為目標框架的項目。通過NuGet包管理器搜索SqlSugar，根據項目目標框架的.NET版本，選擇與之對應的SqlSugar版本進行下載安裝。目前SqlSugar已更新至.NET Framework 5.0.3版本，針對.NET跨平臺開發項目，可選擇SqlSugarCore進行下載安裝。由于SqlSugar內部引用Newtonsoft.Json庫，因此也需要通過NuGet包管理器下載安裝Newtonsoft.Json包。

3.2 創建數據庫連接對象

將SqlSugarClient類進行實例化，注入ConnectionConfig參數進行SqlSugar實體配置，完成數據庫連接對象創建步驟。本文以訪問SQLServer數據庫為例，創建數據庫連接對象代碼如下：

SqlSugarClient db = new SqlSugarClient

(new ConnectionConfig()

{

//連接字符串

ConnectionString="server=PC20211028\WINC;uid=sa;pwd=123456;database=SqlServerSugar",

//數據庫類型

DbType = DbType.SqlServer,

//是否自動釋放數據庫

IsAutoCloseConnection = true,

//初始化主鍵和自增列信息的方式

InitKeyType = InitKeyType.Attribute

});

3.3 CodeFirst創建表

首先定義一個實體類Student，使用數據庫連接對象調用CodeFirst類中InitTables方法便可完成表的創建，代碼如下：

[SugarTable("StudentTable")]//表名

public class Student

{

[SugarColumn(IsPrimaryKey = true,

IsIdentity = true, ColumnName = "ID",

ColumnDescription = "主鍵")]

public int Id { get; set; }

public int? SchoolId { get; set; }

[SugarColumn(Length = 50, IsNullable = true)]

public string Name { get; set; }

public DateTime? CreateTime { get; set; }

}

db.CodeFirst.InitTables(typeof(Student));

3.4 DbFirst生成實體類文件

根據實體配置所連接的數據庫，自動生成與其表內字段一一對應的實例類文件，代碼如下：

var Path= "D:\SqlSugarDemo\Model";

var NameSpace = "SqlSugarModel";

//庫內所有表都生成實體類文件

db.DbFirst.CreateClassFile(Path,NameSpace );

//生成實體帶屬性

db.DbFirst.IsCreateAttribute().CreateClassFile(Path, NameSpace);

//根據表名生成實體類

db.DbFirst.Where("StudentTable").CreateClassFile(Path, NameSpace);

注意：使用DbFirst時，數據庫訪問賬戶需要擁有系統表權限，否則無法讀取表結構、根據表結構生成實體類文件。

3.5 數據庫基本操作(增刪改查)

根據實際開發過程中的業務需要與應用場景，本文重點列出常用的增刪改查方式并給出簡易代碼示例。

3.5.1 插入數據

(1)插入單條數據

db.Insertable(StudentObj).ExecuteCommand();

(2)批量插入數據

db.Insertable(List).ExecuteCommand()；

(3)根據字典插入數據

var dc = new Dictionary();

dc.Add("name", "Tom");

dc.Add("CreateTime", DateTime.Now);

dc.Add("SchoolId", 1);

db.Insertable(dc).AS("Student").ExecuteCommand();

Insertable類包含多個插入方法，不同插入方法返回值不同，如表1所示。

表1 插入方法與返回值

3.5.2 刪除數據

(1)根據實體進行刪除

db.Deleteable().Where(new Student() { Id = 3 }).ExecuteCommand();

(2)根據主鍵進行刪除

db.Deleteable().In(new int[] { 4, 6 }).

ExecuteCommand();

(3)根據表達式進行刪除

db.Deleteable().Where(it => it.Name == "marry").ExecuteCommand();

3.5.3 更新數據

(1)根據主鍵進行單條更新

db.Updateable(updateObj).ExecuteCommand();

(2)只更新某列

db.Updateable(updateObj).UpdateColumns(it =>

new { it.Name, it.CreateTime }).ExecuteCommand();

(3)忽略某列進行更新

db.Updateable(updateObj).IgnoreColumns(it =>

new { it.CreateTime, it.TestId }).ExecuteCommand();

3.5.4 查詢數據

(1)全部查詢

db.Queryable().ToList();

(2)條件查詢

db.Queryable().Where(it => it.Id > 10 && it.Name == "Tom").ToList();

(3)動態OR查詢

var exp = Expressionable.Create();

exp.OrIF(true, it => it.Id > 14);//.OrIf 是條件成立才會拼接OR

exp.Or(it => it.Name.Contains("sss"));//拼接OR

db.Queryable().Where(exp.ToExpression()).ToList();

(4)數據行數查詢

db.Queryable().Where(it => it.Id > 11).

Count();

Queryable類不僅具備上述常用基礎查詢功能，同時具備分頁查詢、分組查詢、聯表查詢、嵌套查詢等復雜查詢功能。

3.6 數據庫讀寫分離實現

通常來講，數據庫增刪改查操作滿足“二八原則”，即增刪改操作占數據庫操作的20%，80%來源于數據查詢操作。為了提高數據查詢性能，可對數據庫進行分庫操作，實現負載均衡[4]。SqlSugar通過配置屬性，可輕松實現數據庫讀寫分離功能(如圖2所示)。

圖2 讀寫分離示意圖

以SQLServer數據庫為例，通過SSMS(SQL Server Management Studio)配置分發服務器，并創建訂閱，完成主從數據庫的環境搭建。SqlSugar在創建數據庫連接對象時配置ConnectionConfig類的SlaveConnectionConfigs屬性，即可完成數據庫的讀寫分離功能。關鍵代碼如下：

SlaveConnectionConfigs = new List()

{

//HitRate為從庫權重屬性

new SlaveConnectionConfig() { HitRate=10,

ConnectionString=Config.ConnectionString1 } ,

new SlaveConnectionConfig() { HitRate=30,

ConnectionString=Config.ConnectionString2 }

}

4 結 語

SqlSugar作為ORM框架，提供了一種簡潔高效的數據庫訪問技術，應用SqlSugar可極大提高開發者工作效率。SqlSugar高度封裝接口，支持鏈式調用，使得程序業務邏輯更清晰，可讀性更高。SqlSugar各部分模塊化程度高，可拓展性更強。SqlSugar技術優點眾多，可廣泛應用到各行業軟件開發中。