Accessing and manipulating data in C# applications can be challenging, but with the right tool, you can save time and improve performance. In this blog post, we'll explore how Dapper ORM, together with stored procedures, can be exactly what you need to efficiently work with data in MSSQL databases.
To demonstrate the usage of Dapper for C# data access, I created a solution with 2 projects: a Class Library and a SQL Server Database Project. The Class Library is targeting .NET, while the target platform of the SQL project is SQL Server 2019.
SQL Server project details
I'll start with the deepest layer, the SQL Server project. In that project I’ve created one table named Product:
Product table design with T-SQL script
Stored Procedures are used for data manipulation. This blog will demonstrate both the stored procedure and the C# method (later on) that only retrieves data; the other CRUD operations work similarly.
spProduct_GetAll:
CREATE PROCEDURE [dbo].[spProduct_GetAll]
AS
BEGIN
SELECT [Id], [Name], [Description], [StockQuantity] FROM [dbo].[Product]
END
The entire database structure looks like this:
SQL Server Database Project structure
Class Library project details
This project requires the installation of 3 NuGet packages. The first 2 packages are used for connecting to and manipulating the SQL database, while the third package is used to read the connectionString from the appsettings.json file.
A model simply reflects the schema of the table in the database:
public class ProductModel
{
public int Id { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public int StockQuantity { get; set; }
}
This model will be needed later when we retrieve data using Dapper and stored procedures, to map the data.
The following code in ProductManager.cs demonstrates CRUD operations on the Product table:
using Dapper;
using DataAccess.Models;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System.Data;
namespace DataAccess.Managers;
public class ProductManager(IConfiguration config)
{
private readonly string _connString = config.GetConnectionString("Default")!;
public async Task<IEnumerable<ProductModel>> GetProducts()
{
using IDbConnection connection = new SqlConnection(_connString);
var data = await connection.QueryAsync<ProductModel>("spProduct_GetAll", commandType: CommandType.StoredProcedure);
return data;
}
...
}
If we need to retrieve data (GET methods), we will use Dapper's `QueryAsync` function, which returns Task<IEnumerable<ProductModel>>. The first parameter is the name of the stored procedure, followed by optional parameters that the procedure expects, and finally, we specify the commandType to be StoredProcedure.
If we don't need to retrieve data (POST, PUT, and DELETE methods), we will use Dapper's ExecuteAsync function instead of QueryAsync.
Each of these methods consists of only 2-3 lines of code.
Pros & Cons of using Dapper for data access
Advantages:
Speed: Dapper is approximately as fast as ADO.NET.
Easier for SQL developers to work with.
Not a black box: it's transparent - what we ask from SQL is what we get; the SQL command we write will be executed and give us the desired result, without unexpected overhead in transport.
Designed for loose coupling, especially when used in a class library project, allowing it to be referenced from other project types.
SQL is excellent at storing, retrieving, sorting, and filtering data, so we should let it do its job as much as possible. With Dapper, we can offload part of the work to SQL and then map the result to C# models.
If there are any data access problems, we'll know exactly which query is problematic, so we can fix it to perform better.
Easy to debug: if an exception occurs, the log message immediately identifies the problematic query, facilitating prompt resolution.
Better database security: the database can be locked down so that only stored procedures can be called, for example.
Simple (data access can be implemented with just one line of code).
No auto-generated code in C# - which sometimes can be difficult to understand and potentially dangerous from a performance standpoint later in production environment.
Dapper forces you to learn SQL.
Disadvantages:
Slower development phase – you must know SQL.
You must manually update your queries and models if there are changes to the database tables.
There is no code-first or database-first approach, no LINQ syntax, no migrations, etc.
Dapper doesn't provide abstraction layers like some ORM tools such as Entity Framework, making writing complex queries and mapping relationships between entities more challenging.
Dapper doesn't offer full support for complex relationships between tables and complex queries involving multiple tables or data aggregations.
Unlike some ORM tools, Dapper doesn't provide automatic object lifecycle management or object state tracking, which may require additional code to manage objects in the application.
Dapper requires more manual effort in mapping data from query results to C# model objects, especially when working with more complex data structures.
As with any data access tool, improper use of Dapper can lead to performance issues or security vulnerabilities in the application.
When to use Dapper:
When high performance data access is needed, especially in applications with heavy loads. For a detailed comparison of performance between Dapper and Entity Framework, including examples with complex queries and large datasets, check out this link: dev.to/xaberue/mechanisms-and-performance-when-querying-data-to-sqlserver-from-c-3ech. This article provides an in-depth analysis and benchmarks to help you make an informed decision.
When you want greater control over SQL queries and want to avoid the complex abstractions offered by other ORM tools.
When working on a project where SQL knowledge is already present in the team and you want to leverage that expertise.
When developing microservices architectures where implementation simplicity is crucial, and Dapper provides a simple and efficient way to interact with the database.
When there is a simple data model in a project.
When efficient handling of large volumes of data or complex queries is needed.
When to avoid using Dapper:
When developing an application with complex business logic and intricate relationships between entities, where ORM tools with richer functionalities would be a better choice.
When there is a need to quickly build a prototype or MVP (Minimum Viable Product), where development speed is more important than performance.
When there is a need to support multiple platforms or databases.
I believe Dapper is a highly useful tool for data access and a valuable addition to any developer's toolbox. What appeals to me most is the clear separation between the C# and SQL aspects.
Despite the potential need for additional effort, especially when dealing with complex queries or intricate data mappings in C#, I find that this division ultimately fosters a more organized and manageable development process. Moreover, I particularly appreciate its ability to be used in isolation, such as in a class library project, allowing UI projects to remain oblivious to data access specifics and simply reference the data access project and utilize it.
Throughout my career, I have mainly relied on EF Core for data access, but I'm keen to incorporate Dapper more in the future, as its advantages are evident. While Dapper provides a fast and efficient solution for data access in C# applications, it's important to consider its strengths and limitations when choosing a development tool.
Dapper is particularly useful in situations where high performance is required and direct execution of SQL queries without abstraction layers is preferred. However, developers should be aware of Dapper's limitations regarding complex queries, entity relationship mapping, and object lifecycle management. Proper use of Dapper requires a good understanding of SQL and C# programming, as well as careful planning and testing to avoid potential pitfalls and ensure an efficient and secure application.
This website uses cookies to provide a better user experience, personalization of content and ads, as well as the integration of social media and analysis of traffic on the internet. By clicking on 'Accept', you consent to the placing of these cookies.
