这是我的存储过程:
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 ENDI 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
[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**); }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.
更多推荐
使用EF Core获取存储过程的输出参数值?
发布评论