Dapper Stored Procedure tip
Tags: csharp, database, codenewbie
Published: 2024-06-20 20:35:03

## 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](https://dev.to/karenpayneoregon/working-with-dapper-in-c-5kd) 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**.

```csharp
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.

```csharp
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.

{% cta https://github.com/karenpayneoregon/sql-basics/tree/master/DapperStoredProcedures1 %} Sample project {% endcta %}

1. Under LocalDb create a database named DapperStoredProcedures
1. Run Scripts\populate.sql
1. 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](https://www.nuget.org/packages/kp.Dapper.Handlers/1.0.0?_src=template) is used.

```csharp
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](https://github.com/DapperLib/Dapper/issues).

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