Calling all database/ ORM/ data access layer experts

Question

Hi guys, I have a little DB challenge for you. Hopefully you can help.

The Problem:

I need a solution that will allows me to "handle" changes to primary keys/ composite keys. By "handle", I mean I should still be able to perform CRUD operations with little or no code changes. It needs to have minimum hassle from an application/ database point of view.

I am allowed to use pretty much anything under the sun except for the 2 constraints as follows: 1) Need to use SQL Server 2) Not allowed to use dynamic SQL queries

My initial brainstorm/ Possible Solutions?

a) Can it be solved at stored procedure level somehow?

b) Can some sort of clever use of DTO with adapter pattern/ reflection/ DB schema interrogation solve this?

c) Some sort of fancy LINQ/ reflection trick?

d) Can NHibernate solve this somehow?

I am under a bit of time pressure (I have to come up with a solution before the end of the week) so any help will be appreciated. So, here it is. If you think you might have a solution but could not be bothered using any brain cells, here is an incentive (hopefully it will work)...if your solution solves the problem for me and I end up using it, you get fully licensed commercial (not pirated :P ) version of any Microsoft software of your choosing eg. Windows 7, SQL Server, Office 2007, etc. If the incentive does not work, I hope you'll help me anyways? :-) Kind of desperate here :-)

Thank you so much.

Answer 1

Have a look at the CSLA (http://www.lhotka.net/) business objects.

Also have a look at CodeSmith for code generation, it has CSLA templates and other ORM templates.

Answer 2

Suppose you have a record with a generated primary key - that's just an immutable identifier for the record. An end user never need see it, but it allows you to reliably identify a record and hence specify changes to it.

Other columns or combinations of columns are what the user thinks of as the PrimaryKey but really are not "primary" they are just (possibly unique) chnangeable pieces of data.

So GivenName/FamilyName - nice fields perhaps for an index, but clearly changable.

Social Security Number (or some such supposedly unique number) - sounds like a great primary key. Hmm but suppose somehow the wrong one gets into the system, how do you fix it. If it's just a column with a unique index where's the problem?

The real primary key is the thing we use to join to other tables.

User -- User's Orders

Linked by the system generated primary key, not his mutable SSN-like thing.