/// <summary>
/// Executes query stored procedures for 5 lists - Async.
/// </summary>
/// <typeparam name="TFirst">Type of first list</typeparam>
/// <typeparam name="TSecond">Type of second list</typeparam>
/// <typeparam name="TThird">Type of third list</typeparam>
/// <typeparam name="TForth">Type of forth list</typeparam>
/// <typeparam name="TFifth">Type of fifth list</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>QueryMultipleListsReturnItem</returns>
public virtual async Task <QueryMultipleListsReturnItem <TFirst, TSecond, TThird, TForth, TFifth> > ExecuteQueryMultipleStoredProcedureAsync <TFirst, TSecond, TThird, TForth, TFifth>(string storedProcedureName, IDbParameterList parametersCollection, bool isReturnValueExists = true)
{
QueryMultipleListsReturnItem <TFirst, TSecond, TThird, TForth, TFifth> returnItem = new QueryMultipleListsReturnItem <TFirst, TSecond, TThird, TForth, TFifth>();
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();
using (GridReader gridReader = await ExecuteQueryMultipleAsync(storedProcedureName, CommandType.StoredProcedure, parameters))
{
returnItem.FirstCollection = gridReader.Read <TFirst>();
returnItem.SecondCollection = gridReader.Read <TSecond>();
returnItem.ThirdCollection = gridReader.Read <TThird>();
returnItem.FourthCollection = gridReader.Read <TForth>();
returnItem.FifthCollection = gridReader.Read <TFifth>();
}
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 query sql text for 2 lists.
/// </summary>
/// <typeparam name="TFirst">Type of first list</typeparam>
/// <typeparam name="TSecond">Type of second list</typeparam>
/// <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>QueryMultipleListsReturnItem</returns>
public virtual QueryMultipleListsReturnItem <TFirst, TSecond> ExecuteQueryMultipleSqlText <TFirst, TSecond>(string sqlQueryText, IDbParameterList parametersCollection, bool isReturnValueExists = false)
{
QueryMultipleListsReturnItem <TFirst, TSecond> returnItem = new QueryMultipleListsReturnItem <TFirst, TSecond>();
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();
using (GridReader gridReader = ExecuteQueryMultiple(sqlQueryText, CommandType.Text, parameters))
{
returnItem.FirstCollection = gridReader.Read <TFirst>();
returnItem.SecondCollection = gridReader.Read <TSecond>();
}
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 query sql text or stored procedure for 2 lists.
/// </summary>
/// <typeparam name="TFirst">Type of first list</typeparam>
/// <typeparam name="TSecond">Type of second list</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>QueryMultipleListsReturnItem</returns>
protected virtual QueryMultipleListsReturnItem <TFirst, TSecond> ExecuteQueryMultipleByCommandType <TFirst, TSecond>(string sqlQuery, CommandType commandType, IDbParameterList parametersCollection, bool isReturnValueExists)
{
QueryMultipleListsReturnItem <TFirst, TSecond> returnItem = new QueryMultipleListsReturnItem <TFirst, TSecond>();
GetDynamicParametersAndReturnDbParametersByDbParameters(parametersCollection, isReturnValueExists,
out DynamicParameters parameters, out IDbParameterList returnParameterList);
OpenConnectionForQueryExecution();
using (SqlMapper.GridReader gridReader = ExecuteQueryMultiple(sqlQuery, commandType, parameters))
{
returnItem.FirstCollection = gridReader.Read <TFirst>();
returnItem.SecondCollection = gridReader.Read <TSecond>();
}
CloseConnectionForQueryExecution();
SetReturnItemByReturnDbParameters(returnParameterList, parameters, isReturnValueExists, returnItem);
return(returnItem);
}
static void Main(string[] args)
{
IDbConnector dbConnector = new SqlServerConnector(@"Data Source=CO-IT-CHATHURAN\CSBUNLIMITED; Initial Catalog=Eclipse; User id=sa; Password=5556;");
IDbParameterList dataParameters = new DbParameterList()
{
new DbDataParameter("@Id", 10, dbType: System.Data.DbType.Int32),
new DbDataParameter("@OT", 10, dbType: System.Data.DbType.Int32, direction: System.Data.ParameterDirection.Output)
};
QueryMultipleListsReturnItem <dynamic, dynamic, dynamic> t = dbConnector.ExecuteQueryMultipleStoredProcedure <dynamic, dynamic, dynamic>("[usm].[Test_DapperWrapper]", dataParameters);
var d = dbConnector.ExecuteQueryMultipleStoredProcedure("[usm].[Test_DapperWrapper]", dataParameters);
IDbParameterList dataParameters2 = new DbParameterList()
{
new DbDataParameter("@BaseAgentId", 5, dbType: System.Data.DbType.Int32)
};
var x = dbConnector.ExecuteQueryMultipleSqlText <dynamic, dynamic>(
$"SELECT * FROM usm.ApplicationUser ; Select* from usm.Agent where BaseAgentId = @BaseAgentId;",
dataParameters2);
}
/// <summary>
/// Executes query sql text or stored procedure for 10 lists - Async.
/// </summary>
/// <typeparam name="TFirst">Type of first list</typeparam>
/// <typeparam name="TSecond">Type of second list</typeparam>
/// <typeparam name="TThird">Type of third list</typeparam>
/// <typeparam name="TForth">Type of forth list</typeparam>
/// <typeparam name="TFifth">Type of fifth list</typeparam>
/// <typeparam name="TSixth">Type of sixth list</typeparam>
/// <typeparam name="TSeventh">Type of seventh list</typeparam>
/// <typeparam name="TEighth">Type of eighth list</typeparam>
/// <typeparam name="TNineth">Type of ninth list</typeparam>
/// <typeparam name="TTenth">Type of tenth list</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>QueryMultipleListsReturnItem</returns>
protected virtual async Task <QueryMultipleListsReturnItem <TFirst, TSecond, TThird, TForth, TFifth, TSixth, TSeventh, TEighth, TNineth, TTenth> > ExecuteQueryMultipleByCommandTypeAsync <TFirst, TSecond, TThird, TForth, TFifth, TSixth, TSeventh, TEighth, TNineth, TTenth>(string sqlQuery, CommandType commandType, IDbParameterList parametersCollection, bool isReturnValueExists)
{
QueryMultipleListsReturnItem <TFirst, TSecond, TThird, TForth, TFifth, TSixth, TSeventh, TEighth, TNineth, TTenth> returnItem = new QueryMultipleListsReturnItem <TFirst, TSecond, TThird, TForth, TFifth, TSixth, TSeventh, TEighth, TNineth, TTenth>();
GetDynamicParametersAndReturnDbParametersByDbParameters(parametersCollection, isReturnValueExists,
out DynamicParameters parameters, out IDbParameterList returnParameterList);
OpenConnectionForQueryExecution();
using (SqlMapper.GridReader gridReader = await ExecuteQueryMultipleAsync(sqlQuery, commandType, parameters))
{
returnItem.FirstCollection = gridReader.Read <TFirst>();
returnItem.SecondCollection = gridReader.Read <TSecond>();
returnItem.ThirdCollection = gridReader.Read <TThird>();
returnItem.FourthCollection = gridReader.Read <TForth>();
returnItem.FifthCollection = gridReader.Read <TFifth>();
returnItem.SixthCollection = gridReader.Read <TSixth>();
returnItem.SeventhCollection = gridReader.Read <TSeventh>();
returnItem.EighthCollection = gridReader.Read <TEighth>();
returnItem.NinethCollection = gridReader.Read <TNineth>();
returnItem.TenthCollection = gridReader.Read <TTenth>();
}
CloseConnectionForQueryExecution();
SetReturnItemByReturnDbParameters(returnParameterList, parameters, isReturnValueExists, returnItem);
return(returnItem);
}
