Class QueryExectionExtensions

Inheritance
object
QueryExectionExtensions
Inherited Members
Namespace: SuperOffice.Data
Assembly: SoDataBase.dll
Syntax
public static class QueryExectionExtensions

Methods

ExecuteBatchedReaderAsync(SqlCommand, bool, int, CancellationToken)

Execute a SELECT statement in batches against the database and return the results as an IAsyncEnumerable, suitable for linq expressions and iteration. This is a specialized method intended for very long-running queries (> 60 minutes). Use with care, when needed!

Declaration
public static IAsyncEnumerable<ISoDataRecord> ExecuteBatchedReaderAsync(this SqlCommand command, bool ignoreSentry = false, int batchSize = 20000, CancellationToken cancellationToken = default)
Parameters
Type Name Description
SqlCommand command

The SELECT query to execute in batches. Must be a PrivateSelect with no RightOuterJoin restrictions and no Aggregation return fields.
bool ignoreSentry

Whether to ignore sentry when executing the batch queries.
int batchSize

Maximum number of rows per batch (default 20,000). Actual batch size may exceed this value when ordering by a non-PK field, as the boundary aligns with value changes.
CancellationToken cancellationToken

Cancellation token, passed down to the database server.
Returns
Type Description
IAsyncEnumerable<ISoDataRecord>
Remarks

A fundamental requirement is that the query is quick to (re)start. In other words, restrictions and orderby must be on well-indexed fields. For every batch, the reader will be closed and a new SELECT executed, so if it takes a lot of work for the database to find the first row of the result set, performance will suffer badly. The code does not validate this requirement.
The command must satisfy all of the following conditions (validated on entry):

  • Must be a simple Select (PrivateSelect), not a Union or other compound query.
  • Must have at least one top-level ReturnField whose table is defined.
  • Must not contain any RightOuterJoin restrictions.
  • Must not contain any Aggregation return fields (Count, Sum, etc.).
  • OrderBy, if specified, must be a single FieldInfo that is either the primary key or an int/intId field. If no OrderBy is specified, the primary key ascending is used by default.

The method clones the original select for each batch and appends a key-boundary restriction (>= or <=, depending on sort direction) to iterate through the data.
  • When ordering by primary key, each batch contains at most batchSize rows.
  • When ordering by a non-PK int field, each batch contains at least batchSize rows; the batch boundary is extended until the ordering field changes value, so actual batch size may be larger.

ExecuteNonQuery(SqlCommand)

Declaration
public static int ExecuteNonQuery(this SqlCommand command)
Parameters
Type Name Description
SqlCommand command
Returns
Type Description
int

ExecuteNonQueryAsync(SqlCommand, CancellationToken)

Declaration
[CreateSyncVersion(OmitNullableDirective = true)]
public static Task<int> ExecuteNonQueryAsync(this SqlCommand command, CancellationToken cancellationToken = default)
Parameters
Type Name Description
SqlCommand command
CancellationToken cancellationToken
Returns
Type Description
Task<int>

ExecuteReader(SqlCommand, bool)

Execute a SELECT statement against the database and return the results as an IAsyncEnumerable, suitable for linq expressions and iteration. But please DON'T use the anti-pattern 'fetch everything then post-filter using Linq'; put all the restrictions you can into the select before Fetch'ing the results.

Declaration
public static IEnumerable<ISoDataRecord> ExecuteReader(this SqlCommand command, bool ignoreSentry = false)
Parameters
Type Name Description
SqlCommand command

An SqlCommand that is a select
bool ignoreSentry
Returns
Type Description
IEnumerable<ISoDataRecord>

Enumerable result, which contains FieldInfo-based Get methods for all common data types

ExecuteReaderAsync(SqlCommand, bool, CancellationToken)

Execute a SELECT statement against the database and return the results as an IAsyncEnumerable, suitable for linq expressions and iteration. But please DON'T use the anti-pattern 'fetch everything then post-filter using Linq'; put all the restrictions you can into the select before Fetch'ing the results.

Declaration
[CreateSyncVersion(OmitNullableDirective = true)]
public static IAsyncEnumerable<ISoDataRecord> ExecuteReaderAsync(this SqlCommand command, bool ignoreSentry = false, CancellationToken cancellationToken = default)
Parameters
Type Name Description
SqlCommand command

An SqlCommand that is a select
bool ignoreSentry
CancellationToken cancellationToken
Returns
Type Description
IAsyncEnumerable<ISoDataRecord>

Enumerable result, which contains FieldInfo-based Get methods for all common data types

ExecuteScalarAsync<T>(SqlCommand, bool, CancellationToken)

Declaration
public static Task<T> ExecuteScalarAsync<T>(this SqlCommand command, bool ignoreSentry, CancellationToken cancellationToken = default)
Parameters
Type Name Description
SqlCommand command
bool ignoreSentry
CancellationToken cancellationToken
Returns
Type Description
Task<T>
Type Parameters
Name Description
T

ExecuteScalarAsync<T>(SqlCommand, CancellationToken)

Declaration
[CreateSyncVersion(OmitNullableDirective = true)]
public static Task<T> ExecuteScalarAsync<T>(this SqlCommand command, CancellationToken cancellationToken = default)
Parameters
Type Name Description
SqlCommand command
CancellationToken cancellationToken
Returns
Type Description
Task<T>
Type Parameters
Name Description
T

ExecuteScalar<T>(SqlCommand)

Declaration
public static T ExecuteScalar<T>(this SqlCommand command)
Parameters
Type Name Description
SqlCommand command
Returns
Type Description
T
Type Parameters
Name Description
T

GetFieldPropertyRead(QueryExecutionHelper, FieldInfo)

Declaration
public static FieldProperty GetFieldPropertyRead(this QueryExecutionHelper qeh, FieldInfo field)
Parameters
Type Name Description
QueryExecutionHelper qeh
FieldInfo field
Returns
Type Description
FieldProperty