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.