Provides an abstraction built directly on ADO.NET which falls within the Micro ORM space.
Overview
The Shuttle.Core.Data package provides a thin abstraction over ADO.NET by making use of the DbProviderFactories. Even though it provides object/relational mapping mechanisms it is in no way a fully fledged ORM.
Configuration
Connections
Connections may be added by providing all the required information:
The DatabaseContextService provides ambient access to the current IDatabaseContext instance. This is useful in situations where you do not want to pass the IDatabaseContext instance around.
Attach delegates to the above events should you wish to track ambient data changes.
void BeginScope()
IDatabaseContextFactory
In order to access a database we need a database connection. A database connection is represented by an IDatabaseContext instance that may be obtained by using an instance of an IDatabaseContextFactory implementation.
The DatabaseContextFactory implementation makes use of an IDbConnectionFactory implementation which creates a System.Data.IDbConnection by using the provider name and connection string, which is obtained from the registered connection name. An IDbCommandFactory creates a System.Data.IDbCommand by using an IDbConnection instance.
Before any database context can be created a scope must be started. This is typically done in the integration/application layer (controller/minimal API methods, message handlers). Nested scopes are not permitted as the ambient context will flow across any async methods.
var databaseContextService = provider.GetRequiredService<IDatabaseContextService>();
var databaseContextFactory = provider.GetRequiredService<IDatabaseContextFactory>();
using (databaseContextService.BeginScope()) // <-- will configure the scope (cannot be nested)
using (var databaseContext = databaseContextFactory.Create("connection-name"))
{
// database interaction
}
IQuery
An IQuery encapsulates a database query that can be executed:
void Prepare(IDbCommand command);
This should ensure that the given IDbCommand is configured for execution by setting the relvant command attributes and parameters.
This method is used to add a parameter to the query. The IColumn instance is used to define the column type and the value is the value that should be used for the parameter.
Query
The Query represents a Text command type:
public Query(string commandText, CommandType commandType = CommandType.Text)
You can then add parameters to the query:
query.AddParameter(new Column<Guid>("Id", DbType.Guid), new Guid('{75208260-CF93-454E-95EC-FE1903F3664E}'));
Column
Typically you would not want to create a Column each time you need it and these are also quite fixed. A column mapping can, therefore, by defined statically:
using System;
using System.Data;
using Shuttle.Core.Data;
namespace Shuttle.Ordering.DataAccess
{
public class OrderColumns
{
public static readonly Column<Guid> Id =
new Column<Guid>("Id", DbType.Guid);
public static readonly Column<string> OrderNumber =
new Column<string>("OrderNumber", DbType.String, 20);
public static readonly Column<string> OrderDate =
new Column<string>("OrderDate", DbType.DateTime);
public static readonly Column<string> CustomerName =
new Column<string>("CustomerName", DbType.String, 65);
public static readonly Column<string> CustomerEMail =
new Column<string>("CustomerEMail", DbType.String); // size omitted
}
}
There are quite a few options that you can set on the Column in order to represent your column properly.
Value
public T Value(DataRow row)
This will return the typed value of the specified column as contained in the passed-in DataRow.
IDatabaseGateway
The DatabaseGateway is used to execute IQuery instances in order return data from, or make changes to, the underlying data store. If there is no active open IDatabaseContext returned by the DatabaseContextService.Current an InvalidOperationException will be thrown.
The following sections each describe the methods available in the IDatabaseGateway interface.
Returns an IDataReader instance for the given query statement:
using (databaseContextFactory.Create("connection-name"))
{
var reader = await gateway.GetReaderAsync(new Query("select Id, Username from dbo.Member"));
}
Executes the given query and returns the number of rows affected:
using (databaseContextFactory.Create("connection-name"))
{
await gateway.ExecuteAsync(new Query("delete from dbo.Member where Username = 'mr.resistor'"));
}
Get the scalar value returned by the select query. The query shoud return only one value (scalar):
using (var databaseContext = databaseContextFactory.Create("connection-name"))
{
var username = await gateway.GetScalarAsync<string>(new Query("select Username from dbo.Member where Id = 10"));
var id = await gateway.GetScalarAsync<int>(new Query.Create("select Id from dbo.Member where Username = 'mr.resistor'") );
}
Returns a DataTable containing the rows returned for the given select query.
using (databaseContextFactory.Create("connection-name"))
{
var table = await gateway.GetDataTableAsync(new Query("select Id, Username from dbo.Member"));
}
Returns a single DataRow containing the values returned for a select statement that returns exactly one row:
using (databaseContextFactory.Create("connection-name"))
{
var row = await gateway.GetRowAsync(new Query("select Id, Username, EMail, DateActivated from dbo.Member where Id = 10") );
}
IDataRepository
An IDataRepository<T> implementation is responsible for returning a hydrated object. To this end you make use of the DataReposity<T> class that takes a IDatabaseGateway instance along with a IDataRowMapper<T> used to create the hydrated instance.
The following methods can be used to interact with your object type.
Uses the select clause represented by the IQuery instance to create a list of objects of type T. The select clause will need to select all the required columns and will, typically, return more than one instance.
This is similar to the FetchItems method but instead returns a list of MappedRow<T> instances. Uses the select clause represented by the IQuery instance to create a list of MappedRow instances of type T. The select clause will need to select all the required columns and will, typically, return more than one instance.
Similar to the FetchItem method but instead return a MappedRow<T> instance that is hydrated using the data returned from the select clause represented by the IQuery instance.
Returns true is the IQuery instance select clause returns an int scalar that equals 1; else returns false.
Query
The Query enables you to create any query using the native language structure:
var query = new Query("select UserName from dbo.Member where Id = @Id")
.AddParameter(new Column<Guid>("Id", DbType.Guid), new Guid('{75208260-CF93-454E-95EC-FE1903F3664E}'));
IDataRowMapper
You use this interface to implement a mapper for a DataRow that will result in an object of type T:
using System.Data;
using Shuttle.Core.Data;
using Shuttle.Process.Custom.Server.Domain;
namespace Shuttle.ProcessManagement
{
public class OrderProcessMapper : IDataRowMapper<OrderProcess>
{
public MappedRow<OrderProcess> Map(DataRow row)
{
var result = new OrderProcess(OrderProcessColumns.Id.Value(row))
{
CustomerName = OrderProcessColumns.CustomerName.Value(row),
CustomerEMail = OrderProcessColumns.CustomerEMail.Value(row),
OrderId = OrderProcessColumns.OrderId.Value(row),
InvoiceId = OrderProcessColumns.InvoiceId.Value(row),
DateRegistered = OrderProcessColumns.DateRegistered.Value(row),
OrderNumber = OrderProcessColumns.OrderNumber.Value(row)
};
return new MappedRow<OrderProcess>(row, result);
}
}
}
MappedRow
A MappedRow instance contains both a DataRow and the object that the DataRow mapped to.
This may be useful in situations where the DataRow contains more information than is available on the object. An example may be an OrderLine where the DataRow contains the OrderId column but the OrderLine object does not. In order to still be able to make that association it is useful to have both available.
IAssembler
An IAssembler implementation is used to create multiple mappings with as few calls as possible. An example may be where we perform two select queries; one to get 3 orders and another to get the order lines belonging to those 3 orders.
select OrderId, OrderNumber, OrderDate from dbo.Order where OrderId in (2, 6, 44)
Order Id
Order Number
Order Date
2
ORD-002
14 Feb 2016
6
ORD-006
24 Mar 2016
44
ORD-044
4 Apr 2016
select OrderId, Product, Quantity from dbo.OrderLine where OrderId in (2, 6, 44)
Order Id
Product
Quantity
2
Red Socks
2
2
Blue Socks
3
6
Sports Towel
1
6
Squash Racquet
1
6
Squash Ball
3
44
Vaughn's DDD Book
1
44
Shuttle.Sentinel License
5
Using a MappedData instance we can keep adding the MappedRow instances to the MappedData and then have the assembler return the three Order aggregates:
public class OrderAssembler : IAssembler<Order>
{
public IEnumerable<Order> Assemble(MappedData data)
{
var result = new List<Order>();
foreach (var orderRow in data.MappedRows<Order>())
{
var order = orderRow;
foreach (var orderLineRow in data.MappedRows<OrderLine>())
{
if (orderLineRow.Row["OrderId"].Equals(order.OrderId))
{
order.AddLine(orderLineRow);
}
}
result.Add(order);
}
return result;
}
}