Easily Setup Fluent Nhibernate With Oracle
page 2 of 3
by Muhammad Bilal Ashraf
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 32021/ 63
Article Contents:

Steps

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!


View Entire Article

User Comments

Title: ORM, fluent nhibernate, oracle   
Name: laura
Date: 2012-08-03 3:50:29 PM
Comment:
Excellent article, i wish i had found this ealier
Title: Solution   
Name: Bilal
Date: 2011-07-21 6:45:45 AM
Comment:
Well there are two Data Provier implementations for connecting to Oracle thru .Net Code.
1. Microsoft
2. Oracle
I have used Oracle Implementation and referenced Oracle Data Access for .NET http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html/

The Error is coming because you are referencing Microsoft DLL 'System.Data...'. You should use Oracle Provider DLL instead 'Oracle.DataAccess.Client' .
Title: Mr   
Name: Sudhir
Date: 2011-07-21 4:58:21 AM
Comment:
Same error coming as Mr Jason Lee is saying. Please resolve and update the code
Title: System.InvalidCastException:   
Name: Jason Lee
Date: 2011-03-22 9:31:51 AM
Comment:
Would you happen to know what causes this error. Most of the searching turns up responses for NH instead of FNH. The code seems to error on BuildSessionFactory()

Unable to cast object of type 'Oracle.DataAccess.Client.OracleConnection' to type 'System.Data.Common.DbConnection'.


Line 31: throw;
Line 32: }
Line 33: return c.BuildSessionFactory();
Line 34: }
Line 35: }






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-12-07 7:10:13 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search