How to

How to use Db2hbm

felicepollano — Thu, 25 Feb 2010 13:05:08 GMT

Current revision posted to How to by felicepollano on 25/02/2010 10:05:08 a.m.

Db2hbm is a reverse engineering tool able to generate hbm files from a database schema and some information contained in a configuration file. Unlike other commercial and open source tools serving the same purpose, db2hbm is not template based. The hbm output comes from a serialization based on the NHibernate mapping schema classes. Extendibility is provided through strategies the user can implement or extend. db2hbm does not generate code. In order to generate the classes or other artifacts please consider the use of hbm2net.

Db2hbm is a command line tool. You can download it here. Information on how to connect to the database and various generation options are contained in a separate configuration file you have to provide in order to make the tool work. A schema for the configuration file is provided in the distributed zip:nhibernate-codegen.xsd. You will have intellisense editing the configuration file in Visual Studio by copying that file under the %Program Files%\Microsoft Visual Studio 9.0\Xml\Schemas folder. Below there is a configuration file skeleton and a description for each section is provided.

<?xml version="1.0" encoding="utf-8" ?>

<db2hbm-conf xmlns="urn:nhibernate-codegen-2.2">

<metadata-strategies>

</metadata-strategies>

<foreign-key-crawlers>

<factory>NHibernate.Tool.Db2hbm.ForeignKeyCrawlers.MSSQLForeignKeyCrawlerFactory, NHibernate.Tool.Db2hbm</factory>

</foreign-key-crawlers>

<type-mapping>

<sql-type dbtype="xml" nhtype="String"/>

<sql-type dbtype="bit" nhtype="Boolean"/>

<sql-type dbtype="varbinary" nhtype="Array"/>

<sql-type dbtype="varchar" nhtype="String"/>

<sql-type dbtype="nvarchar" nhtype="String"/>

<sql-type dbtype="nchar" nhtype="String"/>

<sql-type dbtype="char" nhtype="String"/>

<sql-type dbtype="numeric" nhtype="Decimal"/>

<sql-type dbtype="decimal" nhtype="Decimal"/>

<sql-type dbtype="tinyint" nhtype="Byte"/>

<sql-type dbtype="smallint" nhtype="Int16"/>

<sql-type dbtype="int" nhtype="Int32"/>

<sql-type dbtype="bigint" nhtype="Int64"/>

<sql-type dbtype="datetime" nhtype="DateTime"/>

<sql-type dbtype="uniqueidentifier" nhtype="Guid"/>

<sql-type dbtype="money" nhtype="Decimal"/>

<sql-type dbtype="smallmoney" nhtype="Decimal"/>

</type-mapping>

<naming-strategy class="NHibernate.Tool.Db2hbm.DefaultNamingStrategy, NHibernate.Tool.Db2hbm">

<property name="Language">English</property>

</naming-strategy>

<connection-info>

<dialect>NHibernate.Dialect.MsSql2005Dialect, NHibernate</dialect>

<connection-string>Server=localhost\SQLEXPRESS;initial catalog=AdventureWorks;Integrated Security=True</connection-string>

<connection-driver>NHibernate.Driver.SqlClientDriver, NHibernate</connection-driver>

</connection-info>

<table-filter>

</table-filter>

</tables>

<entity-exceptions>

<member-tag match="set|bag|idbag|list" exclude="true"/>

<alter>

</alter>

</entity>

<member-tag match="set|bag|idbag|list">

<alter>

</alter>

</member-tag>

<member-tag match="many-to-one">

<alter>

</alter>

</member-tag>

</entity>

</entity-exceptions>

<entities-namespace>AdventureWorks.Entities</entities-namespace>

<entities-assembly>AdventureWorks.Entities</entities-assembly>

</db2hbm-conf>

<metadata-strategies>

This section configure the strategy used to infer the mapping from the schema or from what is inferred from the previous strategies. The execution order is preserved and reflect the order on the configuration file. The current version has the following strategy embedded:

FirstPassEntityCollector – Collect each table in the schema and provide an entity for each table with a property for each column.
PrimaryKeyStrategy – Find the primary keys on the tables and modify the previously collected entities to reflect the proper <id> or <composite-id> declared in the schema.
ManyToOneStrategy – Find the defined foreign keys, and if they point to mapped tables, creates the <many-to-one> associations
SetStrategy – Make collections at the other end points of a <many-to-one> association.
ManyToManyStrategy – Try to infer if there is a link table between two entities. If so the associative entity is dropped and a pair of collection of type <bag> or <idbag> is created to represent the many-to-many association.
ApplyEntityExceptionsStrategy – Applies code generation exceptions to the entities according to the <entity-exceptions> configuration section( see below )

Each strategy element, if required, can contain one of more element <property> in the followinf form:

<property name="X">yyyyyyy</property>

This means: set the value of the property named “X” to the value yyyyyy. Internally the value is converted to the target type of the property if it is not a string.

<foreign-key-crawlers>

The core strategies used in reverse engineering requires to know how the foreign key are declared on the tables. Since the ADO.NET schema provider model does not fit this requirement, we have to provide a custom strategy for each DB dialect we are using. Currently only MSSQL server is supported, so if you want a proper code generation for another database you should provide your own foreign-key.-crawler, or alternatively configure each FK definition by hand, as shown in the <tables> configuration section ( you would probably prefer not to ).

To provide your own key crawler, you have to create a factory for it, implementing the interface IForeignKeyCrawlerFactory, declared as shown below:

public interface IForeignKeyCrawlerFactory
{
IForeignKeyCrawler Create();
void Register();
}

The register method is called by the infrastructure, and allow you to bind your factory with one or more NHIbernate Dialect. Let’s have an example on how the MSSQLForeignKeyCrawlerFactory implement this function:

public void Register()
{
     var instance = new MSSQLForeignKeyCrawlerFactory();
     ForeignKeyCrawlersRegistar.Register(instance, typeof(MsSql2000Dialect));
     ForeignKeyCrawlersRegistar.Register(instance, typeof(MsSql2005Dialect));
     ForeignKeyCrawlersRegistar.Register(instance, typeof(MsSql2008Dialect));
}

The code above register an instance of MSSQLForeignKeyCrawlerFactory bound to the MsSql200x dialects.

The other method, Create, are supposed to return the IForeignCrawler instance able to deal with the specific database foreign key schema info. This object must implement the following interface:

public interface IForeignKeyCrawler {
IForeignKeyColumnInfo[] GetForeignKeyColumns(DbConnection dbConnection
                                , string constraintName
                                , string catalog
                                , string schema
                                );
}

To proper implement this you should be able to return an array of IForeignKeyColumnInfo from the constraint name, the catalog and the schema containing it.

The IForeignKeyColumnInfo interface is defined as follow:

public interface IForeignKeyColumnInfo
{
       string PrimaryKeyColumnName { get; }
       string PrimaryKeyTableName { get; }
       string PrimaryKeyTableSchema { get; }
       string PrimaryKeyTableCatalog { get; }

       string ForeignKeyColumnName { get; }
       string ForeignKeyTableName { get; }
       string ForeignKeyTableSchema { get; }
       string ForeignKeyTableCatalog { get; }

}

PrimaryKey/(Table/Catalog/Column)Name must return the key column information as defined in the table containing the primary key referenced by the constraint.

ForeignKey/(Table/Catalog/Column)Name must return the key column information as defined in the table containing the foreign key referenced by the constraint.

<type-mapping>

Each element in this section maps a database type to an NHIbernate type. You can force a type with respect to column length/precision too. So you can have:

<sql-type dbtype="varchar" length="1" nhtype="Char"/>
<sql-type dbtype="varchar" length="2-*" nhtype="String"/>

in order to distinguish between char and strings. The most restrictive satisfied condition is used.

<naming-strategy>

The naming strategy is a component driving the name generation for the entities, properties, collections and component key class generated by the tool. A default naming strategy is provided: NHibernate.Tool.Db2hbm.DefaultNamingStrategy. This strategy leverages the unNHAddins inflector to singularize the name of the tables, or pluralize the collections. The unNHaddins inflector is provided for many languages, the DefaultNamingStrategy infer the concrete class to use from the property language:

<property name="Language">English</property>

If an unknown language is specified, DefaultNamingStrategy uses “English “ by default. In order to implement your own naming strategy, you have to derive from the default naming strategy, or implement your own from scratch by implementing the INamingStrategy interface. Please note that despites the name, this interface is not the one contained in NHibernate.

The interface is defined as follow:

public interface INamingStrategy
{
        string GetEntityNameFromTableName(string tableName);
        string GetPropertyNameFromColumnName(string columnName);
        string GetIdPropertyNameFromColumnName(string columnName);
        string GetClassNameForComponentKey(string entityName);
        string GetNameForComponentKey(string entityName,string componentClass);
        string GetNameForManyToOne(string referredEntity, string[] columnNames);
        string GetNameForCollection(string collectingClass, int progressive);
        string GetClassNameForCollectionComponent(string collectionTableName);
}

The meaning of the first three functions should be clear by the name. For the other ones:

GetClassNameForComponentKey – The entity has a composite key, and it is reverse engineering with a component, implementor has to return a name for the class representing that component.
GetNameForManyToOne – The entity has a reference to another entity, you have to return the name you want to assign to the corresponding property. Implementor will receive the referred entity name and the column(s) names defining the reference.
GetNameForCollection – the entity has a collection of other entity instances. The implementor will receive the name of the collected entity, plus a progressive ( you may have more collection of the same type of entity in a class ). implementor has to return the name to assign to the property representhing the collection.
GetClassNameForCollectionComponent – There is a map of component. implementor has to return the name of the class representing the component.

<connection-info>

Db2hbm needs to connect to the database to inquire into schema information. In this section user will provide the essential parameters to achieve the connection.

<dialect> – the NHibernate dialect class.
<connection-string> – the DB connection string
<connection-driver> – the NHibernate connection driver

<table-filter>

User can specify which table to include/exclude from the reverse engineering properties by adding keys to this section.

More than one include/exclude element can be specified. Each element has three attributes:

you can specify a regular expression to specify the pattern for the catalog, the schema and the table. Tables matching this pattern will be excluded/included.

<tables>

If there is not enougth information in the schema, or if there is not a proper foreign key crawler available for the database in use, it is possible to specify manually the foreign key/primary key layout. These information will merge with the ones already present in the schema, if any.

Here below an example:

<entity-exceptions>

In this section the user can finely drive the produced hbm artifacts by altering the default tool behavior on a per-name o per-tag based strategy. Let’s have an example:

<entity-exceptions>
   <entity match=".*Addres.*|UnitMeasure|CreditCard" >
     <member-tag match="set|bag|idbag|list" exclude="true"/>
     <alter>
       <set name="lazy" value="false"/>
       <meta-add attribute="comment">a comment…</meta-add>
     </alter>
   </entity>
   <entity match=".*">
     <member-tag match="set|bag|idbag|list">
       <alter>
         <set name="access" value="field.camelcase-underscore"/>
         <meta-add attribute="test">Value of test</meta-add>
       </alter>
     </member-tag>
     <member-tag match="many-to-one">
       <alter>
         <set name="fetch" value="join"/>
       </alter>
     </member-tag>
   </entity>
</entity-exceptions>

The tag <entity match=".*Addres.*|UnitMeasure|CreditCard"> is a selctor based on the entity name. The selector match attribute is a .NET Regex. All entities satisfied this Regex will be interested in the alter process.

The <member-tag> is a selector, the match is done against the tag name in the generated hbm. The exclude attribute means, in this case, to remove all the collections from the entity. If a name based selection is required, <member-name> has to be used, with the same semantic.

The alter tag drive the modification, and works both on a per-entity or a per-member base. In an alter tag the user can specify <set name=”something” value=”val”/> to set ( or change ) an attribute on the generated hbm. To remove an attribute the <remove name=”toremove”/> is required, and for add an additional meta information on the hbm the <meta-add attribute=”metaattribute”>the attribute content</meta-attribute> has to be used.

<entities-namespace>

Specify the namespace of the generated entities.

<entities-assembly>

Specify the assembly containing the generated entities. ( db2hbm does not compile any code, but this is a parameter in the hbm file).

Using the command line tool

Using the command line tool is very easy: just launch

db2hbm –config:configfilename –output:outputdir

The output directory is optional. If not specified, hbm artifacts will be generated in the current folder.

Customizing Fluent Nhibernate's AutoPersistenceModel Conventions

Chris Nicola — Fri, 25 Dec 2009 17:04:15 GMT

Current revision posted to How to by Chris Nicola on 25/12/2009 02:04:15 p.m.

Customizing Fluent Nhibernate's AutoPersistenceModel Conventions

Filed under: Mapping, FluentNH, AutoPersistenceModel, convention over configuration

(this was originally blogged about here)

One of the things I get a fair bit of use out of is the fluent nHibernate project and the AutoPersistenceModel. If you are not very familiar with fluentNH or AutoPersistenceModel then I would suggest checking out their wiki, as what I am about to discuss, while not difficult, is some relatively advanced usage.

The purpose of AutoPersistenceModel is to automatically generate the nHibernate configuration (the HBM files if you are currently used to XML configuration) directly from your model based on Convention over Configuration, you can hear James Kovacs discuss this concept on .NET Rocks. With tools like SchemaExport and SchemaUpdate it is even possible to automatically generate and execute DDL scripts against your database to keep the schema in sync with your model. This can be a bit rails like in it's use, in fact Adam Dymitruk is currently working on a utility to extend the SchemaUpdate to allow the creation of versioned database scripts like rails has (ok Adam it's official, now you actually have to finish it!).

The default AutoPersistenceModel conventions are kept quite simple, so often you will need to do a bit of customization. One way to do this is through overrides which I showed how to use in this post, were I implemented an override for a self-referencing tree relationship. However, whenever possible, it is preferable to use fluentNH's conventions to do this. Conventions can be used define custom behavior in very flexible ways. Below I am going to show how this can be done to customize database constraints like unique and index. [more]

S#arp Architecture provides a [DomainSignature] attribute which you can apply to your entity's properties. The attribute is used to denote the set of properties that uniquely define the entity and is similar to the concept of a business key often used in SQL database design. It is important to point out that the DomainSignature should not be considered a primary key and that you should always use a surrogate primary key generated either by the database or nHibernate (hilo and guid.comb are the two I prefer).

The domain signature can ensures that entities can be compared using the properties decorated with [DomainSignature]. This is useful if say one object was loaded from the repository using nHibernate but another was constructed and I want to determine if I should treat them as the same entity. It is also useful to determine if a new entity will violate a uniqueness constraint you want to enforce.

A good example of a useful DomainSignature would be the slug of a blog post. A post also has an id for it's primary key, in most cases a guid, but the slug also uniquely identifies a post as well and no two posts can have the same slug. The only problem now is that when I generate my DDL I can see that nHibernate has no notion that it should enforce my uniqueness constraint.

What I want is for nHibernate and hence the database schema should be aware that DomainSignature implies a uniqueness constraint. Fortunately, fluent nHibernate conventions make this is quite easy. Fluent nHibernate defines an AttributePropertyConvention<T> base class for exactly this purpose and we can extend it like this:

public class DomainSignatureConvention : AttributePropertyConvention<DomainSignatureAttribute> {
    protected override void Apply(DomainSignatureAttribute attribute, IPropertyInstance instance) { 
        instance.UniqueKey(instance.EntityType.Name + "DomainSignature");    
    }
}

The DomainSignatureConvention tells fluent that all properties that are decorated with [DomainSignature] should form a unique key. Now if I then define my entity like this:

public class Price : Entity {
    [DomainSignature]
    public virtual Security Security { get; set; }
    [DomainSignature]
    public virtual DateTime PriceDate { get; set; }
    [DomainSignature]
    public virtual bool CanadianPrice { get; set; }
    public virtual decimal Bid { get; set; }
    public virtual decimal Ask { get; set; }
    public virtual decimal Close { get; set; }
}

then SchemaExport will generate DDL like this:

create table Prices (

    Id INTEGER not null,

   PriceDate DATETIME,

   CanadianPrice INTEGER,

   Bid NUMERIC,

   Ask NUMERIC,

   Close NUMERIC,

   SecurityFk INTEGER,

   primary key (Id),

  unique (PriceDate, CanadianPrice)

Except we have a small problem here. The foreign key for the many-to-one relationship on Security was not included in the unique constraint. To be quite honest I am not exactly sure why but I am guessing that the AttributePropertyConvention does not work with a reference. Instead I will need to add something to my ReferenceConvention which is provided by default with s#arp architecture.

public class ReferenceConvention : IReferenceConvention {
    public void Apply(FluentNHibernate.Conventions.Instances.IManyToOneInstance instance) {
        instance.Column(instance.Property.Name + "Fk");
        if (Attribute.IsDefined(instance.Property, typeof (DomainSignatureAttribute)))
            instance.UniqueKey("DomainSignature");
    }
}

I don't really like this as I don't think the attribute should be ignored, but it does at least change the above to unique(PriceDate, CanadianPrice, SecurityFk).

There are obviously many other types of constraints we could use, you could create an attribute for defining indexing on certain properties like [Indexable("IndexName")] and create a convention like this:

public class IndexableAttribute : Attribute {
    private readonly string _name;
 
    public IndexableAttribute(string name) { _name = name; }
 
    public string GetName() { return _name; }
}
 
public class IndexableConvention : AttributePropertyConvention<IndexableAttribute> {
    protected override void Apply(IndexableAttribute attribute, IPropertyInstance instance) {
        instance.Index(attribute.GetName());
    }
}

Now I can create indicies by using the [Indexable("Name")] attribute. Properties with the same "Name" will be part of the same index constraint and indexed together.

I found it is actually a good idea to always index many-to-one relationships, something I briefly mentioned in a previous post. Now that I am using fluent nhibernate however I can't set the index property in the HBM files so I will need a convention for that. It also makes sense to set this indexing on all many-to-one relationships so there is a better way to do this that does not involve the use of an attribute, instead we implement IRefrenceConvention. S#arp architecture already includes this convention by default so we can just edit it:

public class ReferenceConvention : IReferenceConvention {
        public void Apply(FluentNHibernate.Conventions.Instances.IManyToOneInstance instance) {
            instance.Column(instance.Property.Name + "Fk");
            if (Attribute.IsDefined(instance.Property, typeof (DomainSignatureAttribute)))
                instance.UniqueKey("DomainSignature");
            else 
                instance.Index(instance.Property.Name + "Index");
        }
    }

Notice the if.else, if we have already defined UniqueKey defining Index would be redundant as unique key's are already indexed. When defining indexes you will typically see something like like the following DDL output from SchemaExport:

create index SecurityIndex on Prices (SecurityFk)

It is pretty easy to customize the behavior of the fluent nhibernate AutoPersistenceModel using conventions and I find it can be very useful to have this type of fine grained control over your database schema generation.

Customizing Fluent Nhibernate's AutoPersistenceModel Conventions

Chris Nicola — Fri, 25 Dec 2009 00:38:05 GMT

Revision 2 posted to How to by Chris Nicola on 24/12/2009 09:38:05 p.m.

Customizing Fluent Nhibernate's AutoPersistenceModel Conventions

Filed under: Mapping, FluentNH, AutoPersistenceModel, convention over configuration

(this was originally blogged about here)

public class DomainSignatureConvention : AttributePropertyConvention<DomainSignatureAttribute> {
    protected override void Apply(DomainSignatureAttribute attribute, IPropertyInstance instance) { 
        instance.UniqueKey(instance.EntityType.Name + "DomainSignature");    
    }
}

The DomainSignatureConvention tells fluent that all properties that are decorated with [DomainSignature] should form a unique key. Now if I then define my entity like this:

public class Price : Entity {
    [DomainSignature]
    public virtual Security Security { get; set; }
    [DomainSignature]
    public virtual DateTime PriceDate { get; set; }
    [DomainSignature]
    public virtual bool CanadianPrice { get; set; }
    public virtual decimal Bid { get; set; }
    public virtual decimal Ask { get; set; }
    public virtual decimal Close { get; set; }
}

then SchemaExport will generate DDL like this:

create table Prices (

    Id INTEGER not null,

   PriceDate DATETIME,

   CanadianPrice INTEGER,

   Bid NUMERIC,

   Ask NUMERIC,

   Close NUMERIC,

   SecurityFk INTEGER,

   primary key (Id),

  unique (PriceDate, CanadianPrice)

public class ReferenceConvention : IReferenceConvention {
    public void Apply(FluentNHibernate.Conventions.Instances.IManyToOneInstance instance) {
        instance.Column(instance.Property.Name + "Fk");
        if (Attribute.IsDefined(instance.Property, typeof (DomainSignatureAttribute)))
            instance.UniqueKey("DomainSignature");
    }
}

I don't really like this as I don't think the attribute should be ignored, but it does at least change the above to unique(PriceDate, CanadianPrice, SecurityFk).

public class IndexableAttribute : Attribute {
    private readonly string _name;
 
    public IndexableAttribute(string name) { _name = name; }
 
    public string GetName() { return _name; }
}
 
public class IndexableConvention : AttributePropertyConvention<IndexableAttribute> {
    protected override void Apply(IndexableAttribute attribute, IPropertyInstance instance) {
        instance.Index(attribute.GetName());
    }
}

Now I can create indicies by using the [Indexable("Name")] attribute. Properties with the same "Name" will be part of the same index constraint and indexed together.

public class ReferenceConvention : IReferenceConvention {
        public void Apply(FluentNHibernate.Conventions.Instances.IManyToOneInstance instance) {
            instance.Column(instance.Property.Name + "Fk");
            if (Attribute.IsDefined(instance.Property, typeof (DomainSignatureAttribute)))
                instance.UniqueKey("DomainSignature");
            else 
                instance.Index(instance.Property.Name + "Index");
        }
    }

create index SecurityIndex on Prices (SecurityFk)

Customizing Fluent Nhibernate's AutoPersistenceModel Conventions

Chris Nicola — Fri, 25 Dec 2009 00:35:49 GMT

Revision 1 posted to How to by Chris Nicola on 24/12/2009 09:35:49 p.m.

(this was originally blogged about here)

public class DomainSignatureConvention : AttributePropertyConvention<DomainSignatureAttribute> {
    protected override void Apply(DomainSignatureAttribute attribute, IPropertyInstance instance) { 
        instance.UniqueKey(instance.EntityType.Name + "DomainSignature");    
    }
}

The DomainSignatureConvention tells fluent that all properties that are decorated with [DomainSignature] should form a unique key. Now if I then define my entity like this:

public class Price : Entity {
    [DomainSignature]
    public virtual Security Security { get; set; }
    [DomainSignature]
    public virtual DateTime PriceDate { get; set; }
    [DomainSignature]
    public virtual bool CanadianPrice { get; set; }
    public virtual decimal Bid { get; set; }
    public virtual decimal Ask { get; set; }
    public virtual decimal Close { get; set; }
}

then SchemaExport will generate DDL like this:

create table Prices (

    Id INTEGER not null,

   PriceDate DATETIME,

   CanadianPrice INTEGER,

   Bid NUMERIC,

   Ask NUMERIC,

   Close NUMERIC,

   SecurityFk INTEGER,

   primary key (Id),

  unique (PriceDate, CanadianPrice)

public class ReferenceConvention : IReferenceConvention {
    public void Apply(FluentNHibernate.Conventions.Instances.IManyToOneInstance instance) {
        instance.Column(instance.Property.Name + "Fk");
        if (Attribute.IsDefined(instance.Property, typeof (DomainSignatureAttribute)))
            instance.UniqueKey("DomainSignature");
    }
}

I don't really like this as I don't think the attribute should be ignored, but it does at least change the above to unique(PriceDate, CanadianPrice, SecurityFk).

public class IndexableAttribute : Attribute {
    private readonly string _name;
 
    public IndexableAttribute(string name) { _name = name; }
 
    public string GetName() { return _name; }
}
 
public class IndexableConvention : AttributePropertyConvention<IndexableAttribute> {
    protected override void Apply(IndexableAttribute attribute, IPropertyInstance instance) {
        instance.Index(attribute.GetName());
    }
}

Now I can create indicies by using the [Indexable("Name")] attribute. Properties with the same "Name" will be part of the same index constraint and indexed together.

public class ReferenceConvention : IReferenceConvention {
        public void Apply(FluentNHibernate.Conventions.Instances.IManyToOneInstance instance) {
            instance.Column(instance.Property.Name + "Fk");
            if (Attribute.IsDefined(instance.Property, typeof (DomainSignatureAttribute)))
                instance.UniqueKey("DomainSignature");
            else 
                instance.Index(instance.Property.Name + "Index");
        }
    }

create index SecurityIndex on Prices (SecurityFk)

Creating a custom id generator for nHibernate

Chris Nicola — Fri, 25 Dec 2009 00:31:36 GMT

Current revision posted to How to by Chris Nicola on 24/12/2009 09:31:36 p.m.

Creating a custom id generator for nHibernate

Filed under: Mapping, Nhibernate, IIdentifierGenerator, IPersistentIdentifierGenerator, TableGenerator, FluentNH

I originally blogged about this here and here on my blog but Fabio suggested I add these posts to the Forge, so here I am. I have to say I am a little exited to make my first contribution to the Wiki [H]. I will try to add a bit more to them and show some examples that do not use the TableGenerator class but directly implement IIdentifierGenerator . Please let me know if there are any mistakes or anything else you want to see.

Before beginning I recommend that you download the NH source code and take a quick look at the TableGenerator , IIdentifierGenerator and IPersistentIdentifierGenerator classes as you will be working with these classes and interfaces to implement your custom generator.

Part 1: Inheriting from TableGenerator class

Currently I am doing some work developing a utility that imports securities data into a fairly old legacy database. It is still SQL but the database and software havn't been updated in about 15 years (maybe more) and never will be again.

The original application for this database uses a primitive hilo-style ID generator mechanism. Tt simply queries an integer value from a table in the database and increments it, then it adds a fixed value to it and uses that as the next unique ID. It doesn't seem very optimal, but that's how it works and there's no changing it.

It isn't complex, but it also isn't one of the identity strategies available in nHibernate and I fully intend to use nHibernate.

So what to do? One option would be to set generator="assigned" and do it all myself, assigning the ID using ADO.Net directly or something similar... but what fun would that be? No, I've decided to take a peak at the nHibernate code and learn to extend it. Read on to find out how to create a custom class that inherits from IIdentifierGenerator to implement your own custom ID generator strategy for nHibernate.

One of the beautiful things about nHibernate the ability to extend it. You can extend its functionality without ever touching their source code.

It was very easy to locate the classes responsible for ID generation in the NHibernate.ID namespace. It was also clear immediately that most classes inherited from IIdentifierGenerator. I also knew that my strategy was similar to the procedure that hilo implements so I started with TableHiLoGenerator which inherts from TableGenerator.

After playing around with this for a while and trying different things, it was clear that TableGenerator, basically takes a tablename and columnname from the configuration, reads a value, increments it and passes it on as an ID. Voila! That was almost exactly what I needed to do with little else to change. I was quickly able to create my own class inheriting from TableGenerator

public class FDPSequence : TableGenerator
{
    private const Int32 SeedValue = 1048576;
 
    private static readonly ILog Log = LogManager.GetLogger(typeof(FDPSequence));
 
    public override object Generate(ISessionImplementor session, object obj)
    {
        int counter = Convert.ToInt32(base.Generate(session, obj));
        return counter + SeedValue + 1;
    }
}

Not much to that is there? Now that is a fairly simple case, but even getting more complex isn't much harder than that. You can implement IIdentifierGenerator directly and create something far more customized. TableGenerator actually implements IPersistentIdentifierGenerator which is an interface for identifiers which need to write and updated value to the database. However, since nHibernate provides so many versatile and inheritable base class generators that you will likely get by with inheriting one of those and extending it as I did.

In order to use this custom generator class I setup my configuration to use it. Using Fluent I wrote a mapping like this:

public class InvestSpecMap : FDPEntityBaseMap<InvestSpec>
{
    public InvestSpecMap()
    {
        WithTable("InvestSpec");
        Id(x => x.Id).ColumnName(tablename + "ID")
            .SetGeneratorClass(typeof(FDPGenerator.FDPSequence).AssemblyQualifiedName)
            .AddGeneratorParam("table", "zSysCounters") //Name of the ID counter table
            .AddGeneratorParam("column", "InvestSpec"); //ID counter column for this table
        Map(x => x.AnnualDividend).ColumnName("AnnlzdDiv");
        Map(x => x.AssetClass);
        Map(x => x.CurrencyType);
        Map(x => x.CusipNumber).ColumnName("CusipNum");
        Map(x => x.DividendFreq).ColumnName("DivFrequency");
        Map(x => x.FullName);
        Map(x => x.MaturityDate).ColumnName("MaturityDt");
        Map(x => x.PriceAsOf).ColumnName("PriceAsOfDt");
        Map(x => x.ShortName).ColumnName("AbbrName");
        Map(x => x.TaxTreatment);
        Map(x => x.TickerSymbol);
        Map(x => x.Type);
        Map(x => x.UnitPrice);
    }
}

You can do the same with XML mappings just declare the assembly and class as you usually would in XML. This is actually the first time I've used FluentNH.

What is great about this is that even when testing my mappings with SQLLite, SchemaExport is actually able to create the identity table based on the tablename and columnname properties in the nHibernate configuration. The main benefit here however is that identity generation remains transparent to the rest of my application. Also doing something cool with nHibernate, that's priceless of course

Part 2: Stealing from TableGenerator and controlling DDL generation

A while ago I posted about writing a custom id generator using nHibernate and extending one of the existing Generator classes nHibernate provides. This was done to support working with a legacy database which uses a custom id generation strategy. One thing I like to do with all my apps is write some basic CRUD tests to make sure my mappings and queries are all working properly.

To do this I use Oren's approach of creating a temporary in-memory database using SQLite to work with. This involves using the SchemaExport tool in nHibernate to generate the DDL to create the database. If you are using a table generator strategy it also needs to create the table and columns for the custom id generator. In some cases you may need to customize the DDL for your Id table.

As I discovered the code DDL to generate the table to store the next id value for a my custom id is only run created once per class (and then only on does not check the base column name value for each classif you are inheriting). For my id generator there is a separate column used for each table in the database. So, for so this was a problem. For example, the Price table has a column in the Counter table called Price to store it's counter and the Holding table has a column called Holding., but I was only getting one column (this appears to be a problem with hilo and SchemaExport, you can currently only have one column per database)

The TableGenerator class supports two parameters which are set in the HBM files (or using fluent) tablename and columnname which set the table and column names used for the id counter. If you have a copy of the nHibernate source code you will see that the TableGenerator class calls SqlCreateStrings()and SqlDropString() to create and drop the table for the id generator. Unfortunately, as I mentioned above this is only called once, and so it only creates one table with one column for whichever mapping is handled first.

In order to fix this I had to copy the TableGenerator class (yes, yes, I know copy-paste is bad form) and modify those functions. I also added a third parameter allcolumnnames in which I can provide a comma separated list of all the columns the table needs to have (one per table in my database). The SqlCreateStrings()is simple changed to:

public string[] SqlCreateStrings(Dialect dialect) {
  string create = "create table " + tableName + " (";
  string insert = "insert into " + tableName + " values (";
  foreach (string s in allcolumns.Split(',')) {
    create += " " + s + " " + dialect.GetTypeName(columnSqlType) + ",";
    insert += " 1,";
  }
  create = create.Trim(',') + " )";
  insert = insert.Trim(',') + " )";
  return new[] {create, insert};
}

(Yes, yes, I know I should have learned from my last post and used a StringBuilder here as I pointed out in my last post, but seriously, this method will only ever be called once)

Now when it is called it will create the correct table with all the columns I need. A couple of example mappings for the database in FluentNH are shown below:

public InvestSpecMap() {
  WithTable("InvestSpec");
  Id(x => x.Id).ColumnName("InvestSpecID")
    .SetGeneratorClass(typeof (FdpSequenceGen).AssemblyQualifiedName)
    .AddGeneratorParam("allcolumns", "InvestSpec, InvestPrice")
    .AddGeneratorParam("table", "zSysCounters") //ID counter table name
    .AddGeneratorParam("column", "InvestSpec"); //ID counter column name

public PriceMap() {
  WithTable("InvestPrice");
  Id(x => x.Id).ColumnName("InvestPriceID")
    .SetGeneratorClass(typeof (FdpSequenceGen).AssemblyQualifiedName)
    .AddGeneratorParam("allcolumns", "InvestSpec, InvestPrice")
    .AddGeneratorParam("table", "zSysCounters") //ID counter table name
    .AddGeneratorParam("column", "InvestPrice"); //ID counter column name

To be completely "clean code" and avoid repeating myself I should probably factor out the Id() code into a method in an inhereted base class so I can call it from each mapping class like this:

protected void SetIdGenerator(string tablename) {
  Id(x => x.Id).ColumnName(TableName + "Id")
    .SetGeneratorClass(typeof(FdpSequenceGen).AssemblyQualifiedName)
    .AddGeneratorParam("allcolumns", AllColumns)
    .AddGeneratorParam("table", "zSysCounters") //ID counter table name
    .AddGeneratorParam("column", TableName); //ID counter column name
}

Now I can unit test using a temporary in-memory SQLite database and all my tests are passing.

Creating a custom id generator for nHibernate

Chris Nicola — Fri, 25 Dec 2009 00:25:51 GMT

Revision 4 posted to How to by Chris Nicola on 24/12/2009 09:25:51 p.m.

Creating a custom id generator for nHibernate

Filed under: Mapping, Nhibernate, IIdentifierGenerator, IPersistentIdentifierGenerator, TableGenerator, FluentNH

Part 1: Inheriting from TableGenerator class

It isn't complex, but it also isn't one of the identity strategies available in nHibernate and I fully intend to use nHibernate.

One of the beautiful things about nHibernate the ability to extend it. You can extend its functionality without ever touching their source code.

public class FDPSequence : TableGenerator
{
    private const Int32 SeedValue = 1048576;
 
    private static readonly ILog Log = LogManager.GetLogger(typeof(FDPSequence));
 
    public override object Generate(ISessionImplementor session, object obj)
    {
        int counter = Convert.ToInt32(base.Generate(session, obj));
        return counter + SeedValue + 1;
    }
}

In order to use this custom generator class I setup my configuration to use it. Using Fluent I wrote a mapping like this:

public class InvestSpecMap : FDPEntityBaseMap<InvestSpec>
{
    public InvestSpecMap()
    {
        WithTable("InvestSpec");
        Id(x => x.Id).ColumnName(tablename + "ID")
            .SetGeneratorClass(typeof(FDPGenerator.FDPSequence).AssemblyQualifiedName)
            .AddGeneratorParam("table", "zSysCounters") //Name of the ID counter table
            .AddGeneratorParam("column", "InvestSpec"); //ID counter column for this table
        Map(x => x.AnnualDividend).ColumnName("AnnlzdDiv");
        Map(x => x.AssetClass);
        Map(x => x.CurrencyType);
        Map(x => x.CusipNumber).ColumnName("CusipNum");
        Map(x => x.DividendFreq).ColumnName("DivFrequency");
        Map(x => x.FullName);
        Map(x => x.MaturityDate).ColumnName("MaturityDt");
        Map(x => x.PriceAsOf).ColumnName("PriceAsOfDt");
        Map(x => x.ShortName).ColumnName("AbbrName");
        Map(x => x.TaxTreatment);
        Map(x => x.TickerSymbol);
        Map(x => x.Type);
        Map(x => x.UnitPrice);
    }
}

You can do the same with XML mappings just declare the assembly and class as you usually would in XML. This is actually the first time I've used FluentNH.

Part 2: Stealing from TableGenerator and controlling DDL generation

As I discovered the code to generate the table for a custom id is only run once per class (and then only on the base class if you are inheriting). For my id generator there is a separate column used for each table in the database. So, for example the Price table has a column in the Counter table called Price to store it's counter and the Holding table has a column called Holding.

public string[] SqlCreateStrings(Dialect dialect) {
  string create = "create table " + tableName + " (";
  string insert = "insert into " + tableName + " values (";
  foreach (string s in allcolumns.Split(',')) {
    create += " " + s + " " + dialect.GetTypeName(columnSqlType) + ",";
    insert += " 1,";
  }
  create = create.Trim(',') + " )";
  insert = insert.Trim(',') + " )";
  return new[] {create, insert};
}

(Yes, yes, I know I should have learned from my last post and used a StringBuilder here as I pointed out in my last post, but seriously, this method will only ever be called once)

Now when it is called it will create the correct table with all the columns I need. A couple of example mappings for the database in FluentNH are shown below:

public InvestSpecMap() {
  WithTable("InvestSpec");
  Id(x => x.Id).ColumnName("InvestSpecID")
    .SetGeneratorClass(typeof (FdpSequenceGen).AssemblyQualifiedName)
    .AddGeneratorParam("allcolumns", "InvestSpec, InvestPrice")
    .AddGeneratorParam("table", "zSysCounters") //ID counter table name
    .AddGeneratorParam("column", "InvestSpec"); //ID counter column name

public PriceMap() {
  WithTable("InvestPrice");
  Id(x => x.Id).ColumnName("InvestPriceID")
    .SetGeneratorClass(typeof (FdpSequenceGen).AssemblyQualifiedName)
    .AddGeneratorParam("allcolumns", "InvestSpec, InvestPrice")
    .AddGeneratorParam("table", "zSysCounters") //ID counter table name
    .AddGeneratorParam("column", "InvestPrice"); //ID counter column name

To be completely "clean code" and avoid repeating myself I should probably factor out the Id() code into a method in an inhereted base class so I can call it from each mapping class like this:

protected void SetIdGenerator(string tablename) {
  Id(x => x.Id).ColumnName(TableName + "Id")
    .SetGeneratorClass(typeof(FdpSequenceGen).AssemblyQualifiedName)
    .AddGeneratorParam("allcolumns", AllColumns)
    .AddGeneratorParam("table", "zSysCounters") //ID counter table name
    .AddGeneratorParam("column", TableName); //ID counter column name
}

Now I can unit test using a temporary in-memory SQLite database and all my tests are passing.

Creating a custom id generator for nHibernate

Chris Nicola — Fri, 25 Dec 2009 00:18:06 GMT

Revision 3 posted to How to by Chris Nicola on 24/12/2009 09:18:06 p.m.

Creating a custom id generator for nHibernate

Filed under: Mapping, Nhibernate, IIdentifierGenerator, IPersistentIdentifierGenerator, TableGenerator, FluentNH

Part 1: Inheriting from TableGenerator class

It isn't complex, but it also isn't one of the identity strategies available in nHibernate and I fully intend to use nHibernate.

One of the beautiful things about nHibernate the ability to extend it. You can extend its functionality without ever touching their source code.

public class FDPSequence : TableGenerator
{
    private const Int32 SeedValue = 1048576;
 
    private static readonly ILog Log = LogManager.GetLogger(typeof(FDPSequence));
 
    public override object Generate(ISessionImplementor session, object obj)
    {
        int counter = Convert.ToInt32(base.Generate(session, obj));
        return counter + SeedValue + 1;
    }
}

In order to use this custom generator class I setup my configuration to use it. Using Fluent I wrote a mapping like this:

public class InvestSpecMap : FDPEntityBaseMap<InvestSpec>
{
    public InvestSpecMap()
    {
        WithTable("InvestSpec");
        Id(x => x.Id).ColumnName(tablename + "ID")
            .SetGeneratorClass(typeof(FDPGenerator.FDPSequence).AssemblyQualifiedName)
            .AddGeneratorParam("table", "zSysCounters") //Name of the ID counter table
            .AddGeneratorParam("column", "InvestSpec"); //ID counter column for this table
        Map(x => x.AnnualDividend).ColumnName("AnnlzdDiv");
        Map(x => x.AssetClass);
        Map(x => x.CurrencyType);
        Map(x => x.CusipNumber).ColumnName("CusipNum");
        Map(x => x.DividendFreq).ColumnName("DivFrequency");
        Map(x => x.FullName);
        Map(x => x.MaturityDate).ColumnName("MaturityDt");
        Map(x => x.PriceAsOf).ColumnName("PriceAsOfDt");
        Map(x => x.ShortName).ColumnName("AbbrName");
        Map(x => x.TaxTreatment);
        Map(x => x.TickerSymbol);
        Map(x => x.Type);
        Map(x => x.UnitPrice);
    }
}

You can do the same with XML mappings just declare the assembly and class as you usually would in XML. This is actually the first time I've used FluentNH.

Part 2: Stealing from TableGenerator and controlling DDL generation

public string[] SqlCreateStrings(Dialect dialect) {
  string create = "create table " + tableName + " (";
  string insert = "insert into " + tableName + " values (";
  foreach (string s in allcolumns.Split(',')) {
    create += " " + s + " " + dialect.GetTypeName(columnSqlType) + ",";
    insert += " 1,";
  }
  create = create.Trim(',') + " )";
  insert = insert.Trim(',') + " )";
  return new[] {create, insert};
}

(Yes, yes, I know I should have learned from my last post and used a StringBuilder here as I pointed out in my last post, but seriously, this method will only ever be called once)

Now when it is called it will create the correct table with all the columns I need. A couple of example mappings for the database in FluentNH are shown below:

public InvestSpecMap() {
  WithTable("InvestSpec");
  Id(x => x.Id).ColumnName("InvestSpecID")
    .SetGeneratorClass(typeof (FdpSequenceGen).AssemblyQualifiedName)
    .AddGeneratorParam("allcolumns", "InvestSpec, InvestPrice")
    .AddGeneratorParam("table", "zSysCounters") //ID counter table name
    .AddGeneratorParam("column", "InvestSpec"); //ID counter column name

public PriceMap() {
  WithTable("InvestPrice");
  Id(x => x.Id).ColumnName("InvestPriceID")
    .SetGeneratorClass(typeof (FdpSequenceGen).AssemblyQualifiedName)
    .AddGeneratorParam("allcolumns", "InvestSpec, InvestPrice")
    .AddGeneratorParam("table", "zSysCounters") //ID counter table name
    .AddGeneratorParam("column", "InvestPrice"); //ID counter column name

To be completely "clean code" and avoid repeating myself I should probably factor out the Id() code into a method in an inhereted base class so I can call it from each mapping class like this:

protected void SetIdGenerator(string tablename) {
  Id(x => x.Id).ColumnName(TableName + "Id")
    .SetGeneratorClass(typeof(FdpSequenceGen).AssemblyQualifiedName)
    .AddGeneratorParam("allcolumns", AllColumns)
    .AddGeneratorParam("table", "zSysCounters") //ID counter table name
    .AddGeneratorParam("column", TableName); //ID counter column name
}

Now I can unit test using a temporary in-memory SQLite database and all my tests are passing.

Creating a custom id generator for nHibernate

Chris Nicola — Thu, 03 Sep 2009 16:42:06 GMT

Revision 2 posted to How to by Chris Nicola on 03/09/2009 01:42:06 p.m.

Creating a custom id generator for nHibernate

Filed under: Mapping, Nhibernate, IIdentifierGenerator, IPersistentIdentifierGenerator, TableGenerator, FluentNH

Part 1: Inheriting from TableGenerator class

It isn't complex, but it also isn't one of the identity strategies available in nHibernate and I fully intend to use nHibernate.

One of the beautiful things about nHibernate the ability to extend it. You can extend its functionality without ever touching their source code.

public class FDPSequence : TableGenerator
{
    private const Int32 SeedValue = 1048576;
 
    private static readonly ILog Log = LogManager.GetLogger(typeof(FDPSequence));
 
    public override object Generate(ISessionImplementor session, object obj)
    {
        int counter = Convert.ToInt32(base.Generate(session, obj));
        return counter + SeedValue + 1;
    }
}

In order to use this custom generator class I setup my configuration to use it. Using Fluent I wrote a mapping like this:

public class InvestSpecMap : FDPEntityBaseMap<InvestSpec>
{
    public InvestSpecMap()
    {
        WithTable("InvestSpec");
        Id(x => x.Id).ColumnName(tablename + "ID")
            .SetGeneratorClass(typeof(FDPGenerator.FDPSequence).AssemblyQualifiedName)
            .AddGeneratorParam("table", "zSysCounters") //Name of the ID counter table
            .AddGeneratorParam("column", "InvestSpec"); //ID counter column for this table
        Map(x => x.AnnualDividend).ColumnName("AnnlzdDiv");
        Map(x => x.AssetClass);
        Map(x => x.CurrencyType);
        Map(x => x.CusipNumber).ColumnName("CusipNum");
        Map(x => x.DividendFreq).ColumnName("DivFrequency");
        Map(x => x.FullName);
        Map(x => x.MaturityDate).ColumnName("MaturityDt");
        Map(x => x.PriceAsOf).ColumnName("PriceAsOfDt");
        Map(x => x.ShortName).ColumnName("AbbrName");
        Map(x => x.TaxTreatment);
        Map(x => x.TickerSymbol);
        Map(x => x.Type);
        Map(x => x.UnitPrice);
    }
}

You can do the same with XML mappings just declare the assembly and class as you usually would in XML. This is actually the first time I've used FluentNH.

Part 2: Stealing from TableGenerator and controlling DDL generation

public string[] SqlCreateStrings(Dialect dialect) {
  string create = "create table " + tableName + " (";
  string insert = "insert into " + tableName + " values (";
  foreach (string s in allcolumns.Split(',')) {
    create += " " + s + " " + dialect.GetTypeName(columnSqlType) + ",";
    insert += " 1,";
  }
  create = create.Trim(',') + " )";
  insert = insert.Trim(',') + " )";
  return new[] {create, insert};
}

(Yes, yes, I know I should have learned from my last post and used a StringBuilder here as I pointed out in my last post, but seriously, this method will only ever be called once)

Now when it is called it will create the correct table with all the columns I need. A couple of example mappings for the database in FluentNH are shown below:

public InvestSpecMap() {
  WithTable("InvestSpec");
  Id(x => x.Id).ColumnName("InvestSpecID")
    .SetGeneratorClass(typeof (FdpSequenceGen).AssemblyQualifiedName)
    .AddGeneratorParam("allcolumns", "InvestSpec, InvestPrice")
    .AddGeneratorParam("table", "zSysCounters") //ID counter table name
    .AddGeneratorParam("column", "InvestSpec"); //ID counter column name

public PriceMap() {
  WithTable("InvestPrice");
  Id(x => x.Id).ColumnName("InvestPriceID")
    .SetGeneratorClass(typeof (FdpSequenceGen).AssemblyQualifiedName)
    .AddGeneratorParam("allcolumns", "InvestSpec, InvestPrice")
    .AddGeneratorParam("table", "zSysCounters") //ID counter table name
    .AddGeneratorParam("column", "InvestPrice"); //ID counter column name

To be completely "clean code" and avoid repeating myself I should probably factor out the Id() code into a method in an inhereted base class so I can call it from each mapping class like this:

protected void SetIdGenerator(string tablename) {
  Id(x => x.Id).ColumnName(TableName + "Id")
    .SetGeneratorClass(typeof(FdpSequenceGen).AssemblyQualifiedName)
    .AddGeneratorParam("allcolumns", AllColumns)
    .AddGeneratorParam("table", "zSysCounters") //ID counter table name
    .AddGeneratorParam("column", TableName); //ID counter column name
}

Now I can unit test using a temporary in-memory SQLite database and all my tests are passing.

Creating a custom id generator for nHibernate

Chris Nicola — Thu, 03 Sep 2009 16:41:21 GMT

Revision 1 posted to How to by Chris Nicola on 03/09/2009 01:41:21 p.m.

Part 1: Inheriting from TableGenerator class

It isn't complex, but it also isn't one of the identity strategies available in nHibernate and I fully intend to use nHibernate.

One of the beautiful things about nHibernate the ability to extend it. You can extend its functionality without ever touching their source code.

public class FDPSequence : TableGenerator
{
    private const Int32 SeedValue = 1048576;
 
    private static readonly ILog Log = LogManager.GetLogger(typeof(FDPSequence));
 
    public override object Generate(ISessionImplementor session, object obj)
    {
        int counter = Convert.ToInt32(base.Generate(session, obj));
        return counter + SeedValue + 1;
    }
}

In order to use this custom generator class I setup my configuration to use it. Using Fluent I wrote a mapping like this:

public class InvestSpecMap : FDPEntityBaseMap<InvestSpec>
{
    public InvestSpecMap()
    {
        WithTable("InvestSpec");
        Id(x => x.Id).ColumnName(tablename + "ID")
            .SetGeneratorClass(typeof(FDPGenerator.FDPSequence).AssemblyQualifiedName)
            .AddGeneratorParam("table", "zSysCounters") //Name of the ID counter table
            .AddGeneratorParam("column", "InvestSpec"); //ID counter column for this table
        Map(x => x.AnnualDividend).ColumnName("AnnlzdDiv");
        Map(x => x.AssetClass);
        Map(x => x.CurrencyType);
        Map(x => x.CusipNumber).ColumnName("CusipNum");
        Map(x => x.DividendFreq).ColumnName("DivFrequency");
        Map(x => x.FullName);
        Map(x => x.MaturityDate).ColumnName("MaturityDt");
        Map(x => x.PriceAsOf).ColumnName("PriceAsOfDt");
        Map(x => x.ShortName).ColumnName("AbbrName");
        Map(x => x.TaxTreatment);
        Map(x => x.TickerSymbol);
        Map(x => x.Type);
        Map(x => x.UnitPrice);
    }
}

You can do the same with XML mappings just declare the assembly and class as you usually would in XML. This is actually the first time I've used FluentNH.

Part 2: Stealing from TableGenerator and controlling DDL generation

public string[] SqlCreateStrings(Dialect dialect) {
  string create = "create table " + tableName + " (";
  string insert = "insert into " + tableName + " values (";
  foreach (string s in allcolumns.Split(',')) {
    create += " " + s + " " + dialect.GetTypeName(columnSqlType) + ",";
    insert += " 1,";
  }
  create = create.Trim(',') + " )";
  insert = insert.Trim(',') + " )";
  return new[] {create, insert};
}

(Yes, yes, I know I should have learned from my last post and used a StringBuilder here as I pointed out in my last post, but seriously, this method will only ever be called once)

Now when it is called it will create the correct table with all the columns I need. A couple of example mappings for the database in FluentNH are shown below:

public InvestSpecMap() {
  WithTable("InvestSpec");
  Id(x => x.Id).ColumnName("InvestSpecID")
    .SetGeneratorClass(typeof (FdpSequenceGen).AssemblyQualifiedName)
    .AddGeneratorParam("allcolumns", "InvestSpec, InvestPrice")
    .AddGeneratorParam("table", "zSysCounters") //ID counter table name
    .AddGeneratorParam("column", "InvestSpec"); //ID counter column name

public PriceMap() {
  WithTable("InvestPrice");
  Id(x => x.Id).ColumnName("InvestPriceID")
    .SetGeneratorClass(typeof (FdpSequenceGen).AssemblyQualifiedName)
    .AddGeneratorParam("allcolumns", "InvestSpec, InvestPrice")
    .AddGeneratorParam("table", "zSysCounters") //ID counter table name
    .AddGeneratorParam("column", "InvestPrice"); //ID counter column name

To be completely "clean code" and avoid repeating myself I should probably factor out the Id() code into a method in an inhereted base class so I can call it from each mapping class like this:

protected void SetIdGenerator(string tablename) {
  Id(x => x.Id).ColumnName(TableName + "Id")
    .SetGeneratorClass(typeof(FdpSequenceGen).AssemblyQualifiedName)
    .AddGeneratorParam("allcolumns", AllColumns)
    .AddGeneratorParam("table", "zSysCounters") //ID counter table name
    .AddGeneratorParam("column", TableName); //ID counter column name
}

Now I can unit test using a temporary in-memory SQLite database and all my tests are passing.

Your first NHibernate based application

Joe Doyle — Mon, 21 Dec 2009 22:30:32 GMT

Current revision posted to How to by Joe Doyle on 21/12/2009 07:30:32 p.m.

Your first NHibernate based application

Wiki extracted from the original blog post of Gabriel Schenker

Welcome to NHibernate

If you're reading this, we assume that you've just downloaded NHibernate and want to get started using it.

This tutorial will talk you through the following:

Installing NHibernate
Defining a simple business object class.
Create an NHibernate mapping to load and save the business object.
Configure NHibernate to talk to your local database.
Automatically generate a database
Writing simple CRUD code using the Repository pattern.
Using Unit Tests to make sure the code is working correctly.

This is what we're aiming for:

But first things first [:)]

Lets start by actually doing something with that ZIP file you just downloaded.

Installing NHibernate

If you've downloaded the NHibernate binaries in a zip file, all you need to do is extract that file to somewhere sensible. I usually create a folder called SharedLibs c:\Code\SharedLibs\NHibernate and extract the zip to there. But whatever you're comfortable with. This is your SharedLib folder from which you need to add your references to the NHibernate and NUnit dlls. Add references to NHibernate to both the demo project and the unit test project.

That's it! NHibernate is installed (easy huh). We'll talk you through using it with Visual Studio in a moment. First let's look at how we go about creating a project. Note this code is dependent on Visual Studio 2008 and .Net Framework 3.5.

Create Your Project

Before we start building our application and business objects, we'll need to create a blank project to put them in. Fire up Visual Studio and create a new Class Library project. Let's now look at something interesting: creating a business object.

Defining the Business Objects

Lets start by defining a very simple domain. For the moment it consists of one entity called Product. The product has 3 properties Name, Category and Discontinued.

Add a folder Domain to the FirstSample project of your solution. Add a new class Product.cs to this folder. The code is very simple and uses automatic properties (a feature of the new C# 3.0 compiler)

namespace FirstSolution.Domain
{
    public class Product
    {
        public string Name { get; set; }
        public string Category { get; set; }
        public bool Discontinued { get; set; }
    }
}

Now we want to be able to persist instances of this entity in a (relational) database. We have chosen NHibernatefor this task. An instance of an entity in the domain corresponds to a row in a table in the database. So we have to define a mapping between the entity and the corresponding table in the database. This mapping can be done either by defining a mapping file (an xml-document) or by decorating the entity with attributes. I'll start with the mapping file.

Define the Mapping

Create a folder Mappings in the FirstSample project. Add a new xml-document to this folder and call it Product.hbm.xml. Please note the "hbm" part of the file name. This is a convention used by NHibernate to automatically recognize the file as a mapping file. Define "Embedded Resource" as Build Action for this xml file.

In the Windows Explorer locate the nhibernate-mapping.xsd in the src folder of NHibernate and copy it to your SharedLibs folder. We can now use this xml schema definition file when defining our mapping files. VS will then provide intellisense and validation when editing an xml mapping document.

Back in VS add the schema to the Product.hbm.xml file

Let's start now. Each mapping file has to define a <hibernate-mapping> root node

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" 
                   assembly="FirstSolution" 
                   namespace="FirstSolution.Domain">
 
  <!-- more mapping info here -Removed spammer links->
  
</hibernate-mapping>

In a mapping file when referencing a domain class you always have to provide the fully qualified name of the class (e.g. FirstSample.Domain.Product, FirstSample). To make the xml less verbose you can define the assembly name (in which the domain classes are implemented and the namespace of the domain classes in the two attributes assembly and namespace of the root node. It's similar to the using statement in C#.

Now we have to first define a primary key for the product entity. Technically we could take the property Name of the product since this property must be defined and has to be unique. But it is common to use a surrogate key instead. For thus we add a property to our entity and call it Id. We use Guid as the type of the Id but it can as well be an int or a long.

using System;
 
namespace FirstSolution.Domain
{
    public class Product
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public string Category { get; set; }
        public bool Discontinued { get; set; }
    }
}

The complete mapping file

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" 
                   assembly="FirstSolution" 
                   namespace="FirstSolution.Domain">
  
  <class name="Product">
    <id name="Id">
      <generator class="guid" />
    </id>
    <property name="Name" />
    <property name="Category" />
    <property name="Discontinued" />
  </class>
  
</hibernate-mapping>

NHibernate doesn't restore get in our way such as that it defines many reasonable defaults. So if you don't provide a column name for a property explicitly it will name the original content due column according to the property. Or NHibernate can automatically infer the name of the table or the type of the column from the class definition. As a consequence my xml mapping file is not cluttered with redundant information. Please refer to the online documentation for more detailed explanation of the mapping. You can find it here.

Your solution explorer should look like this now (Domain.cd contains the class diagram of our simple domain). You will have added the design folder and created the class diagram yourself although this is for good practice and not required for the purposes of this excercise.

Configure NHibernate

We now have to tell NHibernate which database product we want to use and provide it the connection details in form of a connection string. NHibernate supports many many database products!

Add a new xml file to the FirstSolution project and call it hibernate.cfg.xml. Set its property "Copy to Output" to "Copy always". Since we are using SQL Server Compact Edition in this first sample enter the following information into the xml file

<?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.MsSqlCeDialect</property>
    <property name="connection.driver_class">NHibernate.Driver.SqlServerCeDriver</property>
    <property name="connection.connection_string">Data Source=FirstSample.sdf</property>
    
    <property name="show_sql">true</property>
  </session-factory>
</hibernate-configuration>

With this configuration file we tell NHibernate that we want to use MS SQL Server Compact Edition as our target database and that the name of the database shall be FirstSample.sdf (=connection string). We have also defined that we want to see the SQL NHibernate generates and sends to the database (highly recommended for debugging purposes during development). Double check that you have no typos in the code!

Add an error empty database called FirstSample.sdf to the FirstSample project (choose Local Database as template)

Click Add and ignore the DataSet creation wizard (just hit Cancel).

Test the Setup

It's now time to test our setup. First verify that you have the following files in your SharedLibs folder

The last 8 files you can find in the "Microsoft SQL Server Compact Edition" directory in your Programs folder.

Note: the System.Data.SqlServerCe.dll is located in the sub-folder Desktop.

All other files can be found in the NHibernate folder

Add a reference to the FirstSample project in your test project. Additionally add references to NHibernate.dll, nunit.framework.dll and Systm.Data.SqlServerCe.dll (remember to reference the files located in the SharedLibs folder!). Pay attention to set the property "Copy Local" to true for the assembly System.Data.SqlServerCe.dll since by default it is set to false! Add a copy of hibernate.cfg.xml to the root of this unit test project. Direct action with NHibernate in the NUnit project needs access to this file.

Add a class called GenerateSchema_Fixture to your test project. Your test project should now look like this

We further need the 7 files sqce*.dll in the output directory. We can do this by using a post-build event in VS. Enter the following command in the "Post-build event command line"

copy $(ProjectDir)..\..\SharedLibs\sqlce*.dll $(ProjectDir)$(OutDir)

Now add the following code to the GenerateSchema_Fixture file

using FirstSolution.Domain;
using NHibernate.Cfg;
using NHibernate.Tool.hbm2ddl;
using NUnit.Framework;
 
namespace FirstSolution.Tests
{
    [TestFixture]
    public class GenerateSchema_Fixture
    {
        [Test]
        public void Can_generate_schema()
        {
            var cfg = new Configuration();
            cfg.Configure();
            cfg.AddAssembly(typeof (Product).Assembly);
            
            new SchemaExport(cfg).Execute(false, true, false, false);
        }
    }
}

The first line of the test method creates a new instance of the NHibernate configuration class. This class is used to configure NHibernate. In the second line we tell NHibernate to configure itself. NHibernate will look out for configuration information since we do not provide any information here in the test method. So NHibernate will search for a file called hibernate.cfg.xml in the output directory. That's exactly what we want since we have defined our settings in this file.

In the third line of the code we tell NHibernate that it can find mapping information in the assembly which contains also the class Product. At the time being it will only find one such file (Product.hbm.xml) as an embedded resource.

The fourth line of code uses the SchemaExport helper class of NHibernate to auto-"magically" generate the schema in the database for us. SchemaExport will create the product table in the database and each time you call it it will delete the table and the table data and recreate it.

Note: with this test method we do NOT want to find out whether NHibernate does its job correctly (you can be sure it does) but rater whether we have setup our system correctly. However, you can check the database and see the newly created 'product' table.

If you have TestDriven.Net installed you can now just right click inside the test method and choose "Run Test(s)" to execute the test.

If every thing is ok you should see the following result in the output window

If you have ReSharper installed you can just start the test by clicking the yellow-green circle on the siteleft border and choose Run. Use

The result is as follows

In case of Problems

If your test fails double check that you find the History tab following files in your target directory (that is: m:dev\projects\FirstSolution\src\FirstSolution.Tests\bin\debug)

Double check also if you have no typos in the NHibernate configuration file (hibernate.cfg.xml) or in the mapping file (Product.hbm.xml). Finally check whether you have set the "Build Action" of the mapping file (Product.hbm.xml) to "Embedded Resource". Only continue if the test succeeds.

Our first CRUD operations

Now obviously our system is ready to start. We have successfully implemented our Domain, defined the mapping files and configured NHibernate. Finally we have used NHibernate to automatically generate the database schema from our Domain (and our mapping files).

In the spirit of DDD (see e.g. Domain Driven Design by Eric Evans) we define a repository for all crud operations (create, read, update and delete). The repository interface is part of the domain where as the implementation is not! The implementation is infrastructure specific. We want to keep our domain persistence ignorant (PI).

Add a new interface to the domain folder of our FirstSolution project. Call it IProductRepository. Let's define the following interface

using System;
using System.Collections.Generic;
 
namespace FirstSolution.Domain
{
    public interface IProductRepository
    {
        void Add(Product product);
        void Update(Product product);
        void Remove(Product product);
        Product GetById(Guid productId);
        Product GetByName(string name);
        ICollection<Product> GetByCategory(string category);
    }
}

Add a class ProductRepository_Fixture to the test project of the solution and add the following code

[TestFixture]
    public class ProductRepository_Fixture
    {
        private ISessionFactory _sessionFactory;
        private Configuration _configuration;
 
        [TestFixtureSetUp]
        public void TestFixtureSetUp()
        {
            _configuration = new Configuration();
            _configuration.Configure();
            _configuration.AddAssembly(typeof (Product).Assembly);
            _sessionFactory = _configuration.BuildSessionFactory();
        }
    }

In the fourth line of the method TestFixtureSetUp we create a session factory. This is an expensive process and should thus be executed only once. That's the reason why I put it into this method which is only executed once during a test cycle.

To keep our test methods side effect free we re-create our database schema before the execution of each test method. Thus we add the following method

[SetUp]
        public void SetupContext()
        {
            new SchemaExport(_configuration).Execute(false, true, false, false);
        }

And now we can implement the test method to add a new product instance to the database. Start by adding a new folder called Repositories to your FirstSolution project. Add a class ProductRepository to this folder. Make theProductRepository inherit from the interface IProductRepository.

using System;
using System.Collections.Generic;
using FirstSolution.Domain;
 
namespace FirstSolution.Repositories
{
    public class ProductRepository : IProductRepository
    {
        public void Add(Product product)
        {
            throw new NotImplementedException();
        }
 
        public void Update(Product product)
        {
            throw new NotImplementedException();
        }
 
        public void Remove(Product product)
        {
            throw new NotImplementedException();
        }
 
        public Product GetById(Guid productId)
        {
            throw new NotImplementedException();
        }
 
        public Product GetByName(string name)
        {
            throw new NotImplementedException();
        }
 
        public ICollection<Product> GetByCategory(string category)
        {
            throw new NotImplementedException();
        }
    }
}

Manipulating Data

Now go back to the ProductRepository_Fixture test class and implement the first test method

      [Test]
        public void Can_add_new_product()
        {
            var product = new Product {Name = "Apple", Category = "Fruits"};
            IProductRepository repository = new ProductRepository();
            repository.Add(product);
        }

The first run of the test method will fail since we have not yet implemented the Add method in the repository class. Let's do it. But wait, we have to define a little helper class first which provides us session objects on demand.

using FirstSolution.Domain;
using NHibernate;
using NHibernate.Cfg;
 
namespace FirstSolution.Repositories
{
    public class NHibernateHelper
    {
        private static ISessionFactory _sessionFactory;
 
        private static ISessionFactory SessionFactory
        {
            get
            {
                if(_sessionFactory == null)
                {
                    var configuration = new Configuration();
                    configuration.Configure();
                    configuration.AddAssembly(typeof(Product).Assembly);
                    _sessionFactory = configuration.BuildSessionFactory();
                }
                return _sessionFactory;
            }
        }
 
        public static ISession OpenSession()
        {
            return SessionFactory.OpenSession();
        }
    }
}

This class creates a session factory only the first time a client needs a new session.

Now we can define the Add method in the ProductRepository as follows

 public void Add(Product product)
        {
            using (ISession session = NHibernateHelper.OpenSession())
                using (ITransaction transaction = session.BeginTransaction())
                {
                    session.Save(product);
                    transaction.Commit();
                }
        }

The second run of the test method will again fail with the following message

That's because NHibernate is by default configured to use lazy load for all entities. That is the recommended approach and I warmly recommend not to change it for a maximum of flexibility.

How can we solve this issue? It's easy we have to just make all our properties (and methods) of the domain object(s) virtual. Let's do this for our Product class

    public class Product
    {
        public virtual Guid Id { get; set; }
        public virtual string Name { get; set; }
        public virtual string Category { get; set; }
        public virtual bool Discontinued { get; set; }
    }

Now run the test again. It should succeed and we get the following output

Note the sql spit out by NHibernate.

Now we think that we have successfully inserted a new product into the database. But let's test it whether it is really so. Let's extend our test method

        [Test]
        public void Can_add_new_product()
        {
            var product = new Product {Name = "Apple", Category = "Fruits"};
            IProductRepository repository = new ProductRepository();
            repository.Add(product);
 
            // use session to try to load the product
            using(ISession session = _sessionFactory.OpenSession())
            {
                var fromDb = session.Get<Product>(product.Id);
                // Test that the product was successfully inserted
                Assert.IsNotNull(fromDb);
                Assert.AreNotSame(product, fromDb);
                Assert.AreEqual(product.Name, fromDb.Name);
                Assert.AreEqual(product.Category, fromDb.Category);
            }
        }

Run the test again. Hopefully it will succeed...

Now we are ready to implement also the other methods of the repository. For testing this we would rather have a repository (that is database table) already containing some products. Nothing easier than this. Just add a method CreateInitialData to the test class as follows

private readonly Product[] _products = new[]
                 {
                     new Product {Name = "Melon", Category = "Fruits"},
                     new Product {Name = "Pear", Category = "Fruits"},
                     new Product {Name = "Milk", Category = "Beverages"},
                     new Product {Name = "Coca Cola", Category = "Beverages"},
                     new Product {Name = "Pepsi Cola", Category = "Beverages"},
                 };
 
        private void CreateInitialData()
        {
            
            using(ISession session = _sessionFactory.OpenSession())
                using(ITransaction transaction = session.BeginTransaction())
                {
                    foreach (var product in _products)
                        session.Save(product);
                    transaction.Commit();
                }
        }

Call this method from the SetupContext method (after the create schema call) and we are done. Now each time after the database schema is created the database is populated with some products.

Let's test the Update method of the repository with the following code

        [Test]
        public void Can_update_existing_product()
        {
            var product = _products[0];
            product.Name = "Yellow Pear";
            IProductRepository repository = new ProductRepository();
            repository.Update(product);
 
            // use session to try to load the product
            using (ISession session = _sessionFactory.OpenSession())
            {
                var fromDb = session.Get<Product>(product.Id);
                Assert.AreEqual(product.Name, fromDb.Name);
            }
        }

When running for the first time this code will fail since the Update method has not yet been implemented in the repository. Note: This is the expected behavior since in TDD the first time you run a test it should always fail!

Analogous to the Add method we implement the Update method of the repository. The only difference is that we call the update method of the NHibernate session object instead of the save method.

        public void Update(Product product)
        {
            using (ISession session = NHibernateHelper.OpenSession())
            using (ITransaction transaction = session.BeginTransaction())
            {
                session.Update(product);
                transaction.Commit();
            }
        }

Run the test again an watch it succeed.

The delete method is straight forward. When testing whether the record has really been deleted we just assert that the value returned by the session's get method is equal to null. Here is the test method

       [Test]
        public void Can_remove_existing_product()
        {
            var product = _products[0];
            IProductRepository repository = new ProductRepository();
            repository.Remove(product);
 
            using (ISession session = _sessionFactory.OpenSession())
            {
                var fromDb = session.Get<Product>(product.Id);
                Assert.IsNull(fromDb);
            }
        }

and here the implementation of the Remove method in the repository

        public void Remove(Product product)
        {
            using (ISession session = NHibernateHelper.OpenSession())
                using (ITransaction transaction = session.BeginTransaction())
                {
                    session.Delete(product);
                    transaction.Commit();
                }
        }

Querying the Database

We still have to implement the three methods which query the database for objects. Let's start with the most easy one, the GetById. First we write the test

        [Test]
        public void Can_get_existing_product_by_id()
        {
            IProductRepository repository = new ProductRepository();
            var fromDb = repository.GetById(_products[1].Id);
            Assert.IsNotNull(fromDb);
            Assert.AreNotSame(_products[1], fromDb);
            Assert.AreEqual(_products[1].Name, fromDb.Name);
        }

and then the code to fulfill the test

        public Product GetById(Guid productId)
        {
            using (ISession session = NHibernateHelper.OpenSession())
                return session.Get<Product>(productId);
        }

Now that was easy. For the following two methods we use a new method of the session object. Let's start with the GetByName method. As usual we write the test first

       [Test]
        public void Can_get_existing_product_by_name()
        {
            IProductRepository repository = new ProductRepository();
            var fromDb = repository.GetByName(_products[1].Name);
 
            Assert.IsNotNull(fromDb);
            Assert.AreNotSame(_products[1], fromDb);
            Assert.AreEqual(_products[1].Id, fromDb.Id);
        }

The implementation of the GetByName method can be done by using two different approaches. The first is using HQL (Hibernate Query Language) and the second one HCQ (Hibernate Criteria Query). Let's start with HQL. HQL is a object oriented query language similar (but not equal to) SQL.

To be added: implemetation of GetByName using HQL. Implement HCQ as below this works as expected and returns a product entity.

In the above sample I have introduced a commonly used technique when using NHibernate. It's called fluent interfaces. As a result the code is less verbose and easier to understand. You can see that a HQL query is a string which can have embedded (named) parameters. Parameters are prefixed by a ':'. NHibernate defines many helper methods (like SetString used in the example) to assign values of various types to those parameters. Finally by using UniqueResult I tell NHibernate that I expect only one record to return. If more than one record is returned by the HQL query then an exception is raised. To get more information about HQL please read the online documentation.

The second version uses a criteria query to search the requested product. You need to add a reference to NHibernate.Criterion on your repository page.

        public Product GetByName(string name)
        {
            using (ISession session = NHibernateHelper.OpenSession())
            {
                Product product = session
                    .CreateCriteria(typeof(Product))
                    .Add(Restrictions.Eq("Name", name))
                    .UniqueResult<Product>();
                return product;
            }
        }

Many users of NHibernate think that this approach is more object oriented. On the other hand a complex query written with criteria syntax can quickly become difficult to understand.

The last method to implement is GetByCategory. This method returns a list of products. The test can be implemented as follows

        [Test]
        public void Can_get_existing_products_by_category()
        {
            IProductRepository repository = new ProductRepository();
            var fromDb = repository.GetByCategory("Fruits");
 
            Assert.AreEqual(2, fromDb.Count);
            Assert.IsTrue(IsInCollection(_products[0], fromDb));
            Assert.IsTrue(IsInCollection(_products[1], fromDb));
        }
 
        private bool IsInCollection(Product product, ICollection<Product> fromDb)
        {
            foreach (var item in fromDb)
                if (product.Id == item.Id)
                    return true;
            return false;
        }

and the method itself might contain the following code

        public ICollection<Product> GetByCategory(string category)
        {
            using (ISession session = NHibernateHelper.OpenSession())
            {
                var products = session
                    .CreateCriteria(typeof(Product))
                    .Add(Restrictions.Eq("Category", category))
                    .List<Product>();
                return products;
            }
        }

Summary

In this article I have shown you how to implement a basic sample domain, define the mapping to a database and how to configure NHibernate to be able to persist domain objects in the database. I have shown you how to typically write and test CRUD methods for your domain objects. I have taken MS SQL Compact Edition as sample database but any other supported database can be used (you only have to change the hibernate.cfg.xml file accordingly). Ee have no dependencies on external frameworks or tools other than the database and NHibernate itself (.NET of course never counts here).

Your first NHibernate based application

Joe Doyle — Mon, 21 Dec 2009 22:30:17 GMT

Revision 12 posted to How to by Joe Doyle on 21/12/2009 07:30:17 p.m.

Your first NHibernate based application

san diego wedding photographer san diego wedding photographers

-Removed spammer links, but can't restore the original content due to an error on the site. Use the History tab to read the article.