/// <summary>
/// Executes specified query
/// </summary>
/// <param name="command">Sql string or stored procedure name</param>
/// <param name="parameters">Query parameters</param>
/// <param name="timeout">Timeout in seconds</param>
/// <param name="commandType">Command type: text / stored procedure / TableDirect</param>
/// <param name="transaction">If transaction was opened, it must be specified</param>
/// <returns>Result code of the query</returns>
virtual public int Exec(
string command,
DbParametersConstructor parameters = null,
int?timeout = null,
CommandType commandType = CommandType.Text,
IDbTransaction transaction = null)
{
int result;
parameters = parameters ?? DbParametersConstructor.EmptyParams;
SqlParameterNullWorkaround(parameters);
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = command;
foreach (var c in parameters.Parameters)
{
cmd.Parameters.Add(c);
}
cmd.CommandType = commandType;
cmd.Transaction = transaction;
cmd.CommandTimeout = timeout ?? DefaultExecutionTimeout;
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
result = cmd.ExecuteNonQuery();
cmd.Parameters?.Clear();
}
return(result);
}
/// <summary>
/// Return IEnumerable with results
/// </summary>
/// <typeparam name="T">Generic resulting type</typeparam>
/// <param name="command">SQL command; in case of stored procedure this parameter stores only Proc name, commandType must be specified then</param>
/// <param name="transformFunction">Delegate to recive T from DataReader</param>
/// <param name="parameters">Values of parameters (if necessary)</param>
/// <param name="timeout">Timeout</param>
/// <param name="commandType">Type of batch</param>
/// <param name="transaction">The transaction, inside of wich the command will be executed</param>
/// <returns>Consequentially readed data</returns>
virtual public IEnumerable <T> Query <T>(
string command,
Func <IDataReader, T> transformFunction,
DbParametersConstructor parameters = null,
int?timeout = null,
CommandType commandType = CommandType.Text,
IDbTransaction transaction = null)
{
parameters = parameters ?? DbParametersConstructor.EmptyParams;
SqlParameterNullWorkaround(parameters);
using (var cmd = Connection.CreateCommand())
{
cmd.CommandTimeout = timeout ?? DefaultExecutionTimeout;
cmd.CommandType = commandType;
cmd.CommandText = command;
foreach (var c in parameters.Parameters)
{
cmd.Parameters.Add(c);
}
cmd.Transaction = transaction;
if (this.Connection.State != ConnectionState.Open)
{
Connection.Open();
}
using (var dr = cmd.ExecuteReader(CommandBehavior.SingleResult))
{
while (dr.Read())
{
yield return(transformFunction(dr));
}
}
cmd.Parameters?.Clear();
}
}
/// <summary>
/// Performs ExecuteReader for specified command, performs specified delegate on result, than disposes datareader and command
/// </summary>
/// <typeparam name="T">Result type</typeparam>
/// <param name="command">Sql string or stored procedure name</param>
/// <param name="transformFunction">Delegate for operating whith result datareader</param>
/// <param name="parameters">Query parameters</param>
/// <param name="timeout">Timeout in seconds</param>
/// <param name="commandType">Command type: text / stored procedure / TableDirect</param>
/// <param name="commandBehavior">Hint for exucuting command <see cref="https://docs.microsoft.com/dotnet/api/system.data.commandbehavior" /></param>
/// <param name="transaction">If transaction was opened, it must be specified</param>
/// <returns>T-object, result of delegate execution</returns>
virtual public T ManualProcessing <T>(
string command,
Func <IDataReader, T> transformFunction,
DbParametersConstructor parameters = null,
int?timeout = null,
CommandType commandType = CommandType.Text,
CommandBehavior commandBehavior = CommandBehavior.Default,
IDbTransaction transaction = null)
{
parameters = parameters ?? DbParametersConstructor.EmptyParams;
SqlParameterNullWorkaround(parameters);
T result;
using (var cmd = Connection.CreateCommand())
{
cmd.CommandTimeout = timeout ?? DefaultExecutionTimeout;
cmd.CommandType = commandType;
cmd.CommandText = command;
foreach (var c in parameters.Parameters)
{
cmd.Parameters.Add(c);
}
cmd.Transaction = transaction;
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
using (var dr = cmd.ExecuteReader(commandBehavior))
{
result = transformFunction(dr);
}
cmd.Parameters?.Clear();
}
return(result);
}
/// <summary>
/// Executes specified query asyncroniously
/// </summary>
/// <param name="command">Sql string or stored procedure name</param>
/// <param name="parameters">Query parameters</param>
/// <param name="timeout">Timeout in seconds</param>
/// <param name="commandType">Command type: text / stored procedure / TableDirect</param>
/// <param name="transaction">If transaction was opened, it must be specified</param>
/// <returns>Result code of the query</returns>
public async Task <int> ExecAsync(
string command,
DbParametersConstructor parameters = null,
int?timeout = null,
CommandType commandType = CommandType.Text,
DbTransaction transaction = null)
{
var conn = Connection as DbConnection;
if (conn == null)
{
throw new NotSupportedException(DbConnectionException);
}
parameters = parameters ?? DbParametersConstructor.EmptyParams;
SqlParameterNullWorkaround(parameters);
await using var cmd = conn.CreateCommand();
cmd.CommandText = command;
cmd.Parameters.AddRange(parameters.Parameters);
cmd.CommandType = commandType;
cmd.Transaction = transaction;
cmd.CommandTimeout = timeout ?? DefaultExecutionTimeout;
if (conn.State != ConnectionState.Open)
{
await conn.OpenAsync();
}
var result = await cmd.ExecuteNonQueryAsync();
cmd.Parameters?.Clear();
return(result);
}
/// <summary>
/// Return IAsyncEnumerable with results
/// </summary>
/// <typeparam name="T">Generic resulting type</typeparam>
/// <param name="command">SQL command; in case of stored procedure this parameter stores only Proc name, commandType must be specified then</param>
/// <param name="transformFunction">Delegate to recive T from DataReader</param>
/// <param name="parameters">Values of parameters (if necessary)</param>
/// <param name="timeout">Timeout</param>
/// <param name="commandType">Type of batch</param>
/// <param name="transaction">The transaction, inside of wich the command will be executed</param>
/// <returns>Consequentially readed data</returns>
public async IAsyncEnumerable <T> QueryAsync <T>(
string command,
Func <IDataReader, T> transformFunction,
DbParametersConstructor parameters = null,
int?timeout = null,
CommandType commandType = CommandType.Text,
DbTransaction transaction = null)
{
var conn = Connection as DbConnection;
if (conn == null)
{
throw new NotSupportedException(DbConnectionException);
}
parameters ??= DbParametersConstructor.EmptyParams;
SqlParameterNullWorkaround(parameters);
await using var cmd = conn.CreateCommand();
cmd.CommandTimeout = timeout ?? DefaultExecutionTimeout;
cmd.CommandType = commandType;
cmd.CommandText = command;
cmd.Parameters.AddRange(parameters.Parameters);
cmd.Transaction = transaction;
if (conn.State != ConnectionState.Open)
{
await conn.OpenAsync();
}
await using var dr = await(cmd).ExecuteReaderAsync(CommandBehavior.SingleResult);
while (await dr.ReadAsync())
{
yield return(transformFunction(dr));
}
cmd.Parameters?.Clear();
}
/// <summary>
/// Executes query and returns DataTable with results
/// </summary>
/// <param name="query">Sql string or stored procedure name</param>
/// <param name="parameters">Query parameters</param>
/// <param name="timeout">Timeout in seconds</param>
/// <param name="commandType">Command type: text / stored procedure / TableDirect</param>
/// <param name="transaction">If transaction was opened, it must be specified</param>
/// <returns>The DataTable with results</returns>
virtual public DataTable GetDataTable(
string query,
DbParametersConstructor parameters = null,
int?timeout = null,
CommandType commandType = CommandType.Text,
IDbTransaction transaction = null)
{
return(ManualProcessing(query, dr =>
{
var dt = new DataTable();
dt.Load(dr);
return dt;
},
parameters, timeout, commandType, CommandBehavior.SingleResult, transaction));
}