Harbin.DataAccess.DapperSimpleCRUD 1.0.5

dotnet add package Harbin.DataAccess.DapperSimpleCRUD --version 1.0.5
NuGet\Install-Package Harbin.DataAccess.DapperSimpleCRUD -Version 1.0.5
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Harbin.DataAccess.DapperSimpleCRUD" Version="1.0.5" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Harbin.DataAccess.DapperSimpleCRUD --version 1.0.5
#r "nuget: Harbin.DataAccess.DapperSimpleCRUD, 1.0.5"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install Harbin.DataAccess.DapperSimpleCRUD as a Cake Addin
#addin nuget:?package=Harbin.DataAccess.DapperSimpleCRUD&version=1.0.5

// Install Harbin.DataAccess.DapperSimpleCRUD as a Cake Tool
#tool nuget:?package=Harbin.DataAccess.DapperSimpleCRUD&version=1.0.5

Description

This is Data Access library based on Dapper, Dapper.SimpleCRUD, and DapperQueryBuilder. It implements 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 SimpleCRUD methods so you can easily get INSERT/UPDATE/DELETE as long as you decorate your entities with attributes like [Key] (on primary key columns) and [Required] (on primary key columns which are not identity columns) .
  • 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.DapperSimpleCRUD, add using Harbin.DataAccess.DapperSimpleCRUD.Connections, using Harbin.DataAccess.DapperSimpleCRUD.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 SimpleCRUD 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
    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 SimpleCRUD: Ms Sql Server, MySql, 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 SimpleCRUD):
var contactType = contactTypes.First();
contactType.ModifiedDate = DateTime.Now;
conn.GetReadWriteRepository<ContactType>().Update(contactType);

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

// Deleting using Generic Repository Pattern (using SimpleCRUD):
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

Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 was computed.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
.NET Core netcoreapp2.0 was computed.  netcoreapp2.1 was computed.  netcoreapp2.2 was computed.  netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard2.0 is compatible.  netstandard2.1 is compatible. 
.NET Framework net461 was computed.  net462 was computed.  net463 was computed.  net47 was computed.  net471 was computed.  net472 is compatible.  net48 was computed.  net481 was computed. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen40 was computed.  tizen60 was computed. 
Xamarin.iOS xamarinios was computed. 
Xamarin.Mac xamarinmac was computed. 
Xamarin.TVOS xamarintvos was computed. 
Xamarin.WatchOS xamarinwatchos was computed. 
Compatible target framework(s)
Additional computed target framework(s)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
1.0.5 295 8/14/2021