Get output parameter value of stored procedure using EF Core?
I am using Asp.net core and EF core in my application. Basically I want to get a multiple result set from one stored procedure. Tried to find it in the last 2 days, no such luck. Tried to figure out how to work to solve this problem.
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 this table, so basically it should return a client type table and the output parameter should return 27 ..
Now from my .net 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 list returning me but I am not getting output parameter value from DB .(int)votesParam.Value
shows null
Now if I use ExecuteNonQueryAsync
then I get 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 a way to get both the result set and the output parameter and return as a tuple?
When I just put a hard coded value it 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 the result is like
{"item1":[{"customerId":1,"customerName":"Cus1"},{"customerId":2,"customerName":"Cus2"}],"item2":27}
Basically this is what I'm looking for ... Any help?
source to share
This should work. This time I have filled only the DataTable, but you can fill the DataSet with concise 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 cannot use SqlDataAdapter in .net core, you can use a third party library to archive the same result like NReco.Data , in fact the code is very similar.
source to share
In EF Core you cannot yet return special types from raw SQL queries (they are working on this), so first you need some training for this problem, 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);
}
}
}
}
Then you can call the method below and get the OUTPUT SP from you, 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";
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();
source to share
Here's an example of how you can get multiple result sets from a stored procedure if you're fine with ADO.NET:
Return multiple recordsets from stored proc in C #
In this case, you must cut the output parameter.
source to share