为您创建一个完整的 ASP.NET MVC 应用程序，演示如何通过点击按钮连接 MySQL 数据库并查询数据表。

完整实现步骤

1. 首先安装必要的 NuGet 包

在项目中安装以下 NuGet 包：

MySql.Data
Dapper（可选，用于简化数据访问）

Install-Package MySql.Data
Install-Package Dapper

2. 配置数据库连接字符串

在 Web.config 文件中添加连接字符串：

<configuration><connectionStrings><add name="MySqlConnection" connectionString="Server=localhost;Database=your_database;Uid=username;Pwd=password;" providerName="MySql.Data.MySqlClient"/></connectionStrings>
</configuration>

3. 创建数据模型

创建 Models/Product.cs 类：

namespace MvcMySqlApp.Models
{public class Product{public int Id { get; set; }public string Name { get; set; }public string Description { get; set; }public decimal Price { get; set; }public int Stock { get; set; }public string Category { get; set; }public DateTime CreatedAt { get; set; }}
}

4. 创建数据访问层

创建 Data/ProductRepository.cs 类：

using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;
using MvcMySqlApp.Models;
using System.Configuration;
using Dapper;namespace MvcMySqlApp.Data
{public class ProductRepository{private readonly string connectionString;public ProductRepository(){connectionString = ConfigurationManager.ConnectionStrings["MySqlConnection"].ConnectionString;}public IDbConnection GetConnection(){return new MySqlConnection(connectionString);}// 使用 ADO.NET 方式查询public List<Product> GetProductsWithAdapter(){var products = new List<Product>();using (var connection = new MySqlConnection(connectionString)){connection.Open();// 使用 MySqlDataAdaptervar adapter = new MySqlDataAdapter("SELECT * FROM Products", connection);var dataSet = new DataSet();adapter.Fill(dataSet);foreach (DataRow row in dataSet.Tables[0].Rows){products.Add(new Product{Id = Convert.ToInt32(row["Id"]),Name = row["Name"].ToString(),Description = row["Description"].ToString(),Price = Convert.ToDecimal(row["Price"]),Stock = Convert.ToInt32(row["Stock"]),Category = row["Category"].ToString(),CreatedAt = Convert.ToDateTime(row["CreatedAt"])});}}return products;}// 使用 Dapper 简化查询（可选）public IEnumerable<Product> GetProductsWithDapper(){using (var connection = GetConnection()){connection.Open();return connection.Query<Product>("SELECT * FROM Products");}}// 添加其他数据库操作方法...}
}

5. 创建控制器

创建 Controllers/ProductsController.cs：

using System.Web.Mvc;
using MvcMySqlApp.Data;
using MvcMySqlApp.Models;
using System.Collections.Generic;namespace MvcMySqlApp.Controllers
{public class ProductsController : Controller{private readonly ProductRepository _repository;public ProductsController(){_repository = new ProductRepository();}// GET: Productspublic ActionResult Index(){return View();}// AJAX 动作：获取产品数据[HttpPost]public ActionResult GetProducts(){try{// 使用 ADO.NET DataAdapter 查询数据List<Product> products = _repository.GetProductsWithAdapter();return Json(new { success = true, data = products });}catch (System.Exception ex){return Json(new { success = false, message = ex.Message });}}// 其他动作方法...}
}

6. 创建视图

创建 Views/Products/Index.cshtml：

@{ViewBag.Title = "产品列表";
}<div class="container mt-4"><h2>产品列表</h2><p>点击按钮从MySQL数据库获取产品数据</p><div class="row"><div class="col-md-12"><button id="btnGetProducts" class="btn btn-primary mb-3"><i class="fas fa-database"></i> 获取产品数据</button><div id="loading" class="alert alert-info" style="display: none;"><i class="fas fa-spinner fa-spin"></i> 正在查询数据库，请稍候...</div><div id="error" class="alert alert-danger" style="display: none;"></div><table id="productsTable" class="table table-striped table-bordered" style="display: none;"><thead class="thead-dark"><tr><th>ID</th><th>名称</th><th>描述</th><th>价格</th><th>库存</th><th>分类</th><th>创建时间</th></tr></thead><tbody><!-- 数据将通过JavaScript动态填充 --></tbody></table></div></div>
</div>@section Scripts {<script>$(document).ready(function() {$('#btnGetProducts').click(function() {// 显示加载提示$('#loading').show();$('#error').hide();$('#productsTable').hide();// 发送AJAX请求到服务器$.ajax({url: '@Url.Action("GetProducts", "Products")',type: 'POST',dataType: 'json',success: function(response) {$('#loading').hide();if (response.success) {// 清空表格$('#productsTable tbody').empty();// 填充数据$.each(response.data, function(index, product) {var row = '<tr>' +'<td>' + product.Id + '</td>' +'<td>' + product.Name + '</td>' +'<td>' + (product.Description || '') + '</td>' +'<td>¥' + product.Price.toFixed(2) + '</td>' +'<td>' + product.Stock + '</td>' +'<td>' + product.Category + '</td>' +'<td>' + new Date(product.CreatedAt).toLocaleDateString() + '</td>' +'</tr>';$('#productsTable tbody').append(row);});// 显示表格$('#productsTable').show();} else {$('#error').text('错误: ' + response.message).show();}},error: function(xhr, status, error) {$('#loading').hide();$('#error').text('请求错误: ' + error).show();}});});});</script>
}

7. 添加布局和样式

确保 Views/Shared/_Layout.cshtml 包含 Bootstrap 和 Font Awesome：

<!DOCTYPE html>
<html>
<head><meta charset="utf-8" /><meta name="viewport" content="width=device-width, initial-scale=1.0"><title>@ViewBag.Title - MySQL MVC 应用</title>@Styles.Render("~/Content/css")@Scripts.Render("~/bundles/modernizr")<!-- 添加 Bootstrap 和 Font Awesome --><link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet"><link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0-beta3/css/all.min.css">
</head>
<body><nav class="navbar navbar-expand-lg navbar-dark bg-dark"><div class="container">@Html.ActionLink("MySQL MVC 应用", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })<button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarNav"><span class="navbar-toggler-icon"></span></button><div class="collapse navbar-collapse" id="navbarNav"><ul class="navbar-nav"><li class="nav-item">@Html.ActionLink("首页", "Index", "Home", null, new { @class = "nav-link" })</li><li class="nav-item">@Html.ActionLink("产品", "Index", "Products", null, new { @class = "nav-link" })</li></ul></div></div></nav><div class="container body-content">@RenderBody()<hr /><footer><p>&copy; @DateTime.Now.Year - MySQL MVC 应用</p></footer></div>@Scripts.Render("~/bundles/jquery")@Scripts.Render("~/bundles/bootstrap")<!-- 添加 Bootstrap JS --><script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>@RenderSection("scripts", required: false)
</body>
</html>

8. 创建数据库表

在MySQL中创建示例表：

CREATE DATABASE IF NOT EXISTS ProductDB;
USE ProductDB;CREATE TABLE Products (Id INT AUTO_INCREMENT PRIMARY KEY,Name VARCHAR(100) NOT NULL,Description TEXT,Price DECIMAL(10, 2) NOT NULL,Stock INT NOT NULL DEFAULT 0,Category VARCHAR(50),CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
);-- 插入示例数据
INSERT INTO Products (Name, Description, Price, Stock, Category) VALUES
('智能手机', '最新款智能手机', 1999.99, 50, '电子产品'),
('笔记本电脑', '高性能笔记本电脑', 4999.99, 30, '电子产品'),
('平板电脑', '轻薄便携平板', 2599.99, 25, '电子产品'),
('智能手表', '健康监测智能手表', 899.99, 40, '电子产品'),
('无线耳机', '主动降噪无线耳机', 699.99, 60, '电子产品');