/// <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); }