Tip 10: A LINQ Multiple Result Sets Example (the Back End)
The next two tips combine some of the LINQ content from the last two tips to build a small web-based example. Because I often use an aging receivables example to illustrate reporting/result set techniques, I'll use that one. I don't use LINQ to SQL nearly as much as I use LINQ to XML/Objects/DataSets; however, I do like the ability to discover stored procedures and SP parameters in a strongly-typed manner.
Suppose you want to:
- Write a stored procedure that returns multiple result sets for an aging receivables report.
- Create a strongly-typed context to the stored procedure and the result sets.
- Define a "composite" class that stores multiple sets in one class (similar to a "DataSet" but without the overhead).
- Use the result set classes to design a set of GridViews in ASP.NET.
- Use LINQ to SQL to call the stored procedure, return multiple result sets, and bind them to the grid.
|Figure 4. Using GridViews: Here's a web page with several formatted GridViews.|
In this tip, I'll walk through the back-end steps. In the next tip I'll cover the front-end steps. The end goal is the test web page in Figure 4
First, in the interest of keeping this article from being too long, I won't include the listing for the stored procedure here. Suffice it to say that the specifics of the stored procedure aren't particularly relevant here—the stored procedure receives an XML string of customer IDs, an "as-of" date, and a flag indicating whether to return only summary or both detail and summary information.
Second, you need to create a strongly-typed context to the stored procedures in the database. You can do this by creating a LINQ to SQL DBML file, or you can use SQLMetal. SQLMetal comes with Visual Studio 2008, and is stored in the Program Files \ Microsoft SDKs \ Windows \ v6.0A \ BIN folder. You can use these command-line parameters to generate a context for the stored procedures in a database:
After SQLMetal generates the context file, you can add it to your project. Listing 1
shows an excerpt of the generated context—specifically, the generated context for the stored procedure. Notice that the context class contains a method for the stored procedure along with typed references to the parameters and result sets. The four result sets are GetAgingResults1
, etc. I'll talk about those names in few moments.
There's something "missing" from this class file, something that you'll need. Can you guess what it is? Well, here's a hint: while there are classes for each of the four result sets (details, summary, brackets, clients), there's no class that combines the four result sets into one class. You can address that by creating a composite/container class that combines (or "pairs") sets of lists for the individual result sets.
shows a class called AgingResultSet that combines multiple List<> class properties for each of the result set names. Eventually, you'll do the following:
- Populate four list classes for the four result sets.
- Create an instance of AgingResultSet.
- Store a List of type GetAgingResultSets1 into the List property DetailResult, GetAgingResultSets2 into SummaryResult, etc. (You'll see this in detail when I cover Listing 3—I'm just giving you a quick look forward.)
One other note about the class in Listing 2
—for each of the four result sets, there is a simple GetResults
method for each of the four List class results. These are purely for the client-side data-aware controls to "discover" the result set classes, so that you can design against them.
Finally, now that you've built a strongly-typed context to the stored procedure, and you've established a composite class to hold the result sets, you can build a simple data access function to call the stored procedure. Listing 3
uses the technique from Tip 9 to call a stored procedure using the IMultipleResults interface. In essence, Listing 3
does the following:
Tip 11: A LINQ Multiple Result Sets Example (the Front End)
- Calls the context method GetAging, and returns an instance of IMultipleResults.
- Use the IMultipleResult's GetResult method to access each of the four result sets.
- Create a new instance of the AgingResult composite class (see Listing 2).
- Assign the four result sets into the instance of the AgingResult class. Note the new object's initialization syntax, where you can specify the properties of the instantiated class without needing a constructor for the class (in Listing 3).
- Return the instance of the AgingResult class.
Part 2 of this process is to build the client-side piece to archive the web page in Figure 4
. The goal is to design some basic ASP.NET GridView controls against the result set classes from the context.
Prior to Visual Studio 2008 and ASP.NET 3.5, designing a GridView against any type of result set class could be a bit tricky. Fortunately, the ObjectDataSource control in ASP.NET 3.5 (used for defining the data source) has some improved visibility—this allows you to point the web form GridViews to the result set classes, populate the GridViews with the necessary columns from the result sets, and allows you to customize the output of the GridView controls.
You'll follow these steps:
- Create a new ASP.NET web application and add references to any DLL(s) associated with the Listings from the previous tip.
- Create a new web page and drop four GridView controls onto the page.
- For the first GridView, right-click and select "New Data Source" (see Figure 5).
|Figure 5. Design Model: You can use the GridView Tasks dialog to select a data source for the GridViews.||
|Figure 6. Choose Data Source: In the Data Source Configuration Wizard, select an ObjectDataSource, and give it a name.||
- Visual Studio 2008 will display the ObjectDataSource dialog box (see Figure 6). Specify a meaningful name.
- Select the business object and method name (see Figure 7 and Figure 8, respectively) for the corresponding GridView). Repeat this process for each of the four GridViews.
|Figure 7. Choose a Business Object: Select the business object that you'll use to retrieve or update data.||
|Figure 8. Choose the Retrieval Method: Select the object method that retrieves data.||
At this point, each of the four GridView controls will have column definitions from the result set classes. You can access the column collection editor (see Figure 9) for each of the GridViews, to customize alignments, change heading text, or format columns for date and currency values.
|Figure 9. Modify Generated Columns: After you define the GridView's data source, you can modify the generated columns.|
- Finally, you'll need to write some code to call the data access class from the last tip, and to populate the GridView controls. Listing 4 provides an example of this. Note that in the code, you need to set the DataSourceID to null because you're about to override the design-time source with an actual source (of the same type). Also note that I'm creating an instance of the composite class and that I'm using the properties from the class that were populated back in the data access class:
oResult = oDa.GetAgingData(XmlCustomers,
new DateTime(2006, 1, 1), true);
this.gdvDetails.DataSourceID = null;
this.gdvSummary.DataSourceID = null;