Tip 8: Using LINQ to Build a DAL for Stored Procedure Access
Before I start the next tip, I'd like to respond to something that I often see developers doing with LINQ (and until recently, I did as well): querying into an IEnumerable, and then looping through the IEnumerable to populate a List class. Usually, the code will look something like this:
foreach (VendorResults oRec in oVendorResults)
While that's certainly valid code, you can instead populate the list using a method overload that specifies an IEnumerable:
List<VendorResults> oListResults = new
Most developers still use stored procedures for data access. You can do that and still leverage LINQ's capabilities by calling stored procedures using strong typing. The next two tips will cover examples of this.
First, here's a simple example of calling a stored procedure that returns one result set. LINQ to SQL contains an interface called ISingleResult that you can use to cast a return object from a LINQ call to a stored procedure:
AdventureWorks db = new
XElement XmlOrders = new XElement("Orders",
new XElement("IDpk", 1)),
new XElement("IDpk", 2)),
new XElement("IDpk", 3)));
ISingleResult<GetPOSummaryResult> POHeaderData =
Generally speaking, the code sample above does two things:
Tip 9: Using LINQ to Handle Multiple Result Sets
- Creates an XML XElement object for a variable number of keys as a stored procedure parameter (the stored procedure parameter will shred the XML into a table variable as part of the query). If you're interested in seeing the function that shreds an XML string into a table variable, check this article from the September/October 2007 CoDe Magazine issue.
- Create an object called POHeaderData, of type SingleResult<GetPOSummaryResult>.
- SqlMetal (which I'll cover in the next tip) creates a default type of GetPOSummaryResult from the name of the stored procedure (GetPOSummary).
Many stored procedures return multiple result sets. LINQ to SQL provides the IMultipleResults interface to handle calls to these types of stored procedures:
// Call a stored proc that returns
// multiple results
IMultipleResults result =
IEnumerable <GetPODetailsResult1> oHeader =
IEnumerable<GetPODetailsResult2> oDetails =