Have a scenario to execute a stored procedure and read the return value in EF Core, that returns a single value.
I tried with this code, but this does not work. I understand that ExecuteSqlCommand
does not work for select and can be used only for update to database.
var test = context.Database.ExecuteSqlCommand("SPName");
The stored procedure has just a select statement like Select 'somevalue'
Looking for any alternative to get data that stored procedure returns.
Able to solve my problem with below code. This is based on suggestions given in below replies.
using (var command = context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = "StoredProcedureName";
command.CommandType = CommandType.StoredProcedure;
context.Database.OpenConnection();
var dataReader = command.ExecuteReader();
if (dataReader.Read())
{
string _test = dataReader.GetString(dataReader.GetOrdinal("ColumnName"));
}
}
DbCommand cmd = ctx.Database.GetDbConnection().CreateCommand();
cmd.CommandText = "SPName";
cmd.CommandType = CommandType.StoredProcedure;
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
return await cmd.ExecuteNonQueryAsync();
Here is a post about that: https://nodogmablog.bryanhogan.net/2016/07/entity-framework-core-and-calling-a-stored-proceduce/#comment-60582