(Third-party library) Harbin.DataAccess - Generic Repository Pattern using Dapper, Dapper FastCRUD and DapperQueryBuilder

[Drizin]

Harbin.DataAccess implements Generic Repositories (Generic Repository Pattern) using "raw" Dapper, Dapper.FastCRUD, and DapperQueryBuilder.

• The Inserts/Updates/Deletes are automatically generated by Dapper FastCRUD (class should be decorated with attributes for keys/autoincrement columns)
• Supports FastCRUD bulk update, bulk delete, and async methods.
• Repositories can be extended with custom Queries and custom Commands (allows/promotes CQRS separation)
• Queries can be defined manually (raw sql) or using Dapper FastCRUD syntax
• Dynamic Queries (dynamic number of conditions) can be built using DapperQueryBuilder
• There are Read-only Connection Wrappers and Read-only Repositories, so it's easy to use read-replicas (or multiple databases)
• Support for ADO.NET transactions
• Support for mocking Queries and Commands

Sample Insert/Update/Delete (Generic Repository - this uses Dapper FastCRUD):

var conn = new ReadWriteDbConnection(new System.Data.SqlClient.SqlConnection(connectionString));

// Get a IReadWriteRepository<TEntity> which offers some helpers to Query and Write our table:
var repo = conn.GetReadWriteRepository<ContactType>();

var contactType = repo.QueryAll().First();

// Updating a record
contactType.ModifiedDate = DateTime.Now;
repo.Update(contactType);

// Adding a new record
var newContactType = new ContactType() { Name = "NewType", ModifiedDate = DateTime.Now };
repo.Insert(newContactType);
// FastCRUD will automatically update the auto-generated columns back (identity or guid)

// Deleting a record
repo.Delete(newContactType);

[Table("ContactType", Schema = "Person")]
public class ContactType
{
    [Key] // if column is part of primary key
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)] // if column is auto-increment
    public int ContactTypeId { get; set; }

    public DateTime ModifiedDate { get; set; }

    public string Name { get; set; }
}

Sample Dynamic Queries:

var conn = new ReadDbConnection(new System.Data.SqlClient.SqlConnection(connectionString));


// Get a IReadRepository<TEntity> which offers some helpers to Query our table:
var repo = conn.GetReadRepository<Person>();

// Custom Query (pure Dapper)
var people = repo.Query("SELECT * FROM Person.Person WHERE PersonType = @personType ", new { personType = "EM" } );

// DapperQueryBuilder allows to dynamically append conditions using string interpolation (but injection-safe)

string type = "EM"; string search = "%Sales%";

var dynamicQuery = repo.QueryBuilder(); // if not specified query is initialized with "SELECT * FROM tablename"
dynamicQuery.Where($"PersonType = {type}");
dynamicQuery.Where($"ModifiedDate >= {DateTime.Now.AddDays(-1)} ");
dynamicQuery.Where($"Name LIKE {search}");

// Result is SELECT * FROM [Person].[Person] WHERE PersonType = @p0 AND ModifiedDate >= @p1 AND Name LIKE @p2
var people = dynamicQuery.Query();

Extending Repositories (adding custom 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, so we can just cast.
  var repo = (PersonRepository) conn.GetReadWriteRepository<Person>();
  
  repo.UpdateCustomers();
  var recentEmployees = repo.QueryRecentEmployees();
}

Full documentation here.

There are also sample scripts which extract the schema of an existing SQL Server database and generate POCO classes (with attributes [Key], [DatabaseGenerated(DatabaseGeneratedOption.Identity)], etc.) based on the schema.

[denniscpolley]

This is what I have been searching for, THANK YOU! How would you use your api with a UOW pattern?

[Drizin]

Hi @denniscpolley , I'm glad you liked. Would you mind asking this question in Harbin issues? I'd like to keep the technical discussion there, so it can benefit other users. Thanks!