Class DbConnectionExtensions

Namespace

RentADeveloper.DbConnectionPlus

Assembly

RentADeveloper.DbConnectionPlus.dll

Provides extension members for the type DbConnection.

public static class DbConnectionExtensions

Inheritance

object

DbConnectionExtensions

Inherited Members

object.Equals(object)

object.Equals(object, object)

object.GetHashCode()

object.GetType()

object.MemberwiseClone()

object.ReferenceEquals(object, object)

object.ToString()

Methods

Configure(Action<DbConnectionPlusConfiguration>)

Configures DbConnectionPlus.

public static void Configure(Action<DbConnectionPlusConfiguration> configureAction)

Parameters

configureAction Action<DbConnectionPlusConfiguration>

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.

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

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

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

Parameters

connection DbConnection

The database connection to use to delete the entities.

entities IEnumerable<TEntity>

The entities to delete.

transaction DbTransaction

The database transaction within to perform the operation.

cancellationToken CancellationToken

A token that can be used to cancel the operation.

Returns

Task<int>

A task representing the asynchronous operation. Result will contain the number of rows affected by the delete operation.

Type Parameters

TEntity

The type of entities to delete.

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));

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.

Exceptions

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.

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

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

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

Parameters

connection DbConnection

The database connection to use to delete the entities.

entities IEnumerable<TEntity>

The entities to delete.

transaction DbTransaction

The database transaction within to perform the operation.

cancellationToken CancellationToken

A token that can be used to cancel the operation.

Returns

int

The number of rows affected by the delete operation.

Type Parameters

TEntity

The type of entities to delete.

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));

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.

Exceptions

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.

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

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

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

Parameters

connection DbConnection

The database connection to use to delete the entity.

entity TEntity

The entity to delete.

transaction DbTransaction

The database transaction within to perform the operation.

cancellationToken CancellationToken

A token that can be used to cancel the operation.

Returns

Task<int>

A task representing the asynchronous operation. Result will contain the number of rows affected by the delete operation.

Type Parameters

TEntity

The type of entity to delete.

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);
}

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.

Exceptions

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.

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

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

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

Parameters

connection DbConnection

The database connection to use to delete the entity.

entity TEntity

The entity to delete.

transaction DbTransaction

The database transaction within to perform the operation.

cancellationToken CancellationToken

A token that can be used to cancel the operation.

Returns

int

The number of rows affected by the delete operation.

Type Parameters

TEntity

The type of entity to delete.

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);
}

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.

Exceptions

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.

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

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

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

Parameters

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

int

The number of rows affected by the statement.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

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

Remarks

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

Exceptions

ArgumentNullException

connection is null.

OperationCanceledException

The statement was cancelled via cancellationToken.

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

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

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

Parameters

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

Task<int>

A task representing the asynchronous operation. Result will contain the number of rows affected by the statement.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

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

Remarks

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

Exceptions

ArgumentNullException

connection is null.

OperationCanceledException

The statement was cancelled via cancellationToken.

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

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

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

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

DbDataReader

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

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

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

Remarks

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

Exceptions

ArgumentNullException

connection is null.

OperationCanceledException

The statement was cancelled via cancellationToken.

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

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

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

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

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.

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)}"
);

Remarks

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

Exceptions

ArgumentNullException

connection is null.

OperationCanceledException

The statement was cancelled via cancellationToken.

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.

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

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

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

TTarget

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

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)}"
);

Remarks

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

Exceptions

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.

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.

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

Parameters

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

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

TTarget

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

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

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

Remarks

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

Exceptions

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.

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.

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

Parameters

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

bool

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

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

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

Remarks

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

Exceptions

ArgumentNullException

connection is null.

OperationCanceledException

The statement was cancelled via cancellationToken.

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.

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

Parameters

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

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.

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

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

Remarks

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

Exceptions

ArgumentNullException

connection is null.

OperationCanceledException

The statement was cancelled via cancellationToken.

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

Asynchronously inserts the specified entities into the database.

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

Parameters

connection DbConnection

The database connection to use to insert the entities.

entities IEnumerable<TEntity>

The entities to insert.

transaction DbTransaction

The database transaction within to perform the operation.

cancellationToken CancellationToken

A token that can be used to cancel the operation.

Returns

Task<int>

A task representing the asynchronous operation. Result will contain the number of rows that were affected by the insert operation.

Type Parameters

TEntity

The type of entities to insert.

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);

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 CanConvert(Type, 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.

Exceptions

ArgumentNullException

• connection is null.
• entities is null.

OperationCanceledException

The operation was cancelled via cancellationToken.

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

Inserts the specified entities into the database.

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

Parameters

connection DbConnection

The database connection to use to insert the entities.

entities IEnumerable<TEntity>

The entities to insert.

transaction DbTransaction

The database transaction within to perform the operation.

cancellationToken CancellationToken

A token that can be used to cancel the operation.

Returns

int

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

Type Parameters

TEntity

The type of entities to insert.

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);

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 CanConvert(Type, 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.

Exceptions

ArgumentNullException

• connection is null.
• entities is null.

OperationCanceledException

The operation was cancelled via cancellationToken.

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

Asynchronously inserts the specified entity into the database.

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

Parameters

connection DbConnection

The database connection to use to insert the entity.

entity TEntity

The entity to insert.

transaction DbTransaction

The database transaction within to perform the operation.

cancellationToken CancellationToken

A token that can be used to cancel the operation.

Returns

Task<int>

A task representing the asynchronous operation. Result will contain the number of rows that were affected by the insert operation.

Type Parameters

TEntity

The type of entity to insert.

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);

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.

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 CanConvert(Type, 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.

Exceptions

ArgumentNullException

• connection is null.
• entity is null.

OperationCanceledException

The operation was cancelled via cancellationToken.

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

Inserts the specified entity into the database.

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

Parameters

connection DbConnection

The database connection to use to insert the entity.

entity TEntity

The entity to insert.

transaction DbTransaction

The database transaction within to perform the operation.

cancellationToken CancellationToken

A token that can be used to cancel the operation.

Returns

int

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

Type Parameters

TEntity

The type of entity to insert.

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);

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.

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 CanConvert(Type, 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.

Exceptions

ArgumentNullException

• connection is null.
• entity is null.

OperationCanceledException

The operation was cancelled via cancellationToken.

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.

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

Parameters

parameterValue object

The value to pass as a parameter.

parameterValueExpression string

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

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.

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 DataRow instances. Each row of the result set is mapped to a DataRow where each column is available through the string indexer under the same name as the column.

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

Parameters

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

IEnumerable<DataRow>

A sequence of DataRow instances containing the data of the result set returned by the statement.

Examples

Read the columns through the string indexer. This works on every runtime, including in an application published with Native AOT:

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"];
...
}

Where the runtime supports dynamic code generation, the columns can also be read and written as members through a dynamic reference. This is not available in an application published with Native AOT, where the string indexer above must be used:

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;
var products = connection.Query($"SELECT * FROM Product WHERE CategoryId = {Parameter(categoryId)}");
foreach (dynamic product in products)
{
var id = product.Id;
var name = product.Name;
...
}

Remarks

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

Exceptions

ArgumentNullException

connection is null.

OperationCanceledException

The statement was cancelled via cancellationToken.

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 DataRow instances. Each row of the result set is mapped to a DataRow where each column is available through the string indexer under the same name as the column.

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

Parameters

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

IAsyncEnumerable<DataRow>

An asynchronous sequence of DataRow instances containing the data of the result set returned by the statement.

Examples

Read the columns through the string indexer. This works on every runtime, including in an application published with Native AOT:

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"];
...
}

Where the runtime supports dynamic code generation, the columns can also be read and written as members through a dynamic reference. This is not available in an application published with Native AOT, where the string indexer above must be used:

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;
var products = connection.QueryAsync($"SELECT * FROM Product WHERE CategoryId = {Parameter(categoryId)}");
await foreach (dynamic product in products)
{
var id = product.Id;
var name = product.Name;
...
}

Remarks

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

Exceptions

ArgumentNullException

connection is null.

OperationCanceledException

The statement was cancelled via cancellationToken.

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.

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

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

IAsyncEnumerable<T>

An asynchronous sequence of instances of the type T containing the data of the result set returned by the statement.

Type Parameters

T

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

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

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

Remarks

T can be any of the following types:

1. 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.
2. 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.

Exceptions

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.

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 DataRow where each column is available through the string indexer under the same name as the column.

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

Parameters

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

DataRow

A DataRow containing the data of the first row of the result set returned by the statement.

Examples

Read the columns through the string indexer. This works on every runtime, including in an application published with Native AOT:

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;
var product = connection.QueryFirst($"SELECT * FROM Product WHERE Id = {Parameter(id)}");
var id = product["Id"];
var name = product["Name"];
...

Where the runtime supports dynamic code generation, the columns can also be read and written as members through a dynamic reference. This is not available in an application published with Native AOT, where the string indexer above must be used:

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;
dynamic product = connection.QueryFirst($"SELECT * FROM Product WHERE Id = {Parameter(id)}");
var id = product.Id;
var name = product.Name;
...

Remarks

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

Exceptions

ArgumentNullException

connection is null.

InvalidOperationException

The SQL statement did not return any rows.

OperationCanceledException

The statement was cancelled via cancellationToken.

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 DataRow where each column is available through the string indexer under the same name as the column.

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

Parameters

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

Task<DataRow>

A task representing the asynchronous operation. Result will contain a DataRow containing the data of the first row of the result set returned by the statement.

Examples

Read the columns through the string indexer. This works on every runtime, including in an application published with Native AOT:

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"];
...

Where the runtime supports dynamic code generation, the columns can also be read and written as members through a dynamic reference. This is not available in an application published with Native AOT, where the string indexer above must be used:

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;
dynamic product = await connection.QueryFirstAsync($"SELECT * FROM Product WHERE Id = {Parameter(id)}");
var id = product.Id;
var name = product.Name;
...

Remarks

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

Exceptions

ArgumentNullException

connection is null.

InvalidOperationException

The SQL statement did not return any rows.

OperationCanceledException

The statement was cancelled via cancellationToken.

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.

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

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

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

T

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

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)}");

Remarks

T can be any of the following types:

1. 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.
2. 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.

Exceptions

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.

QueryFirstOrDefault(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 DataRow where each column is available through the string indexer under the same name as the column, or null if the statement did not return any rows.

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

Parameters

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

DataRow

A DataRow 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.

Examples

Read the columns through the string indexer. This works on every runtime, including in an application published with Native AOT:

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"];
...
}

Where the runtime supports dynamic code generation, the columns can also be read and written as members through a dynamic reference. This is not available in an application published with Native AOT, where the string indexer above must be used:

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;
dynamic? product = connection.QueryFirstOrDefault($"SELECT * FROM Product WHERE Id = {Parameter(id)}");
if (product is not null)
{
var id = product.Id;
var name = product.Name;
...
}

Remarks

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

Exceptions

ArgumentNullException

connection is null.

OperationCanceledException

The statement was cancelled via cancellationToken.

QueryFirstOrDefaultAsync(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 DataRow where each column is available through the string indexer under the same name as the column, or null if the statement did not return any rows.

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

Parameters

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

Task<DataRow>

A task representing the asynchronous operation. Result will contain a DataRow 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.

Examples

Read the columns through the string indexer. This works on every runtime, including in an application published with Native AOT:

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"];
...
}

Where the runtime supports dynamic code generation, the columns can also be read and written as members through a dynamic reference. This is not available in an application published with Native AOT, where the string indexer above must be used:

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;
dynamic? product = await connection.QueryFirstOrDefaultAsync($"SELECT * FROM Product WHERE Id = {Parameter(id)}");
if (product is not null)
{
var id = product.Id;
var name = product.Name;
...
}

Remarks

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

Exceptions

ArgumentNullException

connection is null.

OperationCanceledException

The statement was cancelled via cancellationToken.

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.

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

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

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

T

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

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)}"
);

Remarks

T can be any of the following types:

1. 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.
2. 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.

Exceptions

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.

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.

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

Parameters

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

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

T

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

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)}");

Remarks

T can be any of the following types:

1. 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.
2. 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.

Exceptions

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.

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.

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

Parameters

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

T

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

Type Parameters

T

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

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)}");

Remarks

T can be any of the following types:

1. 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.
2. 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.

Exceptions

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.

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 DataRow where each column is available through the string indexer under the same name as the column.

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

Parameters

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

DataRow

A DataRow containing the data of the single row of the result set returned by the statement.

Examples

Read the columns through the string indexer. This works on every runtime, including in an application published with Native AOT:

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;
var product = connection.QuerySingle($"SELECT * FROM Product WHERE Id = {Parameter(id)}");
var id = product["Id"];
var name = product["Name"];
...

Where the runtime supports dynamic code generation, the columns can also be read and written as members through a dynamic reference. This is not available in an application published with Native AOT, where the string indexer above must be used:

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;
dynamic product = connection.QuerySingle($"SELECT * FROM Product WHERE Id = {Parameter(id)}");
var id = product.Id;
var name = product.Name;
...

Remarks

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

Exceptions

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.

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 DataRow where each column is available through the string indexer under the same name as the column.

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

Parameters

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

Task<DataRow>

A task representing the asynchronous operation. Result will contain a DataRow containing the data of the single row of the result set returned by the statement.

Examples

Read the columns through the string indexer. This works on every runtime, including in an application published with Native AOT:

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"];
...

Where the runtime supports dynamic code generation, the columns can also be read and written as members through a dynamic reference. This is not available in an application published with Native AOT, where the string indexer above must be used:

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;
dynamic product = await connection.QuerySingleAsync($"SELECT * FROM Product WHERE Id = {Parameter(id)}");
var id = product.Id;
var name = product.Name;
...

Remarks

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

Exceptions

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.

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.

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

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

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

T

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

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)}");

Remarks

T can be any of the following types:

1. 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.
2. 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.

Exceptions

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.

QuerySingleOrDefault(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 DataRow where each column is available through the string indexer under the same name as the column, or null if the statement did not return any rows.

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

Parameters

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

DataRow

A DataRow 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.

Examples

Read the columns through the string indexer. This works on every runtime, including in an application published with Native AOT:

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"];
...
}

Where the runtime supports dynamic code generation, the columns can also be read and written as members through a dynamic reference. This is not available in an application published with Native AOT, where the string indexer above must be used:

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;
dynamic? product = connection.QuerySingleOrDefault($"SELECT * FROM Product WHERE Id = {Parameter(id)}");
if (product is not null)
{
var id = product.Id;
var name = product.Name;
...
}

Remarks

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

Exceptions

ArgumentNullException

connection is null.

InvalidOperationException

The SQL statement did return more than one row.

OperationCanceledException

The statement was cancelled via cancellationToken.

QuerySingleOrDefaultAsync(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 DataRow where each column is available through the string indexer under the same name as the column, or null if the statement did not return any rows.

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

Parameters

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

Task<DataRow>

A task representing the asynchronous operation. Result will contain a DataRow 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.

Examples

Read the columns through the string indexer. This works on every runtime, including in an application published with Native AOT:

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"];
...
}

Where the runtime supports dynamic code generation, the columns can also be read and written as members through a dynamic reference. This is not available in an application published with Native AOT, where the string indexer above must be used:

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;
dynamic? product = await connection.QuerySingleOrDefaultAsync($"SELECT * FROM Product WHERE Id = {Parameter(id)}");
if (product is not null)
{
var id = product.Id;
var name = product.Name;
...
}

Remarks

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

Exceptions

ArgumentNullException

connection is null.

InvalidOperationException

The SQL statement did return more than one row.

OperationCanceledException

The statement was cancelled via cancellationToken.

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.

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

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

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

T

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

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)}"
);

Remarks

T can be any of the following types:

1. 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.
2. 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.

Exceptions

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.

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.

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

Parameters

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

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

T

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

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)}");

Remarks

T can be any of the following types:

1. 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.
2. 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.

Exceptions

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.

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.

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

Parameters

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

T

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

Type Parameters

T

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

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)}");

Remarks

T can be any of the following types:

1. 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.
2. 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.

Exceptions

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.

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.

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

connection DbConnection

The database connection to use to execute the statement.

statement InterpolatedSqlStatement

The SQL statement to execute.

transaction DbTransaction

The database transaction within to execute the statement.

commandTimeout TimeSpan?

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

IEnumerable<T>

A sequence of instances of the type T containing the data of the result set returned by the statement.

Type Parameters

T

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

Examples

using static RentADeveloper.DbConnectionPlus.DbConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

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

Remarks

T can be any of the following types:

1. 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.
2. 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.

Exceptions

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.

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.

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

Parameters

values IEnumerable<T>

The sequence of scalar values or complex objects to pass as a temporary table.

valuesExpression string

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

InterpolatedTemporaryTable

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

Type Parameters

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

ArgumentException

T is the type object.

ArgumentNullException

values is null.

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

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

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

Parameters

connection DbConnection

The database connection to use to update the entities.

entities IEnumerable<TEntity>

The entities to update.

transaction DbTransaction

The database transaction within to perform the operation.

cancellationToken CancellationToken

A token that can be used to cancel the operation.

Returns

Task<int>

A task representing the asynchronous operation. Result will contain the number of rows that were affected by the update operation.

Type Parameters

TEntity

The type of entities to update.

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);

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.

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 CanConvert(Type, Type).

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.

Exceptions

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.

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

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

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

Parameters

connection DbConnection

The database connection to use to update the entities.

entities IEnumerable<TEntity>

The entities to update.

transaction DbTransaction

The database transaction within to perform the operation.

cancellationToken CancellationToken

A token that can be used to cancel the operation.

Returns

int

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

Type Parameters

TEntity

The type of entities to update.

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);

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.

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 CanConvert(Type, Type).

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.

Exceptions

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.

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

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

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

Parameters

connection DbConnection

The database connection to use to update the entity.

entity TEntity

The entity to update.

transaction DbTransaction

The database transaction within to perform the operation.

cancellationToken CancellationToken

A token that can be used to cancel the operation.

Returns

Task<int>

A task representing the asynchronous operation. Result will contain the number of rows that were affected by the update operation.

Type Parameters

TEntity

The type of entity to update.

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);
}

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.

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 CanConvert(Type, Type).

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.

Exceptions

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.

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

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

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

Parameters

connection DbConnection

The database connection to use to update the entity.

entity TEntity

The entity to update.

transaction DbTransaction

The database transaction within to perform the operation.

cancellationToken CancellationToken

A token that can be used to cancel the operation.

Returns

int

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

Type Parameters

TEntity

The type of entity to update.

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);
}

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.

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 CanConvert(Type, Type).

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.

Exceptions

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.