Stored Procedures with Dapper in ASP.NET Core

When working with databases, some operations are better kept close to the data.

Bulk status updates, cleanup jobs and multi-step writes often end up as long SQL strings scattered across the application. That makes them harder to reuse, test and optimize.

A better approach is to encapsulate that logic in the database itself.

That's where stored procedures come in, and with Dapper calling them from ASP.NET Core is straightforward. Let's see how.

Why Stored Procedures

A stored procedure is a named block of SQL that lives in the database. You call it by name and pass parameters when needed.

They help in a few common scenarios:

• Encapsulation - Keep complex SQL in one place instead of repeating it across endpoints
• Fewer round trips - Several statements run in one call instead of one call per statement
• Consistency - Multiple apps or services can run the same procedure without duplicating logic

Dapper does not generate SQL for you. That makes it a natural fit for stored procedures. You write the procedure once, then invoke it with the same extension methods you already use for regular queries.

The trade-off is that the logic now lives outside your C# code. Keep procedures in version-controlled migration scripts so they don't drift from the application.

If you're new to Dapper, check out my previous blog post on Getting Started with Dapper.

Getting Started

We'll use PostgreSQL and a simple Order model. Install the packages you need:

dotnet add package Dapper
dotnet add package Npgsql

The domain model looks like this:

public class Order
{
    public Guid Id { get; set; }

    public OrderStatus Status { get; set; }

    public DateTime CreatedAt { get; set; }

    public DateTime? ModifiedAt { get; set; }
}

public enum OrderStatus
{
    Created = 0,
    Submitted = 1,
    Shipped = 2,
    Delivered = 3,
    Canceled = 4
}

And the matching table:

CREATE TABLE "Orders" (
    "Id" UUID PRIMARY KEY,
    "Status" INTEGER NOT NULL,
    "CreatedAt" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    "ModifiedAt" TIMESTAMP WITHOUT TIME ZONE
);

For dependency injection we use an ISqlConnectionFactory that opens an NpgsqlConnection. That keeps endpoints lean and easy to test.

public interface ISqlConnectionFactory
{
    IDbConnection OpenConnection();
}

public sealed class PostgresConnectionFactory(string connectionString) : ISqlConnectionFactory
{
    public IDbConnection OpenConnection()
    {
        var dbConnection = new NpgsqlConnection(connectionString);
        dbConnection.Open();
        return dbConnection;
    }
}

Register it once at startup:

builder.Services.AddSingleton<ISqlConnectionFactory>(_ =>
    new PostgresConnectionFactory(
        builder.Configuration.GetConnectionString("Postgres")!));

Creating a Stored Procedure

Imagine we want to cancel orders that stayed in the Created status for too long. That kind of cleanup belongs in a scheduled job, but the SQL itself fits well inside a procedure.

Here is a PostgreSQL procedure that marks old created orders as canceled:

CREATE OR REPLACE PROCEDURE cancel_old_created_orders()
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE "Orders"
    SET "Status" = 4,
        "ModifiedAt" = NOW()
    WHERE "Status" = 0
      AND "CreatedAt" <= NOW() - INTERVAL '1 minute';
END;
$$;

Status 0 is Created and status 4 is Canceled. The procedure updates matching rows and sets ModifiedAt to the current time. The cutoff is one minute only to keep the demo quick.

Calling a Stored Procedure

From C#, you call the procedure by name and tell Dapper what kind of command it is.

The key detail is CommandType.StoredProcedure. Without it, Dapper treats the string as plain SQL text:

private async Task<IResult> Handler(ISqlConnectionFactory connectionFactory)
{
    using var connection = connectionFactory.OpenConnection();

    await connection.ExecuteAsync(
        "cancel_old_created_orders",
        commandType: CommandType.StoredProcedure);

    return Results.NoContent();
}

That's it. No SQL string in the endpoint. Dapper sends the call to the database and your handler carries on.

Use ExecuteAsync when the procedure does not return a result set. It is the same method you use for INSERT, UPDATE and DELETE.

NOTE: Since Npgsql 7.0, CommandType.StoredProcedure generates a CALL statement, so it only works with procedures. Npgsql treats it as a portability feature, so writing CALL cancel_old_created_orders() as plain SQL works just as well. Functions need a plain SELECT, as we'll see below.

Passing Parameters

Most procedures take input. Let's cancel a single order by id instead of sweeping the whole table.

CREATE OR REPLACE PROCEDURE cancel_order(order_id UUID)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE "Orders"
    SET "Status" = 4,
        "ModifiedAt" = NOW()
    WHERE "Id" = order_id;
END;
$$;

In Dapper, pass an anonymous object. Property names should match the procedure parameter names:

private async Task<IResult> Handler(
    ISqlConnectionFactory connectionFactory,
    Guid id)
{
    using var connection = connectionFactory.OpenConnection();

    await connection.ExecuteAsync(
        "cancel_order",
        new { order_id = id },
        commandType: CommandType.StoredProcedure);

    return Results.NoContent();
}

Npgsql passes parameters by name, so order_id has to match the procedure argument exactly. You can also pass a concrete type or DynamicParameters when you need more control.

Here is the same call with DynamicParameters:

var parameters = new DynamicParameters();
parameters.Add("order_id", id);

await connection.ExecuteAsync(
    "cancel_order",
    parameters,
    commandType: CommandType.StoredProcedure);

DynamicParameters is useful when you need output parameters, custom DbType values or direction flags. For simple inputs, an anonymous object is usually enough.

Returning Results

Procedures are great for writes. When you want rows back in PostgreSQL, a function that returns a set is often the cleaner option.

For example, fetch every order with a given status:

CREATE OR REPLACE FUNCTION get_orders_by_status(status INTEGER)
RETURNS SETOF "Orders"
LANGUAGE sql
AS $$
    SELECT *
    FROM "Orders"
    WHERE "Status" = status;
$$;

Functions are invoked with a plain SELECT. Use QueryAsync and map the result to Order:

private async Task<IResult> Handler(
    ISqlConnectionFactory connectionFactory,
    OrderStatus status)
{
    using var connection = connectionFactory.OpenConnection();

    var orders = await connection.QueryAsync<Order>(
        "SELECT * FROM get_orders_by_status(@status)",
        new { status = (int)status });

    return Results.Ok(orders);
}

The same pattern works with QuerySingleOrDefaultAsync, QueryFirstAsync and the other query helpers. Pick the method that matches how many rows you expect.

NOTE: Npgsql 6 and older generated a SELECT for CommandType.StoredProcedure, so older tutorials call functions with it. The Npgsql.EnableStoredProcedureCompatMode switch restores that behavior, but SELECT for functions and CALL for procedures is the safer path.

Conclusion

Stored procedures keep complex or reusable SQL next to the data. Dapper makes calling them from ASP.NET Core simple.

Call procedures by name with CommandType.StoredProcedure or a plain CALL, and query functions with SELECT. Use ExecuteAsync for writes and QueryAsync for reads.

Parameters map the same way as regular Dapper queries, so you stay productive without learning a separate API.

If you want to check out examples I created, you can find the source code here:

I hope you enjoyed it, subscribe and get a notification when a new blog is up!