1、通过Nuget引用MySqlConnector
MySqlConnector是用于.NET和.NET Core的异步MySQL连接器，MySQL的ADO.NET数据提供程序。它提供的实现，查询和更新从托管代码数据库所需类(DbConnection，DbCommand，DbDataReader，DbTransaction等)。此库为数据库操作实现真正的异步I/O，而不阻塞（或使用Task.Run在后台线程上运行同步方法）。这极大地提高了执行数据库操作的Web服务器的吞吐量。
官方地址：https://github.com/mysql-net/MySqlConnector
在Nuget管理程序中，搜索'MySqlConnector' =》选中然后点击'安装'。
相关文档：VS(Visual Studio)中Nuget的使用
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<AppDb>(_ => 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<IActionResult> 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<IActionResult> 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<IActionResult> 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<IActionResult> 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<IActionResult> 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<IActionResult> 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<BlogPost> 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<List<BlogPost>> 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<List<BlogPost>> ReadAllAsync(DbDataReader reader)
{
var posts = new List<BlogPost>();
using (reader)
{
while (await reader.ReadAsync())
{
var post = new BlogPost(Db)
{
Id = await reader.GetFieldValueAsync<int>(0),
Title = await reader.GetFieldValueAsync<string>(1),
Content = await reader.GetFieldValueAsync<string>(2)
};
posts.Add(post);
}
}
return posts;
}
}
}