Execute a stored procedure with Dapper in C#

C# Execute a Stored Procedure with Dapper

Let’s consider an example where you have the following stored procedure that retrieves employee details based on their department ID:

CREATE PROCEDURE spGetEmployeesByDepartment
    @departmentId int
AS
BEGIN
    SELECT * FROM Employees WHERE DepartmentId = @departmentId
END

Here’s how you can execute this stored procedure with Dapper and map the results to an Employee object:

using Dapper;
using System.Data.SqlClient;
using System.Collections.Generic;

public IEnumerable<Employee> GetEmployeesByDepartment(int departmentId)
{
    using (var con = new SqlConnection(connectionString))
    {
        return con.Query<Employee>("dbo.spGetEmployeesByDepartment", 
            param: new { departmentId }, 
            commandType: System.Data.CommandType.StoredProcedure);
    }
}

Using an Output Parameter

If you need to retrieve an output parameter from a stored procedure, you can use the DynamicParameters class. This allows you to specify the output parameter’s type and retrieve its value after execution.

Consider the following stored procedure that retrieves employee details and also returns the total count of employees in the specified department:

CREATE PROCEDURE spGetEmployeesByDepartmentWithCount
    @departmentId int,
    @totalCount int OUTPUT
AS
BEGIN
    SELECT * FROM Employees WHERE DepartmentId = @departmentId

    SELECT @totalCount = @@ROWCOUNT
END

To execute this stored procedure and capture the output parameter value using Dapper, you can do the following:

using Dapper;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

public IEnumerable<Employee> GetEmployeesAndCount(int departmentId)
{
    using (var con = new SqlConnection(connectionString))
    {
        var dynamicParameters = new DynamicParameters();
        dynamicParameters.AddDynamicParams(new { departmentId });
        dynamicParameters.Add("totalCount", DbType.Int32, direction: ParameterDirection.Output);

        var results = con.Query<Employee>("dbo.spGetEmployeesByDepartmentWithCount", dynamicParameters, 
            commandType: CommandType.StoredProcedure);

        var totalCount = dynamicParameters.Get<int>("totalCount");
        Console.WriteLine($"Total employees in department: {totalCount}");

        return results;
    }
}

Correct DbType: Ensure that the DbType for the output parameter is set correctly (e.g., DbType.Int32 for an integer). Incorrect DbType settings may cause issues with the output value.

Dynamic Parameters: The DynamicParameters class is used to add parameters (both input and output) and retrieve the output value after execution.