.NET Core 使用ADO.NET连接操作MySQL数据库

1、通过Nuget引用MySqlConnector

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

官方地址：https://github.com/mysql-net/MySqlConnector

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

相关文档：VS(VisualStudio)中Nuget的使用

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

{
"Logging":{
"IncludeScopes":false,
"LogLevel":{
"Default":"Error",
"System":"Error",
"Microsoft":"Error"
}
},
"ConnectionStrings":{
"DefaultConnection":"server=127.0.0.1;userid=mysqltest;password=test;port=3306;database=blog;",
}
}

3、封装MySqlConnection连接类

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

usingSystem;
usingMySql.Data.MySqlClient;
namespaceMySqlConnector.Conn
{
public>AppDb:IDisposable
{
publicMySqlConnectionConnection;
publicAppDb(stringconnectionString)
{
Connection=newMySqlConnection(connectionString);
}
publicvoidDispose()
{
Connection.Close();
}
}
}

在Startup.cs中注入连接对象：

services.AddTransient<AppDb>(_=>newAppDb(Configuration["ConnectionStrings:DefaultConnection"]));

注意：

1. Transient：每次从容器（IServiceProvider）中获取的时候都是一个新的实例
2. Singleton：每次从同根容器中（同根IServiceProvider）获取的时候都是同一个实例
3. 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 >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代码

usingMySql.Data.MySqlClient;
usingNewtonsoft.Json;
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Data;
usingSystem.Linq;
usingSystem.Threading.Tasks;
namespaceWebApplication2.Models
{
public>BlogPost
{
publicintId{get;set;}
publicstringTitle{get;set;}
publicstringContent{get;set;}
[JsonIgnore]
publicAppDbDb{get;set;}
publicBlogPost(AppDbdb=null)
{
Db=db;
}
publicasyncTaskInsertAsync()
{
varcmd=Db.Connection.CreateCommand()asMySqlCommand;
cmd.CommandText=@"INSERTINTO`BlogPost`(`Title`,`Content`)VALUES(@title,@content);";
BindParams(cmd);
awaitcmd.ExecuteNonQueryAsync();
Id=(int)cmd.LastInsertedId;
}
publicasyncTaskUpdateAsync()
{
varcmd=Db.Connection.CreateCommand()asMySqlCommand;
cmd.CommandText=@"UPDATE`BlogPost`SET`Title`=@title,`Content`=@contentWHERE`Id`=@id;";
BindParams(cmd);
BindId(cmd);
awaitcmd.ExecuteNonQueryAsync();
}
publicasyncTaskDeleteAsync()
{
varcmd=Db.Connection.CreateCommand()asMySqlCommand;
cmd.CommandText=@"DELETEFROM`BlogPost`WHERE`Id`=@id;";
BindId(cmd);
awaitcmd.ExecuteNonQueryAsync();
}
privatevoidBindId(MySqlCommandcmd)
{
cmd.Parameters.Add(newMySqlParameter
{
ParameterName="@id",
DbType=DbType.Int32,
Value=Id,
});
}
privatevoidBindParams(MySqlCommandcmd)
{
cmd.Parameters.Add(newMySqlParameter
{
ParameterName="@title",
DbType=DbType.String,
Value=Title,
});
cmd.Parameters.Add(newMySqlParameter
{
ParameterName="@content",
DbType=DbType.String,
Value=Content,
});
}
}
}

3）BlogPostQuery代码

usingMySql.Data.MySqlClient;
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Data;
usingSystem.Data.Common;
usingSystem.Linq;
usingSystem.Threading.Tasks;
namespaceWebApplication2.Models
{
public>BlogPostQuery
{
publicreadonlyAppDbDb;
publicBlogPostQuery(AppDbdb)
{
Db=db;
}
publicasyncTask<BlogPost>FindOneAsync(intid)
{
varcmd=Db.Connection.CreateCommand()asMySqlCommand;
cmd.CommandText=@"SELECT`Id`,`Title`,`Content`FROM`BlogPost`WHERE`Id`=@id";
cmd.Parameters.Add(newMySqlParameter
{
ParameterName="@id",
DbType=DbType.Int32,
Value=id,
});
varresult=awaitReadAllAsync(awaitcmd.ExecuteReaderAsync());
returnresult.Count>0?result[0]:null;
}
publicasyncTask<List<BlogPost>>LatestPostsAsync()
{
varcmd=Db.Connection.CreateCommand();
cmd.CommandText=@"SELECT`Id`,`Title`,`Content`FROM`BlogPost`ORDERBY`Id`DESCLIMIT10;";
returnawaitReadAllAsync(awaitcmd.ExecuteReaderAsync());
}
publicasyncTaskDeleteAllAsync()
{
vartxn=awaitDb.Connection.BeginTransactionAsync();
try
{
varcmd=Db.Connection.CreateCommand();
cmd.CommandText=@"DELETEFROM`BlogPost`";
awaitcmd.ExecuteNonQueryAsync();
awaittxn.CommitAsync();
}
catch
{
awaittxn.RollbackAsync();
throw;
}
}
privateasyncTask<List<BlogPost>>ReadAllAsync(DbDataReaderreader)
{
varposts=newList<BlogPost>();
using(reader)
{
while(awaitreader.ReadAsync())
{
varpost=newBlogPost(Db)
{
Id=awaitreader.GetFieldValueAsync<int>(0),
Title=awaitreader.GetFieldValueAsync<string>(1),
Content=awaitreader.GetFieldValueAsync<string>(2)
};
posts.Add(post);
}
}
returnposts;
}
}
}