Shuttle.Core.Data 14.0.2

The ID prefix of this package has been reserved for one of the owners of this package by NuGet.org. Prefix Reserved
dotnet add package Shuttle.Core.Data --version 14.0.2
NuGet\Install-Package Shuttle.Core.Data -Version 14.0.2
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="Shuttle.Core.Data" Version="14.0.2" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Shuttle.Core.Data --version 14.0.2
#r "nuget: Shuttle.Core.Data, 14.0.2"
#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 Shuttle.Core.Data as a Cake Addin
#addin nuget:?package=Shuttle.Core.Data&version=14.0.2

// Install Shuttle.Core.Data as a Cake Tool
#tool nuget:?package=Shuttle.Core.Data&version=14.0.2

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

The Shuttle.Core.Data package provides a thin abstraction over ADO.NET by making use of the DbProviderFactories (see Shuttle.Core.Data.SqlClient for .Net Core Provider Factory adapter). Even though it provides object/relational mapping mechanisms it is in no way an 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";
	// -- or --
    builder.Options.DatabaseContextFactory.DefaultProviderName = "provider-name",
    builder.Options.DatabaseContextFactory.DefaultConnectionString = "connection-string"
    }
});

The default JSON settings structure is as follows:

{
	"Shuttle": {
		"DataAccess": {
			"CommandTimeout": 25,
			"DatabaseContextFactory":
			{
				"DefaultConnectionStringName": "connection-string-name",
				// or
				"DefaultProviderName": "provider-name",
				"DefaultConnectionString": "connection-string"
			}
		} 
	}
}

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. An IDbCommandFactory creates a System.Data.IDbCommand by using an IDbConnection instance. The DatabaseContextFactory also requires an instance of an IDatabaseContextCache that stores connections and is assigned to the DatabaseContext in order to obtain the active connection.

var databaseContextFactory = provider.GetRequiredService<DatabaseContextFactory>();

using (var context = databaseContextFactory.Create("connection-name"))
{
	// database interaction
}

using (var context = databaseContextFactory
	.Create("System.Data.SqlClient", 
		"Data Source=.\sqlexpress;Initial Catalog=Shuttle;Integrated Security=SSPI"))
{
	// database interaction
}

using (var context = databaseContextFactory.Create(existingIDbConnection))
{
	// database interaction
}

IQuery

An IQuery encapsulates a database query that can be executed. There is only one method that needs to be implemented:

void Prepare(IDbCommand command);

This should ensure that the given IDbCommand is configured for execution by setting the relvant command attributes and parameters.

RawQuery

The RawQuery represents a Text command type:

public RawQuery(string sql);
public static IQueryParameter Create(string sql, params object[] args);
public static IQueryParameter Create(string sql, dynamic parameters);

You can either use the constructor or one of the static methods to specify the sql to use. Parameters may either be added using the AddParameterValue of the returned IQueryParameter or they may be added as params object[] args in order to insert them at the required index. When using dynamic parameters the object values are converted to MappedColumn instances and added as parameters using AddParameterValue.

ProcedureQuery

The ProcedureQuery represents a StoredProcedure command type.

Dynamic mapping extensions

Methods that take an IQuery instance as a parameter will typically also be implemented as an extension method that take the sql and dynamic parameters as input, e.g.:

public static int DataAccessMethod(this IDataAccessInterface dataAccessInstance, string sql, dynamic parameters = null);

Where DataAccessMethod is the relevant method on the IDataAccessInterface that you would like to call.

MappedColumn

Typically you would not want to create a MappedColumn 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 MappedColumn<Guid> Id =
            new MappedColumn<Guid>("Id", DbType.Guid);

        public static readonly MappedColumn<string> OrderNumber =
            new MappedColumn<string>("OrderNumber", DbType.String, 20);

        public static readonly MappedColumn<string> OrderDate =
            new MappedColumn<string>("OrderDate", DbType.DateTime);

        public static readonly MappedColumn<string> CustomerName =
        new MappedColumn<string>("CustomerName", DbType.String, 65);

        public static readonly MappedColumn<string> CustomerEMail =
            new MappedColumn<string>("CustomerEMail", DbType.String); // size omitted
    }
}

There are quite a few options that you can set on the MappedColumn in order to represent your column properly.

MapFrom

public T MapFrom(DataRow row)

This will return the typed value of the specified column as contained in the passed-in DataRow.

IQueryParameter: IQuery

An IQueryParameter inherits the IQuery interface and extends it by allowing you to add parameters to a query by specifying an IMappedColumn (see below) instance along with the value for the parameter.

There are two implementations of this interface.

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 DatabaseContext.Current and InvalidOperationException will be thrown.

The following section each describe the methods available in the IDatabaseGateway interface.

GetReader

IDataReader GetReader(IQuery query);

Returns an IDataReader instance for the given select statement:

var databaseContextFactory = DatabaseContextFactory.Default();
var gateway = new DatabaseGateway();

using (var context = databaseContextFactory.Create("connection-name"))
{
	var reader = gateway.GetReader(RawQuery.Create("select Id, Username from dbo.Member"));
}

Execute

int Execute(IQuery query);

Executes the given query and returns the number of rows affected:

var databaseContextFactory = DatabaseContextFactory.Default();
var gateway = new DatabaseGateway();

using (var context = databaseContextFactory.Create("connection-name"))
{
	gateway.Execute(RawQuery.Create("delete from dbo.Member where Username = 'mr.resistor'"));
}

GetScalar

T GetScalar<T>(IQuery query);

Get the scalar value returned by the select query. The query shoud return only one value (scalar):

var databaseContextFactory = DatabaseContextFactory.Default();
var gateway = new DatabaseGateway();

using (var context = databaseContextFactory.Create("connection-name"))
{
	var username = gateway.GetScalar<string>(
		RawQuery.Create("select Username from dbo.Member where Id = 10")
	);
	
	var id = gateway.GetScalar<int>(
		RawQuery.Create("select Id from dbo.Member where Username = 'mr.resistor'")
	);
}

GetDataTable

DataTable GetDataTable(IQuery query);

Returns a DataTable containing the rows returned for the given select statement.

var databaseContextFactory = DatabaseContextFactory.Default();
var gateway = new DatabaseGateway();

using (var context = databaseContextFactory.Create("connection-name"))
{
	var table = gateway.GetDataTable(RawQuery.Create("select Id, Username from dbo.Member"));
}

GetRows

IEnumerable<DataRow> GetRows(IQuery query);

Returns an enumerable containing the DataRow instances returned for a select query:

var databaseContextFactory = DatabaseContextFactory.Default();
var gateway = new DatabaseGateway();

using (var context = databaseContextFactory.Create("connection-name"))
{
	var rows = gateway.GetRows(RawQuery.Create("select Id, Username from dbo.Member"));
}

GetRow

DataRow GetRow(IQuery query);

Returns a single DataRow containing the values returned for a select statement that returns exactly one row:

var databaseContextFactory = DatabaseContextFactory.Default();
var gateway = new DatabaseGateway();

using (var context = databaseContextFactory.Create("connection-name"))
{
	var row = gateway.GetRow(
		RawQuery.Create("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.

FetchItems

IEnumerable<T> FetchItems(IQuery query);

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.

FetchItem

T FetchItem(IQuery query);

Returns a single object instance of type T that is hydrated using the data returned from the select clause represented by the IQuery instance.

FetchMappedRows

IEnumerable<MappedRow<T>> FetchMappedRows(IQuery query);

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.

FetchMappedRow

MappedRow<T> FetchMappedRow(IQuery query);

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.

Contains

bool Contains(IQuery query);

Returns true is the IQuery instance select clause returns an int scalar that equals 1; else returns false.

RawQuery

The RawQuery enables you to create any query using the native language structure:

var query = RawQuery.Create("select UserName from dbo.Member where Id = @Id")
	.AddParameterValue(new MappedColumn<Guid>("Id", DbType.Guid), 
		new Guid('{75208260-CF93-454E-95EC-FE1903F3664E}'));

ProcedureQuery

The ProcedureQuery is used to execute a stored procedure:

var query = ProcedureQuery.Create("uspMemberById")
	.AddParameterValue(new MappedColumn<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.MapFrom(row))
            {
                CustomerName = OrderProcessColumns.CustomerName.MapFrom(row),
                CustomerEMail = OrderProcessColumns.CustomerEMail.MapFrom(row),
                OrderId = OrderProcessColumns.OrderId.MapFrom(row),
                InvoiceId = OrderProcessColumns.InvoiceId.MapFrom(row),
                DateRegistered = OrderProcessColumns.DateRegistered.MapFrom(row),
                OrderNumber = OrderProcessColumns.OrderNumber.MapFrom(row)
            };

            return new MappedRow<OrderProcess>(row, result);
        }
    }
}

MappedRow

A MappedRow instance contains bother a DataRow and the object that the DataRow mapped to.

This may be useful in situation where the DataRow contains more information that 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.Result;

			foreach (var orderLineRow in data.MappedRows<OrderLine>())
			{
				if (orderLineRow.Row["OrderId"].Equals(order.OrderId))
				{
					order.AddLine(orderLineRow.Result);
				}
			}

			result.Add(order);
		}

		return result;
	}
}
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 was computed.  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)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (13)

Showing the top 5 NuGet packages that depend on Shuttle.Core.Data:

Package Downloads
Shuttle.Core.Data.Http The ID prefix of this package has been reserved for one of the owners of this package by NuGet.org.

IDatabaseContextCache implementation for use in HTTP scenarios.

Shuttle.Recall.Sql.Storage The ID prefix of this package has been reserved for one of the owners of this package by NuGet.org.

Sql-based implementation of the event store Shuttle.Recall persistence interfaces.

Shuttle.Esb.Process The ID prefix of this package has been reserved for one of the owners of this package by NuGet.org.

Shuttle.Esb process management using Shuttle.Recall event sourcing.

Shuttle.Esb.Sql.Subscription The ID prefix of this package has been reserved for one of the owners of this package by NuGet.org.

Sql-based implementation of ISubscriptionManager interface for use with Shuttle.Esb.

Shuttle.Esb.Sql.Queue The ID prefix of this package has been reserved for one of the owners of this package by NuGet.org.

Sql-based implementation of IQueue interface for use with Shuttle.Esb.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
14.0.2 1,104 12/4/2022
13.0.2 6,069 12/1/2022
13.0.1 3,050 11/17/2022
13.0.0 7,065 9/4/2022
12.1.0 1,822 6/1/2022
12.0.2 6,757 4/9/2022
12.0.1 4,048 4/9/2022
12.0.0 3,784 3/21/2022
11.0.4 24,309 7/16/2019
11.0.3 1,776 6/27/2019
11.0.2 1,092 6/21/2019
11.0.1 5,965 5/31/2019
10.0.10 30,403 12/3/2018
10.0.9 33,168 11/16/2018
10.0.8 6,853 7/8/2018
10.0.6 2,302 7/4/2018
10.0.5 1,450 6/3/2018
10.0.4 2,260 2/20/2018
10.0.3 1,522 2/18/2018
10.0.2 5,628 2/13/2018
8.0.1 7,038 8/6/2017
8.0.0 7,312 3/24/2017
4.2.3 2,307 9/21/2016
4.2.2 1,681 9/21/2016
4.2.1 1,567 9/4/2016
4.2.0 1,945 6/4/2016
4.1.3 3,247 6/4/2016
4.1.2 2,133 5/27/2016
4.1.1 1,568 5/17/2016
4.0.0 2,794 5/1/2016
3.2.19 3,623 4/23/2016
3.2.14 4,534 3/19/2016