Stored procedures, output parameters and return values — Dapper — The Micro-ORM from Basics to Advanced

3. Stored procedures, output parameters and return values

Intermediate★ Must knowLesson 3 of 8

csharp

var p = new DynamicParameters();
p.Add("@CustomerId", 42);
p.Add("@FromDate", new DateTime(2026, 1, 1));
p.Add("@TotalCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@ReturnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

var orders = await conn.QueryAsync<OrderDto>("dbo.usp_GetCustomerOrders", p, commandType: CommandType.StoredProcedure);

int total = p.Get<int>("@TotalCount");      // read after the reader is consumed
int ret = p.Get<int>("@ReturnValue");

sql

CREATE OR ALTER PROCEDURE dbo.usp_GetCustomerOrders
    @CustomerId INT, @FromDate DATE, @TotalCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @TotalCount = COUNT(*) FROM dbo.Orders WHERE CustomerId = @CustomerId AND OrderDate >= @FromDate;
    SELECT Id, OrderDate, Total FROM dbo.Orders WHERE CustomerId = @CustomerId AND OrderDate >= @FromDate ORDER BY OrderDate DESC;
    RETURN 0;
END