Class DbConnectionExtensions

Provides extension members for the type DbConnection.

Inheritance

object

DbConnectionExtensions

Inherited Members

object.GetType()

object.MemberwiseClone()

object.ToString()

object.Equals(object)

object.Equals(object, object)

object.ReferenceEquals(object, object)

object.GetHashCode()

Namespace: RentADeveloper.DbConnectionPlus

Assembly: RentADeveloper.DbConnectionPlus.dll

Syntax

public static class DbConnectionExtensions

Methods

View Source

Configure(Action<DbConnectionPlusConfiguration>)

Configures DbConnectionPlus.

Declaration

public static void Configure(Action<DbConnectionPlusConfiguration> configureAction)

Parameters

Type	Name	Description
Action<DbConnectionPlusConfiguration>	configureAction	The action that configures DbConnectionPlus.

Remarks

This method should only be called once during the application's lifetime. This is because the configuration is frozen after it is set for the first time to ensure thread safety and to prevent changes to the configuration after it has been used.

View Source

DeleteEntitiesAsync<TEntity>(DbConnection, IEnumerable<TEntity>, DbTransaction?, CancellationToken)

Asynchronously deletes the specified entities, identified by their key property/properties, from the database.

Declaration

public static Task<int> DeleteEntitiesAsync<TEntity>(this DbConnection connection, IEnumerable<TEntity> entities, DbTransaction? transaction = null, CancellationToken cancellationToken = default) where TEntity : class

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to delete the entities.
IEnumerable<TEntity>	entities	The entities to delete.
DbTransaction	transaction	The database transaction within to perform the operation.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
Task<int>	A task representing the asynchronous operation. Result will contain the number of rows affected by the delete operation.

Type Parameters

Name	Description
TEntity	The type of entities to delete.

Remarks

The table from which the entities will be deleted can be configured via TableAttribute or Configure(Action<DbConnectionPlusConfiguration>). Per default, the singular name of the type TEntity is used as the table name.

The type TEntity must have at least one instance property configured as key property. Use KeyAttribute or Configure(Action<DbConnectionPlusConfiguration>) to configure key properties.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

class Product
{
    [Key]
    public Int64 Id { get; set; }
    public Boolean IsDiscontinued { get; set; }
}

await connection.DeleteEntitiesAsync(products.Where(a => a.IsDiscontinued));

Exceptions

Type	Condition
ArgumentNullException	`connection` is null. `entities` is null.
ArgumentException	No instance property of the type `TEntity` is configured as a key property.
DbUpdateConcurrencyException	A concurrency violation was encountered while deleting an entity. A concurrency violation occurs when an unexpected number of rows are affected by a delete operation. This is usually because the data in the database has been modified since the entity has been loaded.
OperationCanceledException	The operation was cancelled via `cancellationToken`.

View Source

DeleteEntities<TEntity>(DbConnection, IEnumerable<TEntity>, DbTransaction?, CancellationToken)

Deletes the specified entities, identified by their key property/properties, from the database.

Declaration

public static int DeleteEntities<TEntity>(this DbConnection connection, IEnumerable<TEntity> entities, DbTransaction? transaction = null, CancellationToken cancellationToken = default) where TEntity : class

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to delete the entities.
IEnumerable<TEntity>	entities	The entities to delete.
DbTransaction	transaction	The database transaction within to perform the operation.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
int	The number of rows affected by the delete operation.

Type Parameters

Name	Description
TEntity	The type of entities to delete.

Remarks

The type TEntity must have at least one instance property configured as key property. Use KeyAttribute or Configure(Action<DbConnectionPlusConfiguration>) to configure key properties.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

class Product
{
    [Key]
    public Int64 Id { get; set; }
    public Boolean IsDiscontinued { get; set; }
}

connection.DeleteEntities(products.Where(a => a.IsDiscontinued));

Exceptions

Type	Condition
ArgumentNullException	`connection` is null. `entities` is null.
ArgumentException	No instance property of the type `TEntity` is configured as a key property.
DbUpdateConcurrencyException	A concurrency violation was encountered while deleting an entity. A concurrency violation occurs when an unexpected number of rows are affected by a delete operation. This is usually because the data in the database has been modified since the entity has been loaded.
OperationCanceledException	The operation was cancelled via `cancellationToken`.

View Source

DeleteEntityAsync<TEntity>(DbConnection, TEntity, DbTransaction?, CancellationToken)

Asynchronously deletes the specified entity, identified by its key property / properties, from the database.

Declaration

public static Task<int> DeleteEntityAsync<TEntity>(this DbConnection connection, TEntity entity, DbTransaction? transaction = null, CancellationToken cancellationToken = default) where TEntity : class

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to delete the entity.
TEntity	entity	The entity to delete.
DbTransaction	transaction	The database transaction within to perform the operation.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
Task<int>	A task representing the asynchronous operation. Result will contain the number of rows affected by the delete operation.

Type Parameters

Name	Description
TEntity	The type of entity to delete.

Remarks

The table from which the entity will be deleted can be configured via TableAttribute or Configure(Action<DbConnectionPlusConfiguration>). Per default, the singular name of the type TEntity is used as the table name.

The type TEntity must have at least one instance property configured as key property. Use KeyAttribute or Configure(Action<DbConnectionPlusConfiguration>) to configure key properties.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

class Product
{
    [Key]
    public Int64 Id { get; set; }
    public Boolean IsDiscontinued { get; set; }
}

if (product.IsDiscontinued)
{
    await connection.DeleteEntityAsync(product);
}

Exceptions

Type	Condition
ArgumentNullException	`connection` is null. `entity` is null.
ArgumentException	No instance property of the type `TEntity` is configured as a key property.
DbUpdateConcurrencyException	A concurrency violation was encountered while deleting an entity. A concurrency violation occurs when an unexpected number of rows are affected by a delete operation. This is usually because the data in the database has been modified since the entity has been loaded.
OperationCanceledException	The operation was cancelled via `cancellationToken`.

View Source

DeleteEntity<TEntity>(DbConnection, TEntity, DbTransaction?, CancellationToken)

Deletes the specified entity, identified by its key property / properties, from the database.

Declaration

public static int DeleteEntity<TEntity>(this DbConnection connection, TEntity entity, DbTransaction? transaction = null, CancellationToken cancellationToken = default) where TEntity : class

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to delete the entity.
TEntity	entity	The entity to delete.
DbTransaction	transaction	The database transaction within to perform the operation.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
int	The number of rows affected by the delete operation.

Type Parameters

Name	Description
TEntity	The type of entity to delete.

Remarks

The type TEntity must have at least one instance property configured as key property. Use KeyAttribute or Configure(Action<DbConnectionPlusConfiguration>) to configure key properties.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

class Product
{
    [Key]
    public Int64 Id { get; set; }
    public Boolean IsDiscontinued { get; set; }
}

if (product.IsDiscontinued)
{
    connection.DeleteEntity(product);
}

Exceptions

Type	Condition
ArgumentNullException	`connection` is null. `entity` is null.
ArgumentException	No instance property of the type `TEntity` is configured as a key property.
DbUpdateConcurrencyException	A concurrency violation was encountered while deleting an entity. A concurrency violation occurs when an unexpected number of rows are affected by a delete operation. This is usually because the data in the database has been modified since the entity has been loaded.
OperationCanceledException	The operation was cancelled via `cancellationToken`.

View Source

ExecuteNonQuery(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Executes the specified SQL statement and returns the number of rows affected by the statement.

Declaration

public static int ExecuteNonQuery(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
int	The number of rows affected by the statement.

Remarks

See ExecuteNonQuery() for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

if (supplier.IsRetired)
{
    var numberOfDeletedProducts = connection.ExecuteNonQuery(
       $"DELETE FROM Product WHERE SupplierId = {Parameter(supplier.Id)}"
    );
}

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

ExecuteNonQueryAsync(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Asynchronously executes the specified SQL statement and returns the number of rows affected by the statement.

Declaration

public static Task<int> ExecuteNonQueryAsync(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
Task<int>	A task representing the asynchronous operation. Result will contain the number of rows affected by the statement.

Remarks

See ExecuteNonQueryAsync(CancellationToken) for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

if (supplier.IsRetired)
{
    var numberOfDeletedProducts = await connection.ExecuteNonQueryAsync(
       $"DELETE FROM Product WHERE SupplierId = {Parameter(supplier.Id)}"
    );
}

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

ExecuteReader(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandBehavior, CommandType, CancellationToken)

Executes the specified SQL statement and returns a DbDataReader to read the statement result set.

Declaration

public static DbDataReader ExecuteReader(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandBehavior commandBehavior = CommandBehavior.Default, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandBehavior	commandBehavior	The command behavior to be passed to ExecuteReader(CommandBehavior).
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
DbDataReader	An instance of DbDataReader that can be used to read the statement result set.

Remarks

See ExecuteReader() for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

using var lowStockProductsReader = connection.ExecuteReader(
   $"SELECT * FROM Product WHERE UnitsInStock < {Parameter(lowStockThreshold)}"
);

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

ExecuteReaderAsync(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandBehavior, CommandType, CancellationToken)

Asynchronously executes the specified SQL statement and returns a DbDataReader to read the statement result set.

Declaration

public static Task<DbDataReader> ExecuteReaderAsync(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandBehavior commandBehavior = CommandBehavior.Default, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandBehavior	commandBehavior	The command behavior to be passed to ExecuteReaderAsync(CommandBehavior, CancellationToken).
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
Task<DbDataReader>	A task representing the asynchronous operation. Result will contain an instance of DbDataReader that can be used to read the statement result set.

Remarks

See ExecuteReaderAsync(CancellationToken) for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

await using var lowStockProductsReader = await connection.ExecuteReaderAsync(
   $"SELECT * FROM Product WHERE UnitsInStock < {Parameter(lowStockThreshold)}"
);

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

ExecuteScalarAsync<TTarget>(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Asynchronously executes the specified SQL statement and returns the first column of the first row in the result set returned by the statement converted to the type TTarget. Additional columns or rows are ignored.

Declaration

public static Task<TTarget> ExecuteScalarAsync<TTarget>(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
Task<TTarget>	A task representing the asynchronous operation. Result will contain the first column of the first row in the result set converted to the type `TTarget`, or default of `TTarget` if the result set is empty.

Type Parameters

Name	Description
TTarget	The type to which the first column of the first row in the result set returned by the statement should be converted.

Remarks

See ExecuteScalarAsync(CancellationToken) for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

var numberOfLowStockProducts = await connection.ExecuteScalarAsync<Int32>(
   $"SELECT COUNT(*) FROM Product WHERE UnitsInStock < {Parameter(lowStockThreshold)}"
);

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
InvalidCastException	The first column of the first row in the result set returned by the statement could not be converted to the type `TTarget`.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

ExecuteScalar<TTarget>(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Executes the specified SQL statement and returns the first column of the first row in the result set returned by the statement converted to the type TTarget. Additional columns or rows are ignored.

Declaration

public static TTarget ExecuteScalar<TTarget>(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
TTarget	The first column of the first row in the result set converted to the type `TTarget`, or default of `TTarget` if the result set is empty.

Type Parameters

Name	Description
TTarget	The type to which the first column of the first row in the result set returned by the statement should be converted.

Remarks

See ExecuteScalar() for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

var numberOfLowStockProducts = connection.ExecuteScalar<Int32>(
   $"SELECT COUNT(*) FROM Product WHERE UnitsInStock < {Parameter(lowStockThreshold)}"
);

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
InvalidCastException	The first column of the first row in the result set returned by the statement could not be converted to the type `TTarget`.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

Exists(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Executes the specified SQL statement and returns a bool value indicating whether the result set returned by the statement contains at least one row. This method is intended to check for the existence of rows matching certain criteria, e.g. checking whether a Product with a specific Id exists.

Declaration

public static bool Exists(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
bool	true if the result set returned by the statement contains at least one row; otherwise, false.

Remarks

See ExecuteReader() for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

var existLowStockProducts = connection.Exists(
   $"SELECT 1 FROM Product WHERE UnitsInStock < {Parameter(lowStockThreshold)}"
);

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

ExistsAsync(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Asynchronously executes the specified SQL statement and returns a bool value indicating whether the result set returned by the statement contains at least one row. This method is intended to check for the existence of rows matching certain criteria, e.g. checking whether a Product with a specific Id exists.

Declaration

public static Task<bool> ExistsAsync(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
Task<bool>	A task representing the asynchronous operation. Result will contain true if the result set returned by the statement contains at least one row; otherwise, false.

Remarks

See ExecuteReaderAsync(CancellationToken) for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

var existLowStockProducts = await connection.ExistsAsync(
   $"SELECT 1 FROM Product WHERE UnitsInStock < {Parameter(lowStockThreshold)}"
);

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

InsertEntitiesAsync<TEntity>(DbConnection, IEnumerable<TEntity>, DbTransaction?, CancellationToken)

Asynchronously inserts the specified entities into the database.

Declaration

public static Task<int> InsertEntitiesAsync<TEntity>(this DbConnection connection, IEnumerable<TEntity> entities, DbTransaction? transaction = null, CancellationToken cancellationToken = default) where TEntity : class

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to insert the entities.
IEnumerable<TEntity>	entities	The entities to insert.
DbTransaction	transaction	The database transaction within to perform the operation.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
Task<int>	A task representing the asynchronous operation. Result will contain the number of rows that were affected by the insert operation.

Type Parameters

Name	Description
TEntity	The type of entities to insert.

Remarks

The table into which the entities will be inserted can be configured via TableAttribute or Configure(Action<DbConnectionPlusConfiguration>). Per default, the singular name of the type TEntity is used as the table name.

Per default, each instance property of the type TEntity is mapped to a column with the same name (case-sensitive) in the table. This can be configured via ColumnAttribute or Configure(Action<DbConnectionPlusConfiguration>).

The columns must have data types that are compatible with the property types of the corresponding properties. The compatibility is determined using RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type,System.Type).

Properties configured as ignored properties (via NotMappedAttribute or Configure(Action<DbConnectionPlusConfiguration>)) are not inserted.

Properties configured as identity or computed properties (via DatabaseGeneratedAttribute or Configure(Action<DbConnectionPlusConfiguration>)) are also not inserted. Once an entity is inserted, the values for these properties are retrieved from the database and the entity properties are updated accordingly.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

class Product
{
    public Int64 Id { get; set; }
    public Int64 SupplierId { get; set; }
    public String Name { get; set; }
    public Decimal UnitPrice { get; set; }
    public Int32 UnitsInStock { get; set; }
}

var newProducts = await GetNewProductsAsync();

await connection.InsertEntitiesAsync(newProducts);

Exceptions

Type	Condition
ArgumentNullException	`connection` is null. `entities` is null.
OperationCanceledException	The operation was cancelled via `cancellationToken`.

View Source

InsertEntities<TEntity>(DbConnection, IEnumerable<TEntity>, DbTransaction?, CancellationToken)

Inserts the specified entities into the database.

Declaration

public static int InsertEntities<TEntity>(this DbConnection connection, IEnumerable<TEntity> entities, DbTransaction? transaction = null, CancellationToken cancellationToken = default) where TEntity : class

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to insert the entities.
IEnumerable<TEntity>	entities	The entities to insert.
DbTransaction	transaction	The database transaction within to perform the operation.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
int	The number of rows that were affected by the insert operation.

Type Parameters

Name	Description
TEntity	The type of entities to insert.

Remarks

Properties configured as ignored properties (via NotMappedAttribute or Configure(Action<DbConnectionPlusConfiguration>)) are not inserted.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

class Product
{
    public Int64 Id { get; set; }
    public Int64 SupplierId { get; set; }
    public String Name { get; set; }
    public Decimal UnitPrice { get; set; }
    public Int32 UnitsInStock { get; set; }
}

var newProducts = GetNewProducts();

connection.InsertEntities(newProducts);

Exceptions

Type	Condition
ArgumentNullException	`connection` is null. `entities` is null.
OperationCanceledException	The operation was cancelled via `cancellationToken`.

View Source

InsertEntityAsync<TEntity>(DbConnection, TEntity, DbTransaction?, CancellationToken)

Asynchronously inserts the specified entity into the database.

Declaration

public static Task<int> InsertEntityAsync<TEntity>(this DbConnection connection, TEntity entity, DbTransaction? transaction = null, CancellationToken cancellationToken = default) where TEntity : class

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to insert the entity.
TEntity	entity	The entity to insert.
DbTransaction	transaction	The database transaction within to perform the operation.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
Task<int>	A task representing the asynchronous operation. Result will contain the number of rows that were affected by the insert operation.

Type Parameters

Name	Description
TEntity	The type of entity to insert.

Remarks

The table into which the entity will be inserted can be configured via TableAttribute or Configure(Action<DbConnectionPlusConfiguration>). Per default, the singular name of the type TEntity is used as the table name.

Properties configured as ignored properties (via NotMappedAttribute or Configure(Action<DbConnectionPlusConfiguration>)) are not inserted.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

class Product
{
    public Int64 Id { get; set; }
    public Int64 SupplierId { get; set; }
    public String Name { get; set; }
    public Decimal UnitPrice { get; set; }
    public Int32 UnitsInStock { get; set; }
}

var newProduct = await GetNewProductAsync();

await connection.InsertEntityAsync(newProduct);

Exceptions

Type	Condition
ArgumentNullException	`connection` is null. `entity` is null.
OperationCanceledException	The operation was cancelled via `cancellationToken`.

View Source

InsertEntity<TEntity>(DbConnection, TEntity, DbTransaction?, CancellationToken)

Inserts the specified entity into the database.

Declaration

public static int InsertEntity<TEntity>(this DbConnection connection, TEntity entity, DbTransaction? transaction = null, CancellationToken cancellationToken = default) where TEntity : class

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to insert the entity.
TEntity	entity	The entity to insert.
DbTransaction	transaction	The database transaction within to perform the operation.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
int	The number of rows that were affected by the insert operation.

Type Parameters

Name	Description
TEntity	The type of entity to insert.

Remarks

Properties configured as ignored properties (via NotMappedAttribute or Configure(Action<DbConnectionPlusConfiguration>)) are not inserted.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

class Product
{
    public Int64 Id { get; set; }
    public Int64 SupplierId { get; set; }
    public String Name { get; set; }
    public Decimal UnitPrice { get; set; }
    public Int32 UnitsInStock { get; set; }
}

var newProduct = GetNewProduct();

connection.InsertEntity(newProduct);

Exceptions

Type	Condition
ArgumentNullException	`connection` is null. `entity` is null.
OperationCanceledException	The operation was cancelled via `cancellationToken`.

View Source

Parameter(object?, string?)

Wraps parameterValue in an instance of InterpolatedParameter to indicate that this value should be passed as a parameter to an SQL statement.

Use this method to pass a value in an interpolated string as a parameter to an SQL statement.

Declaration

public static InterpolatedParameter Parameter(object? parameterValue, string? parameterValueExpression = null)

Parameters

Type	Name	Description
object	parameterValue	The value to pass as a parameter.
string	parameterValueExpression	The expression from which `parameterValue` was obtained. Used to infer the name for the parameter. This parameter is optional and is automatically provided by the compiler.

Returns

Type	Description
InterpolatedParameter	An instance of InterpolatedParameter indicating that `parameterValue` should be passed as a parameter to an SQL statement.

Remarks

To use this method, import DbConnectionExtensions with a using directive with the static modifier:

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

Example:

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

var lowStockProductsReader = connection.ExecuteReader(
   $"""
    SELECT  *
    FROM    Product
    WHERE   UnitsInStock < {Parameter(lowStockThreshold)}
    """
);

This will add a parameter with the name "LowStockThreshold" and the value of the variable "lowStockThreshold" to the SQL statement.

The name of the parameter will be inferred from the expression from which parameterValue was obtained. If the name cannot be inferred from the expression a generic name like "Parameter_1", "Parameter_2", and so on will be used.

If you pass an Enum value as a parameter, the enum value is serialized according to the setting EnumSerializationMode.

View Source

Query(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Executes the specified SQL statement and materializes the result set returned by the statement into a sequence of dynamic objects. Each row of the result set is mapped to a dynamic object where each column is represented as a property of the dynamic object with the same name as the column.

Declaration

public static IEnumerable<dynamic> Query(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
IEnumerable<dynamic>	A sequence of dynamic objects containing the data of the result set returned by the statement.

Remarks

See ExecuteReader(CommandBehavior) for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var products = connection.Query($"SELECT * FROM Product WHERE CategoryId = {Parameter(categoryId)}");

foreach (var product in products)
{
    var id = product.Id;
    var name = product.Name;
    ...
}

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

QueryAsync(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Asynchronously executes the specified SQL statement and materializes the result set returned by the statement into a sequence of dynamic objects. Each row of the result set is mapped to a dynamic object where each column is represented as a property of the dynamic object with the same name as the column.

Declaration

public static IAsyncEnumerable<dynamic> QueryAsync(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
IAsyncEnumerable<dynamic>	An asynchronous sequence of dynamic objects containing the data of the result set returned by the statement.

Remarks

See ExecuteReaderAsync(CommandBehavior, CancellationToken) for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var products = connection.QueryAsync($"SELECT * FROM Product WHERE CategoryId = {Parameter(categoryId)}");

await foreach (var product in products)
{
    var id = product.Id;
    var name = product.Name;
    ...
}

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

QueryAsync<T>(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Asynchronously executes the specified SQL statement and materializes the result set returned by the statement into a sequence of instances of the type T.

Declaration

public static IAsyncEnumerable<T> QueryAsync<T>(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
IAsyncEnumerable<T>	An asynchronous sequence of instances of the type `T` containing the data of the result set returned by the statement.

Type Parameters

Name	Description
T	The type of objects to materialize the result set to. See remarks for details.

Remarks

T can be any of the following types:

A built-in .NET type or a nullable built-in .NET type like DateTime or string. In this case only the first column of the result set will be read and converted to the type T. Other columns in the result set will be ignored.

An entity type (a class or a record) In this case each row in the result set will be materialized into an instance of the entity type, with the constructor arguments or properties of the entity being populated from the corresponding columns of the row.

        All columns returned by the SQL statement must have a name.

        The type <code class="typeparamref">T</code> must either:

        1. Have a constructor whose parameters match the columns of the result set returned by the
           statement.
           The names of the parameters must match the names of the columns (case-insensitive).
           The types of the parameters must be compatible with the data types of the columns.
           The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.
           The parameters can be in any order.

        Or

        2. Have a parameterless constructor and properties (with public setters) that match the columns of
           the result set returned by the statement.

           Per default, the names of the properties must match the names of the columns (case-insensitive).
           This can be configured via <xref href="System.ComponentModel.DataAnnotations.Schema.ColumnAttribute" data-throw-if-not-resolved="false"></xref> or <xref href="RentADeveloper.DbConnectionPlus.DbConnectionExtensions.Configure(System.Action%7bRentADeveloper.DbConnectionPlus.Configuration.DbConnectionPlusConfiguration%7d)" data-throw-if-not-resolved="false"></xref>.

           The types of the properties must be compatible with the data types of the columns.
           The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.

           Columns without a matching property will be ignored.

        If neither condition is satisfied, an <xref href="System.ArgumentException" data-throw-if-not-resolved="false"></xref> will be thrown.

        If a constructor parameter or a property cannot be set to the value of the corresponding column
        due to a type mismatch, an <xref href="System.InvalidCastException" data-throw-if-not-resolved="false"></xref> will be thrown.
    </li><li><span class="term">A <xref href="System.ValueTuple" data-throw-if-not-resolved="false"></xref> type like <xref href="System.ValueTuple%603" data-throw-if-not-resolved="false"></xref></span>
        In this case each row in the result set will be materialized into an instance of the value tuple
        type, with the fields of the value tuple being populated from the corresponding columns of the
        row.

        All columns returned by the SQL statement must have a name.
        The SQL statement must return the same number of columns as the value tuple has fields.
        The SQL statement must return the columns in the same order as the fields in the value tuple.

        The data types of the columns must be compatible with the field types of the value tuple.
        The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.

        If those conditions are not met, an <xref href="System.ArgumentException" data-throw-if-not-resolved="false"></xref> is thrown.
    </li></ol>

See ExecuteReader() for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

var lowStockProducts = connection.QueryAsync<Product>(
   $"SELECT * FROM Product WHERE UnitsInStock < {Parameter(lowStockThreshold)}"
);

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
ArgumentException	`T` is an entity type or a ValueTuple type and the SQL statement returned no columns. `T` is an entity type or a ValueTuple type and the SQL statement returned a column without a name. `T` is an entity type or a ValueTuple type and the SQL statement returned a column with an unsupported data type. `T` is an entity type or a ValueTuple type that does not satisfy the conditions described in the remarks.
InvalidCastException	`T` is a built-in type or a nullable built-in type and the first column of the result set returned by the statement contains a value that could not be converted to the type `T`. `T` is an entity type and a column value returned by the statement could not be converted to the type of the corresponding constructor parameter or property of the type `T`. `T` is a ValueTuple type and a column value returned by the statement could not be converted to the type of the corresponding field of the type `T`.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

QueryFirst(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Executes the specified SQL statement and materializes the first row of the result set returned by the statement into a dynamic object where each column is represented as a property of the dynamic object with the same name as the column.

Declaration

public static dynamic QueryFirst(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
dynamic	A dynamic object containing the data of the first row of the result set returned by the statement.

Remarks

See ExecuteReader(CommandBehavior) for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var product = connection.QueryFirst($"SELECT * FROM Product WHERE Id = {Parameter(id)}");

var id = product.Id;
var name = product.Name;
...

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
InvalidOperationException	The SQL statement did not return any rows.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

QueryFirstAsync(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Asynchronously executes the specified SQL statement and materializes the first row of the result set returned by the statement into a dynamic object where each column is represented as a property of the dynamic object with the same name as the column.

Declaration

public static Task<dynamic> QueryFirstAsync(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
Task<dynamic>	A task representing the asynchronous operation. Result will contain a dynamic object containing the data of the first row of the result set returned by the statement.

Remarks

See ExecuteReaderAsync(CommandBehavior, CancellationToken) for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var product = await connection.QueryFirstAsync($"SELECT * FROM Product WHERE Id = {Parameter(id)}");

var id = product.Id;
var name = product.Name;
...

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
InvalidOperationException	The SQL statement did not return any rows.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

QueryFirstAsync<T>(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Asynchronously executes the specified SQL statement and materializes the first row of the result set returned by the statement into an instance of the type T.

Declaration

public static Task<T> QueryFirstAsync<T>(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
Task<T>	A task representing the asynchronous operation. Result will contain an instance of the type `T` containing the data of the first row of the result set returned by the statement.

Type Parameters

Name	Description
T	The type of object to materialize the first row of the result set to. See remarks for details.

Remarks

T can be any of the following types:

A built-in .NET type or a nullable built-in .NET type like DateTime or string. In this case only the first column of the result set will be read and converted to the type T. Other columns in the result set will be ignored.

An entity type (a class or a record) In this case the first row in the result set will be materialized into an instance of the entity type, with the constructor arguments or properties of the entity being populated from the corresponding columns of the row.

        All columns returned by the SQL statement must have a name.

        The type <code class="typeparamref">T</code> must either:

        1. Have a constructor whose parameters match the columns of the result set returned by the
           statement.
           The names of the parameters must match the names of the columns (case-insensitive).
           The types of the parameters must be compatible with the data types of the columns.
           The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.
           The parameters can be in any order.

        Or

        2. Have a parameterless constructor and properties (with public setters) that match the columns of
           the result set returned by the statement.

           Per default, the names of the properties must match the names of the columns (case-insensitive).
           This can be configured via <xref href="System.ComponentModel.DataAnnotations.Schema.ColumnAttribute" data-throw-if-not-resolved="false"></xref> or <xref href="RentADeveloper.DbConnectionPlus.DbConnectionExtensions.Configure(System.Action%7bRentADeveloper.DbConnectionPlus.Configuration.DbConnectionPlusConfiguration%7d)" data-throw-if-not-resolved="false"></xref>.

           The types of the properties must be compatible with the data types of the columns.
           The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.

           Columns without a matching property will be ignored.

        If neither condition is satisfied, an <xref href="System.ArgumentException" data-throw-if-not-resolved="false"></xref> will be thrown.

        If a constructor parameter or a property cannot be set to the value of the corresponding column
        due to a type mismatch, an <xref href="System.InvalidCastException" data-throw-if-not-resolved="false"></xref> will be thrown.
    </li><li><span class="term">A <xref href="System.ValueTuple" data-throw-if-not-resolved="false"></xref> type like <xref href="System.ValueTuple%603" data-throw-if-not-resolved="false"></xref></span>
        In this case the first row in the result set will be materialized into an instance of the value
        tuple type, with the fields of the value tuple being populated from the corresponding columns of the
        row.

        All columns returned by the SQL statement must have a name.
        The SQL statement must return the same number of columns as the value tuple has fields.
        The SQL statement must return the columns in the same order as the fields in the value tuple.

        The data types of the columns must be compatible with the field types of the value tuple.
        The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.

        If those conditions are not met, an <xref href="System.ArgumentException" data-throw-if-not-resolved="false"></xref> is thrown.
    </li></ol>

See ExecuteReader() for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

public class Order
{
    [Key]
    public Int64 Id { get; set; }
    public DateTime OrderDate { get; set; }
    public Decimal TotalAmount { get; set; }
}

var order = await connection.QueryFirstAsync<Order>($"SELECT * FROM [Order] WHERE Id = {Parameter(orderId)}");

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
ArgumentException	`T` is an entity type or a ValueTuple type and the SQL statement returned no columns. `T` is an entity type or a ValueTuple type and the SQL statement returned a column without a name. `T` is an entity type or a ValueTuple type and the SQL statement returned a column with an unsupported data type. `T` is an entity type or a ValueTuple type that does not satisfy the conditions described in the remarks.
InvalidCastException	`T` is a built-in type or a nullable built-in type and the first column of the result set returned by the statement contains a value that could not be converted to the type `T`. `T` is an entity type and a column value returned by the statement could not be converted to the type of the corresponding constructor parameter or property of the type `T`. `T` is a ValueTuple type and a column value returned by the statement could not be converted to the type of the corresponding field of the type `T`.
InvalidOperationException	The SQL statement did not return any rows.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

QueryFirstOrDefault(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Declaration

public static dynamic? QueryFirstOrDefault(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
dynamic	A dynamic object containing the data of the first row of the result set returned by the statement or null if the statement did not return any rows.

Remarks

See ExecuteReader(CommandBehavior) for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var product = connection.QueryFirstOrDefault($"SELECT * FROM Product WHERE Id = {Parameter(id)}");

if (product is not null)
{
    var id = product.Id;
    var name = product.Name;
    ...
}

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

QueryFirstOrDefaultAsync(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Declaration

public static Task<dynamic?> QueryFirstOrDefaultAsync(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
Task<dynamic>	A task representing the asynchronous operation. Result will contain a dynamic object containing the data of the first row of the result set returned by the statement or null if the statement did not return any rows.

Remarks

See ExecuteReaderAsync(CommandBehavior, CancellationToken) for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var product = await connection.QueryFirstOrDefaultAsync($"SELECT * FROM Product WHERE Id = {Parameter(id)}");

if (product is not null)
{
    var id = product.Id;
    var name = product.Name;
    ...
}

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

QueryFirstOrDefaultAsync<T>(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Asynchronously executes the specified SQL statement and materializes the first row of the result set returned by the statement into an instance of the type T or the default of T if the statement did not return any rows.

Declaration

public static Task<T?> QueryFirstOrDefaultAsync<T>(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
Task<T>	A task representing the asynchronous operation. Result will contain an instance of the type `T` containing the data of the first row of the result set returned by the statement or the default of `T` if the statement did not return any rows.

Type Parameters

Name	Description
T	The type of object to materialize the first row of the result set to. See remarks for details.

Remarks

T can be any of the following types:

A built-in .NET type or a nullable built-in .NET type like DateTime or string. In this case only the first column of the result set will be read and converted to the type T. Other columns in the result set will be ignored.

        All columns returned by the SQL statement must have a name.

        The type <code class="typeparamref">T</code> must either:

        1. Have a constructor whose parameters match the columns of the result set returned by the
           statement.
           The names of the parameters must match the names of the columns (case-insensitive).
           The types of the parameters must be compatible with the data types of the columns.
           The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.
           The parameters can be in any order.

        Or

        2. Have a parameterless constructor and properties (with public setters) that match the columns of
           the result set returned by the statement.

           Per default, the names of the properties must match the names of the columns (case-insensitive).
           This can be configured via <xref href="System.ComponentModel.DataAnnotations.Schema.ColumnAttribute" data-throw-if-not-resolved="false"></xref> or <xref href="RentADeveloper.DbConnectionPlus.DbConnectionExtensions.Configure(System.Action%7bRentADeveloper.DbConnectionPlus.Configuration.DbConnectionPlusConfiguration%7d)" data-throw-if-not-resolved="false"></xref>.

           The types of the properties must be compatible with the data types of the columns.
           The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.

           Columns without a matching property will be ignored.

        If neither condition is satisfied, an <xref href="System.ArgumentException" data-throw-if-not-resolved="false"></xref> will be thrown.

        If a constructor parameter or a property cannot be set to the value of the corresponding column
        due to a type mismatch, an <xref href="System.InvalidCastException" data-throw-if-not-resolved="false"></xref> will be thrown.
    </li><li><span class="term">A <xref href="System.ValueTuple" data-throw-if-not-resolved="false"></xref> type like <xref href="System.ValueTuple%603" data-throw-if-not-resolved="false"></xref></span>
        In this case the first row in the result set will be materialized into an instance of the value
        tuple type, with the fields of the value tuple being populated from the corresponding columns of the
        row.

        All columns returned by the SQL statement must have a name.
        The SQL statement must return the same number of columns as the value tuple has fields.
        The SQL statement must return the columns in the same order as the fields in the value tuple.

        The data types of the columns must be compatible with the field types of the value tuple.
        The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.

        If those conditions are not met, an <xref href="System.ArgumentException" data-throw-if-not-resolved="false"></xref> is thrown.
    </li></ol>

See ExecuteReader() for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

public class Order
{
    [Key]
    public Int64 Id { get; set; }
    public DateTime OrderDate { get; set; }
    public Decimal TotalAmount { get; set; }
}

var order = await connection.QueryFirstOrDefaultAsync<Order>(
    $"SELECT * FROM [Order] WHERE Id = {Parameter(orderId)}"
);

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
ArgumentException	`T` is an entity type or a ValueTuple type and the SQL statement returned no columns. `T` is an entity type or a ValueTuple type and the SQL statement returned a column without a name. `T` is an entity type or a ValueTuple type and the SQL statement returned a column with an unsupported data type. `T` is an entity type or a ValueTuple type that does not satisfy the conditions described in the remarks.
InvalidCastException	`T` is a built-in type or a nullable built-in type and the first column of the result set returned by the statement contains a value that could not be converted to the type `T`. `T` is an entity type and a column value returned by the statement could not be converted to the type of the corresponding constructor parameter or property of the type `T`. `T` is a ValueTuple type and a column value returned by the statement could not be converted to the type of the corresponding field of the type `T`.
InvalidOperationException	The SQL statement did not return any rows.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

QueryFirstOrDefault<T>(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Executes the specified SQL statement and materializes the first row of the result set returned by the statement into an instance of the type T or the default of T if the statement did not return any rows.

Declaration

public static T? QueryFirstOrDefault<T>(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
T	An instance of the type `T` containing the data of the first row of the result set returned by the statement or the default of `T` if the statement did not return any rows.

Type Parameters

Name	Description
T	The type of object to materialize the first row of the result set to. See remarks for details.

Remarks

T can be any of the following types:

A built-in .NET type or a nullable built-in .NET type like DateTime or string. In this case only the first column of the result set will be read and converted to the type T. Other columns in the result set will be ignored.

        All columns returned by the SQL statement must have a name.

        The type <code class="typeparamref">T</code> must either:

        1. Have a constructor whose parameters match the columns of the result set returned by the
           statement.
           The names of the parameters must match the names of the columns (case-insensitive).
           The types of the parameters must be compatible with the data types of the columns.
           The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.
           The parameters can be in any order.

        Or

        2. Have a parameterless constructor and properties (with public setters) that match the columns of
           the result set returned by the statement.

           Per default, the names of the properties must match the names of the columns (case-insensitive).
           This can be configured via <xref href="System.ComponentModel.DataAnnotations.Schema.ColumnAttribute" data-throw-if-not-resolved="false"></xref> or <xref href="RentADeveloper.DbConnectionPlus.DbConnectionExtensions.Configure(System.Action%7bRentADeveloper.DbConnectionPlus.Configuration.DbConnectionPlusConfiguration%7d)" data-throw-if-not-resolved="false"></xref>.

           The types of the properties must be compatible with the data types of the columns.
           The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.

           Columns without a matching property will be ignored.

        If neither condition is satisfied, an <xref href="System.ArgumentException" data-throw-if-not-resolved="false"></xref> will be thrown.

        If a constructor parameter or a property cannot be set to the value of the corresponding column
        due to a type mismatch, an <xref href="System.InvalidCastException" data-throw-if-not-resolved="false"></xref> will be thrown.
    </li><li><span class="term">A <xref href="System.ValueTuple" data-throw-if-not-resolved="false"></xref> type like <xref href="System.ValueTuple%603" data-throw-if-not-resolved="false"></xref></span>
        In this case the first row in the result set will be materialized into an instance of the value
        tuple type, with the fields of the value tuple being populated from the corresponding columns of the
        row.

        All columns returned by the SQL statement must have a name.
        The SQL statement must return the same number of columns as the value tuple has fields.
        The SQL statement must return the columns in the same order as the fields in the value tuple.

        The data types of the columns must be compatible with the field types of the value tuple.
        The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.

        If those conditions are not met, an <xref href="System.ArgumentException" data-throw-if-not-resolved="false"></xref> is thrown.
    </li></ol>

See ExecuteReader() for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

public class Order
{
    [Key]
    public Int64 Id { get; set; }
    public DateTime OrderDate { get; set; }
    public Decimal TotalAmount { get; set; }
}

var order = connection.QueryFirstOrDefault<Order>($"SELECT * FROM [Order] WHERE Id = {Parameter(orderId)}");

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
ArgumentException	`T` is an entity type or a ValueTuple type and the SQL statement returned no columns. `T` is an entity type or a ValueTuple type and the SQL statement returned a column without a name. `T` is an entity type or a ValueTuple type and the SQL statement returned a column with an unsupported data type. `T` is an entity type or a ValueTuple type that does not satisfy the conditions described in the remarks.
InvalidCastException	`T` is a built-in type or a nullable built-in type and the first column of the result set returned by the statement contains a value that could not be converted to the type `T`. `T` is an entity type and a column value returned by the statement could not be converted to the type of the corresponding constructor parameter or property of the type `T`. `T` is a ValueTuple type and a column value returned by the statement could not be converted to the type of the corresponding field of the type `T`.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

QueryFirst<T>(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Executes the specified SQL statement and materializes the first row of the result set returned by the statement into an instance of the type T.

Declaration

public static T QueryFirst<T>(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
T	An instance of the type `T` containing the data of the first row of the result set returned by the statement.

Type Parameters

Name	Description
T	The type of object to materialize the first row of the result set to. See remarks for details.

Remarks

T can be any of the following types:

A built-in .NET type or a nullable built-in .NET type like DateTime or string. In this case only the first column of the result set will be read and converted to the type T. Other columns in the result set will be ignored.

        All columns returned by the SQL statement must have a name.

        The type <code class="typeparamref">T</code> must either:

        1. Have a constructor whose parameters match the columns of the result set returned by the
           statement.
           The names of the parameters must match the names of the columns (case-insensitive).
           The types of the parameters must be compatible with the data types of the columns.
           The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.
           The parameters can be in any order.

        Or

        2. Have a parameterless constructor and properties (with public setters) that match the columns of
           the result set returned by the statement.

           Per default, the names of the properties must match the names of the columns (case-insensitive).
           This can be configured via <xref href="System.ComponentModel.DataAnnotations.Schema.ColumnAttribute" data-throw-if-not-resolved="false"></xref> or <xref href="RentADeveloper.DbConnectionPlus.DbConnectionExtensions.Configure(System.Action%7bRentADeveloper.DbConnectionPlus.Configuration.DbConnectionPlusConfiguration%7d)" data-throw-if-not-resolved="false"></xref>.

           The types of the properties must be compatible with the data types of the columns.
           The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.

           Columns without a matching property will be ignored.

        If neither condition is satisfied, an <xref href="System.ArgumentException" data-throw-if-not-resolved="false"></xref> will be thrown.

        If a constructor parameter or a property cannot be set to the value of the corresponding column
        due to a type mismatch, an <xref href="System.InvalidCastException" data-throw-if-not-resolved="false"></xref> will be thrown.
    </li><li><span class="term">A <xref href="System.ValueTuple" data-throw-if-not-resolved="false"></xref> type like <xref href="System.ValueTuple%603" data-throw-if-not-resolved="false"></xref></span>
        In this case the first row in the result set will be materialized into an instance of the value
        tuple type, with the fields of the value tuple being populated from the corresponding columns of the
        row.

        All columns returned by the SQL statement must have a name.
        The SQL statement must return the same number of columns as the value tuple has fields.
        The SQL statement must return the columns in the same order as the fields in the value tuple.

        The data types of the columns must be compatible with the field types of the value tuple.
        The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.

        If those conditions are not met, an <xref href="System.ArgumentException" data-throw-if-not-resolved="false"></xref> is thrown.
    </li></ol>

See ExecuteReader() for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

public class Order
{
    [Key]
    public Int64 Id { get; set; }
    public DateTime OrderDate { get; set; }
    public Decimal TotalAmount { get; set; }
}

var order = connection.QueryFirst<Order>($"SELECT * FROM [Order] WHERE Id = {Parameter(orderId)}");

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
ArgumentException	`T` is an entity type or a ValueTuple type and the SQL statement returned no columns. `T` is an entity type or a ValueTuple type and the SQL statement returned a column without a name. `T` is an entity type or a ValueTuple type and the SQL statement returned a column with an unsupported data type. `T` is an entity type or a ValueTuple type that does not satisfy the conditions described in the remarks.
InvalidCastException	`T` is a built-in type or a nullable built-in type and the first column of the result set returned by the statement contains a value that could not be converted to the type `T`. `T` is an entity type and a column value returned by the statement could not be converted to the type of the corresponding constructor parameter or property of the type `T`. `T` is a ValueTuple type and a column value returned by the statement could not be converted to the type of the corresponding field of the type `T`.
InvalidOperationException	The SQL statement did not return any rows.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

QuerySingle(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Executes the specified SQL statement and materializes the single row of the result set returned by the statement into a dynamic object where each column is represented as a property of the dynamic object with the same name as the column.

Declaration

public static dynamic QuerySingle(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
dynamic	A dynamic object containing the data of the single row of the result set returned by the statement.

Remarks

See ExecuteReader(CommandBehavior) for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var product = connection.QuerySingle($"SELECT * FROM Product WHERE Id = {Parameter(id)}");

var id = product.Id;
var name = product.Name;
...

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
InvalidOperationException	The SQL statement did not return any rows. The SQL statement did return more than one row.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

QuerySingleAsync(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Asynchronously executes the specified SQL statement and materializes the single row of the result set returned by the statement into a dynamic object where each column is represented as a property of the dynamic object with the same name as the column.

Declaration

public static Task<dynamic> QuerySingleAsync(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
Task<dynamic>	A task representing the asynchronous operation. Result will contain a dynamic object containing the data of the single row of the result set returned by the statement.

Remarks

See ExecuteReaderAsync(CommandBehavior, CancellationToken) for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var product = await connection.QuerySingleAsync($"SELECT * FROM Product WHERE Id = {Parameter(id)}");

var id = product.Id;
var name = product.Name;
...

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
InvalidOperationException	The SQL statement did not return any rows. The SQL statement did return more than one row.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

QuerySingleAsync<T>(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Asynchronously executes the specified SQL statement and materializes the single row of the result set returned by the statement into an instance of the type T.

Declaration

public static Task<T> QuerySingleAsync<T>(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
Task<T>	A task representing the asynchronous operation. Result will contain an instance of the type `T` containing the data of the single row of the result set returned by the statement.

Type Parameters

Name	Description
T	The type of object to materialize the single row of the result set to. See remarks for details.

Remarks

T can be any of the following types:

A built-in .NET type or a nullable built-in .NET type like DateTime or string. In this case only the first column of the result set will be read and converted to the type T. Other columns in the result set will be ignored.

An entity type (a class or a record) In this case the single row in the result set will be materialized into an instance of the entity type, with the constructor arguments or properties of the entity being populated from the corresponding columns of the row.

        All columns returned by the SQL statement must have a name.

        The type <code class="typeparamref">T</code> must either:

        1. Have a constructor whose parameters match the columns of the result set returned by the
           statement.
           The names of the parameters must match the names of the columns (case-insensitive).
           The types of the parameters must be compatible with the data types of the columns.
           The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.
           The parameters can be in any order.

        Or

        2. Have a parameterless constructor and properties (with public setters) that match the columns of
           the result set returned by the statement.

           Per default, the names of the properties must match the names of the columns (case-insensitive).
           This can be configured via <xref href="System.ComponentModel.DataAnnotations.Schema.ColumnAttribute" data-throw-if-not-resolved="false"></xref> or <xref href="RentADeveloper.DbConnectionPlus.DbConnectionExtensions.Configure(System.Action%7bRentADeveloper.DbConnectionPlus.Configuration.DbConnectionPlusConfiguration%7d)" data-throw-if-not-resolved="false"></xref>.

           The types of the properties must be compatible with the data types of the columns.
           The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.

           Columns without a matching property will be ignored.

        If neither condition is satisfied, an <xref href="System.ArgumentException" data-throw-if-not-resolved="false"></xref> will be thrown.

        If a constructor parameter or a property cannot be set to the value of the corresponding column
        due to a type mismatch, an <xref href="System.InvalidCastException" data-throw-if-not-resolved="false"></xref> will be thrown.
    </li><li><span class="term">A <xref href="System.ValueTuple" data-throw-if-not-resolved="false"></xref> type like <xref href="System.ValueTuple%603" data-throw-if-not-resolved="false"></xref></span>
        In this case the single row in the result set will be materialized into an instance of the value
        tuple type, with the fields of the value tuple being populated from the corresponding columns of the
        row.

        All columns returned by the SQL statement must have a name.
        The SQL statement must return the same number of columns as the value tuple has fields.
        The SQL statement must return the columns in the same order as the fields in the value tuple.

        The data types of the columns must be compatible with the field types of the value tuple.
        The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.

        If those conditions are not met, an <xref href="System.ArgumentException" data-throw-if-not-resolved="false"></xref> is thrown.
    </li></ol>

See ExecuteReader() for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

public class Order
{
    [Key]
    public Int64 Id { get; set; }
    public DateTime OrderDate { get; set; }
    public Decimal TotalAmount { get; set; }
}

var order = await connection.QuerySingleAsync<Order>($"SELECT * FROM [Order] WHERE Id = {Parameter(orderId)}");

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
ArgumentException	`T` is an entity type or a ValueTuple type and the SQL statement returned no columns. `T` is an entity type or a ValueTuple type and the SQL statement returned a column without a name. `T` is an entity type or a ValueTuple type and the SQL statement returned a column with an unsupported data type. `T` is an entity type or a ValueTuple type that does not satisfy the conditions described in the remarks.
InvalidCastException	`T` is a built-in type or a nullable built-in type and the first column of the result set returned by the statement contains a value that could not be converted to the type `T`. `T` is an entity type and a column value returned by the statement could not be converted to the type of the corresponding constructor parameter or property of the type `T`. `T` is a ValueTuple type and a column value returned by the statement could not be converted to the type of the corresponding field of the type `T`.
InvalidOperationException	The SQL statement did not return any rows. The SQL statement did return more than one row.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

QuerySingleOrDefault(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Declaration

public static dynamic? QuerySingleOrDefault(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
dynamic	A dynamic object containing the data of the single row of the result set returned by the statement or null if the statement did not return any rows.

Remarks

See ExecuteReader(CommandBehavior) for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var product = connection.QuerySingleOrDefault($"SELECT * FROM Product WHERE Id = {Parameter(id)}");

if (product is not null)
{
    var id = product.Id;
    var name = product.Name;
    ...
}

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
InvalidOperationException	The SQL statement did return more than one row.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

QuerySingleOrDefaultAsync(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Declaration

public static Task<dynamic?> QuerySingleOrDefaultAsync(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
Task<dynamic>	A task representing the asynchronous operation. Result will contain a dynamic object containing the data of the single row of the result set returned by the statement or null if the statement did not return any rows.

Remarks

See ExecuteReaderAsync(CommandBehavior, CancellationToken) for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var product = await connection.QuerySingleOrDefaultAsync($"SELECT * FROM Product WHERE Id = {Parameter(id)}");

if (product is not null)
{
    var id = product.Id;
    var name = product.Name;
    ...
}

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
InvalidOperationException	The SQL statement did return more than one row.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

QuerySingleOrDefaultAsync<T>(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Asynchronously executes the specified SQL statement and materializes the single row of the result set returned by the statement into an instance of the type T or the default of T if the statement did not return any rows.

Declaration

public static Task<T?> QuerySingleOrDefaultAsync<T>(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
Task<T>	A task representing the asynchronous operation. Result will contain an instance of the type `T` containing the data of the single row of the result set returned by the statement or the default of `T` if the statement did not return any rows.

Type Parameters

Name	Description
T	The type of object to materialize the single row of the result set to. See remarks for details.

Remarks

T can be any of the following types:

A built-in .NET type or a nullable built-in .NET type like DateTime or string. In this case only the first column of the result set will be read and converted to the type T. Other columns in the result set will be ignored.

        All columns returned by the SQL statement must have a name.

        The type <code class="typeparamref">T</code> must either:

        1. Have a constructor whose parameters match the columns of the result set returned by the
           statement.
           The names of the parameters must match the names of the columns (case-insensitive).
           The types of the parameters must be compatible with the data types of the columns.
           The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.
           The parameters can be in any order.

        Or

        2. Have a parameterless constructor and properties (with public setters) that match the columns of
           the result set returned by the statement.

           Per default, the names of the properties must match the names of the columns (case-insensitive).
           This can be configured via <xref href="System.ComponentModel.DataAnnotations.Schema.ColumnAttribute" data-throw-if-not-resolved="false"></xref> or <xref href="RentADeveloper.DbConnectionPlus.DbConnectionExtensions.Configure(System.Action%7bRentADeveloper.DbConnectionPlus.Configuration.DbConnectionPlusConfiguration%7d)" data-throw-if-not-resolved="false"></xref>.

           The types of the properties must be compatible with the data types of the columns.
           The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.

           Columns without a matching property will be ignored.

        If neither condition is satisfied, an <xref href="System.ArgumentException" data-throw-if-not-resolved="false"></xref> will be thrown.

        If a constructor parameter or a property cannot be set to the value of the corresponding column
        due to a type mismatch, an <xref href="System.InvalidCastException" data-throw-if-not-resolved="false"></xref> will be thrown.
    </li><li><span class="term">A <xref href="System.ValueTuple" data-throw-if-not-resolved="false"></xref> type like <xref href="System.ValueTuple%603" data-throw-if-not-resolved="false"></xref></span>
        In this case the single row in the result set will be materialized into an instance of the value
        tuple type, with the fields of the value tuple being populated from the corresponding columns of the
        row.

        All columns returned by the SQL statement must have a name.
        The SQL statement must return the same number of columns as the value tuple has fields.
        The SQL statement must return the columns in the same order as the fields in the value tuple.

        The data types of the columns must be compatible with the field types of the value tuple.
        The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.

        If those conditions are not met, an <xref href="System.ArgumentException" data-throw-if-not-resolved="false"></xref> is thrown.
    </li></ol>

See ExecuteReader() for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

public class Order
{
    [Key]
    public Int64 Id { get; set; }
    public DateTime OrderDate { get; set; }
    public Decimal TotalAmount { get; set; }
}

var order = await connection.QuerySingleOrDefaultAsync<Order>(
    $"SELECT * FROM [Order] WHERE Id = {Parameter(orderId)}"
);

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
ArgumentException	`T` is an entity type or a ValueTuple type and the SQL statement returned no columns. `T` is an entity type or a ValueTuple type and the SQL statement returned a column without a name. `T` is an entity type or a ValueTuple type and the SQL statement returned a column with an unsupported data type. `T` is an entity type or a ValueTuple type that does not satisfy the conditions described in the remarks.
InvalidCastException	`T` is a built-in type or a nullable built-in type and the first column of the result set returned by the statement contains a value that could not be converted to the type `T`. `T` is an entity type and a column value returned by the statement could not be converted to the type of the corresponding constructor parameter or property of the type `T`. `T` is a ValueTuple type and a column value returned by the statement could not be converted to the type of the corresponding field of the type `T`.
InvalidOperationException	The SQL statement did return more than one row.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

QuerySingleOrDefault<T>(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Executes the specified SQL statement and materializes the single row of the result set returned by the statement into an instance of the type T or the default of T if the statement did not return any rows.

Declaration

public static T? QuerySingleOrDefault<T>(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
T	An instance of the type `T` containing the data of the single row of the result set returned by the statement or the default of `T` if the statement did not return any rows.

Type Parameters

Name	Description
T	The type of object to materialize the single row of the result set to. See remarks for details.

Remarks

T can be any of the following types:

A built-in .NET type or a nullable built-in .NET type like DateTime or string. In this case only the first column of the result set will be read and converted to the type T. Other columns in the result set will be ignored.

        All columns returned by the SQL statement must have a name.

        The type <code class="typeparamref">T</code> must either:

        1. Have a constructor whose parameters match the columns of the result set returned by the
           statement.
           The names of the parameters must match the names of the columns (case-insensitive).
           The types of the parameters must be compatible with the data types of the columns.
           The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.
           The parameters can be in any order.

        Or

        2. Have a parameterless constructor and properties (with public setters) that match the columns of
           the result set returned by the statement.

           Per default, the names of the properties must match the names of the columns (case-insensitive).
           This can be configured via <xref href="System.ComponentModel.DataAnnotations.Schema.ColumnAttribute" data-throw-if-not-resolved="false"></xref> or <xref href="RentADeveloper.DbConnectionPlus.DbConnectionExtensions.Configure(System.Action%7bRentADeveloper.DbConnectionPlus.Configuration.DbConnectionPlusConfiguration%7d)" data-throw-if-not-resolved="false"></xref>.

           The types of the properties must be compatible with the data types of the columns.
           The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.

           Columns without a matching property will be ignored.

        If neither condition is satisfied, an <xref href="System.ArgumentException" data-throw-if-not-resolved="false"></xref> will be thrown.

        If a constructor parameter or a property cannot be set to the value of the corresponding column
        due to a type mismatch, an <xref href="System.InvalidCastException" data-throw-if-not-resolved="false"></xref> will be thrown.
    </li><li><span class="term">A <xref href="System.ValueTuple" data-throw-if-not-resolved="false"></xref> type like <xref href="System.ValueTuple%603" data-throw-if-not-resolved="false"></xref></span>
        In this case the single row in the result set will be materialized into an instance of the value
        tuple type, with the fields of the value tuple being populated from the corresponding columns of the
        row.

        All columns returned by the SQL statement must have a name.
        The SQL statement must return the same number of columns as the value tuple has fields.
        The SQL statement must return the columns in the same order as the fields in the value tuple.

        The data types of the columns must be compatible with the field types of the value tuple.
        The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.

        If those conditions are not met, an <xref href="System.ArgumentException" data-throw-if-not-resolved="false"></xref> is thrown.
    </li></ol>

See ExecuteReader() for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

public class Order
{
    [Key]
    public Int64 Id { get; set; }
    public DateTime OrderDate { get; set; }
    public Decimal TotalAmount { get; set; }
}

var order = connection.QuerySingleOrDefault<Order>($"SELECT * FROM [Order] WHERE Id = {Parameter(orderId)}");

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
ArgumentException	`T` is an entity type or a ValueTuple type and the SQL statement returned no columns. `T` is an entity type or a ValueTuple type and the SQL statement returned a column without a name. `T` is an entity type or a ValueTuple type and the SQL statement returned a column with an unsupported data type. `T` is an entity type or a ValueTuple type that does not satisfy the conditions described in the remarks.
InvalidCastException	`T` is a built-in type or a nullable built-in type and the first column of the result set returned by the statement contains a value that could not be converted to the type `T`. `T` is an entity type and a column value returned by the statement could not be converted to the type of the corresponding constructor parameter or property of the type `T`. `T` is a ValueTuple type and a column value returned by the statement could not be converted to the type of the corresponding field of the type `T`.
InvalidOperationException	The SQL statement did return more than one row.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

QuerySingle<T>(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Executes the specified SQL statement and materializes the single row of the result set returned by the statement into an instance of the type T.

Declaration

public static T QuerySingle<T>(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
T	An instance of the type `T` containing the data of the single row of the result set returned by the statement.

Type Parameters

Name	Description
T	The type of object to materialize the single row of the result set to. See remarks for details.

Remarks

T can be any of the following types:

A built-in .NET type or a nullable built-in .NET type like DateTime or string. In this case only the first column of the result set will be read and converted to the type T. Other columns in the result set will be ignored.

        All columns returned by the SQL statement must have a name.

        The type <code class="typeparamref">T</code> must either:

        1. Have a constructor whose parameters match the columns of the result set returned by the
           statement.
           The names of the parameters must match the names of the columns (case-insensitive).
           The types of the parameters must be compatible with the data types of the columns.
           The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.
           The parameters can be in any order.

        Or

        2. Have a parameterless constructor and properties (with public setters) that match the columns of
           the result set returned by the statement.

           Per default, the names of the properties must match the names of the columns (case-insensitive).
           This can be configured via <xref href="System.ComponentModel.DataAnnotations.Schema.ColumnAttribute" data-throw-if-not-resolved="false"></xref> or <xref href="RentADeveloper.DbConnectionPlus.DbConnectionExtensions.Configure(System.Action%7bRentADeveloper.DbConnectionPlus.Configuration.DbConnectionPlusConfiguration%7d)" data-throw-if-not-resolved="false"></xref>.

           The types of the properties must be compatible with the data types of the columns.
           The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.

           Columns without a matching property will be ignored.

        If neither condition is satisfied, an <xref href="System.ArgumentException" data-throw-if-not-resolved="false"></xref> will be thrown.

        If a constructor parameter or a property cannot be set to the value of the corresponding column
        due to a type mismatch, an <xref href="System.InvalidCastException" data-throw-if-not-resolved="false"></xref> will be thrown.
    </li><li><span class="term">A <xref href="System.ValueTuple" data-throw-if-not-resolved="false"></xref> type like <xref href="System.ValueTuple%603" data-throw-if-not-resolved="false"></xref></span>
        In this case the single row in the result set will be materialized into an instance of the value
        tuple type, with the fields of the value tuple being populated from the corresponding columns of the
        row.

        All columns returned by the SQL statement must have a name.
        The SQL statement must return the same number of columns as the value tuple has fields.
        The SQL statement must return the columns in the same order as the fields in the value tuple.

        The data types of the columns must be compatible with the field types of the value tuple.
        The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.

        If those conditions are not met, an <xref href="System.ArgumentException" data-throw-if-not-resolved="false"></xref> is thrown.
    </li></ol>

See ExecuteReader() for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

public class Order
{
    [Key]
    public Int64 Id { get; set; }
    public DateTime OrderDate { get; set; }
    public Decimal TotalAmount { get; set; }
}

var order = connection.QuerySingle<Order>($"SELECT * FROM [Order] WHERE Id = {Parameter(orderId)}");

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
ArgumentException	`T` is an entity type or a ValueTuple type and the SQL statement returned no columns. `T` is an entity type or a ValueTuple type and the SQL statement returned a column without a name. `T` is an entity type or a ValueTuple type and the SQL statement returned a column with an unsupported data type. `T` is an entity type or a ValueTuple type that does not satisfy the conditions described in the remarks.
InvalidCastException	`T` is a built-in type or a nullable built-in type and the first column of the result set returned by the statement contains a value that could not be converted to the type `T`. `T` is an entity type and a column value returned by the statement could not be converted to the type of the corresponding constructor parameter or property of the type `T`. `T` is a ValueTuple type and a column value returned by the statement could not be converted to the type of the corresponding field of the type `T`.
InvalidOperationException	The SQL statement did not return any rows. The SQL statement did return more than one row.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

Query<T>(DbConnection, InterpolatedSqlStatement, DbTransaction?, TimeSpan?, CommandType, CancellationToken)

Executes the specified SQL statement and materializes the result set returned by the statement into a sequence of instances of the type T.

Declaration

public static IEnumerable<T> Query<T>(this DbConnection connection, InterpolatedSqlStatement statement, DbTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to execute the statement.
InterpolatedSqlStatement	statement	The SQL statement to execute.
DbTransaction	transaction	The database transaction within to execute the statement.
TimeSpan?	commandTimeout	The timeout to use for the execution of the statement.
CommandType	commandType	A value indicating how `statement` is to be interpreted.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
IEnumerable<T>	A sequence of instances of the type `T` containing the data of the result set returned by the statement.

Type Parameters

Name	Description
T	The type of objects to materialize the result set to. See remarks for details.

Remarks

T can be any of the following types:

A built-in .NET type or a nullable built-in .NET type like DateTime or string. In this case only the first column of the result set will be read and converted to the type T. Other columns in the result set will be ignored.

        All columns returned by the SQL statement must have a name.

        The type <code class="typeparamref">T</code> must either:

        1. Have a constructor whose parameters match the columns of the result set returned by the
           statement.
           The names of the parameters must match the names of the columns (case-insensitive).
           The types of the parameters must be compatible with the data types of the columns.
           The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.
           The parameters can be in any order.

        Or

        2. Have a parameterless constructor and properties (with public setters) that match the columns of
           the result set returned by the statement.

           Per default, the names of the properties must match the names of the columns (case-insensitive).
           This can be configured via <xref href="System.ComponentModel.DataAnnotations.Schema.ColumnAttribute" data-throw-if-not-resolved="false"></xref> or <xref href="RentADeveloper.DbConnectionPlus.DbConnectionExtensions.Configure(System.Action%7bRentADeveloper.DbConnectionPlus.Configuration.DbConnectionPlusConfiguration%7d)" data-throw-if-not-resolved="false"></xref>.

           The types of the properties must be compatible with the data types of the columns.
           The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.

           Columns without a matching property will be ignored.

        If neither condition is satisfied, an <xref href="System.ArgumentException" data-throw-if-not-resolved="false"></xref> will be thrown.

        If a constructor parameter or a property cannot be set to the value of the corresponding column
        due to a type mismatch, an <xref href="System.InvalidCastException" data-throw-if-not-resolved="false"></xref> will be thrown.
    </li><li><span class="term">A <xref href="System.ValueTuple" data-throw-if-not-resolved="false"></xref> type like <xref href="System.ValueTuple%603" data-throw-if-not-resolved="false"></xref></span>
        In this case each row in the result set will be materialized into an instance of the value tuple
        type, with the fields of the value tuple being populated from the corresponding columns of the
        row.

        All columns returned by the SQL statement must have a name.
        The SQL statement must return the same number of columns as the value tuple has fields.
        The SQL statement must return the columns in the same order as the fields in the value tuple.

        The data types of the columns must be compatible with the field types of the value tuple.
        The compatibility is determined using <xref href="RentADeveloper.DbConnectionPlus.Converters.ValueConverter.CanConvert(System.Type%2cSystem.Type)" data-throw-if-not-resolved="false"></xref>.

        If those conditions are not met, an <xref href="System.ArgumentException" data-throw-if-not-resolved="false"></xref> is thrown.
    </li></ol>

See ExecuteReader() for additional exceptions this method may throw.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

var lowStockProducts = connection.Query<Product>(
   $"SELECT * FROM Product WHERE UnitsInStock < {Parameter(lowStockThreshold)}"
);

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
ArgumentException	`T` is an entity type or a ValueTuple type and the SQL statement returned no columns. `T` is an entity type or a ValueTuple type and the SQL statement returned a column without a name. `T` is an entity type or a ValueTuple type and the SQL statement returned a column with an unsupported data type. `T` is an entity type or a ValueTuple type that does not satisfy the conditions described in the remarks.
InvalidCastException	`T` is a built-in type or a nullable built-in type and the first column of the result set returned by the statement contains a value that could not be converted to the type `T`. `T` is an entity type and a column value returned by the statement could not be converted to the type of the corresponding constructor parameter or property of the type `T`. `T` is a ValueTuple type and a column value returned by the statement could not be converted to the type of the corresponding field of the type `T`.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

View Source

TemporaryTable<T>(IEnumerable<T>, string?)

Wraps the sequence values in an instance of InterpolatedTemporaryTable to indicate that this sequence should be passed as a temporary table to an SQL statement.

Use this method to pass a sequence of scalar values or complex objects in an interpolated string as a temporary table to an SQL statement.

Declaration

public static InterpolatedTemporaryTable TemporaryTable<T>(IEnumerable<T> values, string? valuesExpression = null)

Parameters

Type	Name	Description
IEnumerable<T>	values	The sequence of scalar values or complex objects to pass as a temporary table.
string	valuesExpression	The expression from which `valuesExpression` was obtained. Used to infer the name for the temporary table. This parameter is optional and is automatically provided by the compiler.

Returns

Type	Description
InterpolatedTemporaryTable	An instance of InterpolatedTemporaryTable indicating that the sequence `values` should be passed as a temporary table to an SQL statement.

Type Parameters

Name	Description
T	The type of values in `values`.

Remarks

To use this method import DbConnectionExtensions with a using directive with the static modifier:

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

You can pass a sequence of scalar values (e.g. string, int, DateTime, Enum and so on) or a sequence of complex objects.

If a sequence of scalar values is passed, the temporary table will have a single column named "Value" with a data type that is compatible with the type of the passed values.

Example:

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var retiredSupplierIds = suppliers.Where(a => a.IsRetired).Select(a => a.Id);

var retiredSupplierProductsReader = connection.ExecuteReader(
   $"""
    SELECT  *
    FROM    Product
    WHERE   SupplierId IN (
                SELECT  Value
                FROM    {TemporaryTable(retiredSupplierIds)}
            )
    """
);

This will create a temporary table with a single column named "Value" with a data type that matches the type of the passed values:

CREATE TABLE #RetiredSupplierIds_48d42afd5d824a27bd9352676ab6c198
(
    Value BIGINT
)

If a sequence of complex objects is passed, the temporary table will have multiple columns. The temporary table will contain a column for each instance property (with a public getter) of the passed objects. The name of each column will be the name of the corresponding property. If a property is denoted with the ColumnAttribute, the name specified in the attribute is used as the column name. The data type of each column will be compatible with the property type of the corresponding property.

Example:

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

class OrderItem
{
    public Int64 ProductId { get; set; }
    public DateTime OrderDate { get; set; }
}

var orderItems = GetOrderItems();
var sixMonthsAgo = DateTime.UtcNow.AddMonths(-6);

var productsOrderedInPastSixMonthsReader = connection.ExecuteReader(
    $"""
     SELECT     *
     FROM       Product
     WHERE      EXISTS (
                    SELECT  1
                    FROM    {TemporaryTable(orderItems)} TOrderItem
                    WHERE   TOrderItem.ProductId = Product.Id AND
                            TOrderItem.OrderDate >= {Parameter(sixMonthsAgo)}
                )
     """
);

This will create a temporary table with columns matching the properties of the passed objects:

CREATE TABLE #OrderItems_d6545835d97148ab93709efe9ba1f110
(
    ProductId BIGINT,
    OrderDate DATETIME2
)

The name of the temporary table will be inferred from the expression passed to TemporaryTable<T>(IEnumerable<T>, string?) and suffixed with a new Guid to avoid naming conflicts (e.g. "OrderItems_395c98f203514e81aa0098ec7f13e8a2").

If the name cannot be inferred from the expression the name "Values" (also suffixed with a new Guid) will be used (e.g. "Values_395c98f203514e81aa0098ec7f13e8a2").

If you pass enum values or objects containing enum properties, the enum values are serialized according to the setting EnumSerializationMode.

Exceptions

Type	Condition
ArgumentException	`T` is the type object.
ArgumentNullException	`values` is null.

View Source

UpdateEntitiesAsync<TEntity>(DbConnection, IEnumerable<TEntity>, DbTransaction?, CancellationToken)

Asynchronously updates the specified entities, identified by their key property / properties, in the database.

Declaration

public static Task<int> UpdateEntitiesAsync<TEntity>(this DbConnection connection, IEnumerable<TEntity> entities, DbTransaction? transaction = null, CancellationToken cancellationToken = default) where TEntity : class

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to update the entities.
IEnumerable<TEntity>	entities	The entities to update.
DbTransaction	transaction	The database transaction within to perform the operation.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
Task<int>	A task representing the asynchronous operation. Result will contain the number of rows that were affected by the update operation.

Type Parameters

Name	Description
TEntity	The type of entities to update.

Remarks

The table in which the entities will be updated can be configured via TableAttribute or Configure(Action<DbConnectionPlusConfiguration>). Per default, the singular name of the type TEntity is used as the table name.

The type TEntity must have at least one instance property configured as key property. Use KeyAttribute or Configure(Action<DbConnectionPlusConfiguration>) to configure key properties.

Properties configured as ignored properties (via NotMappedAttribute or Configure(Action<DbConnectionPlusConfiguration>)) are not updated.

Properties configured as identity or computed properties (via DatabaseGeneratedAttribute or Configure(Action<DbConnectionPlusConfiguration>)) are also not updated. Once an entity is updated, the values for these properties are retrieved from the database and the entity properties are updated accordingly.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

class User
{
    [Key]
    public Int64 Id { get; set; }
    public DateTime LastLoginDate { get; set; }
    public UserState State { get; set; }
}

var usersWithoutLoginInPastYear = connection.QueryAsync<User>(
    """
    SELECT  *
    FROM    Users
    WHERE   LastLoginDate < DATEADD(YEAR, -1, GETUTCDATE())
    """
);

await foreach (var user in usersWithoutLoginInPastYear)
{
    user.State = UserState.Inactive;
}

await connection.UpdateEntitiesAsync(usersWithoutLoginInPastYear);

Exceptions

Type	Condition
ArgumentException	No instance property of the type `TEntity` is configured as a key property. KeyAttribute.
ArgumentNullException	`connection` is null. `entities` is null.
DbUpdateConcurrencyException	A concurrency violation was encountered while updating an entity. A concurrency violation occurs when an unexpected number of rows are affected by an update operation. This is usually because the data in the database has been modified since the entity has been loaded.
OperationCanceledException	The operation was cancelled via `cancellationToken`.

View Source

UpdateEntities<TEntity>(DbConnection, IEnumerable<TEntity>, DbTransaction?, CancellationToken)

Updates the specified entities, identified by their key property / properties, in the database.

Declaration

public static int UpdateEntities<TEntity>(this DbConnection connection, IEnumerable<TEntity> entities, DbTransaction? transaction = null, CancellationToken cancellationToken = default) where TEntity : class

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to update the entities.
IEnumerable<TEntity>	entities	The entities to update.
DbTransaction	transaction	The database transaction within to perform the operation.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
int	The number of rows that were affected by the update operation.

Type Parameters

Name	Description
TEntity	The type of entities to update.

Remarks

The type TEntity must have at least one instance property configured as key property. Use KeyAttribute or Configure(Action<DbConnectionPlusConfiguration>) to configure key properties.

Properties configured as ignored properties (via NotMappedAttribute or Configure(Action<DbConnectionPlusConfiguration>)) are not updated.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

class User
{
    [Key]
    public Int64 Id { get; set; }
    public DateTime LastLoginDate { get; set; }
    public UserState State { get; set; }
}

var usersWithoutLoginInPastYear = connection.Query<User>(
    """
    SELECT  *
    FROM    Users
    WHERE   LastLoginDate < DATEADD(YEAR, -1, GETUTCDATE())
    """
);

foreach (var user in usersWithoutLoginInPastYear)
{
    user.State = UserState.Inactive;
}

connection.UpdateEntities(usersWithoutLoginInPastYear);

Exceptions

Type	Condition
ArgumentException	No instance property of the type `TEntity` is configured as a key property.
ArgumentNullException	`connection` is null. `entities` is null.
DbUpdateConcurrencyException	A concurrency violation was encountered while updating an entity. A concurrency violation occurs when an unexpected number of rows are affected by an update operation. This is usually because the data in the database has been modified since the entity has been loaded.
OperationCanceledException	The operation was cancelled via `cancellationToken`.

View Source

UpdateEntityAsync<TEntity>(DbConnection, TEntity, DbTransaction?, CancellationToken)

Asynchronously updates the specified entity, identified by its key property / properties, in the database.

Declaration

public static Task<int> UpdateEntityAsync<TEntity>(this DbConnection connection, TEntity entity, DbTransaction? transaction = null, CancellationToken cancellationToken = default) where TEntity : class

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to update the entity.
TEntity	entity	The entity to update.
DbTransaction	transaction	The database transaction within to perform the operation.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
Task<int>	A task representing the asynchronous operation. Result will contain the number of rows that were affected by the update operation.

Type Parameters

Name	Description
TEntity	The type of entity to update.

Remarks

The table in which the entity will be updated can be configured via TableAttribute or Configure(Action<DbConnectionPlusConfiguration>). Per default, the singular name of the type TEntity is used as the table name.

The type TEntity must have at least one instance property configured as key property. Use KeyAttribute or Configure(Action<DbConnectionPlusConfiguration>) to configure key properties.

Properties configured as ignored properties (via NotMappedAttribute or Configure(Action<DbConnectionPlusConfiguration>)) are not updated.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

class User
{
    [Key]
    public Int64 Id { get; set; }
    public DateTime LastLoginDate { get; set; }
    public UserState State { get; set; }
}

if (user.LastLoginDate < DateTime.UtcNow.AddYears(-1))
{
    user.State = UserState.Inactive;
    await connection.UpdateEntityAsync(user);
}

Exceptions

Type	Condition
ArgumentNullException	`connection` is null. `entity` is null.
ArgumentException	No instance property of the type `TEntity` is configured as a key property.
DbUpdateConcurrencyException	A concurrency violation was encountered while updating an entity. A concurrency violation occurs when an unexpected number of rows are affected by an update operation. This is usually because the data in the database has been modified since the entity has been loaded.
OperationCanceledException	The operation was cancelled via `cancellationToken`.

View Source

UpdateEntity<TEntity>(DbConnection, TEntity, DbTransaction?, CancellationToken)

Updates the specified entity, identified by its key property / properties, in the database.

Declaration

public static int UpdateEntity<TEntity>(this DbConnection connection, TEntity entity, DbTransaction? transaction = null, CancellationToken cancellationToken = default) where TEntity : class

Parameters

Type	Name	Description
DbConnection	connection	The database connection to use to update the entity.
TEntity	entity	The entity to update.
DbTransaction	transaction	The database transaction within to perform the operation.
CancellationToken	cancellationToken	A token that can be used to cancel the operation.

Returns

Type	Description
int	The number of rows that were affected by the update operation.

Type Parameters

Name	Description
TEntity	The type of entity to update.

Remarks

The type TEntity must have at least one instance property configured as key property. Use KeyAttribute or Configure(Action<DbConnectionPlusConfiguration>) to configure key properties.

Properties configured as ignored properties (via NotMappedAttribute or Configure(Action<DbConnectionPlusConfiguration>)) are not updated.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

class User
{
    [Key]
    public Int64 Id { get; set; }
    public DateTime LastLoginDate { get; set; }
    public UserState State { get; set; }
}

if (user.LastLoginDate < DateTime.UtcNow.AddYears(-1))
{
    user.State = UserState.Inactive;
    connection.UpdateEntity(user);
}

Exceptions

Type	Condition
ArgumentNullException	`connection` is null. `entity` is null.
ArgumentException	No instance property of the type `TEntity` is configured as a key property.
DbUpdateConcurrencyException	A concurrency violation was encountered while updating an entity. A concurrency violation occurs when an unexpected number of rows are affected by an update operation. This is usually because the data in the database has been modified since the entity has been loaded.
OperationCanceledException	The operation was cancelled via `cancellationToken`.