Good Practice when modularizing an application with EF and multi-bank

Question

Let’s say I want to model an application on .NET using the EntityFramework as ORM and use one of its advantages, to be "generic" for several RDBMS.

So, for example, I want my application to agree to work with Firebird, SQL Server or MySQL. That in the installation of the application the user can select which SGBD he wants and then the settings are made.

But let’s also say I’ll have commands SQL customized for each database in certain tasks.

Soon, I create my modules with projects Class Library thus:

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

That would be my initial approach.

How I would then be able to have my DAL layer specific to each RDBMS?
Or rather, what are the best practices to be adopted in this scenario?
How to handle references/dependencies DLL s?

Answer 1

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

But I already warn that to make work some Sgdbs will have a small headache, but after everything ok and installed 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.

My classes I do in the Model folder, a . Cs for each "table"/class. In this model, as I use EF, I make an inherited class to make life easier in development. 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 the models, I use interface, so I can make an event to popular the user data.

I’m implementing like this.

Update 1:

As a comment of doubts, I edited the answer. So come on. When I started messing with EP and C#, I didn’t have much knowledge. But in every system I develop I put the fields that are in Ientidadebase. Some people will ask me why structure, simple, with it I go on record and see the information of it quickly.

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 with, simple, with it you can do a method of when you save, you call the method and it inserts the registration values. But why did I do so? In the EF4 version there was no way to make a Dbset.

Now let’s talk about Blogcateriaext and this. With this class I can do so:

db.BlogCateria.ToListERP();

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

In addition, my extended models have the Grid method, which is a standard return for listing.

And finally, why all this in one . Cs only? Simple, so you don’t have to walk too far to one side.

I hope I’ve cleared my doubts, something gives a shout.