With Entity Framework Core you are able creating a model from the database and also calling a stored procedure in a Microsoft SQL Server. I thought this would be easy to implement the call with stored procedure.
There is a method called ExecuteSqlCommand which takes the stored proc name and an array of parameters. I wanted to pass in two parameters and get one back, no problem, I’ve done this with ADO.NET and Entity Framework 6.
Two hours later and it was nowhere near working and I was very annoyed. ExecuteSqlCommand takes SqlParameters and the constructor for the output parameter is way more confusing than it needs to be.
The input SqlParameter takes a name, a type and a value but the output SqlParameter needs eight more constructor params!!
Fortunately, Entity Framework Core still supports old style commands
DbCommand cmd = _context.Database.GetDbConnection().CreateCommand();
DbCommand still has the ExecuteNonQueryAsync method. Save yourself a major headache and use the cmd.ExecuteNonQueryAsync().
Here is a simple example passing in two parameters and getting one output back.
The SQL procedure I want to call requires two parameters in input (firstName and lastName) and one in output (id). The definition of the stored procedure is like that:
ALTER PROCEDURE sp_DoSomething
(
@firstName VARCHAR(50),
@lastName VARCHAR(100),
@id INT OUTPUT
)
AS
BEGIN
...
END
Now, I have to define in the code, what parameters are in input and what in output. It is enough to specified only if the direction is in output with Direction = ParameterDirection.Output adding in the SqlParameters.
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Threading.Tasks;
using Microsoft.Data.Entity;
private async Task ExecuteStoredProc()
{
DbCommand cmd = _context.Database.GetDbConnection().CreateCommand();
cmd.CommandText = "dbo.sp_DoSomething";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@firstName", SqlDbType.VarChar) { Value = "Enrico" });
cmd.Parameters.Add(new SqlParameter("@lastName", SqlDbType.VarChar) { Value = "Rossini" });
cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.BigInt) { Direction = ParameterDirection.Output });
if (cmd.Connection.State != ConnectionState.Open)
cmd.Connection.Open();
await cmd.ExecuteNonQueryAsync();
long id = (long)cmd.Parameters["@Id"].Value;
}
You can download the source code from Github how to use Entity Framework Core and calling a stored procedure.
Read more…
In conclusion, I published other posts about Entity Framework Core that may be interesting to you:
5 thoughts on “Entity Framework Core and calling a stored procedure”