Best Practices when modulating an application with EF and multi-bank

7

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

So, for example, I want my application to accept working 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.

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

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

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

This would be my initial approach.

How would I then be able to have my specific DAL layer for each RDBMS ?
Or rather, what are the best practices to adopt in this scenario?
How to deal with DLL references ?

    
asked by anonymous 09.05.2014 / 03:23

1 answer

3

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

But I warned that to make some SGDBs work will have a little headache, but after everything ok and installed works perfectly.

My applications follow the pattern:

Database: In this folder I have the ERPDBContext.cs, which has the DbContext and the 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 I notice, I use the 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 models, I use the interface, so I can make an event to populate user data.

I'm implementing it like this.

Update 1:

As per the comment of doubts, I edited the answer. So come on. When I started messing with EF and C #, I was not very knowledgeable. But in every system I develop, I put the fields that are in IEntidadeBase . There are people who will ask me why structure, simple, with it I go into the registry 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, simple, with it you can make a method of when to save, you call the method and it inserts the values of register. But why did I do so? In the EF4 version I had no way to do a generic DbSet, I do not know if it has new ones.

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

db.BlogCateria.ToListERP();

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

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

And to finish, why all this in a .cs only? Simple, so you do not have to walk too far from side to side.

I hope to heal the doubts, anything gives a shout.

    
10.05.2014 / 15:45