Modernising a 'Hand Rolled' Data Access Library

Question

There's this web app, which relies on a sort of data access library (simple data objects and associated objects to perform CRUD operations on them) which is generated directly from the database.

So from a Person table

ID
Forename
Surname
DoBirth

you'd get a generated Person class with fields:

ID, Forename, Surname, DoBirth typed from their db columns.

And a helper class PersonPersister

with

Create(Person p)  
Update(Person p)  
Delete(Person p)  

methods.

It will also create the necessary CRUD sprocs on the database.

I felt uneasy about this when I started as, aside from brief flirtations with nHibernate and MEF I'm used to handcoding my dataaccess layer. All my worries appear to be coming to fruition now, a year on as we're doing another phase of development with a larger team of devs and the cracks have started to appear.

The basic problem is that as developers we have no control over whats generated and there is no way to version the DAL.

Everytime we do a release we much time hand-configuring the app, dal and databse to get it working. Often the scenario is one in which the DAL has been generated off the dev db and then applied to the live db which of course lacks the tables/sprocs etc. created during development.

At these times, I often find myself heading over to jobserve.com, even though this issue aside I rather like working here.

Ideas I've had include modding the codegenerator so it overwrites source files in an explicit DAL-handling Visual Studio project - these would then be trackable in a CVS and also hand-editable. Does anyone have any positive experiences of such a strategy? At the moment the only artifact the build generates is a dll so seeing the history of changes is not possible.

Aside from using an ORM (management is not a fan - yes, I know) what are our options as far as rationalising this to give ourselves control? We still need an element of automation but the amount we have is unworkable at present.

We are very lucky to have MSDN subscriptions here, so we're running TFS 2010 with automated builds, the latest Visual Studio etc. etc., but because of this aspect of our development environment, it feels like we're a decade or more behind the times.

Answer 1

How about a database project which maintains all your stored procedures...

Pros

1. version control of DB
2. Easy deployment..You just have to specify which database it goes to and with 1 click u can deploy it...
3. You know exactly what's changing as a result of introducing a new Class
4. All Database code resides along with the other code and is available from one IDE which makes it so much easier to send in changes at once..

Cons

1. You might have to invest time into migrating your present infrastructure which creates stored procs to, something that creates stored proc script files...Basically all code within create/update/delete u were mentioning will have to be written again.

and lots more pro's and cons which you will be a better judge of...

If you consider going for this option you must look at these links below

1. Beginner's tutorial
2. Sample Project

Answer 2

This strikes me as more of a problem with your deployment strategy rather than a development one. Whether you use an ORM or your current tool then it's going to be generating (if you use the auto-generate) entities from the dev database. Your deployment needs to make sure that all changes to the app are propagated through your QA and onto your Production systems, whether app, database or some other dependency.

Answer 3

I've worked with code generation tools in the past, and used them to generate template code which was included as part of a Visual Studio project, then modified by hand to suit the requirements. This meant the code generation was just a time saving step, and the source was completely version controlled.

It sounds like a good first approach for you would be to do the file overwriting that you speak of, then you get the obvious benefits of version control and a proper QA'd release cycle.

Answer 4

There are two issues here - your DAL and your deployment strategy.

To tackle the deployment one you should take a look at Fluent Migrations

I recently implemented it for a client that had similar issues, and now they can deploy the app knowing that the database will be automatically upgraded/syncronized if necessary (this is an optional feature).

You can use a fluent API, embedded SQL scripts, inline SQL - whatever works for you. You can also include the migrator in an automated build process quite easily.

This article reveals one companies experience with it, and provides some code examples on how to have the upgrade process hook into application start up.