Implementation of. NET Core Dapper Operating Mysql Database

Time:2019-8-6

Preface

Now ORM is in vogue. N different ORM packages have appeared on the market. Today, let’s not talk about EF, nor about the dark horse, let’s talk about Dapper. How to use Dapper to operate Mysql database in. NET Core? Let’s follow the camera (flip down manually) and see what happens.

Configuration Chapter

As the saying goes, if you want to be good at something, you must first use your weapon. First, we will introduce the NUGet package of MySql. Data. Some people may have black faces, how to introduce them? Well, for your skeletal surprise, I’ll tell you two ways:

The first way


Install-Package MySql.Data -Version 8.0.15

Copy the command line above and execute it in the package management console. What? You don’t know what a package management console is? OMG, or whatever, for your skeletal surprise, I’ll tell you

Hand Path: Tool NuGet Package Manager Package Management Console

The second way

Hand Path: Right-click to introduce the dependencies of the package’s project Manage the NuGet packageBrowse and enter MySql.Data

 

Just install it directly, because I have already installed it, so here is uninstall or update.

In the same way, you need to introduce:


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

Teaching Paper

Everyone who has played. NET Core knows that configuration files are usually placed in appsettings. JSON files, but there is a problem. Is it really safe if we use database connection strings to store plaintext user name and password directly? We don’t discuss security here. We use placeholders in connection strings to control our multi-database situation, and then use userName and passWord as our passwords (which will be replaced later), so it looks like this:

"ConnectionStrings": {
  "Default Connection": "server = server; port = port number; database = Regatta {0}; SslMode = None; uid = userName; PWD = passWord; Allow User Variables = true"
 },

Next, we create a new BaseRepository to read Configuration and set 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", "real account"). Replace ("passWord", "real 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();
}
}

}

Okay, now that we’ve created it, how do we use it? For example, there’s a CrewManager Repository class for manipulating databases. We just need to let this class work.Inherit BaseRepository, as shown below

/// <summary>
    /// Obtain basic user information according to Id and Id
    /// </summary>
    /// <param name="regatta Id">event Id</param>
    /// <param name="userId">user Id</param>
    /// <returns></returns>
    Public async Task < Entity Object > 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 Table 1 a left join table 2 B on a.userid=b.id 
                Left join table 3 C on b.id = c.centralid where [email protected];";
          // Here you can access different databases based on the incoming regattaId
        using (var conn = GetMySqlConnection(regattaId))
        {
          if (conn.State == ConnectionState.Closed)
          {
            await conn.OpenAsync();
          }

          var memberModel = conn
            Query < Entity Object > (cmdText, new {userId = userId}, commandType: CommandType. Text)
            .FirstOrDefault();
          return memberModel ?? new MemberDetail();
        }
      }
      catch (Exception ex)
      {
        _logger.LogError(ex, "FindUserByAccount by Id Failed!");
        throw;
      }


    }

Some students may have black faces. What if they need something (a smile on the corner of their mouth)?

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 table name where field [email protected];
               Delete from table name 2 where field [email protected];
               Delete from table name 3 where field [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;
      }
    }

In this way, the block of execution code is wrapped up with Transaction, and if there is an exception, Rollback (rollback transaction) in catch can ensure the consistency of data. If it’s a high concurrency scenario, locks may also be needed. There’s no extended discussion here for the time being.

If it is a return set, it is also easy to handle:

Public async Task < List < Entity > GetClubs ByUserId (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 table 1 a left join table 2 B on 
           A. clubinfo_id = B. club_id left join table 3C on 
           b.clubbase_id=c.club_id where [email protected]_Id";
        List < Entity > clubDetailList=
          (await it Conn. Query Async < entity > (sql, new {user_Id = userId}, commandType: CommandType. Text))
          .ToList();

        return clubDetailList;
      }
    }

Here’s a sample of Dapper. You can browse the official website for more information.

https://dapper-tutorial.net/

The above is the whole content of this article. I hope it will be helpful to everyone’s study, and I hope you will support developpaer more.