使用ASP Core MVC与SQL存储过程实现多对多跟踪订单表编辑功能

1. 编辑存储过程代码 #

存储过程命名为UpdateTracking，逻辑覆盖：校验跟踪单存在性、更新订单的订单号/司机信息、更新跟踪单日期、避免订单号重复冲突，代码如下：

ALTER PROCEDURE [dbo].[UpdateTracking]
@TrackingID int,
@NewOrderNo int,
@NewDriverID int,
@NewTrackingDate date
AS
BEGIN
SET NOCOUNT ON;
DECLARE @OldOrderID int
-- 校验跟踪单是否存在，并获取原关联的订单ID
SELECT @OldOrderID = tor.OrderID 
FROM Tracking_Order tor
INNER JOIN Tracking t ON tor.TrackingID = t.TrackingID
WHERE tor.TrackingID = @TrackingID

IF @OldOrderID IS NULL
BEGIN
	RETURN -1 -- 跟踪单不存在，返回错误码
END

-- 校验新订单号是否被其他订单占用
IF EXISTS(SELECT 1 FROM Orders WHERE OrderNo = @NewOrderNo AND OrderID != @OldOrderID)
BEGIN
	RETURN -2 -- 订单号重复，返回错误码
END

-- 更新订单的订单号和司机信息
UPDATE Orders 
SET OrderNo = @NewOrderNo, DriverID = @NewDriverID
WHERE OrderID = @OldOrderID

-- 更新跟踪单日期
UPDATE Tracking
SET TDate = @NewTrackingDate
WHERE TrackingID = @TrackingID

RETURN 1 -- 更新成功
END

2. Edit控制器POST方法代码 #

首先修正方法参数，绑定update视图模型的字段，逻辑覆盖：参数校验、订单号重复校验、调用存储过程、返回对应提示，代码如下：

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit(int id, [Bind("Id,OrderNo,Tdate,DriverId")] update updateModel)
{
    // 确保路由id和视图模型的跟踪单id一致
    if (id != updateModel.Id)
    {
        return NotFound();
    }

    // 获取当前跟踪单关联的原订单信息
    var currentOrder = (from tor in _context.TrackingOrders
                       join o in _context.Orders on tor.OrderId equals o.OrderId
                       where tor.TrackingId == id
                       select o).FirstOrDefault();
    if (currentOrder == null)
    {
        ViewBag.errorMsg = "待编辑的跟踪单不存在";
        ViewData["DriverId"] = new SelectList(_context.Drivers, "DriverId", "DriverName");
        return View(updateModel);
    }
    // 校验订单号是否被其他订单占用
    var duplicateOrder = (from o in _context.Orders
                         where o.OrderNo == updateModel.OrderNo && o.OrderId != currentOrder.OrderId
                         select o).Any();

    if (ModelState.IsValid)
    {
        if (duplicateOrder)
        {
            ViewBag.Duplicate = "订单号已被其他订单占用";
        }
        else
        {
            // 调用存储过程执行更新
            var execResult = _context.Database.ExecuteSqlInterpolated($"UpdateTracking @TrackingID={id},@NewOrderNo={updateModel.OrderNo},@NewDriverID={updateModel.DriverId},@NewTrackingDate={updateModel.Tdate}");
            
            if (execResult > 0)
            {
                ViewBag.successMsg = "跟踪单更新成功";
                ModelState.Clear();
            }
            else
            {
                ViewBag.errorMsg = "更新失败，请重试";
            }
        }
    }
    // 填充司机下拉框选项
    ViewData["DriverId"] = new SelectList(_context.Drivers, "DriverId", "DriverName", updateModel.DriverId);
    return View(updateModel);
}

补充说明 #

• 你的Tracking模型中存在冗余字段OrderID和拼写错误的DiverID，这两个字段可以直接删除：订单与跟踪单的多对多关联已经通过Tracking_Orders表维护，司机信息存储在订单表中，冗余字段容易造成数据不一致
• 前端编辑页面需要将当前编辑的跟踪单ID赋值给update视图模型的Id字段，确保提交时参数正确

内容的提问来源于stack exchange，提问作者Ibrahim Al-Habian