.NET 7.0 + MySQL在 C# 和 ASP.NET Core 中使用 Dapper 连接到 MySQL 数据库

在C#和ASP.NET Core中使用Dapper连接到MySQL数据库是一种简单而高效的方法。Dapper是一个轻量级的ORM（对象关系映射）工具，它提供了一种简单的方式来执行SQL查询和操作数据库。

首先，确保你已经安装了.NET 7.0和MySQL数据库。然后，通过NuGet包管理器或者使用dotnet命令行工具安装Dapper。

接下来，创建一个新的C#控制台应用程序或ASP.NET Core项目。在项目中添加对Dapper和MySQL连接器的引用。

在C#控制台应用程序中，你可以使用以下代码示例连接到MySQL数据库：

csharp
using System;
using System.Data;
using System.Data.SqlClient;
using Dapper;

namespace DapperExample
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = Your MySQL Connection String;
            
            using (IDbConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                
                // 查询数据
                var result = connection.Query(SELECT * FROM Customers);
                foreach (var customer in result)
                {
                    Console.WriteLine($Customer ID: {customer.CustomerId} Name: {customer.Name});
                }
                
                // 插入数据
                var newCustomer = new { CustomerId = 1 Name = John Doe };
                connection.Execute(INSERT INTO Customers (CustomerId Name) VALUES (@CustomerId @Name) newCustomer);
                
                // 更新数据
                var updatedCustomer = new { CustomerId = 1 Name = Jane Smith };
                connection.Execute(UPDATE Customers SET Name = @Name WHERE CustomerId = @CustomerId updatedCustomer);
                
                // 删除数据
                var customerIdToDelete = 1;
                connection.Execute(DELETE FROM Customers WHERE CustomerId = @CustomerId new { CustomerId = customerIdToDelete });
            }
        }
    }
}

csharp
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using Dapper;
using MySql.Data.MySqlClient;

namespace DapperExample.Controllers
{
    [ApiController]
    [Route([controller])]
    public class CustomersController : ControllerBase
    {
        private readonly string _connectionString;

        public CustomersController()
        {
            _connectionString = Your MySQL Connection String;
        }

        [HttpGet]
        public IEnumerable Get()
        {
            using (var connection = new MySqlConnection(_connectionString))
            {
                connection.Open();

                var result = connection.Query(SELECT * FROM Customers);
                return result;
            }
        }

        [HttpPost]
        public void Post(Customer customer)
        {
            using (var connection = new MySqlConnection(_connectionString))
            {
                connection.Open();

                connection.Execute(INSERT INTO Customers (CustomerId Name) VALUES (@CustomerId @Name) customer);
            }
        }

        [HttpPut({id})]
        public void Put(int id Customer customer)
        {
            using (var connection = new MySqlConnection(_connectionString))
            {
                connection.Open();

                customer.CustomerId = id;
                connection.Execute(UPDATE Customers SET Name = @Name WHERE CustomerId = @CustomerId customer);
            }
        }

        [HttpDelete({id})]
        public void Delete(int id)
        {
            using (var connection = new MySqlConnection(_connectionString))
            {
                connection.Open();

                connection.Execute(DELETE FROM Customers WHERE CustomerId = @CustomerId new { CustomerId = id });
            }
        }
    }

    public class Customer
    {
        public int CustomerId { get; set; }
        public string Name { get; set; }
    }
}