.NET Core Dapper操作mysql数据库的实现方法

Install-Package MySql.Data -Version 8.0.15

Microsoft.AspNetCore.All
MySql.Data.EntityFrameworkCore、
Dapper
Microsoft.Extensions.Configuration.Abstractions
Microsoft.Extensions.Configuration.FileExtensions
Microsoft.Extensions.Configuration.Json

 "ConnectionStrings": {
 "DefaultConnection": "server=服务器;port=端口号;database=regatta{0};SslMode=None;uid=userName;pwd=passWord;Allow User Variables=true"
 },

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();
}
}

}

 /// <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 a.userid=@userId;";
　　　　　　　　　　//此处可以根据传入的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 字段1=@clubId;
 delete from 表名2 where 字段2=@clubId;
 delete from 表名3 where 字段3=@userId and clubinfo_id=@clubId;";

 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;
 }
 }

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 a.authorize_userid=@user_Id";
 List<实体> clubDetailList =
 (await conn.QueryAsync<实体>(sql, new { user_Id = userId }, commandType: CommandType.Text))
 .ToList();

 return clubDetailList;
 }
 }