The question of whether to use Dynamic SQL generated by an
ORM or instead use Stored Procedures when building a data layer is a topic that
generates endless (very passionate) debate amongst developers, architects and
DBAs. A lot of people much smarter than me have written on this topic, so
I won't rehash the arguments for and against each side here.
The LINQ to SQL ORM that ships in .NET 3.5 is pretty
flexible, and can be used to create data model classes whose object model can
be independent of the underlying database schema, and which can encapsulate business logic and validation rules that work
regardless of whether the data model is populated/persisted via dynamic SQL or
via SPROCs.
In my LINQ to SQL Part 3: Querying our Database post I discussed
how you can write LINQ query expressions against a LINQ to SQL data model
using code like below:
Figure 1
When you write LINQ query expressions like
this the LINQ to SQL ORM will execute the necessary dynamic SQL for you to
retrieve Product objects that matches your query.
As you'll learn in this post, you can also
optionally map SPROCs in the database to your LINQ to SQL DataContext
class, which allows you to alternatively retrieve the same Product objects by
calling a stored procedure instead:
Figure 2
This ability to use both dynamic SQL and SPROCs with a clean
data model layer is pretty powerful, and provides a great deal of flexibility
when working on projects.