C# MVC 4中用OleDb单SELECT语句连接Access同库两表

我来帮你搞定这个问题！核心就是用一条SQL关联Access里的PhoneList和Employee表，再把数据分别绑定到Grid.Mvc和页面标签上，下面一步步来：

首先得确定两个表的关联字段（比如EmployeeID，假设PhoneList里用这个字段关联Employee的主键）。用JOIN把两个表的数据合并，一条SQL就能拿到所有需要的信息：

SELECT pl.*, emp.EmployeeName, emp.Department, emp.Position
FROM PhoneList pl
INNER JOIN Employee emp ON pl.EmployeeID = emp.EmployeeID

如果想保留所有员工（哪怕没有电话记录），把INNER JOIN换成LEFT JOIN就行。

在MVC项目里新建一个ViewModel类，用来存放查询出来的合并数据，方便后续视图绑定：

public class EmployeePhoneViewModel
{
    // PhoneList表的字段，根据你的实际表结构调整
    public int PhoneID { get; set; }
    public string PhoneNumber { get; set; }
    public string PhoneType { get; set; }
    public int EmployeeID { get; set; }

    // Employee表的字段，同样根据实际结构调整
    public string EmployeeName { get; set; }
    public string Department { get; set; }
    public string Position { get; set; }
}

在Controller里写数据访问逻辑，用OleDb连接Access，执行SQL并把结果映射到ViewModel集合：

public ActionResult Index()
{
    // 注意：如果是Access 2007+，把Provider换成Microsoft.ACE.OLEDB.12.0
    string connStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\My HR.mdb;";
    var employeePhoneData = new List<EmployeePhoneViewModel>();

    using (var conn = new OleDbConnection(connStr))
    {
        string sql = @"SELECT pl.*, emp.EmployeeName, emp.Department, emp.Position
                       FROM PhoneList pl
                       INNER JOIN Employee emp ON pl.EmployeeID = emp.EmployeeID";
        var cmd = new OleDbCommand(sql, conn);
        conn.Open();

        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                // 处理DBNull避免转换报错
                var item = new EmployeePhoneViewModel
                {
                    PhoneID = reader.IsDBNull(reader.GetOrdinal("PhoneID")) ? 0 : Convert.ToInt32(reader["PhoneID"]),
                    PhoneNumber = reader.IsDBNull(reader.GetOrdinal("PhoneNumber")) ? "" : reader["PhoneNumber"].ToString(),
                    PhoneType = reader.IsDBNull(reader.GetOrdinal("PhoneType")) ? "" : reader["PhoneType"].ToString(),
                    EmployeeID = reader.IsDBNull(reader.GetOrdinal("EmployeeID")) ? 0 : Convert.ToInt32(reader["EmployeeID"]),
                    EmployeeName = reader.IsDBNull(reader.GetOrdinal("EmployeeName")) ? "" : reader["EmployeeName"].ToString(),
                    Department = reader.IsDBNull(reader.GetOrdinal("Department")) ? "" : reader["Department"].ToString(),
                    Position = reader.IsDBNull(reader.GetOrdinal("Position")) ? "" : reader["Position"].ToString()
                };
                employeePhoneData.Add(item);
            }
        }
    }

    // 默认把第一条员工数据传到ViewBag，用来填充标签
    ViewBag.CurrentEmployee = employeePhoneData.FirstOrDefault();
    return View(employeePhoneData);
}

在视图里把ViewModel集合绑定到Grid.Mvc，同时用ViewBag的数据填充标签；如果需要点击Grid行切换标签内容，再加个AJAX逻辑：

@model IEnumerable<EmployeePhoneViewModel>

<!-- Grid.Mvc 绑定PhoneList相关数据 -->
<div class="grid-container">
    @Html.Grid(Model).Columns(columns =>
    {
        columns.Add(c => c.PhoneNumber).Titled("电话号码");
        columns.Add(c => c.PhoneType).Titled("号码类型");
        columns.Add(c => c.EmployeeName).Titled("所属员工");
        // 加个按钮触发标签更新
        columns.Add().Encoded(false).RenderValueAs(c => $"<button class='btn btn-sm' onclick='showEmployeeInfo({c.EmployeeID})'>查看详情</button>");
    }).WithPaging(10)
</div>

<!-- 显示Employee信息的标签区域 -->
<div class="employee-info mt-4">
    @if (ViewBag.CurrentEmployee != null)
    {
        <h5>员工详情</h5>
        <p>姓名：<strong>@ViewBag.CurrentEmployee.EmployeeName</strong></p>
        <p>部门：<strong>@ViewBag.CurrentEmployee.Department</strong></p>
        <p>职位：<strong>@ViewBag.CurrentEmployee.Position</strong></p>
    }
</div>

<!-- AJAX动态更新标签内容 -->
<script>
function showEmployeeInfo(empId) {
    $.ajax({
        url: '@Url.Action("GetEmployeeDetails")',
        type: 'GET',
        data: { id: empId },
        success: function(data) {
            $(".employee-info p:nth-child(2) strong").text(data.EmployeeName);
            $(".employee-info p:nth-child(3) strong").text(data.Department);
            $(".employee-info p:nth-child(4) strong").text(data.Position);
        }
    });
}
</script>

如果要实现点击Grid行动态更新标签，需要加一个返回员工详情的Action：

public JsonResult GetEmployeeDetails(int id)
{
    string connStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\My HR.mdb;";
    var empDetails = new EmployeeViewModel();

    using (var conn = new OleDbConnection(connStr))
    {
        string sql = @"SELECT EmployeeName, Department, Position FROM Employee WHERE EmployeeID = @Id";
        var cmd = new OleDbCommand(sql, conn);
        cmd.Parameters.AddWithValue("@Id", id);
        conn.Open();

        using (var reader = cmd.ExecuteReader())
        {
            if (reader.Read())
            {
                empDetails.EmployeeName = reader.IsDBNull(reader.GetOrdinal("EmployeeName")) ? "" : reader["EmployeeName"].ToString();
                empDetails.Department = reader.IsDBNull(reader.GetOrdinal("Department")) ? "" : reader["Department"].ToString();
                empDetails.Position = reader.IsDBNull(reader.GetOrdinal("Position")) ? "" : reader["Position"].ToString();
            }
        }
    }

    return Json(empDetails, JsonRequestBehavior.AllowGet);
}

// 对应的ViewModel
public class EmployeeViewModel
{
    public string EmployeeName { get; set; }
    public string Department { get; set; }
    public string Position { get; set; }
}

• 连接字符串：Access 2007+必须用Microsoft.ACE.OLEDB.12.0作为Provider，还要确保服务器安装了Access数据库引擎。
• 字段名空格：如果Access字段名带空格，SQL里要加方括号，比如[Employee Name]。
• DBNull处理：一定要判断字段是否为DBNull，否则会抛出转换异常。
• Grid.Mvc版本：确保你的Grid.Mvc版本支持当前语法，比如Titled、RenderValueAs这些方法。

内容的提问来源于stack exchange，提问作者user3051175