In this article, we are going to learn about passing output parameters to stored procedures with Dapper.

Stored procedures are a great way to improve the performance, security, and usability of our code. They bring many advantages over relying on our application code to do the heavy lifting. In C# we often let Dapper take care of our Data Access layer. If you need a refresher on this great micro ORM definitely check here!

To download the source code for this article, you can visit our GitHub repository.

Let’s get started! 

Create a Stored Procedure With Output Parameters

Let us suppose we have Developers table in our database:

Id	Name	Language
1	John	 C#
2	Josh	 C
3	Jane	 F#
4	Anne	 Java
5	David	Go

It is a simple table that we use to track Developers in our team. Since we are using only one column for the Name of our Developers, we can have an unpleasant situation if there are two Developers with the same Name and Language. We can’t differentiate them!

Support Code Maze on Patreon to get rid of ads and get the best discounts on our products!
Become a patron at Patreon!

To resolve this issue we can use a stored procedure for executing Insert statements in our Developers table.

Let’s create it:

CREATE PROCEDURE DeveloperInsert
@Name nvarchar(100),
@Language nvarchar(50),
@Id int OUTPUT,
@Message nvarchar(200) OUTPUT
AS
BEGIN
IF EXISTS(SELECT Id FROM Developers WHERE Name=@Name AND Language=@Language)
    BEGIN
        SET @Message ='There is already a developer '+@Name+' programming in '+@Language
    END
ELSE
    BEGIN
        INSERT INTO Developers(Name,Language) VALUES(@Name,@Language)
        SET @Id=@@IDENTITY
    END
END

It maybe seems complex, however, it is actually very simple.  Firstly we are checking if there is already a Developer with the same Name and Language in our team. Consequently, if we find one we return a message warning about the duplicate, which comes in very handy:

There is already a developer John programming in C# 

Completion time: 2023-02-16T18:01:06.9093631+01:00

Upon successfully inserting the new Developer to the team, however, we get the Id of the new Developer in return:

(1 row affected)
 
6

Completion time: 2023-02-16T18:05:43.0361387+01:00

Use Dapper to Provide Arguments for Output Parameters to Stored Procedures

Now it is time to make use of our stored procedure using Dapper and C#. We are going to use a simple console application.

Let’s create a standard database connection along with our Dapper SQL parameters first:

using (var connection = new SqlConnection(ConnectionString))
{
    var parameters = new DynamicParameters();
    parameters.Add("Name", "John");
    parameters.Add("Language", "C#");
    parameters.Add("Id", dbType: DbType.Int32, direction: ParameterDirection.Output);
    parameters.Add("Message", dbType: DbType.String, direction: ParameterDirection.Output,size:200);
}

Notice we are specifying which parameters we expect as Output and their type. Also, we have to add the System.Data namespace in our code.

Hence everything is ready to use Dapper for executing DeveloperInsert and capturing those output parameters:

public static dynamic ExecuteStoredProcedure(DynamicParameters parameters)
{
    using (var connection = new SqlConnection(ConnectionString))
    connection.Execute("DeveloperInsert", parameters, commandType: CommandType.StoredProcedure);

    var message = parameters.Get<string>("Message"); 
    var id = parameters.Get<int?>("Id"); 

    Console.WriteLine(message); 
    Console.Write($"The Id of the Developer Inserted is: {id}); 
    Console.ReadLine();
}

It is as simple as that! Running  the application we get the following output for a duplicate value:

There is already a developer John programming in C#

Let us change the Developer's Name and rerun the application:

The Id of the Developer Inserted is: 7

Conclusion

In this article, we talked about passing output parameters to stored procedures in C#. Thanks to Dapper the experience is simple, efficient, and elegant.

Liked it? Take a second to support Code Maze on Patreon and get the ad free reading experience!
Become a patron at Patreon!