Shuttle.Core.Data
17.0.0
Prefix Reserved
dotnet add package Shuttle.Core.Data --version 17.0.0
NuGet\Install-Package Shuttle.Core.Data -Version 17.0.0
<PackageReference Include="Shuttle.Core.Data" Version="17.0.0" />
paket add Shuttle.Core.Data --version 17.0.0
#r "nuget: Shuttle.Core.Data, 17.0.0"
// Install Shuttle.Core.Data as a Cake Addin #addin nuget:?package=Shuttle.Core.Data&version=17.0.0 // Install Shuttle.Core.Data as a Cake Tool #tool nuget:?package=Shuttle.Core.Data&version=17.0.0
Shuttle.Core.Data
PM> Install-Package Shuttle.Core.Data
Provides an abstraction built directly on ADO.NET which falls within the Micro ORM space.
Overview
NOTE: Since a database connection is represented by a IDatabaseContext
instance it is important to understand that this instance is not thread-safe. It is therefore important to ensure that the IDatabaseContext
instance is not shared between threads. See the DatabaseContextScope
to ensure thread-safe database context flow.
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:
services.AddDataAccess(builder =>
{
builder.AddConnection(name, providerName, connectionString);
});
A connection may also be added by omitting the connectionString
, in which case it will be read from the ConnectionStrings
section:
services.AddDataAccess(builder =>
{
builder.AddConnectionString(name, providerName);
});
Options
The relevant options may be set using the builder:
services.AddDataAccess(builder =>
{
builder.Options.CommandTimeout = timeout;
builder.Options.DatabaseContextFactory.DefaultConnectionStringName = "connection-string-name";
});
The default JSON settings structure is as follows:
{
"Shuttle": {
"DataAccess": {
"CommandTimeout": 25,
"DatabaseContextFactory":
{
"DefaultConnectionStringName": "connection-string-name",
}
}
}
}
DatabaseContextScope
The DatabaseContextService
contains a collection of the DatabaseContext
instances created by IDatabaseContextFactory
. However, since the DatabaseContextService
is a singleton the same collection will be used in all thread contexts. This includes not only the same execution context, but also "peered" execution context running in parallel.
To enable an individual execution/thread context-specific collection which also enables async context flow wrap the initial database context creation in a new DatabaseContextScope()
:
using (new DatabaseContextScope())
{
// database interaction
})
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.
var databaseContextFactory = provider.GetRequiredService<IDatabaseContextFactory>();
using (var databaseContext = databaseContextFactory.Create("connection-name"))
{
// database interaction
}
// or, in async/await implementations
using (new DatabaseContextScope())
using (var databaseContext = databaseContextFactory.Create("connection-name"))
{
// database interaction that will flow across threads
}
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.
IQuery AddParameter(IColumn column, object value);
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.
GetReaderAsync
Task<IDataReader> GetReaderAsync(IQuery query, CancellationToken cancellationToken = default);
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"));
}
ExecuteAsync
Task<int> ExecuteAsync(IQuery query, CancellationToken cancellationToken = default);
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'"));
}
GetScalarAsync
Task<T> GetScalarAsync<T>(IQuery query, CancellationToken cancellationToken = default);
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'") );
}
GetDataTableAsync
Task<DataTable> GetDataTableAsync(IQuery query, CancellationToken cancellationToken = default);
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"));
}
GetRowsAsync
Task<IEnumerable<DataRow>> GetRowsAsync(IQuery query, CancellationToken cancellationToken = default);
Returns an enumerable containing the DataRow
instances returned for a select
query:
using (databaseContextFactory.Create("connection-name"))
{
var rows = await gateway.GetRowsAsync(new Query("select Id, Username from dbo.Member"));
}
GetRowAsync
Task<DataRow> GetRowAsync(IQuery query, CancellationToken cancellationToken = default);
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.
FetchItemsAsync
Task<IEnumerable<T>> FetchItemsAsync(IQuery query, CancellationToken cancellationToken = default);
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.
FetchItemAsync
Task<T> FetchItemAsync(IQuery query, CancellationToken cancellationToken = default);
Returns a single object instance of type T
that is hydrated using the data returned from the select
clause represented by the IQuery
instance.
FetchMappedRowsAsync
Task<MappedRow<T>> FetchMappedRowsAsync(IQuery query, CancellationToken cancellationToken = default);
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.
FetchMappedRowAsync
Task<IEnumerable<MappedRow<T>>> FetchMappedRowAsync(IQuery query, CancellationToken cancellationToken = default);
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.
ContainsAsync
Task<bool> ContainsAsync(IQuery query, CancellationToken cancellationToken = default);
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;
}
}
Product | Versions 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 | netcoreapp3.0 was computed. netcoreapp3.1 was computed. |
.NET Standard | netstandard2.1 is compatible. |
MonoAndroid | monoandroid was computed. |
MonoMac | monomac was computed. |
MonoTouch | monotouch was computed. |
Tizen | tizen60 was computed. |
Xamarin.iOS | xamarinios was computed. |
Xamarin.Mac | xamarinmac was computed. |
Xamarin.TVOS | xamarintvos was computed. |
Xamarin.WatchOS | xamarinwatchos was computed. |
-
- Microsoft.CSharp (>= 4.7.0)
- Microsoft.Extensions.Configuration (>= 7.0.0)
- Microsoft.Extensions.Configuration.Binder (>= 7.0.3)
- Microsoft.Extensions.DependencyInjection (>= 7.0.0)
- Microsoft.Extensions.Options (>= 7.0.1)
- Shuttle.Core.Contract (>= 11.1.1)
NuGet packages (14)
Showing the top 5 NuGet packages that depend on Shuttle.Core.Data:
Package | Downloads |
---|---|
Shuttle.Core.Data.Http
IDatabaseContextCache implementation for use in HTTP scenarios. |
|
Shuttle.Recall.Sql.Storage
Sql-based implementation of the event store Shuttle.Recall persistence interfaces. |
|
Shuttle.Esb.Sql.Subscription
Sql-based implementation of ISubscriptionService interface for use with Shuttle.Esb implementations. |
|
Shuttle.Esb.Sql.Queue
Sql-based implementation of IQueue interface for use with Shuttle.Esb. |
|
Shuttle.Recall.Sql.EventProcessing
Sql-based implementation of the event store Shuttle.Recall projection interfaces. |
GitHub repositories
This package is not used by any popular GitHub repositories.
Version | Downloads | Last updated |
---|---|---|
17.0.0 | 532 | 8/5/2024 |
16.0.2 | 100 | 7/21/2024 |
16.0.1 | 236 | 7/16/2024 |
16.0.0 | 121 | 7/16/2024 |
15.0.1 | 767 | 4/30/2024 |
14.0.2 | 1,227 | 12/4/2022 |
13.0.2 | 7,594 | 12/1/2022 |
13.0.1 | 3,266 | 11/17/2022 |
13.0.0 | 7,515 | 9/4/2022 |
12.1.0 | 1,972 | 6/1/2022 |
12.0.2 | 7,185 | 4/9/2022 |
12.0.1 | 4,338 | 4/9/2022 |
12.0.0 | 4,042 | 3/21/2022 |
11.0.4 | 27,679 | 7/16/2019 |
11.0.3 | 1,893 | 6/27/2019 |
11.0.2 | 1,181 | 6/21/2019 |
11.0.1 | 6,280 | 5/31/2019 |
10.0.10 | 32,546 | 12/3/2018 |
10.0.9 | 35,099 | 11/16/2018 |
10.0.8 | 7,262 | 7/8/2018 |
10.0.6 | 2,416 | 7/4/2018 |
10.0.5 | 1,540 | 6/3/2018 |
10.0.4 | 2,354 | 2/20/2018 |
10.0.3 | 1,615 | 2/18/2018 |
10.0.2 | 5,980 | 2/13/2018 |
8.0.1 | 7,395 | 8/6/2017 |
8.0.0 | 7,816 | 3/24/2017 |
4.2.3 | 2,422 | 9/21/2016 |
4.2.2 | 1,772 | 9/21/2016 |
4.2.1 | 1,662 | 9/4/2016 |
4.2.0 | 2,039 | 6/4/2016 |
4.1.3 | 3,441 | 6/4/2016 |
4.1.2 | 2,266 | 5/27/2016 |
4.1.1 | 1,659 | 5/17/2016 |
4.0.0 | 2,963 | 5/1/2016 |
3.2.19 | 3,822 | 4/23/2016 |
3.2.14 | 4,843 | 3/19/2016 |