Learn How to Use Stored Procedures with Parameters in Dapper

Dapper, a lightweight Object-Relational Mapping (ORM) library, provides a simple and efficient way to interact with databases in .NET applications. In this article, we will explore how to harness the power of stored procedures with parameters using Dapper, allowing for more organized and secure database operations.

Setting up the SqlConnection:

using (var connection = new SqlConnection(connectionString)) {
    // ... (Code to be explained further)
}

Setting up Parameters with DynamicParameters:

To pass parameters to a stored procedure, we use the DynamicParameters class provided by Dapper. In the example, we add a parameter named "id" with a value of 1.

DynamicParameters parameters = new DynamicParameters();
parameters.Add("id", 1);

Executing the Stored Procedure:

Now, it's time to execute the stored procedure using the QuerySingleOrDefault method. This method is designed to return a single result, or default if no result is found, which is suitable for scenarios where a single record is expected.

var customer = connection.QuerySingleOrDefault<Employee>("procedurename", parameters, commandType: CommandType.StoredProcedure);

Here, "procedurename" represents the name of the stored procedure to be executed. Replace it with the actual name of your stored procedure.

Mapping Results to an Object:

Dapper simplifies the process of mapping database results to objects. In this example, the results of the stored procedure execution are mapped to an object of type Employee. Adjust the type accordingly based on the structure of your stored procedure result set and your application's needs.

Here is the complete code for the method

        public ActionResult Index()
        {
            //Set up DynamicParameters object to pass parameters  
            using (var connection = new SqlConnection(connectionString))
            {
                //Set up DynamicParameters object to pass parameters  
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("id", 1);

                //Execute stored procedure and map the returned result to a Customer object  
                var customer = connection.QuerySingleOrDefault<Employee>("procedurename", parameters, commandType: CommandType.StoredProcedure);
            }

            
            return View();
        }

Procedure used for Demo

Create proc GetEmployeeById
(
@Id INT
)
AS
BEGIN
Select * from Employees WHERE  Id=@Id
END

Conclusion:

By leveraging Dapper's capabilities, we've learned how to use stored procedures with parameters to interact with databases efficiently. This approach enhances code organization, improves security by preventing SQL injection, and allows for seamless integration of database operations in .NET applications.

Incorporating stored procedures with Dapper not only streamlines database access but also contributes to a cleaner and more maintainable codebase. This knowledge is valuable for developers aiming to build robust and scalable applications in the .NET ecosystem.

So this is how we can use  stored procedure with parameters in dapper using asp.net.