基于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技术优点众多，可广泛应用到各行业软件开发中。