Best practices when modularizing an application with EF and multi-bank
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.
But let's also say that I will have SQL commands customized for each database in certain tasks.
Soon, I create my modules with projects Class Library like this:
- application - containing the business rules, made to be consumed by the layers that request certain tasks from the application;
- Domain - which contains the classes representing the database tables;
- from - Ondem has as classes containing the EF request methods for application methods;
-
repository - which will contain my inherited class of
DbContext
and its propertiesDbSet<>
;
That 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 be adopted in this scenario?
How to deal with references / dependencies of dlls ?
1 answers
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 some SGDBs work will have a little headache, but after all 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 Dbsets. 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 noted I use the Database.SetInitializer( null); not to create the database.
My classes I do in the Model folder, one .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 interface, so I can make an event to populate the user data.
I am implementing like this.
Update 1:
As a comment of doubts, I edited the response. Then let's go. When I started messing with EF and C#, I didn't have much knowledge. But in all the system I develop I put the fields that are in the IEntidadeBase . There are people who will ask me why structure, simple, with it I go to the registry and see the information of the same 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 it is saved, you call the method and it enters the registration values. But why did I do that? In the version of EF4 had no way to make a generic DbSet, I do not know if the new ones have.
Now let's talk about BlogCateriaExt and this. With this class I can do like this:
db.BlogCateria.ToListERP();
In my case, this is interesting because it follows all system rules. Ah I have other events or rather, other returns. The return listing, serves this very 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 default return to mount listing.
And to finish, because all this in one .cs only? Simple, so you don't have to keep walking around too much.
I hope I have settled the doubts, anything gives a shout.