LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)
page 6 of 11
by Scott Guthrie
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 47837/ 66

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. 

View Entire Article

User Comments

Title: Programmer   
Name: Alexander
Date: 2012-11-08 4:51:26 PM
Your Figure or pictures don't show up
Title: Use Teporary table   
Name: Ashok Kumar
Date: 2011-07-16 3:28:51 AM
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

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-04-15 1:22:48 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search