Generating Clean Business Object Classes from a horrible data source

Question

I'm starting with a blank slate for a layer of business/entity classes, but with an existing data source. Normally this would be cake, fire up Entity Framework, point it at the DB, and call it a day, but for the time being, I need to get the actual data from a third party vendor data source that...

1. Can only be accessed from a generic ODBC provider (not SQL)
2. Has very bad syntax, naming, and in some cases, duplicate data everywhere
3. Has well over 100 tables, which, when combined, would be in the neighborhood of 1,000 columns/properties of data

I only need read-only from this DB, so I don't need to support updates / inserts / deletes, just need to extract the "dirty" data into clean entities once per application run.

I'd like to isolate the database as much as possible from my nice, clean, well-named entity classes.

Is there a good way to:

1. Generate the initial entity classes from the DB tables? (That way I'm just mostly renaming and fixing class properties to sanitize it instead of starting from scratch.)
2. Map the data from the DB into my nice, clean classes without writing 1000 property sets?

Edit: The main goal here is not as much to come up with a pseudo-ORM as it is to generate as much of the existing code as possible based on what's already there, and then tweak as needed, eliminating a lot of the manual labor-intensive class writing tasks.

Answer 1

I like to avoid auto-generating classes from database schemas just to have more control over the structure of the entities - that and what makes sense for a database structure doesn't always make sense for a class structure. For 3rd party or legacy systems, we use an adapter pattern for our business objects - converting the old or poorly structured format - be in in a database, flat files, other components, etc. into something more suitable.

That being said, you could create views or stored procedures to represent the data in a manor more suitable to your needs than the database's current structure. This is assuming that you are allowed to touch the database.

Answer 2

Dump the database. I mean, redesign the schema, migrate your data and you should be good.