Printed from www.rmfusion.com A Developer website designed for Developers

NHibernate Custom Queries Code Review

Code Download

Define Person, Role and Salary Classes

Define the Person class which maps to the Person database table.

public class Person { private Int32 m_id; private Int32 m_idRole; private String m_lastName; private String m_firstName; private Role m_role; private IList<Salary> m_salary = new List<Salary>();
public Person() { }
public virtual Role Role { get { return this.m_role; } set { this.m_role = value; } } public virtual Int32 ID { get { return this.m_id; } set { this.m_id = value; } } public virtual Int32 IDRole { get { return this.m_idRole; } set { this.m_idRole = value; } } public virtual String LastName { get { return this.m_lastName; } set { this.m_lastName = value; } } public virtual String FirstName { get { return this.m_firstName; } set { this.m_firstName = value; } } public virtual IList<Salary> Salaries { get { return this.m_salary; } set { this.m_salary = value; } } }

The object relational mapping of the Person object to the Person database table is handled in the XML Mapping file.

The IList collection is used by NHibernate to store a collection of Salary objects. In the Person class, one Person object can be linked to many Salary objects. The Role object represents the role to which the person is associated to.

Define the Role class which maps to the Role database table.

public class Role { private Int32 m_id; private String m_roleDescription; private IList<Person> m_people = new List<Person>();
public Role() { }
public virtual Int32 ID { get { return this.m_id; } set { this.m_id = value; } } public virtual IList<Person> People { get { return this.m_people; } set { this.m_people = value; } } public virtual String RoleDescription { get { return this.m_roleDescription; } set { this.m_roleDescription = value; } } }

The object relational mapping of the Role object to the Role database table is handled in the XML Mapping file.

The IList collection is used by NHibernate to store a collection of Person objects. In the Role class, one Role object can be associated with many Person objects.

Define the Salary class which maps to the Salary database table.

public class Salary { private Int32 m_id; private Int32 m_year; private Double m_salaryYear; private Person m_person;
public virtual Int32 ID { get { return this.m_id; } set { this.m_id = value; } } public virtual Person Person { get { return this.m_person; } set { this.m_person = value; } } public virtual Int32 Year { get { return this.m_year; } set { this.m_year = value; } } public virtual Double SalaryYear { get { return this.m_salaryYear; } set { this.m_salaryYear = value; } } }

The object relational mapping of the Salary object to the Salary database table is handled in the XML Mapping file.

The Person object represents the person to which the salary is linked to.

Mapping the Class and Database Objects Using XML Mapping File

The following XML elements are used to define the key objects in the database model:

The below XML is stored in the Person.hbm.xml file and is used to specify mapping between the Person data model of the database and the Person application object model.

<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true" namespace="PersonNHibernateStoredProc_2_0" assembly="PersonNHibernateStoredProc_2_0">
<class name="Person" table="Person" lazy="true"> <id name="ID"> <column name="ID" /> <generator class="increment" /> </id>
<many-to-one name="Role" class="Role" column="IDRole" cascade="save-update"/>
<bag name="Salaries" cascade="all" lazy="true" inverse="true"> <key column="IDPerson" /> <one-to-many class="Salary" /> <loader query-ref="AllSalariesForPerson"/> </bag>
<property name="LastName" column="LastName" /> <property name="FirstName" column="FirstName" />
<sql-insert>exec [nhibInsertPerson] ?,?,?,?</sql-insert>
<sql-update check="none">exec [nhibUpdatePerson] ?,?,?,?</sql-update>
<sql-delete check="none">exec [nhibDeletePerson] ?</sql-delete> </class>
<sql-query name ="AllPeopleData"> <return class="Person" /> exec [nhibGetAllPeople] </sql-query> <sql-query name="AllSalariesForPerson"> <load-collection alias="Salaries" role="Person.Salaries" /> exec [nhibGetSalariesByPersonId] ? </sql-query> <sql-query name ="GetPersonByLastName"> <return class="Person" /> exec [nhibGetPersonByLastName] @LastName=:LastName </sql-query> <sql-query name ="GetPersonByIDPerson"> <return class="Person" /> exec [nhibGetPersonByID] @IDPerson=:IDPerson </sql-query> <sql-query name="ApplyDatabaseReset"> exec [hibResetData] </sql-query> </hibernate-mapping>

The below XML is stored in the Role.hbm.xml file and is used to specify mapping between the Role data model of the database and the Role application object model.

<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true" namespace="PersonNHibernateStoredProc_2_0" assembly="PersonNHibernateStoredProc_2_0">
<class name="Role" table="Role" lazy="true">
<id name="ID"> <column name="id"/> <generator class="increment" /> </id>
<bag name="People" table="Person" cascade="all" inverse="true"> <key column="IDRole" /> <one-to-many class="Person" /> <loader query-ref="AllPeopleForRole"/> </bag>
<property name="RoleDescription" column="RoleDescription" />
<loader query-ref="GetRoleByRoleID"/>
<sql-insert>exec [nhibInsertRole] ?,?</sql-insert>
<sql-delete check="none">exec [nhibDeleteRole] ?</sql-delete> </class>
<sql-query name="GetRoleByRoleID"> <return class="Role"/> exec [nhibGetRoleByRoleId] ? </sql-query> <sql-query name="AllRoleData"> <return class="Role"/> exec [nhibGetAllRoles] </sql-query> <sql-query name="AllPeopleForRole"> <load-collection alias="People" role="Role.People" /> exec [nhibGetPeopleByRoleId] ? </sql-query> <sql-query name="GetRoleByRoleDescription"> <return class="Role"/> exec [nhibGetRoleByDescription] @RoleDescription=:RoleDescription </sql-query> <sql-query name="GetRoleByRoleDescriptionAndID"> <return class="Role"/> exec [nhibGetRoleByDescriptionAndByID] @RoleDescription=:RoleDescription, @IDRole=:IDRole </sql-query> </hibernate-mapping>

The below XML is stored in the Salary.hbm.xml file and is used to specify mapping between the Salary data model of the database and the Salary application object model.

<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true" namespace="PersonNHibernateStoredProc_2_0" assembly="PersonNHibernateStoredProc_2_0">
<class name="Salary" table="Salary" lazy="true">
<id name="ID"> <column name="ID"/> <generator class="increment" /> </id>
<many-to-one name="Person" class="Person" column="IDPerson" cascade="save-update" />
<property name="Year" column="Year" /> <property name="SalaryYear" column="SalaryYear" />
<sql-insert>exec [nhibInsertSalary] ?,?,?,?</sql-insert>
<sql-delete check="none">exec [nhibDeleteSalary] ?</sql-delete> </class>
<sql-query name ="GetSalaryByValueAndByID"> <return class="Salary" /> exec [nhibGetSalaryByValueAndID] @SalaryYear=:SalaryYear, @ID=:SalaryID </sql-query> </hibernate-mapping>

Define NHibernate Application Configuration

The following XML elements are used to configure the NHibernate ISessionFactory object:

<?xml version="1.0" encoding="utf-8" ?> <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2"> <session-factory> <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property> <property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property> <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property> <property name="connection.connection_string">Server=SQLEXPRESS;Initial Catalog=Foo;Integrated Security=SSPI</property> <property name="hibernate.show_sql">False</property> <mapping assembly="PersonNHibernateStoredProc_2_0" /> </session-factory> </hibernate-configuration>

Manipulate the Database using NHibernate

Define the application static variables, including the NHibernate ISessionFactory object.

class Program { private static ISessionFactory m_sessionFactory = null;
private static Int32 m_IDKNash = Int32.MinValue; private static Int32 m_IDJBloggs = Int32.MinValue; private static Int32 m_IDAdmin = Int32.MinValue; private static Int32 m_IDAnalyst = Int32.MinValue; private static Int32 m_SalIDGrant = Int32.MinValue;

Initialise the NHibernate ISessionFactory object and load the NHibernate mapping configuration.

public static ISessionFactory factory { get { if (m_sessionFactory == null) { m_sessionFactory = new Configuration() .Configure() .BuildSessionFactory(); } return m_sessionFactory; } }

The static Main() method defines various NHibernate functions to be applied against the database.

static void Main(string[] args) { log4net.Config.XmlConfigurator.Configure();
AddPersonWithNewRoleAndSalary("Administrator"); AddRole(); AddPersonWithRoleAndSalary("Analyst"); AddSalaryForPerson();
GetPersonData(); UpdatePersonData("Manager");
GetPersonData(); GetRoleData(); GetPersonSalaryData();
DeletePerson(); DeleteRoleAndPeople("Analyst"); DeleteRole("Administrator"); DeleteSalary();
Console.Read(); }

Load the configuration settings from App.config file for the Log4Net component, which is used for logging NHibernate database activity.

Create new Person, Role and Salary objects and store these objects in the relevant tables in the database.

static void AddPersonWithNewRoleAndSalary(String roleDesc) { try { using (ISession session = factory.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { Role role = new Role(); role.RoleDescription = "Administrator";
Person person = new Person(); person.FirstName = "Kevin"; person.LastName = "Nash"; person.Role = role;
Salary salary = new Salary(); salary.Year = 2006; salary.SalaryYear = 12000; salary.Person = person;
person.Salaries.Add(salary);
session.Save(person); transaction.Commit();
m_IDKNash = person.ID; m_IDAdmin = role.ID; } } Console.WriteLine("Kevin Nash (Person) has been added."); } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Insert a new record into the Person, Role and Salary database tables within a database transaction, using the configured database stored procedures.

The Save() method is invoked in the Session object, however, the records are not appended to the database until the Commit() method is invoked on the Transaction object. Retrieve and store the identifer values for newly created Person and Role database records.

Create a new Role object and store this object in the Role table in the database.

static void AddRole() { try { Role role = new Role(); role.RoleDescription = "Analyst";
using (ISession session = factory.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { session.Save(role); transaction.Commit(); m_IDAnalyst = role.ID; } } Console.WriteLine("Analyst (Role) has been added."); } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Insert a new record into the Role database table within a database transaction, using the configured database stored procedures.

The Save() method is invoked in the Session object, however, the record is not appended to the database until the Commit() method is invoked on the Transaction object.

Create a new Person and Salary object, associating the Person object to a existing Role object, and store these created objects in the relevant tables in the database.

static void AddPersonWithRoleAndSalary(String roleDesc) { try { using (ISession session = factory.OpenSession()) { IQuery query = session.GetNamedQuery("GetRoleByRoleDescriptionAndID") .SetDecimal("IDRole", m_IDAnalyst) .SetString("RoleDescription", @roleDesc); Role r = (Role)query.UniqueResult();
using (ITransaction transaction = session.BeginTransaction()) { Person person = new Person(); person.FirstName = "Joe"; person.LastName = "Bloggs"; person.Role = r;
Salary salary = new Salary(); salary.Year = 2008; salary.SalaryYear = 7000; salary.Person = person;
person.Salaries.Add(salary); session.Save(person); transaction.Commit();
m_IDJBloggs = person.ID; } Console.WriteLine("Joe Bloggs (Person) has been added."); } } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Use a NHibernate named query to retrieve the analyst role record from the Role database table, using the configured database stored procedure. Store the result in a Role object.

Insert a new record into the Person and Salary database tables within a database transaction. The Save() method is invoked in the Session object, however, the records are not appended to the database until the Commit() method is invoked on the Transaction object.

Retrieve and store the identifer value for the newly created Person database record.

Create a new Salary object, associating the Salary object to a existing Person object, and store the created object in the Salary table in the database.

static void AddSalaryForPerson() { try { using (ISession session = factory.OpenSession()) { IQuery query = session.GetNamedQuery("GetPersonByLastName") .SetString("LastName", "Grant"); Person p = (Person)query.UniqueResult();
using (ITransaction transaction = session.BeginTransaction()) { Salary salary = new Salary(); salary.Year = 2007; salary.SalaryYear = 13500; salary.Person = p;
session.Save(salary); transaction.Commit();
m_SalIDGrant = salary.ID; } } Console.WriteLine("Salary for Mary Grant has been added.\n"); } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Use a NHibernate named query to retrieve the person record from the Person database table, using the configured database stored procedure. Store the result in a Person object.

Insert a new record into the Salary database table within a database transaction. The Save() method in the Session object, however, the records are not appended to the database until the Commit() method is invoked on the Transaction object. Retrieve and store the identifer value for the newly created Salary database record.

Modify the role associated with a particular person in the database.

static void UpdatePersonData(String roleDesc) { try { using (ISession session = factory.OpenSession()) { IQuery query = session.GetNamedQuery("GetPersonByLastName") .SetString("LastName", "Nash"); Person p = (Person)query.UniqueResult();
IQuery query2 = session.GetNamedQuery("GetRoleByRoleDescription") .SetString("RoleDescription", @roleDesc); Role r = (Role)query2.UniqueResult();
using (ITransaction transaction = session.BeginTransaction()) { p.Role = r; session.Save(p); transaction.Commit(); } } } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Use two different NHibernate named queries to retrieve the person record from the Person database table, and the role record from the Role database table, using the configured database stored procedures. Store the results in a Person and Role object respectively.

Assign the role object to the Person role property and invoke the Save() method in the Session object. To save the changes invoke the Commit() method in the Transaction object.

Retrieve all the records from the Person database table, together with the corresponding roles in the Role database table.

static void GetPersonData() { try { using (ISession session = factory.OpenSession()) { IQuery query = session.GetNamedQuery("AllPeopleData"); IList<Person> result = query.List<Person>();
foreach (Person row in result) { Console.WriteLine("Full Name: {0} {1} Role: {2}",row.FirstName, row.LastName, row.Role.RoleDescription); } } } catch (Exception ex) { Console.WriteLine(ex.Message); } Console.WriteLine(); }

Use a NHibernate named query to retrieve a list of all Person objects, with the corresponding roles, from the database using the configured database stored procedures.

Retrieve all the records from the Role database table, and display the associated people assigned to the role.

static void GetRoleData() { try { using (ISession session = factory.OpenSession()) { IQuery query = session.GetNamedQuery("AllRoleData"); IList<Role> result = query.List<Role>();
foreach (Role row in result) { Console.WriteLine("Role ID: {0} RoleName: {1}", row.ID, row.RoleDescription); Console.WriteLine(new String('=', 30));
if (row.People.Count > 0) { foreach (Person person in row.People) { Console.WriteLine("{0},{1}", person.LastName, person.FirstName); } } else { Console.WriteLine("No people found."); } Console.WriteLine(); } } } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Use a NHibernate named query to retrieve a list of all Role objects from the database, using the configured database stored procedures. Store the result in an IEnumberable collection object.

Iterate through the Role collection and for each Role object iterate through the corresponding Person persistent collection.

Retrieve all the records from the Person database table, and display the role and salaries associated with the person.

static void GetPersonSalaryData() { try { using (ISession session = factory.OpenSession()) { IQuery query = session.GetNamedQuery("AllPeopleData"); IList<Person> result = query.List<Person>();
foreach (Person row in result) { Console.WriteLine("Full Name: {0} {1} Role: {2}",row.FirstName, row.LastName, row.Role.RoleDescription); Console.WriteLine(new String('=', 45));
if (row.Salaries.Count > 0) { foreach (Salary salary in row.Salaries) { Console.WriteLine("{0},{1}", salary.Year, salary.SalaryYear); } } else { Console.WriteLine("No salaries found."); } Console.WriteLine(); } } } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Use a NHibernate named query to retrieve a list of all Person objects from the database, using the configured database stored procedures. Store the result in an IEnumberable collection object.

Iterate through the Person collection and for each Person object iterate through the corresponding Salary persistent collection.

Delete a single person record and all associated salary records from the Person and Salary tables in the database.

static void DeletePerson() { try { using (ISession session = factory.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { IQuery query = session.GetNamedQuery("GetPersonByIDPerson") .SetDecimal("IDPerson", m_IDKNash); Person p = (Person)query.UniqueResult();
session.Delete(p); transaction.Commit(); } } Console.WriteLine("Kevin Nash (IDPerson={0}) has been deleted.", m_IDKNash.ToString()); } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Use a NHibernate named query to retrieve the person record from the Person database table, using the configured database stored procedures. Store the result in a Person object.

The Delete() method is invoked in the Session object, however, the records are not deleted from the database until the Commit() method is invoked on the Transaction object.

NHibernate will perform a cascading delete and all corresponding Salary records, and any unassigned Role records will also be deleted using the configured database stored procedures.

Delete a single role record and all associated people and salary records from the Person and Salary tables in the database.

static void DeleteRoleAndPeople(String roleDesc) { try { using (ISession session = factory.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { IQuery query = session.GetNamedQuery("GetRoleByRoleDescriptionAndID") .SetString("RoleDescription", @roleDesc) .SetDecimal("IDRole", m_IDAnalyst); Role r = (Role)query.UniqueResult();
session.Delete(r); transaction.Commit(); } } Console.WriteLine("{0} (IDRole={1}) has been deleted.", roleDesc, m_IDAnalyst.ToString()); } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Use a NHibernate named query to retrieve the role record from the Role database table. Store the result in a Role object.

The Delete() method is invoked in the Session object, however, the records are not deleted from the database until the Commit() method is invoked on the Transaction object.

NHibernate will perform a cascading delete and all associated Person records, and all corresponding Salary records will also be deleted using the configured database stored procedures.

Delete a single role record and all associated people and salary records from the Person and Salary tables in the database.

static void DeleteRole(String roleDesc) { try { using (ISession session = factory.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { IQuery query = session.GetNamedQuery("GetRoleByRoleDescriptionAndID") .SetString("RoleDescription", @roleDesc) .SetDecimal("IDRole", m_IDAdmin); Role r = (Role)query.UniqueResult();
session.Delete(r); transaction.Commit(); } } Console.WriteLine("{0} (IDRole={1}) has been deleted.", roleDesc, m_IDAdmin.ToString()); } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Use a NHibernate named query to retrieve the role record from the Role database table, using the configured database stored procedures. Store the result in a Role object.

The Delete() method is invoked in the Session object, however, the records are not deleted from the database until the Commit() method is invoked on the Transaction object.

NHibernate will perform a cascading delete and all associated Person records, and all corresponding Salary records will also be deleted using the configured database stored procedures.

Delete a single salary record from the Salary table in the database.

static void DeleteSalary() { try { using (ISession session = factory.OpenSession()) { using (ITransaction transaction = session.BeginTransaction()) { IQuery query = session.GetNamedQuery("GetSalaryByValueAndByID") .SetDecimal("SalaryYear", 13500) .SetDecimal("SalaryID", m_SalIDGrant); Salary s = (Salary)query.UniqueResult();
session.Delete(s); transaction.Commit(); } } Console.WriteLine("Salary (Salary ID={0}) has been deleted.", m_SalIDGrant.ToString()); } catch (Exception ex) { Console.WriteLine(ex.Message); } }

Use a NHibernate named query to retrieve the salary record from the Salary database table, using the configured database stored procedure. Store the result in a Salary object.

The Delete() method is invoked in the Session object, however, the record is not deleted from the database until the Commit() method is invoked on the Transaction object.