ADO.NET中如何将传递的外键通过HiddenFor提交至数据库？

1. 补充AddContent的GET控制器方法 #

你目前只有POST方法，需要先添加GET方法接收URL中的PPuid，并将其赋值给Content模型的CPuid字段，再传递给视图：

[HttpGet]
public ActionResult AddContent(Guid id)
{
    // id就是URL传递过来的Packages.PPuid
    var content = new Content
    {
        CPuid = id // 直接把PPuid赋值给Content的外键CPuid
    };
    return View(content);
}

2. 完善AddContent视图 #

视图里的@Html.HiddenFor(m => m.CPuid)会自动绑定GET方法传递过来的CPuid值，同时需要添加CAmount和CNotes的输入字段，确保表单能收集完整数据：

@model Receiving.Models.Content
@{
    Layout = "~/Views/Shared/_Layout.cshtml";
}

@using (Html.BeginForm("AddContent", "Content", FormMethod.Post, new { @class = "form-horizontal" }))
{
    @Html.AntiForgeryToken() <!-- 防止跨站请求伪造 -->
    
    <!-- 自动填充从GET方法传递过来的CPuid值 -->
    @Html.HiddenFor(m => m.CPuid)
    
    <div class="form-group">
        @Html.LabelFor(m => m.CAmount, new { @class = "control-label col-md-2" })
        <div class="col-md-10">
            @Html.TextBoxFor(m => m.CAmount, new { @class = "form-control" })
            @Html.ValidationMessageFor(m => m.CAmount)
        </div>
    </div>
    
    <div class="form-group">
        @Html.LabelFor(m => m.CNotes, new { @class = "control-label col-md-2" })
        <div class="col-md-10">
            @Html.TextAreaFor(m => m.CNotes, new { @class = "form-control", rows = 3 })
            @Html.ValidationMessageFor(m => m.CNotes)
        </div>
    </div>
    
    <div class="form-group">
        <div class="col-md-offset-2 col-md-10">
            <input type="submit" value="添加内容" class="btn btn-default" />
        </div>
    </div>
}

3. 修改AddContent的POST方法 #

POST方法不需要单独接收id参数，表单提交的Content对象已包含CPuid值（来自隐藏字段），直接使用即可：

[HttpPost]
[ValidateAntiForgeryToken] // 和视图的AntiForgeryToken对应
public ActionResult AddContent(Content content)
{
    if (!ModelState.IsValid) // 先验证模型合法性
    {
        return View(content);
    }
    
    using (SqlConnection con = new SqlConnection(connectonString))
    {
        using (SqlCommand cmd = new SqlCommand("POST_Content", con))
        {
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@CPuid", content.CPuid);
            cmd.Parameters.AddWithValue("@CAmount", content.CAmount);
            cmd.Parameters.AddWithValue("@CNotes", content.CNotes);

            con.Open();
            cmd.ExecuteNonQuery();
        } // using会自动关闭连接，无需手动调用con.Close()
    }
    
    return RedirectToAction("Index", "Shipment");
}

4. 修复SearchByTrackingNumber的SQL注入漏洞 #

你当前的SQL语句用字符串拼接TrackingNumber，存在严重注入风险，必须改成参数化查询：

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult SearchByTrackingNumber(string? TrackingNumber)
{
    List<Shipment> shipments = new List<Shipment>();
    using(SqlConnection con = new SqlConnection(connectonString))
    {
        con.Open();
        // 参数化查询避免SQL注入
        string sql = @"select sSUID, packages.pPUID, sFrom, sCompanyName, sFName, sMName, sLName, 
                          sAddress1, sAddress2, sCity, sState, sPostalCode, sCountry 
                      from shipments inner join packages on shipments.sSUID = packages.pSUID 
                      where pTrackingNumber like @TrackingNumber 
                      order by sDateReceived";
        SqlCommand cmd = new SqlCommand(sql, con);
        // 构造模糊查询参数值
        cmd.Parameters.AddWithValue("@TrackingNumber", $"%{TrackingNumber}%");

        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                Shipment shipment = new Shipment();
                shipment.SSuid = Guid.Parse(reader["sSUID"].ToString());
                shipment.PPuid = Guid.Parse(reader["pPUID"].ToString());
                shipment.SCompanyName = reader["sCompanyName"].ToString();
                shipment.SFname = reader["sFName"].ToString();
                shipment.SMname = reader["sMName"].ToString();
                shipment.SLname = reader["sLName"].ToString();
                shipment.SFrom = reader["sFrom"].ToString();
                shipment.SAddress1 = reader["sAddress1"].ToString();
                shipment.SAddress2 = reader["sAddress2"].ToString();
                shipment.SCity = reader["sCity"].ToString();
                shipment.SState = reader["sState"].ToString();                           
                shipment.SPostalCode = reader["sPostalCode"].ToString();
                shipment.SCountry = reader["sCountry"].ToString();
                
                shipments.Add(shipment);
            }
        } // reader的using会自动关闭，con的using会自动关闭连接
    }

    return View(shipments); 
}

核心逻辑说明 #

• GET方法接收URL中的PPuid并赋值给Content模型，视图的HiddenFor会自动填充该值。
• 表单提交时，隐藏字段的CPuid会和其他字段一起提交到POST方法，直接存入数据库即可。
• 参数化查询是Web应用安全的基本要求，必须替换字符串拼接的SQL写法。

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