Software Warlock

Many times when working with a legacy relation model and a newly developed C# object model that sits on top of the relational model, you may need to map enum values to/from arbitrary strings in the database using NHibernate.

For example, suppose you have a a work order object that three possible states: request, approved, denied. To represent this, you might use the following enum:

enum WorkRequestState
{
    Request,
    Approved,
    Denied
};

But let’s assume that the legacy relational model has represented these values as strings: REQ, APR, DEN. You can’t change the values for the relational model, and you certainly don’t want to use these values in your enum because they are not as readable as the values shown above.

By default, NHibernate will automatically convert you enum to a string value that matches the name of the enum values if the table field a string, or it will convert to the integer value for the enum options if the table field is an integer.

To do the above custom mapping we must implement a custom NHibernate type.

First, create a new class that inherits NHibernate.Type.EnumStringType. In your constructor, pass the type of the enum you are handling, and the maximum number of characters that the enum values will be to the base class constructor:

class WorkRequestStateEnumStringType : NHibernate.Type.EnumStringType
{
    public WorkRequestStateEnumStringType()
        : base( typeof(WorkRequestState), 3)
    {
    }
    ...
}

Next, override the GetValue(...) method to map the enum value to the equivalent string.

public override object GetValue(object enm)
{
	if( null == enm )
		return String.Empty;

	switch( (WorkRequestState)enm )
	{
		case WorkRequestState.Request	: return "REQ";
		case WorkRequestState.Approved	: return "APR";
		case WorkRequestState.Denied	: return "DEN";
		default : throw new ArgumentException("Invalid WorkRequestState.");
	}
}

Override the GetInstance(...) method to map a string value to the equivalent enum value:

public override object GetInstance(object code)
{
	code = code.ToUpper();

	if( "REQ".Equals(code) )
		return WorkRequestState.Request;
	else if( "APR".Equals(code) )
		return WorkRequestState.Approved;
	else if( "DEN".Equals(code) )
		return WorkRequestState.Denied;

	throw new ArgumentException(
		"Cannot convert code '" + code + "' to WorkRequestState.");
}

Finally, in the NHibernate mapping file, specify the property’s type as the derived EnumStringType class.

<class name="WorkRequest" table="work_request_table">
  <property
    name="Status"
    column="status_field"
    type="my.namespace. WorkRequestStateEnumStringType, MyAssembly" />
  ...
<class>

Note that when specifying this type, you must use the assembly-qualified name because the NHibernate code doing the mapping is in a separate assembly from the type you created.

This information has been derived from Jeremy Miller’s original post here.

I’m just getting started with CoreData, and I’ve been excited about about its nice graphical environment to define your model.

CoreData’s approach to ORM is somewhat different than other tools, such as Hibernate, because it takes advantage of Objective-C’s dynamic nature, and avoids some of the clutter problems you experience with more statically typed languages. Instead of providing mapping information for classes that are defined elsewhere, all modeled objects are instances of NSManagedObject and the system customizes the accessors on the created objects based on the schema.

This is very nice when you are defining your model, and when you can use things like Cocoa Bindings to provide much of the system’s behavior based on state of the model, but when you need to start writing custom controller logic, it isn’t very readable to have everything going around as an NSManagedObject. You can use the dynamically generated properties on these objects, but you will get compiler warnings. Annoying.

You can certainly subclass NSManagedObject and tell the model to use your implementation. This is necessarily if you are going to provide custom behavior on the model objects above the simple data oriented behavior CoreData provides for you. Xcode will even generate source code for you from your data model.

While in the data model editor, do File, New File… and select Managed Object Class from the Cocoa category.

Choose the location where your files will be generated.

Select the model entities for which classes will be generated and click Finish.

The problem with this approach is that you are stuck manually keeping the model and the custom classes in sync. You can certainly regenerate, but if you have made any changes to the files, they will be overwritten.

One alternative to this manual update process is a tool, mogenerator, that will automatically generate custom classes for you. It even goes so far as two generate two classes for each model object:
NSManagedObject <— GenClassA <— GenClassB (where <— represents inherits from). This allows you to modify the code in GenClassB and leave GenClassA alone, which stores the core information from the model. This way you can regenerate GenClassA as needed, without overwriting your custom behavior in GenClassB. PyrusMalus has an introduction on how to get started with mogenerator, Important Shock has a discussion about the approach used by the tool, and Cocoa & Medicine has a description of how to run mogerator from within Xcode using Apple Script.

There is some dissension about if the method of generating custom classes is a good thing. On this thread on CocoaBuilder Ben (who appears to be from Apple according to LinkedIn) seems to be against the idea, though I his main objection is to the idea that the custom accessors generated by NSManagedObject are slower than those statically generated by mogenerator.

One of Apples recommended approaches to avoid compiler warnings is to created categories for NSManagedObject so that the compiler accepts that the accessors you are using are actually there. Xcode provides the ability to copy the attribute declarations for use in these categories (or in custom classes), but to cover everything, you need to copy two separate sets of declarations and paste them into the code. You could use the Xcode custom class generation to get the signatures you need, but there is a still a bit of manual work in the process.

In my opinion, Apple needs to work more to help developers with this problem. I like the approach taken by mogenerator, and I would like to see that same regenerative ability in the built-in Xcode tools, using the 2-class generation-gap design pattern to allow you to make changes to the generated classes without having the changes overwritten next time you re-generate.

Long of the short of it is there isn’t a good way to do it.

A common example is that you might want select multiple rows from a table, using a list of IDs:

SELECT * FROM room WHERE room_id IN (1,2,3,4);

What you’d like to do is have this list as an array parameter and set it appropriately on the command:


int ids = new int[] { 1, 2, 3, 4 };
string sql = "SELECT * FROM room WHERE room_id IN @IDS;";

// DOES NOT WORK!
cmd.Parameters.Add("@IDS", ids);


The problem is there is no array type in SQL server/ADO.NET. The only way to accomplish this is through dynamic sql, which is definitely not a best practice.

There are several articles discussing this problem. The following are taken from this forum discussion in which Arnie Rowland provided the links.

• http://www.sommarskog.se/arrays-in-sql.html
• http://databases.aspfaq.com/database/how-do-i-simulate-an-array-inside-a-stored-procedure.html
• http://www.projectdmx.com/tsql/sqlarrays.aspx
• http://omnibuzz-sql.blogspot.com/2006/06/interesting-queries-using-recursive.html
• http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
• http://msdn.microsoft.com/en-us/library/ms188332.aspx
• http://msdn.microsoft.com/en-us/library/ms175170.aspx

I’m currently working on a project that will serve as the back-end for my adult hockey teams website (more on that to come). I’m also using this project as a test bed for some ideas I’d like to try at work for upcoming development.

One of the big problems we face with our current iteration of our product is that we need to manage the structure of the database. We have a .NET app that has a database back-end powering some web services and a web based interface to the product. The problem is we have many customers (well, I guess that’s not a problem) and they tend to be running different versions of the product, and hence different version of the database. As part of customization services we offer, we may need to make minor alterations to the database structure that later get rolled into the next major product release.

Back to the hockey info manager. I’ve been researching best practices on how to structure your development environment to help manage all these challenges, and I’ve been basing a lot of what I’m doing on Jean-Paul S. Boodhoo’s work, as posted previously. For these database problems, I’m working with this post in particular.

Specifically, Jean-Paul advocates splitting database creation scripts into multiple templated files to handle creating the various aspects of the database. I’ve generalized this a bit more so that there are multiple phases to the database, the initial phase and the update phase.

The database scripts are setup with the following naming convention:

YYYYY.##.ZZZZZ.sql.template

Where each of the sections means the following:

• YYYYY – The phase of the database creation this script applies to
• ## – Within the phase, the order in which the script should be executed
• ZZZZZ – A human friendly description of what the script addresses

The database creation scripts are divided up into three phases: Initial,
Update, and Test.

The Initial phase create the database fresh for the current major release. These
scripts deal with dropping the existing database if it exists, and creating
everything from scratch.

The Update phase moves the database from it’s initial phase to the most current
configuration. Once the product has been released for use, the Initial phase
scripts should no longer be updated, but rather the Update phase scripts should
contain all changes to the database as part of patches, hotfixes, etc.
Furthermore, scripts in the updates phase should assume that data already
exists within the system and nothing should be broken by running the upgrade
scripts. This includes customers who may have previously had their database
updated (inidicating the script is being run on a database that has been
partially updated). By combining (in order) the scripts in the update phase,
a master database update script can be generated that will take the database
from any state on or after the initial state to the most current phase.

When the product is being prepared for a major version release, a master upgrade
package is created that combines all the updates from the previous version.
This script is kept separately as part of a migration package, and all the
changes in the updates are incorporated into the initial creation scripts. At
this point the update scripts are wiped clean and the process begins again.

The Test phase does not include any changes to the structure of the database
but rather only adds data to the database that can be used in testing.

Below is the list of files currently in use in the project:

Initial.00.Database.sql.template

Initial.01.Views.sql.template

Initial.02.StoredProcedures.sql.template

Initial.03.Security.sql.template

Initial.04.Data.sql.template

Update.00.Database.sql.template

Update.01.Views.sql.template

Update.02.StoredProcedures.sql.template

Update.03.Security.sql.template

Update.04.Data.sql.template

Test.00.Data.sql.template

Obviously, I have yet to test this structure in practice, but I’ll keep the blog up to date with how well this structure ends up working.