C#调用更新存储过程未修改数据库问题求助

First off, I feel your pain—spending a morning stuck on something that should work is the worst. Let’s break down the possible issues and fixes based on what you’ve shared:

Possible Root Causes & Fixes #

• Verify your connection string points to the correct database
  This is way more common than people think! It’s easy to accidentally point your app to a test/staging DB while you’re checking the production one, or vice versa. Double-check your config file (web.config/appsettings.json) to confirm the connection string matches the DB you’re querying in SSMS.

• Confirm the stored procedure name in code matches the actual DB procedure
  In your code, you’re using StoredProcedures.DevRequests.UpdateDevRequest, but your stored procedure is named [dbo].[sp_UpdateDevRequests]. Make sure the constant StoredProcedures.DevRequests.UpdateDevRequest resolves to the exact string "sp_UpdateDevRequests" (including the schema if needed, like "dbo.sp_UpdateDevRequests"). A mismatched name could mean you’re executing a different (possibly outdated) procedure that returns 1 but doesn’t update the right table.

• Add logging to confirm parameter values at execution time
  Even though you think the parameters are correct, it’s worth logging them right before executing the command to rule out any unexpected value changes (e.g., model binding issues in the MVC action). Add something like this:

  // Log these values (use your app's logging framework or even Debug.WriteLine)
  Debug.WriteLine($"Executing UpdateDevRequest with: changeID={changeID}, evaluator={evaluator}, priority={priority}, status={status}");
  int result = cmd.ExecuteNonQuery();
  Debug.WriteLine($"Rows affected: {result}");
  

  This will confirm that the values reaching the SqlCommand are exactly what you expect.

• Use using statements for connection and command objects
  Your current code manually opens/closes the connection, which can lead to connection leaks or unexpected behavior if an exception occurs. Wrapping your SqlConnection and SqlCommand in using blocks ensures proper resource disposal and can resolve subtle issues:

  [HttpPost, ValidateAntiForgeryToken]
  public ActionResult UpdateDevelopmentRequest(int changeID, string evaluator, int priority, string status)
  {
      using (SqlConnection conn = new SqlConnection(yourConnectionString)) // Replace with your actual connection string
      using (SqlCommand cmd = new SqlCommand(StoredProcedures.DevRequests.UpdateDevRequest, conn))
      {
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.Parameters.Add("@changeID", SqlDbType.Int).Value = changeID;
          cmd.Parameters.Add("@evaluator", SqlDbType.NVarChar, 30).Value = evaluator;
          cmd.Parameters.Add("@priority", SqlDbType.Int).Value = priority;
          cmd.Parameters.Add("@status", SqlDbType.NVarChar, 15).Value = status;
  
          conn.Open();
          int rowsAffected = cmd.ExecuteNonQuery();
          Debug.WriteLine($"Updated {rowsAffected} row(s)");
      }
      return RedirectToAction("DevelopmentRequests");
  }
  

• Check for schema mismatches in the stored procedure
  Ensure the it_ChangeRequest table in your DB has the exact column names: Evaluator, Status, Priority, and ChangeID. A typo (e.g., Priority vs Prioritiy) would cause the update to run without errors but not modify the intended columns.

Quick Test to Narrow It Down #

Try modifying your action to execute the raw update query directly (bypassing the stored procedure) with the same parameters. If this works, the issue is definitely with how the stored procedure is being called or defined. If it still doesn’t work, you can rule out the stored procedure as the problem and focus on the connection/parameter binding.

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