Hey NHibernate, Don’t Mess With My Enums!

So I’ve been using Fluent NHibernate for a short while now. Initially I had to overcome some minor challenges, but since I got those out of the way it’s been pretty smooth sailing. One thing that stands out, which required more tinkering and timeshare than I would’ve liked is the way NHibernate handles the .NET enum type. Natively NHibernate allows you to save your enum’s value as a string or number property/column in the referencing object’s table. In other words, by default it doesn’t allow you to map your enum to its own separate table, and then let your objects refer to it through an association/foreign key. For NHibernate enums are primitive values, and not “entity objects” (logically speaking – ignoring the technical internal mechanics of .NET’s enum). I would argue that enums can be both a primitive string or number, or a more complex entity. Under certain circumstances an enum can be viewed as a simple “object” that consists of two properties:

  • An Id, represented by the enum member’s number value
  • And a name, represented by the enum member’s string name.

I’ve found that it’s very convenient to use the “entity object” version of enums for very simple, slow changing look-up data with a fair amount of business logic attached to it. For instance in a credit application app, you might only support 3 or 4 types of loans, but you know that over time app’s life, the company won’t add more than 2 or 3 new types of loans. Adding a loan type requires some additional work, and isn’t merely a matter of just inserting a new loan type into a look-up table. The reason is that a fair amount of the app’s business logic, mainly in the form of conditional logic statements, must also be adapted to accommodate the new loan type. From a coding perspective it’s very convenient to use enum types in these cases, because you can refer to the various options through DRY strong typed members, with a simultaneous string and number representation. So instead of

var loan = loanRepository.FindById(234);
var loanType = loanTypeRepository.FindById(123);

// ...

if (loan.Type == "PersonalLoan")
    // ...

rather do

var loan = loanRepository.FindById(234);

if (loan.Type == LoanType.Personal)

Okay, schweet, you get the point. Next logical question: How do you get NHibernate to treat your enums as objects with their own table, and not primitive values? To do this you have to create a generic class that can wrap your enum types, and then create a mapping for this enum  wrapper class. I call this class Reference:

public class Reference<TEnum>
    private TEnum enm;

    public Reference(TEnum enm)
        this.enm = enm;

    public Reference() {}

    public virtual int Id
        get { return Convert.ToInt32(enm); }
        set { enm = (TEnum)Enum.Parse(typeof(TEnum), value.ToString(), true); }

    public virtual string Name
        get { return enm.ToString(); }
        set { enm = (TEnum)Enum.Parse(typeof(TEnum), value, true); }

    public virtual TEnum Value
        get { return enm; }
        set { enm = value; }

The Reference class is pretty straight forward. All it does is translate the contained enum into an object with three properties:

  • Id – the integer value of the enum member.
  • Name – the string name of the enum member.
  • Value – the contained enum member.

You might wonder why I didn’t bother to restrict the allowed generic Types to enums. Well, it so happens that .NET generics doesn’t allow you to restrict generic type declarations to enums. It allows you to restrict generic types to structs, and all sort of other things, but not to enums. So you will never be able to get an exact generic restriction for the Reference class. So I thought, aag what the hell, if I can’t get an exact restriction, then what’s the point anyways? I’ll have to trust that whoever is using the code, knows what he’s doing.

Now, for example, instead of directly using the LoanTypes enum, the Loan class’s Type property will be a Reference object, with its generic type set to the LoanTypes enum:

public class Loan
    // ...
    public Reference<LoanType> Type { get; set; }
    // ...

This is not completely tidy, because to a degree the limitations of the data access infrastructure, i.e. NHibernate, force us to adopt a compromise solution that’s not necessary if we changed to something else. In other words things from the data infrastructure layers spills into the domain.

What’s left to do is (1) create a mapping for Reference<LoanType>, and (2) get NHibernate to use the right table name, i.e. LoanType, instead of Reference[LoanType]. Here the Fluent NHibernate mapping for Reference<LoanType>:

public class LoanTypeMap: ClassMap<Reference<LoanType>>
    public LoanTypeMap()
        Id(loanType => loanType.Id).GeneratedBy.Assigned();
        Map(loanType => loanType.Name);

The above Fluent NHibernate mapping tells NHibernate to use whatever value property Id has for the primary key, and not generate one for it. You also have to explicitly specify the table’s name you’d like NHibernate to use, because you want to ignore “Reference” as part of the table name, and only use the enum type name.

And that’s it. You will now have a separate table called LoanType, with the foreign keys of other classes’ tables referencing the LoanType enum’s table. Just keep in mind that this solution might not always be feasible. For example it might not work too well when you write a multilingual application. Also should you want to get a pretty description for each enum’s member, for example “Personal Loan”, instead of “PersonalLoan” you’ll have to throw in some intelligent text parsing that split’s a text string before each uppercase character. Hopefully this post gave you another option to map your enum types with NHibernate.


Fluent NHibernate on PostgreSQL

When you write your first Fluent NHibernate application with Mono/.NET based on the Getting started tutorial, you eventually discover that you require a few extra assembly-dll references not mentioned. For my Postgres (PostgreSQL) project my references are:

Fluent NHibernate References

I won’t go into the detail of the matter, other than to say that many of these don’t give you a very clear indication as to what exactly is missing.

To configure Fluent NHibernate to work with Postgres you will need the following:

var connectionStr = "Server=;Port=5432;Database=the_db;User Id=user_name;Password=password;"
ISessionFactory sessionFactory = Fluently
 .Mappings(m => m.FluentMappings.AddFromAssemblyOf<TypeOfFluentNHibernateMapping>())

private static void BuildSchema(Configuration config)
// This NHibernate tool takes a configuration (with mapping info in)
// and exports a database schema from it.
var dbSchemaExport = new SchemaExport(config);
//dbSchemaExport.Drop(false, true);
dbSchemaExport.Create(false, true);

TypeOfFluentNHibernateMapping is a class that inherits from FluentNHibernate.Mapping.ClassMap<T>. This tells Fluent to load all ClassMappings from the assembly where this type is defined.

BuildSchema(…) creates the database’s schema based on the specified mapping configuration and recreates the tables and the rest of it in the database specified by the connection string. I included the call to the schema export’s drop method, because the code originates from my unit tests, where I drop & recreate the database on each test run.

So far I like Fluent NHibernate, and the only complaint I have so far is the way NHibernate (not Fluent) handles enums. It assumes you want to use the enum member’s string name. The way I like to store my enums, are to have a separate table for them.

Sexy Transactions In Spring.NET

Spring.NET really has tons of handy features you can put to work in your application. One of them is its transaction management, that provides an implementation agnostic abstraction for your application. The first question you’re probably asking yourself is, why do you need to abstract away your transaction technology? The short answer is there are various ways of using transactions and different transaction technologies: ADO.NET, Enterprise Services, System.Transactions, and the different transaction implementations for other database technologies like ORMs (NHibernate). I won’t go into the detail of these, suffice to say that choosing, using and changing a transaction technology can become a complicated affair, and that Spring.NET protects your application from these ugly details. I encourage you to take a deep dive into the official Spring.NET documentation, should you want to know more about the details behind this.

What I did was, write a helper class that makes it a little easier and cleaner to use Spring.NET’s transaction management:

/// <summary>
 /// Manages database transactions by wrapping executed
 /// methods in a transaction.
 /// </summary>
 public static class Transaction
 /// <summary>
 /// Executes the specified method in a transaction.
 /// </summary>
 public static void Execute(Action method, bool commitChanges = true)
 // Create a new transaction
 Instantiate.New<ITransactionOperations>().Execute(delegate(ITransactionStatus transaction)
 // If changes should not be committed, make
 // sure we roll back once done
 if (!commitChanges) transaction.SetRollbackOnly();
 return null;

/// <summary>
 /// Instantiates objects based on their name.
 /// </summary>
 public static class Instantiate
 #region Fields

 private static IApplicationContext context;


 #region Get objects from ApplicationContext

 /// <summary>
 /// Retrieves the object with the specified name.
 /// </summary>
 /// <typeparam name="T">The type of object to return.
 /// <param name="name">The name of the object.</param>
 /// <returns>A newly created object with the type specified by the name, or a singleton object if so configured.</returns>
 public static T New<T>(string name)
 return (T)Context.GetObject(name);

 /// <summary>
 /// Retrieves the object of the specified type.
 /// </summary>
 public static T New<T>()
 return (from obj in Context.GetObjectsOfType(typeof(T)).Values.Cast().ToList()
 select obj).FirstOrDefault();

 /// <summary>
 /// Retrieves the object with the specified name, and constructor arguments.
 /// </summary>
 public static T New<T>(string name, object[] arguments)
 return (T)Context.GetObject(name, arguments);


 #region Private methods

 /// <summary>
 /// Gets the context.
 /// </summary>
 /// <value>The context.</value>
 private static IApplicationContext Context
 if (context == null)
 context = ContextRegistry.GetContext();

 return context;


Class Instantiate

The Instantiate class is a wrapper for Spring.NET’s IApplicationContext, with a few helper methods. It provides three overloads of the method New<T>, that instantiates new objects from the ApplicationContext configuration. The most important method is New<T> without any arguments. This method simply grabs the first instance of the requested Type it finds in the ApplicationContext. The other two variations allow you to instantiate a new object with a specific id.

With the help of the Instantiate class the Transaction class will obtain the specified transaction management from Spring’s ApplicationContext.

Class Transaction

This is where all the action really happens as far as database transactions go. The Transaction class has a single Execute method, that transparently wraps any block of code in a transaction. There are a small number of things you have to do to use Spring’s transaction management:

1. First get an instance of your chosen transaction implementation.

2. Call Execute with a delegate with an ITransactionStatus argument.

3. When you’re done, you have to return something, even if it’s null.

My goal is to remove the above 3 requirements, so I can just pass a block of code through to get it wrapped in a transaction. This is exactly what Execute achieves:

Transaction.Execute(() =>
 // Get a FileTypeRepository
 var fileTypeRepository = Instantiate.New();

 // Add an object

 // Save the changes to the database


Spring.NET provides 4 transaction implementations:

  • Spring.Data.Core.AdoPlatformTransactionManager – local ADO.NET based transactions
  • Spring.Data.Core.ServiceDomainPlatformTransactionManager – distributed transaction manager from Enterprise Services
  • Spring.Data.Core.TxScopeTransactionManager – local/distributed transaction manager from System.Transactions.
  • Spring.Data.NHibernate.HibernateTransactionManager – local transaction manager for use with NHibernate or mixed ADO.NET/NHibernate data access operations.

To configure your transaction manager for the ApplicationContext you need:

<objects xmlns="http://www.springframework.net">
 <object id="transactionManager"
 type="Spring.Data.Core.TxScopeTransactionManager, Spring.Data" />
 <object id="transactionTemplate"
 type="Spring.Transaction.Support.TransactionTemplate, Spring.Data"
 autowire="constructor" />

Spring.NET’ provides a TransactionTemplate that handles all the necessary transaction logic and resources like commits, rollbacks, and errors. It frees you from getting involved with low level transaction management details, and provides all transaction management out of the box. If you desire you can directly access a transaction manager, through an IPlatformTransactionManager interface, to manage the details of your transaction process.

TransactionTemplate implements the ITransactionOperations interface. It has a constructor that requires a IPlatformTransactionManager instance. All transaction managers implement the IPlatformTransactionManager interface. This means TransactionTemplate contains the specified transaction manager. The required transaction manager is provided to the TransactionTemplate through autowiring of the constructor’s parameters. The autowiring searches through the objects and returns the first configured object that implements the IPlatformTransactionManager interface, which is transactionManager in  our case.

Object transactionManager is an instance of TxScopeTransactionManager, that is in turn based on System.Transactions.

Transaction.Execute has a second optional parameter, commitChanges. The commitChanges argument defaults to true, so the transaction manager will always attempt to commit changes. I use it when I test my application, and I want all test data to be rolled back.

Based on the fact that it commitChanges is an optional parameter, you’ve probably figured out by now that I recompiled Spring.NET to run on .NET 4.

The example IFileTypeRepository is actually based on Entity Framework 4 with Plain Old CLR/C# Objects (POCO). Just in case you were wondering if Spring.NET’s transaction management works with Entity Framework.

The Smart Way To Test Your Data Access Code

A very important factor to determine before writing any unit test, is what its scope should be. In other words a unit test should be written with clear objectives in mind, as to what it’s going to test, and anything outside this scope should be ignored as far as possible. This means that if we would like to verify business rules applied to data, then we should not test anything else. This includes the low level data access methods responsible for returning and transforming the data from the database.

Mocks come in extremely handy in this scenario. They can be used to return generated data from my data access methods that reflect an ideal situation where result-sets are 100% correct. However this creates an overly artificial test setup, which completely ignores the role of data access methods. Mistakes in the underlaying queries also go unnoticed until its too late.

Testing data access code, and including them in the scope of unit tests is a very cumbersome process. Hence I give them very low priority in my unit tests.

The most straightforward option is to follow the following process:

  1. Insert generated objects with the expected initial state.
  2. Fetch the inserted objects from the database, and execute business logic operations on them.
  3. Update the objects.
  4. Re-fetched them again, and delete the returned objects to clean-up the database.

With this approach problems start when unit tests fail midway, and bits and pieces of the broken objects’ state remain scattered across the development database. Over time it gets messy, and often the broken data affects other queries and unit tests. This means that we lose the isolated characteristic of a good unit test, since one unit test can cause unwanted effects in other unit tests through the central database. Very bad indeed.

So what can we do to solve this problem? Luckily Hendry Luk has a whole series on this very problem and how we can solve it. He suggests the following smart ways to test data access code:

  1. In-memory database using Sqlite
  2. Transaction rollback
  3. Restorable file-based DB
  4. Preset state with NDBUnit
  5. Fake DB provider with Linq

Setting up MySQL for Python (MySQLdb) on Mac OS X

MySQL is an excellent, very popular and open source database management system. A binary installation is available for Mac OS, and the process of installing MySQL on Mac OS is also very well documented. So just head over to MySQL.com, where you will find the binary installation, together with extensive installation, and usage documentation.

To connect to MySQL from Python use MySQL for Python (MySQLdb). Get MySQLdb from http://sourceforge.net/projects/mysql-python/.

    1. Download MySQL from mysql.com and install the DBMS as instructed in the documentation.
    2. Make sure “Library/Python/2.3” does not have:
  • Directory “MySQL”
  • File “_mysql.pyd”
  • File “_mysql_exceptions.py”
  • File “_mysql_exceptions.pyc”

Either uninstall MySQL for Python if a setup program was used to install it, otherwise manually delete them.

  1. Download and unpack MySQL for Python. At the time of writing this is MySQL-python-1.2.1_p2.tar.gz .
  2. Open Terminal and change to the directory where MySQLdb was unpacked to.
  3. Get rid of any previous builds that might interfere, by deleting the “build” directory if it exists.
  4. Edit the setup.py file, and change:

    return popen(“mysql_config –%s” % what)
    return popen(“/usr/local/mysql/bin/mysql_config –%s” % what)

  5. Cleanup any previous install attempts:

    python setup.py clean

  6. Build MySQLdb:

    python setup.py build

Viola! MySQL for Python is all setup, and ready for your Python-MySQL data access code. Enjoy!

SQL Server Error: OLE DB error trace [OLE/DB Provider ‘SQLOLEDB’ IUnknown::QueryInterface returned 0x80004005: The provider did not give any information about the error.]


You receive the following error trying to execute a query against a linked server:

Server: Msg 7399, Level 16, State 1, Line [Line Number]
OLE DB provider ‘SQLOLEDB’ reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider ‘SQLOLEDB’ IUnknown::QueryInterface returned 0x80004005: The provider did not give any information about the error.]


Turn off the SQLOLEDB provider options

  • LevelZeroOnly
  • IndexAsAccessPath (not always required)
  1. In SQL Server 2000 Enterprise Manager, expand the server node, then the security node. Right click on the “Linked Servers” node. Select the “New Linked Server… ” option to add a new linked server.
  2. Complete the fields as follows:
    • Linked server: Type the logical name for the SQL Server that will be used in SQL queries.
    • Other data source, Provider name: Select Microsoft OLE DB Provider for SQL Server.
    • Product name: SQLOLEDB.
    • Data source: Network name of the SQL Server.
    • Provider string: Can be left empty.
    • Catalog: Database that will be connected to.
  3. Edit the Provider Options by clicking the “Provider Options…” button.

    • Uncheck “Level zero only”.
    • Depending on your specific scenario, optionally uncheck “Index as access path”.

    After completing the above tasks you can continue setting the properties for the new linked server as you would under normal circumstances.