Class SqlHelper

SQL Server data access helper providing async query execution and result mapping. Handles connection management, command execution, and result transformation.

Inheritance

object

SqlHelper

Implements

ISqlHelper

Inherited Members

object.Equals(object)

object.Equals(object, object)

object.GetHashCode()

object.GetType()

object.MemberwiseClone()

object.ReferenceEquals(object, object)

object.ToString()

Namespace: DataWeb.Data.SqlServer

Assembly: DataWeb.Data.SqlServer.dll

Syntax

public class SqlHelper : ISqlHelper

Remarks

Implements ISqlHelper interface with methods for:

• GetListAsync: Execute queries and buffer results into a read-only list
• GetStreamAsync: Execute queries and stream results as IAsyncEnumerable (no buffering)
• GetReaderAsync: Execute queries returning open DataReader (caller must dispose)
• GetValueAsync: Execute scalar queries returning single values
• ExecuteAsync: Execute non-query commands (INSERT, UPDATE, DELETE)
• Utility methods for boolean/bit conversions (SQL Server specifics)

All methods support:

• Parameterized queries to prevent SQL injection
• Optional custom connection strings
• Cancellation tokens for async operation control
• Detailed logging of query execution

Constructors

SqlHelper(IConfigurationService, ILogger<SqlHelper>)

SQL Server data access helper providing async query execution and result mapping. Handles connection management, command execution, and result transformation.

Declaration

public SqlHelper(IConfigurationService configurationService, ILogger<SqlHelper> logger)

Parameters

Type	Name	Description
IConfigurationService	configurationService
ILogger<SqlHelper>	logger

Remarks

Implements ISqlHelper interface with methods for:

• GetListAsync: Execute queries and buffer results into a read-only list
• GetStreamAsync: Execute queries and stream results as IAsyncEnumerable (no buffering)
• GetReaderAsync: Execute queries returning open DataReader (caller must dispose)
• GetValueAsync: Execute scalar queries returning single values
• ExecuteAsync: Execute non-query commands (INSERT, UPDATE, DELETE)
• Utility methods for boolean/bit conversions (SQL Server specifics)

All methods support:

• Parameterized queries to prevent SQL injection
• Optional custom connection strings
• Cancellation tokens for async operation control
• Detailed logging of query execution

Methods

ExecuteAsync(Query, string, CancellationToken)

Executes a non-query command (INSERT, UPDATE, DELETE). Overload accepting a pre-compiled Query object.

Declaration

public Task ExecuteAsync(Query query, string connectionString = null, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
Query	query	Pre-compiled Query object containing SQL and parameters.
string	connectionString	Optional custom connection string (uses default if null).
CancellationToken	cancellationToken	Token to cancel the async operation.

Returns

Type	Description
Task

Remarks

Automatically manages connection and command disposal after execution. Does not return result set; use for data modification operations only.

ExecuteAsync(string, List<SqlParameter>, string, CancellationToken)

Executes a non-query command (INSERT, UPDATE, DELETE).

Declaration

public Task ExecuteAsync(string query, List<SqlParameter> parameters = null, string connectionString = null, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
string	query	SQL command (T-SQL).
List<SqlParameter>	parameters	Optional list of SqlParameter objects for parameterized command.
string	connectionString	Optional custom connection string (uses default if null).
CancellationToken	cancellationToken	Token to cancel the async operation.

Returns

Type	Description
Task

Remarks

Automatically manages connection and command disposal after execution. Does not return result set; use for INSERT, UPDATE, DELETE operations only.

Examples

await sqlHelper.ExecuteAsync("UPDATE Items SET Status = @Status WHERE Id = @Id", parameters);

Exceptions

Type	Condition
ArgumentException	Thrown if query is null or empty.

GetListAsync<T>(Query, Func<IDataReader, T>, string, CancellationToken)

Executes a query and returns a fully materialized read-only list of mapped results. Overload accepting a pre-compiled Query object.

Declaration

public Task<IReadOnlyList<T>> GetListAsync<T>(Query query, Func<IDataReader, T> fetchReader, string connectionString = null, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
Query	query	Pre-compiled Query object containing SQL and parameters.
Func<IDataReader, T>	fetchReader	Delegate to map IDataReader row to type T.
string	connectionString	Optional custom connection string (uses default if null).
CancellationToken	cancellationToken	Token to cancel the async operation.

Returns

Type	Description
Task<IReadOnlyList<T>>	Fully materialized read-only list of mapped results, or empty if no rows returned.

Type Parameters

Name	Description
T	Type to map each row to.

Examples

var items = await sqlHelper.GetListAsync(query, reader => new Item { Id = reader["Id"] });

GetListAsync<T>(string, Func<IDataReader, T>, List<SqlParameter>, int, string, CancellationToken)

Executes a query and returns a fully materialized read-only list of mapped results.

Declaration

public Task<IReadOnlyList<T>> GetListAsync<T>(string query, Func<IDataReader, T> fetchReader, List<SqlParameter> parameters = null, int count = 0, string connectionString = null, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
string	query	SQL query (T-SQL).
Func<IDataReader, T>	fetchReader	Delegate to map IDataReader row to type T.
List<SqlParameter>	parameters	Optional list of SqlParameter objects for parameterized query.
int	count	Maximum number of rows to return. 0 = all rows. Optimizes CommandBehavior.
string	connectionString	Optional custom connection string (uses default if null).
CancellationToken	cancellationToken	Token to cancel the async operation.

Returns

Type	Description
Task<IReadOnlyList<T>>	Fully materialized read-only list of mapped results. Pre-allocated based on expected count.

Type Parameters

Name	Description
T	Type to map each row to.

Remarks

List is pre-allocated with capacity: count (if count > 0) or 16 (default). When count=1, CommandBehavior.SingleRow optimization is applied. For large result sets, prefer GetStreamAsync to avoid buffering all rows in memory.

Exceptions

Type	Condition
ArgumentException	Thrown if query is null or empty.

GetReaderAsync(Query, string, CancellationToken)

Executes a query and returns an open DataReader. Overload accepting a pre-compiled Query object.

Declaration

public Task<DbDataReader> GetReaderAsync(Query query, string connectionString = null, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
Query	query	Pre-compiled Query object containing SQL and parameters.
string	connectionString	Optional custom connection string (uses default if null).
CancellationToken	cancellationToken	Token to cancel the async operation.

Returns

Type	Description
Task<DbDataReader>	Open DbDataReader with associated connection and command.

Remarks

Caller MUST dispose the returned reader using 'await using' statement. Disposing the reader will also dispose the underlying command and connection. Failure to dispose will cause connection leaks.

CORRECT usage: await using var reader = await sqlHelper.GetReaderAsync(query); while (await reader.ReadAsync()) { /* use reader */ } // Reader disposed automatically here

GetReaderAsync(string, List<SqlParameter>, int, string, CancellationToken)

Executes a query and returns an open DataReader.

Declaration

public Task<DbDataReader> GetReaderAsync(string query, List<SqlParameter> parameters = null, int count = 0, string connectionString = null, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
string	query	SQL query (T-SQL).
List<SqlParameter>	parameters	Optional list of SqlParameter objects for parameterized query.
int	count	Expected number of rows. 0 = all rows. Optimizes CommandBehavior.
string	connectionString	Optional custom connection string (uses default if null).
CancellationToken	cancellationToken	Token to cancel the async operation.

Returns

Type	Description
Task<DbDataReader>	Open DbDataReader with associated connection and command.

Remarks

Caller MUST dispose the returned reader using 'await using' statement. Disposing the reader will also dispose the underlying command and connection.

When count=1, CommandBehavior.SingleRow optimization is applied for better performance.

CORRECT usage: await using var reader = await sqlHelper.GetReaderAsync(sql); while (await reader.ReadAsync()) { /* use reader */ } // Reader disposed automatically here

Exceptions

Type	Condition
ArgumentException	Thrown if query is null or empty.

GetStreamAsync<T>(Query, Func<IDataReader, T>, string, CancellationToken)

Executes a query and streams results as an async enumerable. Overload accepting a pre-compiled Query object.

Declaration

public IAsyncEnumerable<T> GetStreamAsync<T>(Query query, Func<IDataReader, T> fetchReader, string connectionString = null, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
Query	query	Pre-compiled Query object containing SQL and parameters.
Func<IDataReader, T>	fetchReader	Delegate to map IDataReader row to type T.
string	connectionString	Optional custom connection string (uses default if null).
CancellationToken	cancellationToken	Token to cancel the async operation.

Returns

Type	Description
IAsyncEnumerable<T>	Async stream of mapped results; rows are yielded as they are read from the database.

Type Parameters

Name	Description
T	Type to map each row to.

Remarks

Prefer over GetListAsync when the result set is large and full buffering should be avoided. Caller iterates with 'await foreach'.

GetStreamAsync<T>(string, Func<IDataReader, T>, List<SqlParameter>, int, string, CancellationToken)

Executes a query and streams results as an async enumerable.

Declaration

public IAsyncEnumerable<T> GetStreamAsync<T>(string query, Func<IDataReader, T> fetchReader, List<SqlParameter> parameters = null, int count = 0, string connectionString = null, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
string	query	SQL query (T-SQL).
Func<IDataReader, T>	fetchReader	Delegate to map IDataReader row to type T.
List<SqlParameter>	parameters	Optional list of SqlParameter objects for parameterized query.
int	count	Expected number of rows. Used only to apply CommandBehavior.SingleRow when count=1.
string	connectionString	Optional custom connection string (uses default if null).
CancellationToken	cancellationToken	Token to cancel the async operation.

Returns

Type	Description
IAsyncEnumerable<T>	Async stream of mapped results; rows are yielded as they are read from the database.

Type Parameters

Name	Description
T	Type to map each row to.

Remarks

No buffering: rows are yielded one at a time as they arrive from SQL Server. Use 'await foreach' to consume. The connection is kept open until iteration completes or is abandoned. Prefer over GetListAsync for large result sets.

Exceptions

Type	Condition
ArgumentException	Thrown if query is null or empty.

GetValueAsync(Query, string, CancellationToken)

Executes a scalar query and returns a single value. Overload accepting a pre-compiled Query object.

Declaration

public Task<object> GetValueAsync(Query query, string connectionString = null, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
Query	query	Pre-compiled Query object containing SQL and parameters.
string	connectionString	Optional custom connection string (uses default if null).
CancellationToken	cancellationToken	Token to cancel the async operation.

Returns

Type	Description
Task<object>	First column of first row, or null if result set is empty.

Remarks

Automatically manages connection and command disposal. Suitable for COUNT, SUM, MAX, MIN and similar aggregate queries.

Examples

var count = await sqlHelper.GetValueAsync(new Query { /* ... */ });

GetValueAsync(string, List<SqlParameter>, string, CancellationToken)

Executes a scalar query and returns a single value.

Declaration

public Task<object> GetValueAsync(string query, List<SqlParameter> parameters = null, string connectionString = null, CancellationToken cancellationToken = default)

Parameters

Type	Name	Description
string	query	SQL query (T-SQL).
List<SqlParameter>	parameters	Optional list of SqlParameter objects for parameterized query.
string	connectionString	Optional custom connection string (uses default if null).
CancellationToken	cancellationToken	Token to cancel the async operation.

Returns

Type	Description
Task<object>	First column of first row of the result set, or null if empty.

Remarks

Automatically manages connection and command disposal. Suitable for COUNT, SUM, MAX, MIN and other aggregate functions.

Examples

var itemCount = await sqlHelper.GetValueAsync("SELECT COUNT(*) FROM Items");

Exceptions

Type	Condition
ArgumentException	Thrown if query is null or empty.

ToBit(bool)

Converts a boolean value to SQL Server bit integer representation.

Declaration

public int ToBit(bool value)

Parameters

Type	Name	Description
bool	value	Boolean value to convert.

Returns

Type	Description
int	1 for true, 0 for false.

Remarks

Preferred over ToBitString for parameterized queries. Can be passed to SqlParameter directly.

ToBitString(bool)

Converts a boolean value to SQL Server bit string representation.

Declaration

public string ToBitString(bool value)

Parameters

Type	Name	Description
bool	value	Boolean value to convert.

Returns

Type	Description
string	"1" for true, "0" for false.

Remarks

Used for SQL string concatenation where bit values need text representation. For parameterized queries, prefer ToBit() to avoid string concatenation.

ToBool(char)

Converts a SQL Server bit character to boolean.

Declaration

public bool ToBool(char value)

Parameters

Type	Name	Description
char	value	Character value ('1' or '0').

Returns

Type	Description
bool	true if value is '1', false otherwise.

ToBool(int)

Converts a SQL Server bit integer to boolean.

Declaration

public bool ToBool(int value)

Parameters

Type	Name	Description
int	value	Integer value (1 or 0).

Returns

Type	Description
bool	true if value is 1, false otherwise.

Implements

ISqlHelper