/// <summary>
/// Executes non-query sql text or stored procedure.
/// </summary>
/// <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>NonQueryReturnItem</returns>
protected virtual NonQueryReturnItem ExecuteNonQueryByCommandType(string sqlQuery, CommandType commandType, IDbParameterList parametersCollection, bool isReturnValueExists)
{
NonQueryReturnItem returnItem = new NonQueryReturnItem();
GetDynamicParametersAndReturnDbParametersByDbParameters(parametersCollection, isReturnValueExists,
out DynamicParameters parameters, out IDbParameterList returnParameterList);
OpenConnectionForQueryExecution();
returnItem.EffectedRowsCount = ExecuteNonQuery(sqlQuery, commandType, parameters);
CloseConnectionForQueryExecution();
SetReturnItemByReturnDbParameters(returnParameterList, parameters, isReturnValueExists, returnItem);
return(returnItem);
}
/// <summary>
/// Executes non-query stored procedures - Async.
/// </summary>
/// <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>NonQueryReturnItem</returns>
public virtual async Task <NonQueryReturnItem> ExecuteNonQueryStoredProcedureAsync(string storedProcedureName, IDbParameterList parametersCollection, bool isReturnValueExists = true)
{
NonQueryReturnItem returnItem = new NonQueryReturnItem();
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();
await ExecuteNonQueryAsync(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);
}
/// <summary>
/// Executes non-query sql text.
/// </summary>
/// <param name="sqlQueryText">SQL Query Text</param>
/// <param name="parametersCollection">Input/Output parameter list</param>
/// <param name="isReturnValueExists">Indicates whether return value, needed to be included</param>
/// <returns>NonQueryReturnItem</returns>
public virtual NonQueryReturnItem ExecuteNonQuerySqlText(string sqlQueryText, IDbParameterList parametersCollection, bool isReturnValueExists = false)
{
NonQueryReturnItem returnItem = new NonQueryReturnItem();
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();
ExecuteNonQuery(sqlQueryText, CommandType.Text, 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);
}