前言

现在ORM盛行,市面上已经出现了N款不同的ORM套餐了。今天,我们不谈EF,也不聊神马黑马,就说说 Dapper。如何在.NET
Core中使用Dapper操作Mysql数据库呢,让我们跟随镜头(手动下翻)一看究竟。

配置篇

俗话说得好,欲要善其事必先利其器。首先,我们要引入MySql.Data
的Nuget包。有人可能出现了黑人脸,怎么引入。也罢,看在你骨骼惊奇的份上,我就告诉你,两种方式:

第一种方式
Install-Package MySql.Data -Version 8.0.15
 复制上面命令行 在程序包管理控制台中执行,什么?你不知道什么是程序包管理控制台?OMG,也罢,看在你骨骼惊奇的份上,我就告诉你

 手点路径:工具 → NuGet包管理器 → 程序包管理控制台 



第二种方式

手点路径:右键你需要引入包的项目的依赖项 → 管理NuGet程序包 → 浏览里面输入MySql.Data

 

直接安装即可,因为我已经安装过了,所以这里是卸载或者更新

同样的方式你需要引入:
Microsoft.AspNetCore.All MySql.Data.EntityFrameworkCore、
Dapper
Microsoft.Extensions.Configuration.Abstractions
Microsoft.Extensions.Configuration.FileExtensions
Microsoft.Extensions.Configuration.Json

 

教学篇

玩儿过.NET Core 的都知道配置文件我们一般都放在appsettings.json
文件中,但是有个问题,如果我们使用数据库连接字符串,直接存放明文的user
name和password,真的安全吗?这里我们不对安全性做讨论,我们在连接字符串中
用占位符控制我们的多数据库情况,然后用userName以及passWord充当我们密码(后面会被替换掉),所以看起来是这个样子:
"ConnectionStrings": { "DefaultConnection":
"server=服务器;port=端口号;database=regatta{0};SslMode=None;uid=userName;pwd=passWord;Allow
User Variables=true" },
接下来,我们新建一个BaseRepository 用于读取Configuration,以及设置MySqlConnection:

 
public class BaseRepository : IDisposable { public static IConfigurationRoot
Configuration {get; set; } private MySqlConnection conn; public MySqlConnection
GetMySqlConnection(int regattaId = 0, bool open = true, bool
convertZeroDatetime =false, bool allowZeroDatetime = false) {
IConfigurationBuilder builder= new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory()) .AddJsonFile("appsettings.json");
Configuration= builder.Build(); string cs = Configuration.GetConnectionString("
DefaultConnection"); cs = regattaId == 0 ? string.Format(cs, string.Empty) :
string.Format(cs, "_" + regattaId.ToString()); cs = cs.Replace("userName", "
真正的账号").Replace("passWord", "真正的密码"); var csb = new
MySqlConnectionStringBuilder(cs) { AllowZeroDateTime= allowZeroDatetime,
ConvertZeroDateTime= convertZeroDatetime }; conn = new
MySqlConnection(csb.ConnectionString);return conn; }
public void Dispose() { if (conn != null && conn.State !=
System.Data.ConnectionState.Closed) { conn.Close(); } } }
好了,创建完毕,我们该如何使用呢,比方说 现在有个CrewManagerRepository类用于操作数据库,我们只需要让此类
继承BaseRepository ,示例如下

 
/// <summary> /// 根据赛事Id、用户Id获取用户基本信息 /// </summary> /// <param
name="regattaId">赛事Id</param> /// <param name="userId">用户Id</param> ///
<returns></returns> public async Task<实体对象> FindUserByAccount(int regattaId, int
userId) {try { var cmdText = @"select b.id_number as IdentifierId,b.isvalid as
Isvalid,a.name as Name,a.userid as InternalId,a.sex as Sexual,a.sex as
SexTypeId,a.age as Age, c.isprofessional as IsProfessional,c.role_type as
RoleTypeId,a.weight as Weight,a.height as Height, a.phone as
PhoneNumber,a.thumb_image as ThubmnailImage, a.image as Image,c.athlete_id as
AthleteId from 表1 a left join 表2 b on a.userid=b.id left join 表3 c on
b.id=c.centralid where [email protected];";
          //此处可以根据传入的regattaId访问不同的数据库 using (var conn =
GetMySqlConnection(regattaId)) {if (conn.State == ConnectionState.Closed) {
await conn.OpenAsync(); } var memberModel = conn .Query<实体对象>(cmdText, new {
userId = userId }, commandType: CommandType.Text) .FirstOrDefault(); return
memberModel ??new MemberDetail(); } } catch (Exception ex) {
_logger.LogError(ex,"FindUserByAccount by Id Failed!"); throw; } }
 

那有同学可能有黑人脸出现了,如果需要事务呢(露出嘴角的微笑)?
public async Task<bool> DeleteXXX(int regattaId, int id, int userId) { var
result =false; using (var conn = GetMySqlConnection(regattaId)) { if
(conn.State == ConnectionState.Closed) { await conn.OpenAsync(); } using (var
transaction = conn.BeginTransaction()) { try { const string sqlDelClub = @"
delete from 表名 where 字段[email protected]; delete from 表名2 where 字段[email protected]; delete
from 表名3 where 字段[email protected] and [email protected];"; await
conn.QueryAsync(sqlDelClub,new { clubId = id, userId = userId, }, commandType:
CommandType.Text);transaction.Commit(); result = true; } catch (Exception e) {
Console.WriteLine(e);transaction.Rollback(); result = false; throw; } } return
result; } }
这样,用Transaction将执行代码块包起来,如果出现异常,在catch中
进行Rollback(回滚事务),就可以保证了数据的一致性。如果是高并发场景,可能还会需要用到锁,这里暂时不做延伸讨论。

如果是返回集合,也很容易处理:
public async Task<List<实体>> GetClubsByUserId(int regattaId, int userId) { using
(var conn = GetMySqlConnection(regattaId)) { if (conn.State ==
ConnectionState.Closed) {await conn.OpenAsync(); } const string sql = @"select
b.club_id as id,c.name,c.image as
ImageData,c.year,c.address,c.creator,c.description,b.contact ,b.phone,b.isvalid
from 表1 a left join 表2 b on a.clubinfo_id=b.club_id left join 表3 c on
b.clubbase_id=c.club_id where [email protected]_Id"; List<实体>
clubDetailList = (await conn.QueryAsync<实体>(sql, new { user_Id = userId },
commandType: CommandType.Text)) .ToList();return clubDetailList; } }
关于Dapper的示例 本文就讲到这儿,大家可以上官网浏览了解更多:

https://dapper-tutorial.net/ <https://dapper-tutorial.net/>

 

本文完