What data access strategy for a large application

Question

I am about to embark on a rewrite of a VB6 application in .NET 3.5sp1. The VB6 app is pretty well written and the data layer is completely based on stored procedures. I'd like to go with something automated like Linq2SQL/Entity Framework/NHibernate/SubSonic. Admittedly, I haven't used any of these tools in anything other than throwaway projects.

The potential problem I fear I might have with all these choices is speed. For instance, right now to retrieve a single row (or the entire list), I use the following sproc:

ALTER PROCEDURE [dbo].[lst_Customers]
 @intID  INT = NULL
,@chvName   VARCHAR(100) = NULL
AS

SELECT   Customer_id, Name
FROM dbo.Customer
WHERE (@intID IS NULL OR @intID = Customer_id)
 AND (@chvName IS NULL OR Name like ('%' + @chvName + '%'))
ORDER BY name

To retrieve a single row in Linq2SQL/Entity Framework/NHibernate/SubSonic, would these solutions have to bring the entire list down to the client and find the row that I need?

So, what's the consensus for the data access strategy for an application with a large data domain?

Answer 1

I can't speak on Linq-to-SQL, Entity Framework, nor NHibernate, but I am in love with SubSonic. My experience with it has been overwhelmingly positive.

The way these tools work, in general, is that they build parameterized queries for you in managed code, encapsulate that access in classes, then expose those classes to your apps. Fully generated DALs rock.

By using the parameterized queries, your concern that they might "have to bring the entire list down to the client and find the row that I need" is handled. They support where clauses and other filtering to get just the row(s) you need. You can do the equivalent of select * from foo, but you're not stuck in that mode.

That said, SubSonic does -- when used out of box for direct table/view access -- bring down entire rows, which could be a downside in some scenarios. However, your access via stored procs is not an issue -- I can't speak to the others, but SubSonic helpfully creates an SPs class encapsulating all your procs, allowing you to call them as methods, and returning the appropriate DataTables, which you can then parse manually as needed. There are also ways to initialize lists of DAL classes from procs, so if the proc returns a dataset which matches a table/view directly, you can still have the cleaner syntax without manual processing.

(SubSonic, by the way, cured me of "procs for everything." I now, typically, spend almost no time doing CRUD procs as I did in the past, and only wind up using them for complicated reporting. But your mileage may, and in fact will, vary.)

Answer 2

SubSonic, even according to Rob Connery, one of the authors, is written more to support rapid application development and less about large applications. I'd say go with NHibernate as you'll find the most support from the community as well as tried and true tested framework. You can get good information from www.dimecasts.net on setting up your NHibernate stuff.

Answer 3

I would recommend using SubSonic to generate all the code to access your existing stored procs, this way you reduce the chances of regression because of a new data access layer. Any new features can be then accessed via using the ActiveRecord classes that are generated by SubSonic. This seems to me to be the safest and fastest approach to proceed,

I don't agree with the NHibernate recommendation because it's not really well suited for working with stored procedures.

Answer 4

I'm going to play devil's advocate and recommend you at least consider sticking with the stored procedures. These represent a chunk of code that you do not have to re-write and debug. This article from our Very Own [tm] Joel Spolsky gives a coherent argument for avoiding complete re-writes.

Given a 'greenfield' project you can use what you want, and an O/R mapper might well be a good choice. However, you've already stated that the VB6 app is well written. If the sprocs are well written, then you get some of your app for free and it comes already debugged, plus you get to recycle the database schema and avoid most of the pain from data migration.

Fowler's Patterns of Enterprise Application Architecture should give you some good pointers for designing a data access layer that will play nicely with the stored procedures without causing maintenance issues.

This is done quite commonly on Oracle/Java apps. Many legacy Oracle apps have large bodies of stored procedure code in PL/SQL - this was a standard architecture back in the client-server days of Oracle Forms. It is common practice to write a wrapper for the sprocs in Java and build the user interface on top of the wrapper.

One of the other posters mentioned that Subsonic can generate wrappers for the sprocs.

Once upon a time I had occasion to do a data dictionary hack that generated a proof-of-concept Java/JDBC wrapper for PL/SQL sprocs - IIRC it only took a day or so. Given that it's not that hard to do, I'd be surprised to find that there isn't quite a bit of choice in things you can get off the shelf to do this. In a pinch, writing your own isn't all that hard either.

Answer 5

We tried to use entity framework as an orm and ran into multiple issues when trying to use it with Domain Driven Development. Linq to Sql also has some limitations and Microsoft is going to stop supporting it in the next release, I believe.

Answer 6

If the queries are in stored procedures, there's a pretty good chance they've already been well-optimized. And probably they make liberal use of SQL expressions for JOINS, subqueries, etc.

Duplicating that kind of efficiency and expressiveness, accurately, with an ORM-type abstraction layer would I expect be a challenge - especially if you're not totally familiar with the tools.

You can always refactor the queries after you get the rest of the app straight. And the ORM world is changing fast enough that the options will certainly be different when you get there.