ASP.Net Core 3.1 Razor Pages+Dapper员工列表加载问题求助

嘿，我看到你在把Blazor的Dapper示例转成ASP.NET Core 3.1 Razor Pages时遇到了服务调用的问题，咱们一步步来解决它，顺便把增删改功能也补上：

1. 解决当前无法调用GetEmployees()的核心问题 #

你的Employee.cshtml.cs里只是声明了readonly IEmployeeService _employeeService;，但没有通过构造函数依赖注入来实例化服务，运行时肯定会报空引用错误。修改后的代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using BookListRazor.Data;
using BookListRazor.Model;

namespace BookListRazor.Pages
{
    public class EmployeeModel : PageModel
    {
        // 通过依赖注入获取服务实例
        private readonly IEmployeeService _employeeService;

        public IEnumerable<Employee> EmployeeList { get; set; }

        // 构造函数注入服务，框架会自动传入你在Startup中注册的实现类
        public EmployeeModel(IEmployeeService employeeService)
        {
            _employeeService = employeeService;
        }

        public async Task OnGet()
        {
            // 现在可以正常调用服务方法了
            EmployeeList = await _employeeService.GetEmployees();
        }
    }
}

另外，你可以移除代码中无用的SqlConnectionConfiguration声明——连接逻辑已经封装在EmployeeDapperService里，页面层不需要直接操作数据库连接。

2. 在Employee.cshtml中渲染员工列表 #

修改页面代码来展示从服务获取到的数据：

@page
@model BookListRazor.Pages.EmployeeModel
@{
    ViewData["Title"] = "员工列表";
}

<h1>员工列表</h1>

<a asp-page="./CreateEmployee" class="btn btn-primary mb-3">新增员工</a>

<table class="table table-striped">
    <thead>
        <tr>
            <th>姓名</th>
            <th>部门</th>
            <th>职位</th>
            <th>公司</th>
            <th>城市</th>
            <th>操作</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var emp in Model.EmployeeList)
        {
            <tr>
                <td>@emp.Name</td>
                <td>@emp.Department</td>
                <td>@emp.Designation</td>
                <td>@emp.Company</td>
                <td>@emp.City</td>
                <td>
                    <a asp-page="./EditEmployee" asp-route-id="@emp.Id" class="btn btn-sm btn-warning">编辑</a>
                    <a asp-page="./DeleteEmployee" asp-route-id="@emp.Id" class="btn btn-sm btn-danger">删除</a>
                </td>
            </tr>
        }
    </tbody>
</table>

3. 实现新增员工功能 #

3.1 创建CreateEmployee.cshtml.cs #

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using BookListRazor.Data;
using BookListRazor.Model;

namespace BookListRazor.Pages
{
    public class CreateEmployeeModel : PageModel
    {
        private readonly IEmployeeService _employeeService;

        public CreateEmployeeModel(IEmployeeService employeeService)
        {
            _employeeService = employeeService;
        }

        // BindProperty特性用于自动绑定表单提交的数据
        [BindProperty]
        public Employee Employee { get; set; }

        public IActionResult OnGet()
        {
            return Page();
        }

        public async Task<IActionResult> OnPostAsync()
        {
            if (!ModelState.IsValid)
            {
                return Page();
            }

            await _employeeService.CreateEmployee(Employee);
            // 新增完成后跳回员工列表页
            return RedirectToPage("./Employee");
        }
    }
}

3.2 创建CreateEmployee.cshtml #

@page
@model BookListRazor.Pages.CreateEmployeeModel
@{
    ViewData["Title"] = "新增员工";
}

<h1>新增员工</h1>

<form method="post">
    <div class="form-group">
        <label asp-for="Employee.Name" class="control-label"></label>
        <input asp-for="Employee.Name" class="form-control" />
        <span asp-validation-for="Employee.Name" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label asp-for="Employee.Department" class="control-label"></label>
        <input asp-for="Employee.Department" class="form-control" />
        <span asp-validation-for="Employee.Department" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label asp-for="Employee.Designation" class="control-label"></label>
        <input asp-for="Employee.Designation" class="form-control" />
        <span asp-validation-for="Employee.Designation" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label asp-for="Employee.Company" class="control-label"></label>
        <input asp-for="Employee.Company" class="form-control" />
        <span asp-validation-for="Employee.Company" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label asp-for="Employee.City" class="control-label"></label>
        <input asp-for="Employee.City" class="form-control" />
        <span asp-validation-for="Employee.City" class="text-danger"></span>
    </div>
    <div class="form-group">
        <input type="submit" value="保存" class="btn btn-primary" />
        <a asp-page="./Employee" class="btn btn-secondary">返回列表</a>
    </div>
</form>

@section Scripts {
    @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}

4. 实现编辑员工功能 #

4.1 创建EditEmployee.cshtml.cs #

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using BookListRazor.Data;
using BookListRazor.Model;

namespace BookListRazor.Pages
{
    public class EditEmployeeModel : PageModel
    {
        private readonly IEmployeeService _employeeService;

        public EditEmployeeModel(IEmployeeService employeeService)
        {
            _employeeService = employeeService;
        }

        [BindProperty]
        public Employee Employee { get; set; }

        public async Task<IActionResult> OnGetAsync(string id)
        {
            if (id == null)
            {
                return NotFound();
            }

            Employee = await _employeeService.SingleEmployee(id);

            if (Employee == null)
            {
                return NotFound();
            }
            return Page();
        }

        public async Task<IActionResult> OnPostAsync()
        {
            if (!ModelState.IsValid)
            {
                return Page();
            }

            await _employeeService.EditEmployee(Employee.Id, Employee);
            return RedirectToPage("./Employee");
        }
    }
}

4.2 创建EditEmployee.cshtml #

@page "{id}"
@model BookListRazor.Pages.EditEmployeeModel
@{
    ViewData["Title"] = "编辑员工";
}

<h1>编辑员工</h1>

<form method="post">
    <!-- 隐藏Id字段，保证修改时能定位到正确的记录 -->
    <input type="hidden" asp-for="Employee.Id" />
    <div class="form-group">
        <label asp-for="Employee.Name" class="control-label"></label>
        <input asp-for="Employee.Name" class="form-control" />
        <span asp-validation-for="Employee.Name" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label asp-for="Employee.Department" class="control-label"></label>
        <input asp-for="Employee.Department" class="form-control" />
        <span asp-validation-for="Employee.Department" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label asp-for="Employee.Designation" class="control-label"></label>
        <input asp-for="Employee.Designation" class="form-control" />
        <span asp-validation-for="Employee.Designation" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label asp-for="Employee.Company" class="control-label"></label>
        <input asp-for="Employee.Company" class="form-control" />
        <span asp-validation-for="Employee.Company" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label asp-for="Employee.City" class="control-label"></label>
        <input asp-for="Employee.City" class="form-control" />
        <span asp-validation-for="Employee.City" class="text-danger"></span>
    </div>
    <div class="form-group">
        <input type="submit" value="保存修改" class="btn btn-primary" />
        <a asp-page="./Employee" class="btn btn-secondary">返回列表</a>
    </div>
</form>

@section Scripts {
    @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}

5. 实现删除员工功能 #

5.1 创建DeleteEmployee.cshtml.cs #

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using BookListRazor.Data;
using BookListRazor.Model;

namespace BookListRazor.Pages
{
    public class DeleteEmployeeModel : PageModel
    {
        private readonly IEmployeeService _employeeService;

        public DeleteEmployeeModel(IEmployeeService employeeService)
        {
            _employeeService = employeeService;
        }

        public Employee Employee { get; set; }

        public async Task<IActionResult> OnGetAsync(string id)
        {
            if (id == null)
            {
                return NotFound();
            }

            Employee = await _employeeService.SingleEmployee(id);

            if (Employee == null)
            {
                return NotFound();
            }
            return Page();
        }

        public async Task<IActionResult> OnPostAsync(string id)
        {
            if (id == null)
            {
                return NotFound();
            }

            await _employeeService.DeleteEmployee(id);
            return RedirectToPage("./Employee");
        }
    }
}

5.2 创建DeleteEmployee.cshtml #

@page "{id}"
@model BookListRazor.Pages.DeleteEmployeeModel
@{
    ViewData["Title"] = "删除员工";
}

<h1>删除员工</h1>

<p>确定要删除员工 <strong>@Model.Employee.Name</strong> 吗？此操作不可撤销。</p>

<form method="post">
    <input type="hidden" name="id" value="@Model.Employee.Id" />
    <input type="submit" value="删除" class="btn btn-danger" />
    <a asp-page="./Employee" class="btn btn-secondary">取消</a>
</form>

额外优化建议 #

1. 连接字符串配置：确保appsettings.json里的SqlDbContext连接字符串正确：

   "ConnectionStrings": {
     "SqlDbContext": "Server=你的数据库服务器;Database=你的数据库名;Trusted_Connection=True;MultipleActiveResultSets=true"
   }
   

2. 海量数据分页：针对你的海量数据场景，建议给GetEmployees()添加分页逻辑，避免一次性加载所有数据：

   public async Task<List<Employee>> GetEmployees(int pageNumber = 1, int pageSize = 10)
   {
       IEnumerable<Employee> employees;
       await using var conn = new SqlConnection(_configuration.Value);
       const string query = @"select * from dbo.Employees ORDER BY Name OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";
       employees = await conn.QueryAsync<Employee>(query, new { Offset = (pageNumber - 1) * pageSize, PageSize = pageSize });
       return employees.ToList();
   }
   

3. 简化连接管理：可以用await using替代手动的Open()/Close()，ASP.NET Core 3.1支持这个语法，会自动释放连接资源。

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