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!
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!
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.
