Search code examples
.netstored-proceduresvb6legacydata-access-layer

What data access strategy for a large application

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?

Solution

  • 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.