Peereflits.Shared.Databases

Hci.Common.Databases handles the "nitty-gritty" details of database communication by:

1. managing database connections;
2. handle transient errors;
3. logging of statements (at debug log level).

It contains no business/domain logic, and applies Command/Query separation (but has no dependency on Peereflits.Shared.Commanding).

• An (I)DatabaseQuery handles read/search queries (SELECT statements) and returns the result;
• An (I)DatabaseCommand handles database action (INSERT, UPDATE or DELETE statements) and returns no result.

The diagram below shows the logical structure of this library:

Examples

The examples below assume that the types are registered in the DI container.

Parameterless query

public  interface IGetCustomers 
{ 
  Task<IEnumerable<Customer>> Execute(); 
}

internal class GetCustomers: IGetCustomers
{
  internal const string Sql = @"
  SELECT t.Id
       , t.Name
       , ... other columns
    FROM dbo.Customers t
ORDER BY t.Name
";
  
  private readonly IDatabaseQuery query;
  
  public GetCustomersQuery(IDatabaseQuery query)
      => this.query = query;
 
  public async Task<IEnumerable<Customer>> Execute() 
      => await query.Execute<Customer>(Sql);
}

Note: IEnumerable<Customer> is the result type of the query.

Parameterised query

public interface IGetCustomer 
{
  Task<Customer> Execute(int id);
}

internal class GetCustomer: IGetCustomer
{
  internal const string Sql = @"
SELECT t.Id
     , t.Name
     , ... other columns
  FROM dbo.Customers t
 WHERE t.Id = @Id
";

  private readonly IDatabaseQuery database;

  public GetCustomerQuery(IDatabaseQuery database)
      => this.database = database;

  public async Task<Customer> Execute(int id)
  {
    if(Id <= 0) 
    { 
      throw new ArgumentOutOfRangeException($"{nameof(id)} cannot be zero or less.", nameof(id));
    }

    IEnumerable<Customer> query = await database.Execute<Customer>(Sql, new { Id = id });
    return query.SingleOrDefault();
  }
}

Parameterised command

public class CreateCustomerParameters
{
  // All properties are parameters of the command
  public string Name { get; set; }
  ... other properties
}

public interface ICreateCustomer
{
  Task Execute(CreateCustomerParameters parameters);
}

internal class CreateCustomer: ICreateCustomer
{
  internal const string Sql = @"
INSERT INTO dbo.Customers
( Name
, ... other columns
)
VALUES
( @Name
, ... other parameters
);
";

  private readonly IDatabaseCommand database;

  public CreateCustomer(IDatabaseCommand database)
      => this.database = database;

  public async Task Execute(CreateCustomerParameters parameters)
  {
    if (parameters == null) 
    { 
      throw new ArgumentNullException(nameof(parameters)); 
    }
    // Validate all other parameters

    await database.Execute(provider.Execute(),
                           Sql,
                           new
                           {
                               parameters.Name,
                               ... other properties that map to column names
                           }
                          );
  }
}

Version support

This library supports the following .NET versions:

1. .NET 8.0
2. .NET 9.0
3. .NET 10.0

---

© No copyright applicable
® "Peereflits" is my codename.

---