Are you working on an application that needs to connect to a database? Have you considered using NHibernate to do this? In this tutorial i am going to go though how to set up a simple console application to use NHibernate. We will create the entities and mappings for our database and I will show you how to insert, update and select data from your database. Using C# and the NHibernate library.
[wp_ad_camp_3]
Nuget package
Create a visual studio console application and import the following NuGet package
import Install-Package NHibernateWhat is NHibernate?
NHibernate is an object-relational mapping (ORM) solution for the Microsoft .NET platform. It provides a framework for mapping an object-oriented domain model to a traditional relational database. Its purpose is to relieve the developer from a significant portion of relational data persistence-related programming tasks. NHibernate is free as open source software that is distributed under the GNU Lesser General Public License. NHibernate is a port of Hibernate.
In plan English IMO NHibernate makes it easier for us to access the database.
Setting up NHibernate
Entities
The first thing I will do is create a class for each of my tables in the database. Note I am not creating the tables in the Database I am going to letNHibernate do this for me. Smart ha!.
public class Person
{
public virtual int ID { get; set; }
public virtual string LastName { get; set; }
public virtual string FirstName { get; set; }
public virtual Car Car { get; set; }
}
public class Car
{
public virtual int ID { get; set; }
public virtual string Make { get; set; }
public virtual string Model { get; set; }
public virtual string Year { get; set; }
}
So now we have two classes Person and Car. As you can see a person can own a car. So if we were to create this in the database our selves we would end up with a Person table and a Car table the Person table would then have a foreign key to the Car table.
[wp_ad_camp_5]
Mappings
Now we need to tell NHibernate how things map together. We do that by creating mappings for each table inheriting from the ClassMapping class.
public class PersonMapping : ClassMapping
{
public PersonMapping()
{
Id(s => s.ID, im => im.Generator(Generators.Identity)); // primary key mapping
Property(s => s.FirstName, pm => pm.NotNullable(true));
Property(s => s.LastName, pm => pm.NotNullable(true));
ManyToOne(s => s.Car, mom => mom.Cascade(Cascade.Persist));
}
}
Lets go though this step by step. The first Method I am calling in the constructor is ID this is how we define the primary key for our table. The first parameter is the name of the column in the Person class that is the primary key the second is how I would like to generate the new primary keys. I have chosen to allow the database to do this by declaring the Identity column.
The second method we are calling is used to define normal columns. It is called Property the first parameter is the name of the column in our person class the second is wither or not we want the column to be nullable.
The third method we are calling is used to define reference tables. It is used to define how we treat the parent child relationship. It is called ManyToOne it gives us our link to our car.
public class CarMapping : ClassMapping
{
public CarMapping()
{
Id(s => s.ID, im => im.Generator(Generators.Identity));
Property(s => s.Make, pm => pm.NotNullable(true));
Property(s => s.Model, pm => pm.NotNullable(true));
Property(s => s.Year, pm => pm.NotNullable(true));
}
}
And we did the same thing for our Car.
Configuration
Now we need to configure our connection to the database.
var configuration = new Configuration();
configuration.DataBaseIntegration(db =>
{
db.ConnectionString = @"Server=.\sqlexpress;initial catalog=NHibernateTest;Integrated Security=true";
db.Dialect();
db.Driver();
});
Followed by adding the mappings to the tables.
var modelMapper = new ModelMapper(); modelMapper.AddMapping(); modelMapper.AddMapping (); var mapping = modelMapper.CompileMappingForAllExplicitlyAddedEntities(); configuration.AddDeserializedMapping(mapping,"Test"); var schema = new SchemaExport(configuration);
The first time you run your code you will need to run the following command to create your database. If you run it again it will drop the database and recreate everything
schema.Execute(false, true, false);
Accessing the database
To access the database we run everything though a session transaction
using (var session = buildSessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
// Add code here
}
Insert into the database
To insert a row into the database we first create a new Person exactly how we would wish it to look including adding our Car class. NHibernate will handle creating the row in the car table followed by the person table and adding the foreign key as needed.
var person = new Person
{
FirstName = "Linda",
LastName = "Lawton",
Car = new Car { Make = "Chevrolet", Model = "Camaro" , Year = "1967"}
};
session.Save(person);
tx.Commit();
Select rows form the database
Requesting data from the database is as simple as using QueryOver to telling it which table we are requesting from and adding any where clause as needed.
var person = session.QueryOver().Where(p => p.FirstName == "Linda").List();
Update the database
Once we have selected our row we can simply update the object it self calling tx.Commit(); will cause the changes to be saved.
var person = session.QueryOver().Where(p => p.FirstName == "Linda").List(); person.FirstOrDefault().Car.Year = "1968"; Console.WriteLine(person.ToString()); tx.Commit();
Delete from the database
To delete a row we simly call delete on the session and supply the object we wish to delete.
var person = session.QueryOver().Where(p => p.FirstName == "Linda").List(); session.Delete(person.FirstOrDefault()); tx.Commit();
Conclusion
NHibernate makes it a lot easier to access the database. Once you have things setup the sky is the limit. I have the opportunity to work with NHibernate now so as usual I will be writing simple tutorials as I learn to use this library. The solution that goes along with this tutorial can be found on GitHub