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.
