.NET Core调用存储过程遇SqlException：找不到存储过程问题排查

我在.NET Core项目中通过ADO.NET调用带参数的存储过程FindRecipesByTitle时，抛出如下异常：

Microsoft.Data.SqlClient.SqlException (0x80131904): Could not find stored procedure 'EXEC FindRecipesByTitle'.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)

存储过程代码 #

CREATE procedure [dbo].[FindRecipesByTitle]
    @title nvarchar(max)
AS
    SET NOCOUNT ON;
BEGIN
    SELECT
        Recipes.Id,
        Recipes.Name AS 'RecipeName',
        Recipes.PostedById,
        CASE
            WHEN Recipes.DishTypeId = 1 THEN 'Appetizers, Beverages'
            WHEN Recipes.DishTypeId = 2 THEN 'Soups, Salads'
            WHEN Recipes.DishTypeId = 3 THEN 'Vegetables'
            ELSE Recipes.DishTypeId 
        END AS DishTypeId,
        CASE
            WHEN Recipes.VegNonVeg = 0 THEN 'Vegetarian'
            WHEN Recipes.VegNonVeg = 1 THEN 'Non Vegetarian'
            ELSE Recipes.VegNonVeg 
        END AS VegNonVeg,
        Recipes.EstimatedTimeInMinutes,
        Recipes.Ingredients,
        Recipes.Description,
        Recipes.ImagePath,
        Recipes.CreatedAt,
        DishTypes.Name AS 'DishName'
    FROM
        [Recipes]
    JOIN
        [DishTypes] ON Recipes.DishTypeId = DishTypes.Id
    WHERE
        Recipes.Name LIKE '%' + @title + '%'
END

调用代码 #

public async Task<List<RecipeViewModel>> GetRecipeByTitleFromSP(string title)
{
    List<RecipeViewModel> lstRcpVM = new List<RecipeViewModel>();

    using (var cmd=_dbContext.Database.GetDbConnection().CreateCommand())
    {
        cmd.CommandText = $@"EXEC FindRecipesByTitle";
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add(new SqlParameter("@title", title));

        _dbContext.Database.OpenConnection();

        using (var reader = cmd.ExecuteReader())
        {
            while(reader.Read())
            {
                lstRcpVM.Add(new RecipeViewModel
                {
                    RecipeName = reader.GetString("RecipeName"),
                    PostedById = reader.GetString("PostedById"),
                    DishTypeId = reader.GetString("DishTypeId"),
                    VegNonVeg = reader.GetString("VegNonVeg"),
                    EstimatedTimeInMinutes = reader.GetInt32("EstimatedTimeInMinutes"),
                    Ingredients = reader.GetString("Ingredients"),
                    Description = reader.GetString("Description"),
                    ImagePath = reader.GetString("ImagePath"),
                    CreatedAt = Convert.ToDateTime(reader["CreatedAt"]),
                    DishName = reader.GetString("DishName")
                });
            }
        }

        _dbContext.Database.CloseConnection();
    }

    return lstRcpVM;
}

已确认存储过程存在，但运行代码仍报错，求错误原因及解决方法。

错误原因 #

当CommandType设置为CommandType.StoredProcedure时，CommandText只需填写存储过程的纯名称，不需要添加EXEC关键字。当前代码中cmd.CommandText = "EXEC FindRecipesByTitle"，数据库会把整个字符串EXEC FindRecipesByTitle当作存储过程名称去查找，自然找不到对应存储过程，因此抛出异常。

解决方法 #

修改调用代码中的CommandText，移除EXEC关键字，仅保留存储过程名称：

cmd.CommandText = "FindRecipesByTitle";
cmd.CommandType = CommandType.StoredProcedure;

额外优化建议 #

• 方法标记为async，建议使用异步数据库方法以提升性能，比如：

  await _dbContext.Database.OpenConnectionAsync();
  using (var reader = await cmd.ExecuteReaderAsync())
  {
      while(await reader.ReadAsync())
      {
          // 赋值逻辑保持不变
      }
  }
  await _dbContext.Database.CloseConnectionAsync();
  

• 检查PostedById的类型：如果数据库中该字段是整数类型，reader.GetString("PostedById")会抛出类型转换异常，需根据实际类型调整为reader.GetInt32("PostedById")或对应类型的读取方法。

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