Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

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.


advertisement
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) oListResults.Add(oRec);

While that's certainly valid code, you can instead populate the list using a method overload that specifies an IEnumerable:

List<VendorResults> oListResults = new List<VendorResults>(oVendorResults);

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 AdventureWorks(this.GetConnString()); 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 = db.GetPOSummary(XmlOrders);

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 = db.GetPODetails(XmlOrders); IEnumerable <GetPODetailsResult1> oHeader = result.GetResult<GetPODetailsResult1>(); IEnumerable<GetPODetailsResult2> oDetails = result.GetResult<GetPODetailsResult2>();



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

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