Published:
16 Aug 2007
|
Abstract
In this article, Scott examines how to retrieve data using SQL Server stored procedures with LINQ |
|
by Scott Guthrie
Feedback
|
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days):
47827/
81
|
|
|
Introduction |
Republished with Permission - Original Article
Over the last few weeks I've been writing a series of blog
posts that cover LINQ to SQL. LINQ to SQL is a built-in O/RM (object
relational mapper) that ships in the .NET Framework 3.5 release, and which
enables you to model relational databases using .NET classes. You can use
LINQ expressions to query the database with them, as well as
update/insert/delete data.
Below are the first five parts of my LINQ to SQL series:
Part 1: Introduction to LINQ to SQL
Part 2: Defining our Data Model Classes
Part 3: Querying our Database
Part 4: Updating our Database
Part 5: Binding UI using the ASP:LinqDataSource Control
In these previous LINQ to SQL blog posts I demonstrated how
you could use LINQ query expressions to programmatically retrieve data from a
database.
In today's blog post I'll cover how you can also use
database stored procedures (SPROCs) and user defined functions (UDFs) with your
LINQ to SQL data model. Today's blog post will specifically cover how to
call SPROCs to query and retrieve data from the database. In my next blog
post in this series I'll then show how you can optionally also use SPROCs to update/insert/delete
data from the database.
|
To SPROC or not to SPROC? That is the question.... |
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.
|
The Steps to Map and Call a SPROC using LINQ to SQL |
In my Part 2: Defining our Data Model Classes tutorial I
discussed how to use the LINQ to SQL ORM designer to create a LINQ to SQL class
model like below:
Figure 3
Notice above how there are two panes on the
LINQ to SQL ORM designer surface. The left pane enables us to define
data model classes that map to our database. The right method pane allows
us to optionally map SPROCs (and UDFs) to our LINQ to SQL DataContext object,
which we can then use in-place of dynamic SQL to populate the data model
objects.
|
How to Map a SPROC to a LINQ to SQL
DataContext |
To map SPROCs to our DataContext class, let's
first go to the VS 2008 Server Explorer window and look at the
SPROCs within our database:
Figure 4
We can double click any of the SPROCs above to open and edit
them. For example, below is the "CustOrderHist" SPROC in
Northwind:
Figure 5
To map the above SPROC to our LINQ to SQL
DataContext, we can drag/drop it from the Server Explorer onto our
LINQ to SQL ORM designer. This will automatically create a new method on
our LINQ to SQL DataContext class like below:
Figure 6
By default the method name created on the
DataContext class will be the same as the SPROC name, and the return type of
the method will be an automatically created type that follows the
"[SprocName]Result" naming pattern. For example: the SPROC above
would return a sequence of "CustOrderHistResult" objects. We
could optionally change the name of the method by selecting it in the designer
and then use the property grid to rename it.
|
How to Call our Newly Mapped SPROC |
Once we've done the steps above to map a SPROC
onto our DataContext class, it is easy to use it to programmatically
retrieve data. All we need to-do is call the new method we mapped on
our DataContext class to get back a sequence of strongly typed results
from the SPROC:
Calling the SPROC in VB:
Figure 7
Calling the Sproc in C#:
Figure 8
In addition to programming looping over the result like in
the code samples above, I could also obviously bind the results to any UI
control to display them. For example, the below code databinds the result
of our SPROC to a <asp:gridview> control:
Figure 9
Which then displays the product history of our customer on a
page like so:
Figure 10
|
Mapping the Return Type of SPROC Methods to Data Model
Classes |
In the "CustOrderHist" SPROC example above the
stored procedure returned a sequence of product history results containing two
columns of data: the ProductName of the product, and the Total Number
of orders the customer has made for that product. The LINQ to SQL
designer automatically defined a new "CustOrderHistResult" class to
represent this result.
We could alternatively choose to have the return result of a
SPROC map to an existing data model class we have already defined in the LINQ
to SQL designer (for example: an existing Product or Order entity class).
For example, assume we have a
"GetProductsByCategory" SPROC in our database that returns product
information like so:
Figure 11
Like before we can create
a "GetProductsByCategory" method on our DataContext that calls
this SPROC by dragging it onto our LINQ to SQL designer. Rather than just
dropping the SPROC anywhere on the designer, though, we'll instead drop the
SPROC on top of the existing "Product" class in our data model
designer:
Figure 12
This gesture of dropping the SPROC onto the
Product class tells the LINQ to SQL designer to have the
"GetProductsByCategory" method return a sequence of
"Product" objects as a return result:
Figure 13
One of the cool things about having our
SPROC return "Product" objects like above is that LINQ to SQL will automatically
track the changes made to the returned Product objects just like it would
Product objects returned via LINQ queries. When we call the
"SubmitChanges()" method on our DataContext, the changes we have made
to these objects will automatically be saved back to the database.
For example, we could write the code below to
retrieve (using a SPROC) and change the price of all products within
a specific Category to be 90% of their current value:
Figure 14
When we call SubmitChanges() at the end it will
transactionally update all of the product prices. To understand more
about how change tracking and the SubmitChanges() method work, as well as about
how Validation Business Logic can be added to data model entities, please
read my LINQ to SQL Part 4: Updating our Database tutorial.
In my next blog post in this LINQ to SQL series I'll also
cover how you can replace the dynamic insert/update/delete SQL generated by the
ORM with custom SPROCs that handle the database updates instead. The nice
thing is that the code above wouldn't change at all if I configured my
DataContext to use SPROCs for updates - it would purely be a mapping layer
change and the code written against my data model would be oblivious to
it.
|
Handling SPROC Output Parameters |
LINQ to SQL maps "out" parameters in
SPROCs as reference parameters (ref keyword), and for value types declares
the parameter as nullable.
For example, consider the below
"GetCustomerDetails" SPROC which takes a CustomerID as an input
parameter, and which returns the company name as an output
parameter in addition to its order history as a query result:
Figure 15
If we drag the above SPROC onto our
"Order" class in the LINQ to SQL designer, we could then write the
below code to call it:
VB:
Figure 16
C#:
Figure 17
Notice in the code above how the SPROC helper method returns
back a sequence of Order objects - but also then returns the CompanyName as an
output parameter to the helper method.
|
Handling Multiple Result Shapes from SPROCs |
When a stored procedure can return multiple result shapes,
the return type of the SPROC method on the DataContext cannot be strongly typed
to a single class shape. For example, consider the SPROC below which
returns either a product result or an order result depending on the input
parameter:
Figure 18
LINQ to SQL supports the ability to create
SPROC helper methods that can return either a Product or Order shape by adding
a partial "NorthwindDataContext" class to the project that defines
a method (which in this case we'll call "VariablesShapeSample")
that invokes the SPROC and returns an IMultipleResult object like so:
VB:
Figure 19
C#:
Figure 20
Once this method is added into our project we
can then call it and convert the result to be either a Product or Order
sequence when we are using it:
VB:
Figure 21
C#:
Figure 22
|
Supporting User Defined Functions (UDFs) |
In addition to SPROCS, LINQ to SQL also supports both scalar-valued and table-valued user defined functions (UDFs), as well as the
in-line counterpart to both. Once added to your DataContext as a method,
you can use these UDF functions within your LINQ queries.
For example, consider a simple scalar user defined function
called "MyUpperFunction":
Figure 23
We can drag/drop it from the Visual Studio Server Explorer
onto our LINQ to SQL Designer to add it as a method on our DataContext:
Figure 24
We can then use this
UDF function inline within our LINQ expressions when writing
queries against our LINQ to SQL data model (notice it is being used within the
"where" clause below):
VB:
Figure 25
C#:
Figure 26
If you use the LINQ to SQL Debug Visualizer that I blogged about here, you can see how LINQ to SQL transforms the above
expression queries into raw SQL that execute the UDF inside the
database at runtime:
Figure 27
|
Summary |
LINQ to SQL supports the ability to call Stored Procedures
and UDFs within the database and nicely integrate them into our data
model. In this blog post I demonstrated how you can use SPROCs to easily
retrieve data and populate our data model classes. In my next blog post
in this series I'll cover how you can also use SPROCs to override the
update/insert/delete logic when you SubmitChanges() on your DataContext to
persist back to the database.
Hope this helps,
Scott
|
Resources |
|
|
|
User Comments
Title:
Programmer
Name:
Alexander
Date:
2012-11-08 4:51:26 PM
Comment:
Your Figure or pictures don't show up
|
Title:
Use Teporary table
Name:
Ashok Kumar
Date:
2011-07-16 3:28:51 AM
Comment:
if we are fetching the combination of table order and product form database then structure of the newly table will not be match with the mapped table order or price details due to column how to handle this
|
|
|
|