ASP.NET MVC 連接 MySQL 數據庫查詢示例

為您創建一個完整的?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, '電子產品');