使用EF Core獲取存儲過程的輸出參數值?

asp.net-core c# entity-framework entity-framework-core

我在我的應用程序中使用Asp.net核心和EF核心。基本上我想從單個存儲過程中獲取多個結果集。試圖搜索它最近2天沒有這樣的運氣。試圖弄清楚解決它的工作..

這是我的存儲過程:

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

我在該表中有2條記錄,所以基本上應該返回一個客戶類型表,輸出參數應該返回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);
}

上面的一個返回列表,但我沒有從DB獲取輸出參數的值.(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}

基本上這就是我要找的......有什麼幫助嗎?

熱門答案

在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";

            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();


Related

許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow