开发手册 欢迎您!
软件开发者资料库

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

本文主要介绍.NET Core中使用ADO.NET的原生方式,不使用ORM框架来连接操作MySQL数据库的方法及示例代码。

1、通过Nuget引用MySqlConnector

MySqlConnector是用于.NET和.NET Core的异步MySQL连接器,MySQL的ADO.NET数据提供程序。它提供的实现,查询和更新从托管代码数据库所需类(DbConnectionDbCommandDbDataReaderDbTransaction等)。此库为数据库操作实现真正的异步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(_ => new AppDb(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 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;
        }
    }
}