/// <summary>
 /// Set Return Item By Return Db Parameters
 /// </summary>
 /// <param name="returnDbDataParameters">Output. Returning Db Parameter List</param>
 /// <param name="dynamicParameters">Output. Dynamic Parameters</param>
 /// <param name="isReturnValueExists">Return value required or not</param>
 /// <param name="returnItem">IReturnItem referenced object</param>
 protected void SetReturnItemByReturnDbParameters(IDbParameterList returnDbDataParameters, DynamicParameters dynamicParameters, bool isReturnValueExists, IReturnItem returnItem)
 {
     if (returnDbDataParameters.Count > 0)
     {
         foreach (DbDataParameter parameter in returnDbDataParameters)
         {
             parameter.Value = GetOutputParameterValue(parameter.ParameterName, parameter.DbType, dynamicParameters);
         }
         returnItem.ReturnParametersCollection = returnDbDataParameters;
     }
     if (isReturnValueExists)
     {
         returnItem.ReturnValue = dynamicParameters.Get <int>(ReturnValueParameterName);
     }
 }
        /// <summary>
        /// Get Dynamic Parameters And Return Db Parameters By DbParameters
        /// </summary>
        /// <param name="parametersCollection">Given DB parameters list</param>
        /// <param name="isReturnValueExists">Return value is required or not</param>
        /// <param name="dynamicParameters">Output. Dynamic Parameters</param>
        /// <param name="returnParameterCollection">Output. Return DB parameter list</param>
        protected void GetDynamicParametersAndReturnDbParametersByDbParameters(IDbParameterList parametersCollection,
                                                                               bool isReturnValueExists, out DynamicParameters dynamicParameters, out IDbParameterList returnParameterCollection)
        {
            returnParameterCollection = new DbParameterList();
            dynamicParameters         = new DynamicParameters();

            foreach (DbDataParameter parameter in parametersCollection)
            {
                dynamicParameters.Add(parameter.ParameterName, parameter.Value, parameter.DbType, parameter.Direction);

                if (parameter.Direction == ParameterDirection.Output || parameter.Direction == ParameterDirection.InputOutput)
                {
                    returnParameterCollection.Add(parameter);
                }
            }

            if (isReturnValueExists)
            {
                dynamicParameters.Add(ReturnValueParameterName, dbType: DbType.Int32, direction: ParameterDirection.ReturnValue, size: ReturnValueSize);
            }
        }
        /// <summary>
        /// Executes query sql text for 5 lists.
        /// </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="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, TThird, TForth, TFifth> ExecuteQueryMultipleSqlText <TFirst, TSecond, TThird, TForth, TFifth>(string sqlQueryText, IDbParameterList parametersCollection, bool isReturnValueExists = false)
        {
            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 = ExecuteQueryMultiple(sqlQueryText, CommandType.Text, 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 stored procedures for item with list.
 /// </summary>
 /// <typeparam name="TFirst">Type of first item</typeparam>
 /// <typeparam name="TSecond">Type of second 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 QueryMultipleSingleAndListReturnItem <TFirst, TSecond> ExecuteQueryMultipleSingleWithListStoredProcedure <TFirst, TSecond>(string storedProcedureName, IDbParameterList parametersCollection, bool isReturnValueExists = true) =>
 ExecuteQueryMultipleSingleWithListByCommandType <TFirst, TSecond>(storedProcedureName, CommandType.StoredProcedure, parametersCollection, isReturnValueExists);
 /// <summary>
 /// Executes query stored procedures for single data record.
 /// </summary>
 /// <typeparam name="T">Type of the 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>QuerySingleOrDefaultReturnItem</returns>
 public virtual QuerySingleOrDefaultReturnItem <T> ExecuteQuerySingleOrDefaultStoredProcedure <T>(string storedProcedureName, IDbParameterList parametersCollection, bool isReturnValueExists = true) =>
 ExecuteQuerySingleOrDefaultByCommandType <T>(storedProcedureName, CommandType.StoredProcedure, parametersCollection, isReturnValueExists);
        /// <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);
        }
        /// <summary>
        /// Executes query sql text or stored procedure for item with list - Async.
        /// </summary>
        /// <typeparam name="TFirst">Type of first item</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 async Task <QueryMultipleSingleAndListReturnItem <TFirst, TSecond> > ExecuteQueryMultipleSingleWithListByCommandTypeAsync <TFirst, TSecond>(string sqlQuery, CommandType commandType, IDbParameterList parametersCollection, bool isReturnValueExists)
        {
            QueryMultipleSingleAndListReturnItem <TFirst, TSecond> returnItem = new QueryMultipleSingleAndListReturnItem <TFirst, TSecond>();

            GetDynamicParametersAndReturnDbParametersByDbParameters(parametersCollection, isReturnValueExists,
                                                                    out DynamicParameters parameters, out IDbParameterList returnParameterList);


            OpenConnectionForQueryExecution();
            using (SqlMapper.GridReader gridReader = await ExecuteQueryMultipleAsync(sqlQuery, commandType, parameters))
            {
                returnItem.FirstItem        = gridReader.ReadSingleOrDefault <TFirst>();
                returnItem.SecondCollection = gridReader.Read <TSecond>();
            }
            CloseConnectionForQueryExecution();

            SetReturnItemByReturnDbParameters(returnParameterList, parameters, isReturnValueExists, returnItem);

            return(returnItem);
        }
Example #8
0
 /// <summary>
 /// Initialize to default values
 /// </summary>
 public NonQueryReturnItem()
 {
     ReturnParametersCollection = new DbParameterList();
 }
        /// <summary>
        /// Executes query sql text or stored procedure for 3 lists.
        /// </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>
        /// <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, TThird> ExecuteQueryMultipleByCommandType <TFirst, TSecond, TThird>(string sqlQuery, CommandType commandType, IDbParameterList parametersCollection, bool isReturnValueExists)
        {
            QueryMultipleListsReturnItem <TFirst, TSecond, TThird> returnItem = new QueryMultipleListsReturnItem <TFirst, TSecond, TThird>();

            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>();
                returnItem.ThirdCollection  = gridReader.Read <TThird>();
            }
            CloseConnectionForQueryExecution();

            SetReturnItemByReturnDbParameters(returnParameterList, parameters, isReturnValueExists, returnItem);

            return(returnItem);
        }
        /// <summary>
        /// Executes query stored procedures 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 nineth list</typeparam>
        /// <typeparam name="TTenth">Type of tenth 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, TSixth, TSeventh, TEighth, TNineth, TTenth> > ExecuteQueryMultipleStoredProcedureAsync <TFirst, TSecond, TThird, TForth, TFifth, TSixth, TSeventh, TEighth, TNineth, TTenth>(string storedProcedureName, IDbParameterList parametersCollection, bool isReturnValueExists = true)
        {
            QueryMultipleListsReturnItem <TFirst, TSecond, TThird, TForth, TFifth, TSixth, TSeventh, TEighth, TNineth, TTenth> returnItem = new QueryMultipleListsReturnItem <TFirst, TSecond, TThird, TForth, TFifth, TSixth, TSeventh, TEighth, TNineth, TTenth>();
            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>();
                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>();
            }

            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 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);
        }
        /// <summary>
        /// Executes query stored procedures for multiple datasets - 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>QueryMultipleReturnItem</returns>
        public virtual async Task <QueryMultipleReturnItem> ExecuteQueryMultipleStoredProcedureAsync(string storedProcedureName, IDbParameterList parametersCollection, bool isReturnValueExists = true)
        {
            QueryMultipleReturnItem returnItem          = new QueryMultipleReturnItem();
            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);
            }

            IList <dynamic> returnedLists = new List <dynamic>();

            OpenConnectionForSingleTransaction();

            using (GridReader gridReader = await ExecuteQueryMultipleAsync(storedProcedureName, CommandType.StoredProcedure, parameters))
            {
                while (!gridReader.IsConsumed)
                {
                    returnedLists.Add(gridReader.Read());
                }
            }

            CloseConnectionForSingleTransaction();

            returnItem.DataLists = returnedLists;

            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);
        }
Example #13
0
 /// <summary>
 /// Executes query sql text for 3 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>
 /// <param name="sqlQueryText">SQL Query Text</param>
 /// <param name="parametersCollection">Input parameter list</param>
 /// <returns>QueryMultipleListsReturnItem</returns>
 public virtual async Task <QueryMultipleListsReturnItem <TFirst, TSecond, TThird> > ExecuteQueryMultipleSqlTextAsync <TFirst, TSecond, TThird>(string sqlQueryText, IDbParameterList parametersCollection) =>
 await ExecuteQueryMultipleByCommandTypeAsync <TFirst, TSecond, TThird>(sqlQueryText, CommandType.Text, parametersCollection, false);
Example #14
0
 /// <summary>
 /// Executes query sql text for multiple datasets - Async.
 /// </summary>
 /// <param name="sqlQueryText">SQL Query Text</param>
 /// <param name="parametersCollection">Input parameter list</param>
 /// <returns>QueryMultipleReturnItem</returns>
 public virtual async Task <QueryMultipleReturnItem> ExecuteQueryMultipleSqlTextAsync(string sqlQueryText, IDbParameterList parametersCollection) =>
 await ExecuteQueryMultipleByCommandTypeAsync(sqlQueryText, CommandType.Text, parametersCollection, false);
Example #15
0
 /// <summary>
 /// Executes query sql text for single data record - Async.
 /// </summary>
 /// <typeparam name="T">Type of the returned model</typeparam>
 /// <param name="sqlQueryText">SQL Query Text</param>
 /// <param name="parametersCollection">Input parameter list</param>
 /// <returns>QuerySingleOrDefaultReturnItem</returns>
 public virtual async Task <QuerySingleOrDefaultReturnItem <T> > ExecuteQuerySingleOrDefaultSqlTextAsync <T>(string sqlQueryText, IDbParameterList parametersCollection) =>
 await ExecuteQuerySingleOrDefaultByCommandTypeAsync <T>(sqlQueryText, CommandType.Text, parametersCollection, false);
 /// <summary>
 /// Executes query sql text for item with list.
 /// </summary>
 /// <typeparam name="TFirst">Type of first item</typeparam>
 /// <typeparam name="TSecond">Type of second list</typeparam>
 /// <param name="sqlQueryText">SQL Query Text</param>
 /// <param name="parametersCollection">Input parameter list</param>
 /// <returns>QueryMultipleListsReturnItem</returns>
 public virtual QueryMultipleSingleAndListReturnItem <TFirst, TSecond> ExecuteQueryMultipleSingleWithListSqlText <TFirst, TSecond>(string sqlQueryText, IDbParameterList parametersCollection) =>
 ExecuteQueryMultipleSingleWithListByCommandType <TFirst, TSecond>(sqlQueryText, CommandType.Text, parametersCollection, false);
 /// <summary>
 /// Executes query sql text for 4 lists.
 /// </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>
 /// <param name="sqlQueryText">SQL Query Text</param>
 /// <param name="parametersCollection">Input parameter list</param>
 /// <returns>QueryMultipleListsReturnItem</returns>
 public virtual QueryMultipleListsReturnItem <TFirst, TSecond, TThird, TForth> ExecuteQueryMultipleSqlText <TFirst, TSecond, TThird, TForth>(string sqlQueryText, IDbParameterList parametersCollection) =>
 ExecuteQueryMultipleByCommandType <TFirst, TSecond, TThird, TForth>(sqlQueryText, CommandType.Text, parametersCollection, false);
        /// <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 sql text or stored procedure for multiple datasets - Async.
        /// </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>QueryMultipleReturnItem</returns>
        protected virtual async Task <QueryMultipleReturnItem> ExecuteQueryMultipleByCommandTypeAsync(string sqlQuery, CommandType commandType, IDbParameterList parametersCollection, bool isReturnValueExists)
        {
            QueryMultipleReturnItem returnItem = new QueryMultipleReturnItem();

            GetDynamicParametersAndReturnDbParametersByDbParameters(parametersCollection, isReturnValueExists,
                                                                    out DynamicParameters parameters, out IDbParameterList returnParameterList);

            IList <dynamic> returnedLists = new List <dynamic>();

            OpenConnectionForQueryExecution();
            using (SqlMapper.GridReader gridReader = await ExecuteQueryMultipleAsync(sqlQuery, commandType, parameters))
            {
                while (!gridReader.IsConsumed)
                {
                    returnedLists.Add(gridReader.Read());
                }
            }
            CloseConnectionForQueryExecution();

            returnItem.DataLists = returnedLists;

            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) =>
 await ExecuteQueryByCommandTypeAsync <T>(storedProcedureName, CommandType.StoredProcedure, parametersCollection, isReturnValueExists);
        /// <summary>
        /// Executes non-query sql text or stored procedure - Async.
        /// </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 async Task <NonQueryReturnItem> ExecuteNonQueryByCommandTypeAsync(string sqlQuery, CommandType commandType, IDbParameterList parametersCollection, bool isReturnValueExists)
        {
            NonQueryReturnItem returnItem = new NonQueryReturnItem();

            GetDynamicParametersAndReturnDbParametersByDbParameters(parametersCollection, isReturnValueExists,
                                                                    out DynamicParameters parameters, out IDbParameterList returnParameterList);


            OpenConnectionForQueryExecution();
            returnItem.EffectedRowsCount = await ExecuteNonQueryAsync(sqlQuery, commandType, parameters);

            CloseConnectionForQueryExecution();

            SetReturnItemByReturnDbParameters(returnParameterList, parameters, isReturnValueExists, returnItem);

            return(returnItem);
        }
 /// <summary>
 /// Executes query stored procedures for 3 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>
 /// <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> > ExecuteQueryMultipleStoredProcedureAsync <TFirst, TSecond, TThird>(string storedProcedureName, IDbParameterList parametersCollection, bool isReturnValueExists = true) =>
 await ExecuteQueryMultipleByCommandTypeAsync <TFirst, TSecond, TThird>(storedProcedureName, CommandType.StoredProcedure, parametersCollection, isReturnValueExists);
        /// <summary>
        /// Executes query sql text or stored procedure for single data record - Async.
        /// </summary>
        /// <typeparam name="T">Type of the 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>QuerySingleOrDefaultReturnItem</returns>
        protected virtual async Task <QuerySingleOrDefaultReturnItem <T> > ExecuteQuerySingleOrDefaultByCommandTypeAsync <T>(string sqlQuery, CommandType commandType, IDbParameterList parametersCollection, bool isReturnValueExists)
        {
            QuerySingleOrDefaultReturnItem <T> returnItem = new QuerySingleOrDefaultReturnItem <T>();

            GetDynamicParametersAndReturnDbParametersByDbParameters(parametersCollection, isReturnValueExists,
                                                                    out DynamicParameters parameters, out IDbParameterList returnParameterList);

            OpenConnectionForQueryExecution();
            returnItem.DataItem = await ExecuteSingleOrDefaultQueryAsync <T>(sqlQuery, commandType, parameters);

            CloseConnectionForQueryExecution();

            SetReturnItemByReturnDbParameters(returnParameterList, parameters, isReturnValueExists, returnItem);

            return(returnItem);
        }
 /// <summary>
 /// Executes scalar sql text.
 /// </summary>
 /// <param name="sqlQueryText">SQL Query Text</param>
 /// <param name="parametersCollection">Input parameter list</param>
 /// <returns>NonQueryReturnItem</returns>
 public virtual QueryScalarReturnItem <T> ExecuteScalarSqlText <T>(string sqlQueryText, IDbParameterList parametersCollection) =>
 ExecuteScalarByCommandType <T>(sqlQueryText, CommandType.Text, parametersCollection, false);
 /// <summary>
 /// Executes query stored procedures for multiple datasets.
 /// </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>QueryMultipleReturnItem</returns>
 public virtual QueryMultipleReturnItem ExecuteQueryMultipleStoredProcedure(string storedProcedureName, IDbParameterList parametersCollection, bool isReturnValueExists = true) =>
 ExecuteQueryMultipleByCommandType(storedProcedureName, CommandType.StoredProcedure, parametersCollection, isReturnValueExists);
 /// <summary>
 /// Executes non-query sql text.
 /// </summary>
 /// <param name="sqlQueryText">SQL Query Text</param>
 /// <param name="parametersCollection">Input parameter list</param>
 /// <returns>NonQueryReturnItem</returns>
 public virtual NonQueryReturnItem ExecuteNonQuerySqlText(string sqlQueryText, IDbParameterList parametersCollection) =>
 ExecuteNonQueryByCommandType(sqlQueryText, CommandType.Text, parametersCollection, false);
 /// <summary>
 /// Executes query stored procedures for 4 lists.
 /// </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>
 /// <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 QueryMultipleListsReturnItem <TFirst, TSecond, TThird, TForth> ExecuteQueryMultipleStoredProcedure <TFirst, TSecond, TThird, TForth>(string storedProcedureName, IDbParameterList parametersCollection, bool isReturnValueExists = true) =>
 ExecuteQueryMultipleByCommandType <TFirst, TSecond, TThird, TForth>(storedProcedureName, CommandType.StoredProcedure, parametersCollection, isReturnValueExists);
 /// <summary>
 /// Executes query sql text for single data record.
 /// </summary>
 /// <typeparam name="T">Type of the returned model</typeparam>
 /// <param name="sqlQueryText">SQL Query Text</param>
 /// <param name="parametersCollection">Input parameter list</param>
 /// <returns>QuerySingleOrDefaultReturnItem</returns>
 public virtual QuerySingleOrDefaultReturnItem <T> ExecuteQuerySingleOrDefaultSqlText <T>(string sqlQueryText, IDbParameterList parametersCollection) =>
 ExecuteQuerySingleOrDefaultByCommandType <T>(sqlQueryText, CommandType.Text, parametersCollection, false);
 /// <summary>
 /// Executes query sql text for multiple datasets.
 /// </summary>
 /// <param name="sqlQueryText">SQL Query Text</param>
 /// <param name="parametersCollection">Input parameter list</param>
 /// <returns>QueryMultipleReturnItem</returns>
 public virtual QueryMultipleReturnItem ExecuteQueryMultipleSqlText(string sqlQueryText, IDbParameterList parametersCollection) =>
 ExecuteQueryMultipleByCommandType(sqlQueryText, CommandType.Text, parametersCollection, false);
        /// <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);
        }