Introduction
ASP.NET Core is a powerful framework for building web applications, and "Dapper" is a popular micro ORM (Object-Relational Mapping) that works well with ASP.NET Core for database operations. Dapper provides a simple way to interact with databases using raw SQL queries while mapping the results to objects.
Setting Up Dapper in ASP.NET Core
- Create an ASP.NET Core Project: Use Visual Studio or the .NET CLI to create a new ASP.NET Core project.
- Install Dapper: You can add Dapper to your project using NuGet Package Manager.
dotnet add package Dapper
- Set Up Database Connection: Configure a database connection in your
appsettings.json file.
"ConnectionStrings": {
"DefaultConnection": "YourDatabaseConnectionString"
}
Create Model
namespace APIDevelopmentUsingDapper.Model
{
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public string Category { get; set; }
public string CategoryName { get; set; }
public string CategoryDescription { get; set; }
public string CategoryCategory { get; set; }
public string CategoryCategoryName { get; set;}
}
}
Create IDapperDbConnection
using System.Data;
namespace APIDevelopmentUsingDapper.Interfaces
{
public interface IDapperDbConnection
{
public IDbConnection CreateConnection();
}
}
Create Dapper Service Connection
using APIDevelopmentUsingDapper.Interfaces;
using Microsoft.Data.SqlClient;
using System.Data;
namespace APIDevelopmentUsingDapper.DapperDbConnections
{
public class DapperDbConnection: IDapperDbConnection
{
public read-only string _connectionString;
public DapperDbConnection(IConfiguration configuration)
{
_connectionString = configuration.GetConnectionString("DefaultConnection");
}
public IDbConnection CreateConnection()
{
return new SqlConnection(_connectionString);
}
}
}
Create IProductRepository
using APIDevelopmentUsingDapper.Model;
namespace APIDevelopmentUsingDapper.Interfaces
{
public interface IProductRepository
{
Task<IEnumerable<Product>> GetAllProductsAsync();
Task<Product> GetProductByIdAsync(int id);
Task<int> CreateProductAsync(Product Product);
Task<bool> UpdateProductAsync(Product Product);
Task<bool> DeleteProductAsync(int id);
}
}
Product Repository Implementation
using APIDevelopmentUsingDapper.Interfaces;
using APIDevelopmentUsingDapper.Model;
using Dapper;
using System.Data;
namespace APIDevelopmentUsingDapper.Repositories
{
public class ProductRepository: IProductRepository
{
public IDapperDbConnection _dapperDbConnection;
public ProductRepository(IDapperDbConnection dapperDbConnection)
{
_dapperDbConnection = dapperDbConnection;
}
public async Task<IEnumerable<Product>> GetAllProductsAsync()
{
using(IDbConnection db = _dapperDbConnection.CreateConnection())
{
return await db.QueryAsync<Product>("SELECT * FROM Products");
}
}
public async Task<Product> GetProductByIdAsync(int id)
{
using(IDbConnection db = _dapperDbConnection.CreateConnection())
{
return await db.QueryFirstOrDefaultAsync<Product>("SELECT * FROM Products WHERE Id = @Id", new { Id = id });
}
}
public async Task<int> CreateProductAsync(Product Product)
{
using(IDbConnection db = _dapperDbConnection.CreateConnection())
{
const string query = "INSERT INTO Products (Name) VALUES (@Name); SELECT SCOPE_IDENTITY();";
return await db.ExecuteScalarAsync<int>(query, Product);
}
}
public async Task<bool> UpdateProductAsync(Product Product)
{
using(IDbConnection db = _dapperDbConnection.CreateConnection())
{
const string query = "UPDATE Products SET Name = @Name WHERE Id = @Id";
int rowsAffected = await db.ExecuteAsync(query, Product);
return rowsAffected > 0;
}
}
public async Task<bool> DeleteProductAsync(int id)
{
using(IDbConnection db = _dapperDbConnection.CreateConnection())
{
const string query = "DELETE FROM Products WHERE Id = @Id";
int rowsAffected = await db.ExecuteAsync(query, new { Id = id });
return rowsAffected > 0;
}
}
}
}
Product Repository Controller
using APIDevelopmentUsingDapper.Interfaces;
using APIDevelopmentUsingDapper.Model;
using APIDevelopmentUsingDapper.Repositories;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
namespace APIDevelopmentUsingDapper.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class ProductController : ControllerBase
{
private readonly IProductRepository _repository;
public ProductController(IProductRepository repository)
{
_repository = repository;
}
[HttpGet]
public async Task<ActionResult<IEnumerable<Product>>> GetProducts()
{
var Products = await _repository.GetAllProductsAsync();
return Ok(Products);
}
[HttpGet("{id}")]
public async Task<ActionResult<Product>> GetProduct(int id)
{
var Product = await _repository.GetProductByIdAsync(id);
if(Product == null)
{
return NotFound();
}
return Ok(Product);
}
[HttpPost]
public async Task<ActionResult<Product>> CreateProduct(Product Product)
{
int newProductId = await _repository.CreateProductAsync(Product);
Product.Id = newProductId;
return CreatedAtAction(nameof(GetProduct), new { id = newProductId }, Product);
}
[HttpPut("{id}")]
public async Task<IActionResult> UpdateProduct(int id, Product Product)
{
if(id != Product.Id)
{
return BadRequest();
}
bool updated = await _repository.UpdateProductAsync(Product);
if(!updated)
{
return NotFound();
}
return NoContent();
}
[HttpDelete("{id}")]
public async Task<IActionResult> DeleteProduct(int id)
{
bool deleted = await _repository.DeleteProductAsync(id);
if(!deleted)
{
return NotFound();
}
return NoContent();
}
}
}
Dependency Injection
using APIDevelopmentUsingDapper.App;
using APIDevelopmentUsingDapper.DapperDbConnections;
using APIDevelopmentUsingDapper.Interfaces;
using APIDevelopmentUsingDapper.Repositories;
using Microsoft.EntityFrameworkCore;
using Microsoft.OpenApi.Models;
var builder = WebApplication.CreateBuilder(args);
// Access configuration from the builder
var configuration = builder.Configuration;
// Configure database connection using Entity Framework Core
builder.Services.AddDbContext<ApplicationDbContext>(options =>
options.UseSqlServer(configuration.GetConnectionString("DefaultConnection")));
builder.Services.AddScoped<IProductRepository,ProductRepository>();
builder.Services.AddScoped<IDapperDbConnection, DapperDbConnection>();
builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new OpenApiInfo { Title = "Your API", Version = "v1" });
});
var app = builder.Build();
// Configure the HTTP request pipeline.
if(app.Environment.IsDevelopment())
{
app.UseSwagger();
app.UseSwaggerUI(c =>
{
c.SwaggerEndpoint("/swagger/v1/swagger.json", "Your API V1");
// Configure additional settings for SwaggerUI if needed
});
}
app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();
Output
![Dapper]()
GitHub Project Link
Conclusion
Integrating Dapper with ASP.NET Core offers a streamlined approach to interacting with databases using raw SQL queries while mapping results to objects. By following the steps to set up Dapper within your ASP.NET Core application—installing the package, defining models, creating a repository, and utilizing it within controllers, you establish a robust foundation for efficient database operations. This combination empowers you to leverage the simplicity of Dapper's micro ORM alongside the flexibility and power of ASP.NET Core for building robust web applications.