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

Question

I’m studying to make a CRUD for a framework but I’m trying some problems to be able to do it in the most generic way possible.

For example:

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

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

Answer 1

What you want, from what I understand, 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 like Repositories, for example, or else implement the Data Access Layer (DAL) directly. The ideal is that this layer of data access is based on an interface using a generic type. I’ll give an example to be 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 must 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 must implement a method that selects the data according to some parameters.

You can also implement a common class that implements this interface, more or less 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, automatically C# forces you to implement the method Selecionar(IEnumerable<Operador> operadores) (by being Abstract) and your data access class already wins a method called Selecionar() no parameters. Then you can implement your class as that of 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). Note that I haven’t left it too generic yet, but if you are improving this pattern (for example, doing the method Inserir class Comum read the properties of a data object and mount a dynamic SQL), I believe you can get a very cool result without using too much specific code.

Let me give you a few more tips on the 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 any point, just talk.

Answer 2

Why don’t you take a look at Petapoco (http://www.toptensoftware.com/petapoco/)?

It is based on the framework used here in Stackoverflow and has Insert(Object) methods that insert the object directly into the base. And the speed is infinitely higher than Entity Framework or Linq to SQL =)

Some examples of 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

Answer 3

It is worth noting that there is an abstraction level difference between standards such as MVC, MVP, MVVM and those Enterprise or Gof standards (Adapter, Iterator, Bridge, Composite, Intercepting Filter, etc).

The first are patterns architectural, are one level up in the abstraction hierarchy.

---

A while ago I came across this article.

As it is something new, there is no literature about it (and I looked for it, because I intended to do my TCC on it, but I found almost nothing usable).

What is the "problem" with MVC?

The architectural pattern designed by Trygve Reenskaug was designed for desktop applications. Since it is something highly abstract, it was possible to adapt and modify it over time until arriving at the various implementations that 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 define the function of this layer.

Example:

In a desktop application, the controller is the part that keeps listening to events and calling callbacks for them (will that be all?).

In a traditional web application, much of the controller’s function is done by the browser itself, you don’t even have to worry about it. His role 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 also hinders, leaves a lot of developer around.

The proposal of MOVE

To remove cloudiness around controllers, the idea is to create two well-delimited 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, ie an attempt to log in, sending data for registration, etc.

Symfony Framework already has one component 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 using MOVE in real applications, but it’s an interesting idea.