Class SqlHelper

Namespace
DbExport
Assembly
DbExport.Api.dll

A helper class for executing SQL queries and scripts against a database. It provides methods for querying data, executing non-query commands, and executing SQL scripts with support for different database providers. The class implements IDisposable to ensure proper disposal of database connections when necessary.

public sealed class SqlHelper : IDisposable
Inheritance
SqlHelper
Implements
Inherited Members

Constructors

SqlHelper(Database)

Initializes a new instance of the SqlHelper class with the specified Database object.

public SqlHelper(Database database)

Parameters

database Database

The Database object containing the provider name and connection string for the database connection.

SqlHelper(DbConnection)

Initializes a new instance of the SqlHelper class with the specified database connection.

public SqlHelper(DbConnection connection)

Parameters

connection DbConnection

The database connection to be used by the SqlHelper instance.

SqlHelper(string, string)

Initializes a new instance of the SqlHelper class with the specified provider name and connection string.

public SqlHelper(string providerName, string connectionString)

Parameters

providerName string

The name of the database provider.

connectionString string

The connection string for the database connection.

Properties

ProviderName

Gets the name of the database provider being used by the SqlHelper instance.

public string ProviderName { get; }

Property Value

string

Methods

Dispose()

Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.

public void Dispose()

Execute(string)

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

public int Execute(string sql)

Parameters

sql string

The SQL command to be executed.

Returns

int

The number of rows affected by the command.

ExecuteBatch(string, DbDataReader)

Executes a batch of SQL statements using the provided SQL command template and a data reader as the source for parameters.

public int ExecuteBatch(string sql, DbDataReader dataReader)

Parameters

sql string

The SQL command template to be executed for each row of data read from the data reader.

dataReader DbDataReader

The data reader containing the rows of data to be processed in the batch.

Returns

int

The total number of rows affected by executing the batch.

ExecuteBatch<TSource>(string, IEnumerable<TSource>, Action<DbCommand, TSource>)

Executes a batch of SQL commands using a provided SQL statement and a collection of parameters.

public int ExecuteBatch<TSource>(string sql, IEnumerable<TSource> paramSources, Action<DbCommand, TSource> binder)

Parameters

sql string

The SQL command to be executed for each item in the collection.

paramSources IEnumerable<TSource>

The collection of parameter sources, where each item represents the parameters for a single execution of the SQL command.

binder Action<DbCommand, TSource>

A delegate that binds the parameters from a parameter source to a database command.

Returns

int

The total number of rows affected by all executed commands in the batch.

Type Parameters

TSource

The type of the parameter source used for binding.

ExecuteScript(string, string, string)

Executes the specified SQL script against the database using the provided provider name and connection string.

public static void ExecuteScript(string providerName, string connectionString, string script)

Parameters

providerName string

The name of the database provider.

connectionString string

The connection string for the database connection.

script string

A string containing the SQL script to be executed.

Execute<TSource>(string, TSource, Action<DbCommand, TSource>)

Executes a parameterized SQL command using the specified parameter source and binder.

public int Execute<TSource>(string sql, TSource paramSource, Action<DbCommand, TSource> binder)

Parameters

sql string

The SQL command to execute.

paramSource TSource

The source object providing parameter values for the command.

binder Action<DbCommand, TSource>

The action to bind the parameter values from the source object to the command.

Returns

int

The number of rows affected by the command execution.

Type Parameters

TSource

The type of the parameter source object.

FromArray(DbCommand, object[])

Populates the parameters of a database command with the specified array of values.

public static void FromArray(DbCommand command, object[] values)

Parameters

command DbCommand

The database command whose parameters will be populated.

values object[]

An array of values to assign to the command's parameters.

FromDictionary(DbCommand, Dictionary<string, object>)

Populates the parameters of a database command with values from the provided dictionary.

public static void FromDictionary(DbCommand command, Dictionary<string, object> values)

Parameters

command DbCommand

The database command whose parameters will be populated.

values Dictionary<string, object>

A dictionary containing parameter names as keys and their corresponding values.

FromEntity<TEntity>(DbCommand, TEntity)

Sets the parameter values of the specified DbCommand using the property values of the provided entity.

public static void FromEntity<TEntity>(DbCommand command, TEntity entity) where TEntity : class

Parameters

command DbCommand

The database command whose parameters will be updated.

entity TEntity

The entity from which the parameter values are retrieved.

Type Parameters

TEntity

The type of the entity, which must be a reference type.

GetScripExecutor(string)

Gets the script executor that matches the given provider name.

public static IScriptExecutor GetScripExecutor(string providerName)

Parameters

providerName string

The name of the database provider.

Returns

IScriptExecutor

An instance of a class that implements the IScriptExecutor interface.

QueryScalar(string)

Executes the specified SQL query and returns the value of the first column of the first row in the result set.

public object QueryScalar(string sql)

Parameters

sql string

The SQL query to be executed.

Returns

object

The value of the first column of the first row in the result set, or null if the result set is empty.

QueryScalar<TSource>(string, TSource, Action<DbCommand, TSource>)

Executes the specified SQL query with parameters, using a custom binder to bind the parameters, and returns a single scalar value resulting from the query.

public object QueryScalar<TSource>(string sql, TSource paramSource, Action<DbCommand, TSource> binder)

Parameters

sql string

The SQL query to be executed.

paramSource TSource

The object containing the parameter values to be used in the SQL query.

binder Action<DbCommand, TSource>

An action that binds the parameter values to the SQL command.

Returns

object

The scalar value returned from the execution of the SQL query.

Type Parameters

TSource

The type of the parameter source.

Query<TResult>(string, Func<DbDataReader, TResult>)

Executes the specified SQL query and uses the provided extractor function to process the results from the data reader.

public TResult Query<TResult>(string sql, Func<DbDataReader, TResult> extractor)

Parameters

sql string

The SQL query to be executed.

extractor Func<DbDataReader, TResult>

The function that processes the data reader and returns a result of type TResult.

Returns

TResult

A result of type TResult obtained by processing the data reader with the extractor function.

Type Parameters

TResult

The type of the result returned by the extractor function.

Query<TSource, TResult>(string, TSource, Action<DbCommand, TSource>, Func<DbDataReader, TResult>)

Executes a SQL query with the specified parameters, binds them to the command, and extracts the result using the provided extractor function.

public TResult Query<TSource, TResult>(string sql, TSource paramSource, Action<DbCommand, TSource> binder, Func<DbDataReader, TResult> extractor)

Parameters

sql string

The SQL query to be executed.

paramSource TSource

The source object containing parameters to be bound to the query.

binder Action<DbCommand, TSource>

An action that binds parameters from the source object to the database command.

extractor Func<DbDataReader, TResult>

A function that processes the data reader and extracts the desired result.

Returns

TResult

The result extracted from the data reader based on the extractor function.

Type Parameters

TSource

The type of the parameter source object.

TResult

The type of the result to be returned.

ToArray(DbDataReader)

Extracts the values of the columns from the first row of the data reader and returns them as an array of objects.

public static object[] ToArray(DbDataReader dataReader)

Parameters

dataReader DbDataReader

The DbDataReader from which to extract the column values.

Returns

object[]

An array of objects containing the values of the columns from the first row of the data reader, or null if the data reader is empty.

ToArrayList(DbDataReader)

Extracts the values of the columns from all rows of the data reader and returns them as a list of object arrays, where each object array represents a row of data, and the elements of the array correspond to the column values in that row.

public static List<object[]> ToArrayList(DbDataReader dataReader)

Parameters

dataReader DbDataReader

The DbDataReader from which to extract the column values.

Returns

List<object[]>

A list of object arrays, where each object array contains the column values for a row of data from the data reader.

ToDictionary(DbDataReader)

Extracts the values of the columns from the first row of the data reader and returns them as a dictionary, where the keys are the column names and the values are the corresponding column values.

public static Dictionary<string, object> ToDictionary(DbDataReader dataReader)

Parameters

dataReader DbDataReader

The DbDataReader from which to extract the column values.

Returns

Dictionary<string, object>

A dictionary containing the column names as keys and the corresponding column values as values from the first row of the data reader, or null if the data reader is empty.

ToDictionaryList(DbDataReader)

Extracts the values of the columns from all rows of the data reader and returns them as a list of dictionaries, where each dictionary represents a row of data, and the keys of the dictionary are the column names, and the values are the corresponding column values for that row.

public static List<Dictionary<string, object>> ToDictionaryList(DbDataReader dataReader)

Parameters

dataReader DbDataReader

The DbDataReader from which to extract the column values.

Returns

List<Dictionary<string, object>>

A list of dictionaries, where each dictionary contains the column names as keys and the corresponding column values as values for a row of data from the data reader.

ToEntityList<TEntity>(DbDataReader)

Converts the data retrieved by a DbDataReader into a list of entities of the specified type.

public static List<TEntity> ToEntityList<TEntity>(DbDataReader dataReader) where TEntity : class, new()

Parameters

dataReader DbDataReader

The DbDataReader containing the data to be converted.

Returns

List<TEntity>

A list of entities of type TEntity, with each entity representing a row from the DbDataReader.

Type Parameters

TEntity

The type of the entity to convert each row into. This type must be a class and have a parameterless constructor.

ToEntity<TEntity>(DbDataReader)

Converts the current row of the specified DbDataReader into an instance of the specified entity type.

public static TEntity ToEntity<TEntity>(DbDataReader dataReader) where TEntity : class, new()

Parameters

dataReader DbDataReader

The DbDataReader positioned on the row to be converted.

Returns

TEntity

An instance of TEntity populated with values from the current row of the dataReader, or null if no rows are available to read.

Type Parameters

TEntity

The type of the entity to create. Must be a reference type with a parameterless constructor.

ToList(DbDataReader)

Extracts the values of the first column from all rows of the data reader and returns them as a list of objects, where each object in the list corresponds to the value of the first column for a row of data from the data reader.

public static List<object> ToList(DbDataReader dataReader)

Parameters

dataReader DbDataReader

The DbDataReader from which to extract the column values.

Returns

List<object>

A list of objects containing the values of the first column for each row of data from the data reader.

Edit this page