ASP.NET Core调用SQL Server存储过程无变更问题求助

问题背景 #

基于MVVM架构和Razor Pages构建ASP.NET Core Web应用，编辑模态表单提交后，通过ExecuteSqlRaw调用存储过程更新数据。SQL Server Profiler显示存储过程已命中服务器且返回正常结果，但数据库无数据变更；直接执行Profiler捕获的语句可正常修改数据。原计划用LINQ实现更新，但因表上存在20年历史的审计触发器，直接编辑会抛出异常，故改用存储方案。

处理方法代码 #

public IActionResult OnPostChangePart([FromBody] PartNum updatedPart)
{
    
    var partRecord = _context.tblPartsInventory
        .FirstOrDefault(x => x.ID == updatedPart.ID);

    if (partRecord != null )
    {
        string sql = "[dbo].[usp_UpdatetblPartsInventoryBurkeSite] @ID, @txtPart, @txtColor, @Inventory, @txtGroup, @Description, @BinLocation, @Skid";

        var parameters = new List<SqlParameter>()
        {
            new SqlParameter {ParameterName = "@ID", Value = partRecord.ID, SqlDbType = SqlDbType.Int},
            new SqlParameter {ParameterName = "@txtPart", Value = partRecord.txtPart, IsNullable=true, SqlDbType = SqlDbType.NVarChar},
            new SqlParameter {ParameterName = "@txtColor", Value = partRecord.txtColor != null ? partRecord.txtColor : DBNull.Value, IsNullable=true, SqlDbType = SqlDbType.NVarChar},
            new SqlParameter {ParameterName = "@Inventory", Value= partRecord.InStock, IsNullable=true, SqlDbType = SqlDbType.Int},
            new SqlParameter {ParameterName = "@txtGroup", Value= partRecord.txtGroup != null ? partRecord.txtGroup : DBNull.Value, IsNullable=true, SqlDbType = SqlDbType.NVarChar},
            new SqlParameter {ParameterName = "@Description", Value= partRecord.Description != null ? partRecord.Description : DBNull.Value, IsNullable=true, SqlDbType = SqlDbType.NVarChar},
            new SqlParameter {ParameterName = "@BinLocation", Value= partRecord.BinLocation != null ? partRecord.BinLocation : DBNull.Value, IsNullable=true, SqlDbType = SqlDbType.NVarChar},
            new SqlParameter {ParameterName = "@Skid", Value= partRecord.Skid != null ? partRecord.Skid : DBNull.Value, IsNullable=true, SqlDbType = SqlDbType.NVarChar}
        };
        
        _context.Database.ExecuteSqlRaw(sql, parameters.ToArray());
    }
    // Redirect to the main view page
    return Redirect("/InventoryViewer/InventoryViewer");
}

SQL Server Profiler捕获的执行语句 #

exec sp_executesql N'[dbo].[usp_UpdatetblPartsInventoryBurkeSite] @ID, @txtPart, @txtColor, @Inventory, @txtGroup, @Description, @BinLocation, @Skid',N'@ID int,@txtPart nvarchar(8),@txtColor nvarchar(1),@Inventory int,@txtGroup nvarchar(4000),@Description nvarchar(14),@BinLocation nvarchar(7),@Skid nvarchar(4000)',@ID=144739,@txtPart=N'000-0001',@txtColor=N'0',@Inventory=2247,@txtGroup=NULL,@Description=N'COMMON CASTING',@BinLocation=N'20-B12A',@Skid=NULL

核心问题分析 #

代码存在致命逻辑错误：参数使用的是从数据库查询出的partRecord旧值，而非前端提交的updatedPart新值。这导致存储过程执行时，传入的是数据库原本就存在的数据，自然不会产生任何变更。直接执行Profiler语句能生效，是因为你手动确认过参数为正确新值，但代码并未传递新值。

修正后的代码 #

将参数来源替换为updatedPart，同时保留必要的空值处理：

public IActionResult OnPostChangePart([FromBody] PartNum updatedPart)
{
    if (updatedPart == null || updatedPart.ID <= 0)
    {
        return BadRequest("无效的更新参数");
    }

    string sql = "[dbo].[usp_UpdatetblPartsInventoryBurkeSite] @ID, @txtPart, @txtColor, @Inventory, @txtGroup, @Description, @BinLocation, @Skid";

    var parameters = new List<SqlParameter>()
    {
        new SqlParameter {ParameterName = "@ID", Value = updatedPart.ID, SqlDbType = SqlDbType.Int},
        new SqlParameter {ParameterName = "@txtPart", Value = updatedPart.txtPart ?? DBNull.Value, IsNullable=true, SqlDbType = SqlDbType.NVarChar},
        new SqlParameter {ParameterName = "@txtColor", Value = updatedPart.txtColor ?? DBNull.Value, IsNullable=true, SqlDbType = SqlDbType.NVarChar},
        new SqlParameter {ParameterName = "@Inventory", Value= updatedPart.InStock ?? (object)DBNull.Value, IsNullable=true, SqlDbType = SqlDbType.Int},
        new SqlParameter {ParameterName = "@txtGroup", Value= updatedPart.txtGroup ?? DBNull.Value, IsNullable=true, SqlDbType = SqlDbType.NVarChar},
        new SqlParameter {ParameterName = "@Description", Value= updatedPart.Description ?? DBNull.Value, IsNullable=true, SqlDbType = SqlDbType.NVarChar},
        new SqlParameter {ParameterName = "@BinLocation", Value= updatedPart.BinLocation ?? DBNull.Value, IsNullable=true, SqlDbType = SqlDbType.NVarChar},
        new SqlParameter {ParameterName = "@Skid", Value= updatedPart.Skid ?? DBNull.Value, IsNullable=true, SqlDbType = SqlDbType.NVarChar}
    };
    
    _context.Database.ExecuteSqlRaw(sql, parameters.ToArray());

    return Redirect("/InventoryViewer/InventoryViewer");
}

额外排查点（若修正后仍有问题） #

• 事务提交问题：检查EF Core事务配置，确保ExecuteSqlRaw操作被正确提交，必要时可显式调用_context.SaveChanges()。
• 存储过程逻辑：确认存储过程内部无跳过更新的判断条件（如权限校验、行版本号不匹配等），可在存储过程中添加日志输出排查。
• 实体跟踪干扰：若必须查询partRecord，使用AsNoTracking()避免EF Core跟踪实体，防止缓存覆盖：

  var partRecord = _context.tblPartsInventory.AsNoTracking()
      .FirstOrDefault(x => x.ID == updatedPart.ID);
  

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