DEV Community

Cover image for Dapper Stored Procedure tip
Karen Payne
Karen Payne

Posted on

Dapper Stored Procedure tip

#csharp #database #codenewbie

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();

}
Enter fullscreen mode Exit fullscreen mode

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();

}
Enter fullscreen mode Exit fullscreen mode

Code

To try out the above clone the following repository.

Sample project

  1. Under LocalDb create a database named DapperStoredProcedures
  2. Run Scripts\populate.sql
  3. 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();
    }
}
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (3)

Collapse
 
vince_zalamea_512a0f853fd profile image
Vince Zalamea •

If you don't pass a CommandType, then Dapper has to infer it. It checks if your SQL is a single word and presumes it's the name of a stored procedure unless it's in an exclusion list of single-word commands of type text (e.g., commit, rollback, revert, vacuum).

If I know the command I'm passing is the name of a stored procedure, then I'd prefer to pass CommandType: StoredProcedure. Then your program (including Dapper) does less work.

Collapse
 
karenpayneoregon profile image
Karen Payne •

Of course you can pass the command type. And if you look at the Dapper source code there really is nothing lost work-wise.

Collapse
 
vince_zalamea_512a0f853fd profile image
Vince Zalamea •

just out of curiosity, I ran this using BenchmarkDotNet. My "usp_dummy" stored procedure simply does a "SELECT 1". I have a SQL Server instance running on my local machine. Not exactly the best test to isolate Dapper's inference logic.

using BenchmarkDotNet.Attributes;
using BenchmarkDotNet.Running;
using Dapper;
using Microsoft.Data.SqlClient;

BenchmarkRunner.Run<MyBenchmarks>();

[MemoryDiagnoser]
public class MyBenchmarks
{
    const string _connectionString = @"server=MYLOCALSQLSERVER;database=TEST;Trusted_Connection=true;TrustServerCertificate=true;";
    const string _procedure = "usp_dummy";

    [Benchmark]
    public int Implicit()
    {
        using SqlConnection connection = new SqlConnection(_connectionString);
        int i = connection.QuerySingle<int>(_procedure);
        return i;
    }

    [Benchmark (Baseline = true)]
    public int Explicit()
    {
        using SqlConnection connection = new SqlConnection(_connectionString);
        int i = connection.QuerySingle<int>(_procedure, commandType: System.Data.CommandType.StoredProcedure);
        return i;
    }
}
Enter fullscreen mode Exit fullscreen mode

The Explicit method was always faster and usually by 5-6% (about 4 microseconds). So I wouldn't say the cost of reading/writing less code is completely free. If the difference was in nano seconds, I probably wouldn't care. I do like that Dapper infers it if it's omitted but that omission comes at a cost.

Image description

Image description

Read next

jacktt profile image

[Postgres] Isolation levels

JackTT -

apilover profile image

Top 10 Database Query Tools for Effective Data Management

Wanda -

alexmercedcoder profile image

Getting Started with Data Analytics Using PyArrow in Python

Alex Merced -

moh_moh701 profile image

C# Advanced:Extension Methods Demo

mohamed Tayel -