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

编程入门行业动态更新时间:2024-10-24 16:25:22

本文介绍了使用EF Core获取存储过程的输出参数值？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述我在我的应用程序中使用Asp核心和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 - 这个25是一个复合体的变量查询但总是一个整数 END

我在该表中有2条记录，所以基本上应该返回一个客户类型表，输出参数应该返回27 ..

现在从我的代码到目前为止我已经尝试了

[HttpGet] public async任务< 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（）; 返回新元组< 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（）; } 等待cmd.ExecuteNonQueryAsync（）; long SomeOutput =（long）cmd.Parameters [@ SomeOutput]。 }

有没有办法获取结果集和输出参数并返回为一个元组？

当我只是硬编码的值时，它看起来像

[HttpGet] public async任务< 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：[{ 客户ID：1，客户名称： Cus1}，{ 客户ID：2 客户名称： Cus2}]， item2：27}

基本上这是我正在寻找...任何帮助？ / p>

解决方案

这应该是正常的。这一次我只填写了一个DataTable，但是可以使用多个DataTable填充DataSet

使用（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]。 connection.Close（）; }

由于您不能在内核中使用SqlDataAdapter，因此可以使用第三方图书馆可以获得相同的结果，例如 NReco.Data ，实际上，代码很相似。

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

This is my stored procedure:

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

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

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

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

And result like

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

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

解决方案

This should work. This time I filled only a DataTable, but you can fill a DataSet with multiples DataTables

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

Since you can't use SqlDataAdapter in core, you can use a third party library to archieve the same result, like NReco.Data , actually, the code is pretty similar.