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

本文介绍了使用 EF Core 获取存储过程的输出参数值?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在我的应用程序中使用了 Asp 核心和 EF 核心.基本上我想从单个存储过程中获取多个结果集.试图搜索它过去 2 天没有这样的运气.试图找出解决方法来解决它..

I am using Asp core and EF core in my application. Basically I want to get multiple result set from a single stored procedure. Tried to search it for last 2 days no such luck. Tried to figure out a work around to resolve it..

这是我的存储过程:

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..

I have 2 records in that table, so basically it should return a table of customer type and output parameter should return 27..

现在从我的 代码中我已经尝试过

Now from my code what I have tried so far

[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 显示为空

Above one returning me the list but I am not getting the value of output parameter from DB .(int)votesParam.Value is showing null

现在如果我使用ExecuteNonQueryAsync，那么我得到的是输出参数，但不是实际数据

Now if I use ExecuteNonQueryAsync, then I am getting the output parameter but not the actual data

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; }

有没有办法同时获取结果集和输出参数并作为元组返回?

Is there any way to get both result set and the output parameter and return as a tuple?

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

When I just put the hard coded value then it's looks like

[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}

基本上这就是我要找的...有什么帮助吗?

Basically this is what I am looking for... Any help?

推荐答案

在 EF Core 中，您还不能从原始 SQL 查询中返回即席类型(他们正在解决这个问题)，因此您需要一个解决方法问题.将此类添加到您的项目中:

In EF Core you can't return ad-hoc types from raw SQL queries yet (they are working on this), so you will need a workaround for this issue. Add this class to your project:

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，这是一个示例:

Then you can call the method below and get the OUTPUT from you SP, here's a sample:

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