【.Net Core从零开始前后端分离】（五）——轻量级ORM--SqlSugar
前言
今天学习一个轻量级的ORM框架——SqlSugar
一、创建实体模型与数据库
1、实体模型
创建Advertisement的实体模型，其他的相关模型，大家自行下载代码即可：
public class Advertisement
{
/// <summary>
/// 主键
/// </summary>
public int Id { get; set; }
/// <summary>
/// 广告图片
/// </summary>
public string ImgUrl { get; set; }
/// <summary>
/// 广告标题
/// </summary>
public string Title { get; set; }
/// <summary>
/// 广告链接
/// </summary>
public string Url { get; set; }
/// <summary>
/// 备注
/// </summary>
public string Remark { get; set; }
/// <summary>
/// 创建时间
/// </summary>
public DateTime Createdate { get; set; } = DateTime.Now;
}
2、创建数据库
这里采用的是MySql数据库。sql语句在项目的Db文件夹。使用DBeaver来管理数据库。这个是一个免费的数据库管理工具。基本涵盖了所有的数据库类型。选择Community Edition 社区版本。
当然，前提，需要先自行安装MySql。建议安装5.7以上的版本。
打开DBeaver，在Mysql数据库类型，新建数据库Blog，选择字符类型为 utf8mb4，点击确定创建空的数据库：
在创建的Blog数据库，右键 工具-Restore database，选择sql文件，点击开始进行数据库恢复。
恢复完成之后
二、在 IRepository 层设计接口
仓储接口 IAdvertisementRepository.cs 添加CURD四个接口，首先需要将Model层添加引用。
namespace Blog.Core.IRepository
{
public interface IAdvertisementRepository
{
int Sum(int i, int j);
int Add(Advertisement model);
bool Delete(Advertisement model);
bool Update(Advertisement model);
List<Advertisement> Query(Expression<Func<Advertisement, bool>> whereExpression);
}
}
三、在 Repository 层实现相应接口
在继承的 IAdvertisementRepository点击右键，选择快速操作和重构，点击实现接口。就可以快速创建需要实现的接口。
namespace Blog.Core.Repository
{
public class AdvertisementRepository : IAdvertisementRepository
{
public int Add(Advertisement model)
{
throw new NotImplementedException();
}
public bool Delete(Advertisement model)
{
throw new NotImplementedException();
}
public List<Advertisement> Query(Expression<Func<Advertisement, bool>> whereExpression)
{
throw new NotImplementedException();
}
public int Sum(int i, int j)
{
return i + j;
}
public bool Update(Advertisement model)
{
throw new NotImplementedException();
}
}
}
四、引用轻量级的ORM框架——SqlSugar
首先什么是ORM， 对象关系映射（Object Relational Mapping，简称ORM）模式是一种为了解决面向对象与关系数据库存在的互不匹配的现象的技术。简单的说，ORM是通过使用描述对象和数据库之间映射的元数据，将程序中的对象自动持久化到关系数据库中。
开始，我们需要先向 Repository 层中引入SqlSugar，
直接在类库中通过Nuget引入 sqlSugarCore。
1、在Blog.Core.Repository新建一个sugar文件夹，然后添加两个配置文件，BaseDBConfig.cs 和 DbContext.cs.
namespace Blog.Core.Repository.sugar
{
public class BaseDBConfig
{
//mysql的连接语句
public static string ConnectionString = "server=localhost;uid=root;pwd=admin;port=3306;database=blog;sslmode=Preferred;charset=utf8";
}
}
//DbContext.cs，一个详细的上下文类
namespace Blog.Core.Repository.sugar
{
public class DbContext
{
private static string _connectionString;
private static DbType _dbType;
private SqlSugarClient _db;
/// <summary>
/// 连接字符串
/// Blog.Core
/// </summary>
public static string ConnectionString
{
get { return _connectionString; }
set { _connectionString = value; }
}
/// <summary>
/// 数据库类型
/// Blog.Core
/// </summary>
public static DbType DbType
{
get { return _dbType; }
set { _dbType = value; }
}
/// <summary>
/// 数据连接对象
/// Blog.Core
/// </summary>
public SqlSugarClient Db
{
get { return _db; }
private set { _db = value; }
}
/// <summary>
/// 数据库上下文实例（自动关闭连接）
/// Blog.Core
/// </summary>
public static DbContext Context
{
get
{
return new DbContext();
}
}
/// <summary>
/// 功能描述:构造函数
/// 作 者:Blog.Core
/// </summary>
private DbContext()
{
if (string.IsNullOrEmpty(_connectionString))
throw new ArgumentNullException("数据库连接字符串为空");
_db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = _connectionString,
DbType = _dbType,
IsAutoCloseConnection = true,
IsShardSameThread = true,
ConfigureExternalServices = new ConfigureExternalServices()
{
//DataInfoCacheService = new HttpRuntimeCache()
},
MoreSettings = new ConnMoreSettings()
{
//IsWithNoLockQuery = true,
IsAutoRemoveDataCache = true
}
});
}
/// <summary>
/// 功能描述:构造函数
/// 作 者:Blog.Core
/// </summary>
/// <param name="blnIsAutoCloseConnection">是否自动关闭连接</param>
private DbContext(bool blnIsAutoCloseConnection)
{
if (string.IsNullOrEmpty(_connectionString))
throw new ArgumentNullException("数据库连接字符串为空");
_db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = _connectionString,
DbType = _dbType,
IsAutoCloseConnection = blnIsAutoCloseConnection,
IsShardSameThread = true,
ConfigureExternalServices = new ConfigureExternalServices()
{
//DataInfoCacheService = new HttpRuntimeCache()
},
MoreSettings = new ConnMoreSettings()
{
//IsWithNoLockQuery = true,
IsAutoRemoveDataCache = true
}
});
}
#region 实例方法
/// <summary>
/// 功能描述:获取数据库处理对象
/// 作 者:Blog.Core
/// </summary>
/// <returns>返回值</returns>
public SimpleClient<T> GetEntityDB<T>() where T : class, new()
{
return new SimpleClient<T>(_db);
}
/// <summary>
/// 功能描述:获取数据库处理对象
/// 作 者:Blog.Core
/// </summary>
/// <param name="db">db</param>
/// <returns>返回值</returns>
public SimpleClient<T> GetEntityDB<T>(SqlSugarClient db) where T : class, new()
{
return new SimpleClient<T>(db);
}
#region 根据数据库表生产实体类
/// <summary>
/// 功能描述:根据数据库表生产实体类
/// 作 者:Blog.Core
/// </summary>
/// <param name="strPath">实体类存放路径</param>
public void CreateClassFileByDBTalbe(string strPath)
{
CreateClassFileByDBTalbe(strPath, "Km.PosZC");
}
/// <summary>
/// 功能描述:根据数据库表生产实体类
/// 作 者:Blog.Core
/// </summary>
/// <param name="strPath">实体类存放路径</param>
/// <param name="strNameSpace">命名空间</param>
public void CreateClassFileByDBTalbe(string strPath, string strNameSpace)
{
CreateClassFileByDBTalbe(strPath, strNameSpace, null);
}
/// <summary>
/// 功能描述:根据数据库表生产实体类
/// 作 者:Blog.Core
/// </summary>
/// <param name="strPath">实体类存放路径</param>
/// <param name="strNameSpace">命名空间</param>
/// <param name="lstTableNames">生产指定的表</param>
public void CreateClassFileByDBTalbe(
string strPath,
string strNameSpace,
string[] lstTableNames)
{
CreateClassFileByDBTalbe(strPath, strNameSpace, lstTableNames, string.Empty);
}
/// <summary>
/// 功能描述:根据数据库表生产实体类
/// 作 者:Blog.Core
/// </summary>
/// <param name="strPath">实体类存放路径</param>
/// <param name="strNameSpace">命名空间</param>
/// <param name="lstTableNames">生产指定的表</param>
/// <param name="strInterface">实现接口</param>
public void CreateClassFileByDBTalbe(
string strPath,
string strNameSpace,
string[] lstTableNames,
string strInterface,
bool blnSerializable = false)
{
if (lstTableNames != null && lstTableNames.Length > 0)
{
_db.DbFirst.Where(lstTableNames).IsCreateDefaultValue().IsCreateAttribute()
.SettingClassTemplate(p => p = @"
{using}
namespace {Namespace}
{
{ClassDescription}{SugarTable}" + (blnSerializable ? "[Serializable]" : "") + @"
public partial class {ClassName}" + (string.IsNullOrEmpty(strInterface) ? "" : (" : " + strInterface)) + @"
{
public {ClassName}()
{
{Constructor}
}
{PropertyName}
}
}
")
.SettingPropertyTemplate(p => p = @"
{SugarColumn}
public {PropertyType} {PropertyName}
{
get
{
return _{PropertyName};
}
set
{
if(_{PropertyName}!=value)
{
base.SetValueCall(" + "\"{PropertyName}\",_{PropertyName}" + @");
}
_{PropertyName}=value;
}
}")
.SettingPropertyDescriptionTemplate(p => p = " private {PropertyType} _{PropertyName};\r\n" + p)
.SettingConstructorTemplate(p => p = " this._{PropertyName} ={DefaultValue};")
.CreateClassFile(strPath, strNameSpace);
}
else
{
_db.DbFirst.IsCreateAttribute().IsCreateDefaultValue()
.SettingClassTemplate(p => p = @"
{using}
namespace {Namespace}
{
{ClassDescription}{SugarTable}" + (blnSerializable ? "[Serializable]" : "") + @"
public partial class {ClassName}" + (string.IsNullOrEmpty(strInterface) ? "" : (" : " + strInterface)) + @"
{
public {ClassName}()
{
{Constructor}
}
{PropertyName}
}
}
")
.SettingPropertyTemplate(p => p = @"
{SugarColumn}
public {PropertyType} {PropertyName}
{
get
{
return _{PropertyName};
}
set
{
if(_{PropertyName}!=value)
{
base.SetValueCall(" + "\"{PropertyName}\",_{PropertyName}" + @");
}
_{PropertyName}=value;
}
}")
.SettingPropertyDescriptionTemplate(p => p = " private {PropertyType} _{PropertyName};\r\n" + p)
.SettingConstructorTemplate(p => p = " this._{PropertyName} ={DefaultValue};")
.CreateClassFile(strPath, strNameSpace);
}
}
#endregion
#region 根据实体类生成数据库表
/// <summary>
/// 功能描述:根据实体类生成数据库表
/// 作 者:Blog.Core
/// </summary>
/// <param name="blnBackupTable">是否备份表</param>
/// <param name="lstEntitys">指定的实体</param>
public void CreateTableByEntity<T>(bool blnBackupTable, params T[] lstEntitys) where T : class, new()
{
Type[] lstTypes = null;
if (lstEntitys != null)
{
lstTypes = new Type[lstEntitys.Length];
for (int i = 0; i < lstEntitys.Length; i++)
{
T t = lstEntitys[i];
lstTypes[i] = typeof(T);
}
}
CreateTableByEntity(blnBackupTable, lstTypes);
}
/// <summary>
/// 功能描述:根据实体类生成数据库表
/// 作 者:Blog.Core
/// </summary>
/// <param name="blnBackupTable">是否备份表</param>
/// <param name="lstEntitys">指定的实体</param>
public void CreateTableByEntity(bool blnBackupTable, params Type[] lstEntitys)
{
if (blnBackupTable)
{
_db.CodeFirst.BackupTable().InitTables(lstEntitys); //change entity backupTable
}
else
{
_db.CodeFirst.InitTables(lstEntitys);
}
}
#endregion
#endregion
#region 静态方法
/// <summary>
/// 功能描述:获得一个DbContext
/// 作 者:Blog.Core
/// </summary>
/// <param name="blnIsAutoCloseConnection">是否自动关闭连接（如果为false，则使用接受时需要手动关闭Db）</param>
/// <returns>返回值</returns>
public static DbContext GetDbContext(bool blnIsAutoCloseConnection = true)
{
return new DbContext(blnIsAutoCloseConnection);
}
/// <summary>
/// 功能描述:设置初始化参数
/// 作 者:Blog.Core
/// </summary>
/// <param name="strConnectionString">连接字符串</param>
/// <param name="enmDbType">数据库类型</param>
public static void Init(string strConnectionString, DbType enmDbType = SqlSugar.DbType.SqlServer)
{
_connectionString = strConnectionString;
_dbType = enmDbType;
}
/// <summary>
/// 功能描述:创建一个链接配置
/// 作 者:Blog.Core
/// </summary>
/// <param name="blnIsAutoCloseConnection">是否自动关闭连接</param>
/// <param name="blnIsShardSameThread">是否夸类事务</param>
/// <returns>ConnectionConfig</returns>
public static ConnectionConfig GetConnectionConfig(bool blnIsAutoCloseConnection = true, bool blnIsShardSameThread = false)
{
ConnectionConfig config = new ConnectionConfig()
{
ConnectionString = _connectionString,
DbType = _dbType,
IsAutoCloseConnection = blnIsAutoCloseConnection,
ConfigureExternalServices = new ConfigureExternalServices()
{
//DataInfoCacheService = new HttpRuntimeCache()
},
IsShardSameThread = blnIsShardSameThread
};
return config;
}
/// <summary>
/// 功能描述:获取一个自定义的DB
/// 作 者:Blog.Core
/// </summary>
/// <param name="config">config</param>
/// <returns>返回值</returns>
public static SqlSugarClient GetCustomDB(ConnectionConfig config)
{
return new SqlSugarClient(config);
}
/// <summary>
/// 功能描述:获取一个自定义的数据库处理对象
/// 作 者:Blog.Core
/// </summary>
/// <param name="sugarClient">sugarClient</param>
/// <returns>返回值</returns>
public static SimpleClient<T> GetCustomEntityDB<T>(SqlSugarClient sugarClient) where T : class, new()
{
return new SimpleClient<T>(sugarClient);
}
/// <summary>
/// 功能描述:获取一个自定义的数据库处理对象
/// 作 者:Blog.Core
/// </summary>
/// <param name="config">config</param>
/// <returns>返回值</returns>
public static SimpleClient<T> GetCustomEntityDB<T>(ConnectionConfig config) where T : class, new()
{
SqlSugarClient sugarClient = GetCustomDB(config);
return GetCustomEntityDB<T>(sugarClient);
}
#endregion
}
}
2、修改数据连接字符串，这样以后就可以在AppSettings配置连接
a、在appsettings.json 中添加
"AppSettings": {
"MysqlConnection": "server=localhost;uid=root;pwd=admin;port=3306;database=blog;sslmode=Preferred;charset=utf8",
"Database": "BlogCoreDb"
},
b、在 startup.cs 中的 ConfigureServices() 方法中添加
//数据库配置
BaseDBConfig.ConnectionString = Configuration.GetSection("AppSettings:MysqlConnection").Value;
c、修改BaseDBConfig.cs
public static string ConnectionString { get; set; }
3、然后在刚刚我们实现那四个方法的AdvertisementRepository.cs中，重写构造函数，编辑统一Sqlsugar实例方法，用到了私有属性，为以后的单列模式做准备。最终的仓储持久化是：
namespace Blog.Core.Repository
{
public class AdvertisementRepository : IAdvertisementRepository
{
private DbContext context;
private SqlSugarClient db;
private SimpleClient<Advertisement> entityDB;
internal SqlSugarClient Db
{
get { return db; }
private set { db = value; }
}
public DbContext Context
{
get { return context; }
set { context = value; }
}
public AdvertisementRepository()
{
DbContext.Init(BaseDBConfig.ConnectionString);
DbContext.DbType = DbType.MySql;
context = DbContext.GetDbContext();
db = context.Db;
entityDB = context.GetEntityDB<Advertisement>(db);
}
public int Add(Advertisement model)
{
//返回的i是long类型,这里你可以根据你的业务需要进行处理
var i = db.Insertable(model).ExecuteReturnBigIdentity();
return i.ObjToInt();
}
public bool Delete(Advertisement model)
{
var i = db.Deleteable(model).ExecuteCommand();
return i > 0;
}
public List<Advertisement> Query(Expression<Func<Advertisement, bool>> whereExpression)
{
return entityDB.GetList(whereExpression);
}
public int Sum(int i, int j)
{
return i + j;
}
public bool Update(Advertisement model)
{
//这种方式会以主键为条件
var i = db.Updateable(model).ExecuteCommand();
return i > 0;
}
}
}
4、正式开始写持久化逻辑代码（注意：我在Model层中，添加了全局的数据类型转换方法，UtilConvert，这样就不用每次都Convert，而且也解决了为空转换异常的bug）
namespace Blog.Core.Model.Helper
{
public static class UtilConvert
{
/// <summary>
///
/// </summary>
/// <param name="thisValue"></param>
/// <returns></returns>
public static int ObjToInt(this object thisValue)
{
int reval = 0;
if (thisValue == null) return 0;
if (thisValue != null && thisValue != DBNull.Value && int.TryParse(thisValue.ToString(), out reval))
{
return reval;
}
return reval;
}
/// <summary>
///
/// </summary>
/// <param name="thisValue"></param>
/// <param name="errorValue"></param>
/// <returns></returns>
public static int ObjToInt(this object thisValue, int errorValue)
{
int reval = 0;
if (thisValue != null && thisValue != DBNull.Value && int.TryParse(thisValue.ToString(), out reval))
{
return reval;
}
return errorValue;
}
/// <summary>
///
/// </summary>
/// <param name="thisValue"></param>
/// <returns></returns>
public static double ObjToMoney(this object thisValue)
{
double reval = 0;
if (thisValue != null && thisValue != DBNull.Value && double.TryParse(thisValue.ToString(), out reval))
{
return reval;
}
return 0;
}
/// <summary>
///
/// </summary>
/// <param name="thisValue"></param>
/// <param name="errorValue"></param>
/// <returns></returns>
public static double ObjToMoney(this object thisValue, double errorValue)
{
double reval = 0;
if (thisValue != null && thisValue != DBNull.Value && double.TryParse(thisValue.ToString(), out reval))
{
return reval;
}
return errorValue;
}
/// <summary>
///
/// </summary>
/// <param name="thisValue"></param>
/// <returns></returns>
public static string ObjToString(this object thisValue)
{
if (thisValue != null) return thisValue.ToString().Trim();
return "";
}
/// <summary>
///
/// </summary>
/// <param name="thisValue"></param>
/// <param name="errorValue"></param>
/// <returns></returns>
public static string ObjToString(this object thisValue, string errorValue)
{
if (thisValue != null) return thisValue.ToString().Trim();
return errorValue;
}
/// <summary>
///
/// </summary>
/// <param name="thisValue"></param>
/// <returns></returns>
public static Decimal ObjToDecimal(this object thisValue)
{
Decimal reval = 0;
if (thisValue != null && thisValue != DBNull.Value && decimal.TryParse(thisValue.ToString(), out reval))
{
return reval;
}
return 0;
}
/// <summary>
///
/// </summary>
/// <param name="thisValue"></param>
/// <param name="errorValue"></param>
/// <returns></returns>
public static Decimal ObjToDecimal(this object thisValue, decimal errorValue)
{
Decimal reval = 0;
if (thisValue != null && thisValue != DBNull.Value && decimal.TryParse(thisValue.ToString(), out reval))
{
return reval;
}
return errorValue;
}
/// <summary>
///
/// </summary>
/// <param name="thisValue"></param>
/// <returns></returns>
public static DateTime ObjToDate(this object thisValue)
{
DateTime reval = DateTime.MinValue;
if (thisValue != null && thisValue != DBNull.Value && DateTime.TryParse(thisValue.ToString(), out reval))
{
reval = Convert.ToDateTime(thisValue);
}
return reval;
}
/// <summary>
///
/// </summary>
/// <param name="thisValue"></param>
/// <param name="errorValue"></param>
/// <returns></returns>
public static DateTime ObjToDate(this object thisValue, DateTime errorValue)
{
DateTime reval = DateTime.MinValue;
if (thisValue != null && thisValue != DBNull.Value && DateTime.TryParse(thisValue.ToString(), out reval))
{
return reval;
}
return errorValue;
}
/// <summary>
///
/// </summary>
/// <param name="thisValue"></param>
/// <returns></returns>
public static bool ObjToBool(this object thisValue)
{
bool reval = false;
if (thisValue != null && thisValue != DBNull.Value && bool.TryParse(thisValue.ToString(), out reval))
{
return reval;
}
return reval;
}
}
}
五、在 IServices 层设计服务接口，并 Service 层实现
namespace Blog.Core.IService
{
public interface IAdvertisementServices
{
int Sum(int i, int j);
int Add(Advertisement model);
bool Delete(Advertisement model);
bool Update(Advertisement model);
List<Advertisement> Query(Expression<Func<Advertisement, bool>> whereExpression);
}
}
namespace Blog.Core.Service
{
public class AdvertisementServices : IAdvertisementServices
{
public IAdvertisementRepository dal = new AdvertisementRepository();
public int Sum(int i, int j)
{
return dal.Sum(i, j);
}
public int Add(Advertisement model)
{
return dal.Add(model);
}
public bool Delete(Advertisement model)
{
return dal.Delete(model);
}
public List<Advertisement> Query(Expression<Func<Advertisement, bool>> whereExpression)
{
return dal.Query(whereExpression);
}
public bool Update(Advertisement model)
{
return dal.Update(model);
}
}
}
六、Controller测试接口
新增一个Get方法，根据id获取信息
// GET: api/Blog/5
/// <summary>
///
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
[HttpGet("{id}", Name = "Get")]
public List<Advertisement> Get(int id)
{
IAdvertisementServices advertisementServices = new AdvertisementServices();
return advertisementServices.Query(d => d.Id == id);
}
接下来运行调试，在我们接口文档中，直接点击调试.得到的结果返回结果http代码是200
结语
今天简单的了解了什么是ORM，以及其中的SqlSugar，然后呢，仓储模式的具体使用，最后还有真正的连接数据库，获取到数据。
