Call a stored procedure in ASP.NET Core 3.1/5.0

Question

I have a simple ASP.NET Core service that needs to have exactly one interaction with a database. It calls a stored procedure that returns a BigInt/long.

It seems a bit of overkill to fire up all the stuff to run Entity Framework Core for this. Especially since EF Core does not do stored procedures very well (as of the last time I checked).

Is there a .NET Core only way to call and get the results of a stored procedure (without using EF Core or other frameworks)?

Answer 1

Something like this is the minimum you would need. Obviously you might want to store the connection string in a config file.

await using DbConnection connection = new SqlConnection("Connection_String");
await connection.OpenAsync();
await using var command = connection.CreateCommand();

command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "Stored_Proc_Name";
// Declare any parameters here
var p = command.CreateParameter();
p.ParameterName = "IsCool";
p.Value = true;
p.DbType = System.Data.DbType.Boolean;
command.Parameters.Add(p);

var result = await command.ExecuteScalarAsync();
if (result == null) {
    throw new Exception("Bad");
}

long numValue = (long) result;