What would be the best way to do a CRUD for a framework in the most generic way possible?

12

I am studying to do a CRUD for a framework but I am trying some problems to be able to do this in the most generic way possible.

For example:

I have a class Pessoa() I want to do CRUD passing only the Person object to another class in another layer like for example: DAL.AccessData(Pessoa objPessoa) . And within this class DAL.AccessData(Pessoa objPessoa) I would have my methods: insert/delete/update/select .

What would be the best way to do this? Of course, also using the concept of dependency injection with control inversion?

    
asked by anonymous 05.02.2014 / 21:04

4 answers

11

What you want, as I understand it, is to implement your own Framework with CRUD operations. I think that in this first step it is not necessary to use dependency injection control inversion.

First think about what the data architecture will look like. You can use business classes, such as Repositories , for example, or implement the data access layer (DAL) directly. Ideally, this data access layer should be embedded in an interface using a generic type. I'll give you an example to get clearer:

namespace MeuProjeto.Data.Interfaces
{
    public interface IAcessoDados<T>
        where T: class
    {
        List<T> Selecionar();
        List<T> Selecionar(IEnumerable<Operador> operadores); 
        void Incluir(T objeto);
        void Atualizar(T objeto);
    }
}

Any and all data access classes should use this interface. This ensures that the methods are the same for all data classes.

Notice that Selecionar is polymorphic. Calling without operators, you must implement a method that will select all elements of your data source (a relational database, for example). In the second you should implement a method that selects the data according to some parameters.

You can also implement a common class that implements this interface, something like this:

namespace MeuProjeto.Data
{
    public abstract class Comum<T>: IDisposable, IComum<T>
        where T: class
    {
        #region Propriedades

        protected string ConexaoBancoDados { get; set; }

        private String _whereOuAnd = " WHERE ";
        protected String WhereOuAnd
        {
            get
            {
                var retorno = _whereOuAnd;
                _whereOuAnd = " AND ";
                return retorno;
            }
        }

        private string _virgulaOuEspaco = " ";
        public String VirgulaOuEspaco {
            get
            {
                var retorno = _virgulaOuEspaco;
                _virgulaOuEspaco = ", ";
                return retorno;
            }
        }

        protected void ReiniciarWhereEVirgula()
        {
            _whereOuAnd = " WHERE ";
            _virgulaOuEspaco = " ";
        }

        #endregion

        #region Construtores

        protected Comum() { }

        protected Comum(String conexaoBancoDados)
        {
            ConexaoBancoDados = conexaoBancoDados;
        }

        #endregion

        #region Métodos

        /// <summary>
        /// Overload que retorna uma lista com todos os objetos do tipo T.
        /// </summary>
        /// <returns>
        /// Lista com objetos do tipo Job.
        /// </returns>
        public virtual List<T> Selecionar()
        {
            return Selecionar(new List<Operador>());
        }

        /// <summary>
        /// Método selecionar padrão. Recebe uma lista de operadores para selecionar do banco e devolver uma lista
        /// </summary>
        /// <param name="operadores"></param>
        /// <returns></returns>
        /// <remarks>Deve ser implementado em cada classe derivada.</remarks>
        public abstract List<T> Selecionar(IEnumerable<Operador> operadores);

        public abstract void Incluir(T objeto);
        public abstract void Atualizar(T objeto);

        #endregion

        public void Dispose()
        {

        }
    }
}

If your data classes inherit from this class Comum , C # automatically forces you to implement the Selecionar(IEnumerable<Operador> operadores) method (because it is abstract) and your data access class already gains a method named Selecionar() with no parameters. Then you can implement your class like this from a project of mine:

namespace MeuProjeto.Data
{
    public class AtividadesComentarios : Comum<AtividadesComentario>, IDisposable
    {
        public AtividadesComentarios() { }
        public AtividadesComentarios(String conexaoBancoDados) : base(conexaoBancoDados) { }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="parametros"></param>
        /// <returns></returns>
        public override List<AtividadesComentario> Selecionar(IEnumerable<Operadores.Operador> operadores)
        {
            using (var obj = new Common.Database())
            {
                var sSql =
                    "select ac.ID_COMENTARIO, ac.ID_ATIVIDADE, ac.TEXTO, ac.DATA, ac.ID_USUARIO, ac.HISTORICO_ANTIGO " +
                    " from ATIVIDADES_COMENTARIOS ac ";

                foreach (var operador in operadores)
                {
                    sSql += WhereOuAnd + " ac." + operador;
                }

                var parametros = operadores.Where(o => o.GetType().IsAssignableFrom(typeof(Igual))).Select(o2 => ((Igual)o2).ParametroOracle).ToList();
                var retorno = new List<AtividadesComentario>();
                OracleConnection connection;

                using (OracleDataReader reader = obj.ConsultarSqlReader(ConexaoBancoDados, sSql, parametros, out connection))
                {
                    while (reader.Read())
                    {
                        retorno.Add(new AtividadesComentario
                        {
                            AtividadesComentarioId = reader.GetInt32(reader.GetOrdinal("ID_COMENTARIO")),
                            AtividadeId = reader.GetInt32(reader.GetOrdinal("ID_ATIVIDADE")),
                            UsuarioId = reader.GetInt32(reader.GetOrdinal("ID_USUARIO")),
                            HistoricoAntigoId = reader.GetInt32(reader.GetOrdinal("HISTORICO_ANTIGO")),
                            Texto = reader.GetOracleClob(reader.GetOrdinal("TEXTO")).Value,
                            Data = reader.GetDateTime(reader.GetOrdinal("DATA"))
                        });
                    }

                    reader.Close();
                    connection.Close();
                }

                return retorno;
            }
        }

        /// <summary>
        /// Inclui um novo comentário na atividade.
        /// </summary>
        /// <param name="objeto">O objeto a ser inserido.</param>
        public override void Incluir(AtividadesComentario objeto)
        {
            try
            {
                var oDataBase = new Database();
                objeto.AtividadesComentarioId = oDataBase.RecuperaIDSequence(ConexaoBancoDados, "SEQ_ID_COMENTARIO_ATIVIDADE");
                const string sSql = "INSERT INTO ATIVIDADES_COMENTARIOS (ID_COMENTARIO, ID_ATIVIDADE, TEXTO, DATA, ID_USUARIO, HISTORICO_ANTIGO) " +
                                    " VALUES (:ID_COMENTARIO, :ID_ATIVIDADE, :TEXTO, :DATA, :ID_USUARIO, :HISTORICO_ANTIGO)";

                var oParams = ExtrairParametros(objeto);
                oDataBase.ExecutaComandoNonQuery(ConexaoBancoDados, sSql, oParams.ToList());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public override void Atualizar(AtividadesComentario objeto)
        {
            throw new NotImplementedException();
        }
    }
}

That object oDatabase is an object that communicates with the specific technology of your database (in my case, an Oracle database). Notice that I have not made it very generic yet, but if you improve this pattern (for example, by making the Inserir method of the Comum class read the properties of a data object and build a dynamic SQL), I believe you can get a cool result without using too much specific code.

I'll give you some more tips on methods I've built to read data objects via Reflection:

Extract Primary Keys from a Data Object

    /// <summary>
    /// Extrai a chave primária de um objeto (property decorada com o atributo [Key]).
    /// </summary>
    /// <param name="objeto">Um objeto pertencente ao Namespace Metadata.</param>
    /// <returns></returns>
    protected IEnumerable<OracleParameter> ExtrairChavesPrimarias(Object objeto)
    {
        var type = objeto.GetType();
        var properties =
            type.GetProperties(BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public |
                               BindingFlags.NonPublic | BindingFlags.FlattenHierarchy);

        foreach (var property in properties)
        {
            var keyAttribute = Attribute.GetCustomAttribute(property, typeof (KeyAttribute)) as KeyAttribute;
            if (keyAttribute != null)
            {
                var columnAttribute = (ColumnAttribute)property.GetCustomAttributes(typeof(ColumnAttribute), true).FirstOrDefault();

                if (columnAttribute != null)
                {
                    yield return new OracleParameter
                    {
                        ParameterName = columnAttribute.Name,
                        Value = property.GetValue(objeto, null)
                    };
                }
                else
                {
                    yield return new OracleParameter
                    {
                        ParameterName = property.Name,
                        Value = property.GetValue(objeto, null)
                    };
                }
            }
        }
    }

Extract Parameters from a Data Object

    /// <summary>
    /// Método selecionar padrão. Recebe uma lista de operadores para selecionar do banco e devolver uma lista
    /// </summary>
    /// <param name="operadores"></param>
    /// <returns></returns>
    /// <remarks>Deve ser implementado em cada classe derivada.</remarks>
    public abstract List<T> Selecionar(IEnumerable<Operador> operadores);

    private IEnumerable<PropertyInfo> ExtrairPropertiesDeObjeto(Object objeto)
    {
        return objeto.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public |
                               BindingFlags.NonPublic | BindingFlags.FlattenHierarchy);
    }

    /// <summary>
    /// Monta uma lista de parâmetros por Reflection. Um parâmetro é uma property decorada com o atributo [Column].
    /// </summary>
    /// <param name="objeto"></param>
    /// <returns></returns>
    protected IEnumerable<OracleParameter> ExtrairParametros(Object objeto)
    {
        foreach (var propertyInfo in ExtrairPropertiesDeObjeto(objeto))
        {
            var columnAttribute = (ColumnAttribute)propertyInfo.GetCustomAttributes(typeof(ColumnAttribute), true).FirstOrDefault();
            if (columnAttribute != null)
            {
                yield return new OracleParameter
                {
                    ParameterName = columnAttribute.Name,
                    Value = propertyInfo.GetValue(objeto, null)
                };
            }
            else
            {
                yield return new OracleParameter
                {
                    ParameterName = propertyInfo.Name,
                    Value = propertyInfo.GetValue(objeto, null)
                };
            }
        }
    }

If you need me to detail the answer at some point, just talk.

    
13.02.2014 / 16:31
6

Why not take a look at PetaPoco ( link )?

It is based on the framework used here in StackOverflow and has methods of type Insert (object) that inserts the object directly into the base. And the speed is infinitely greater than Entity Framework or Linq to SQL =)

Some examples from the site:

// Represents a record in the "articles" table
public class article
{
    public long article_id { get; set; }
    public string title { get; set; }
    public DateTime date_created { get; set; }
    public bool draft { get; set; }
    public string content { get; set; }
}

// Create a PetaPoco database object
var db=new PetaPoco.Database("connectionStringName");

// Show all articles    
foreach (var a in db.Query<article>("SELECT * FROM articles"))
{
    Console.WriteLine("{0} - {1}", a.article_id, a.title);
}

// Create the article
var a=new article();
a.title="My new article";
a.content="PetaPoco was here";
a.date_created=DateTime.UtcNow;

// Insert it
db.Insert("articles", "article_id", a);

// by now a.article_id will have the id of the new article
    
05.02.2014 / 21:21
3

It is worth mentioning that there is a difference of level of abstraction between standards like MVC, MVP, MVVM and those Enterprise or GoF (Adapter, Iterator, Bridge, Composite, Intercepting Filter, etc.) standards.

The first are architectural standards, they are one level above the abstraction hierarchy.

Some time ago I came across this article .

As it is something new, there is no literature on this (and I did look, as I intended to do my CBT on this, but I found almost nothing usable).

What is the "problem" with MVC?

The architectural standard designed by Trygve Reenskaug was designed for desktop applications. As it is highly abstracted, it has been possible to adapt and modify it over time to reach the various implementations we see today. And it works well! It's just kind of confusing.

In the article I put above, he points out that the biggest confusion is in the definition of Controller . It is difficult to delimit the function of this layer.

Example:

In a desktop application, the controller is the part that listens to events and calls to them (is that all?).

In a traditional web application, much of the controller's function is carried out by the browser itself, you do not even have to worry about it. The role of it is then only to interpret the parameters of the request in order to select which data source to fetch the data and which will be the View used to display this data.

And when the application is "dynamic" in the sense that there are several interactions that are mediated by Javascript? Every time you do

document.querySelector('#some-element').addEventListener('click', function(){...}); 

and its variations, you are implementing a controller, much more like desktop applications.

This flexibility in the definition is good, but it also gets in the way, leaving a lot of developer out there lost.

The MOVE proposal

To remove the cloudiness around the controllers, the idea is to create two well-defined sets that represent the user's actions:

  • Operations: What do you want to do? Register a post, log in, list data ...
  • Events: the act of requesting an operation, that is, an attempt to log in, sending data for registration, etc.

The Symfony Framework already has a component of server-side events, which would help create applications with this new architectural style, but I've never seen anything implemented.

It may be a little early to try to use MOVE in real applications, but it's an interesting idea.

    
13.03.2014 / 18:14
0

Another ORM (object-relational mapper) that is fairly simple and opensource is Dapper . I've used it quite successfully.

    
06.02.2014 / 01:29