What is Dapper
Dapper is an open-source, lightweight Object-Relational Mapping (ORM) library for .NET. It provides a simple and efficient way to interact with databases by mapping database records to strongly typed objects and vice versa. Dapper focuses on raw SQL queries and avoids the complexity and overhead of traditional ORMs, making it a popular choice for developers who want more control over their data access while still benefiting from object mapping.
Key features of Dapper:
- Performance: Dapper is known for its excellent performance. It uses a minimal amount of code to map database records to objects, which results in faster execution times compared to some other ORMs.
- Simplicity: Dapper is designed to be simple and easy to use. It uses extension methods and query extensions that feel natural to developers familiar with SQL.
- Customization: Dapper allows you to write your own SQL queries, providing a high level of control over database interactions. This can be particularly useful for optimizing queries or working with complex database operations.
- Support for Multiple Database Providers: Dapper supports a wide range of database providers, including SQL Server, MySQL, PostgreSQL, SQLite, and more.
- Parameterization: Dapper handles parameterized queries to help prevent SQL injection vulnerabilities.
- Mapping: Dapper provides automatic mapping of query results to strongly typed objects. You can map query results to classes, structs, and anonymous types.
- Query Composition: Dapper supports query composition, allowing you to build complex queries by combining smaller query components.
- Raw SQL Queries: While Dapper encourages the use of strongly typed objects, it also supports executing raw SQL queries and mapping the results to objects.
- Stored Procedures: Dapper supports executing stored procedures and mapping the results to objects.
Dapper is a versatile and lightweight library that is particularly well-suited for scenarios where performance and control over SQL queries are priorities. It’s a popular choice for developers who want to strike a balance between the convenience of ORMs and the flexibility of raw SQL.
We’ll understand the example of performing CRUD (Create, Read, Update, Delete) operations using Dapper in a .NET Core Web Application.
- Create a .NET Core Web Application:
Use the following command to create a new .NET Core Web Application:
dotnet new web -n DapperCRUDDemo
cd DapperCRUDDemo- Install Dapper and Database Provider:
Install the necessary NuGet packages for Dapper and your preferred database provider. For example, if you’re using SQL Server, you can use the following packages:
dotnet add package Dapper
dotnet add package Microsoft.Data.SqlClient- Create a Model:
Define a model class that represents the entity you want to perform CRUD operations on. Let’s say we’re working with a simpleProductentity:
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
}- Configure Database Connection:
In yourappsettings.jsonfile, add your database connection string:
{
"ConnectionStrings": {
"DefaultConnection": "your-connection-string"
},
// Other settings
}Replace "your-connection-string" with your actual database connection string.
- Create Repository and Data Access Layer:
Create a data access layer to perform CRUD operations using Dapper. Create aProductRepository.csclass:
using System.Collections.Generic;
using System.Data;
using Dapper;
using Microsoft.Extensions.Configuration;
using System.Data.SqlClient;
using System.Threading.Tasks;
public class ProductRepository : IProductRepository
{
private readonly string _connectionString;
public ProductRepository(IConfiguration configuration)
{
_connectionString = configuration.GetConnectionString("DefaultConnection");
}
public async Task<IEnumerable<Product>> GetProductsAsync()
{
using IDbConnection dbConnection = new SqlConnection(_connectionString);
dbConnection.Open();
return await dbConnection.QueryAsync<Product>("SELECT * FROM Products");
}
public async Task<Product> GetProductByIdAsync(int id)
{
using IDbConnection dbConnection = new SqlConnection(_connectionString);
dbConnection.Open();
return await dbConnection.QueryFirstOrDefaultAsync<Product>("SELECT * FROM Products WHERE Id = @Id", new { Id = id });
}
public async Task<int> InsertProductAsync(Product product)
{
using IDbConnection dbConnection = new SqlConnection(_connectionString);
dbConnection.Open();
const string sql = @"INSERT INTO Products (Name, Price) VALUES (@Name, @Price);
SELECT CAST(SCOPE_IDENTITY() AS INT)";
return await dbConnection.ExecuteScalarAsync<int>(sql, product);
}
public async Task<bool> UpdateProductAsync(Product product)
{
using IDbConnection dbConnection = new SqlConnection(_connectionString);
dbConnection.Open();
const string sql = @"UPDATE Products SET Name = @Name, Price = @Price WHERE Id = @Id";
return await dbConnection.ExecuteAsync(sql, product) > 0;
}
public async Task<bool> DeleteProductAsync(int id)
{
using IDbConnection dbConnection = new SqlConnection(_connectionString);
dbConnection.Open();
const string sql = @"DELETE FROM Products WHERE Id = @Id";
return await dbConnection.ExecuteAsync(sql, new { Id = id }) > 0;
}
}- Create Controller:
Create a controller to handle CRUD operations. For example, create aProductsController.csclass:
using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
private readonly IProductRepository _productRepository;
public ProductsController(IProductRepository productRepository)
{
_productRepository = productRepository;
}
[HttpGet]
public async Task<IActionResult> GetProducts()
{
var products = await _productRepository.GetProductsAsync();
return Ok(products);
}
[HttpGet("{id}")]
public async Task<IActionResult> GetProduct(int id)
{
var product = await _productRepository.GetProductByIdAsync(id);
if (product == null)
{
return NotFound();
}
return Ok(product);
}
[HttpPost]
public async Task<IActionResult> CreateProduct(Product product)
{
var id = await _productRepository.InsertProductAsync(product);
return CreatedAtAction(nameof(GetProduct), new { id }, product);
}
[HttpPut("{id}")]
public async Task<IActionResult> UpdateProduct(int id, Product product)
{
if (id != product.Id)
{
return BadRequest();
}
var success = await _productRepository.UpdateProductAsync(product);
if (!success)
{
return NotFound();
}
return NoContent();
}
[HttpDelete("{id}")]
public async Task<IActionResult> DeleteProduct(int id)
{
var success = await _productRepository.DeleteProductAsync(id);
if (!success)
{
return NotFound();
}
return NoContent();
}
}- Run the Application:
Start the application using the following command:
dotnet runYou can use tools like curl, Postman, or a browser to interact with the API endpoints (GET, POST, PUT, DELETE) at the /api/products route.
This example demonstrates how to perform CRUD operations using Dapper in a .NET Core Web Application. Make sure to implement proper error handling, input validation, and other necessary features based on your application requirements.
