如何在Razor Pages中将HTML表格薪资数据保存至SQL数据库

1. 确保前端表单能正确绑定后端模型 #

要提交表格中的多条数据，先定义对应的数据模型：

public class SalaryCalculationResult
{
    public int EmployeeId { get; set; }
    public string EmployeeName { get; set; }
    public decimal BasicSalary { get; set; }
    public decimal Bonus { get; set; }
    public decimal TotalSalary { get; set; }
}

在PageModel中声明列表属性接收提交数据：

[BindProperty]
public List<SalaryCalculationResult> SalaryResults { get; set; }

前端表格包裹在<form method="post">中，表单控件的name属性按列表名[索引].属性名格式命名：

<form method="post">
    <table>
        <thead>
            <tr>
                <th>员工ID</th>
                <th>姓名</th>
                <th>基本工资</th>
                <th>奖金</th>
                <th>总薪资</th>
            </tr>
        </thead>
        <tbody>
            @for (int i = 0; i < Model.SalaryResults.Count; i++)
            {
                <tr>
                    <td>
                        <input type="hidden" asp-for="SalaryResults[i].EmployeeId" />
                        @Model.SalaryResults[i].EmployeeId
                    </td>
                    <td>
                        <input type="hidden" asp-for="SalaryResults[i].EmployeeName" />
                        @Model.SalaryResults[i].EmployeeName
                    </td>
                    <td>
                        <input type="hidden" asp-for="SalaryResults[i].BasicSalary" />
                        @Model.SalaryResults[i].BasicSalary
                    </td>
                    <td>
                        <input type="hidden" asp-for="SalaryResults[i].Bonus" />
                        @Model.SalaryResults[i].Bonus
                    </td>
                    <td>
                        <input type="hidden" asp-for="SalaryResults[i].TotalSalary" />
                        @Model.SalaryResults[i].TotalSalary
                    </td>
                </tr>
            }
        </tbody>
    </table>
    <button type="submit">保存薪资数据</button>
</form>

如果需要用户编辑数据，可将hidden换成对应输入类型（如number、text）。

2. 在OnPostAsync中调用存储过程插入数据 #

方式一：使用SqlConnection直接操作 #

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

    string connectionString = Configuration.GetConnectionString("YourDbConnection");
    
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        await conn.OpenAsync();
        
        foreach (var result in SalaryResults)
        {
            using (SqlCommand cmd = new SqlCommand("SaveSalaryResult", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                
                cmd.Parameters.AddWithValue("@EmployeeId", result.EmployeeId);
                cmd.Parameters.AddWithValue("@EmployeeName", result.EmployeeName);
                cmd.Parameters.AddWithValue("@BasicSalary", result.BasicSalary);
                cmd.Parameters.AddWithValue("@Bonus", result.Bonus);
                cmd.Parameters.AddWithValue("@TotalSalary", result.TotalSalary);
                
                await cmd.ExecuteNonQueryAsync();
            }
        }
    }

    return RedirectToPage("/Success");
}

方式二：使用Entity Framework Core #

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

    foreach (var result in SalaryResults)
    {
        await _context.Database.ExecuteSqlRawAsync(
            "EXEC SaveSalaryResult @EmployeeId = {0}, @EmployeeName = {1}, @BasicSalary = {2}, @Bonus = {3}, @TotalSalary = {4}",
            result.EmployeeId, result.EmployeeName, result.BasicSalary, result.Bonus, result.TotalSalary
        );
    }

    await _context.SaveChangesAsync();
    return RedirectToPage("/Success");
}

3. 常见错误排查要点 #

• 检查ModelState.IsValid：绑定失败时，可在页面添加@Html.ValidationSummary(true)显示错误。
• 确认存储过程参数名、类型与代码完全匹配，避免大小写或类型不兼容问题。
• 验证数据库连接字符串正确性，确保应用有数据库访问权限。
• 添加异常捕获排查问题：

try
{
    // 数据库操作代码
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
    ModelState.AddModelError(string.Empty, "保存失败：" + ex.Message);
    return Page();
}

4. 存储过程示例 #

确保存储过程逻辑正确：

CREATE PROCEDURE SaveSalaryResult
    @EmployeeId INT,
    @EmployeeName NVARCHAR(100),
    @BasicSalary DECIMAL(18,2),
    @Bonus DECIMAL(18,2),
    @TotalSalary DECIMAL(18,2)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO SavedSalaryResults (EmployeeId, EmployeeName, BasicSalary, Bonus, TotalSalary)
    VALUES (@EmployeeId, @EmployeeName, @BasicSalary, @Bonus, @TotalSalary)
END

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