C# – Execute a stored procedure with Dapper

by

You can execute stored procedures with Dapper by specifying the name, parameters, and CommandType.StoredProcedure. Let’s say you have the following stored procedure (single parameter, returns movie rows):

CREATE PROCEDURE spGetMoviesForYear
	@year int
AS
BEGIN
	SELECT * FROM Movies WHERE YearOfRelease=@year
END
Code language: SQL (Structured Query Language) (sql)

Here’s how to execute this stored procedure with Dapper and map its results to Movie objects:

using Dapper;

public IEnumerable<Movie> GetMoviesForYearSP(int year)
{
	using (var con = new SqlConnection(connectionString))
	{
		return con.Query<Movie>("dbo.spGetMoviesForYear", 
			param: new { year }, 
			commandType: System.Data.CommandType.StoredProcedure);
	}
}
Code language: C# (cs)

Use an output parameter

To get a stored procedure’s output parameter, add it as a dynamic parameter (with the DynamicParameters class).

  • Set its DbType to ParameterDirection.Output
  • Add other parameters with AddDynamicParams().
  • After executing, you can get the output parameter value from the DynamicParameters object.

Let’s say you have the following stored procedure with an input and output parameter:

CREATE PROCEDURE [dbo].[spGetMoviesForYear]
	@year int,
	@count int OUTPUT
AS
BEGIN
	SELECT * FROM Movies WHERE YearOfRelease=@year

	SELECT @count = @@ROWCOUNT
END
Code language: SQL (Structured Query Language) (sql)

Here’s an example of executing this with Dapper and checking the output parameter’s value:

using Dapper;

IEnumerable<Movie> GetMoviesByYearAndCount(int year)
{
    using (var con = new SqlConnection(connectionString))
    {
        var dynamicParameters = new DynamicParameters();
        dynamicParameters.AddDynamicParams(new { year });
        dynamicParameters.Add("count", DbType.Int32, direction: ParameterDirection.Output);

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

        var count = dynamicParameters.Get<int>("count");
        Console.WriteLine($"Got {count} records");

        return results;
    }
}
Code language: C# (cs)

Note: Always set the DbType parameter to the right type (such as DbType.Int32), otherwise the output parameter may come back with the wrong value.

When ran, this outputs the output parameter value to the console:

Got 3 recordsCode language: plaintext (plaintext)

Related Articles