What is the data access strategy for a large application
I am about to rewrite a VB6 application in .NET 3.5sp1. The VB6 application is well written and the data layer is completely based on stored procedures. I would like to go with something automated like Linq2SQL / Entity Framework / NHibernate / SubSonic. Admittedly, I have not used any of these tools on anything other than blowout projects.
Potential problem, I'm afraid I have speed with all these choices. For example, right now, to get one line (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 get one row in Linq2SQL / Entity Framework / NHibernate / SubSonic, should these solutions take the whole list to the client and find the row you want?
So what is the consensus on a data access strategy for a large data domain application?
source to share
I'm going to play devil's advocate and recommend that you at least consider sticking to stored procedures. They are a piece of code that you don't need to rewrite or debug. This article from our Very Own [tm] Joel Spolsky gives a consistent argument to avoid a complete overwrite.
Given the "greenfield" project, you can use whatever you want, and an O / R mapper may very well be a good choice. However, you have already stated that the VB6 application is well written. If the sprocs are well written, then you get a free application and it is already being debugged, plus you get the ability to recycle the database schema and avoid most of the pain of migrating data.
Fowler Enterprise Application Architecture Patterns should give you some good pointers for designing a data access layer that plays well with stored procedures without causing maintenance issues.
This is done quite often in Oracle / Java applications. Many legacy Oracle applications have large chunks of stored procedure code in PL / SQL - this was the standard architecture in the Oracle Forms client / server days. It is generally accepted to write a wrapper for sprocs in Java and build a UI on top of the wrapper.
One of the other posters mentioned that Subsonic can create wrappers for sprocs.
I once had to make a hacked data dictionary that wrapped a Java / JDBC proof-of-concept wrapper for PL / SQL sprocs - IIRC, it only took one day. Given that this is not that hard to do, I would be surprised to find that there is no choice in the fact that you can step off the shelf to do this. As a last resort, writing your own isn't that hard either.
source to share
I can't talk about Linq-to-SQL, Entity Framework or NHibernate, but I'm in love with SubSonic. My experience with him has been generally positive.
How these tools work, in general, is that they build parameterized queries for you in managed code, encapsulate that access into classes, and then expose those classes to your applications. Fully crafted DALs stone.
With parameterized queries, your problem is that they can "force the entire list to be delivered to the client and find the string I want." They support suggestions where
and other filtering to get only the rows you want. You can do the equivalent select * from foo
, but you are not stuck in this mode.
However, SubSonic does - when used out of the box for direct table / view access - collapses entire rows, which may be a disadvantage in some scenarios. However, your access through stored procedures is not a problem - I can't talk to others, but SubSonic helps create a class SPs
that encapsulates all of your procs, allowing you to call them as methods and return the appropriate DataTable
s, which you can manually break apart manually as you go necessity. There are also ways to initialize DAL class lists from procs, so if proc returns a dataset that directly matches the table / view, you can still have cleaner syntax without manual handling.
(SubSonic, by the way, cured me of “procs for everything.” As a rule, I don't do much CRUD processing now, as in the past, and am just finishing using them for complex reports. Mileage can and, in fact, will change. )
source to share
I would recommend using SubSonic to generate all the code to access existing stored processes, this way you reduce the chances of regression due to the new layer of data access. Any new functionality can be accessed through the ActiveRecord classes created by SubSonic. It seems to me that this is the safest and fastest way to proceed,
I disagree with NHibernate's recommendation because it is not very well suited to working with stored procedures.
source to share
If queries are stored in stored procedures, chances are good that they are already well optimized. And they probably liberally use SQL expressions for JOINS, subqueries, etc.
Duplicating this kind of efficiency and expressiveness, exactly, with an ORM abstraction layer, I expect this to be a problem, especially if you are not fully familiar with the tools.
You can always refactor your queries after you get the rest of the app straight. And the ORM world is changing fast enough that the parameters will certainly be different when you get there.
source to share
SubSonic is, even according to one of the authors, Rob Connery, more written to support rapid application development and less about large applications. I would say you go with NHibernate as you will find a lot of community support and a tried and tested platform. You can get good information from www.dimecasts.net on setting up your NHibernate stuff.
source to share