Using NHibernate with PostgreSql in ASP.NET MVC: A tutorial
Author: Veeresh Rudrappa. Date: March 9, 2013
In my previous blog I showed how to set up NHibernate with PostgreSql. Now let us move on and build a small application
and learn how to do the NHibernate Mappings. I have come up with a small ASP.NET MVC 3 project just to illustrate
the usage of NHibernate. In the process you will also learn:
1. How to create Database, Tables, Schema and roles in PostgreSql
2. How create a MVC 3 project and use HTML helper forms.
3. How to map model objects and save them into Database
4. How to define one to many relationship in NHibernate.
With this blog and the previous one, you should be all set to build and develop any large scale project.
Step 1: Let us first start with writing a SQL script to create database and roles. Open your Notepad++ or any editor and create a file with .sql extension.
Copy the script below into the created file.
Let us create two roles, "veeresh" and "user1". We shall make veeresh as the absolute owner of the database giving him GRANT ALL privilege,
while "user1" role would be given GRANT CONNECT privilege. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up.
A role with the LOGIN attribute can be considered the same thing as a database user.
Let us also create a schema by name testdb which is nothing but a namespace and grant authorization to the owner veeresh.
For our ASP.NET application, we would be using "user1" to access and modify database objects. For now just "grant usage on" privilege for "user1".
As and when we create database tables we shall "grant usage" on those to "user1".
/*
* psql -U postgres -f create_testdb.sql
*
*/
CREATE ROLE veeresh LOGIN
PASSWORD 'veeresh123'
NOINHERIT CREATEDB
VALID UNTIL 'infinity';
CREATE ROLE user1 LOGIN
PASSWORD 'user1'
NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;
CREATE TABLESPACE testdb_data OWNER veeresh LOCATION E'c:\\TestDB\\data\\TestDB\\data';
CREATE TABLESPACE testdb_index OWNER veeresh LOCATION E'c:\\TestDB\\data\\TestDB\\index';
CREATE DATABASE testdb
WITH ENCODING='UTF8'
OWNER=veeresh
TABLESPACE=testdb_data;
GRANT ALL ON DATABASE testdb TO veeresh;
GRANT CONNECT ON DATABASE testdb TO user1;
\c testdb
CREATE SCHEMA testdb AUTHORIZATION veeresh;
GRANT USAGE ON SCHEMA testdb TO user1;
Step 2: Next let us create the Database tables. I Have come up with a simple design where in there will be a "Person" table and "Car" table. A "Person" can own any number of "Cars". Let the person have just a "name" attribute and let car have "model name" and "model year" as its attributes. Its a fairly simple design, but good enough to get you started.
CREATE TABLE testdb.persons
(
person_id bigserial NOT NULL,
person_name character varying(36) NOT NULL,
CONSTRAINT persons_pk PRIMARY KEY (person_id))
WITH (
OIDS=FALSE
);
ALTER TABLE testdb.persons OWNER TO veeresh;
GRANT ALL ON TABLE testdb.persons TO user1;
GRANT ALL ON TABLE testdb.persons TO user1;
ALTER TABLE testdb.persons_person_id_seq OWNER TO veeresh;
GRANT ALL ON TABLE testdb.persons_person_id_seq TO veeresh;
GRANT USAGE ON TABLE testdb.persons_person_id_seq TO user1;
CREATE TABLE testdb.cars
(
car_id bigserial NOT NULL,
model_name character varying(36) NOT NULL,
year integer,
person_id bigint,
CONSTRAINT cars_pk PRIMARY KEY (car_id),
CONSTRAINT person_fk FOREIGN KEY (person_id)
REFERENCES testdb.persons (person_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
OIDS=FALSE
);
ALTER TABLE testdb.cars OWNER TO veeresh;
GRANT ALL ON TABLE testdb.cars TO user1;
GRANT ALL ON TABLE testdb.cars TO user1;
ALTER TABLE testdb.cars_car_id_seq OWNER TO veeresh;
GRANT ALL ON TABLE testdb.cars_car_id_seq TO veeresh;
GRANT USAGE ON TABLE testdb.cars_car_id_seq TO user1;
Step 3 Now we would have to update our web.config with the correct connection string for our database.
Step 4: Let us now create the "DBService" class, "Person" class and "Car" class. Create a person folder under Models and add these class files inside that folder.
Of course feel free to add it anywhere, but make sure you update the namespace correctly if you are going to copy and paste my code.
The "DBService" class is needed to manage the NHibernate ISession, and it is here where the object is actually committed to the Database. And class object which wants to commit its
value into Database, would have to have to first create a service class of its own which would extend the "DBService" class.
There are different ways you could do this, and there are some good design patterns to maintain and manage NHIbernate sessions. I believe, the technique that I have used here is pretty basic and
just enough to get you started.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NHibernate;
namespace NHibernateTest.Models.person
{
public class DBService
{
private ISession _mSession;
public void SetSession(ISession session)
{
_mSession = session;
}
public void Update(Object obj)
{
using (var transaction = _mSession.BeginTransaction())
{
_mSession.Save(obj);
transaction.Commit();
}
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace NHibernateTest.Models.person
{
public class Person
{
#region public members
public virtual long Id { get; set; }
public virtual String Name { get; set; }
public virtual IList< Car> CarsOwned { get; set; }
#endregion
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace NHibernateTest.Models.person
{
public class Car
{
#region public members
public virtual long Id { get; set; }
public virtual String ModelName { get; set; }
public virtual Int32 Year { get; set; }
public virtual Person Person { get; set; }
#endregion
}
}
Now the Service methods for Person class and Car class.
namespace NHibernateTest.Models.person
{
public class PersonService : DBService
{
public void UpdateNewPerson(Person person)
{
Update(person);
}
// Add all your query methods here like fetch data, delete data.
}
}
namespace NHibernateTest.Models.person
{
public class CarService : DBService
{
public void UpdateNewCar(Car car)
{
Update(car);
}
// Add all your query methods here like fetch data, delete data.
}
}
Step 5: Finally, lets do the NHIbernate mappings.
a. class name = "Person" says which C# class you are mapping and table="testdb.persons" denotes the corresponding Database table.
b. Mapped classes must declare the primary key column of the database table. Most classes will also have a property holding the unique identifier of an instance. The < id > element defines the mapping from that property to the primary key column of the table in the database.
c. The < property > element is used to map a class property to a column in the database table
d. < one-to-many > tag links the tables of two classes.
e. Since the person table has a collection of cars, we would have to use a bag to link it up. When you query the database for a person, along with it the list of associated cars would also be fetched.
testdb.persons_person_id_seq
testdb.cars_car_id_seq
Step 6: Add the Controller, action method and create new objects.
I did not add any new controller, but just added a new action method on to the home controller to accept data from the view page.
The view page is sending in name of the person, name of two cars and their model Year. We'll create corresponding new objects, call their service methods and save them them in the Database.
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult Register(String name, String carModel1, Int32 year1, String carModel2, Int32 year2)
{
// Get the model services
CarService carService = getCarService();
PersonService personService = getPersonService();
// Create a new person and also save the object in data base
Person person = new Person();
person.Name = name;
personService.UpdateNewPerson(person);
IList< Car> cars = new List< Car>();
// Create a new Car and link it with the person and save into database
Car car1 = new Car();
car1.Person = person;
car1.ModelName = carModel1;
car1.Year = year1;
carService.UpdateNewCar(car1);
cars.Add(car1);
Car car2 = new Car();
car2.Person = person;
car2.ModelName = carModel2;
car2.Year = year2;
carService.UpdateNewCar(car2);
cars.Add(car2);
person.CarsOwned = cars;
return RedirectToAction("Index", "Home");
}
private CarService getCarService()
{
CarService carService = new CarService();
carService.SetSession(ApplicationCore.Instance.SessionFactory.OpenSession());
return carService;
}
private PersonService getPersonService()
{
PersonService personService = new PersonService();
personService.SetSession(ApplicationCore.Instance.SessionFactory.OpenSession());
return personService;
}
Step 7: Create a view page to test our application.
@{
ViewBag.Title = "Home Page";
}
@ViewBag.Message
@{
using (Html.BeginForm("Register", "Home", FormMethod.Post, new { id = "myForm" }))
{
@Html.TextBox("Name")
@Html.TextBox("CarModel1")
@Html.TextBox("Year1")
@Html.TextBox("CarModel2")
@Html.TextBox("Year2")
}
}
Check if the data has been stored in the database
