Entity Framework Core and calling a stored procedure

Jul 23, 2016 # Entity Framework

I thought this would be easy, 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.

But holy s* * t, 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, EF 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.

 1using System.Data;
 2using System.Data.Common;
 3using System.Data.SqlClient;
 4using System.Threading.Tasks;
 5using Microsoft.Data.Entity;
 6using Nito.AsyncEx;
 7
 8private async Task ExecuteStoredProc()
 9{
10	DbCommand cmd = _context.Database.GetDbConnection().CreateCommand();
11
12	cmd.CommandText = "dbo.sp_DoSomething";
13	cmd.CommandType = CommandType.StoredProcedure;
14
15	cmd.Parameters.Add(new SqlParameter("@firstName", SqlDbType.VarChar) { Value = "Steve" });
16	cmd.Parameters.Add(new SqlParameter("@lastName", SqlDbType.VarChar) { Value = "Smith" });
17
18	cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.BigInt) { Direction = ParameterDirection.Output });
19
20	if (cmd.Connection.State != ConnectionState.Open)
21	{
22		cmd.Connection.Open();
23	}
24
25	await cmd.ExecuteNonQueryAsync();
26
27	long id = (long)cmd.Parameters["@Id"].Value;
28}

Download full source code here.

Entity Framework

Entity Framework Core and calling a stored procedure

Related