We have been looking into replacing our data access layer with a loosely coupled approach. Here is a summary of what the resulting data access layer looks like.
Starting with 2 tables with a foreign key relationship. The first table has some wishes that have WishStatus given in the second class.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace PartnerRe.NCP.Data.Contracts
{
public class Wish
{
public virtual int Id { get; set; }
public virtual string Title { get; set; }
public virtual string Description { get; set; }
public virtual DateTime EntryDate { get; set; }
public virtual WishStatus Status { get; set; }
}
//CREATE TABLE [dbo].[DEV_WISH_LIST](
// [WISH_LIST_ID] [int] IDENTITY(1,1) NOT NULL,
// [TITLE] [varchar](max) NULL,
// [DESCRIPTION] [varbinary](max) NULL,
// [COMMENT] [varbinary](max) NULL,
// [ENTRY_DATE] [datetime] NULL,
// [STATUS_ID] [int] NULL,
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace PartnerRe.NCP.Data.Contracts
{
public enum WishStatus
{
New,
Approved,
InProgress,
Completed,
Declined,
Postponed
}
}
Here is a mapping file that maps the fields in these classes to database table fields
Wish.hbm.xml
============
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="Entities"
namespace="Entities">
<class
name="Wish"
table="DEV_WISH_LIST"
lazy="false">
<id name="Id" column="WISH_LIST_ID">
<generator class="native"/>
</id>
<property name="Title" column="TITLE" />
<property name="Description" column="DESCRIPTION" />
<property name="EntryDate" column="ENTRY_DATE" />
<property name="Status" column="STATUS_ID" />
</class>
</hibernate-mapping>
The database connection strings are defined in a config file
<?xml version="1.0"?>
<configuration>
<configSections>
<section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler, NHibernate" />
</configSections>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2" >
<session-factory>
<property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
<property name="connection.connection_string">Data Source=localhost;Initial Catalog=RESEARCH_CM;Integrated Security=True</property>
<property name="proxyfactory.factory_class">NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle</property>
<property name="default_catalog">RESEARCH_CM</property>
<property name="default_schema">dbo</property>
</session-factory>
</hibernate-configuration>
</configuration>
Next we make a Generic Data Access Layer. First we need to generate an interface. This interface enables us to replace NHibernate with any data access technology.
using System.Collections;
using System.Collections.Generic;
using System.Linq;
namespace PartnerRe.NCP.Data.Contracts
{
/// <summary>
/// Simple, general purpose, reusable data access layer (DAL) interface.
/// </summary>
public interface IDatabaseContext
{
/// <summary>
/// Gets all entities of given type.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <returns>The list of entities.</returns>
IList<TEntity> GetAll<TEntity>();
/// <summary>
/// Gets all entities of a given type ordered ascending or descending by the given property.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="orderBy">The property the entities are ordered by.</param>
/// <param name="ascending">if set to <c>true</c> [ascending].</param>
/// <returns>The list of ordered entities.</returns>
IList<TEntity> GetAll<TEntity>(string orderBy, bool ascending);
/// <summary>
/// Gets all entities according to the given simple filter.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="propertyName">Name of the property.</param>
/// <param name="value">The value of the property.</param>
/// <returns></returns>
IList<TEntity> GetFiltered<TEntity>(string propertyName, object value);
/// <summary>
/// Loads the entity with the given identifier.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="dbId">The db id.</param>
/// <returns>The loaded entitiy.</returns>
TEntity GetById<TEntity>(object dbId);
/// <summary>
/// HACK: this interface should not be domain specific. Use query/sql string or criteria API, or LINQ !!!
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="status"></param>
/// <returns></returns>
//IList<TEntity> GetByStatus<TEntity>(WishStatus status);
//IList<Wish> GetWishByLinq();
IQueryable<TEntity> GetLinq<TEntity>();
/// <summary>
/// Reattaches a detached entity to the current session.
/// </summary>
/// <param name="entity">The entity to reattach.</param>
void Reattach(object entity);
/// <summary>
/// Detaches an attached entity from the current session. Removes it from the cache (evict).
/// </summary>
/// <param name="entity">The entity to detach.</param>
void Detach(object entity);
/// <summary>
/// Inserts or updates the given entities to the database.
/// </summary>
/// <param name="entities">The entities to insert or update.</param>
void Save(params object[] entities);
/// <summary>
/// Deletes the specified entities from the database.
/// </summary>
/// <param name="entities">The entities to delete.</param>
void Delete(params object[] entities);
/// <summary>
/// Begins a new transaction.
/// </summary>
/// <returns>Returns a general transaction scope. Can be used in "using() { .Commit(); }" semantic. </returns>
ITransactionScope BeginTransaction();
/// <summary>
/// Determines whether there is an active transaction.
/// </summary>
/// <returns>
/// <c>true</c> if transaction is active; otherwise, <c>false</c>.
/// </returns>
bool IsActiveTransaction();
/// <summary>
/// Commits the currently open transaction.
/// </summary>
void CommitTransaction();
/// <summary>
/// Rolls back the currently open transaction.
/// </summary>
void RollbackTransaction();
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace PartnerRe.NCP.Data.Contracts
{
/// <summary>
/// Custom transaction scope to abstract System.TransactionScope, NHibernate TransactionScope, or other into
/// independant transaction context.
/// This allows the "using() { .Commit(); }" semantic.
/// </summary>
public interface ITransactionScope : IDisposable
{
/// <summary>
/// Commits the transaction.
/// </summary>
void Commit();
/// <summary>
/// Rolls back the transaction.
/// </summary>
void Rollback();
}
}
The Generic implementation of NHibernate with this interface looks like:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using NHibernate;
using NHibernate.Cfg;
using NHibernate.Criterion;
using NHibernate.Linq;
using System.Reflection;
using System.IO;
using PartnerRe.NCP.Data.Contracts;
namespace PartnerRe.NCP.Data.NHibernate
{
/// <summary>
/// NHibernate implementation of data access layer.
/// This is an "expensive" object due to the containing NHibernate session factory!
/// </summary>
public class DatabaseContext : IDatabaseContext
{
/// <summary>
/// The NHibernate SessionFactory. Static.
/// </summary>
protected static ISessionFactory SessionFactory { get; set; }
/// <summary>
/// NHibenrate configuration instance. Static.
/// </summary>
protected static Configuration Configuration { get; set; }
/// <summary>
/// Current NHibernate session.
/// </summary>
protected ISession Session;
/// <summary>
/// Current NHibernate transaction.
/// </summary>
protected ITransaction Transaction;
/// <summary>
/// Initializes a new instance of the <see cref="DatabaseContext"/> class.
/// </summary>
public DatabaseContext() : this(false)
{
}
private void CriteriaApiWithNigel()
{
//ICriteria criteria = new EqPropertyExpression(fs.ENTRY_TYPE_COLUMN, xxx);
//NHibernate.
//this.Session.CreateCriteria<facebug.entities.Entry>().
}
/// <summary>
/// Initializes a new instance of the <see cref="DatabaseContext"/> class.
/// </summary>
/// <param name="createSchema">if set to <c>true</c> creates the database schema (tables).</param>
public DatabaseContext(bool createSchema)
{
if (Configuration == null)
{
throw new InvalidOperationException("must initialize expensive stuff (static factories) before creating instances");
}
// create session
this.Session = this.CreateSession();
}
/// <summary>
/// Initializes a new instance of the <see cref="DatabaseContext"/> class.
/// </summary>
/// <param name="session">The native NHibernate session.</param>
public static void Initialize(string configFile, string assemblyLocation)
{
// configure NHibernate using given config file
Configuration = new Configuration();
Configuration.Configure(configFile);
// look for NHibernate mappings
if (!string.IsNullOrEmpty(assemblyLocation))
{
foreach (Assembly assembly in AssemblyReflectionHelper
.GetAssembliesWithAttribute<HbmContainerAssemblyAttribute>(
new DirectoryInfo(assemblyLocation),
"*"))
{
Configuration.AddAssembly(assembly);
}
}
}
/// <summary>
/// Creates a NHiberntae Session to work with.
/// </summary>
/// <returns>An open NHibernate session </returns>
public ISession CreateSession()
{
return CreateSession(null);
}
/// <summary>
/// Creates a NHiberntae Session to work with.
/// </summary>
/// <param name="interceptor">Interceptor connected to the session</param>
/// <returns></returns>
public ISession CreateSession(IInterceptor interceptor)
{
if (interceptor != null)
{
return GetSessionFactory().OpenSession(interceptor);
}
this.Session = GetSessionFactory().OpenSession();
// make sure to be in the default catalog to make all the queries working.
// There is an issue when creating tables:
// JIRA Issue http://jira.nhibernate.org/browse/NH-1443
return this.Session;
}
/// <summary>
/// Gets the NHibernate session factory. Expensive object.
/// </summary>
/// <returns></returns>
protected ISessionFactory GetSessionFactory()
{
if (SessionFactory == null)
{
SessionFactory = Configuration.BuildSessionFactory();
}
return SessionFactory;
}
#region IDatabaseContext Members
/// <summary>
/// Gets all entities of given type.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <returns>The list of entities.</returns>
public IList<TEntity> GetAll<TEntity>()
{
return Session
.CreateCriteria(typeof(TEntity))
.List<TEntity>();
}
/// <summary>
/// Gets all entities of a given type ordered ascending or descending by the given property.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="orderBy">The property the entities are ordered by.</param>
/// <param name="ascending">if set to <c>true</c> [ascending].</param>
/// <returns>The list of ordered entities.</returns>
public IList<TEntity> GetAll<TEntity>(string orderBy, bool ascending)
{
return Session
.CreateCriteria(typeof(TEntity))
.AddOrder(new Order(orderBy, ascending))
.List<TEntity>();
}
/// <summary>
/// Gets all entities according to the given simple filter.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="propertyName">Name of the property.</param>
/// <param name="value">The value of the property.</param>
/// <returns></returns>
public IList<TEntity> GetFiltered<TEntity>(string propertyName, object value)
{
return Session
.CreateCriteria(typeof(TEntity))
.Add(Expression.Eq(propertyName, value))
.List<TEntity>();
}
/// <summary>
/// HACK: this interface should not be domain specific. Use query/sql string or criteria API, or LINQ !!!
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="status"></param>
/// <returns></returns>
//public IList<TEntity> GetByStatus<TEntity>(WishStatus status)
//{
// return Session
// .CreateCriteria(typeof(TEntity))
// .Add(Expression.Eq("Status", WishStatus.Completed))
// .List<TEntity>();
//}
//public IList<Wish> GetWishByLinq()
//{
// return this.Session
// .Linq<Wish>()
// .Where<Wish>((e) => e.Status == WishStatus.InProgress)
// .OrderBy<Wish, string>(w => w.Title)
// .ToList();
//}
public IQueryable<TEntity> GetLinq<TEntity>()
{
return this.Session.Linq<TEntity>();
}
/// <summary>
/// Loads the entity with the given identifier.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="dbId">The db id.</param>
/// <returns>The loaded entitiy.</returns>
public TEntity GetById<TEntity>(object dbId)
{
return Session.Get<TEntity>(dbId);
}
/// <summary>
/// Reattaches a detached entity to the current session.
/// </summary>
/// <param name="entity">The entity to reattach.</param>
public void Reattach(object entity)
{
Session.Lock(entity, LockMode.None);
}
/// <summary>
/// Detaches an attached entity from the current session. Removes it from the cache (evict).
/// </summary>
/// <param name="entity">The entity to detach.</param>
public void Detach(object entity)
{
if (Session.IsDirty())
{
throw new InvalidOperationException("Do not detach on dirty session. Commit first.");
}
Session.Evict(entity);
}
/// <summary>
/// Inserts or updates the given entities to the database.
/// </summary>
/// <param name="entities">The entities to insert or update.</param>
public void Save(params object[] entities)
{
if (entities.Length == 0) throw new ArgumentNullException("entities", "must pass entities to persist");
foreach (object entity in entities)
{
Session.SaveOrUpdate(entity);
}
}
/// <summary>
/// Deletes the specified entities from the database.
/// </summary>
/// <param name="entities">The entities to delete.</param>
public void Delete(params object[] entities)
{
if (entities.Length == 0) throw new ArgumentNullException("entities", "must pass entities to delete");
foreach (object entity in entities)
{
Session.Delete(entity);
}
}
/// <summary>
/// Begins a new transaction.
/// </summary>
/// <returns>
/// Returns a general transaction scope. Can be used in "using() { .Commit(); }" semantic.
/// </returns>
public ITransactionScope BeginTransaction()
{
if (this.Transaction != null && this.Transaction.IsActive)
{
throw new InvalidOperationException("nested transactions are not supported. Close open transaction before doing this");
}
this.Transaction = Session.BeginTransaction();
return new TransactionScope(this.Transaction);
}
/// <summary>
/// Commits the currently open transaction.
/// </summary>
public void CommitTransaction()
{
this.Transaction.Commit();
}
/// <summary>
/// Rolls back the currently open transaction.
/// </summary>
public void RollbackTransaction()
{
this.Transaction.Rollback();
}
/// <summary>
/// Determines whether there is an active transaction.
/// </summary>
/// <returns>
/// <c>true</c> if transaction is active; otherwise, <c>false</c>.
/// </returns>
public bool IsActiveTransaction()
{
if (this.Transaction != null && this.Transaction.IsActive)
{
return true;
}
return false;
}
#endregion
}
}
Here is an example of how an extension may look like:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace PartnerRe.NCP.Data.NHibernate
{
public static class DatabasePivotExtension
{
public static IQueryable<T> Pivot<T>(this IQueryable<T> q)
{
return q;
}
}
}
To use this in the business logic you first need to create a database session. This session can then be used in Linq queries. Linq builds up an object graph that is loosely coupled from the data access layer that then uses NHibernate as an ORM to persist data to the database. Here’s an example including the Linq Extention
wishList = this.databaseContext
.GetLinq<Wish>()
.Where<Wish>((w) => w.Status == WishStatus.InProgress)
.OrderBy<Wish, string>(w => w.Title)
.Pivot<Wish>()
.ToList();
8<---
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DAL;
using Entities;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
private IDatabaseContext databaseContext = new DatabaseContext();
private IList<Wish> wishList;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
wishList = this.databaseContext.GetAll<Wish>();
this.dataGridView1.DataSource = wishList;
}
private void button1_Click(object sender, EventArgs e)
{
int start = Environment.TickCount;
//wishList = this.databaseContext.GetByStatus<Wish>(WishStatus.InProgress);
//wishList = this.databaseContext.GetWishByLinq();
wishList = this.databaseContext
.GetLinq<Wish>()
.Where<Wish>((w) => w.Status == WishStatus.InProgress)
.OrderBy<Wish, string>(w => w.Title)
.Pivot<Wish>()
.ToList();
MessageBox.Show((Environment.TickCount - start).ToString(), "Loading took");
this.dataGridView1.DataSource = wishList;
}
}
}
This is a very powerful method of building up a data access layer. The ORM does have an overhead that needs to be tested. For bulk inserts other methods such as ADO.NET Bulkcopy may be more appropriate.
