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