如何将用户输入作为存储过程 C#、.NET 4.8 ASP.NET MVC 和 SQL Server 的变量传递

问：

我是很新的，想把这个项目作为一种实践，但我一辈子都无法弄清楚这一点。该项目是使用 C#、.NET Framework ASP.NET MVC 和 Microsoft SQL Server 生成的。

项目应该能够接受用户输入（“OrderNumber”）并将其作为变量传递给存储过程。该过程检查数据库中是否存在具有该订单号的表中的行。如果未找到，则会引发错误，如果找到错误，则会检查另一列名为“ScanStatus”。如果为 1，则给出错误“已扫描”，如果为 0，则更新为 1。我不能让它为我的生活工作，但这是下面的代码。任何帮助将不胜感激。

这是控制器

using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Data;
using WebApplication1.Models;

namespace WebApplication1.Controllers
{
    public class ScanController : Controller
    {
        private static void UpdateScanStatus(bool Scan_Status, string connectionString)
        {
            //Update the Scan_Status Column 
            string commandText = "UPDATE SpeedPassBd.Orders SET Scan_Status = @scan_status" + "WHERE Order_Number = @Onumber;";
            string xmlData = "<Orders><ID>''</ID><Order_Number>@Order_Number</Order_Number><Customer_Name>''</Customer_Name><Scan_Status>@scan_status<Scan_Status><QR_Number>''</QR_Number></Orders>";

            using (SqlConnection connection = new SqlConnection("Data Source=SBSQL;User ID=SpeedPass;Password=********;Connect Timeout=30;Encrypt=False;Trust Server Certificate=False;Application Intent=ReadWrite;Multi Subnet Failover=False"))
            {
                SqlCommand command = new SqlCommand(commandText, connection);
                command.Parameters.Add("@scan_status", SqlDbType.Bit);
                command.Parameters[@scan_status].Value = Scan_Status;

                //Use AddWithValue to assign OrderNumber
                //SQL server will implicitly convert strings to XML.
                command.Parameters.AddWithValue("@Order_Number", xmlData);

                try
                {
                    connection.Open();
                    Int32 rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine("RowsAffected: {0}", rowsAffected);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
        }
        
    }
}

这是模型类：

using System; 
using System.Collections.Generic; 
using System.ComponentModel.DataAnnotations; 
using System.Linq; 
using System.Web; 
  
namespace SpeedPass.Models 
{ 
     public class Orders 
     { 
         [Key] 
         public string Order_Number { get; set; } 
  
         [Required] 
         public string Customer_Number { get; set; } 
  
         [Required] 
         public bool Scan_Status { get; set; } 
     } 
}

这是 DbContext

using System; 
using SpeedPass.Models; 
using System.Collections.Generic; 
using System.Data.Entity; 
using System.Linq; 
using System.Web; 
 
namespace SpeedPass.Data 
{ 
     public class SpeedPassDbContext : DbContext 
     { 
         public SpeedPassDbContext() : base("Data Source=SBSQL\\SB_SANDBOX_DB;Initial Catalog=testDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;Trust Server Certificate=False;Application Intent=ReadWrite;Multi Subnet Failover=False") 
         { 
         } 

         public DbSet<Orders> Orders { get; set; } 
         public DbSet<Customers> Customers { get; set; } 
     } 
} 

这是存储过程

Use [testDB]
GO

SET ANSI_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UpdateScanStatus]
    @OrderNumber varchar(8)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM Orders WHERE Order_Number = @OrderNumber)
Begin
    DECLARE @ScanStatus BIT;
    SELECT @ScanStatus = ScanStatus
    FROM Orders
    WHERE Order_Number = @Order_Number

    IF @ScanStatus = 1
    BEGIN
        RAISERROR('Order is already scanned!', 23,1);
    END
    ELSE
    BEGIN
        UPDATE Orders
        SET Scan_Status = 1
        WHERE Order_Number = @OrderNumber
        
        PRINT 'Successful!';
        END
    END
    ELSE
    BEGIN

        RAISERROR('Order does not exist!'22,1);
    END
END;

我想在这里提供更多信息，尽管作为新手，我不知道该探索什么。我查看了 Microsoft 文档、C# 角和其他一些使用不同版本框架以及使用脚手架工具的网站。到目前为止，几乎都是反复试验。存储过程用于查找和更新数据库列以及错误处理。