Class SqlConnectionExtensions

Provides extension members for the type SqlConnection.

Inheritance

object

SqlConnectionExtensions

Inherited Members

object.Equals(object)

object.Equals(object, object)

object.GetHashCode()

object.GetType()

object.MemberwiseClone()

object.ReferenceEquals(object, object)

object.ToString()

Namespace: RentADeveloper.SqlConnectionPlus

Assembly: RentADeveloper.SqlConnectionPlus.dll

Syntax

public static class SqlConnectionExtensions

Properties

| Edit this page View Source

EnumSerializationMode

Controls how Enum values are serialized when they are sent to a database using one of the following methods:

When an entity containing an enum property is inserted via InsertEntities<TEntity>(SqlConnection, IEnumerable<TEntity>, SqlTransaction?, CancellationToken), InsertEntitiesAsync<TEntity>(SqlConnection, IEnumerable<TEntity>, SqlTransaction?, CancellationToken), InsertEntity<TEntity>(SqlConnection, TEntity, SqlTransaction?, CancellationToken) or InsertEntityAsync<TEntity>(SqlConnection, TEntity, SqlTransaction?, CancellationToken).
When an entity containing an enum property is updated via UpdateEntities<TEntity>(SqlConnection, IEnumerable<TEntity>, SqlTransaction?, CancellationToken), UpdateEntitiesAsync<TEntity>(SqlConnection, IEnumerable<TEntity>, SqlTransaction?, CancellationToken), UpdateEntity<TEntity>(SqlConnection, TEntity, SqlTransaction?, CancellationToken) or UpdateEntityAsync<TEntity>(SqlConnection, TEntity, SqlTransaction?, CancellationToken).
When an enum value is passed as a parameter to an SQL statement via Parameter(object?, string?).
When a sequence of enum values is passed as a temporary table to an SQL statement via TemporaryTable<T>(IEnumerable<T>, string?).
When objects containing an enum property are passed as a temporary table to an SQL statement via TemporaryTable<T>(IEnumerable<T>, string?).

The default is Strings.

Declaration

public static EnumSerializationMode EnumSerializationMode { get; set; }

Property Value

Type	Description
EnumSerializationMode

Remarks

Thread Safety: This is a static mutable property. To avoid race conditions in multi-threaded applications, set this property during application initialization before any database operations are performed, and do not change it afterward. Changing this value while database operations are in progress from multiple threads may lead to inconsistent enum serialization behavior.

Methods

| Edit this page View Source

DeleteEntitiesAsync<TEntity>(SqlConnection, IEnumerable<TEntity>, SqlTransaction?, CancellationToken)

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

Declaration

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

Parameters

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

Returns

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

Type Parameters

Name	Description
TEntity	The type of entities to delete.

Remarks

The table from which the entities will be deleted is determined by the TableAttribute applied to the type TEntity. If this attribute is not present, the singular name of the type TEntity is used.

The type TEntity must have a property (with a public getter) denoted with a KeyAttribute.

Examples

Delete a sequence of entities:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

Exceptions

Type	Condition
ArgumentNullException	`connection` is null. `entities` is null.
ArgumentException	No property (with a public getter) of the type `TEntity` is denoted with a KeyAttribute.
OperationCanceledException	The operation was cancelled via `cancellationToken`.

| Edit this page View Source

DeleteEntities<TEntity>(SqlConnection, IEnumerable<TEntity>, SqlTransaction?, CancellationToken)

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

Declaration

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

Parameters

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

Returns

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

Type Parameters

Name	Description
TEntity	The type of entities to delete.

Remarks

The table from which the entities will be deleted is determined by the TableAttribute applied to the type TEntity. If this attribute is not present, the singular name of the type TEntity is used.

The type TEntity must have a property (with a public getter) denoted with a KeyAttribute.

Examples

Delete a sequence of entities:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

Exceptions

Type	Condition
ArgumentNullException	`connection` is null. `entities` is null.
ArgumentException	No property (with a public getter) of the type `TEntity` is denoted with a KeyAttribute.
OperationCanceledException	The operation was cancelled via `cancellationToken`.

| Edit this page View Source

DeleteEntityAsync<TEntity>(SqlConnection, TEntity, SqlTransaction?, CancellationToken)

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

Declaration

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

Parameters

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

Returns

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

Type Parameters

Name	Description
TEntity	The type of entity to delete.

Remarks

The table from which the entity will be deleted is determined by the TableAttribute applied to the type TEntity. If this attribute is not present, the singular name of the type TEntity is used.

The type TEntity must have a property (with a public getter) denoted with a KeyAttribute.

Examples

Delete an entity:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

Exceptions

Type	Condition
ArgumentNullException	`connection` is null. `entity` is null.
ArgumentException	No property (with a public getter) of the type `TEntity` is denoted with a KeyAttribute.
OperationCanceledException	The operation was cancelled via `cancellationToken`.

| Edit this page View Source

DeleteEntity<TEntity>(SqlConnection, TEntity, SqlTransaction?, CancellationToken)

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

Declaration

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

Parameters

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

Returns

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

Type Parameters

Name	Description
TEntity	The type of entity to delete.

Remarks

The table from which the entity will be deleted is determined by the TableAttribute applied to the type TEntity. If this attribute is not present, the singular name of the type TEntity is used.

The type TEntity must have a property (with a public getter) denoted with a KeyAttribute.

Examples

Delete an entity:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

Exceptions

Type	Condition
ArgumentNullException	`connection` is null. `entity` is null.
ArgumentException	No property (with a public getter) of the type `TEntity` is denoted with a KeyAttribute.
OperationCanceledException	The operation was cancelled via `cancellationToken`.

| Edit this page View Source

ExecuteNonQuery(SqlConnection, InterpolatedSqlStatement, SqlTransaction?, TimeSpan?, CommandType, CancellationToken)

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

Declaration

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

Parameters

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

Returns

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

Remarks

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

Examples

Execute an SQL statement:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var numberOfDeletedProducts = connection.ExecuteNonQuery(
    """
    DELETE FROM Product
    WHERE       IsDiscontinued = 1
    """
);

Pass a parameter via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

Pass a sequence of scalar values as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

var numberOfDeletedProducts = connection.ExecuteNonQuery(
   $"""
    DELETE FROM Product
    WHERE       SupplierId IN (
                    SELECT  Value
                    FROM    {TemporaryTable(retiredSupplierIds)}
                )
    """
);

Pass a sequence of complex objects as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

// Delete products that have not been ordered in the past six months.
var numberOfDeletedProducts = connection.ExecuteNonQuery(
   $"""
    DELETE FROM Product
    WHERE       NOT EXISTS (
                    SELECT  1
                    FROM    {TemporaryTable(orderItems)} TOrderItem
                    WHERE   TOrderItem.ProductId = Product.Id AND
                            TOrderItem.OrderDate >= {Parameter(sixMonthsAgo)}
                )
    """
);

Exceptions

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

| Edit this page View Source

ExecuteNonQueryAsync(SqlConnection, InterpolatedSqlStatement, SqlTransaction?, TimeSpan?, CommandType, CancellationToken)

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

Declaration

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

Parameters

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

Returns

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

Remarks

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

Examples

Execute an SQL statement:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var numberOfDeletedProducts = await connection.ExecuteNonQueryAsync(
    """
    DELETE FROM Product
    WHERE       IsDiscontinued = 1
    """
);

Pass a parameter via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

Pass a sequence of scalar values as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

var numberOfDeletedProducts = await connection.ExecuteNonQueryAsync(
   $"""
    DELETE FROM Product
    WHERE       SupplierId IN (
                    SELECT  Value
                    FROM    {TemporaryTable(retiredSupplierIds)}
                )
    """
);

Pass a sequence of complex objects as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

// Delete products that have not been ordered in the past six months.
var numberOfDeletedProducts = await connection.ExecuteNonQueryAsync(
   $"""
    DELETE FROM Product
    WHERE       NOT EXISTS (
                    SELECT  1
                    FROM    {TemporaryTable(orderItems)} TOrderItem
                    WHERE   TOrderItem.ProductId = Product.Id AND
                            TOrderItem.OrderDate >= {Parameter(sixMonthsAgo)}
                )
    """
);

Exceptions

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

| Edit this page View Source

ExecuteReader(SqlConnection, InterpolatedSqlStatement, SqlTransaction?, TimeSpan?, CommandBehavior, CommandType, CancellationToken)

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

Declaration

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

Parameters

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

Returns

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

Remarks

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

Examples

Execute an SQL statement and get a DbDataReader to read the result set:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var productsReader = connection.ExecuteReader(
    """
    SELECT  *
    FROM    Product
    """
);

Pass a parameter via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

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

Pass a sequence of scalar values as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

Pass a sequence of complex objects as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

Exceptions

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

| Edit this page View Source

ExecuteReaderAsync(SqlConnection, InterpolatedSqlStatement, SqlTransaction?, TimeSpan?, CommandBehavior, CommandType, CancellationToken)

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

Declaration

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

Parameters

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

Returns

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

Remarks

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

Examples

Execute an SQL statement and get a DbDataReader to read the result set:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var productsReader = await connection.ExecuteReaderAsync(
    """
    SELECT  *
    FROM    Product
    """
);

Pass a parameter via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

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

Pass a sequence of scalar values as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

Pass a sequence of complex objects as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

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

Exceptions

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

| Edit this page View Source

ExecuteScalarAsync<TTarget>(SqlConnection, InterpolatedSqlStatement, SqlTransaction?, TimeSpan?, CommandType, CancellationToken)

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

Declaration

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

Parameters

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

Returns

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

Type Parameters

Name	Description
TTarget

Remarks

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

Examples

Execute an SQL statement and get the first column of the first row in the result set.

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var numberOfDiscontinuedProducts = await connection.ExecuteScalarAsync<Int32>(
    """
    SELECT  COUNT(*)
    FROM    Product
    WHERE   IsDiccontinued = 1
    """
);

Pass a parameter via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

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

Pass a sequence of scalar values as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

var numberOfProductsOfRetiredSuppliers = await connection.ExecuteScalarAsync<Int32>(
  $"""
  SELECT    COUNT(*)
  FROM      Product
  WHERE     SupplierId IN (
                SELECT  Value
                FROM    {TemporaryTable(retiredSupplierIds)}
            )
  """
);

Pass a sequence of complex objects as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

var numberOfProductsOrderedInPastSixMonths = await connection.ExecuteScalarAsync<Int32>(
    $"""
     SELECT     COUNT(*)
     FROM       Product
     WHERE      EXISTS (
                    SELECT  1
                    FROM    {TemporaryTable(orderItems)} TOrderItem
                    WHERE   TOrderItem.ProductId = Product.Id AND
                            TOrderItem.OrderDate >= {Parameter(sixMonthsAgo)}
                )
     """
);

Exceptions

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

| Edit this page View Source

ExecuteScalar<TTarget>(SqlConnection, InterpolatedSqlStatement, SqlTransaction?, TimeSpan?, CommandType, CancellationToken)

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

Declaration

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

Parameters

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

Returns

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

Type Parameters

Name	Description
TTarget

Remarks

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

Examples

Execute an SQL statement and get the first column of the first row in the result set.

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var numberOfDiscontinuedProducts = connection.ExecuteScalar<Int32>(
    """
    SELECT  COUNT(*)
    FROM    Product
    WHERE   IsDiccontinued = 1
    """
);

Pass a parameter via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

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

Pass a sequence of scalar values as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

var numberOfProductsOfRetiredSuppliers = connection.ExecuteScalar<Int32>(
  $"""
  SELECT    COUNT(*)
  FROM      Product
  WHERE     SupplierId IN (
                SELECT  Value
                FROM    {TemporaryTable(retiredSupplierIds)}
            )
  """
);

Pass a sequence of complex objects as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

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

Exceptions

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

| Edit this page View Source

ExecuteXmlReader(SqlConnection, InterpolatedSqlStatement, SqlTransaction?, CommandType, TimeSpan?, CancellationToken)

Executes the specified SQL statement and returns an XmlReader to read the statement result set as XML.

Declaration

public static XmlReader ExecuteXmlReader(this SqlConnection connection, InterpolatedSqlStatement statement, SqlTransaction? transaction = null, CommandType commandType = CommandType.Text, TimeSpan? commandTimeout = null, CancellationToken cancellationToken = default)

Parameters

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

Returns

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

Remarks

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

Examples

Execute an SQL statement and get an XmlReader to read the result set as XML:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var productsXmlReader = connection.ExecuteXmlReader(
    """
    SELECT  *
    FROM    Product
    FOR     XML AUTO
    """
);

Pass a parameter via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

var lowStockProductsXmlReader = connection.ExecuteXmlReader(
   $"""
    SELECT  *
    FROM    Product
    WHERE   UnitsInStock < {Parameter(lowStockThreshold)}
    FOR     XML AUTO
    """
);

Pass a sequence of scalar values as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

var retiredSupplierProductsXmlReader = connection.ExecuteXmlReader(
   $"""
    SELECT  *
    FROM    Product
    WHERE   SupplierId IN (
                SELECT  Value
                FROM    {TemporaryTable(retiredSupplierIds)}
            )
    FOR     XML AUTO
    """
);

Pass a sequence of complex objects as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

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

Exceptions

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

| Edit this page View Source

ExecuteXmlReaderAsync(SqlConnection, InterpolatedSqlStatement, SqlTransaction?, CommandType, TimeSpan?, CancellationToken)

Asynchronously executes the specified SQL statement and returns an XmlReader to read the statement result set as XML.

Declaration

public static Task<XmlReader> ExecuteXmlReaderAsync(this SqlConnection connection, InterpolatedSqlStatement statement, SqlTransaction? transaction = null, CommandType commandType = CommandType.Text, TimeSpan? commandTimeout = null, CancellationToken cancellationToken = default)

Parameters

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

Returns

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

Remarks

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

Examples

Execute an SQL statement and get an XML reader to read the result set as XML:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var productsXmlReader = await connection.ExecuteXmlReaderAsync(
    """
    SELECT  *
    FROM    Product
    FOR     XML AUTO
    """
);

Pass a parameter via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

var lowStockProductsXmlReader = await connection.ExecuteXmlReaderAsync(
   $"""
    SELECT  *
    FROM    Product
    WHERE   UnitsInStock < {Parameter(lowStockThreshold)}
    FOR     XML AUTO
    """
);

Pass a sequence of scalar values as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

var retiredSupplierProductsXmlReader = await connection.ExecuteXmlReaderAsync(
   $"""
    SELECT  *
    FROM    Product
    WHERE   SupplierId IN (
                SELECT  Value
                FROM    {TemporaryTable(retiredSupplierIds)}
            )
    FOR     XML AUTO
    """
);

Pass a sequence of complex objects as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

var productsOrderedInPastSixMonthsXmlReader = await connection.ExecuteXmlReaderAsync(
    $"""
     SELECT     *
     FROM       Product
     WHERE      EXISTS (
                    SELECT  1
                    FROM    {TemporaryTable(orderItems)} TOrderItem
                    WHERE   TOrderItem.ProductId = Product.Id AND
                            TOrderItem.OrderDate >= {Parameter(sixMonthsAgo)}
                )
     FOR        XML AUTO
     """
);

Exceptions

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

| Edit this page View Source

Exists(SqlConnection, InterpolatedSqlStatement, SqlTransaction?, TimeSpan?, CommandType, CancellationToken)

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

Declaration

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

Parameters

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

Returns

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

Remarks

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

Examples

Execute an SQL statement and check if it returned at least one row.

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var existDiscontinuedProducts = connection.Exists(
    """
    SELECT  1
    FROM    Product
    WHERE   IsDiscontinued = 1
    """
);

Pass a parameter via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

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

Pass a sequence of scalar values as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

var existProductsOfRetiredSuppliers = connection.Exists(
   $"""
    SELECT  1
    FROM    Product
    WHERE   SupplierId IN (
                SELECT  Value
                FROM    {TemporaryTable(retiredSupplierIds)}
            )
    """
);

Pass a sequence of complex objects as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

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

Exceptions

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

| Edit this page View Source

ExistsAsync(SqlConnection, InterpolatedSqlStatement, SqlTransaction?, TimeSpan?, CommandType, CancellationToken)

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

Declaration

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

Parameters

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

Returns

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

Remarks

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

Examples

Execute an SQL statement and check if it returned at least one row.

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var existDiscontinuedProducts = await connection.ExistsAsync(
    """
    SELECT  1
    FROM    Product
    WHERE   IsDiscontinued = 1
    """
);

Pass a parameter via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

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

Pass a sequence of scalar values as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

var existProductsOfRetiredSuppliers = await connection.ExistsAsync(
   $"""
    SELECT  1
    FROM    Product
    WHERE   SupplierId IN (
                SELECT  Value
                FROM    {TemporaryTable(retiredSupplierIds)}
            )
    """
);

Pass a sequence of complex objects as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

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

Exceptions

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

| Edit this page View Source

InsertEntitiesAsync<TEntity>(SqlConnection, IEnumerable<TEntity>, SqlTransaction?, CancellationToken)

Asynchronously inserts the specified entities into the database.

Declaration

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

Parameters

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

Returns

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

Type Parameters

Name	Description
TEntity	The type of entities to insert.

Remarks

The table into which the entities will be inserted is determined by the TableAttribute applied to the type TEntity. If this attribute is not present, the singular name of the type TEntity is used.

Each property (with a public getter) of the type TEntity is mapped to a column with the same name (case-sensitive) in the table. The columns must have data types that are compatible with the property types of the corresponding properties. Properties denoted with the NotMappedAttribute are ignored.

Examples

Insert a sequence of entities:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

var newProducts = await GetNewProductsAsync();

await connection.InsertEntitiesAsync(newProducts);

Exceptions

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

| Edit this page View Source

InsertEntities<TEntity>(SqlConnection, IEnumerable<TEntity>, SqlTransaction?, CancellationToken)

Inserts the specified entities into the database.

Declaration

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

Parameters

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

Returns

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

Type Parameters

Name	Description
TEntity	The type of entities to insert.

Remarks

The type TEntity must have a property (with a public getter) denoted with a KeyAttribute.

Examples

Insert a sequence of entities:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

var newProducts = GetNewProducts();

connection.InsertEntities(newProducts);

Exceptions

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

| Edit this page View Source

InsertEntityAsync<TEntity>(SqlConnection, TEntity, SqlTransaction?, CancellationToken)

Asynchronously inserts the specified entity into the database.

Declaration

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

Parameters

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

Returns

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

Type Parameters

Name	Description
TEntity	The type of entity to insert.

Remarks

The table into which the entity will be inserted is determined by the TableAttribute applied to the type TEntity. If this attribute is not present, the singular name of the type TEntity is used.

Examples

Insert an entity:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

var newProduct = await GetNewProductAsync();

await connection.InsertEntityAsync(newProduct);

Exceptions

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

| Edit this page View Source

InsertEntity<TEntity>(SqlConnection, TEntity, SqlTransaction?, CancellationToken)

Inserts the specified entity into the database.

Declaration

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

Parameters

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

Returns

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

Type Parameters

Name	Description
TEntity	The type of entity to insert.

Remarks

The table into which the entity will be inserted is determined by the TableAttribute applied to the type TEntity. If this attribute is not present, the singular name of the type TEntity is used.

The type TEntity must have a property (with a public getter) denoted with a KeyAttribute.

Examples

Insert an entity:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

var newProduct = GetNewProduct();

connection.InsertEntity(newProduct);

Exceptions

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

| Edit this page View Source

Parameter(object?, string?)

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

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

Declaration

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

Parameters

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

Returns

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

Remarks

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

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

Example:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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.

| Edit this page View Source

QueryEntitiesAsync<TEntity>(SqlConnection, InterpolatedSqlStatement, SqlTransaction?, 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 TEntity.

Declaration

public static IAsyncEnumerable<TEntity> QueryEntitiesAsync<TEntity>(this SqlConnection connection, InterpolatedSqlStatement statement, SqlTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default) where TEntity : new()

Parameters

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

Returns

Type	Description
IAsyncEnumerable<TEntity>	An async enumerable that represents the sequence of instances of the type `TEntity` containing the data of the result set returned by the statement.

Type Parameters

Name	Description
TEntity	The type of objects to materialize the result set to.

Remarks

The type TEntity must have properties (with public setters) that match the names (case-sensitive) and data types of the columns returned by the statement.

Each row in the result set will be materialized into an instance of TEntity, with the properties being populated from the corresponding columns in the row. The data types of the columns must be compatible with the property types of the properties.

If the statement returns a column that does not have a corresponding property in the type TEntity, an ArgumentException will be thrown.

If a column value returned by the statement could not be converted to the property type of the corresponding property of the type TEntity, an InvalidCastException will be thrown.

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

Examples

Execute an SQL statement and get the result set as a sequence of entities:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

public enum OrderStatus : Int32
{
    Pending = 1,
    Processing = 2,
    Shipped = 3,
    Delivered = 4,
    Cancelled = 5
}

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

var products = await connection.QueryEntitiesAsync<Order>(
    """
    SELECT  *
    FROM    [Order]
    """
);

Pass a parameter via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

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

Pass a sequence of scalar values as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

var retiredSupplierProducts = await connection.QueryEntitiesAsync<Product>(
   $"""
    SELECT  *
    FROM    Product
    WHERE   SupplierId IN (
                SELECT  Value
                FROM    {TemporaryTable(retiredSupplierIds)}
            )
    """
);

Pass a sequence of complex objects as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

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

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
ArgumentException	The statement returned a column for which no matching property (with a public setter) exists in the type `TEntity`.
InvalidCastException	A column value returned by the statement could not be converted to the property type of the corresponding property of the type `TEntity`.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

| Edit this page View Source

QueryEntities<TEntity>(SqlConnection, InterpolatedSqlStatement, SqlTransaction?, 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 TEntity.

Declaration

public static IEnumerable<TEntity> QueryEntities<TEntity>(this SqlConnection connection, InterpolatedSqlStatement statement, SqlTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default) where TEntity : new()

Parameters

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

Returns

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

Type Parameters

Name	Description
TEntity	The type of objects to materialize the result set to.

Remarks

The type TEntity must have properties (with public setters) that match the names (case-sensitive) and data types of the columns returned by the statement.

If the statement returns a column that does not have a corresponding property in the type TEntity, an ArgumentException will be thrown.

If a column value returned by the statement could not be converted to the property type of the corresponding property of the type TEntity, an InvalidCastException will be thrown.

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

Examples

Execute an SQL statement and get the result set as a sequence of entities:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

public enum OrderStatus : Int32
{
    Pending = 1,
    Processing = 2,
    Shipped = 3,
    Delivered = 4,
    Cancelled = 5
}

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

var orders = connection.QueryEntities<Order>(
    """
    SELECT  *
    FROM    [Order]
    """
);

Pass a parameter via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

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

Pass a sequence of scalar values as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

var retiredSupplierProducts = connection.QueryEntities<Product>(
   $"""
    SELECT  *
    FROM    Product
    WHERE   SupplierId IN (
                SELECT  Value
                FROM    {TemporaryTable(retiredSupplierIds)}
            )
    """
);

Pass a sequence of complex objects as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

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

Exceptions

Type	Condition
ArgumentNullException	`connection` is null.
ArgumentException	The statement returned a column for which no matching property (with a public setter) exists in the type `TEntity`.
InvalidCastException	A column value returned by the statement could not be converted to the property type of the corresponding property of the type `TEntity`.
OperationCanceledException	The statement was cancelled via `cancellationToken`.

| Edit this page View Source

QueryScalarsAsync<TTarget>(SqlConnection, InterpolatedSqlStatement, SqlTransaction?, TimeSpan?, CommandType, CancellationToken)

Asynchronously executes the specified SQL statement and returns the values of the first column of the result set returned by the statement converted to a sequence of values of the type TTarget.

Declaration

public static IAsyncEnumerable<TTarget> QueryScalarsAsync<TTarget>(this SqlConnection connection, InterpolatedSqlStatement statement, SqlTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

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

Returns

Type	Description
IAsyncEnumerable<TTarget>	An async enumerable that represents the values of the first column of the result set returned by the statement converted to a sequence of values of the type `TTarget`.

Type Parameters

Name	Description
TTarget	The type to convert the values of the first column of the result set of the statement to.

Remarks

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

Examples

Execute an SQL statement and get the values of the first column of the result set.

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var discontinuedProductIds = await connection.QueryScalarsAsync<Int64>(
    """
    SELECT  Id
    FROM    Product
    WHERE   IsDiscontinued = 1
    """
);

Pass a parameter via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

var lowStockProductIds = await connection.QueryScalarsAsync<Int64>(
   $"""
    SELECT  Id
    FROM    Product
    WHERE   UnitsInStock < {Parameter(lowStockThreshold)}
    """
);

Pass a sequence of scalar values as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

var idsOfProductsOfRetiredSuppliers = await connection.QueryScalarsAsync<Int64>(
  $"""
  SELECT    Id
  FROM      Product
  WHERE     SupplierId IN (
                SELECT  Value
                FROM    {TemporaryTable(retiredSupplierIds)}
            )
  """
);

Pass a sequence of complex objects as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

var idsOfProductsOrderedInPastSixMonths = await connection.QueryScalarsAsync<Int64>(
    $"""
     SELECT     Id
     FROM       Product
     WHERE      EXISTS (
                    SELECT  1
                    FROM    {TemporaryTable(orderItems)} TOrderItem
                    WHERE   TOrderItem.ProductId = Product.Id AND
                            TOrderItem.OrderDate >= {Parameter(sixMonthsAgo)}
                )
     """
);

Exceptions

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

| Edit this page View Source

QueryScalars<TTarget>(SqlConnection, InterpolatedSqlStatement, SqlTransaction?, TimeSpan?, CommandType, CancellationToken)

Executes the specified SQL statement and returns the values of the first column of the result set returned by the statement converted to a sequence of values of the type TTarget.

Declaration

public static IEnumerable<TTarget> QueryScalars<TTarget>(this SqlConnection connection, InterpolatedSqlStatement statement, SqlTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default)

Parameters

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

Returns

Type	Description
IEnumerable<TTarget>	The values of the first column of the result set returned by the statement converted to a sequence of values of the type `TTarget`.

Type Parameters

Name	Description
TTarget	The type to convert the values of the first column of the result set of the statement to.

Remarks

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

Examples

Execute an SQL statement and get the values of the first column of the result set.

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var discontinuedProductIds = connection.QueryScalars<Int64>(
    """
    SELECT  Id
    FROM    Product
    WHERE   IsDiscontinued = 1
    """
);

Pass a parameter via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

var lowStockProductIds = connection.QueryScalars<Int64>(
   $"""
    SELECT  Id
    FROM    Product
    WHERE   UnitsInStock < {Parameter(lowStockThreshold)}
    """
);

Pass a sequence of scalar values as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

var idsOfProductsOfRetiredSuppliers = connection.QueryScalars<Int64>(
  $"""
  SELECT    Id
  FROM      Product
  WHERE     SupplierId IN (
                SELECT  Value
                FROM    {TemporaryTable(retiredSupplierIds)}
            )
  """
);

Pass a sequence of complex objects as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

var idsOfProductsOrderedInPastSixMonths = connection.QueryScalars<Int64>(
    $"""
     SELECT     Id
     FROM       Product
     WHERE      EXISTS (
                    SELECT  1
                    FROM    {TemporaryTable(orderItems)} TOrderItem
                    WHERE   TOrderItem.ProductId = Product.Id AND
                            TOrderItem.OrderDate >= {Parameter(sixMonthsAgo)}
                )
     """
);

Exceptions

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

| Edit this page View Source

QueryTuplesAsync<TValueTuple>(SqlConnection, InterpolatedSqlStatement, SqlTransaction?, TimeSpan?, CommandType, CancellationToken)

Asynchronously executes the specified SQL statement and materializes the result set returned by the statement into a sequence of value tuples of the type TValueTuple.

Declaration

public static IAsyncEnumerable<TValueTuple> QueryTuplesAsync<TValueTuple>(this SqlConnection connection, InterpolatedSqlStatement statement, SqlTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default) where TValueTuple : struct, IStructuralEquatable, IStructuralComparable, IComparable

Parameters

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

Returns

Type	Description
IAsyncEnumerable<TValueTuple>	An async enumerable that represents the sequence of value tuples of the type `TValueTuple` containing the data of the result set returned by the statement.

Type Parameters

Name	Description
TValueTuple	The type of value tuples to materialize the result set to. Only value tuples with up to 7 fields are supported.

Remarks

Each row in the result set will be materialized into an instance of TValueTuple, with the fields of the value tuple being populated from the corresponding columns in the row.

The order of the fields in the value tuple must match the order of the columns in the result set. The data types of the columns must be compatible with the field types of the fields of the value tuple.

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

Examples

Execute an SQL statement and get the returned rows as value tuples.

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var productUnitsInStockInfos = await connection.QueryTuplesAsync<(Int64 ProductId, Int32 UnitsInStock)>(
    """
    SELECT  Id, UnitsInStock
    FROM    Product
    """
);

Pass a parameter via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

var lowStockProductInfos = await connection.QueryTuplesAsync<(Int64 ProductId, Int32 UnitsInStock)>(
   $"""
    SELECT  Id, UnitsInStock
    FROM    Product
    WHERE   UnitsInStock < {Parameter(lowStockThreshold)}
    """
);

Pass a sequence of scalar values as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

var retiredSupplierProductInfos = await connection.QueryTuplesAsync<(Int64 ProductId, Int32 UnitsInStock)>(
   $"""
    SELECT  Id, UnitsInStock
    FROM    Product
    WHERE   SupplierId IN (
                SELECT  Value
                FROM    {TemporaryTable(retiredSupplierIds)}
            )
    """
);

Pass a sequence of complex objects as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

var productsOrderedInPastSixMonthsInfos =
    await connection.QueryTuplesAsync<(Int64 ProductId, Int32 UnitsInStock)>(
        $"""
         SELECT     Id, UnitsInStock
         FROM       Product
         WHERE      EXISTS (
                        SELECT  1
                        FROM    {TemporaryTable(orderItems)} TOrderItem
                        WHERE   TOrderItem.ProductId = Product.Id AND
                                TOrderItem.OrderDate >= {Parameter(sixMonthsAgo)}
                    )
             """
    );

Exceptions

Type Condition

ArgumentNullException

connection is null.

ArgumentException

<ul><li>
            <code class="typeparamref">TValueTuple</code> is not a <xref href="System.ValueTuple" data-throw-if-not-resolved="false"></xref> type or a

ValueTuple type with more than 7 fields.

The type TValueTuple has a different number of fields than the number of columns returned by the statement.

A column returned by the statement contains NULL and the corresponding field of the type TValueTuple has a non-nullable field type.

A column returned by the statement has a data type which is not compatible with the field type of the corresponding field of the type TValueTuple.

OperationCanceledException

The statement was cancelled via cancellationToken.

| Edit this page View Source

QueryTuples<TValueTuple>(SqlConnection, InterpolatedSqlStatement, SqlTransaction?, TimeSpan?, CommandType, CancellationToken)

Executes the specified SQL statement and materializes the result set returned by the statement into a sequence of value tuples of the type TValueTuple.

Declaration

public static IEnumerable<TValueTuple> QueryTuples<TValueTuple>(this SqlConnection connection, InterpolatedSqlStatement statement, SqlTransaction? transaction = null, TimeSpan? commandTimeout = null, CommandType commandType = CommandType.Text, CancellationToken cancellationToken = default) where TValueTuple : struct, IStructuralEquatable, IStructuralComparable, IComparable

Parameters

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

Returns

Type	Description
IEnumerable<TValueTuple>	A sequence of value tuples of the type `TValueTuple` containing the data of the result set returned by the statement.

Type Parameters

Name	Description
TValueTuple	The type of value tuples to materialize the result set to. Only value tuples with up to 7 fields are supported.

Remarks

Each row in the result set will be materialized into an instance of TValueTuple, with the fields of the value tuple being populated from the corresponding columns in the row.

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

Examples

Execute an SQL statement and get the returned rows as value tuples.

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var productUnitsInStockInfos = connection.QueryTuples<(Int64 ProductId, Int32 UnitsInStock)>(
    """
    SELECT  Id, UnitsInStock
    FROM    Product
    """
);

Pass a parameter via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

var lowStockThreshold = configuration.Thresholds.LowStock;

var lowStockProductInfos = connection.QueryTuples<(Int64 ProductId, Int32 UnitsInStock)>(
   $"""
    SELECT  Id, UnitsInStock
    FROM    Product
    WHERE   UnitsInStock < {Parameter(lowStockThreshold)}
    """
);

Pass a sequence of scalar values as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

var retiredSupplierProductInfos = connection.QueryTuples<(Int64 ProductId, Int32 UnitsInStock)>(
   $"""
    SELECT  Id, UnitsInStock
    FROM    Product
    WHERE   SupplierId IN (
                SELECT  Value
                FROM    {TemporaryTable(retiredSupplierIds)}
            )
    """
);

Pass a sequence of complex objects as a temporary table via an interpolated string:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

var productsOrderedInPastSixMonthsInfos = connection.QueryTuples<(Int64 ProductId, Int32 UnitsInStock)>(
    $"""
     SELECT     Id, UnitsInStock
     FROM       Product
     WHERE      EXISTS (
                    SELECT  1
                    FROM    {TemporaryTable(orderItems)} TOrderItem
                    WHERE   TOrderItem.ProductId = Product.Id AND
                            TOrderItem.OrderDate >= {Parameter(sixMonthsAgo)}
                )
     """
);

Exceptions

Type Condition

ArgumentNullException

connection is null.

ArgumentException

<ul><li>
            <code class="typeparamref">TValueTuple</code> is not a <xref href="System.ValueTuple" data-throw-if-not-resolved="false"></xref> type or a

ValueTuple type with more than 7 fields.

The type TValueTuple has a different number of fields than the number of columns returned by the statement.

A column returned by the statement contains NULL and the corresponding field of the type TValueTuple has a non-nullable field type.

A column returned by the statement has a data type which is not compatible with the field type of the corresponding field of the type TValueTuple.

OperationCanceledException

The statement was cancelled via cancellationToken.

| Edit this page View Source

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

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

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

Declaration

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

Parameters

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

Returns

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

Type Parameters

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

Remarks

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

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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 matches the type of the passed values.

Example:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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" and 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 property (with a public getter) of the passed objects. The name of each column will be the name of the corresponding property. The data type of each column will be compatible with the property type of the corresponding property.

Example:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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.

When EnumSerializationMode is set to Strings, the data type of the corresponding column in the temporary table will be

NVARCHAR(200)

When EnumSerializationMode is set to Integers, the data type of the corresponding column in the temporary table will be

INT

| Edit this page View Source

UpdateEntitiesAsync<TEntity>(SqlConnection, IEnumerable<TEntity>, SqlTransaction?, CancellationToken)

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

Declaration

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

Parameters

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

Returns

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

Type Parameters

Name	Description
TEntity	The type of entities to update.

Remarks

The table where the entities will be updated is determined by the TableAttribute applied to the type TEntity. If this attribute is not present, the singular name of the type TEntity is used.

The type TEntity must have a property (with a public getter) denoted with a KeyAttribute.

Examples

Update a sequence of entities:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

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

await connection.UpdateEntitiesAsync(usersWithoutLoginInPastYear);

Exceptions

Type	Condition
ArgumentNullException	`connection` is null. `entities` is null.
ArgumentException	No property (with a public getter) of the type `TEntity` is denoted with a KeyAttribute.
OperationCanceledException	The operation was cancelled via `cancellationToken`.

| Edit this page View Source

UpdateEntities<TEntity>(SqlConnection, IEnumerable<TEntity>, SqlTransaction?, CancellationToken)

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

Declaration

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

Parameters

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

Returns

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

Type Parameters

Name	Description
TEntity	The type of entities to update.

Remarks

The table where the entities will be updated is determined by the TableAttribute applied to the type TEntity. If this attribute is not present, the singular name of the type TEntity is used.

The type TEntity must have a property (with a public getter) denoted with a KeyAttribute.

Examples

Update a sequence of entities:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

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

connection.UpdateEntities(usersWithoutLoginInPastYear);

Exceptions

Type	Condition
ArgumentNullException	`connection` is null. `entities` is null.
ArgumentException	No property (with a public getter) of the type `TEntity` is denoted with a KeyAttribute.
OperationCanceledException	The operation was cancelled via `cancellationToken`.

| Edit this page View Source

UpdateEntityAsync<TEntity>(SqlConnection, TEntity, SqlTransaction?, CancellationToken)

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

Declaration

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

Parameters

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

Returns

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

Type Parameters

Name	Description
TEntity	The type of entity to update.

Remarks

The table where the entity will be updated is determined by the TableAttribute applied to the type TEntity. If this attribute is not present, the singular name of the type TEntity is used.

The type TEntity must have a property (with a public getter) denoted with a KeyAttribute.

Examples

Update an entity:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

Exceptions

Type	Condition
ArgumentNullException	`connection` is null. `entity` is null.
ArgumentException	No property (with a public getter) of the type `TEntity` is denoted with a KeyAttribute.
OperationCanceledException	The operation was cancelled via `cancellationToken`.

| Edit this page View Source

UpdateEntity<TEntity>(SqlConnection, TEntity, SqlTransaction?, CancellationToken)

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

Declaration

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

Parameters

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

Returns

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

Type Parameters

Name	Description
TEntity	The type of entity to update.

Remarks

The table where the entity will be updated is determined by the TableAttribute applied to the type TEntity. If this attribute is not present, the singular name of the type TEntity is used.

The type TEntity must have a property (with a public getter) denoted with a KeyAttribute.

Examples

Update an entity:

using static RentADeveloper.SqlConnectionPlus.SqlConnectionExtensions;

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

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

Exceptions

Type	Condition
ArgumentNullException	`connection` is null. `entity` is null.
ArgumentException	No property (with a public getter) of the type `TEntity` is denoted with a KeyAttribute.
OperationCanceledException	The operation was cancelled via `cancellationToken`.