using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace Payroll.BO
{
    public class ETransactionContext
    {
        public const int TimeOut = 10000;
        public ETransactionContext() { }

        private static SqlConnection _SQLConnection = null;
       
        public SqlConnection SQLConnection
        {
            get { return _SQLConnection; }
            set { _SQLConnection = value; }
        }
        private static OracleConnection _OrcaleConnection = null;

        public OracleConnection ORACLEConnection
        {
            get { return _OrcaleConnection; }
            set { _OrcaleConnection = value; }
        }
        private static OleDbConnection _OledbConnection = null;

        public OleDbConnection OLEDBConnection
        {
            get { return _OledbConnection; }
            set { _OledbConnection = value; }
        }

        private Connection _Connection = null;

        public Connection Connection
        {
            get { return _Connection; }
            set { _Connection = value; }
        }
        private static Connection _CurrentConnection = null;

        public static Connection CurrentConnection
        {
            get { return _CurrentConnection; }
            set 
            { 
                _CurrentConnection = value;
                
            }
        }

        public void Begin()
        {
            MakeConnection();
            _Connection = _CurrentConnection;
        }

        private ETransactionContext getTC()
        {
            return this;
        }

        private static void MakeConnection()
        {
            try
            {
                switch (CurrentConnection.ConnectionType)
                {
                    case ConnectionType.SQL:
                        _SQLConnection = new SqlConnection(CurrentConnection.ConnectionString);
                        
                        if (_SQLConnection.State != ConnectionState.Open)
                            _SQLConnection.Open();
                        SQL.SQLSyntax = SQLSyntax.SQL;
                        break;
                    case ConnectionType.Oracle:
                        _OrcaleConnection = new OracleConnection(CurrentConnection.ConnectionString);
                        
                        if (_OrcaleConnection.State != ConnectionState.Open)
                            _OrcaleConnection.Open();
                        SQL.SQLSyntax = SQLSyntax.Oracle;
                        break;
                    case ConnectionType.Oledb:
                        _OledbConnection = new OleDbConnection(CurrentConnection.ConnectionString);
                        
                        if (_OledbConnection.State != ConnectionState.Open)
                            _OledbConnection.Open();
                        SQL.SQLSyntax = SQLSyntax.Access;
                        break;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error to Make Connection. Error :\n" + ex.Message);
            }
        }

        public void End()
        {
            try
            {
                switch (CurrentConnection.ConnectionType)
                {
                    case ConnectionType.SQL:
                        if (_SQLConnection  !=null && _SQLConnection.State == ConnectionState.Open)
                            _SQLConnection.Close();
                        break;
                    case ConnectionType.Oracle:
                        if (_OrcaleConnection != null && _OrcaleConnection.State == ConnectionState.Open)
                            _OrcaleConnection.Close();
                        break;
                    case ConnectionType.Oledb:
                        if (_OledbConnection != null && _OledbConnection.State == ConnectionState.Open)
                            _OledbConnection.Close();
                        break;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error to End Connection. Error :\n" + ex.Message);
            }
        }

        public void HandleError()
        {
            try
            {
                switch (CurrentConnection.ConnectionType)
                {
                    case ConnectionType.SQL:
                        if (_SQLConnection != null && _SQLConnection.State != ConnectionState.Open)
                            _SQLConnection.Close();
                        break;
                    case ConnectionType.Oracle:
                        if (_OrcaleConnection != null && _OrcaleConnection.State != ConnectionState.Open)
                            _OrcaleConnection.Close();
                        break;
                    case ConnectionType.Oledb:
                        if (_OledbConnection != null && _OledbConnection.State != ConnectionState.Open)
                            _OledbConnection.Close();
                        break;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error to Handle Connection. Error :\n" + ex.Message);
            }
        }

        public  void ExecuteNonQuery(string commandText, params object[] parameterValues)
        {
            try
            {
                switch (CurrentConnection.ConnectionType)
                {
                    case ConnectionType.SQL:
                        SqlCommand cmd = new SqlCommand(SQL.MakeSQL(commandText, parameterValues), _SQLConnection);
                        cmd.CommandTimeout = TimeOut;
                        cmd.ExecuteNonQuery();
                        break;
                    case ConnectionType.Oracle:
                        OracleCommand cmdOracle = new OracleCommand(SQL.MakeSQL(commandText, parameterValues), _OrcaleConnection);
                        cmdOracle.ExecuteNonQuery();
                        break;
                    case ConnectionType.Oledb:
                        OleDbCommand cmdOledb = new OleDbCommand(SQL.MakeSQL(commandText, parameterValues), _OledbConnection);
                        cmdOledb.CommandTimeout = TimeOut;
                        cmdOledb.ExecuteNonQuery();
                        break;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error to execute command. Error :\n" + ex.Message);
            }

        }

        public DataSet ExecuteDataSet(string commandText, params object[] parameterValues)
        {
            DataSet dataset=new DataSet();
            try
            {
                switch (CurrentConnection.ConnectionType)
                {
                    case ConnectionType.SQL:
                        
                        SqlCommand cmd = new SqlCommand(SQL.MakeSQL(commandText, parameterValues), _SQLConnection);                        
                        SqlDataAdapter adp = new SqlDataAdapter(cmd);
                        cmd.CommandTimeout = TimeOut;
                        adp.Fill(dataset);
                        break;
                    case ConnectionType.Oracle:
                        OracleCommand cmdOracle = new OracleCommand(SQL.MakeSQL(commandText, parameterValues), _OrcaleConnection);
                        OracleDataAdapter adpOracle = new OracleDataAdapter(cmdOracle);                        
                        adpOracle.Fill(dataset);
                        break;
                    case ConnectionType.Oledb:
                        OleDbCommand cmdOledb = new OleDbCommand(SQL.MakeSQL(commandText, parameterValues), _OledbConnection);
                        OleDbDataAdapter adpOledb = new OleDbDataAdapter(cmdOledb);
                        cmdOledb.CommandTimeout = TimeOut;
                        adpOledb.Fill(dataset);
                        break;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error to execute command. Error :\n" + ex.Message);
            }
            return dataset;
        }

        public void ExecuteDataSet(DataSet dataset, string tableName, string commandText, params object[] parameterValues)
        {
            
            try
            {
                DataTable table = new DataTable(tableName);
                switch (CurrentConnection.ConnectionType)
                {
                    case ConnectionType.SQL:

                        SqlCommand cmd = new SqlCommand(SQL.MakeSQL(commandText, parameterValues), _SQLConnection);
                        SqlDataAdapter adp = new SqlDataAdapter(cmd);
                        cmd.CommandTimeout = TimeOut;
                        adp.Fill(table);
                        break;
                    case ConnectionType.Oracle:
                        OracleCommand cmdOracle = new OracleCommand(SQL.MakeSQL(commandText, parameterValues), _OrcaleConnection);
                        OracleDataAdapter adpOracle = new OracleDataAdapter(cmdOracle);
                        adpOracle.Fill(table);
                        break;
                    case ConnectionType.Oledb:
                        OleDbCommand cmdOledb = new OleDbCommand(SQL.MakeSQL(commandText, parameterValues), _OledbConnection);
                        OleDbDataAdapter adpOledb = new OleDbDataAdapter(cmdOledb);
                        cmdOledb.CommandTimeout = TimeOut;
                        adpOledb.Fill(table);
                        break;
                }
                dataset.Tables.Add(table);
            }
            catch (Exception ex)
            {
                throw new Exception("Error to execute command. Error :\n" + ex.Message);
            }
            
        }

        public object ExecuteScalar(string commandText, params object[] parameterValues)
        {
            object obj = null;
            try
            {
                switch (CurrentConnection.ConnectionType)
                {
                    case ConnectionType.SQL:
                        SqlCommand cmd = new SqlCommand(SQL.MakeSQL(commandText, parameterValues), _SQLConnection);
                        cmd.CommandTimeout = TimeOut;
                        obj = cmd.ExecuteScalar();
                        break;
                    case ConnectionType.Oracle:
                        OracleCommand cmdOracle = new OracleCommand(SQL.MakeSQL(commandText, parameterValues), _OrcaleConnection);
                        obj = cmdOracle.ExecuteScalar();
                        break;
                    case ConnectionType.Oledb:
                        OleDbCommand cmdOledb = new OleDbCommand(SQL.MakeSQL(commandText, parameterValues), _OledbConnection);
                        cmdOledb.CommandTimeout = TimeOut;
                        obj = cmdOledb.ExecuteScalar();
                        break;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error to execute command. Error :\n" + ex.Message);
            }
            return obj;
        }

        public IDataReader ExecuteReader(string commandText, params object[] parameterValues)
        {
            IDataReader reader=null;
            try
            {
                switch (CurrentConnection.ConnectionType)
                {
                    case ConnectionType.SQL:
                        SqlCommand cmd = new SqlCommand(SQL.MakeSQL(commandText, parameterValues), _SQLConnection);
                        cmd.CommandTimeout = TimeOut;
                        reader=cmd.ExecuteReader();
                        break;
                    case ConnectionType.Oracle:
                        OracleCommand cmdOracle = new OracleCommand(SQL.MakeSQL(commandText, parameterValues), _OrcaleConnection);                        
                        reader = cmdOracle.ExecuteReader();
                        break;
                    case ConnectionType.Oledb:
                        OleDbCommand cmdOledb = new OleDbCommand(SQL.MakeSQL(commandText, parameterValues), _OledbConnection);
                        cmdOledb.CommandTimeout = TimeOut;
                        reader = cmdOledb.ExecuteReader();
                        break;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error to execute command. Error :\n" + ex.Message);
            }
            return reader;
        }

        //internal static ETransactionContext Begin()
        //{
        //    throw new Exception("The method or operation is not implemented.");
        //}
    }
}