1、通过Nuget引用MySqlConnector

MySqlConnector是用于.NET和.NET Core的异步MySQL连接器，MySQL的ADO.NET数据提供程序。它提供的实现，查询和更新从托管代码数据库所需类(DbConnection，DbCommand，DbDataReader，DbTransaction等)。此库为数据库操作实现真正的异步I/O，而不阻塞(或使用Task.Run在后台线程上运行同步方法)。这极大地提高了执行数据库操作的Web服务器的吞吐量。

在Nuget管理程序中，搜索'MySqlConnector' =》选中然后点击'安装'。

2、appsettings.json配置文件连接字符串配置{

"Logging": {

"IncludeScopes": false,

"LogLevel": {

"Default": "Error",

"System": "Error",

"Microsoft": "Error"

}

},

"ConnectionStrings": {

"DefaultConnection": "server=127.0.0.1;user id=mysqltest;password=test;port=3306;database=blog;",

}

}

3、封装MySqlConnection连接类

从配置文件读取ConnectionString，创建连接对象。using System;

using MySql.Data.MySqlClient;

namespace MySqlConnector.Conn

{

public class AppDb : IDisposable

{

public MySqlConnection Connection;

public AppDb(string connectionString)

{

Connection = new MySqlConnection(connectionString);

}

public void Dispose()

{

Connection.Close();

}

}

}

在Startup.cs中注入连接对象：services.AddTransient(_ => new AppDb(Configuration["ConnectionStrings:DefaultConnection"]));

注意：

Transient：每次从容器 (IServiceProvider)中获取的时候都是一个新的实例

Singleton：每次从同根容器中(同根IServiceProvider)获取的时候都是同一个实例

Scoped：每次从同一个容器中获取的实例是相同的

4、项目示例代码

1)HomeController.cs文件代码using System;

using System.Collections.Generic;

using System.Diagnostics;

using System.Linq;

using System.Threading.Tasks;

using Microsoft.AspNetCore.Mvc;

using WebApplication2.Models;

namespace WebApplication2.Controllers

{

public class HomeController : Controller

{

private AppDb db;

public HomeController(AppDb app)

{

db = app;

}

public IActionResult Index()

{

return View();

}

// GET api/async

[HttpGet]

public async Task GetLatest()

{

using (db)

{

await db.Connection.OpenAsync();

var query = new BlogPostQuery(db);

var result = await query.LatestPostsAsync();

return new OkObjectResult(result);

}

}

// GET api/async/5

[HttpGet("{id}")]

public async Task GetOne(int id)

{

using (db)

{

await db.Connection.OpenAsync();

var query = new BlogPostQuery(db);

var result = await query.FindOneAsync(id);

if (result == null)

return new NotFoundResult();

return new OkObjectResult(result);

}

}

// POST api/async

[HttpPost]

public async Task Post([FromBody]BlogPost body)

{

using (db)

{

await db.Connection.OpenAsync();

body.Db = db;

await body.InsertAsync();

return new OkObjectResult(body);

}

}

// PUT api/async/5

[HttpPut("{id}")]

public async Task PutOne(int id, [FromBody]BlogPost body)

{

using (db)

{

await db.Connection.OpenAsync();

var query = new BlogPostQuery(db);

var result = await query.FindOneAsync(id);

if (result == null)

return new NotFoundResult();

result.Title = body.Title;

result.Content = body.Content;

await result.UpdateAsync();

return new OkObjectResult(result);

}

}

// DELETE api/async/5

[HttpDelete("{id}")]

public async Task DeleteOne(int id)

{

using (db)

{

await db.Connection.OpenAsync();

var query = new BlogPostQuery(db);

var result = await query.FindOneAsync(id);

if (result == null)

return new NotFoundResult();

await result.DeleteAsync();

return new OkResult();

}

}

// DELETE api/async

[HttpDelete]

public async Task DeleteAll()

{

using (db)

{

await db.Connection.OpenAsync();

var query = new BlogPostQuery(db);

await query.DeleteAllAsync();

return new OkResult();

}

}

}

}

2)BlogPost代码using MySql.Data.MySqlClient;

using Newtonsoft.Json;

using System;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using System.Threading.Tasks;

namespace WebApplication2.Models

{

public class BlogPost

{

public int Id { get; set; }

public string Title { get; set; }

public string Content { get; set; }

[JsonIgnore]

public AppDb Db { get; set; }

public BlogPost(AppDb db = null)

{

Db = db;

}

public async Task InsertAsync()

{

var cmd = Db.Connection.CreateCommand() as MySqlCommand;

cmd.CommandText = @"INSERT INTO `BlogPost` (`Title`, `Content`) VALUES (@title, @content);";

BindParams(cmd);

await cmd.ExecuteNonQueryAsync();

Id = (int)cmd.LastInsertedId;

}

public async Task UpdateAsync()

{

var cmd = Db.Connection.CreateCommand() as MySqlCommand;

cmd.CommandText = @"UPDATE `BlogPost` SET `Title` = @title, `Content` = @content WHERE `Id` = @id;";

BindParams(cmd);

BindId(cmd);

await cmd.ExecuteNonQueryAsync();

}

public async Task DeleteAsync()

{

var cmd = Db.Connection.CreateCommand() as MySqlCommand;

cmd.CommandText = @"DELETE FROM `BlogPost` WHERE `Id` = @id;";

BindId(cmd);

await cmd.ExecuteNonQueryAsync();

}

private void BindId(MySqlCommand cmd)

{

cmd.Parameters.Add(new MySqlParameter

{

ParameterName = "@id",

DbType = DbType.Int32,

Value = Id,

});

}

private void BindParams(MySqlCommand cmd)

{

cmd.Parameters.Add(new MySqlParameter

{

ParameterName = "@title",

DbType = DbType.String,

Value = Title,

});

cmd.Parameters.Add(new MySqlParameter

{

ParameterName = "@content",

DbType = DbType.String,

Value = Content,

});

}

}

}

3)BlogPostQuery代码using MySql.Data.MySqlClient;

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.Common;

using System.Linq;

using System.Threading.Tasks;

namespace WebApplication2.Models

{

public class BlogPostQuery

{

public readonly AppDb Db;

public BlogPostQuery(AppDb db)

{

Db = db;

}

public async Task FindOneAsync(int id)

{

var cmd = Db.Connection.CreateCommand() as MySqlCommand;

cmd.CommandText = @"SELECT `Id`, `Title`, `Content` FROM `BlogPost` WHERE `Id` = @id";

cmd.Parameters.Add(new MySqlParameter

{

ParameterName = "@id",

DbType = DbType.Int32,

Value = id,

});

var result = await ReadAllAsync(await cmd.ExecuteReaderAsync());

return result.Count > 0 ? result[0] : null;

}

public async Task> LatestPostsAsync()

{

var cmd = Db.Connection.CreateCommand();

cmd.CommandText = @"SELECT `Id`, `Title`, `Content` FROM `BlogPost` ORDER BY `Id` DESC LIMIT 10;";

return await ReadAllAsync(await cmd.ExecuteReaderAsync());

}

public async Task DeleteAllAsync()

{

var txn = await Db.Connection.BeginTransactionAsync();

try

{

var cmd = Db.Connection.CreateCommand();

cmd.CommandText = @"DELETE FROM `BlogPost`";

await cmd.ExecuteNonQueryAsync();

await txn.CommitAsync();

}

catch

{

await txn.RollbackAsync();

throw;

}

}

private async Task> ReadAllAsync(DbDataReader reader)

{

var posts = new List();

using (reader)

{

while (await reader.ReadAsync())

{

var post = new BlogPost(Db)

{

Id = await reader.GetFieldValueAsync(0),

Title = await reader.GetFieldValueAsync(1),

Content = await reader.GetFieldValueAsync(2)

};

posts.Add(post);

}

}

return posts;

}

}

}