基于SqlSugar數(shù)據(jù)庫(kù)訪問(wèn)技術(shù)研究與實(shí)踐應(yīng)用

陳景光，于文學(xué)，張 鵬，胡選仲

(中機(jī)試驗(yàn)裝備股份有限公司，吉林 長(zhǎng)春 130103)

1 引 言

目前，眾多傳統(tǒng)行業(yè)面臨著“互聯(lián)網(wǎng)+”的轉(zhuǎn)型，應(yīng)用互聯(lián)網(wǎng)技術(shù)對(duì)傳統(tǒng)行業(yè)進(jìn)行產(chǎn)品升級(jí)，進(jìn)而提高市場(chǎng)競(jìng)爭(zhēng)力。數(shù)據(jù)庫(kù)是互聯(lián)網(wǎng)技術(shù)的基礎(chǔ)，通過(guò)對(duì)其存儲(chǔ)的海量數(shù)據(jù)信息進(jìn)行篩選、分析與決策，對(duì)傳統(tǒng)行業(yè)產(chǎn)品進(jìn)行賦能，實(shí)現(xiàn)智能化、明星化。數(shù)據(jù)庫(kù)系統(tǒng)是轉(zhuǎn)型過(guò)程的關(guān)鍵一環(huán)，熟練的數(shù)據(jù)庫(kù)訪問(wèn)技術(shù)成為軟件開(kāi)發(fā)人員必備的一項(xiàng)專(zhuān)業(yè)技能。

ADO.NET是由Microsoft公司開(kāi)發(fā)的數(shù)據(jù)訪問(wèn)接口，建議開(kāi)發(fā)者在.NET編程環(huán)境中優(yōu)先使用該數(shù)據(jù)訪問(wèn)接口[1]。ADO.NET數(shù)據(jù)訪問(wèn)接口的缺點(diǎn)是需要開(kāi)發(fā)者足夠了解SQL語(yǔ)句，通過(guò)執(zhí)行SQL語(yǔ)句對(duì)數(shù)據(jù)庫(kù)進(jìn)行訪問(wèn)操作。為了避免由于繁瑣的SQL語(yǔ)句帶來(lái)的項(xiàng)目開(kāi)發(fā)效率低下、不同類(lèi)型數(shù)據(jù)庫(kù)的應(yīng)用程序可拓展性以及數(shù)據(jù)庫(kù)訪問(wèn)層與業(yè)務(wù)邏輯層耦合性高等諸多問(wèn)題，開(kāi)發(fā)者可使用對(duì)象關(guān)系映射(ORM)框架操作數(shù)據(jù)庫(kù)，將SQL語(yǔ)句抽象化處理，通過(guò)創(chuàng)建實(shí)例對(duì)象、調(diào)用接口函數(shù)等方法輕松實(shí)現(xiàn)數(shù)據(jù)庫(kù)訪問(wèn)功能。

目前，主流的開(kāi)源ORM框架有Entity Framework(EF)、Dapper、SqlSugar等。EF是重量級(jí)ORM，支持所有流行的數(shù)據(jù)庫(kù)，面向接口進(jìn)行開(kāi)發(fā)設(shè)計(jì)，可擴(kuò)充性強(qiáng)，對(duì)事務(wù)、緩存有良好的封裝及配置[2]。可是，EF自動(dòng)生成的SQL查詢執(zhí)行效率低，對(duì)于大數(shù)據(jù)量、高負(fù)載的應(yīng)用場(chǎng)景需慎重考慮。同時(shí)，由于EF屬于重量級(jí)ORM，其功能全面，這導(dǎo)致學(xué)習(xí)成本較高。Dapper是輕量級(jí)ORM，支持MySql、Sqlite、Oracle、SqlServer等一系列的數(shù)據(jù)庫(kù)，其執(zhí)行效率高，應(yīng)用簡(jiǎn)單，但Dapper屬于半自動(dòng)ORM，實(shí)體類(lèi)需要自行構(gòu)造。綜合上述情況，本文介紹的SqlSugar技術(shù)可彌補(bǔ)EF、Dapper的不足，同時(shí)兼顧簡(jiǎn)單易用、靈活拓展的技術(shù)特點(diǎn)。

2 SqlSugar技術(shù)介紹

2.1 功能概述

SqlSugar基于.NET框架進(jìn)行設(shè)計(jì)、開(kāi)發(fā)，支持MySql、SqlServer、Sqlite、Oracle、Access等主流數(shù)據(jù)庫(kù)，同時(shí)支持其他數(shù)據(jù)庫(kù)進(jìn)行自定義擴(kuò)展，擁有DbFirst、CodeFirst工具類(lèi)，具有鏈?zhǔn)讲樵?、更新、刪除、插入、自動(dòng)分表、復(fù)雜模型查詢、數(shù)據(jù)庫(kù)讀寫(xiě)分離等實(shí)用功能。SqlSugar技術(shù)通過(guò)Emit動(dòng)態(tài)中間語(yǔ)言動(dòng)態(tài)編譯到程序集，完成實(shí)體綁定，性能可達(dá)到原生水平。

2.2 總體框架

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

圖1 SqlSugar總體框架

2.3 SqlSugar抽象類(lèi)簡(jiǎn)介

Queryable：數(shù)據(jù)查詢類(lèi)，具備基礎(chǔ)查詢、多表查詢、分組查詢、分頁(yè)查詢功能，同時(shí)支持SqlFunc函數(shù)和Lambda解析。

Insertable：數(shù)據(jù)插入類(lèi)，具備單條插入、批量插入、指定插入列、排除插入列等功能。

Updateable：數(shù)據(jù)更新類(lèi)，具備單條更新、批量更新、指定更新列、排除更新列、根據(jù)實(shí)體更新、根據(jù)Lambda更新等功能。

Deleteable：數(shù)據(jù)刪除類(lèi)，具備根據(jù)主鍵刪除、單條刪除、條件刪除、表達(dá)式刪除等功能。

CodeFirst：代碼先行類(lèi)，支持通過(guò)類(lèi)生成數(shù)據(jù)庫(kù)表字段、表主鍵、表自增列等數(shù)據(jù)庫(kù)相關(guān)信息，支持表的備份和字段名稱(chēng)的修改。

DbFirst：數(shù)據(jù)先行類(lèi)，根據(jù)數(shù)據(jù)庫(kù)相關(guān)信息創(chuàng)建實(shí)體類(lèi)，支持模型自定義，可以生成全部表的實(shí)體，也可以指定表的實(shí)體。

AdoProvider：支持SqlQuery一系列原生SQL操作，支持事務(wù)、存儲(chǔ)過(guò)程輸出參數(shù)等功能。

EntityMaintenance：實(shí)體對(duì)象維護(hù)類(lèi)，獲取實(shí)體類(lèi)的相關(guān)信息。

DbMaintenance：數(shù)據(jù)庫(kù)維護(hù)類(lèi)，支持表備份、庫(kù)備份、添加表、添加列、獲取表信息、根據(jù)表獲取主鍵等相關(guān)數(shù)據(jù)庫(kù)層面操作。

3 SqlSugar技術(shù)應(yīng)用實(shí)例

3.1 創(chuàng)建開(kāi)發(fā)環(huán)境

打開(kāi)VS開(kāi)發(fā)環(huán)境，新建一個(gè)以.NET為目標(biāo)框架的項(xiàng)目。通過(guò)NuGet包管理器搜索SqlSugar，根據(jù)項(xiàng)目目標(biāo)框架的.NET版本，選擇與之對(duì)應(yīng)的SqlSugar版本進(jìn)行下載安裝。目前SqlSugar已更新至.NET Framework 5.0.3版本，針對(duì).NET跨平臺(tái)開(kāi)發(fā)項(xiàng)目，可選擇SqlSugarCore進(jìn)行下載安裝。由于SqlSugar內(nèi)部引用Newtonsoft.Json庫(kù)，因此也需要通過(guò)NuGet包管理器下載安裝Newtonsoft.Json包。

3.2 創(chuàng)建數(shù)據(jù)庫(kù)連接對(duì)象

將SqlSugarClient類(lèi)進(jìn)行實(shí)例化，注入ConnectionConfig參數(shù)進(jìn)行SqlSugar實(shí)體配置，完成數(shù)據(jù)庫(kù)連接對(duì)象創(chuàng)建步驟。本文以訪問(wèn)SQLServer數(shù)據(jù)庫(kù)為例，創(chuàng)建數(shù)據(jù)庫(kù)連接對(duì)象代碼如下：

SqlSugarClient db = new SqlSugarClient

(new ConnectionConfig()

{

//連接字符串

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

//數(shù)據(jù)庫(kù)類(lèi)型

DbType = DbType.SqlServer,

//是否自動(dòng)釋放數(shù)據(jù)庫(kù)

IsAutoCloseConnection = true,

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

InitKeyType = InitKeyType.Attribute

});

3.3 CodeFirst創(chuàng)建表

首先定義一個(gè)實(shí)體類(lèi)Student，使用數(shù)據(jù)庫(kù)連接對(duì)象調(diào)用CodeFirst類(lèi)中InitTables方法便可完成表的創(chuàng)建，代碼如下：

[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生成實(shí)體類(lèi)文件

根據(jù)實(shí)體配置所連接的數(shù)據(jù)庫(kù)，自動(dòng)生成與其表內(nèi)字段一一對(duì)應(yīng)的實(shí)例類(lèi)文件，代碼如下：

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

var NameSpace = "SqlSugarModel";

//庫(kù)內(nèi)所有表都生成實(shí)體類(lèi)文件

db.DbFirst.CreateClassFile(Path,NameSpace );

//生成實(shí)體帶屬性

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

//根據(jù)表名生成實(shí)體類(lèi)

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

注意：使用DbFirst時(shí)，數(shù)據(jù)庫(kù)訪問(wèn)賬戶需要擁有系統(tǒng)表權(quán)限，否則無(wú)法讀取表結(jié)構(gòu)、根據(jù)表結(jié)構(gòu)生成實(shí)體類(lèi)文件。

3.5 數(shù)據(jù)庫(kù)基本操作(增刪改查)

根據(jù)實(shí)際開(kāi)發(fā)過(guò)程中的業(yè)務(wù)需要與應(yīng)用場(chǎng)景，本文重點(diǎn)列出常用的增刪改查方式并給出簡(jiǎn)易代碼示例。

3.5.1 插入數(shù)據(jù)

(1)插入單條數(shù)據(jù)

db.Insertable(StudentObj).ExecuteCommand();

(2)批量插入數(shù)據(jù)

db.Insertable(List).ExecuteCommand()；

(3)根據(jù)字典插入數(shù)據(jù)

var dc = new Dictionary();

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

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

dc.Add("SchoolId", 1);

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

Insertable類(lèi)包含多個(gè)插入方法，不同插入方法返回值不同，如表1所示。

表1 插入方法與返回值

3.5.2 刪除數(shù)據(jù)

(1)根據(jù)實(shí)體進(jìn)行刪除

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

(2)根據(jù)主鍵進(jìn)行刪除

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

ExecuteCommand();

(3)根據(jù)表達(dá)式進(jìn)行刪除

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

3.5.3 更新數(shù)據(jù)

(1)根據(jù)主鍵進(jìn)行單條更新

db.Updateable(updateObj).ExecuteCommand();

(2)只更新某列

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

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

(3)忽略某列進(jìn)行更新

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

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

3.5.4 查詢數(shù)據(jù)

(1)全部查詢

db.Queryable().ToList();

(2)條件查詢

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

(3)動(dòng)態(tài)OR查詢

var exp = Expressionable.Create();

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

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

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

(4)數(shù)據(jù)行數(shù)查詢

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

Count();

Queryable類(lèi)不僅具備上述常用基礎(chǔ)查詢功能，同時(shí)具備分頁(yè)查詢、分組查詢、聯(lián)表查詢、嵌套查詢等復(fù)雜查詢功能。

3.6 數(shù)據(jù)庫(kù)讀寫(xiě)分離實(shí)現(xiàn)

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

圖2 讀寫(xiě)分離示意圖

以SQLServer數(shù)據(jù)庫(kù)為例，通過(guò)SSMS(SQL Server Management Studio)配置分發(fā)服務(wù)器，并創(chuàng)建訂閱，完成主從數(shù)據(jù)庫(kù)的環(huán)境搭建。SqlSugar在創(chuàng)建數(shù)據(jù)庫(kù)連接對(duì)象時(shí)配置ConnectionConfig類(lèi)的SlaveConnectionConfigs屬性，即可完成數(shù)據(jù)庫(kù)的讀寫(xiě)分離功能。關(guān)鍵代碼如下：

SlaveConnectionConfigs = new List()

{

//HitRate為從庫(kù)權(quán)重屬性

new SlaveConnectionConfig() { HitRate=10,

ConnectionString=Config.ConnectionString1 } ,

new SlaveConnectionConfig() { HitRate=30,

ConnectionString=Config.ConnectionString2 }

}

4 結(jié) 語(yǔ)

SqlSugar作為ORM框架，提供了一種簡(jiǎn)潔高效的數(shù)據(jù)庫(kù)訪問(wèn)技術(shù)，應(yīng)用SqlSugar可極大提高開(kāi)發(fā)者工作效率。SqlSugar高度封裝接口，支持鏈?zhǔn)秸{(diào)用，使得程序業(yè)務(wù)邏輯更清晰，可讀性更高。SqlSugar各部分模塊化程度高，可拓展性更強(qiáng)。SqlSugar技術(shù)優(yōu)點(diǎn)眾多，可廣泛應(yīng)用到各行業(yè)軟件開(kāi)發(fā)中。