Object-relational mapping

At OCC there are regular presentations between the engineers on any technical topic of interest. We’ve decided to post a recent one on a technique called Object-relational mapping.

Object-relational mapping in computer software is a programming technique for converting data between incompatible type systems in relational databases and object oriented programming languages.

So it bridges the gap between the database and the code that uses the data.

ORM can be used for Fast prototyping and any data-based product that doesn’t have very complex data structures and data-retrieval procedures.

Engineers use ORM because if offers Object orientation (a lovely way to organise complex code), greater productivity, it makes the code agnostic as to the database it’s talking to (SQL server, MySQL, Postgres…), it offers easier maintenance and testability.

And yet…it only offers these advantages when it’s deployed judiciously.Yes, it needs to be used carefully.

You don’t have such easy control over the code, and this can present an overhead on complicated projects. You don’t have such easy control over the performance (though you can fine-tune this). Finally, ORM tools can lead you down a path to poor database design.

We’ve used Nhibernate for the following example. The “Fluent Nhibernate” extension allows easy auto mapping and database script creation straight from .NET objects

Take two simple classes; Customer and Address.

Using the Fluent NHibernate extension we generate code to create the database straight from the classes.

config = Fluently.Configure()
.Database(MsSqlConfiguration.MsSql2005.ConnectionString(c => c.Is(connectionString)))
.Mappings(m =>m.AutoMappings.Add(AutoMap.AssemblyOf<occ.showandtell.orm.hibernate.Customer>()).ExportTo(SchemaExportPath));
Configuration nhconfig = config.BuildConfiguration();
SchemaExport se = new SchemaExport(nhconfig).SetOutputFile(@"C:developmentfluenthibernateexampleschema.sql");
se.Drop(true, true);
se.Create(true, true);

The code generated for Microsoft SQL 2005 would look like this:

if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FKFE9A39C05A2214B5]') AND parent_object_id = OBJECT_ID('[Customer]'))
alter table [Customer]  drop constraint FKFE9A39C05A2214B5
if exists (select * from dbo.sysobjects where id = object_id(N'[Customer]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [Customer]
if exists (select * from dbo.sysobjects where id = object_id(N'[Address]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [Address]

create table [Customer] (
Id INT IDENTITY NOT NULL,
Name NVARCHAR(255) null,
Description NVARCHAR(255) null,
Address_id INT null,
primary key (Id)
)

create table [Address] (
Id INT IDENTITY NOT NULL,
City NVARCHAR(255) null,
Country NVARCHAR(255) null,
Street NVARCHAR(255) null,
Number NVARCHAR(255) null,
primary key (Id)
)

alter table [Customer]
add constraint FKFE9A39C05A2214B5
foreign key (Address_id)
references [Address]

Which is hard to read (!)  but the code you write to use the ORM is nice.

Here’s something simple

for (int cnt = 0; cnt < 10; cnt++)
{
Customer cust = new Customer();
cust.Name = "Customer" + cnt.ToString();
cust.Description = "Customer description " + cnt.ToString();

Address ad = new Address();
ad.City = "Oxford";
ad.Country = "UK";
ad.Number = rnd.Next(200);
ad.Street = "some street "+cnt;

cust.Address = ad;

session.Save(cust);
session.Save(ad);
session.Flush();
}

List customers = (List)session.CreateCriteria(typeof(Customer))
.AddOrder(new NHibernate.Criterion.Order("Name", true))
.SetMaxResults(5)
.SetFirstResult(0)
.List();

Customer retcust = session.CreateCriteria().Add(Expression.Eq("Name", "Customer8")).UniqueResult();

The alternatives to ORM are Hand written SQL or LINQ or non-SQL approaches such as Cassandra and Big Table.

Useful links