/Fontes/SisVendas/SisVendas.DataAccess/DataBaseHelper.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using MySql.Data;

using MySql.Data.MySqlClient;

using System.Configuration;

using System.Data;

using System.Reflection;



namespace SisVendas.DataAccess

{

   public class DataBaseHelper

    {

         #region Variáveis

        public static MySqlCommand _myCommand =  new MySqlCommand();

        private static MySqlTransaction _myTransaction; 

        #endregion





       #region Propriedades



        public MySqlConnection MyBdConnection { get; set; } 

        public string NomeStringConexao { get; set; }

     

        #endregion 

     

       #region Construtores 

    

        public DataBaseHelper() 

       {



           this.NomeStringConexao = ConfigurationManager.ConnectionStrings["conexao"].ConnectionString;



           if (string.IsNullOrEmpty(this.NomeStringConexao))

               throw new ApplicationException("");



           this.MyBdConnection = new MySqlConnection(this.NomeStringConexao);

       }



       #endregion 

    

       #region Métodos Privados 



        

       private string GetCorrectParameterName(string parameterName) 

       { 

           if (parameterName[0] != '@') 

           { 

               parameterName = "@" + parameterName; 

           } 

           return parameterName; 

       } 

    

       #endregion 

    

       #region Métodos Públicos 

    





       public static DataBaseHelper Create() 

       {

           return new DataBaseHelper(); 

       } 

    

    

       public void OpenConnection() 

       { 

           //se o objeto de transação for nulo, significa que nenhuma operação com transação esta sendo feita, 

           //neste caso, a conexão poderá ser aberta normalmente, do contrário, a conexão utilizada junto com o objeto

           //de tranção deverá ser utilizada, não sendo necessária a execução do código abaixo

           if (_myTransaction == null)

           {

               if (this.MyBdConnection.State == System.Data.ConnectionState.Closed)

               {

                   this.MyBdConnection.Open();

               }

           }

       } 

    

       public void CloseConection() 

       {

           //se o objeto de transação for nulo, significa que nenhuma operação com transação esta sendo feita, 

           //neste caso, a conexão poderá ser aberta normalmente, do contrário, a conexão utilizada junto com o objeto

           //de tranção deverá ser utilizada, não sendo necessária a execução do código abaixo

          if (_myTransaction == null)

           this.MyBdConnection.Close(); 

       } 

    

       public MySqlParameter BuildParameter(string nome, object valor, DbType tipo, int size) 

       { 

           MySqlParameter parametro = new MySqlParameter(this.GetCorrectParameterName(nome), valor); 

           parametro.DbType = tipo; 

           parametro.Size = size; 

           return parametro; 

       } 

    

       public void BuildParameter(string nome, object valor, DbType tipo, int size, List<MySqlParameter> listParametros) 

       { 

           MySqlParameter parametro = this.BuildParameter(nome, valor, tipo, size); 

           listParametros.Add(parametro); 

       } 

    

       public MySqlParameter BuildOutPutParameter(string nome, DbType tipo, int size) 

       { 

           MySqlParameter parametro = new MySqlParameter(); 

           parametro.ParameterName = this.GetCorrectParameterName(nome); 

           parametro.DbType = tipo; 

           parametro.Size = size; 

           parametro.Direction = ParameterDirection.Output; 

           return parametro; 

       }



       public void BuildOutPutParameter(string nome, DbType tipo, int size, List<MySqlParameter> listParametros) 

       { 

           MySqlParameter parametro = this.BuildOutPutParameter(nome, tipo, size); 

           listParametros.Add(parametro); 

       } 

    

       public void ExecuteNonQuery(MySqlCommand command) 

       { 

           command.ExecuteNonQuery(); 

       } 

    

     public void ExecuteNonQuery(MySqlCommand command, bool openConnection) 

     { 

         if (openConnection) 

         { 

             this.OpenConnection(); 

         } 

         this.ExecuteNonQuery(command); 

         if (openConnection) 

         { 

             this.CloseConection(); 

         }

     }





     public void ExecuteCommands(params MySqlCommand[] commands) 

     { 

         Exception erro = null; 

         MySqlTransaction trans = null; 

         try 

         { 

             this.MyBdConnection.Open(); 

             trans = this.MyBdConnection.BeginTransaction(); 

             for (int i = 0; i < commands.Length; i++) 

             { 

                 commands[i].Transaction = trans; 

                 this.ExecuteNonQuery(commands[i]); 

             } 

             trans.Commit(); 

             this.MyBdConnection.Close(); 

         } 

         catch(Exception ex) 

         { 

             trans.Rollback(); 

             erro = ex; 

         } 

         finally 

         { 

             this.MyBdConnection.Close(); 

         } 

  

         if (erro != null) 

         { 

             throw erro; 

         } 

     }



     public DataTable ExecuteQueryWithResult(string query, params MySqlParameter[] parameters)

     {

         Exception erro = null;

         DataTable dt = new DataTable();

         try

         {

             if (_myTransaction==null)

                this.OpenConnection();



             MySqlCommand command = this.MyBdConnection.CreateCommand();

             command.CommandText = query;



             if (_myTransaction != null)

                 command.Transaction = _myTransaction;

             

             if (parameters != null)

                command.Parameters.AddRange(parameters);

             

             MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command);



             dataAdapter.Fill(dt);



             if (_myTransaction ==null)

                 this.CloseConection();

         }

         catch (Exception ex)

         {

             erro = ex;

         }

         finally

         {

             this.CloseConection();

         }



         if (erro != null)

         {

             throw erro;

         }

         return dt;



     }





     public List<T> ExecuteQueryWithResult<T>(string query, params MySqlParameter[] parameters) where T : new()

     {

         Exception erro = null;

         DataTable dt = new DataTable();

         try

         {

             if (_myTransaction == null)

                 this.OpenConnection();



             MySqlCommand command = this.MyBdConnection.CreateCommand();

             command.CommandText = query;



             if (_myTransaction != null)

                 command.Transaction = _myTransaction;



             if (parameters != null)

                 command.Parameters.AddRange(parameters);



             MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command);

             dataAdapter.Fill(dt);



             if (_myTransaction == null)

                 this.CloseConection();

         }

         catch (Exception ex)

         {

             erro = ex;

         }

         finally

         {

             this.CloseConection();

         }



         if (erro != null)

         {

             throw erro;

         }



         List<T> lista = GetList<T>(dt);

         return lista;



     }



    //Inicia a transação

     public void IniciarTransacao()

     {

         this.MyBdConnection.Open();

         _myTransaction = this.MyBdConnection.BeginTransaction();

     }



     public void CancelarTransacao()

     {

         

         if (_myTransaction != null)

         {

             _myTransaction.Rollback();

             _myTransaction = null;

             this.MyBdConnection.Close();

         }

     }



     public void FinalizarTransacao()

     {

         if (_myTransaction != null)

         {

             _myTransaction.Commit();

             _myTransaction = null;

             this.MyBdConnection.Close();

         }

     }



     public void ExecuteNonQuery(string query, params MySqlParameter[] parameters)

     {

         ExecuteNonQuery(query, CommandType.Text,parameters);

     }



    public void ExecuteNonQuery(string query, CommandType tipoComando,params MySqlParameter[] parameters)

    {

         Exception erro = null;

         try

         {       

             this.OpenConnection();

             

             MySqlCommand command = this.MyBdConnection.CreateCommand();

             command.CommandText = query;

             command.CommandType = tipoComando;



             //Configura o objeto command para utilizar a transaction caso a mesma tenha sido iniciada

             if (_myTransaction != null)

                 command.Transaction = _myTransaction;



             command.Parameters.AddRange(parameters);

             this.ExecuteNonQuery(command);



             this.CloseConection();

         }

         catch (Exception ex)

         {

             erro = ex;

         }

         finally

         {

             if (_myTransaction == null)

                 this.CloseConection();

         }



         if (erro != null)

         {

             throw erro;

         }

     }



    public object ExecuteScalar(string query, params MySqlParameter[] parameters)

    {

       return ExecuteScalar(query,CommandType.Text,parameters);

    }



    public object ExecuteScalar(string query, CommandType commandType, params MySqlParameter[] parameters)

    {

        Exception erro = null;

        Object objRetorno = -1;

        try

        {

            this.OpenConnection();



            MySqlCommand command = this.MyBdConnection.CreateCommand();

            command.CommandType = commandType;

            command.CommandText = query;



            //Configura o objeto para recuperar a transaction

            if (_myTransaction != null)

                command.Transaction = _myTransaction;



            command.Parameters.AddRange(parameters);



            objRetorno = command.ExecuteScalar();



            



    

            this.CloseConection();

        }

        catch (Exception ex)

        {

            erro = ex;

        }

        finally

        {

            if (_myTransaction == null)

                this.CloseConection();

        }



        if (erro != null)

        {

            throw erro;

        }



        return objRetorno;

    }





   



     public  List<T> GetList<T>(DataTable dt) where T : new()

    {

        List<T> lista = new List<T>();



        foreach (DataRow r in dt.Rows)

        {

            T t = new T();



            foreach (PropertyInfo pi in t.GetType().GetProperties())

            {

                if (dt.Columns.IndexOf(pi.Name) > -1)

                {

                    if (pi.PropertyType.Equals(typeof(Int32)))

                        pi.SetValue(t, int.Parse(r[pi.Name].ToString()), null);



                    if (pi.PropertyType.Equals(typeof(bool)))

                        pi.SetValue(t, bool.Parse(r[pi.Name].ToString()), null);



                    if (pi.PropertyType.Equals(typeof(string)))

                        pi.SetValue(t,r[pi.Name].ToString(), null);

                    

                }

            }





            lista.Add(t);

        }



        return lista;

    }



#endregion





     



    }

}