Harbin.DataAccess 1.0.2

Description

This is Data Access library based on Dapper, Dapper.FastCRUD, and DapperQueryBuilder. It implement Repositories (Generic Repository Pattern) and helps to manage connections to distributed databases and/or read-replicas.

Design Principles

Harbin Database Library was designed based on the following ideas:

• Wrappers around IDbConnection, but which also implement IDbConnection so can be used as regular connections.
• "Bare metal", does not try to "hide" ADO.NET or Dapper, so you can use the full power of Dapper, IDbTransactions, etc.
• Easy to manage multiple database connections, either to different databases (e.g. distributed databases, microservices, heterogeneous databases) or to differentiate masters vs read-only replicas.
• ReadOnlyDbConnection, ReadOnlyDbConnection<DB>, ReadWriteDbConnection, ReadWriteDbConnection<DB>.
• Those classes respectively can build ReadRepository<TEntity> or ReadWriteRepository<TEntity> which are Generic Repositories (Generic Repository Patter) for your Entities.
• ReadRepository<TEntity> includes facades to Dapper Query Methods, and also facades to DapperQueryBuilder methods.
• ReadWriteRepository<TEntity> includes facades to Dapper FastCRUD methods so you can easily get INSERT/UPDATE/DELETE as long as you decorate your entities with attributes like [Key] and [DatabaseGenerated(DatabaseGeneratedOption.Identity)] .
• Repositories (ReadRepository / ReadWriteRepository) and Connections (ReadConnection / ReadWriteDbConnection) can be extended either through method extensions or through inheritance.
• By keeping Queries on ReadRepository and DbCommands on ReadWriteRepository you're isolating your Queries and Commands (CQRS).
• You can unit test your application even if it depends on ReadConnection, ReadWriteDbConnection, ReadRepository, ReadWriteRepository, etc. They all can be "faked" using inheritance or a mocking library.

Installation

Just install nuget package Harbin.DataAccess, add using Harbin.DataAccess.Connection, using using Harbin.DataAccess.Repositories, and start using (see examples below).
See documentation below, or more examples in unit tests.

Documentation

Define your Entities and decorate attributes on Primary Keys and Identity columns

Generic Repositories by default use Dapper FastCRUD so you have to describe your entities accordingly.

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

// AdventureWorks is a sample database which uses schemas for grouping tables
// If table uses default schema and table name matches class name you don't need the [Table] attribute
[Table("ContactType", Schema = "Person")]
public partial class ContactType
{
    [Key] // column is part of primary key
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)] // column is auto-increment
    public int ContactTypeId { get; set; }

    public DateTime ModifiedDate { get; set; }

    public string Name { get; set; }

}

Basic usage: Creating a connection, Querying, Inserting and Updating

// This is basically a wrapper around your own IDbConnection 
// (which can be any database supported by Dapper FastCRUD: LocalDb, Ms Sql Server, MySql, SqLite, PostgreSql)
// You can use ReadDbConnection or ReadWriteDbConnection which is a derived class
var conn = new ReadWriteDbConnection(new System.Data.SqlClient.SqlConnection(connectionString));

// ReadDbConnection (and derived ReadWriteDbConnection) has Dapper facades for all Dapper Query methods 
// (including Async methods), no need to add "using Dapper".
var contactTypes = conn.Query<ContactType>("SELECT * FROM Person.ContactType");

// Since we have a ReadWriteDbConnection we can get a Generic ReadWriteRepository for 
// our Entities (IReadWriteRepository<TEntity>), which offers Update/Insert/Delete methods:

// Updating a record using Generic Repository Pattern (using FastCRUD):
var contactType = contactTypes.First();
contactType.ModifiedDate = DateTime.Now;
conn.GetReadWriteRepository<ContactType>().Update(contactType);

// Adding a new record using Generic Repository Pattern (using FastCRUD):
var newContactType = new ContactType() { Name = "NewType", ModifiedDate = DateTime.Now };
conn.GetReadWriteRepository<ContactType>().Insert(newContactType);

// Deleting using Generic Repository Pattern (using FastCRUD):
conn.GetReadWriteRepository<ContactType>().Delete(newContactType);

// Both with ReadWriteDbConnection or ReadDbConnection we can get a IReadRepository<TEntity> 
// which has some helpers to Query our table:
var all = conn.GetReadRepository<ContactType>().QueryAll();
all = conn.GetReadRepository<ContactType>().Query("SELECT * FROM Person.ContactType WHERE ContactTypeId < 5");
all = conn.GetReadRepository<ContactType>().Query("WHERE ContactTypeId < 5"); // just syntactic sugar to automatically fill the table/schema according to class attributes

// DapperQueryBuilder allows to dynamically append new conditions and is also safe against sql-injection 
// (parameters can be described using string interpolation and it's converted into Dapper DynamicParams)
var dynamicQuery = conn.GetReadRepository<ContactType>().QueryBuilder();
dynamicQuery.Where($"ContactTypeId < 5");
dynamicQuery.Where($"ModifiedDate < GETDATE()");
string search = "%Sales%";
dynamicQuery.Where($"Name LIKE {search}");
all = dynamicQuery.Query();

Adding reusable Queries and Commands using Extension Methods

public static class PersonQueryExtensions
{
  public static IEnumerable<Person> QueryRecentEmployees(this IReadDbRepository<Person> repo)
  {
    return repo.Query("SELECT TOP 10 * FROM [Person].[Person] WHERE [PersonType]='EM' ORDER BY [ModifiedDate] DESC");
  }
}

public static class PersonDbCommandExtensions
{
  public static void UpdateCustomers(this IReadWriteDbRepository<Person> repo)
  {
    repo.Execute("UPDATE [Person].[Person] SET [FirstName]='Rick' WHERE [PersonType]='EM' ");
  }
}
public void Sample()
{
  var repo = conn.GetReadWriteRepository<Person>();
  
  // Generic Repository methods
  repo.Insert(new Person() { FirstName = "Rick", LastName = "Drizin" });
  var allPeople = repo1.QueryAll();
  
  // Custom Extensions
  repo.UpdateCustomers();
  var recentEmployees = repo.QueryRecentEmployees();
}

Adding reusable Queries and Commands using inheritance

public class PersonRepository : ReadWriteDbRepository<Person>
{
  public PersonRepository(IReadWriteDbConnection db) : base(db)
  {
  }
  public virtual IEnumerable<Person> QueryRecentEmployees()
  {
    return this.Query("SELECT TOP 10 * FROM [Person].[Person] WHERE [PersonType]='EM' ORDER BY [ModifiedDate] DESC");
  }
  public virtual void UpdateCustomers()
  {
    this.Execute("UPDATE [Person].[Person] SET [FirstName]='Rick' WHERE [PersonType]='EM' ");
  }
}
public void Sample()
{
  // Registers that GetReadWriteRepository<Person>() should return a derived type PersonRepository
  ReadWriteDbConnection.RegisterRepositoryType<Person, PersonRepository>();

  var conn = new ReadWriteDbConnection(new System.Data.SqlClient.SqlConnection(connectionString));  
  
  // we know exactly what subtype to expect
  var repo = (PersonRepository) conn.GetReadWriteRepository<Person>();
  
  repo.UpdateCustomers();
  var recentEmployees = repo.QueryRecentEmployees();
}

See full documentation here

License

MIT License