Dapper - a simple object mapper for .Net. Basically it's a NuGet library that can be added to any .NET project for database operations. It's architected to focus on the most important task of working with database tables instead of creating, modifying the database schema, tracking changes etc.
Dapper drastically reduces the database access code and work with any database: SQLite, SQL CE, Firebird, Oracle, MySQL, PostgreSQL and MS SQL Server. It uses underlying SQLConnection to work with the database, so it's easy to use a different database at the same time, use Dapper for MS SQL Server, Oracle or MySQL, etc. in the same application. But Dapper's key feature is performance.
Dapper is in production use at Stack Overflow. And that's something!
In this sample, we're going to create a very simple ASP.NET Core application which uses Dapper to access data.
using Dapper.Razor.Demo.Models.Attributes;
using System;
using System.ComponentModel.DataAnnotations;
namespace Dapper.Razor.Demo.Models
{
/// <summary>
/// Product model/entity
/// </summary>
[Serializable]
public class Product
{
[Key]
[Ignore] // ***Ignore Id property when inserting/updating entity because is AUTOINCREMENT
[Display(Name = "Product Id")]
public int Id { get; set; }
[Required]
[Display(Name = "Product Name")]
[StringLength(128, ErrorMessage = "Name should be 1 to 128 char in lenght")]
public string Name { get; set; }
[Required]
[Display(Name = "Model")]
[StringLength(64, ErrorMessage = "Name should be 1 to 64 char in lenght")]
public string Model { get; set; }
[Required]
[Display(Name = "Price")]
public int Price { get; set; }
[Display(Name = "Obsolete")]
public bool Obsolete { get; set; } = false;
[Display(Name = "Modified")]
public DateTime ModifiedDate { get; set; } = DateTime.UtcNow;
}
}Key code is in the base generic repository which implements IBaseRepository interface:
using System.Collections.Generic;
using System.Threading.Tasks;
namespace Dapper.Razore.Demo.Services.Repositories.Base
{
public interface IBaseRepository<TEntity> where TEntity : class
{
Task<int> DeleteAsync(int id);
Task<IEnumerable<TEntity>> GetAsync();
Task<TEntity> GetAsync(int id);
Task<int> InsertAsync(TEntity entity);
Task<int> UpdateAsync(TEntity entity);
}
}using Dapper.Razor.Demo.Models.Attributes;
using Microsoft.Data.Sqlite;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
namespace Dapper.Razore.Demo.Services.Repositories.Base
{
/// <summary>
/// Generic asynchronous base repository using Dapper
/// </summary>
/// <typeparam name="TEntity"></typeparam>
public class BaseRepository<TEntity> : IBaseRepository<TEntity> where TEntity : class
{
private readonly string _sqlConnectionString;
private readonly string _tableName;
protected BaseRepository(string sqlConnectionString, string tableName)
{
_sqlConnectionString = sqlConnectionString;
_tableName = tableName;
}
/// <summary>
/// Delete entity with id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task<int> DeleteAsync(int id)
{
using var connection = new SqliteConnection(_sqlConnectionString);
return await connection.ExecuteAsync($"DELETE FROM {_tableName} WHERE Id=@Id", new { Id = id }).ConfigureAwait(false);
}
/// <summary>
/// Gets all entities
/// </summary>
/// <returns></returns>
public async Task<IEnumerable<TEntity>> GetAsync()
{
using var connection = new SqliteConnection(_sqlConnectionString);
connection.Open();
return await connection.QueryAsync<TEntity>($"SELECT * FROM {_tableName}").ConfigureAwait(false);
}
/// <summary>
/// Get entity by id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task<TEntity> GetAsync(int id)
{
using var connection = new SqliteConnection(_sqlConnectionString);
var result = await connection.QuerySingleOrDefaultAsync<TEntity>($"SELECT * FROM {_tableName} WHERE Id=@Id", new { Id = id }).ConfigureAwait(false);
if (result == null)
{
throw new KeyNotFoundException($"{_tableName} with Id [{id}] not found");
}
return result;
}
/// <summary>
/// Insert new entity
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public async Task<int> InsertAsync(TEntity entity)
{
var insertQuery = CreateInsertQuery();
using var connection = new SqliteConnection(_sqlConnectionString);
return await connection.ExecuteAsync(insertQuery, entity).ConfigureAwait(false);
}
/// <summary>
/// Update existing entity
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public async Task<int> UpdateAsync(TEntity entity)
{
var updateQuery = CreateUpdateQuery();
using var connection = new SqliteConnection(_sqlConnectionString);
return await connection.ExecuteAsync(updateQuery, entity).ConfigureAwait(false);
}
/// <summary>
/// Gets entity properties names as string list, except those with [Ignore] attribute
/// </summary>
/// <param name="properties"></param>
/// <returns></returns>
private static List<string> GetPropertiesNames(IEnumerable<PropertyInfo> properties)
{
var result = new List<string>();
foreach (var prop in properties)
{
var attributes = prop.GetCustomAttributes(typeof(Ignore), false);
if (attributes.Length >= 1)
{
continue;
}
result.Add(prop.Name);
}
return result;
}
/// <summary>
/// Creates insert TSQL query
/// </summary>
/// <returns></returns>
private string CreateInsertQuery()
{
var result = new StringBuilder($"INSERT INTO {_tableName} (");
var entityProperties = typeof(TEntity).GetProperties();
var propertiesNames = GetPropertiesNames(entityProperties);
propertiesNames.ForEach(prop => result.Append($"[{prop}],"));
result.Remove(result.Length - 1, 1).Append(") VALUES (");
propertiesNames.ForEach(prop =>
{
result.Append($"@{prop},");
});
result.Remove(result.Length - 1, 1).Append(")");
return result.ToString();
}
/// <summary>
/// Creates update TSQL query
/// </summary>
/// <returns></returns>
private string CreateUpdateQuery()
{
var result = new StringBuilder($"UPDATE {_tableName} SET ");
var entityProperties = typeof(TEntity).GetProperties();
var propertiesNames = GetPropertiesNames(entityProperties);
propertiesNames.ForEach(property =>
{
if (!property.Equals("Id", System.StringComparison.InvariantCultureIgnoreCase))
{
result.Append($"{property}=@{property},");
}
});
result.Remove(result.Length - 1, 1);
result.Append(" WHERE Id=@Id");
return result.ToString();
}
}
}Repository implements additional method CreateTableIfNotExistsAsync which create Products data table in database if not exists:
using Custom.Configuration.Provider.Demo.Configuration;
using Dapper.Razor.Demo.Models;
using Dapper.Razore.Demo.Services.Repositories.Base;
using Microsoft.Data.Sqlite;
using Microsoft.Extensions.Options;
using System.Threading.Tasks;
namespace Dapper.Razor.Demo.Services.Repositories
{
/// <summary>
/// Products repository
/// </summary>
public class ProductRepository : BaseRepository<Product>, IProductRepository
{
private readonly SqlServerOptions _sqlServerOptions;
public ProductRepository(IOptions<SqlServerOptions> sqlServerOptions)
: base(sqlServerOptions.Value.SqlServerConnection, sqlServerOptions.Value.ProductsTableName)
{
_sqlServerOptions = sqlServerOptions.Value;
}
/// <summary>
/// Create Products data table if not exists
/// </summary>
/// <returns></returns>
public async Task CreateTableIfNotExistsAsync()
{
using var connection = new SqliteConnection(_sqlServerOptions.SqlServerConnection);
await connection.ExecuteAsync($"CREATE TABLE IF NOT EXISTS { _sqlServerOptions.ProductsTableName} (Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, Name TEXT NOT NULL, Model TEXT NOT NULL, Price INTEGER NOT NULL, Obsolete BOOLEAN DEFAULT(FALSE), ModifiedDate DATETIME DEFAULT CURRENT_TIMESTAMP)").ConfigureAwait(false);
}
}
}using Dapper.Razor.Demo.Models;
using Dapper.Razor.Demo.Services.Repositories;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Threading.Tasks;
using System;
namespace Dapper.Razor.Demo.Pages
{
/// <summary>
/// Index Page Model
/// </summary>
public class IndexModel : PageModel
{
readonly IProductRepository _productRepository;
public IndexModel(IProductRepository productRepository)
{
_productRepository = productRepository ?? throw new ArgumentNullException(nameof(productRepository));
_productRepository.CreateTableIfNotExistsAsync();
}
[BindProperty]
public IEnumerable<Product> Products { get; set; }
/// <summary>
/// Initializes any state needed for the page, in our case Products List
/// </summary>
public async Task OnGetAsync()
{
Products = await _productRepository.GetAsync().ConfigureAwait(false);
}
}
}For a Web Client we used ASP.NET Core Razor Pages which can make coding page-focused scenarios easier and more productive than using controllers and views.
Add new product:
Edit product:
Delete product:
This example demonstrate also the following functionalities:
- Razor Pages in ASP.NET Core
- Global Error Handling
- Logging with Serilog sink to file
- Asynchronous generic repository Pattern for Entity types
- SQLite Database Provider
- Visual Studio 2022 17.5.x or greater
- .NET SDK 7.0