look at this sample..
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.
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
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:
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
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:
can double click any of the SPROCs above to open and edit them. For
example, below is the "CustOrderHist" SPROC in Northwind:
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:
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
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:
Calling the Sproc in C#:
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:
Which then displays the product history of our customer on a page like so: