Application that depends heavily on stored procedures

Question

We currently have an application that depends largely on stored procedures. There is a heavy use of temp tables. It's an extremely large application.

Facing this situation, I would like to use Entity Framework or Linq2Sql for a rewrite. I might consider using Fluent Hibernate or Subsonic, as i've used them quite extensively in the past.

I've had problems with Linq2Sql generating the return types for the stored procedures because of the usage of the temp tables, and I think it's cumbersome to go and change all the stored procedures from temp tables to in-memory tables.

Considering the 2 choices that I want to make, which one of the 2 is the best route to go and why? If my choices are extremely idiotic, please provide alternatives.

Edit: The reason for the question and the change is that the data access layer is non-existent and was built 10 years ago. We currently still run into a lot of issues with it. I don't want to divulge too much, but if you saw it, your eyes would start bleeding :)

Answer 1

Have you thought about LLBLGen Pro? It is a commercial solution but is pretty cheap and I believe it is much better than those two alternatives. I think Frans Bouma is actually an SO user as well but their forums are also very helpful.

Answer 2

This probably isn't the answer you're after, but it sounds like this is mostly about isolating the relevant code into a 'new' data access layer.

If you abstract out the data access via interfaces you'd be able to use any data access implementation you like. I'm just not 100% sure how that relates to stuff like the Enity Framework specifically.

The reason I'd take that path is that it allows a really good clean seperation of concerns that makes the app easier to work with over time, and it doesn't do it at the expense of performance (in my experience).

As a first step - rather than a rewrite I'd be looking to do that - just get a Data layer in-place, and abstracted out so you can work with it.

In parallel, you can do some proof of concept work with stuff like the Enity Framework so that when you're ready to refactor the new DAL, you've got solid info to base your decision on.

Just remember that "execution qualities" need to be balanced with the "evolutional" ones :)