SQL instead of stored procedures? Is this really best practice for data access?

Topics: General Discussion Forum
Mar 15, 2007 at 1:55 PM
Hi there

Studying the Global Bank ASMX reference implementation (RI1) for the WSSF I’ve stumbled upon the class:

GlobalBank.GlobalBranchServices.DataAccess.CustomerSelectionFactory

This class is responsible for turning a customer selection criteria into a select statement.

The method GenerateSelector creates a DbCommand based upon a SQL-statement created on the fly in C#-code. This SQL command is then send to the database.

Is this really best practice for data access?

Aren’t we supposed to use stored procedures instead of SQL in the data access layer?

How should or could this be done instead?

Isn’t there some nice SQL 2005 features that could help us solve this common problem in a more elegant and secure manner?

Best regards,

Michael Brandt Lassen
3F, Denmark
Developer
Mar 15, 2007 at 3:08 PM
Hi Michael,

You inquire is completly reasonable but I would say that IMHO, that depends on the context and usage scenario (Who needs Stored Procedures, anyways?).
As you can see, most of the data access use SP. Just that particular sample use a dynamic SQL that provide a pretty simple approach than doing the same inside an SP. Performance wise, you don't have much gain using SP over dynamis SQL as you can see here Stored Procs vs Dynamic SQL.

Charly
Mar 15, 2007 at 4:11 PM
Hi Charly

Thanks a bunch.

I still don’t like it though. For one there's the management issue (as mentioned in your links). But the chosen solution also seems to create an even tighter coupling between the data access layer and the database. Schema changes could easily break the code.

While we are waiting on ADO.NET 3.0 and LinQ, doesn’t SQL 2005 (say User Defined types and functions) provide some better solutions?

Best regards,

Michael Brandt Lassen
3F, Denmark