时间戳

首页 > 解决方案 > 如何在 ASP.NET Core MVC 中使用 ADO.NET 向存储过程添加参数？

c# - 如何在 ASP.NET Core MVC 中使用 ADO.NET 向存储过程添加参数？

问题描述

我想在 ASP.NET Core MVC 中执行具有 ADO.NET 输出参数的存储过程。但是我收到一个错误，说我的存储过程需要一个我已经设置的参数。

我的存储过程是这样的：

ALTER PROCEDURE [dbo].[spSearch] 
    @Notes nvarchar(max) = NULL, 
    @StartDate datetime = NULL, 
    @EndDate datetime = NULL,
    @UserId nvarchar(450),
    @Result money OUTPUT
AS 
BEGIN
    IF (@StartDate > @EndDate) 
    BEGIN 
        RAISERROR (N'Start date cannot be greater than end date',16,1) 
    END
    ELSE
    BEGIN 
        IF (@Notes IS NULL OR @Notes = '') 
        BEGIN 
            SELECT *
            FROM Expenses
            WHERE UserId = @UserId 
              AND [date] BETWEEN @StartDate AND @EndDate 
            ORDER BY [date] DESC 

            IF (@@ROWCOUNT = 0) 
            BEGIN 
                RAISERROR (N'No data found according to date match', 16, 1) 
            END 

            SELECT @Result = SUM(amount) 
            FROM Expenses 
            WHERE UserId = @UserId 
              AND [date] BETWEEN @StartDate AND @EndDate 
        END
        ELSE 
        BEGIN
            IF (@StartDate IS NULL OR @StartDate = '' OR @EndDate IS NULL OR @EndDate = '')
            BEGIN
                SELECT * 
                FROM expenses 
                WHERE UserId = @UserId 
                  AND Notes LIKE '%' + @notes + '%' 
                ORDER BY [date] DESC 

                SELECT @Result = SUM(amount) 
                FROM expenses 
                WHERE UserId = @UserId 
                  AND notes IN (SELECT notes 
                                FROM expenses 
                                WHERE notes LIKE '%' + @Notes + '%') 
            END
            ELSE
            BEGIN
                SELECT *
                FROM   Expenses  
                WHERE UserId=@UserId and notes LIKE '%' + @notes + '%'   and [date] BETWEEN @StartDate AND @EndDate 
                                 ORDER  BY [date] DESC 
                        IF( @@ROWCOUNT = 0 ) 
                        BEGIN 
                        RAISERROR(N'No matching record found',16,1) 
                        END 

                        SELECT @Result = Sum(amount) 
                        FROM   Expenses 
                        WHERE  Notes IN(SELECT Notes 
                              FROM   Expenses 
                             WHERE UserId=@UserId and  notes LIKE '%' + @Notes + '%') 
                        AND [date] BETWEEN @StartDate AND @EndDate 
            END
       END
    END 
 END

我的方法是这样的：

public IQueryable<Expenses> SearchExpenses(string UserId, string Notes, DateTime startFrom, DateTime endTo)
    {
        List<Expenses> expenses = new List<Expenses>();
        using (DALC.GetConnection())
        {
            DALC.Command("[spSearch]");
            DALC.cmd.Parameters.Add("@Notes", SqlDbType.NVarChar).Value = Notes;
            DALC.cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = startFrom;
            DALC.cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = endTo;
            DALC.cmd.Parameters.Add("@UserId", SqlDbType.NVarChar).Value = UserId;//I set my sp paremeter here but why error?
            //Is This the correct way to set an output parameter?
            SqlParameter howMuch = new SqlParameter("@Result", SqlDbType.Money);
            howMuch.Direction = ParameterDirection.Output;

            DALC.cmd.Parameters.Add(howMuch);
            DALC.cmd.ExecuteNonQuery();

            var result = Convert.ToDecimal(DALC.cmd.Parameters["@Result"]);

            using (SqlDataReader reader = DALC.cmd.ExecuteReader())
            {
                reader.Read();
                Expenses expense = new Expenses
                {
                    Id = Convert.ToInt32(reader["Id"]),
                    Desription = reader["Notes"].ToString(),
                    Date = Convert.ToDateTime(reader["Date"]),
                    IsCash = Convert.ToBoolean(reader["IsCash"]),
                    IsCard = Convert.ToBoolean(reader["IsCash"]),
                    SearchResultOut = result
                };
                expenses.Add(expense);
            }
        }
        return expenses.AsQueryable();
    }

错误：

System.Data.SqlClient.SqlException：“过程或函数“spSearch”需要参数“@UserId”，但未提供。

我的控制器：

[HttpPost]
    public IActionResult Search(string UserId, string notes, DateTime startFrom, DateTime endTo)
    {
        UserId = GetUserId();
        context.SearchExpenses(UserId,notes,startFrom,endTo);            
        return View();
    }

标签： c#sqlado.net

解决方案

考虑以相同的方式创建所有 sql 参数。

对于输出参数，您可以通过创建 sql 参数对象并设置方向来正确执行此操作。

您应该尝试对其他参数执行相同的操作（当然不设置方向）

调试

您应该在执行之前放置一个断点，并在 cmd 对象的参数集合中添加一个监视以查看它的样子。

还要验证 cmd 对象是否属于存储过程类型。

如果全部检查，打开 sql profiler，您应该能够看到通过 SQL profiler 传入的内容，并获取对存储过程的所有参数的实际调用。

接听实际电话并在 SSMS 上运行，看看它做了什么。

这应该可以帮助您弄清楚。

此外，当您返回结果时，语法为 var result = Convert.ToDecimal(DALC.cmd.Parameters["@Result"].Value);

推荐阅读