////////////////////////////////////////////////////////////////////////////////////////////////////
        /// <summary>   An IDbConnection extension method that executes the reader operation. </summary>
        ///
        /// <remarks>   Msacli, 22.04.2019. </remarks>
        ///
        /// <param name="connection">   The connection to act on. </param>
        /// <param name="sql">          The SQL. </param>
        /// <param name="commandType">  (Optional) Type of the command. </param>
        /// <param name="transaction">  (Optional) The transaction. </param>
        /// <param name="parameters">   A variable-length parameters list containing parameters. </param>
        ///
        /// <returns>   An IDataReader. </returns>
        ////////////////////////////////////////////////////////////////////////////////////////////////////
        public static IDataReader ExecuteReader(this IDbConnection connection,
                                                string sql, CommandType commandType = CommandType.Text,
                                                IDbTransaction transaction          = null,
                                                params object[] parameters)
        {
            IDataReader reader = null;

            try
            {
                using (IDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = sql;
                    command.CommandType = commandType;

                    if (transaction != null)
                    {
                        command.Transaction = transaction;
                    }

                    DxDbCommandHelper.SetCommandParameters(command, parameters);

                    reader = command.ExecuteReader();
                }
            }
            catch (Exception e)
            {
                throw;
            }

            return(reader);
        }
        ////////////////////////////////////////////////////////////////////////////////////////////////////
        /// <summary>   An IDbConnection extension method that executes. </summary>
        ///
        /// <remarks>   Msacli, 22.04.2019. </remarks>
        ///
        /// <param name="connection">   The connection to act on. </param>
        /// <param name="sql">          The SQL. </param>
        /// <param name="commandType">  (Optional) Type of the command. </param>
        /// <param name="transaction">  (Optional) The transaction. </param>
        /// <param name="parameters">   A variable-length parameters list containing parameters. </param>
        ///
        /// <returns>   An int. </returns>
        ////////////////////////////////////////////////////////////////////////////////////////////////////
        public static int Execute(this IDbConnection connection,
                                  string sql,
                                  CommandType commandType    = CommandType.Text,
                                  IDbTransaction transaction = null,
                                  params object[] parameters)
        {
            int res = 0;

            try
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = sql;
                    command.CommandType = commandType;

                    if (transaction != null)
                    {
                        command.Transaction = transaction;
                    }

                    DxDbCommandHelper.SetCommandParameters(command, parameters);

                    res = command.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                res = -1;
                throw;
            }

            return(res);
        }
        ////////////////////////////////////////////////////////////////////////////////////////////////////
        /// <summary>   An IDbConnection extension method that gets result set. </summary>
        ///
        /// <remarks>   Msacli, 22.04.2019. </remarks>
        ///
        /// <param name="connection">   The connection to act on. </param>
        /// <param name="sql">          The SQL. </param>
        /// <param name="commandType">  (Optional) Type of the command. </param>
        /// <param name="transaction">  (Optional) The transaction. </param>
        /// <param name="parameters">   A variable-length parameters list containing parameters. </param>
        ///
        /// <returns>   The result set. </returns>
        ////////////////////////////////////////////////////////////////////////////////////////////////////
        public static DataSet GetResultSet(this IDbConnection connection,
                                           string sql, CommandType commandType = CommandType.Text,
                                           IDbTransaction transaction          = null,
                                           params object[] parameters)
        {
            DataSet dataset = null;

            try
            {
                Type adapterType = null;

                var adapterTypes =
                    connection.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 = connection.GetType().Name;
                    connTypeName = connTypeName.Substring(0, connTypeName.Length - 10).ToLower();
                    adapterType  = adapterTypes.Where(typ => typ.Name.ToLower().StartsWith(connTypeName)).First();
                }
                else
                {
                    adapterType = adapterTypes.First();
                }

                if (adapterType != null)
                {
                    var dataAdapter = Activator.CreateInstance(adapterType) as IDbDataAdapter;

                    using (IDbCommand command = connection.CreateCommand())
                    {
                        command.CommandText = sql;
                        command.CommandType = commandType;

                        if (transaction != null)
                        {
                            command.Transaction = transaction;
                        }

                        DxDbCommandHelper.SetCommandParameters(command, parameters);

                        dataAdapter.SelectCommand = command;
                        dataset = new DataSet();
                        var result = dataAdapter.Fill(dataset);
                    }
                }
            }
            catch (Exception e)
            {
                throw;
            }

            return(dataset);
        }
コード例 #4
0
        ////////////////////////////////////////////////////////////////////////////////////////////////////
        /// <summary>
        /// An IDbConnection extension method that gets dynamic result set with paging.
        /// </summary>
        ///
        /// <remarks>   Msacli, 22.04.2019. </remarks>
        ///
        /// <param name="connection">       The connection to act on. </param>
        /// <param name="sql">              The SQL. </param>
        /// <param name="commandType">      Type of the command. </param>
        /// <param name="transaction">      (Optional) The transaction. </param>
        /// <param name="inputParameters">  (Optional) Options for controlling the input. </param>
        /// <param name="outputParameters"> (Optional) Options for controlling the output. </param>
        /// <param name="pageNumber">       (Optional) The page number. </param>
        /// <param name="pageItemCount">    (Optional) Number of page ıtems. </param>
        ///
        /// <returns>   The dynamic result set with paging. </returns>
        ////////////////////////////////////////////////////////////////////////////////////////////////////
        public static List <ExpandoObject> GetDynamicResultSetWithPaging(this IDbConnection connection,
                                                                         string sql, CommandType commandType,
                                                                         IDbTransaction transaction = null,
                                                                         Dictionary <string, object> inputParameters  = null,
                                                                         Dictionary <string, object> outputParameters = null,
                                                                         uint pageNumber = 1, uint pageItemCount = 10)
        {
            List <ExpandoObject> list = new List <ExpandoObject>();

            using (IDbCommand command = connection.CreateCommand())
            {
                command.CommandText = sql;
                command.CommandType = commandType;

                if (transaction != null)
                {
                    command.Transaction = transaction;
                }

                DxDbCommandHelper.SetCommandParameters(command, inputParameters, outputParameters);

                using (var reader = command.ExecuteReader())
                {
                    try
                    {
                        if (outputParameters != null && outputParameters.Count > 0)
                        {
                            outputParameters = (Dictionary <string, object>)DxDbCommandHelper.GetOutParametersOfCommand(command);
                        }

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

            return(list);
        }
コード例 #5
0
        ////////////////////////////////////////////////////////////////////////////////////////////////////
        /// <summary>   An IDbConnection extension method that executes the reader operation. </summary>
        ///
        /// <remarks>   Msacli, 22.04.2019. </remarks>
        ///
        /// <param name="connection">       The connection to act on. </param>
        /// <param name="sql">              The SQL. </param>
        /// <param name="commandType">      (Optional) Type of the command. </param>
        /// <param name="transaction">      (Optional) The transaction. </param>
        /// <param name="inputParameters">  (Optional) Options for controlling the input. </param>
        /// <param name="outputParameters"> (Optional) Options for controlling the output. </param>
        ///
        /// <returns>   An IDataReader. </returns>
        ////////////////////////////////////////////////////////////////////////////////////////////////////
        public static IDataReader ExecuteReader(this IDbConnection connection,
                                                string sql, CommandType commandType          = CommandType.Text,
                                                IDbTransaction transaction                   = null,
                                                Dictionary <string, object> inputParameters  = null,
                                                Dictionary <string, object> outputParameters = null, CommandBehavior?commandBehavior = null)
        {
            IDataReader dataReader = null;

            try
            {
                using (IDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = sql;
                    command.CommandType = commandType;

                    if (transaction != null)
                    {
                        command.Transaction = transaction;
                    }

                    DxDbCommandHelper.SetCommandParameters(command, inputParameters, outputParameters);

                    if (!commandBehavior.HasValue)
                    {
                        dataReader = command.ExecuteReader();
                    }
                    else
                    {
                        dataReader = command.ExecuteReader(commandBehavior.Value);
                    }

                    if (outputParameters != null && outputParameters.Count > 0)
                    {
                        outputParameters = (Dictionary <string, object>)DxDbCommandHelper.GetOutParametersOfCommand(command);
                    }
                }
            }
            catch (Exception e)
            {
                throw;
            }

            return(dataReader);
        }
コード例 #6
0
        ////////////////////////////////////////////////////////////////////////////////////////////////////
        /// <summary>   An IDbConnection extension method that gets multi dynamic result set. </summary>
        ///
        /// <remarks>   Msacli, 22.04.2019. </remarks>
        ///
        /// <param name="connection">       The connection to act on. </param>
        /// <param name="sql">              The SQL. </param>
        /// <param name="commandType">      (Optional) Type of the command. </param>
        /// <param name="transaction">      (Optional) The transaction. </param>
        /// <param name="inputParameters">  (Optional) Options for controlling the input. </param>
        /// <param name="outputParameters"> (Optional) Options for controlling the output. </param>
        ///
        /// <returns>   The multi dynamic result set. </returns>
        ////////////////////////////////////////////////////////////////////////////////////////////////////
        public static List <List <ExpandoObject> > GetMultiDynamicResultSet(this IDbConnection connection,
                                                                            string sql, CommandType commandType          = CommandType.Text,
                                                                            IDbTransaction transaction                   = null,
                                                                            Dictionary <string, object> inputParameters  = null,
                                                                            Dictionary <string, object> outputParameters = null)
        {
            var list = new List <List <ExpandoObject> >();

            using (IDbCommand command = connection.CreateCommand())
            {
                command.CommandText = sql;
                command.CommandType = commandType;

                if (transaction != null)
                {
                    command.Transaction = transaction;
                }

                DxDbCommandHelper.SetCommandParameters(command, inputParameters, outputParameters);

                using (var reader = command.ExecuteReader())
                {
                    try
                    {
                        if (outputParameters != null && outputParameters.Count > 0)
                        {
                            outputParameters = (Dictionary <string, object>)DxDbCommandHelper.GetOutParametersOfCommand(command);
                        }

                        list = reader.GetMultiDynamicResultSet(closeAtFinal: true);
                    }
                    catch (Exception e)
                    {
                        throw;
                    }
                    finally
                    {
                        reader.CloseIfNot();
                    }
                }
            }

            return(list);
        }
        ////////////////////////////////////////////////////////////////////////////////////////////////////
        /// <summary>   An IDbConnection extension method that gets multi dynamic result set. </summary>
        ///
        /// <remarks>   Msacli, 22.04.2019. </remarks>
        ///
        /// <param name="connection">   The connection to act on. </param>
        /// <param name="sql">          The SQL. </param>
        /// <param name="commandType">  (Optional) Type of the command. </param>
        /// <param name="transaction">  (Optional) The transaction. </param>
        /// <param name="parameters">   A variable-length parameters list containing parameters. </param>
        ///
        /// <returns>   The multi dynamic result set. </returns>
        ////////////////////////////////////////////////////////////////////////////////////////////////////
        public static List <List <ExpandoObject> > GetMultiDynamicResultSet(this IDbConnection connection,
                                                                            string sql, CommandType commandType = CommandType.Text,
                                                                            IDbTransaction transaction          = null,
                                                                            params object[] parameters)
        {
            var list = new List <List <ExpandoObject> >();

            using (IDbCommand command = connection.CreateCommand())
            {
                command.CommandText = sql;
                command.CommandType = commandType;

                if (transaction != null)
                {
                    command.Transaction = transaction;
                }

                DxDbCommandHelper.SetCommandParameters(command, parameters);

                using (var reader = command.ExecuteReader())
                {
                    try
                    {
                        list = reader.GetMultiDynamicResultSet(closeAtFinal: true);
                    }
                    catch (Exception e)
                    {
                        throw;
                    }
                    finally
                    {
                        if (reader != null && !reader.IsClosed)
                        {
                            reader.Close();
                        }
                    }
                }
            }

            return(list);
        }
コード例 #8
0
        ////////////////////////////////////////////////////////////////////////////////////////////////////
        /// <summary>   An IDbConnection extension method that executes. </summary>
        ///
        /// <remarks>   Msacli, 22.04.2019. </remarks>
        ///
        /// <param name="connection">       The connection to act on. </param>
        /// <param name="sql">              The SQL. </param>
        /// <param name="commandType">      (Optional) Type of the command. </param>
        /// <param name="transaction">      (Optional) The transaction. </param>
        /// <param name="inputParameters">  (Optional) Options for controlling the input. </param>
        /// <param name="outputParameters"> (Optional) Options for controlling the output. </param>
        ///
        /// <returns>   An int. </returns>
        ////////////////////////////////////////////////////////////////////////////////////////////////////
        public static int Execute(this IDbConnection connection,
                                  string sql,
                                  CommandType commandType    = CommandType.Text,
                                  IDbTransaction transaction = null,
                                  Dictionary <string, object> inputParameters  = null,
                                  Dictionary <string, object> outputParameters = null)
        {
            int res = 0;

            try
            {
                using (IDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = sql;
                    command.CommandType = commandType;

                    if (transaction != null)
                    {
                        command.Transaction = transaction;
                    }

                    DxDbCommandHelper.SetCommandParameters(command, inputParameters, outputParameters);

                    res = command.ExecuteNonQuery();

                    if (outputParameters != null && outputParameters.Count > 0)
                    {
                        outputParameters = (Dictionary <string, object>)DxDbCommandHelper.GetOutParametersOfCommand(command);
                    }
                }
            }
            catch (Exception e)
            {
                res = -1;
                throw;
            }

            return(res);
        }