SpDev Install free

Calling SQL Server stored procedures from C#: ADO.NET, Dapper and EF Core

Working C# code to call a stored procedure, read several result sets, and get output parameters and return values with ADO.NET, Dapper and EF Core, plus the mistakes that return null.

By Areeb Ahmer, . 4 minute read.

Calling a stored procedure from .NET looks the same in every library until you need more than one result set, an output parameter or the return code. That’s where each library has its own API, and its own way to give you null.

All the code below was run against the SpDevDemo database on SQL Server 2022, with Microsoft.Data.SqlClient 5.2, Dapper 2.1 and EF Core 8. The procedure returns two result sets, an output parameter and a return code:

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT,
    @OrderCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @OrderCount = COUNT(*) FROM dbo.Orders WHERE CustomerId = @CustomerId;

    SELECT CustomerId, Name FROM dbo.Customers WHERE CustomerId = @CustomerId;          -- result set 1
    SELECT OrderId, OrderNumber, OrderDate, Total FROM dbo.Orders                       -- result set 2
    WHERE CustomerId = @CustomerId ORDER BY OrderDate;

    RETURN CASE WHEN @OrderCount = 0 THEN 1 ELSE 0 END;
END

ADO.NET (Microsoft.Data.SqlClient)

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

await using var command = new SqlCommand("dbo.GetCustomerOrders", connection)
{
    CommandType = CommandType.StoredProcedure
};
command.Parameters.Add("@CustomerId", SqlDbType.Int).Value = 42;

var orderCount = command.Parameters.Add("@OrderCount", SqlDbType.Int);
orderCount.Direction = ParameterDirection.Output;

var returnValue = command.Parameters.Add("@ReturnValue", SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;

await using (var reader = await command.ExecuteReaderAsync())
{
    while (await reader.ReadAsync())                  // result set 1: the customer
        Console.WriteLine(reader.GetString(1));

    await reader.NextResultAsync();                   // result set 2: the orders
    while (await reader.ReadAsync())
        Console.WriteLine($"{reader.GetString(1)}  {reader.GetDecimal(3)}");
}

// Only now, after the reader is closed:
Console.WriteLine($"Orders: {orderCount.Value}, return code: {returnValue.Value}");

Things that matter:

  • CommandType.StoredProcedure and the procedure’s name as the command text. ADO.NET then sends a proper RPC call, with typed parameters.
  • Parameters.Add with a type, not AddWithValue. AddWithValue guesses the type from the .NET value: every string becomes NVARCHAR, with its length set to the string’s length. Against a VARCHAR column, that can turn an index seek into a scan; see the AddWithValue article.
  • Output parameters and the return value are filled only after the reader is closed. SQL Server sends them after the last result set. Read them while the reader is open and they’re null. In the test run, orderCount.Value was null inside the using block and 9 after it.
  • The return value parameter’s name doesn’t matter, only its direction.

For a procedure that returns nothing, use ExecuteNonQueryAsync(); for a single value, ExecuteScalarAsync().

Dapper

await using var connection = new SqlConnection(connectionString);

var p = new DynamicParameters();
p.Add("@CustomerId", 42);
p.Add("@OrderCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

using (var grid = await connection.QueryMultipleAsync(
    "dbo.GetCustomerOrders", p, commandType: CommandType.StoredProcedure))
{
    var customer = await grid.ReadSingleAsync<Customer>();
    var orders = (await grid.ReadAsync<OrderRow>()).ToList();
}

var count = p.Get<int>("@OrderCount");          // after the grid is disposed
var status = p.Get<int>("@ReturnValue");

record Customer(int CustomerId, string Name);
record OrderRow(int OrderId, string OrderNumber, DateTime OrderDate, decimal Total);
  • commandType: CommandType.StoredProcedure is required; without it Dapper sends the name as a SQL batch.
  • QueryMultipleAsync reads several result sets in order. For one result set, QueryAsync<T>.
  • Same rule as ADO.NET: p.Get<T>() for output values works only after the reader (the grid) is disposed.
  • Strings passed in an anonymous object or DynamicParameters are sent as NVARCHAR(4000). For VARCHAR parameters, use new DbString { Value = s, IsAnsi = true, Length = 20 }.

EF Core

EF Core has three entry points, depending on what the procedure returns.

Rows that map to an entity: FromSql.

var customerId = 42;
var open = await db.Orders
    .FromSql($"EXECUTE dbo.GetOpenOrders @CustomerId = {customerId}")
    .ToListAsync();

The interpolated string looks like string concatenation, but isn’t: FromSql turns {customerId} into a real @p0 parameter, so it’s safe from SQL injection. (FromSqlRaw with your own string concatenation is not.)

You can’t compose a LINQ query on top of a procedure call, because SQL Server can’t use EXEC as a subquery:

await db.Orders.FromSql($"EXECUTE dbo.GetOpenOrders @CustomerId = {customerId}")
    .Where(o => o.Total > 1000)
    .ToListAsync();
InvalidOperationException: 'FromSql' or 'SqlQuery' was called with non-composable SQL and with a query
composing over it. Consider calling 'AsEnumerable' after the method to perform the composition on the client side.

Filter after ToListAsync() (in memory), or better, add a parameter to the procedure. If you need composition, an inline table-valued function can be composed (FROM dbo.SomeFunction(@x)), and filters are applied in SQL.

Rows that don’t map to an entity: Database.SqlQuery<T> (EF Core 8 and later), for any type whose properties match the columns:

var rows = await db.Database
    .SqlQuery<OrderRow>($"EXECUTE dbo.GetOpenOrders @CustomerId = {customerId}")
    .ToListAsync();

No rows, or output parameters: Database.ExecuteSqlAsync, with a SqlParameter for each output:

var count = new SqlParameter("@Count", SqlDbType.Int) { Direction = ParameterDirection.Output };

await db.Database.ExecuteSqlAsync(
    $"EXECUTE dbo.CountOrders @CustomerId = {customerId}, @Count = {count} OUTPUT");

Console.WriteLine(count.Value);   // 9

Note the OUTPUT keyword inside the SQL string: without it, the call succeeds and count.Value stays empty, exactly as in T-SQL.

EF Core doesn’t read multiple result sets from a procedure. For that, use the underlying connection (db.Database.GetDbConnection()) with ADO.NET or Dapper.

Which one to use

Need Best fit
One result set into objects Dapper QueryAsync<T>, or EF Core FromSql / SqlQuery<T>
Several result sets Dapper QueryMultipleAsync, or ADO.NET
Output parameters and return code ADO.NET or Dapper DynamicParameters; EF Core ExecuteSqlAsync for outputs
Full control over types and streaming ADO.NET

Debugging the procedure your app calls

When the C# side is right but the procedure returns the wrong thing, you need to see inside it while your app calls it. SpDev can do that: add the SpDev.Runtime package to the app (UseSpDev() for EF Core, connection.WithSpDev(services) for Dapper and ADO.NET), run SpDev: Attach to Application in VS Code, and the debugger stops in the procedure’s .sql file when your app calls it, with the parameter values your code actually sent.