步骤 1: 创建一个新的 ASP.NET Core Web API 项目

步骤 2: 安装所需的 NuGet 包(不要安装9.0.0的Json包，不兼容)

步骤 3: 配置数据库连接字符串

在 appsettings.json 中添加数据库

{
    "ConnectionStrings": {
        "MySqlConnection": "Server=localhost;Port=3306;Database=qq123;User=root;Password=123123;"
    },
    "Logging": {
        "LogLevel": {
            "Default": "Information",
            "Microsoft.AspNetCore": "Warning"
        }
    },
    "AllowedHosts": "*"
}

步骤 4: 创建数据模型类

在项目中创建一个 Models 文件夹，并添加以下 C# 类：这里的属性要和数据库里面对应

namespace WebApplication9.Models
{
    public class HistoryAlarm
    {
        public int Id { get; set; }
        public int ProductId { get; set; }
        public int Quantity { get; set; }
        public decimal TotalPrice { get; set; }
    }
}



namespace WebApplication9.Models
{
    public class HistoryTrend
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
    }
}

步骤 5: 创建 ApplicationDbContext 类

在项目中创建一个 Data 文件夹，并在其中添加 ApplicationDbContext 类：

这步可以跳过，如果你不使用Entity Framework

步骤 6. 创建服务类来处理 MySQL 操作

在 Services 文件夹下创建一个服务类，使用 MySqlConnection 来执行 SQL 查询。假设我们需要从 Products 和 Orders 表中获取数据。

using MySql.Data.MySqlClient;
using System.Collections.Generic;
using System.Data;
using Microsoft.Extensions.Configuration;
using System;
using WebApplication9.Models;

namespace WebApplication9.Services
{
    public class MySqlService
    {
        private readonly string _connectionString;

        public MySqlService(IConfiguration configuration)
        {
            _connectionString = configuration.GetConnectionString("MySqlConnection");
        }

        public List<HistoryAlarm> GetProducts()
        {
            List<HistoryAlarm> products = new List<HistoryAlarm>();

            using (var connection = new MySqlConnection(_connectionString))
            {
                connection.Open();

                string query = "SELECT * FROM HistoryAlarm";
                using (var command = new MySqlCommand(query, connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var product = new HistoryAlarm
                            {
                                Id = reader.GetInt32("Id"),
                                ProductId = reader.GetInt32("ProductId"),
                            };
                            products.Add(product);
                        }
                    }
                }
            }

            return products;
        }

        public List<HistoryTrend> GetOrders()
        {
            List<HistoryTrend> orders = new List<HistoryTrend>();

            using (var connection = new MySqlConnection(_connectionString))
            {
                connection.Open();

                string query = "SELECT * FROM Orders";
                using (var command = new MySqlCommand(query, connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var order = new HistoryTrend
                            {
                                Id = reader.GetInt32("Id"),
                                Name = reader.GetString("Name"),

                            };
                            orders.Add(order);
                        }
                    }
                }
            }

            return orders;
        }
    }

}

注册 MySqlService 服务

using WebApplication9.Services;

var builder = WebApplication.CreateBuilder(args);

// 注册 MySqlService
builder.Services.AddSingleton<MySqlService>();

builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();

app.Run();

6. 创建控制器

在 Controllers 文件夹中创建一个新的控制器 DataController.cs，并注入 MySqlService 以获取数据库数据。

右键——添加——控制器——选择空

using Microsoft.AspNetCore.Mvc;
using WebApplication9.Services;

namespace WebApiExample.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class DataController : ControllerBase
    {
        private readonly MySqlService _mySqlService;

        public DataController(MySqlService mySqlService)
        {
            _mySqlService = mySqlService;
        }

        // 获取所有产品
        [HttpGet("products")]
        public IActionResult GetProducts()
        {
            var products = _mySqlService.GetProducts();
            return Ok(products);
        }

        // 获取所有订单
        [HttpGet("orders")]
        public IActionResult GetOrders()
        {
            var orders = _mySqlService.GetOrders();
            return Ok(orders);
        }
    }
}

7. 运行 Web API

常见报错：System.IndexOutOfRangeException: Could not find specified column in results: XXX

表结构与创建的C#类不一致。