NHibernate and pre-defined queries

I have been wanting to update this BLOG for sometime now……..A lot of people have been enquiring me on my updates…..I have never felt so much pressure to release my updates as this one.

I had spent a weeks time to investigate more on LINQ and do some comparison on LINQ (vs) NHibernate which consumed some time. I’ll soon release my work on LINQ here, at the moment, it’s all NHibernate

Another way to fetch records from DB is using CreateSQLQuery, and write in your SQL query.

        public IList ListSalaryInfo()
        {        
            ISession session = NHibernateHelper.GetCurrentSession();
            ITransaction tx = session.BeginTransaction();
            IList lstSalary = session.CreateSQLQuery("SELECT * FROM SALARY").AddEntity(typeof(NHibWebApplication.Entity.Salary)).List();
            tx.Commit();
            NHibernateHelper.CloseSession();
            return lstSalary;
        }
        

Filter Options

Execute a sql query with filter option. In the example below we query Roles table to fetch records selectively. Please note that in real time scenarios, we hardly get to use “*”. Replace “*” with appropriate column names.

                public int GetRoleId(string sRole)
                {
                    Role role = null;
                    ISession session = NHibernateHelper.GetCurrentSession();
                    ITransaction tx = session.BeginTransaction();
                    role = new Role();
                    role.RoleName = sRole;
                    NHibernate.Expression.Example example = NHibernate.Expression.Example.Create(role).ExcludeProperty("Id");
                    IList lstRole = (List)session.CreateCriteria(typeof(Role)).Add(example).List();
                    if (lstRole.Count > 0)
                    {
                        role.Id = lstRole[0].Id;
                    }
                    //return role.Id;
                    return role.Id;
                }
            

Pre-Defined Queries

NHibernate allows execution of pre-defined queries. We can define queries in XML and execute it from our application. This will make our application look much cleaner. This is how we can do it

public IList ExecPreDefinedQuery()
        {
            Role role = new Role();
            ISession session = NHibernateHelper.GetCurrentSession();
            ITransaction tx = session.BeginTransaction();
            IList lstRole = (IList)session.GetNamedQuery("roles").SetInt32("roleId",1).List();
            return lstRole;
        }
        

In the example above, we have executed a query by name “roles” stored in the file Roles.xml.hbm. These queries can be stored in a common XML and referred in any hbm files. But I prefer to put across respective queries in appropriate hbm files. This would make things comfortable to edit, incase if the number of queries are less.

Stored Prcedures

Support for execution of procedure has been limited in the previous versions of Hibernate. After a lot of debate with internal community and challenges, I managed to execute it. Here’s the same of the code.

I have 2 sections of it. First, procedure without any parameters and Second, with parameter. In a similar fashion, we will be able to execute functions and cursors.

            public IList ExecProcedureWithNoParams()
            {
                Role role = new Role();
                ISession session = NHibernateHelper.GetCurrentSession();
                ITransaction tx = session.BeginTransaction();
                IList lstRole = (IList)session.GetNamedQuery("spRoles").List();
                return lstRole;
            }
        

Procedure with parameter

            public IList ExecProcedureWithParams()
            {
                ISession session = NHibernateHelper.GetCurrentSession();
                ITransaction tx = session.BeginTransaction();
                IList  lstRole = (IList)session.GetNamedQuery("spParamRoles").SetInt32(0, 2).List();
                return lstRole;
            }
        

I had defined these procedure names in my Roles.xml.hbm file. Snippet of the file is appended

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="NHibWebApplication.Entity.Role, NHibWebApplication" table="Role">
<id name="Id" column="Id" type="int">
<generator class="assigned" />
</id>
<property name="RoleName" column="Designation" type="String" length="50"/>
</class>
<sql-query name="roles">
<return alias="roles" class="NHibWebApplication.Entity.Role, NHibWebApplication"/>
SELECT Id, Designation from ROLE where Id = :roleId
</sql-query>
<sql-query name="spRoles">
<return alias="spRoles" class="NHibWebApplication.Entity.Role, NHibWebApplication"/>
Exec GetAllRoles
</sql-query>
<sql-query name="spParamRoles">
<return alias="spParamRoles" class="NHibWebApplication.Entity.Person, NHibWebApplication"/>
exec GetPersonInfo ?
</sql-query>
</hibernate-mapping>

Await more updates on filters, projection and relationship (one-to-one, one-to-many, many-to-one and many-to-many) samples