NHibernate Custom Queries Code Review

Manipulate the Database using NHibernate

Code Review

Code Walkthrough

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.

More Information

Code Review

Open Source

Share Content