

namespace GMLL00ImportadorXsd.DAO
{
    using System.Collections.ObjectModel;
    using System.Data;
    using Npgsql;
    using System;
    using System.IO;


    public class ConectaBbdd : IDisposable
    {
        private readonly string cadenaConexion;
        NpgsqlConnection connection;


        public ConectaBbdd(string cadenaConexion)
        {
            this.cadenaConexion = cadenaConexion;
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }


        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                connection.Dispose();
            }
        }



        public bool PruebaConexion()
        {

            NpgsqlConnection conexion;
            bool conexionOK = false;
            try
            {
                conexion = new NpgsqlConnection(this.cadenaConexion);
                conexion.Open();
                if (conexion.State == System.Data.ConnectionState.Open)
                {
                    conexion.Dispose();
                    conexion.Close();
                    conexionOK = true;
                }
                else
                {
                    conexionOK = false;
                }
            }
            catch (NpgsqlException)
            {
                conexionOK = false;
            }
            catch (FormatException)
            {
                conexionOK = false;
            }
            catch (IOException)
            {
                conexionOK = false;
            }

            return conexionOK;

        }


        public string EjecutaSelect(string sentenciaSql)
        {

            NpgsqlDataReader dataReader = null;
            string salidaEjecucion = string.Empty;


            if (dataReader != null)
            {
                dataReader.Close();
            }

            NpgsqlCommand command = new NpgsqlCommand(sentenciaSql, connection);
            try
            {
                dataReader = command.ExecuteReader();
            }
            catch (NpgsqlException e)
            {
                salidaEjecucion = e.Message;
            }
            finally
            {
                command.Dispose();
                if (dataReader != null)
                {
                    dataReader.Close();
                }

            }

            return salidaEjecucion;
        }


        public string EjecutaInsert(string sentenciaSql)
        {
            string salidaEjecucion = string.Empty;
            try
            {
                NpgsqlCommand command = new NpgsqlCommand(sentenciaSql, connection);
                salidaEjecucion = Convert.ToString(command.ExecuteNonQuery(), null);
                command.Dispose();

            }
            catch (NpgsqlException e)
            {
                salidaEjecucion = e.Message;

            }


            return salidaEjecucion;
        }


        public void AbrirConexion()
        {
            try
            {
                connection = new NpgsqlConnection(this.cadenaConexion);
                connection.Open();
            }
            catch (NpgsqlException)
            {
                //string mensaje = e.Message;
            }
        }




        public void CerrarConexion()
        {
            try
            {
                connection.Dispose();
                connection.Close();
            }
            catch (NpgsqlException)
            {
                //string mensaje = e.Message;
            }
        }

    }
}
