How to Call a Stored Procedure Using Dapper in C#

Dapper is a popular and easy-to-use object-relational mapping (ORM) tool for .NET developers. It is lightweight, fast, and provides a simple way to execute SQL queries and map the results to strongly typed objects.

This article will discuss how to call a stored procedure using Dapper in C#. We will cover the following topics: (Reference)

1. What is a Stored Procedure?
2. How to Create a Stored Procedure in SQL Server
3. How to Call a Stored Procedure Using Dapper in C#

What is a Stored Procedure?

A stored procedure is a precompiled collection of stored SQL statements in the database. Stored procedures can encapsulate complex business logic, improve performance, and enforce security.

Stored procedures can take parameters and return results like a regular SQL query. In addition, stored procedures can perform other actions, such as modifying data, sending emails, or executing other stored procedures.

How to Create a Stored Procedure in SQL Server

Before we can call a stored procedure using Dapper, we must create a stored procedure in SQL Server. Here is an example of a simple stored procedure that takes a parameter and returns a result:

CREATE PROCEDURE GetCustomerByID
    @CustomerID INT
AS
BEGIN
    SELECT * FROM Customers WHERE CustomerID = @CustomerID
END

This stored procedure takes an integer parameter called CustomerID, and returns all columns from the Customers table where the CustomerID matches the input parameter.

How to Call a Stored Procedure Using Dapper in C#

Now that we have created a stored procedure in SQL Server, we can call it using Dapper in C#. Here is an example of how to do this:

using Dapper;
using System.Data.SqlClient;

string connectionString = "Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=True;";
int customerID = 1;

using (SqlConnection connection = new SqlConnection(connectionString))
{
    var parameters = new { CustomerID = customerID };
    var result = connection.Query<Customer>("GetCustomerByID", parameters, commandType: System.Data.CommandType.StoredProcedure);
}

Let's break this code down step by step:

1. We start by creating a connection to the database using the SqlConnection class. We pass in a connection string that specifies the server name, database name, and authentication method.
2. Next, we define a variable that represents the stored procedure's input parameter.
3. Inside the using block, we create an anonymous object that contains the stored procedure's input parameters. In this case, we only have one parameter called CustomerID, which we set to the value of customerID.
4. We then call the Query method on the connection object, passing in the name of the stored procedure ("GetCustomerByID"), the parameters object, and the command type (CommandType.StoredProcedure).
5. Finally, we store the result of the query in a variable called result. In this example, we assume that the Customer class has properties that match the columns in the Customers table.

Using Dapper to call stored procedures can be a powerful tool when working with databases. Here are a few tips to keep in mind when using Dapper to call stored procedures:

1. Use parameters to prevent SQL injection attacks: Always use parameters when passing data to a stored procedure to avoid SQL injection attacks. Dapper makes this easy by allowing you to pass in an anonymous object containing your parameter values.
2. Map results to strongly-typed objects: Dapper makes it easy to map the results of a stored procedure to strongly-typed things, which can make your code cleaner and more maintainable. You can do this by passing in the type of object you want to map to as a generic parameter to the Query method.
3. Use transactions to ensure data consistency: If you need to execute multiple queries as part of a single operation, you should use a transaction to ensure that the data remains consistent. Dapper supports transactions through the IDbTransaction interface.
4. Be aware of performance implications: While Dapper is generally very fast, calling a stored procedure can be slower than executing a raw SQL query due to the overhead of invoking the stored procedure. If performance is a concern, consider using a basic SQL query instead.

In conclusion, calling a stored procedure using Dapper in C# is a straightforward process that can help you write clean and efficient database code. Following the tips outlined above, ensure your code is secure, maintainable, and performs well.

Follow me on

C# Publication, LinkedIn, Instagram, Twitter, Dev. to, Pinterest, Substack, Hashnode, Write.as

Reference Docs