Coverting from Datasets to stored procs and modern ORM

Question

How much effort would it take to migrate a large existing codebase from a Strongly Typed Dataset driven data access layer to a DAL driven by stored procs and/or a more modern ORM package? Is there any shiny tool that automates a portion of this process?

The current code base has well over 100+ datasets mirroring the sql database (but haven't always been 100% in sync with changes in the DB structure). The current stance is that it would be too much time/effort to change now, but I question how much technical debt this is leaving us to pay down every week. This is to say nothing of the performance on the backend of datasets' SQL vs. an optimized sproc.

So, is that justified? Would something like that be too much of a monster to tackle in a reasonable time and get a worthwhile payoff? I know I could change the DAO-like classes they use to interfaces (should be already) and develop this on the side while still using the datasets in production until a feasibility test of some sort could be done on a small subset of the whole.

Answer 1

I would say moving on to an ORM like LINQ to SQL would be far less effort intensive compared to the stored proc driven DAL layer. few things that come straight to my mind :

• Situation 1 - if you are using your typed datasets outside your DAL [in UI, BLL] then the effort is going to be high for sure because you will need to do an extensive impact analysis of the change and make changes pretty much everywhere you have used your typed datasets.

• Situation 2 - if you are using your typed datasets ONLY withing your DAL and your UI, BLL dont care about the internal implementation of DAL and are oblivious of the typed dataset, then it would be far less effort intensive. You will need to change only within the DAL layer.

• If you are in situation 2, then i think it would definitely be worthwile to move from typed data sets to ORM mapper.

• If you intend to take the stored proc approach for DAL, then you might look at www.mygenerationsoftware.com to auto generate your procs to reduce some effort, however the effort would still be higher compared to the ORM mapper and another downside may be that you end up with umpteen simple insert / update procs in your DB. we generally use procs primarily for cascaded UPSERTS (update+insert) or complex calcualtions only and use LINQ to SQL for basic insert, update, deletes.

hope this helps in someway !