using System;
using System.Data;
using System.Data.OracleClient;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace DataAccessLayer
{
    /// <summary>
    /// DataAccess costruito solo per ConsulenzaEvoluta. A differenza degli altri DB quello di CE è stato impostato con IsolationLevel = Snapshot.
    /// </summary>
    public class DataAccessCE
    {
        /// <summary>
        /// Ritorna un DataTable lanciando la stored con i parametri passati in input sul database di ConsulenzaEvoluta.
        /// IsolationLevel impostato a Snapshot
        /// </summary>
        /// <param name="_dbprovider"></param>
        /// <param name="nomeStoredProcedure"></param>
        /// <param name="paramList"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTableStoredProcedure(DBProvider _dbprovider, string nomeStoredProcedure, List<Parametro> paramList)
        {
            // The DataTable to be ret  urned 
            Database db = GetDatabaseObject(_dbprovider);

            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction(IsolationLevel.Snapshot);
                try
                {
                    // Execute the command making sure the connection gets closed in the end
                    DbCommand comm = db.GetStoredProcCommand(nomeStoredProcedure);
                    comm.CommandType = CommandType.StoredProcedure;

                    if (paramList != null)
                    {
                        foreach (Parametro param in paramList)
                            db.AddParameter(comm, param.ParameterName, param.DbType, param.Direction, param.SourceColumn, param.SourceVersion, param.Value);
                    }

                    DataTable table = db.ExecuteDataSet(comm, transaction).Tables[0];
                    table.TableName = "TABLENAME";
                    return table;

                }
                catch (Exception ex)
                {
                    throw new DataBaseException(ex, db.ConnectionStringWithoutCredentials, nomeStoredProcedure, paramList);
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// Ritorna un DataSet lanciando la stored con i parametri passati in input sul database di ConsulenzaEvoluta.
        /// IsolationLevel impostato a Snapshot
        /// </summary>
        /// <param name="_dbprovider"></param>
        /// <param name="nomeStoredProcedure"></param>
        /// <param name="paramList"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSetStoredProcedure(DBProvider _dbprovider, string nomeStoredProcedure, List<Parametro> paramList)
        {
            Database db = GetDatabaseObject(_dbprovider);

            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction(IsolationLevel.Snapshot);
                try
                {
                    // Execute the command making sure the connection gets closed in the end
                    DbCommand comm = db.GetStoredProcCommand(nomeStoredProcedure);
                    comm.CommandType = CommandType.StoredProcedure;

                    if (paramList != null)
                    {
                        foreach (Parametro param in paramList)
                            db.AddParameter(comm, param.ParameterName, param.DbType, param.Direction, param.SourceColumn, param.SourceVersion, param.Value);
                    }

                    // Execute the command making sure the connection gets closed in the end
                    return db.ExecuteDataSet(comm, transaction);
                }
                catch (Exception ex)
                {
                    throw new DataBaseException(ex, db.ConnectionStringWithoutCredentials, nomeStoredProcedure, paramList);
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// Recupera un valore intero eseguendo la stored con i parametri passati in input sul database di ConsulenzaEvoluta.
        /// IsolationLevel impostato a Snapshot
        /// </summary>
        /// <param name="_dbprovider"></param>
        /// <param name="nomeStoredProcedure"></param>
        /// <param name="paramList"></param>
        /// <returns></returns>
        public static object ExecuteScalarStoredProcedure(DBProvider _dbprovider, string nomeStoredProcedure, List<Parametro> paramList)
        {
            Database db = GetDatabaseObject(_dbprovider);

            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction(IsolationLevel.Snapshot);
                try
                {
                    DbCommand comm = db.GetStoredProcCommand(nomeStoredProcedure);
                    if (paramList != null)
                    {
                        foreach (Parametro param in paramList)
                            db.AddParameter(comm, param.ParameterName, param.DbType, param.Direction, param.SourceColumn, param.SourceVersion, param.Value);
                    }

                    return db.ExecuteScalar(comm, transaction);
                }
                catch (Exception ex)
                {
                    throw new DataBaseException(ex, db.ConnectionStringWithoutCredentials, nomeStoredProcedure, paramList);
                }
                finally
                {
                    connection.Close();
                }

            }
        }

        /// <summary>
        /// Esegue gli statement sul database di ConsulenzaEvoluta aggiunti alla collezione listaSqlStatement in modalità transazionale.
        /// IsolationLevel impostato a Snapshot
        /// </summary>
        /// <param name="_dbprovider"></param>
        /// <param name="_transactionStatement"></param>
        /// <returns></returns>
        public static bool ExecuteNonQueryMultipleTransaction(DBProvider _dbprovider, List<TransactionStatement> _transactionStatement)
        {
            bool result = true;
            string _sqlstatement = "";
            List<Parametro> _paramlist = null;
            DbCommand command = null;
            // The DataTable to be ret  urned 
            Database db = GetDatabaseObject(_dbprovider);

            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction(IsolationLevel.Snapshot);

                try
                {
                    foreach (TransactionStatement _trans in _transactionStatement)
                    {
                        _sqlstatement = _trans.SqlStatement;
                        _paramlist = _trans.ListaParametro;
                        // Esegue la transazione.

                        if (_trans.StatementType == CommandType.Text)
                            command = db.GetSqlStringCommand(_trans.SqlStatement);
                        else if (_trans.StatementType == CommandType.StoredProcedure)
                            command = db.GetStoredProcCommand(_trans.SqlStatement);

                        foreach (Parametro param in _trans.ListaParametro)
                            db.AddParameter(command, param.ParameterName, param.DbType, param.Direction, param.SourceColumn, param.SourceVersion, param.Value);

                        db.ExecuteNonQuery(command, transaction);
                    }
                    // Commit the transaction.
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    // Roll back the transaction. 
                    transaction.Rollback();
                    throw new DataBaseException(ex, db.ConnectionStringWithoutCredentials, _sqlstatement, _paramlist);
                }
                finally
                {
                    connection.Close();
                }
            }

            return result;

        }

        /// <summary>
        /// Esegue la stored con i parametri passati in input sul database di ConsulenzaEvoluta.
        /// IsolationLevel impostato a Snapshot
        /// </summary>
        /// <param name="_dbprovider"></param>
        /// <param name="nomeStoredProcedure"></param>
        /// <param name="paramList"></param>
        /// <returns></returns>
        public static int ExecuteNonQueryStoredProcedure(DBProvider _dbprovider, string nomeStoredProcedure, List<Parametro> paramList)
        {
            Database db = GetDatabaseObject(_dbprovider);

            int ritorno = -1;

            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction(IsolationLevel.Snapshot);
                try
                {
                    // Execute the command making sure the connection gets closed in the end
                    DbCommand comm = db.GetStoredProcCommand(nomeStoredProcedure);
                    if (paramList != null)
                    {
                        foreach (Parametro param in paramList)
                            db.AddParameter(comm, param.ParameterName, param.DbType, param.Direction, param.SourceColumn, param.SourceVersion, param.Value);
                    }

                    Parametro returnParam = new Parametro();
                    returnParam.ParameterName = "RETURN";
                    returnParam.DbType = DbType.Int32;
                    returnParam.Direction = ParameterDirection.ReturnValue;
                    returnParam.Value = -1;

                    db.AddParameter(comm, returnParam.ParameterName, returnParam.DbType, returnParam.Direction, returnParam.SourceColumn, returnParam.SourceVersion, returnParam.Value);

                    ritorno = db.ExecuteNonQuery(comm, transaction);
                    ritorno = (Int32)comm.Parameters["@RETURN"].Value;

                    //Commit the transaction
                    transaction.Commit();
                    return ritorno;
                }
                catch (DataBaseException ex)
                {
                    //Roll back the transaction. 
                    transaction.Rollback();
                    throw new DataBaseException(ex, db.ConnectionStringWithoutCredentials, nomeStoredProcedure, paramList);
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// Recupera la stringa di connessione per il database di ConsulenzaEvoluta
        /// </summary>
        /// <param name="_dbprovider"></param>
        /// <returns></returns>
        private static Database GetDatabaseObject(DBProvider _dbprovider)
        {
            string connstring = WebConfigParameter.getConnectionString("SqlServerConsulenzaEvolutaConnection");
            return new SqlDatabase(connstring);
        }
    }

}