Mapping class properties to generic columns in table .NET

Question

I have have a SQL Server table which has generic names like Text1, Text2.. etc. The table was designed like this because the same structure is used for different projects.

I have a class in .NET which has properties. Say a Customer class has a property called FirstName.

How can I do the mapping from FirstName to Text1 just once (central place) in the application so that I don't have to remember and hard code the mappings all over the app when I create the different DAL methods?

For example, I want the app to know when I want to update, insert a FirstName, the DAL automatically uses Text1. Basically I don't have to remember which property goes to which column. The idea is so the developers do not map the properlies/columns in a wrong way. It's always consistent.

Note: Database inserts, updates and deletes are allowed through stored procedures only.

Answer 1

Look into the various ORMs out there. For mapping a legacy database, NHibernate is perfect.

Answer 2

If you're stuck with MS and don't want to use the entity framework LinqToSQL is pretty good.

Answer 3

If you can't use NHibernate (and I would strongly suggest you do) or are not able to use LINQ to SQL (perhaps you're code base is .NET 2.0) then...

You will probably want to implement some form of factory pattern. Inside your factory object you might want to return a strongly dataset then use an object to object mapper to translate between your dataset and DTO/View/Entity objects. Jimmy from Lostechies has created a great open source object to object mapper which you might find very useful: AutoMapper.

Happy coding