/// <summary>
/// Executes query sql text or stored procedure for a list.
/// </summary>
/// <typeparam name="T">Type of the list of returned model</typeparam>
/// <param name="sqlQuery">SQL Query</param>
/// <param name="commandType">SQL Query command type</param>
/// <param name="parametersCollection">Input/Output parameter list</param>
/// <param name="isReturnValueExists">Indicates whether return value, needed to be included</param>
/// <returns>QueryReturnItem</returns>
protected virtual QueryReturnItem <T> ExecuteQueryByCommandType <T>(string sqlQuery, CommandType commandType, IDbParameterList parametersCollection, bool isReturnValueExists)
{
QueryReturnItem <T> returnItem = new QueryReturnItem <T>();
GetDynamicParametersAndReturnDbParametersByDbParameters(parametersCollection, isReturnValueExists,
out DynamicParameters parameters, out IDbParameterList returnParameterList);
OpenConnectionForQueryExecution();
returnItem.DataItemList = ExecuteQuery <T>(sqlQuery, commandType, parameters);
CloseConnectionForQueryExecution();
SetReturnItemByReturnDbParameters(returnParameterList, parameters, isReturnValueExists, returnItem);
return(returnItem);
}
/// <summary>
/// Executes query stored procedure for a list - Async.
/// </summary>
/// <typeparam name="T">Type of the list of returned model</typeparam>
/// <param name="storedProcedureName">Name of the stored procedure</param>
/// <param name="parametersCollection">Input/Output parameter list</param>
/// <param name="isReturnValueExists">Indicates whether return value, needed to be included</param>
/// <returns>QueryReturnItem</returns>
public virtual async Task <QueryReturnItem <T> > ExecuteQueryStoredProcedureAsync <T>(string storedProcedureName, IDbParameterList parametersCollection, bool isReturnValueExists = true)
{
QueryReturnItem <T> returnItem = new QueryReturnItem <T>();
IDbParameterList returnParameterList = new DbParameterList();
DynamicParameters parameters = new DynamicParameters();
foreach (DbDataParameter parameter in parametersCollection)
{
parameters.Add(parameter.ParameterName, parameter.Value, parameter.DbType, parameter.Direction);
if (parameter.Direction == ParameterDirection.Output || parameter.Direction == ParameterDirection.InputOutput)
{
returnParameterList.Add(parameter);
}
}
if (isReturnValueExists)
{
parameters.Add(ReturnValueParameterName, dbType: DbType.Int32, direction: ParameterDirection.ReturnValue, size: ReturnValueSize);
}
OpenConnectionForSingleTransaction();
returnItem.DataItemList = await ExecuteQueryAsync <T>(storedProcedureName, CommandType.StoredProcedure, parameters);
CloseConnectionForSingleTransaction();
if (returnParameterList.Count > 0)
{
foreach (DbDataParameter parameter in returnParameterList)
{
parameter.Value = GetOutputParameterValue(parameter.ParameterName, parameter.DbType, parameters);
}
returnItem.ReturnParametersCollection = returnParameterList;
}
if (isReturnValueExists)
{
returnItem.ReturnValue = parameters.Get <int>(ReturnValueParameterName);
}
return(returnItem);
}