使用EF Core如何获取存储过程的输出参数值？

Question

我正在使用Asp.net核心和EF核心进行应用程序开发。基本上，我想从单个存储过程中获取多个结果集。过去两天一直在搜索，但没有找到相关内容。尝试找出解决方案来解决这个问题。

这是我的存储过程：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_CustomerAll_sel]
    @SomeOutput int OUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * 
    FROM [dbo].[Customer]

    SELECT @SomeOutput = @@rowcount + 25 --This number 25 is a variable from a complex query but always an integer
END

我在那张表里有两条记录，因此基本上它应该返回一个客户类型的表格，输出参数应该返回27。

现在从我的 .net 代码中，我到目前为止尝试过的是：

[HttpGet]
public async Task<Tuple<IEnumerable<Customer>, int>> GetAllCustomer()
{
    var votesParam = new SqlParameter
            {
                ParameterName = "SomeOutput",
                Value = -1,
                Direction = ParameterDirection.Output
            };

    var y = await _customerContext.Customers.FromSql("usp_CustomerAll_sel @SomeOutput out", votesParam).ToArrayAsync();

    return new Tuple<IEnumerable<Customer>, int>(y, (int)votesParam.Value);
}

上面的代码返回了一个列表，但我没有从数据库中获取到输出参数的值。(int)votesParam.Value 显示为 null。

现在如果我使用 ExecuteNonQueryAsync，那么我可以获取到输出参数，但是没有实际的数据。

private async Task ExecuteStoredProc()
{
    DbCommand cmd = _customerContext.Database.GetDbConnection().CreateCommand();

    cmd.CommandText = "dbo.usp_CustomerAll_sel";
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new SqlParameter("@SomeOutput", SqlDbType.BigInt) { Direction = ParameterDirection.Output, Value = -1 });

    if (cmd.Connection.State != ConnectionState.Open)
    {
        cmd.Connection.Open();
    }

    await cmd.ExecuteNonQueryAsync();

    long SomeOutput = (long)cmd.Parameters["@SomeOutput"].Value;
}

有没有办法同时获取结果集和输出参数，并将它们作为元组返回？

当我只输入硬编码的值时，它看起来像：

[HttpGet]
public async Task<Tuple<IEnumerable<Customer>, int>> GetAllCustomer()
{
    var votesParam = new SqlParameter
    {
        ParameterName = "SomeOutput",
        Value = -1,
        Direction = ParameterDirection.Output
    };

    var y = await _customerContext.Customers.FromSql("usp_CustomerAll_sel @SomeOutput out", votesParam).ToArrayAsync();
    return new Tuple<IEnumerable<Customer>, int>(y, **25**);
}

而结果如下：

{"item1":[{"customerId":1,"customerName":"Cus1"},{"customerId":2,"customerName":"Cus2"}],"item2":27}

基本上这就是我在寻找的内容...需要帮忙吗？

- Rahul Chowdhury

---

解决方案在这里：https://dev59.com/-FcO5IYBdhLWcg3wxEYP - Bruno

Answer 1

在 EF Core 中，目前还不能从原始 SQL 查询中返回临时类型（他们正在研究此问题），因此您需要解决此问题。将此类添加到您的项目中：

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Threading;
using System.Threading.Tasks;

namespace Microsoft.EntityFrameworkCore
{

    public static class RDFacadeExtensions
    {
        public static RelationalDataReader ExecuteSqlQuery(this DatabaseFacade databaseFacade, string sql, params object[] parameters)
        {
            var concurrencyDetector = databaseFacade.GetService<IConcurrencyDetector>();

            using (concurrencyDetector.EnterCriticalSection())
            {
                var rawSqlCommand = databaseFacade
                    .GetService<IRawSqlCommandBuilder>()
                    .Build(sql, parameters);

                return rawSqlCommand
                    .RelationalCommand
                    .ExecuteReader(
                        databaseFacade.GetService<IRelationalConnection>(),
                        parameterValues: rawSqlCommand.ParameterValues);
            }
        }
    }
}

然后您可以调用以下方法并从SP获取OUTPUT，这里是一个示例：

            var _sMsg = new SqlParameter("sMsg", "")
            {
                Direction = ParameterDirection.Output,
                DbType = DbType.String,
                Size = 500
            };

            var sql = "exec sp_foo @sUserId, @sMsg OUTPUT";

            using (var dr = _ctx.Database.ExecuteSqlQuery(sql, _sUserID, _sMsg))
            {
                //here you can retrive your table
                while (dr.DbDataReader.Read())
                {
                    var bar = dr.DbDataReader[0].ToString();
                }

                //here is your OUTPUT
                return _sMsg.Value.ToString();
            }

- Rohirrim

---

我需要从一个计算存储过程中获取两个输出参数... 但是尝试调整这段代码并不能带回另一个计算结果。 - Slagmoth

只要数据读取器打开，该过程就不会返回输出参数值，必须关闭数据读取器。请参见以下解决方案。 - Bruno

@Bruno 谢谢，我已经采取了略微不同的方向，不再需要输出参数，但是当我将其与 EF 3.1 API 传输时，扩展中的 parameterValues 不再被支持。我只需要执行一个没有相应实体的 sproc，而不是质疑这是否是最佳方法。这在我2.2的原型中有效，我以为它也适用于 3.1 :( 有点误导了我。 - Slagmoth

Answer 2

这应该可以运行。这次我只填充了一个 DataTable，但你可以用多个 DataTable 填充一个 DataSet。

using (SqlConnection connection  = new SqlConnection(_customerContext.Database.Connection.ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand("dbo.usp_CustomerAll_sel", connection);
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@SomeOutput", SqlDbType.BigInt) { Direction = ParameterDirection.Output, Value = -1 });

                    if (cmd.Connection.State != ConnectionState.Open)
                    {
                        cmd.Connection.Open();
                    }


                    connection.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);

                    long SomeOutput = (long)cmd.Parameters["@SomeOutput"].Value;

                    connection.Close();
                }

由于在 .net core 中无法使用 SqlDataAdapter，您可以使用第三方库来实现相同的结果，例如NReco.Data，实际上，代码非常相似。

- aperezfals

---

似乎我无法在asp.net core中使用SqlDataAdapter和DataTable。http://stackoverflow.com/questions/38536339/sqldataadapter-missing-in-a-asp-net-core-project - Rahul Chowdhury

Answer 3

以下是一个示例，展示如何从存储过程中获取多个结果集，如果您使用 ADO.NET 的话：

在 C# 中从存储过程返回多个记录集

不过，在这种情况下，您必须放弃输出参数。

- Akos Nagy

---

我知道在ADP.net中有一些可能性，但我正在尝试使用EF Core来实现它。不知何故，我的架构师喜欢将其与await async web api操作一起使用。 - Rahul Chowdhury

Answer 4

我在构建一个新应用程序时遇到了一个非常类似的问题，因此我被迫使用存储过程。 我发现使用ExecuteScalar()查找单个结果和使用ExecuteReader()填充我的列表是成功的。 下面我将添加我的代码片段。

public async Task<IEnumerable<vCompanyLogsheet>> GetCompanyLogsheet(object period)
    {
        using (var db = new TimeMachineTestContext())
        {
            DbCommand cmd = db.Database.GetDbConnection().CreateCommand();
            cmd.CommandText = "exec @return_value = dbo.[usp_GetCompanyLogSheets] 'June 2017'";
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("@return_value", SqlDbType.Int) { Direction = ParameterDirection.Output });
            if (cmd.Connection.State != ConnectionState.Open)
            {
                cmd.Connection.Open();
            }

            var data = new List<vCompanyLogsheet>();

            DbDataReader reader = await cmd.ExecuteReaderAsync();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        data.Add(new vCompanyLogsheet()
                        {
                            TimeEntryId = reader.GetInt32(0),
                            TimeEntryDate = reader.GetString(1),
                            FullName = reader.GetString(2),
                            ProjectName = reader.GetString(3),
                            ProjectCategoryName = reader.GetString(4),
                            CategoryGroup = reader.GetString(5),
                            TimeEntryDetail = reader.GetString(6),
                            NrHours = reader.GetDecimal(7),
                        });
                    }
                }
                else
                {
                    Console.WriteLine("No rows found.");
                }
                reader.Close();

            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.Connection.Close();
            }

            return data;
        }
    }

这里是存储过程：

    CREATE PROCEDURE [dbo].[usp_GetCompanyLogSheets]
    @Period varchar(50)
AS
SELECT
    TimeEntryId,
    CONVERT(varchar(50),TimeEntryDate,105) as TimeEntryDate,
    FullName,
    ProjectName,
    ProjectCategoryName,
    ISNULL(ProjectCategoryGroup , 'N/A') as CategoryGroup,
    TimeEntryDetail,
    CONVERT(DECIMAL(6,2), NrHours) as NrHours
FROM
    viewTimeEntries
WHERE
Period = @Period
ORDER BY
    TimeEntryDate

在 SQL 中执行存储过程会创建以下代码：

    DECLARE @return_value int

EXEC    @return_value = [dbo].[usp_GetCompanyLogSheets]
        @Period = N'June 2017'

SELECT  'Return Value' = @return_value

看起来很简单，但是出于某种原因，return_value 始终为 0。在返回单个整数的更基本的存储过程中，我遇到了同样的问题。我不得不使用 var myVar = cmd.ExecuteScalar() 来获取该返回值。

希望这可以帮助您，我花了大约两天时间才找到一个可行的解决方案。我不知道它有多有效，但它可以工作，我会一直使用它，直到 EF Core 发布官方解决方案。

- Etienne Redelinghuys