NashTech Blog

Get in touch

In the realm of .NET development, efficient database access is crucial for building robust applications. While Entity Framework (EF) has long been a popular choice for ORM (Object-Relational Mapping), developers often seek alternatives that offer greater control and performance. One such tool that has gained significant traction is Dapper ORM.

What is Dapper? 

Dapper is an open-source, lightweight ORM developed by Stack Overflow. It’s designed to work with .NET and SQL databases efficiently, focusing on performance and simplicity. Unlike full-fledged ORMs like Entity Framework, Dapper emphasizes raw SQL query execution and object mapping. 

Why Choose Dapper? 

  1. Performance: Dapper is known for its speed. By minimizing overhead and utilizing ADO.NET directly, it outperforms most ORMs, especially when dealing with large datasets or high-volume transactions. 
  1. Simplicity: Dapper’s API is straightforward and easy to understand. It doesn’t abstract SQL away completely, which appeals to developers who prefer working closely with database queries. 
  1. Control: Developers have precise control over SQL queries, making it easier to optimize performance-critical parts of applications. Dapper maps query results directly to objects, enabling seamless integration with existing domain models. 

Key Features of Dapper 

  • Object Mapping: Dapper maps query results directly to objects or lists of objects, reducing manual mapping code.
  • Parameterized Queries: Protects against SQL injection attacks by automatically parameterizing queries.
  • Multiple Result Sets: Supports handling multiple result sets from a single query.
  • Stored Procedure Support: Easily maps objects to stored procedure results.
  • Async Support: Provides asynchronous methods for improved scalability and responsiveness.

Getting Started with Dapper 

Integrating Dapper into your project is straightforward: 

  1. Install Dapper: Add the Dapper package to your project using NuGet Package Manager or .NET CLI. 
dotnet add package Dapper
  1. Write Queries: Utilize Dapper’s extension methods to execute SQL queries directly against your database connection. 
code:
using (var connection = new SqlConnection(connectionString)) { 
var employees = connection.Query<Employee>("SELECT Id, Name, Department FROM Employees WHERE Department = @Dept", new { Dept = "IT" }); 
 // Use 'employees' object which is a list of Employee objects 
}
  1. Map Results: Dapper maps the query results to objects based on property names, making it intuitive to work with. 
  1. Create a Stored Procedure: Assume we have a stored procedure named GetEmployeesByDepartment in our SQL Server database. This stored procedure retrieves employees based on a specified department. 
CREATE PROCEDURE GetEmployeesByDepartment @Dept NVARCHAR(50)  
AS  
BEGIN  
SELECT Id, Name, Department  
FROM Employees  
WHERE Department = @Dept;  
END
  1. Execute the Stored Procedure Using Dapper: In your C# code, use Dapper’s Query method to execute the stored procedure and map the results to a list of objects. 
code:
using Dapper;
using System.Data;
using System.Data.SqlClient; // Or your preferred database provider

// Assume connectionString is your SQL Server connection string
string connectionString = "your_connection_string_here";

// Define a class to represent the Employee object
public class Employee
{
public int Id { get; set; }
public string Name { get; set; }
public string Department { get; set; }
}

public IEnumerable GetEmployeesByDepartment(string department)
{
using (var connection = new SqlConnection(connectionString))
{
// Execute the stored procedure using Dapper
var employees = connection.Query(
"GetEmployeesByDepartment", // Stored procedure name
new { Dept = department }, // Parameters for the stored procedure
commandType: CommandType.StoredProcedure // Specify command type
);
return employees;
}
}

In this example: 

  • We define a Employee class to map the columns returned by the stored procedure. 
  • Inside the GetEmployeesByDepartment method, we create a SqlConnection using the connection string. 
  • We use connection.Query<Employee> to execute the stored procedure GetEmployeesByDepartment, passing it a dynamic object (new { Dept = department }) with the parameter Dept corresponding to the stored procedure parameter @Dept. 
  • CommandType.StoredProcedure specifies that we are executing a stored procedure. 

When to Use Dapper? 

  • Microservices Architecture: Ideal for microservices where performance and control over database interactions are critical. 
  • Performance-Centric Applications: Applications dealing with large datasets or high-frequency database operations benefit from Dapper’s lightweight nature and speed. 
  • Legacy Systems Integration: Easily integrates with existing databases and stored procedures. 

Conclusion 

Dapper ORM offers a compelling alternative to traditional ORMs like Entity Framework, emphasizing performance, simplicity, and control. Its ability to execute raw SQL queries efficiently while mapping results to .NET objects makes it a favorite among developers who value performance optimization and direct database interaction. 

If you’re looking to enhance database access in your .NET projects without sacrificing performance or control, Dapper ORM is definitely worth exploring.

Scroll to Top