c# – Best Practices when modularizing an application with EF and multi-bank

Question:

Let's say I want to model an application in .NET using EntityFramework as ORM and use one of its advantages, being "generic" for several RDBMS .

So, for example, I want my application to accept working with Firebird , SQL Server or MySQL . That when installing the application, the user can select which SGBD he wants and then the settings are made.

However, let's also say that I will have custom SQL commands for each database in certain tasks.

So, I create my modules with Class Library projects like this:

  • Application – Containing the business rules, made to be consumed by the layers that request certain tasks to the application;
  • Domain – Which contains the classes that represent the database tables;
  • DAL – Ondem has the classes that contain the EF request methods for the application methods;
  • Repository – Which will contain my class inherited from DbContext and its DbSet<> properties;

That would be my initial approach.

How would I do so I could have my specific DAL layer for each RDBMS ?
Or rather, what are the best practices to be adopted in this scenario?
How to deal with DLL's references/dependencies?

Answer:

I currently use EF in my applications on Windows Forms and on the Web. To use multiple databases, the only difference is the connection string.

However, I warn you that to make some SGDBs work you will have a little headache, but after everything is ok and installed it works perfectly.

My applications follow the pattern:

Database: In this folder I have ERPDBContext.cs, which has DbContext and DbSet. Example:

public class ERPContext : DbContext
{
    public ERPContext()
        : base("ConexaoERP")
    {
        Database.SetInitializer<ERPContext>(null);
    }

    public DbSet<Usuario> Usuario { get; set; }
    public DbSet<UsuarioAcesso> UsuarioAcesso { get; set; }     

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Usuario>().ToTable("Usuario");
        modelBuilder.Entity<UsuarioAcesso>().ToTable("UsuarioAcesso");
    }
}

If you notice I use Database.SetInitializer(null); not to create the database.

I make my classes in the Model folder, a .cs for each "table"/class. In this model, as I use EF, I make an inherited class to make development easier. Example:

using System;
using System.Collections.Generic;
using System.Linq.Dynamic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using FlexGestor.Helpers;
using System.Data.Entity;
using FlexGestor.Interface;

namespace FlexGestor.Models
{
    public class BlogCategoria : IEntidadeBase
    {
        [Key]
        public int BlogCategoriaID { get; set; }
        [Display(Name="Seção")]
        public int BlogSecaoID { get; set; }
        public string Sigla { get; set; }
        [Display(Name = "Descrição")]
        public string Descricao { get; set; }

        /* Campos fixos */
        public int EmpresaID { get; set; }
        public string Fixo { get; set; }
        public string Status { get; set; }
        public string Apagado { get; set; }
        public DateTime? DtApagado { get; set; }
        public int UsuCad { get; set; }
        public DateTime DtCad { get; set; }
        public int UsuAlt { get; set; }
        public DateTime DtAlt { get; set; }
        public int UsuUltAlt { get; set; }
        public DateTime DtUltAlt { get; set; }

        [ForeignKey("UsuCad")]
        public virtual Usuario UsuarioCad { get; set; }
        [ForeignKey("UsuAlt")]
        public virtual Usuario UsuarioAlt { get; set; }
        [ForeignKey("UsuUltAlt")]
        public virtual Usuario UsuarioUltAlt { get; set; }
        [ForeignKey("EmpresaID")]
        public virtual Empresa Empresa { get; set; }

        [ForeignKey("BlogSecaoID")]
        public virtual BlogSecao BlogSecao { get; set; }
    }

    public static class BlogCategoriaExt
    {
        public static IEnumerable<Listagem> Listagem(this DbSet<BlogCategoria> entities, int secaoID)
        {
            return entities
                .Where(u => u.Apagado == "N" && u.BlogSecaoID == secaoID)
                .OrderBy(r => r.Descricao)
                .Select(l => new Listagem { Key = l.BlogCategoriaID, Texto = l.Descricao });
        }

        public static IEnumerable<BlogCategoria> ToListERP(this DbSet<BlogCategoria> entities)
        {
            var usuarioLogado = HttpContext.Current.Session["usuarioLogado"] as UsuarioLogado;
            return ToListERP(entities, usuarioLogado);
        }

        public static IEnumerable<BlogCategoria> ToListERP(this DbSet<BlogCategoria> entities, UsuarioLogado usuarioLogado)
        {
            return ToListERP(entities, usuarioLogado.EmpresaIDLogada.GetValueOrDefault(0));
        }

        public static IEnumerable<BlogCategoria> ToListERP(this DbSet<BlogCategoria> entities, int empresaID)
        {
            return entities.Where(w => w.EmpresaID == empresaID && w.Apagado == "N");
        }

        public static ResultadoListagemPadrao Grid(string orderna, string ordenaTipo, string filtro, int? filtroID, UsuarioLogado usuarioLogado)
        {
            ERPContext db = new ERPContext();
            var resultado = new ResultadoListagemPadrao();
            var dados = db.BlogCategoria.ToListERP(usuarioLogado);
            var where = "";            
            var id = 0;

            if (int.TryParse(filtro, out id))
                where = " CategoriaID == " + id.ToString();

            resultado.TotalRegistros = dados.Count();
            if (filtro != null)
                where = " Descricao.Contains(@0) ";
            resultado.Dados =
                (from a in dados.AsQueryable()
                 select new
                 {
                     CategoriaID = a.BlogCategoriaID,
                     a.Sigla,
                     a.Descricao
                 })
                .Where(where, filtro)
                .OrderBy(orderna + " " + ordenaTipo);
            return resultado;
        }
    }
}

For models, I use interface, so I can make an event to populate user data.

I'm implementing it like this.

Update 1:

As per the comments of doubts, I edited the answer. So come on. When I started working with EF and C#, I didn't have much knowledge. However, in all the systems that I develop, I place the fields that are in the IEntidadeBase . There are people who will ask me why it is structured, simple, with it I go to the registry and quickly see its information.

public interface IEntidadeBase
{
    int EmpresaID { get; set; }
    string Fixo { get; set; }
    string Status { get; set; }
    string Apagado { get; set; }
    DateTime? DtApagado { get; set; }
    int UsuCad { get; set; }
    DateTime DtCad { get; set; }
    int UsuAlt { get; set; }
    DateTime DtAlt { get; set; }
    int UsuUltAlt { get; set; }
    DateTime DtUltAlt { get; set; }
    Usuario UsuarioCad { get; set; }
    Usuario UsuarioAlt { get; set; }
    Usuario UsuarioUltAlt { get; set; }
    Empresa Empresa { get; set; }
}

Then just use the interface in the classes. But what this will help me, simple, with it you can make a method when you are going to save, you call the method and it inserts the registration values. But why did I do it like that? In the EF4 version there was no way to make a generic DbSet, I don't know if the new ones have it.

Now let's talk about BlogCateriaExt and this. With this class I can do it like this:

db.BlogCateria.ToListERP();

In my case, this is interesting as it follows all the rules of the system. Then I have other events or better, other returns. The Listing return serves just that, to return the information in a way that is easier to use in combobox.

Also, my extended models have the Grid method, which is a standard return to build listing.

And finally, why all this in one .cs? Simple, so you don't have to walk around a lot.

I hope I have resolved the doubts, anything gives a scream.

Post Views: 0
Scroll to Top