|
by
Granville Barnett | 24 September 2007
In my
previous article, I looked at some of the core
fundamentals of .NET 3.5 and C# 3.0, in particular we looked
at LINQ to SQL using ad-hoc queries. In this article we will
build on what we learnt and look at LINQ to SQL using stored
procedures, and LINQ to XML; we will also demystify lambda
expressions through a series of practical examples.
One of the questions I often get asked when talking about
LINQ to SQL is about how well it plays with stored
procedures, after all not many companies or developers like
to leave their database open to ad-hoc queries we like to
lock things down. The good news for anyone seriously looking
at LINQ for use in their applications is that you can use
stored procedures and still keep the flexibility offered by
ad-hoc queries.
For all the examples in this article
we will use the same database schema as the earlier article
Next Generation Data Access with LINQ.
We will use the SqlMetal tool again to generate our data
access layer (DAL), however, this time we will look at how
to hook up our DAL to use the stored procedures defined in
our database. Setting up our data access layer for use with
stored procedures is unbelievably easy using LINQ to SQL,
even if you decided not to use the SqlMetal tool
The following snippet shows the definition for a stored
procedure GetAuthors:
- [StoredProcedure(Name="GetAuthors")]
- public
StoredProcedureResult<GetAuthorsResult>
GetAuthors()
{
- return
this.ExecuteStoredProcedure
- <GetAuthorsResult>(((MethodInfo)(MethodInfo.GetCurrentMethod())));
- }
One of the things to note is that when using SqlMetal to
extract stored procedures from our database, the tool
actually generates what looks like an entity for that stored
procedure result:
- public
partial
class
GetAuthorsResult
{
- private
int
_AuthorId;
- private
string
_AuthorName;
- public
GetAuthorsResult()
{
- }
-
- [Column(Name="AuthorId",
Storage="_AuthorId",
- DBType="Int")]
- public
int
AuthorId
{
- get
{
- return
this._AuthorId;
- }
- set
{
- if
((this._AuthorId
!=
value))
{
- this._AuthorId
=
value;
- }
- }
- }
-
- [Column(Name="AuthorName",
Storage="_AuthorName",
- DBType="NVarChar(50)")]
- public
string
AuthorName
{
- get
{
- return
this._AuthorName;
- }
- set
{
- if
((this._AuthorName
!=
value))
{
- this._AuthorName
=
value;
- }
- }
- }
- }
The problem with this is that the
GetAuthorsResult
enumeration is actually identical to the entity Author
already defined in the data access layer. There are a few
ways around this, of which the simplest has been
demonstrated excellently by Mike Taulty of Microsoft UK in
one of his blog posts.
Note: Remember this is still a
very early CTP of LINQ, so its likely that the final code
generation tool will take into account the similarities
between already defined entities and stored procedures.
With our data access layer embracing the use of stored
procedures, its time to run through some example queries to
prove just how easy it is to use LINQ to SQL, and the
flexibility it maintains in comparison to ad-hoc queries.
Just like any custom DAL where you would map rows of data to
user-defined entity types and expose them as a series of
methods we achieve the same effect here; however, the LINQ
to SQL implementation is backed up with all the great new
language enhancements introduced in C# 3.0 like anonymous
types.
To retrieve all authors use:
- BookShop
db =
new
BookShop(_conn);
- foreach
(var
author
in
db.GetAuthors())
{
- Console.WriteLine(author.AuthorName);
- }
Just as I explained in the previous article there will be
many times when you want to specifically choose what
properties you want access to, and maybe you even want to
filter the objects in a way that is not defined in the
stored procedure this is all possible when using stored
procedures in LINQ to SQL.
For example, the following shows you how to use the
GetAuthors stored procedure in conjunction with a query:
- var
query
= from
a in
db.GetAuthors()
-
orderby
a.AuthorName
-
select
new {
a.AuthorName
};
- foreach
(var
book
in query)
{
- Console.WriteLine(book.AuthorName);
- }
Notice that stored procedures can be debugged in the
usual way see below image:
[ Debugging ]
When extracting the stored procedures from your database,
all stored procedures that take any arguments will be taken
into account when SqlMetal generates the methods associated
with those stored procedures. In our scenario we have a
stored procedure called GetAuthorByBookId, which takes a
single parameter of type int this leads to the generation
of a method of the same name, which also takes a parameter
of type int. If you have a stored procedure that takes n
arguments then you will also have generated a method with
the same n arguments.
For example:
- //...
- myDataContext.GetAuthorByBookId(8);
- //...
In the next page, let's continue by looking at some other LINQ
operations that we did not get to cover in this page.
Onwards to the
next page.
|
Further Exploration with LINQ - Page 1
