public static IDataReader ExecuteReader(this IDbConnection mConn,
                                                string sql, CommandType cmdType        = CommandType.Text,
                                                IDbTransaction mTrans                  = null,
                                                Dictionary <string, object> inputArgs  = null,
                                                Dictionary <string, object> outputArgs = null)
        {
            IDataReader reader = null;

            try
            {
                using (IDbCommand cmd = mConn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.CommandType = cmdType;

                    if (mTrans != null)
                    {
                        cmd.Transaction = mTrans;
                    }

                    DbCommandHelper.SetCommandParameters(cmd, inputArgs, outputArgs);

                    reader = cmd.ExecuteReader();

                    outputArgs = (Dictionary <string, object>)DbCommandHelper.GetOutParametersOfCommand(cmd);
                }
            }
            catch (Exception e)
            {
                throw;
            }

            return(reader);
        }
        public static DataSet GetResultSet(this IDbConnection mConn,
                                           string sql, CommandType cmdType        = CommandType.Text,
                                           IDbTransaction mTrans                  = null,
                                           Dictionary <string, object> inputArgs  = null,
                                           Dictionary <string, object> outputArgs = null)
        {
            DataSet ds = null;

            try
            {
                Type adapterTyp   = null;
                var  adapterTypes =
                    mConn.GetType().Assembly.GetExportedTypes().Where(
                        typ => typ.IsClass && typ.GetInterfaces().Contains(typeof(IDbDataAdapter)) &&
                        typ.IsAbstract == false && typeof(IDbDataAdapter).IsAssignableFrom(typ));

                if (adapterTypes.Count() > 1)
                {
                    var connTypeName = mConn.GetType().Name;
                    connTypeName = connTypeName.Substring(0, connTypeName.Length - 10).ToLower();
                    adapterTyp   = adapterTypes.Where(typ => typ.Name.ToLower().StartsWith(connTypeName)).First();
                }
                else
                {
                    adapterTyp = adapterTypes.First();
                }

                if (adapterTyp != null)
                {
                    IDbDataAdapter d = null;
                    d = Activator.CreateInstance(adapterTyp) as IDbDataAdapter;

                    using (IDbCommand cmd = mConn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        cmd.CommandType = cmdType;

                        if (mTrans != null)
                        {
                            cmd.Transaction = mTrans;
                        }

                        DbCommandHelper.SetCommandParameters(cmd, inputArgs, outputArgs);

                        d.SelectCommand = cmd;
                        ds = new DataSet();
                        var result = d.Fill(ds);

                        outputArgs = (Dictionary <string, object>)DbCommandHelper.GetOutParametersOfCommand(cmd);
                    }
                }
            }
            catch (Exception e)
            {
                throw;
            }

            return(ds);
        }
        public static List <ExpandoObject> GetDynamicResultSetWithPaging(this IDbConnection mConn,
                                                                         string sql, CommandType cmdType,
                                                                         IDbTransaction mTrans = null,
                                                                         Dictionary <string, object> inputArgs  = null,
                                                                         Dictionary <string, object> outputArgs = null,
                                                                         uint pageNumber = 1, uint pageItemCount = 10)
        {
            List <ExpandoObject> list   = new List <ExpandoObject>();
            IDataReader          reader = null;

            try
            {
                using (IDbCommand cmd = mConn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.CommandType = cmdType;

                    if (mTrans != null)
                    {
                        cmd.Transaction = mTrans;
                    }

                    DbCommandHelper.SetCommandParameters(cmd, inputArgs, outputArgs);

                    reader = cmd.ExecuteReader();

                    outputArgs = (Dictionary <string, object>)DbCommandHelper.GetOutParametersOfCommand(cmd);

                    list = reader.GetDynamicResultSetWithPaging(pageNumber: pageNumber, pageItemCount: pageItemCount, closeAtFinal: false);
                }
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
            }

            return(list);
        }
        public static List <List <ExpandoObject> > GetMultiDynamicResultSet(this IDbConnection mConn,
                                                                            string sql, CommandType cmdType        = CommandType.Text,
                                                                            IDbTransaction mTrans                  = null,
                                                                            Dictionary <string, object> inputArgs  = null,
                                                                            Dictionary <string, object> outputArgs = null)
        {
            List <List <ExpandoObject> > list = new List <List <ExpandoObject> >();
            IDataReader reader = null;

            try
            {
                using (IDbCommand cmd = mConn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.CommandType = cmdType;

                    if (mTrans != null)
                    {
                        cmd.Transaction = mTrans;
                    }

                    DbCommandHelper.SetCommandParameters(cmd, inputArgs, outputArgs);

                    reader = cmd.ExecuteReader();

                    outputArgs = (Dictionary <string, object>)DbCommandHelper.GetOutParametersOfCommand(cmd);

                    list = reader.GetMultiDynamicResultSet();
                }
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
            }

            return(list);
        }