/// <summary>
/// Creates and runs a command to execute a command text
/// </summary>
/// <param name="connection">Database connection</param>
/// <param name="commandText">Command text</param>
/// <param name="isStoredProcedure">Indicates if the command text is a stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>Number of affected rows</returns>
internal static async Task <int> ExecuteNonQueryAsync(DBSqlServerConnection connection, string commandText, bool isStoredProcedure, params DBSqlServerParameter[] parameters)
{
try
{
using (var command = new DBSqlServerCommand(connection: connection, commandText: commandText, isStoredProcedure: isStoredProcedure, parameters: parameters))
{
return(await command.Command.ExecuteNonQueryAsync().ConfigureAwait(false));
}
}
catch (Exception ex)
{
ex.ConvertSqlServerException(isConnecting: false);
throw;
}
}
/// <summary>
/// Create a new prepared statement (used by DBSqlServerBaseDAL)
/// </summary>
/// <param name="connection">Database connection</param>
/// <param name="commandText">Command text</param>
/// <param name="isStoredProcedure">Indicates if the command text is a stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
internal DBSqlServerPreparedStatement(DBSqlServerConnection connection, string commandText, bool isStoredProcedure, params DBSqlServerParameter[] parameters)
{
try
{
Command = DBSqlServerCommand.CreatePreparedStatement(
connection: connection,
commandText: commandText,
isStoredProcedure: isStoredProcedure,
parameters: parameters);
}
catch (Exception ex)
{
ex.ConvertSqlServerException(isConnecting: false);
throw;
}
}
/// <summary>
/// Creates a new prepared statement (used by DBSqlServerPreparedStatement)
/// </summary>
/// <param name="connection">Database connection</param>
/// <param name="commandText">Command text</param>
/// <param name="isStoredProcedure">Indicates if the command text is a stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
internal static DBSqlServerCommand CreatePreparedStatement(DBSqlServerConnection connection, string commandText, bool isStoredProcedure, params DBSqlServerParameter[] parameters)
{
DBSqlServerCommand command = null;
try
{
command = new DBSqlServerCommand(connection: connection, commandText: commandText, isStoredProcedure: isStoredProcedure, parameters: parameters);
command.Command.Prepare();
return(command);
}
catch (Exception ex)
{
command?.Close();
ex.ConvertSqlServerException(isConnecting: false);
throw;
}
}
/// <summary>
/// Creates and runs a command to retrieve a DataTable using a command text
/// </summary>
/// <param name="connection">Database connection</param>
/// <param name="commandText">Command text</param>
/// <param name="isStoredProcedure">Indicates if the command text is a stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>DataTable with the command text results</returns>
internal static DataTable GetDataTable(DBSqlServerConnection connection, string commandText, bool isStoredProcedure, params DBSqlServerParameter[] parameters)
{
try
{
using (var command = new DBSqlServerCommand(connection: connection, commandText: commandText, isStoredProcedure: isStoredProcedure, parameters: parameters))
{
SqlDataAdapter dataAdapter;
try
{
dataAdapter = new SqlDataAdapter(command.Command);
}
catch (Exception ex)
{
try
{
Log.Debug(DBSqlServerLocalizedText.DBSqlServerCommand_GetDataSet_DataAdapterError, ex);
}
catch (Exception)
{
// Nothing to do
}
throw;
}
bool suppressDisposeException = false;
try
{
var dataTable = new DataTable();
dataAdapter.Fill(dataTable);
return(dataTable);
}
catch (Exception ex)
{
try
{
Log.Debug(DBSqlServerLocalizedText.DBSqlServerCommand_GetDataSet_DataRetrieveError, ex);
}
catch (Exception)
{
// Nothing to do
}
suppressDisposeException = true;
throw;
}
finally
{
try
{
dataAdapter.Dispose();
}
catch (Exception ex)
{
try
{
Log.Error(DBSqlServerLocalizedText.DBSqlServerCommand_DataAdapter_Disposal_Error, ex);
}
catch (Exception)
{
// Nothing to do
}
if (!suppressDisposeException)
{
throw;
}
}
}
}
}
catch (Exception ex)
{
ex.ConvertSqlServerException(isConnecting: false);
throw;
}
}
/// <summary>
/// Creates and runs a command to execute a query command text that returns a single row
/// </summary>
/// <typeparam name="T">Return type</typeparam>
/// <param name="connection">Database connection</param>
/// <param name="dataFiller">Function to convert the returned row into the return type object</param>
/// <param name="commandText">Command text</param>
/// <param name="isStoredProcedure">Indicates if the command text is a stored procedure name</param>
/// <param name="nullValue">Value to be returned if the query command text does not return any row</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>Command text result, or nullValue if none</returns>
internal static async Task <T> RetrieveDataItemAsync <T>(DBSqlServerConnection connection, Func <DBSqlServerDataReader, T> dataFiller, string commandText, bool isStoredProcedure, T nullValue = default(T), params DBSqlServerParameter[] parameters)
{
try
{
using (var command = new DBSqlServerCommand(connection: connection, commandText: commandText, isStoredProcedure: isStoredProcedure, parameters: parameters))
{
var sqlDataReader = await command.Command.ExecuteReaderAsync(behavior : CommandBehavior.SingleRow).ConfigureAwait(false);
DBSqlServerDataReader dataReader = null;
try
{
dataReader = new DBSqlServerDataReader(sqlDataReader);
return((await sqlDataReader.ReadAsync().ConfigureAwait(false))
? dataFiller(dataReader)
: nullValue);
}
finally
{
dataReader?.Cleanup();
try
{
sqlDataReader?.Close();
}
catch (Exception ex)
{
try
{
Log.Error(DBSqlServerLocalizedText.DBSqlServerCommand_CloseDataReader, ex);
}
catch (Exception)
{
// Nothing to do
}
}
try
{
sqlDataReader?.Dispose();
}
catch (Exception ex)
{
try
{
Log.Error(DBSqlServerLocalizedText.DBSqlServerCommand_DisposeDataReader, ex);
}
catch (Exception)
{
// Nothing to do
}
}
}
}
}
catch (Exception ex)
{
ex.ConvertSqlServerException(isConnecting: false);
throw;
}
}
/// <summary>
/// Creates and runs a command to execute a command text that returns multiple rows
/// </summary>
/// <typeparam name="T">Return type</typeparam>
/// <param name="connection">Database connection</param>
/// <param name="dataFiller">Function to convert the returned rows into the return type object</param>
/// <param name="commandText">Command text</param>
/// <param name="isStoredProcedure">Indicates if the command text is a stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>List of objects of the return type</returns>
internal static async Task <List <T> > RetrieveDataListAsync <T>(DBSqlServerConnection connection, Func <DBSqlServerDataReader, T> dataFiller, string commandText, bool isStoredProcedure, params DBSqlServerParameter[] parameters)
{
try
{
using (var command = new DBSqlServerCommand(connection: connection, commandText: commandText, isStoredProcedure: isStoredProcedure, parameters: parameters))
{
var sqlDataReader = await command.Command.ExecuteReaderAsync().ConfigureAwait(false);
DBSqlServerDataReader dataReader = null;
List <T> rowList = null;
try
{
dataReader = new DBSqlServerDataReader(sqlDataReader);
rowList = new List <T>();
while (await sqlDataReader.ReadAsync().ConfigureAwait(false))
{
rowList.Add(dataFiller(dataReader));
}
return(rowList);
}
catch (Exception)
{
rowList?.Clear(); // This method fills the internal array with zeros to help the gc
throw;
}
finally
{
dataReader?.Cleanup();
try
{
sqlDataReader?.Close();
}
catch (Exception ex)
{
try
{
Log.Error(DBSqlServerLocalizedText.DBSqlServerCommand_CloseDataReader, ex);
}
catch (Exception)
{
// Nothing to do
}
}
try
{
sqlDataReader?.Dispose();
}
catch (Exception ex)
{
try
{
Log.Error(DBSqlServerLocalizedText.DBSqlServerCommand_DisposeDataReader, ex);
}
catch (Exception)
{
// Nothing to do
}
}
}
}
}
catch (Exception ex)
{
ex.ConvertSqlServerException(isConnecting: false);
throw;
}
}
/// <summary>
/// Creates and runs a command to execute a stored procedure
/// </summary>
/// <param name="commandText">Stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>Number of affected rows</returns>
protected async Task <int> ExecuteSPAsync(string commandText, params DBSqlServerParameter[] parameters)
{
return(await DBSqlServerCommand.ExecuteNonQueryAsync(connection : this, commandText : commandText, isStoredProcedure : true, parameters : parameters).ConfigureAwait(false));
}
/// <summary>
/// Creates and runs a command to retrieve a DataTable using a stored procedure
/// </summary>
/// <param name="commandText">Stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>DataTable with the query results</returns>
protected DataTable RetrieveDataTableSP(string commandText, params DBSqlServerParameter[] parameters)
{
return(DBSqlServerCommand.GetDataTable(connection: this, commandText: commandText, isStoredProcedure: true, parameters: parameters));
}
/// <summary>
/// Creates and runs a command to execute a stored procedure
/// </summary>
/// <param name="commandText">Stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>Number of affected rows</returns>
protected int ExecuteSP(string commandText, params DBSqlServerParameter[] parameters)
{
return(DBSqlServerCommand.ExecuteNonQuery(connection: this, commandText: commandText, isStoredProcedure: true, parameters: parameters));
}
/// <summary>
/// Creates and runs a command to execute a stored procedure that returns a single row
/// </summary>
/// <typeparam name="T">Return type</typeparam>
/// <param name="dataFiller">Function to convert the returned row into the return type object</param>
/// <param name="commandText">Stored procedure name</param>
/// <param name="nullValue">Value to be returned if the stored procedure does not return any row</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>Stored procedure result, or nullValue if none</returns>
protected async Task <T> RetrieveDataItemSPAsync <T>(Func <DBSqlServerDataReader, T> dataFiller, string commandText, T nullValue = default(T), params DBSqlServerParameter[] parameters)
{
return(await DBSqlServerCommand.RetrieveDataItemAsync(connection : this, dataFiller : dataFiller, commandText : commandText, isStoredProcedure : true, nullValue : nullValue, parameters : parameters).ConfigureAwait(false));
}
/// <summary>
/// Creates and runs a command to execute a stored procedure that returns a single row
/// </summary>
/// <typeparam name="T">Return type</typeparam>
/// <param name="dataFiller">Function to convert the returned row into the return type object</param>
/// <param name="commandText">Stored procedure name</param>
/// <param name="nullValue">Value to be returned if the stored procedure does not return any row</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>Stored procedure result, or nullValue if none</returns>
protected T RetrieveDataItemSP <T>(Func <DBSqlServerDataReader, T> dataFiller, string commandText, T nullValue = default(T), params DBSqlServerParameter[] parameters)
{
return(DBSqlServerCommand.RetrieveDataItem(connection: this, dataFiller: dataFiller, commandText: commandText, isStoredProcedure: true, nullValue: nullValue, parameters: parameters));
}
/// <summary>
/// Creates and runs a command to execute a stored procedure that returns multiple rows
/// </summary>
/// <typeparam name="T">Return type</typeparam>
/// <param name="dataFiller">Function to convert the returned rows into the return type object</param>
/// <param name="commandText">Stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>List of objects of the return type</returns>
protected async Task <List <T> > RetrieveDataListSPAsync <T>(Func <DBSqlServerDataReader, T> dataFiller, string commandText, params DBSqlServerParameter[] parameters)
{
return(await DBSqlServerCommand.RetrieveDataListAsync(connection : this, dataFiller : dataFiller, commandText : commandText, isStoredProcedure : true, parameters : parameters).ConfigureAwait(false));
}
/// <summary>
/// Creates and runs a command to execute a stored procedure that returns multiple rows
/// </summary>
/// <typeparam name="T">Return type</typeparam>
/// <param name="dataFiller">Function to convert the returned rows into the return type object</param>
/// <param name="commandText">Stored procedure name</param>
/// <param name="parameters">Bind parameters</param>
/// <returns>List of objects of the return type</returns>
protected List <T> RetrieveDataListSP <T>(Func <DBSqlServerDataReader, T> dataFiller, string commandText, params DBSqlServerParameter[] parameters)
{
return(DBSqlServerCommand.RetrieveDataList(connection: this, dataFiller: dataFiller, commandText: commandText, isStoredProcedure: true, parameters: parameters));
}