NHibernate
A sample repository for product. In practice, derive this from GenericRespository. Construct it with a session from SessionManager.
In addition to what's in a generic repository, you'll want paged lists from the foreign keys. Here there are overloads for both the foreign key entity (category) and the raw key (Id)- the output SQL is identical, but the criteria API makes it very flexible and easy. Note from NHibernate 2.0, Expression.Eq can be replaced by Restrictions.Eq.
See notes on HQL/ICriteria
using System;
using System.Collections.Generic;
using NHibernate;
using NHibernate.Criterion;
namespace Northwind.Repositories
{
/// <summary>
/// A Product repository.
/// </summary>
/// <remarks>
/// All operations are wrapped with <see cref="TransactionRequired"/>. If there is NO open transaction, they open a transaction and commit immediately. If there is an open transaction, nothing is commited, so you should commit at a higher level.
/// </remarks>
public class ProductRepository
{
public ProductRepository(ISession session)
{
_session = session;
}
readonly ISession _session;
private ISession Session
{
get { return _session; }
}
#region Read
/// <summary>
/// Loads a proxy object with the specified id. Throws an exception if not in database.
/// </summary>
public Product LoadProxy(int id)
{
using (TransactionRequired transaction = new TransactionRequired(Session))
{
return Session.Load<Product>(id);
}
}
/// <summary>
/// Gets the Id. Returns null if there is no matching row
/// </summary>
public Product GetById(int id)
{
using (TransactionRequired transaction = new TransactionRequired(Session))
{
return Session.Get<Product>(id);
}
}
/// <summary>
/// Finds all records. Consider <see cref="FindPage"/> for large result sets.
/// </summary>
public ICollection<Product> FindAll()
{
using (TransactionRequired transaction = new TransactionRequired(Session))
{
return Session.CreateCriteria(typeof(Product)).List<Product>();
}
}
/// <summary>
/// Counts the number of records.
/// </summary>
public int Count()
{
ICriteria criteria = Session.CreateCriteria(typeof(Product));
using (TransactionRequired transaction = new TransactionRequired(Session))
{
return criteria.SetProjection(NHibernate.Criterion.Projections.RowCount()).UniqueResult<int>();
}
}
/// <summary>
/// Finds records by page.
/// </summary>
/// <param name="pageStartRow">The page start row.</param>
/// <param name="pageSize">Size of the page.</param>
public IList<Product> FindPage(int pageStartRow, int pageSize)
{
ICriteria criteria = Session.CreateCriteria(typeof(Product));
criteria.SetFirstResult(pageStartRow);
criteria.SetMaxResults(pageSize);
using (TransactionRequired transaction = new TransactionRequired(Session))
{
return criteria.List<Product>();
}
}
/// <summary>
/// Finds records by page, sorted.
/// </summary>
public IList<Product> FindSortedPage(int pageStartRow, int pageSize, string sortBy, bool descending)
{
ICriteria criteria = Session.CreateCriteria(typeof(Product));
if (descending)
criteria.AddOrder(NHibernate.Criterion.Order.Desc(sortBy));
else
criteria.AddOrder(NHibernate.Criterion.Order.Asc(sortBy));
criteria.SetFirstResult(pageStartRow);
criteria.SetMaxResults(pageSize);
using (TransactionRequired transaction = new TransactionRequired(Session))
{
return criteria.List<Product>();
}
}
#endregion
#region Update
/// <summary>
/// Saves the specified object within a transaction.
/// </summary>
public void Save(Product entity)
{
using (TransactionRequired transaction = new TransactionRequired(Session))
{
Session.Save(entity);
transaction.Commit(); //flush to database
}
}
/// <summary>
/// Saves the specified object within a transaction.
/// </summary>
public void SaveOrUpdate(Product entity)
{
using (TransactionRequired transaction = new TransactionRequired(Session))
{
Session.SaveOrUpdate(entity);
transaction.Commit(); //flush to database
}
}
/// <summary>
/// Deletes the specified object within a transaction.
/// </summary>
public void Delete(Product entity)
{
using (TransactionRequired transaction = new TransactionRequired(Session))
{
Session.Delete(entity);
transaction.Commit(); //flush to database
}
}
#endregion
#region Relations
#region Supplier
public IList<Product> ProductBySupplier(Supplier supplier, int pageStartRow, int pageSize)
{
var criteria = CriteriaSupplier(supplier)
.SetFirstResult(pageStartRow)
.SetMaxResults(pageSize);
return criteria.List<Product>();
}
public IList<Product> ProductBySupplier(int supplierId, int pageStartRow, int pageSize)
{
var criteria = CriteriaSupplierId(supplierId)
.SetFirstResult(pageStartRow)
.SetMaxResults(pageSize);
return criteria.List<Product>();
}
public int ProductBySupplierCount(Supplier supplier)
{
var criteria = CriteriaSupplier(supplier);
return criteria.SetProjection(NHibernate.Criterion.Projections.RowCount()).UniqueResult<int>();
}
public int ProductBySupplierCount(int supplierId)
{
var criteria = CriteriaSupplierId(supplierId);
return criteria.SetProjection(NHibernate.Criterion.Projections.RowCount()).UniqueResult<int>();
}
#region CriteriaBuilding
private ICriteria CriteriaSupplierId(int supplierId)
{
ICriteria criteria = Session.CreateCriteria(typeof(Product));
criteria.CreateCriteria("Supplier")
.Add(Expression.Eq("Id", supplierId));
return criteria;
}
private ICriteria CriteriaSupplier(Supplier supplier)
{
return Session.CreateCriteria(typeof(Product))
.Add(Expression.Eq("Supplier", supplier));
}
#endregion
#endregion
#region Category
public IList<Product> ProductByCategory(Category category, int pageStartRow, int pageSize)
{
var criteria = CriteriaCategory(category)
.SetFirstResult(pageStartRow)
.SetMaxResults(pageSize);
return criteria.List<Product>();
}
public IList<Product> ProductByCategory(int categoryId, int pageStartRow, int pageSize)
{
var criteria = CriteriaCategoryId(categoryId)
.SetFirstResult(pageStartRow)
.SetMaxResults(pageSize);
return criteria.List<Product>();
}
public int ProductByCategoryCount(Category category)
{
var criteria = CriteriaCategory(category);
return criteria.SetProjection(NHibernate.Criterion.Projections.RowCount()).UniqueResult<int>();
}
public int ProductByCategoryCount(int categoryId)
{
var criteria = CriteriaCategoryId(categoryId);
return criteria.SetProjection(NHibernate.Criterion.Projections.RowCount()).UniqueResult<int>();
}
#region CriteriaBuilding
private ICriteria CriteriaCategoryId(int categoryId)
{
ICriteria criteria = Session.CreateCriteria(typeof(Product));
criteria.CreateCriteria("Category")
.Add(Expression.Eq("Id", categoryId));
return criteria;
}
private ICriteria CriteriaCategory(Category category)
{
return Session.CreateCriteria(typeof(Product))
.Add(Expression.Eq("Category", category));
}
#endregion
#endregion
#endregion
}
}
Notes (HQL/ICriteria)
- ISession.Load/Get: For invalid ids, Get returns null, and Load returns a proxy but throws when you access properties or save.
Generally Get is more useful, but Load is useful when you know the id is valid and want lazy loading, or just to set an association without an extra sql select. - HQL named parameters have : (colon) prefix.
session.CreateQuery("from Customer cust where cust.Id = :id").SetParameter("id", "AFKI").List();
SetParameter works for entities too; otherwise use typespecific SetString, SetDate, SetEntity