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; } } } |
本文内容不用于商业目的，如涉及知识产权问题，请权利人联系51Testing小编(021-64471599-8017)，我们将立即处理
