using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;

namespace server
{
    public class DataAccess
    {

        #region atributos
        public DateTime EmptyDate = new DateTime(1900, 1, 1);
        private int timeout = 900;
        #endregion

        #region propiedades
        public MySqlConnection Connection { get; }

        public string ConnectionString { get { return Connection.ConnectionString; } }

        #endregion

        #region constructores
        public DataAccess(string connectionString)
        {
            Connection = new MySqlConnection(connectionString);
        }
        public DataAccess()
        {
            String str = System.Configuration.ConfigurationManager.ConnectionStrings["Main"].ConnectionString;
            Connection = new MySqlConnection(str);
        }
        #endregion

        #region métodos
        public void ExecuteMySqlCommand(string sSQL, MySqlParameter[] parameters)
        {
            using (MySqlConnection mySQLConn = new MySqlConnection(ConnectionString))
            using (MySqlCommand mySQLCommd = new MySqlCommand())
            {
                try
                {
                    mySQLConn.Open();
                    mySQLCommd.Connection = mySQLConn;
                    mySQLCommd.CommandText = sSQL;
                    mySQLCommd.CommandType = CommandType.Text;
                    if (parameters != null)
                        foreach (MySqlParameter p in parameters)
                            mySQLCommd.Parameters.Add(p);
                    mySQLCommd.ExecuteNonQuery();
                    mySQLConn.Close();
                }
                catch (MySqlException eMySql)
                {

                }
            }
        }

        public void ExecuteStoreProcedure(string pStoreProcedure, MySqlParameter[] pParameters, int iTimeOut)
        {
            using (MySqlConnection mySQLConn = new MySqlConnection(ConnectionString))
            using (MySqlCommand mySQLCommd = new MySqlCommand())
            {
                mySQLConn.Open();
                mySQLCommd.Connection = mySQLConn;
                mySQLCommd.CommandText = pStoreProcedure;
                mySQLCommd.CommandType = CommandType.StoredProcedure;
                mySQLCommd.CommandTimeout = iTimeOut;
                if (pParameters != null)
                    foreach (MySqlParameter p in pParameters)
                        mySQLCommd.Parameters.Add(p);
                mySQLCommd.ExecuteNonQuery();
                mySQLConn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="nameStoreProcedure"></param>
        /// <returns></returns>
        public List<String> GetParametersFromSP(string nameStoreProcedure)
        {
            List<String> lstResult = new List<string>();
            DataSet dataset = new DataSet();
            using (MySqlConnection mySQLConn = new MySqlConnection(ConnectionString))
            using (MySqlCommand mySQLCommd = new MySqlCommand())
            {
                mySQLConn.Open();
                mySQLCommd.CommandTimeout = timeout;
                mySQLCommd.Connection = mySQLConn;
                mySQLCommd.CommandText = nameStoreProcedure;
                mySQLCommd.CommandType = CommandType.StoredProcedure;
                MySqlCommandBuilder.DeriveParameters(mySQLCommd);
                foreach (MySqlParameter p in mySQLCommd.Parameters)
                    lstResult.Add(p.ParameterName);
            }
            return lstResult;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="nameStoreProcedure"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataSet GetDataSetFromStoreProcedure(string nameStoreProcedure, MySqlParameter[] parameters)
        {
            DataSet dataset = new DataSet();
            using (MySqlConnection mySQLConn = new MySqlConnection(ConnectionString))
            using (MySqlCommand mySQLCommd = new MySqlCommand())
            {
                mySQLConn.Open();
                MySqlDataAdapter mySqlAdapter;
                mySQLCommd.Connection = mySQLConn;
                mySQLCommd.CommandText = nameStoreProcedure;
                mySQLCommd.CommandType = CommandType.StoredProcedure;
                if (parameters != null)
                    foreach (MySqlParameter p in parameters)
                        mySQLCommd.Parameters.Add(p);
                mySqlAdapter = new MySqlDataAdapter(mySQLCommd);
                mySqlAdapter.Fill(dataset);
                mySQLConn.Close();
            }
            return dataset;
        }

        public DataTable GetDataTableFromSQLCommand(string commandText)
        {
            DataSet dataset = new DataSet();
            using (MySqlConnection mySQLConn = new MySqlConnection(ConnectionString))
            using (MySqlCommand mySQLCommd = new MySqlCommand())
            {
                mySQLConn.Open();
                MySqlDataAdapter mySqlAdapter;
                mySQLCommd.Connection = mySQLConn;
                mySQLCommd.CommandText = commandText;
                mySQLCommd.CommandType = CommandType.Text;
                mySqlAdapter = new MySqlDataAdapter(mySQLCommd);
                mySqlAdapter.Fill(dataset);
                mySQLConn.Close();
            }
            return dataset.Tables[0];
        }

        public DataTable GetDataTableFromStoreProcedure(string nameStoreProcedure, MySqlParameter[] parameters)
        {
            DataSet dataset = new DataSet();
            using (MySqlConnection mySQLConn = new MySqlConnection(ConnectionString))
            using (MySqlCommand mySQLCommd = new MySqlCommand())
            {
                mySQLConn.Open();
                MySqlDataAdapter mySqlAdapter;
                mySQLCommd.CommandTimeout = timeout;
                mySQLCommd.Connection = mySQLConn;
                mySQLCommd.CommandText = nameStoreProcedure;
                mySQLCommd.CommandType = CommandType.StoredProcedure;
                if (parameters != null)
                    foreach (MySqlParameter p in parameters)
                        mySQLCommd.Parameters.AddWithValue(p.ParameterName, p.Value);
                mySqlAdapter = new MySqlDataAdapter(mySQLCommd);
                mySqlAdapter.Fill(dataset);
                mySQLConn.Close();
            }
            return dataset.Tables.Count == 0 ? null : dataset.Tables[0];
        }

        #endregion

    }
}