Mapping to legacy data model using ALTER TABLE

GerbenRampaart · **Joined:** Tue Feb 08, 2011 9:34 pm **Posts:** 1

Since this is my first question on this forum, let me start by saying I love NHibernate. You are the gods of open source. I know nothing in software engineering is a silver bullet, but NH comes the absolute closest.

Maybe it's been asked before (certainly hope so, 'cause it would mean there's probably an answer) but here is my problem:

I am supporting a legacy datamodel of about 195 tables, 5 of which are dynamically expanded using user-generated content (it's insane, i know, don't get me started).

We make a web product where users store their personell data. The users (the employers of those people) enter a new person in a webform which is being stored in the database. The properties of a person are dynamic, for example, one employer wants to store shoesize of a person, another wants to store favorite color, etc.

"Nothing fancy", you would think, "just create a nice pivotted table". That's not what the creators of the data model thought, oh no. Every time a user adds a new property to Person, it's added as a freakin' column to the Person table using ALTER TABLE.

Yes, I am serious, no joke. And no, I am not allowed to change the datamodel.

My Q, of course. How do I map this? Ideally I would like to have the dynamic properties/columns stored in a dictionary or something.

Ow yeah, I am using Fluent, if it could be solved that way that would be great, but if a ".hbm.xml" file must be added for it, ultimately, that's no problem either.

Many thx in advance,
Gerben.

mysterd429 · **Joined:** Tue Feb 08, 2011 10:35 am **Posts:** 4

Hi, Gerben. I'm not a Hibernate expert (I can only do the basics), but I was curious to see if I could come up with something. It's very ugly, but then, so is your situation. This is based on the QuickStart guide. Best of luck!

Code:

ISession session = NHibernateHelper.GetCurrentSession();
ITransaction tx = session.BeginTransaction();

Cat mitzi = new Cat
{
    Name = "Cat 1"
  , Sex = 'A'
  , Weight = 4.2f
};
Cat todd = new Cat
{
    Name = "Cat 2"
  , Sex = 'B'
  , Weight = 8f
};

session.Save(mitzi);
session.Save(todd);

tx.Commit();

IDbCommand command = session.Connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText =
@"DECLARE @COLUMNS TABLE(COLUMN_NAME NVARCHAR(30))
DECLARE @COLUMN_NAME NVARCHAR(30)
DECLARE @QUERY NVARCHAR(250)

SET @QUERY = 'SELECT '
INSERT INTO @COLUMNS SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CAT'

SELECT TOP(1) @COLUMN_NAME = COLUMN_NAME FROM @COLUMNS
DELETE FROM @COLUMNS WHERE COLUMN_NAME = @COLUMN_NAME

SET @QUERY = @QUERY + @COLUMN_NAME

WHILE((SELECT COUNT(*) FROM @COLUMNS) > 0)
BEGIN
    SELECT TOP(1) @COLUMN_NAME = COLUMN_NAME FROM @COLUMNS
    DELETE FROM @COLUMNS WHERE COLUMN_NAME = @COLUMN_NAME
    
    SET @QUERY = @QUERY + ', ' + @COLUMN_NAME
END

SET @QUERY = @QUERY + ' FROM CAT'

EXEC (@QUERY)";

tx.Enlist(command);
using (IDataReader reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        Console.Out.WriteLine("===============");
        for(Int32 i = 0; i < reader.FieldCount; i++)
        {
            String columnName = reader.GetName(i);
            Console.Out.WriteLine(
                "{0}: {1}"
              , columnName
              , reader[i]
            );
        }
    }
}

Code:

===============
CatId: 1
Name: Cat 1
Sex: A
Weight: 4.2
===============
CatId: 2
Name: Cat 2
Sex: B
Weight: 8