1.
Before creating a project in visual studio, make sure NuGet is
installed. Create a new project.
2.
From Visual Studio right click on “References” and select “Add Library
Package Reference”. It will open Library Package Manger window. On selecting
“Online” you can search for dozens of online tools including log4net,
Nhibernate, elmah, Fluent Nhibernate etc. You can even create your own package
and publish it and it will be available from this library package manager. As
we are targeting Fluent NHibernate, so search for Fluent NHibernate and click
on install. This will install all the Fluent NHibarnate and all of its
dependencies in your project. For more information on NuGet, please see the
following post
by Scott Hanselman.
After adding the library
package, you will see all the references in your reference section. Behind the
scene NuGet adds a folder named packages in your project directory where it
places all the references along with the configuration. If you don’t want to
use NuGet you can download the binaries and refer them in your project by the
conventional method.
3.
Now add the connection string to talk to Oracle in your
web.config/app.config.
<connectionStrings>
<add name="default" connectionString=
"Data Source=tnsName;User Id=userId;Password=password;" />
</connectionStrings>
4.
Add a reference to ““Oracle.DataAccess” and set “Oracle.DataAccess”
reference property “Copy Local” to true.
5.
Create entities and mappings in your Entities or Model project. Suppose,
I have a person table named PERSON(Id,FirstName,LastName) , so I’ll create two
classes one for entity and one for its mapping.
Below is the entity and its
mapping.
public class Person
{
public virtual int Id { get; private set; }
public virtual string FirstName { get; set; }
public virtual string LastName { get; set; }
}
Mapping class will look like
this:
public class PersonMap : ClassMap<Person>
{
public PersonMap()
{
Table("PERSON");
//PERSON_SEQUENCE is the oracle sequence against the person table
Id(x => x.Id).GeneratedBy.Sequence("PERSON_SEQUENCE");
Map(x => x.FirstName);
Map(x => x.LastName);
}
}
6.
Write code to configure Fluent NHibernate
public class SessionFactoryHelper
{
public static ISessionFactory CreateSessionFactory()
{
var c = Fluently.Configure();
try
{
//Replace connectionstring and default schema
c.Database(OracleDataClientConfiguration.Oracle10.
ConnectionString(x =>
x.FromConnectionStringWithKey("default"))
.DefaultSchema("SchemaName"));
c.Mappings(m => m.FluentMappings.AddFromAssemblyOf< Person >());
}
catch (Exception ex)
{
throw;
}
return c.BuildSessionFactory();
}
}
7.
Write data access code now. Create a data repository interface and implement
it.
public interface IRepository<T>
{
void Save(T obj);
void Update(T obj);
void Delete(T obj);
T Load<T>(object id);
T GetReference<T>(object id);
IList<T> GetByProperty<T>(string property, object value);
IList<T> GetByHQL<T>(string hql);
IList<T> GetAll<T>();
IList<T> GetAllOrdered<T>(string propertyName,bool Ascending);
IList<T> Find<T>(IList<string> criteria);
void Detach(T item);
IList<T> GetAll<T>(int pageIndex, int pageSize);
void Commit();
void Rollback();
void BeginTransaction();
}
Here comes the implementation of the above repository
interface:
public class Repository<T> : IRepository<T>
{
private ISession session;
public Repository()
{
var sessionFactory = SessionFactoryHelper.CreateSessionFactory();
session = sessionFactory.OpenSession();
session.BeginTransaction();
}
public void Save(T obj)
{
session.Save(obj);
}
public void Update(T obj)
{
session.Update(obj);
}
public void Delete(T obj)
{
session.Delete(obj);
}
public T Load<T>(object id)
{
return session.Load<T>(id);
}
public T GetReference<T>(object id)
{
return session.Get<T>(id);
}
public IList<T> GetByHQL<T>(string hql)
{
var obj = session.CreateQuery(hql).List<T>();
return obj;
}
public IList<T> GetByProperty<T>(string property, object value)
{
StringBuilder hql = new StringBuilder();
hql.Append(string.Format("FROM {0} a ", typeof(T).FullName));
hql.Append(string.Format("WHERE a.{0} = ?", property));
var obj = session.CreateQuery(hql.ToString())
.SetParameter(0, value)
.List<T>();
return obj;
}
public IList<T> GetAll<T>(int pageIndex, int pageSize)
{
ICriteria criteria = session.CreateCriteria(typeof(T));
criteria.SetFirstResult(pageIndex * pageSize);
if (pageSize > 0)
{
criteria.SetMaxResults(pageSize);
}
return criteria.List<T>();
}
public IList<T> GetAll<T>()
{
return GetAll<T>(0, 0);
}
public IList<T> Find<T>(IList<string> strs)
{
IList<ICriterion> objs = new List<ICriterion>();
foreach (string s in strs)
{
ICriterion cr1 = Expression.Sql(s);
objs.Add(cr1);
}
ICriteria criteria = session.CreateCriteria(typeof(T));
foreach (ICriterion rest in objs)
session.CreateCriteria(typeof(T)).Add(rest);
criteria.SetFirstResult(0);
return criteria.List<T>();
}
public void Detach(T item)
{
session.Evict(item);
}
internal void Flush()
{
session.Flush();
}
public void Commit()
{
if (session.Transaction.IsActive)
{
session.Transaction.Commit();
}
}
public void Rollback()
{
if (session.Transaction.IsActive)
{
session.Transaction.Rollback();
session.Clear();
}
}
public void BeginTransaction()
{
Rollback();
session.BeginTransaction();
}
public IList<T> GetAllOrdered<T>(string propertyName, bool ascending)
{
Order cr1 = new Order(propertyName, ascending);
IList<T> objsResult = session.CreateCriteria
(typeof(T)).AddOrder(cr1).List<T>();
return objsResult;
}
}
That’s it. Go ahead and test it out.
public IList<Person> GetPersonList()
{
IRepository<Person> repo = new Repository<Person>();
var persons = repo.GetAll<Person>();
return persons;
}
Happy ORM Coding!