本文主要介绍.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<AppDb>(_ => 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<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;
        }
    }
}