RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


The Baker's Dozen: 13 Tips for Building Database Web Applications Using ASP.NET 3.5, LINQ, and SQL Server 2005 Reporting Services : Page 4

ASP.NET 3.5 and LINQ provide the greatest amount of functionality yet for building data-aware web applications. Even if you prefer to write stored procedures, you can still leverage some of LINQ to SQL functionality for accessing stored procedures inside .NET.

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("OrderIDs",
      new XElement("IDpk", 1)),
      new XElement("OrderIDs",
      new XElement("IDpk", 2)),
      new XElement("OrderIDs",
      new XElement("IDpk", 3)));
   ISingleResult<GetPOSummaryResult> POHeaderData = 
Generally speaking, the code sample above does two things:

  • 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).
Tip 9: Using LINQ to Handle Multiple Result Sets
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 = 

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date