terça-feira, 21 de julho de 2015

Oracle Connection C#

using System;
using System.Collections.Generic;
using System.Data;
using Oracle.DataAccess.Client;

namespace Lapuinka.Security.Data
{
    internal static class DataAccess
    {
        #region -- Propriedades --

        ///
        /// OracleConnection.
        /// Cria um objeto OracleConnection.
        ///
        private static OracleConnection _connection;

        ///
        /// OracleCommand.
        /// Cria um objeto OracleCommand.
        ///
        private static OracleCommand _command;

        ///
        /// OracleTransaction.
        /// Cria um objeto OracleTransaction.
        ///
        private static OracleTransaction _transaction;

        ///
        /// Fontes de dados.
        ///
        public enum EnumConnection
        {
            DbSecurity = 1,
            DbView = 2
        }

        #endregion

        #region -- Métodos --

        ///
        /// Método GetConnetion.
        /// Cria o objeto de conexão.
        ///
        /// Retorna OracleConnection.
        private static OracleConnection GetConnection(EnumConnection connection)
        {

            OracleConnection oracleConnection = null;

            switch (connection)
            {
                case EnumConnection.DbSecurity:
                    oracleConnection = new OracleConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]);
                    break;
                case EnumConnection.DbView:
                    oracleConnection = new OracleConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringNew"]);
                    break;
            }

            // Retorno a conexão.
            return oracleConnection;
        }

        ///
        /// Método ExecuteQuery.
        /// Executa o comando sql.
        ///
        ///
        /// Nome da Stored Procedure.
        /// Lista de Parâmetros.
        public static void ExecuteNoQuery(EnumConnection connectionString, string commandString, List listaParametros)
        {
            _connection = new OracleConnection();
            _command = new OracleCommand();

            try
            {
                _connection = GetConnection(connectionString);
                _connection.Open();
                _transaction = _connection.BeginTransaction();

                _command.Connection = _connection;
                _command.Transaction = _transaction;
                _command.CommandType = CommandType.StoredProcedure;
                _command.CommandText = commandString;

                foreach (OracleParameter parametro in listaParametros)
                {
                    _command.Parameters.Add(parametro);
                }

                _command.ExecuteNonQuery();
                _transaction.Commit();
            }
            catch (OracleException)
            {
                if (_transaction != null)
                {
                    _transaction.Rollback();
                }

                if (_connection.State == ConnectionState.Open)
                {
                    _connection.Clone();
                }

                _command.Dispose();
                _connection.Dispose();

                throw;
            }
            catch (Exception)
            {
                if (_transaction != null)
                {
                    _transaction.Rollback();
                }

                if (_connection.State == ConnectionState.Open)
                {
                    _connection.Close();
                }

                _command.Dispose();
                _connection.Dispose();

                throw;
            }
            finally
            {
                if (_connection.State == ConnectionState.Open)
                {
                    _connection.Close();
                }

                _command.Dispose();
                _connection.Dispose();

                listaParametros.Clear();
            }
        }

        ///
        /// Método ExecuteQuery.
        /// Executa o comando sql.
        ///
        ///
        /// Nome da Stored Procedure.
        /// Lista de Parâmetros.
        public static void ExecuteNoQuery(EnumConnection connectionString, string commandString)
        {
            _connection = new OracleConnection();
            _command = new OracleCommand();

            try
            {
                _connection = GetConnection(connectionString);
                _connection.Open();
                _transaction = _connection.BeginTransaction();

                _command.Connection = _connection;
                _command.Transaction = _transaction;
                _command.CommandType = CommandType.StoredProcedure;
                _command.CommandText = commandString;
                _command.ExecuteNonQuery();
                _transaction.Commit();
            }
            catch (OracleException)
            {
                if (_transaction != null)
                {
                    _transaction.Rollback();
                }

                if (_connection.State == ConnectionState.Open)
                {
                    _connection.Clone();
                }

                _command.Dispose();
                _connection.Dispose();

                throw;
            }
            catch (Exception)
            {
                if (_transaction != null)
                {
                    _transaction.Rollback();
                }

                if (_connection.State == ConnectionState.Open)
                {
                    _connection.Close();
                }

                _command.Dispose();
                _connection.Dispose();

                throw;
            }
            finally
            {
                if (_connection.State == ConnectionState.Open)
                {
                    _connection.Close();
                }

                _command.Dispose();
                _connection.Dispose();
            }
        }

        ///
        /// Método ExecuteQueryReader.
        /// Executa o comando sql e retorna um DataTable.
        ///
        ///
        /// Nome da stored procedure.
        /// Lista de Parâmetros.
        /// Retorna DataTable
        public static DataTable ExecuteReader(EnumConnection connectionString, string commandString, List listaParametros)
        {
            var data = new DataTable();
            _connection = new OracleConnection();
            _command = new OracleCommand();

            try
            {
                _connection = GetConnection(connectionString);
                _connection.Open();
                _transaction = _connection.BeginTransaction();

                _command.Connection = _connection;
                _command.Transaction = _transaction;
                _command.CommandType = CommandType.StoredProcedure;
                _command.CommandText = commandString;

                foreach (OracleParameter parametro in listaParametros)
                {
                    _command.Parameters.Add(parametro);
                }

                data.Load(_command.ExecuteReader());
            }
            catch (OracleException)
            {
                if (_transaction != null)
                {
                    _transaction.Rollback();
                }

                if (_connection.State == ConnectionState.Open)
                {
                    _connection.Close();
                }

                _command.Dispose();
                _connection.Dispose();

                throw;
            }
            catch (Exception)
            {
                if (_transaction != null)
                {
                    _transaction.Rollback();
                }

                if (_connection.State == ConnectionState.Open)
                {
                    _connection.Clone();
                }

                _command.Dispose();
                _connection.Dispose();

                throw;
            }
            finally
            {
                if (_connection.State == ConnectionState.Open)
                {
                    _connection.Clone();
                }

                _command.Dispose();
                _connection.Dispose();

                listaParametros.Clear();
            }

            return data;
        }

        ///
        /// Método ExecuteReaderDataSet.
        /// Executa o comando sql e retorna um DataSet.
        ///
        ///
        /// Nome da stored procedure.
        /// Lista de Parâmetros.
        /// Retorna DataSet.
        public static DataSet ExecuteReaderDataSet(EnumConnection connectionString, string commandString, List listaParametros)
        {
            var data = new DataSet();
            var table = new DataTable();

            _connection = new OracleConnection();
            _command = new OracleCommand();

            try
            {
                _connection = GetConnection(connectionString);
                _connection.Open();
                _transaction = _connection.BeginTransaction();

                _command.Connection = _connection;
                _command.Transaction = _transaction;
                _command.CommandType = CommandType.StoredProcedure;
                _command.CommandText = commandString;

                foreach (OracleParameter parametro in listaParametros)
                {
                    _command.Parameters.Add(parametro);
                }

                data.Tables.Add(table);

                data.Load(_command.ExecuteReader(), LoadOption.OverwriteChanges, table);
            }
            catch (OracleException)
            {
                if (_transaction != null)
                {
                    _transaction.Rollback();
                }

                if (_connection.State == ConnectionState.Open)
                {
                    _connection.Clone();
                }

                _command.Dispose();
                _connection.Dispose();

                throw;
            }
            catch (Exception)
            {
                if (_transaction != null)
                {
                    _transaction.Rollback();
                }

                if (_connection.State == ConnectionState.Open)
                {
                    _connection.Clone();
                }

                _command.Dispose();
                _connection.Dispose();

                throw;
            }
            finally
            {
                if (_connection.State == ConnectionState.Open)
                {
                    _connection.Clone();
                }

                _command.Dispose();
                _connection.Dispose();

                listaParametros.Clear();
            }

            return data;
        }

        ///
        /// Método ExecuteScalar.
        /// Executa o comando sql e retorna um inteiro contendo o número de linhas
        /// afetadas na execução do comando sql.
        ///
        ///
        /// Nome da stored procedure.
        /// Lista de Parâmetros.
        /// Return Int.
        public static Int32 ExecuteScalar(EnumConnection connectionString, string commandString, List listaParametros)
        {
            string parameterName = null;

            _connection = new OracleConnection();
            _command = new OracleCommand();

            try
            {
                _connection = GetConnection(connectionString);
                _connection.Open();
                _transaction = _connection.BeginTransaction();

                _command.Connection = _connection;
                _command.Transaction = _transaction;
                _command.CommandType = CommandType.StoredProcedure;
                _command.CommandText = commandString;

                foreach (var parametro in listaParametros)
                {
                    _command.Parameters.Add(parametro);

                    if (parametro.Direction == ParameterDirection.Output ||
                        parametro.Direction == ParameterDirection.InputOutput)
                        parameterName = parametro.ParameterName;
                }

                _command.ExecuteScalar();

                _transaction.Commit();

                return Convert.ToInt32(_command.Parameters[parameterName].Value.ToString());
            }
            catch (OracleException)
            {
                if (_transaction != null)
                {
                    _transaction.Rollback();
                }

                if (_connection.State == ConnectionState.Open)
                {
                    _connection.Close();
                }

                _command.Dispose();
                _connection.Dispose();

                throw;
            }
            catch (Exception)
            {
                if (_transaction != null)
                {
                    _transaction.Rollback();
                }

                if (_connection.State == ConnectionState.Open)
                {
                    _connection.Close();
                }

                _command.Dispose();
                _connection.Dispose();

                throw;
            }
            finally
            {
                if (_connection.State == ConnectionState.Open)
                {
                    _connection.Close();
                }

                _command.Dispose();
                _connection.Dispose();

                listaParametros.Clear();
            }
        }

        #endregion
    }

}
Postar um comentário