Entity Framework Core 3.1：从存储过程返回值（int）

Question

这会返回-1，我该如何从存储过程中获取实际的返回值？

这是我的存储过程：

ALTER PROCEDURE [Production].[Select_TicketQuantity]
    @Ticket NVARCHAR(25),
    @Reference NVARCHAR(20)
AS
BEGIN

    declare @SQL nvarchar (4000)
    SET @SQL = 'select QARTCOL as Quantidade from D805DATPOR.GCARCCR1 where NCOLGIA = ' + @Ticket + ' AND NARTCOM = ''' + @Reference + ''''
    SET @SQL = N'select CONVERT(int,Quantidade) as Quantidade from OpenQuery(MACPAC, ''' + REPLACE(@SQL, '''', '''''') + ''')'
    PRINT @SQL
    EXEC (@SQL)

END   

C# 代码

int? quantity= 0;
try
{
    quantity= await _context.Database.ExecuteSqlRawAsync("EXEC Production.Select_TicketQuantity @p0, @p1", parameters: new[] { ticket, reference});
}
catch (Exception ex)
{
    _logger.LogError($"{ex}");
    return RedirectToPage("Index");
}

- Jackal

---

请考虑标记或分享解决方案... - Diego Venâncio

Answer 1

ExecuteSqlRawAsync返回插入、更新和删除操作影响的行数(-1用于选择)。

如果您不想更改SP以引入输出参数，则可以使用SqlCommand。SqlCommand.ExecuteScalar()返回结果集中第一行的第一列：

using (var cmd = _context.Database.GetDbConnection().CreateCommand()) {
    cmd.CommandText = "[Production].[Select_TicketQuantity]";
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    if (cmd.Connection.State != System.Data.ConnectionState.Open) cmd.Connection.Open();
    cmd.Parameters.Add(new SqlParameter("Ticket", ticket));
    cmd.Parameters.Add(new SqlParameter("Reference", reference));
    quantity = (int)cmd.ExecuteScalar();
}

- AlbertK

Answer 2

您需要创建并使用一个输出参数output parameter，在您的情况下：

ALTER PROCEDURE [Production].[Select_TicketQuantity]
    @Ticket NVARCHAR(25),
    @Reference NVARCHAR(20),
    @Quantity int output
AS
BEGIN

declare @SQL nvarchar(4000) = 'select QARTCOL as Quantidade from D805DATPOR.GCARCCR1 where NCOLGIA = ''' + @Ticket + ''' AND NARTCOM = ''' + @Reference + ''''
select @Quantity = CONVERT(int, Quantidade)
  from OpenQuery(MACPAC, @SQL)

END   

然后在C#中：

int? quantity = 0;
var ticketParam = new SqlParameter("Ticket", ticket);
var referenceParam = new SqlParameter("Reference", reference);
var quantityParam = new SqlParameter("Quantity") { Direction = ParameterDirection.Output };
try
{
    await _context.Database.ExecuteSqlRawAsync("EXEC Production.Select_TicketQuantity @Ticket, @Reference, @Quantity output", new[] { ticketParam, referenceParam, quantityParam });
    quantity = Convert.ToInt32(quantityParam.Value);
}
catch (Exception ex)
{
    _logger.LogError($"{ex}");
    return RedirectToPage("Index");
}

- Ian Kemp

---

此解决方案看起来很有趣，但在转换“Quantidade”时显示无效列，并且在@SQL中期望一个字符串，来自Openquery。 - Jackal

您可以将存储过程中的文本替换为原始内容，那只是我在做一些实验。 - Ian Kemp

Answer 3

为了补充AlbertK的已有内容，但使用ExecuteSqlRaw/ExecuteSqlRawAsync。最好的方法是在您的存储过程中使用输出参数。想象一下需要从存储过程中读取StatusCode的情况：

CREATE PROCEDURE dbo.spIfUserExists (
    @Username VARCHAR(120),
    @StatusCode INT OUTPUT,
) AS
BEGIN
    IF EXISTS (SELECT * FROM dbo.[Users] WHERE Username=@Username)
        SET @StatusCode = 1;
    ELSE
        SET @StatusCode = 0;
END

你实际上没有返回任何东西，而是将一个整数(INT)分配给输出参数，该参数将在外部可用。

在你的C#代码中，你需要使用来自存储过程的输出参数名称创建一个SQLParameter对象。

SqlParameter StatusCode = new SqlParameter
{
    ParameterName = "@StatusCode",
    SqlDbType = System.Data.SqlDbType.Int,
    //Direction of this parameter is output.
    Direction = System.Data.ParameterDirection.Output
 };

最后，使用存储过程所需的参数来执行ExecuteSqlRawAsync。

其中@p0，@p1..@pn表示您的存储过程的位置参数。

string Username = "MyRandomUsername";

using (var ctx = new UsersDbContext())
{
    await ctx.Database.ExecuteSqlRawAsync(
      "spIfUserExists @p0, @StatusCode OUT",
      parameters: new object[] {
        Username,
        StatusCode,
      });
}

//StatusCode now contains the status code from the [spIfUserExists].
Console.WriteLine(Convert.ToInt32(StatusCode.Value));

输出参数不需要像普通参数一样具有位置参数，例如Username。 因此，您无需将@p[n]传递给OUTPUT参数，只需将其提供给parameters列表即可。

如果要使用ExecuteSqlRaw，编码不会有任何区别。您只需要删除await关键字以同步运行ExecuteSqlRaw。

您可以在存储过程中拥有多个具有不同数据类型的output parameter，您只需要遵循相同的模式即可。

- knoxgon

Answer 4

我知道这很老，但是我有一些存储过程使用以下作为存储过程的最后一行。

select SCOPE_IDENTITY()

我借鉴了EF Core源代码片段，并进行了修改，以允许执行ExecuteScalar。以下是该源代码的链接：

https://github.com/dotnet/efcore/blob/main/src/EFCore.Relational/Extensions/RelationalDatabaseFacadeExtensions.cs

    public static TResult ExecuteScalar<TResult>(this DbContext dbContext, string sql, params object[] parameters)
    {            
        var facadeDependencies = (IRelationalDatabaseFacadeDependencies)((IDatabaseFacadeDependenciesAccessor)dbContext.Database).Dependencies;

        var rawSqlCommand = facadeDependencies.RawSqlCommandBuilder.Build(sql, parameters);
            
        var logger = facadeDependencies.CommandLogger;

        TResult result = (TResult)rawSqlCommand.RelationalCommand
                            .ExecuteScalar(
                                new RelationalCommandParameterObject(
                                    facadeDependencies.RelationalConnection,
                                    rawSqlCommand.ParameterValues,
                                    null,
                                    dbContext,
                                    logger, true));

        return result;
    }

- goroth

Answer 5

另一种不需要修改存储过程的方法：使用DbSet的FromSqlRaw扩展方法，以便在IQueryable中获取结果。

public static IQueryable<TResult> RunSp<TResult>(this DbSet<TResult> dbSet, string storedProcedure, DbParameter[] parameters = null) where TResult : class
    {
        var paramNames = string.Empty;
        if (parameters != null)
        {
            paramNames = string.Join(",", parameters.Select(a => a.ParameterName));
        }
        return dbSet.FromSqlRaw($"EXEC {storedProcedure} {paramNames}", parameters ?? new object[]{} );
    }

你需要在你的上下文中定义一个DbSet，其结果类型应该是你想要获取的类型：

[Keyless]
    public class TestSpRecord
    {
        public int Field1 { get; set; }
        public string Field2 { get; set; }
    }

使用方法如下：

var resultList = testCtx.TestSpRecords.RunSp("EchoParams", new DbParameter[] {  new SqlParameter("@Param1",10),new SqlParameter("@Param2","text")});
        

- ilker

Answer 6

为了扩展 @AlbertK 的答案，您可以添加一个扩展到 DatabaseFacade 类型以获取不同类型的标量值返回。

请确保使用 Microsoft.Data.SqlClient 包而非 System.Data.SqlClient 作为您的 SqlParameter 参数，否则您将收到运行时异常。https://github.com/dotnet/efcore/issues/16812

public static class DatabaseFacadeExtensions
{
    public static async Task<TResult> ExecuteScalarAsync<TResult>(
        this DatabaseFacade database, 
        String commandText, 
        CommandType commandType, 
        params SqlParameter[] parameters)
    {
        TResult result;
        using (var cmd = database.GetDbConnection().CreateCommand())
        {
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;
            if (cmd.Connection.State != System.Data.ConnectionState.Open) cmd.Connection.Open();
            cmd.Parameters.AddRange(parameters);
            result = (TResult)(await cmd.ExecuteScalarAsync());
        }
        return result;
    }
}

- BusyBee

Answer 7

我通常使用这种传统方法从sp中获取单个整数值。

var commandText=  "EXEC Production.Select_TicketQuantity"+string.format(" @p0={0}, @p1={1}", ticket, reference)

using (var command = dbContext.Database.GetDbConnection().CreateCommand())
                {
                    command.CommandText = commandText;
                    dbContext.Database.OpenConnection();
                    using (var result = command.ExecuteReader())
                    {
                        if (result.HasRows == true)
                        {
                            if (result.Read())
                            {
                                var returnInteger = result.IsDBNull(0) ? 0 : result.GetInt32(0);
                                result.Close();
                                return returnInteger;
                            }

                        }

                    }
                    return 1;
                }

- audil mohammed