Dapper Stored Procedure tip

Introduction

Dapper is a simple object mapper for .NET data access which uses Microsoft classes under the covers which has been covered in the following article Using Dapper - C# Part 1 which is part of a series on Dapper.

Recently there has been a minor change with working with stored procedures.

The former method for calling a stored procedure using Dapper the command type was required as shown below with commandType: CommandType.StoredProcedure.

private static async Task GetAllEmployees()
{
    await using SqlConnection cn = new(DataConnections.Instance.MainConnection);

    // get employees via a stored procedure
    var employees = 
        (
            await cn.QueryAsync<Employee>("usp_GetAllEmployees", 
                commandType: CommandType.StoredProcedure)
        )
        .AsList();

}

Now a developer has a little less coding as the command type is not required.

private static async Task GetAllEmployees()
{
    await using SqlConnection cn = new(DataConnections.Instance.MainConnection);

    // get employees via a stored procedure
    var employees = 
        (
            await cn.QueryAsync<Employee>("usp_GetAllEmployees")
        )
        .AsList();

}

Code

To try out the above clone the following repository.

Sample project

Under LocalDb create a database named DapperStoredProcedures
Run Scripts\populate.sql
Run the project

GetAllEmployees method returns all records
GetEmployeeByGender method returns records by gender using an enum.

Note
Since Dapper does not handle DateOnly the following package kp.Dapper.Handlers is used.

using Dapper;
using DapperStoredProcedures1.Classes;
using DapperStoredProcedures1.Models;
using Dumpify;
using kp.Dapper.Handlers;
using Microsoft.Data.SqlClient;

namespace DapperStoredProcedures1;
internal partial class Program
{
    static async Task Main(string[] args)
    {
        await Setup();

        // Allows Dapper to handle DateOnly types
        SqlMapper.AddTypeHandler(new SqlDateOnlyTypeHandler());

        await GetAllEmployees();

        Console.WriteLine();

        await GetEmployeeByGender();

        ExitPrompt();
    }

    private static async Task GetEmployeeByGender()
    {

        AnsiConsole.MarkupLine("[cyan]Female employees[/]");

        await using SqlConnection cn = new(DataConnections.Instance.MainConnection);

        // get employees via a stored procedure
        var employees =
            (
                await cn.QueryAsync<Employee>("usp_GetEmployeeByGender", 
                    param: new { GenderId = Genders.Female })
            )
            .AsList();

        // Nicely display the results from the stored procedure
        employees.Dump();
    }

    private static async Task GetAllEmployees()
    {
        AnsiConsole.MarkupLine("[cyan]All employees[/]");

        await using SqlConnection cn = new(DataConnections.Instance.MainConnection);

        // get employees via a stored procedure
        var employees = 
            (
                await cn.QueryAsync<Employee>("usp_GetAllEmployees")
            )
            .AsList();

        // Nicely display the results from the stored procedure
        employees.Dump();
    }
}

Summary

Now a developer has just a little less code to write when working with Dapper and stored procedures. If for some reason this does not work, report this to the Dapper team here.

Also, although code provided uses SQL-Server, this will work with any data provider which supports stored procedures.

Blog

Dapper Stored Procedure tip

Karen Payne

Introduction

Code

Summary

Join Our Newsletter. No Spam, Only the good stuff.

Related