Dapper - How to call a stored procedure with a parameter

This post shows you how to call a stored procedure with a parameter using Dapper. If we have the following table with an Id (int) and a text (NVARCHAR(MAX)) and one row:

CREATE TABLE [dbo].[MyEntities](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Text] [nvarchar](max) NOT NULL,
   CONSTRAINT [PK_MyEntities] PRIMARY KEY CLUSTERED 
   (
	  [Id] ASC
   )
)

INSERT INTO [dbo].[MyEntities]
           ([Text])VALUES ('This is a text!')

And a simple stored procedure that basically wraps a simple SELECT * FROM ... statement:

CREATE PROC GetEntity @Id int
AS
SELECT * FROM MyEntities e
WHERE [id] = @Id

We can execute this stored procedure using Dapper with the following piece of C# code:

using var con = new SqlConnection("<Your connectionstring>");
con.Open();

var sql = "EXEC GetEntity @Id";
var values = new { Id = 0 };
var getEntityResult = con.Query(sql, values).ToList();

In the above we create and open a new connection, we make a SQL statement with a parameter (EXEC GetEntity @Id) and we create an anonymous object that contains the value for our statement. We then run the Query method on our connection and get the result of our stored procedure. That is all there is to calling a stored procedure using Dapper.

With a generic type

We can also create a class for the result and use that in our Query call as a generic parameter. This automtically maps the result to that class:

class MyEntity
{
    public int Id { get; set; }
    public string Text { get; set; }
}

And the .Query<MyEntity>() call:

var sql = "EXEC GetEntity @Id";
var values = new { Id = 0 };
var results = con.Query<MyEntity>(sql, values);

If our procedure has multiple parameters we can provide multiple parameters with a ,:

var sql = "EXEC GetEntity @Id, @Text";
var values = new { Id = 7 , Text = "This is a text!"};
var results = con.Query(sql, values);

That is all

This was a post on how to call a stored procedure with a parameter using Dapper. I hope you found it helpful, please leave a comment down below!