Search code examples
c#asp.netcode-generationdata-access-layerdata-access

Modernising a 'Hand Rolled' Data Access Library

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.

Solution

  • 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