DAL.cs

<< Click to Display Table of Contents >>

Navigation:  Persistencia > Persistencia > banco >

DAL.cs

Previous pageReturn to chapter overviewNext page

using System;

using System.Collections.Generic;

using System.Data;

using System.Linq.Expressions;

using System.Reflection;

using System.Text;

using Modelo.classes;

using Persistencia.classes;

 

namespace Persistencia.banco

{

    // Data Access Layer - acesso a camada de dados

    public class DAL

    {

        public static string GetStringConexao()

        {

            return "server=localhost;User Id=root;Password=123456;database=teste";

            //string parametro = "conexao" + (HttpContext.Current.Request.IsLocal ? "_base_local" : "_base_server");

            //return ConfigurationManager.ConnectionStrings[parametro].ToString();

        }

 

        #region insert e update

        // grava um único objeto

 

        /// <summary>

        /// Persiste (grava) um objeto em banco mySQL baseando-se nos atributos do objeto para saber os campos e seus tipos

        /// Pessoa p = new Pessoa () { Nome = "Junior" };

        /// DAL.Gravar(p);

        /// </summary>

        /// <param name="data">instância do objeto a ser gravado.</param>

        /// <returns>Retornar o id do objeto (em caso de inserção)</returns>

        public static long Gravar(object data)

        {

            long idRetorno = 0;

 

            // o Montador.GetCampos retorna num List<Campo> o nome do campo, se é PK e seu valor (Nullable<object>)

            List<Campo> campos = Montador.GetCampos(data);

            string tableName = Montador.GetTableName(data);

 

            using (Conexao conexao = Conexao.Get(GetStringConexao()))

            using (Comando comando = new Comando(conexao, GetSqlInsertUpdate(tableName, campos)))

            {

                foreach (Campo campo in campos)

                {

                    comando.AddParam(string.Format("@{0}", campo.Nome), (campo.Valor ?? DBNull.Value));

                }

                comando.Execute();

                idRetorno = comando.LastInsertId;

            }

            return idRetorno;

        }

 

        /// <summary>

        /// Grava (persiste) no banco mySQL vários objetos da List (todos dentro da mesma transação)

        /// </summary>

        /// <typeparam name="T">Tipo do objeto a ser gravado</typeparam>

        /// <param name="list">Lista de T</param>

        /// <returns>Quantidade de objetos gravados</returns>

        public static int GravarList<T>(List<T> list)

        {

            if (list.Count == 0)

                return 0;

 

            int idRetorno = 0;

 

            // o Montador.GetCampos retorna num List<Campo> o nome do campo, se é PK e seu valor (Nullable<object>)

            List<Campo> campos = Montador.GetCampos(list[0]);

            string tableName = Montador.GetTableName(list[0]);

 

            using (Conexao conexao = Conexao.Get(GetStringConexao()))

            using (Transacao transacao = new Transacao(conexao))

            using (Comando comando = new Comando(transacao, GetSqlInsertUpdate(tableName, campos)))

            {

                // percorre os objetos da lista

                foreach (Object obj in list)

                {

                    // atualiza os valores

                    campos = Montador.GetCampos(obj);

                    foreach (Campo campo in campos)

                    {

                        comando.AddParam(string.Format("@{0}", campo.Nome), (campo.Valor ?? DBNull.Value));

                    }

                    comando.Execute();

                    // limpa os parâmetros e vamos para o próximo

                    comando.ClearParam();

                    idRetorno++;

                }

                transacao.Commit();

            }

            return idRetorno;

        }

 

        /// <summary>

        /// Grava um objeto mestre e na mesma transação os filhos no list.

        /// <c>

        ///    List<Empresa> lista = new List<Empresa>();

        ///    lista.Add(new Empresa() { Nome = "empresa1" });

        ///    lista.Add(new Empresa() { Nome = "empresa2" });

        ///    lista.Add(new Empresa() { Nome = "empresa3" });

        ///    lista.Add(new Empresa() { Nome = "empresa4" });

        ///    lista.Add(new Empresa() { Nome = "empresa5" });

        ///

        ///    int registros_gravados = DAL.GravarList<Empresa>(lista);

        ///

        ///    Console.WriteLine("Total de registros gravados: " + registros_gravados.ToString());

        /// </c>

        /// </summary>

        /// <typeparam name="T">Tipo do objeto a ser gravado</typeparam>

        /// <param name="objMestre">Objeto pai</param>

        /// <param name="listDetalhes">Lista de T (filhos)</param>

        /// <returns>O id do registro pai</returns>

        public static long GravarMestreDetalhe<T>(object objMestre, List<T> listDetalhes)

        {

            long idRetorno = Montador.GetKeyId(objMestre);

 

            // o Montador.GetCampos retorna num List<Campo> o nome do campo, se é PK e seu valor (Nullable<object>)

            List<Campo> camposMestre = Montador.GetCampos(objMestre);

            string tableNameMestre = Montador.GetTableName(objMestre);

 

            using (Conexao conexao = Conexao.Get(GetStringConexao()))

            using (Transacao transacao = new Transacao(conexao))

            {

                #region mestre

                using (Comando comando = new Comando(transacao, GetSqlInsertUpdate(tableNameMestre, camposMestre)))

                {

                    foreach (Campo campo in camposMestre)

                    {

                        comando.AddParam(string.Format("@{0}", campo.Nome), (campo.Valor ?? DBNull.Value));

                    }

                    comando.Execute();

                    // se é inserção então lemos o ultimo id

                    if (idRetorno == 0)

                        idRetorno = comando.LastInsertId;

                }

                #endregion

 

                #region detalhes

                if (listDetalhes.Count > 0)

                {

                    // pegamos o nome do campo FK no primeiro objeto da lista

                    string campoDetalheFK = Montador.GetFieldFK(listDetalhes[0]);

                    // montamos a estrutura base do SQL 

                    string tableNameDetalhes = Montador.GetTableName(listDetalhes[0]);

                    List<Campo> camposDetalhes = Montador.GetCampos(listDetalhes[0]);

 

                    using (Comando comando = new Comando(transacao, GetSqlInsertUpdate(tableNameDetalhes, camposDetalhes)))

                    {

                        // percorre os objetos da lista

                        foreach (Object obj in listDetalhes)

                        {

                            // atualiza os valores

                            camposDetalhes = Montador.GetCampos(obj);

                            foreach (Campo campo in camposDetalhes)

                            {

                                // o campo atual é FK?  e o valor é ZERO (null converte pra zero) ?

                                if (campo.Nome.Equals(campoDetalheFK) && (int)(campo.Valor ?? 0) == 0)

                                    campo.Valor = idRetorno;

 

                                comando.AddParam(string.Format("@{0}", campo.Nome), (campo.Valor ?? DBNull.Value));

                            }

                            comando.Execute();

                            // limpa os parâmetros e vamos para o próximo

                            comando.ClearParam();

                        }

                    }

                }

                #endregion

                transacao.Commit();

            }

            return idRetorno;

        }

        #endregion

 

        #region rotinas para retornar listas ou leitores

        /// <summary>

        /// Efetua um select na base com where e order by opcionais e retorna num objeto tipo Leitor

        /// </summary>

        /// <c>

        ///    Leitor l = DAL.Listar(new Empregado());

        ///    while (!l.Eof)

        ///    {

        ///        Console.WriteLine(l.GetString("id_empregado") + "-" + l.GetString("nm_empregado"));

        ///        l.Next();

        ///    }

        /// </c>

        /// <param name="data">Instância do objeto</param>

        /// <param name="filtro">É string, mas ao invés de passar um string direto, use o objeto Filtros para facilitar</param>

        /// <param name="ordem">É string, mas ao invés de passar os campos diretamente, use o objeto Order</param>

        /// <returns></returns>

        public static Leitor Listar(object data, string filtro = ""string ordem = "")

        {

            using (Conexao conexao = Conexao.Get(GetStringConexao()))

            using (Comando comando = new Comando(conexao, GetSqlSelect(data, filtro, ordem)))

            {

                return comando.Select();

            }

        }

 

        /// <summary>

        /// Efetua um select na base com where e order by opcionais e retorna num objeto tipo DataTable

        /// </summary>

        /// <c>

        ///    DataTable tb = DAL.ListarDataTable(new Usuario());

        ///    foreach (DataRow r in tb.Rows)

        ///        Console.WriteLine(r["nm_usuario"].ToString());

        /// </c>

        /// <param name="data">Instância do objeto</param>

        /// <param name="filtro">É string, mas ao invés de passar um string direto, use a classe Filtros para facilitar</param>

        /// <param name="ordem">É string, mas ao invés de passar os campos diretamente, use a classe Order</param>

        /// <returns></returns>

        public static DataTable ListarDataTable(object data, string filtro = ""string ordem = "")

        {

            return Listar(data, filtro, ordem).Data;

        }

 

        /// <summary>

        /// Efetua um select na base e retorna um objeto T com base no seu primary key

        /// <c>Empresa empresa = DAL.GetObjetoById<Empresa>(3);</c>

        /// </summary>

        /// <typeparam name="T">Tipo do retorno</typeparam>

        /// <param name="id">Código id para buscar o objeto na base</param>

        /// <returns>Retorna uma instância do Tipo</returns>

        public static T GetObjetoById<T>(int id) where T : classnew()

        {

            // cria uma instância do objeto

            T t = new T();

 

            // pega os campos para poder montar o select

            List<Campo> campos = Montador.GetCampos(t);

 

            // monta o select e filtra pelo campo chave

            string sql = GetSqlSelect(t, string.Format("{0}={1}", GetIdFieldName(campos), id));

 

            using (Conexao conexao = Conexao.Get(GetStringConexao()))

            using (Comando comando = new Comando(conexao, sql))

            using (Leitor leitor = comando.Select())

            {

                // percorre as propriedades

                foreach (PropertyInfo property in InfoAtributo.PropertySimple(t))

                {

                    // valor busta pelo nome do campo

                    object valor = leitor.GetObject(InfoAtributo.Campo(property).FieldName);

 

                    if ((valor != null) && (!(valor is System.DBNull)))

                    {

                        property.SetValue(t, valor, null);

                    }

                }

            }

 

            return t;

        }

 

        /// <summary>

        /// Efetua um select na base e retorna um objeto T com base em um filtro (retorna apenas 1 registro)

        /// </summary>

        /// <c>

        ///    Empregado empregado = new Empregado();

        ///

        ///    Filtros filtro = new Filtros().Add(() => empregado.Nome, empregado, FiltroExpressao.Igual, "Junior");

        ///    empregado = DAL.GetObjeto<Empregado>(filtro.ToString());

        ///

        ///    if (empregado == null)

        ///        Console.WriteLine("Nao encontrado!");

        ///    else

        ///        Console.WriteLine(empregado.Id + "-" + empregado.Nome);

        /// </c>

        /// <typeparam name="T">Tipo do retorno</typeparam>

        /// <param name="filtro">Use a classe Filtros para montar o filtro</param>

        /// <returns>Retorna uma instância do Tipo</returns>

        public static T GetObjeto<T>(string filtro = ""where T : classnew()

        {

            // cria uma instância do objeto

            T t = new T();

 

            // pega os campos para poder montar o select

            List<Campo> campos = Montador.GetCampos(t);

 

            // monta o select e filtra pelo campo chave

            string sql = GetSqlSelect(new T(), filtro);

 

            using (Conexao conexao = Conexao.Get(GetStringConexao()))

            using (Comando comando = new Comando(conexao, sql))

            using (Leitor leitor = comando.Select())

            {

                // não tem nenhum? volta nulo

                if (leitor.RecordCount == 0)

                    return null;

 

                // percorre as propriedades

                foreach (PropertyInfo property in InfoAtributo.PropertySimple(t))

                {

                    // valor busta pelo nome do campo

                    object valor = leitor.GetObject(InfoAtributo.Campo(property).FieldName);

 

                    if ((valor != null) && (!(valor is System.DBNull)))

                    {

                        property.SetValue(t, valor, null);

                    }

                }

            }

 

            return t;

        }

 

        /// <summary>

        /// Efetua um select na base e retorna uma list de objeto T com base em um filtro e order by 

        /// <c> List<Usuario> lista = DAL.ListarObjetos<Usuario>(typeof(Usuario));</c>

        /// </summary>

        /// <typeparam name="E">Tipo do List de retorno</typeparam>

        /// <param name="filtro">Use a classe Filtros para montar o filtro</param>

        /// <param name="ordem">É string, mas ao invés de passar os campos diretamente, use a classe Order</param>

        /// <returns>Retorna uma List de instâncias do Tipo</returns>

        public static List<E> ListarObjetos<E>(string filtro = ""string ordem = ""where E : classnew()

        {

            List<E> list = new List<E>();

 

            string sql = GetSqlSelect(new E(), filtro, ordem);

 

            using (Conexao conexao = Conexao.Get(GetStringConexao()))

            using (Comando comando = new Comando(conexao, sql))

            using (Leitor leitor = comando.Select())

            {

                while (!leitor.Eof)

                {

                    // cria uma instância do objeto

                    E item = new E();

 

                    // percorre as propriedades

                    foreach (PropertyInfo property in InfoAtributo.PropertySimple(item))

                    {

                        // valor busta pelo nome do campo

                        object valor = leitor.GetObject(InfoAtributo.Campo(property).FieldName);

 

                        if ((valor != null) && (!(valor is System.DBNull)))

                        {

                            property.SetValue(item, valor, null);

                        }

                    }

                    list.Add(item);

 

                    //Proximo

                    leitor.Next();

                }

            }

            return list;

        }

        #endregion

 

        #region rotinas auxiliares para montar código sql

        protected static string GetCampo<T, O>(Expression<Func<T>> exp, O obj)

        {

            Auxiliar.Retorno ret = Auxiliar.GetInfo(exp, obj);

            return ret.FieldName;

        }

 

        // retorna um "select campo1, campo2, campo3 from tabela" a partir do objeto passar por parametro

        protected static string GetSqlSelect(object data, string filtro = ""string ordem = "")

        {

            // o Montador.GetCampos retorna num List<Campo> o nome do campo

            List<Campo> campos = Montador.GetCampos(data);

 

            StringBuilder sqlCampos = new StringBuilder();

            foreach (Campo campo in campos)

            {

                sqlCampos.Append(string.Format("{0},", campo.Nome));

            }

            sqlCampos.Remove(sqlCampos.Length - 1, 1);

 

            StringBuilder sql = new StringBuilder();

            sql.Append("select ");

            sql.Append(sqlCampos.ToString());

            sql.Append(" from ");

            sql.Append(Montador.GetTableName(data));

 

            // temos where?

            if (!filtro.Trim().Equals(string.Empty))

            {

                sql.Append(" where ");

                sql.Append(filtro);

            }

 

            // temos order by?

            if (!ordem.Trim().Equals(string.Empty))

            {

                sql.Append(" order by ");

                sql.Append(ordem);

            }

 

            return sql.ToString();

        }

 

        // monta o código de insert e update

        protected static string GetSqlInsertUpdate(string tableName, List<Campo> campos)

        {

            // campo1, campo2, campo3  (usado no inicio - insert into X (AQUI) values ...

            StringBuilder sqlCampos = new StringBuilder();

 

            // @campo1, @campo3, @campo3 (usado nos valores - insert into X (c,c,c) values (AQUI)

            StringBuilder sqlValores = new StringBuilder();

 

            // campo1=@campo1, campo2=@campo3 (usado no on duplicate) - insert into x (c) values (@c) on duplicate key update AQUI

            StringBuilder sqlUpdate = new StringBuilder();

            string sqlId = string.Empty;

 

            foreach (Campo campo in campos)

            {

                sqlCampos.Append(string.Format("{0},", campo.Nome));

                sqlValores.Append(string.Format("@{0},", campo.Nome));

                if (campo.IsKey)

                    sqlId = string.Format("{0}=@{0}", campo.Nome);

                else

                    sqlUpdate.Append(string.Format("{0}=@{0},", campo.Nome));

            }

 

            // remove a vírgula final de todos

            sqlCampos.Remove(sqlCampos.Length - 1, 1);

            sqlValores.Remove(sqlValores.Length - 1, 1);

            sqlUpdate.Remove(sqlUpdate.Length - 1, 1);

 

            // montamos o sql final

            StringBuilder sql = new StringBuilder();

 

            // vamos pegar o valor id

            long id = DAL.GetIdValue(campos);

 

            // inserção?

            if (id == 0)

            {

                sql.Append("insert into ");

                sql.Append(tableName);

                sql.Append(" (");

                sql.Append(sqlCampos.ToString());

                sql.Append(") values (");

                sql.Append(sqlValores.ToString());

                sql.Append(")");

            }

            else

            {

                sql.Append("update ");

                sql.Append(tableName);

                sql.Append(" set ");

                sql.Append(sqlUpdate.ToString());

                sql.Append(" where ");

                sql.Append(sqlId);

            }

            return sql.ToString();

        }

 

        // usado para localizar um registro com base no campo chave (Attribute IsKey)

        protected static string GetSqlLocalizarId(string tableName, List<Campo> campos)

        {

            // campo1=@campo1, campo2=@campo3 

            StringBuilder sqlCampos = new StringBuilder();

            string campoRetorno = string.Empty;

 

            foreach (Campo campo in campos)

            {

                if (campo.IsKey)

                    campoRetorno = campo.Nome;

                else

                    sqlCampos.Append(string.Format("{0}=@{0},", campo.Nome));

            }

 

            // remove a vírgula final de todos

            sqlCampos.Remove(sqlCampos.Length - 1, 1);

 

            // montamos o sql final

            StringBuilder sql = new StringBuilder();

            sql.Append("select ");

            sql.Append(campoRetorno);

            sql.Append(" from ");

            sql.Append(tableName);

            sql.Append(" where ");

            sql.Append(sqlCampos.ToString());

            return sql.ToString();

        }

 

        // retorna o valor do campo chave (Attribute IsKey)

        protected static long GetIdValue(List<Campo> campos)

        {

            long idRetorno = 0;

            foreach (Campo campo in campos)

            {

                if (campo.IsKey)

                    if (campo.Valor.GetType() == typeof(int))

                    {

                        idRetorno = (int)campo.Valor;

                        break;

                    }

            }

            return idRetorno;

        }

 

        // percorre os campos e retorna o nome do campo chave (Atributo IsKey)

        protected static string GetIdFieldName(List<Campo> campos)

        {

            foreach (Campo campo in campos)

                if (campo.IsKey)

                    return campo.Nome;

            return string.Empty;

        }

        #endregion

 

        #region select com join

        /// <summary>

        /// Efetuar um select com JOIN usando com ponto de ligação o attribute IsKey do dataPai e o atribute IsPK do dataFilho

        /// </summary>

        /// <param name="dataPai">Instância do objeto pai (mestre)</param>

        /// <param name="dataFilho">Instância do objeto filho (detalhes)</param>

        /// <param name="filtro">Use a classe Filtros</param>

        /// <param name="ordem">Use a classe Ordem</param>

        /// <returns>Retorna um objeto Leitor</returns>

        public static Leitor ListarJoin(object dataPai, object dataFilho, string filtro = ""string ordem = "")

        {

            using (Conexao conexao = Conexao.Get(GetStringConexao()))

            using (Comando comando = new Comando(conexao, GetSqlSelectJoin(dataPai, dataFilho, filtro, ordem)))

            {

                return comando.Select();

            }

        }

 

        /// <summary>

        /// Efetuar um select com JOIN usando com ponto de ligação o attribute IsKey do dataPai e o atribute IsPK do dataFilho

        /// </summary>

        /// <param name="dataPai">Instância do objeto pai (mestre)</param>

        /// <param name="dataFilho">Instância do objeto filho (detalhes)</param>

        /// <param name="filtro">Use a classe Filtros</param>

        /// <param name="ordem">Use a classe Ordem</param>

        /// <returns>Retorna um objeto DataTable</returns>

        public static DataTable ListarDataTableJoin(object dataPai, object dataFilho, string filtro = ""string ordem = "")

        {

           return ListarJoin(dataPai, dataFilho, filtro, ordem).Data;

        }

 

        // retorna um "select campo1, campo2, campo3 from tabela" a partir do objeto passar por parametro

        private static string GetSqlSelectJoin(object dataPai, object dataFilho, string filtro = ""string ordem = "")

        {

            // o Montador.GetCampos retorna num List<Campo> o nome do campo

            List<Campo> camposPai = Montador.GetCampos(dataPai);

            string tabelaPai = Montador.GetTableName(dataPai);

            string campoKeyPai = string.Empty;

 

            List<Campo> camposFilho = Montador.GetCampos(dataFilho);

            string tabelaFilho = Montador.GetTableName(dataFilho);

            string campoFKFilho = string.Empty;

 

            #region monta os campos

            StringBuilder sqlCampos = new StringBuilder();

            foreach (Campo campo in camposPai)

            {

                if (campo.IsKey)

                {

                    sqlCampos.Append("a.");

                    campoKeyPai = "a." + campo.Nome;

                }

                sqlCampos.Append(string.Format("{0},", campo.Nome));

            }

            foreach (Campo campo in camposFilho)

            {

                if (campo.IsFK)

                    campoFKFilho = "b." + campo.Nome;

                else

                    sqlCampos.Append(string.Format("{0},", campo.Nome));

            }

            sqlCampos.Remove(sqlCampos.Length - 1, 1);

            #endregion

 

            StringBuilder sql = new StringBuilder();

            sql.Append("select ");

            sql.Append(sqlCampos.ToString());

            sql.Append(" from ");

            sql.Append(tabelaFilho);

            sql.Append(" as a join ");

            sql.Append(tabelaPai);

            sql.Append(" as b on ");

            sql.Append(campoKeyPai);

            sql.Append("=");

            sql.Append(campoFKFilho);

 

            // temos where?

            if (!filtro.Trim().Equals(string.Empty))

            {

                sql.Append(" where ");

                // caso tenha no where o campo key ou FK, trocamos pelo "lias.campo" para não dar erro de ambiguous field

                filtro = filtro.Replace(campoKeyPai.Split('.')[1], campoKeyPai);

                sql.Append(filtro);

            }

 

            // temos order by?

            if (!ordem.Trim().Equals(string.Empty))

            {

                sql.Append(" order by ");

                // caso tenha no order o campo key ou FK, trocamos pelo "lias.campo" para não dar erro de ambiguous field

                ordem = ordem.Replace(campoKeyPai.Split('.')[1], campoKeyPai);

                sql.Append(ordem);

            }

            return sql.ToString();

        }

        #endregion

    }

}