The Baker's Dozen Doubleheader: 26 Productivity Tips for Managing Data (Part 2 of 2) : Page 4
The conclusion of a two-part article on managing data with Visual Studio 2005 and SQL Server focuses on .NET generics, provides an introduction to the new ObjectDataSource class, and demonstrates some additional new capabilities in T-SQL 2005.
by Kevin S. Goff
Oct 20, 2006
Page 4 of 5
Tip 7: The Baker's Dozen Spotlight: Building a Data Access Class to Populate Typed Datasets Using .NET Generics
I use a lot of stored procedures and typed datasets. One of my many "holy grail" quests has been to populate a typed dataset directly from a stored procedure.
For a very long time, I used a base method in my data access class to populate a plain vanilla dataset from a stored procedure with parameters. Afterwards I would merge the dataset into a typed dataset. This certainly worked, but it meant additional code and an additional processing step. What I wanted to do was pass an instance of a typed dataset into the base method, and have the base method serve as a factory-to pump out a populated typed dataset.
.NET generics allow me to create such a class and then use it (see Listing 4 and Listing 5). To create a class like this, follow these steps:
Create an instance of a typed dataset, and an instance of the Data Access Class (which appears in Listing 5).
Create a typed List of SQL parameters for the stored procedure (instead of using ArrayList).
Call the data access class method (ReadIntoDs), passing an instance of the typed dataset, the name of the stored procedure, and the typed List of parameters for the stored proc.
Create the data access method ReadIntoDs (see Listing 5), and specify a typed placeholder for the first parameter and for the return value. Note the restriction that the parameter must be a dataset since code inside the method will use dataset-specific properties and methods.
public T ReadIntoDS<T>
(T dsTypedDs, string cStoredProc,
where T : DataSet
Define the standard SqlConnection object, SqlDataAdapter, etc.
Elbow grease time! SQL Server returns stored procedure result sets with names of Table, Table1, Table2, etc. When I designed the typed dataset, I might have used more descriptive names (dtClient, dtDetails, etc.) Therefore, I need to map the names Table, Table1, etc., to the names in the typed dataset, using the TableMappings command of the DataAdapter.
Fill the dataset from the DataAdapter, and return it.
Tip 8: An Overview of the ASP.NET 2.0 ObjectDataSource
In Part 1 of this series, I discussed the ASP.NET 2.0 GridView, how to bind it to a dataset, and also how to handle sorting and paging. I can also use the ASP.NET 2.0 ObjectDataSource, which allows me to expose data-aware business objects/classes to data-bound controls such as the GridView.
Figure 1: Add a reference to the business object, to use for ObjectDataSource.
I'll take a step-by-step approach to using the ObjectDataSource. In the first example, I'll set up a business object that returns a typed dataset of orders that are "on hold." Then I'll create a Web page with a GridView and define the business object as the direct DataSource for the GridView.
Create a separate project called bzOrders. Build a simple typed dataset called dsOrders, and a method called GetOrdersOnHold that returns a populated instance of dsOrders.
Create a new Web page project and add bzOrders.dll as a reference (see Figure 1).
On the new Web page, create an instance of the ObjectDataSource from the toolbox (see Figure 2).
Figure 2: From the toolbox, drop an instance of the ObjectDataSource onto the Web page.
Right-click on the new ObjectDataSource and choose the option to configure the data source. Visual Studio 2005 will display the Configure Data Source dialog box (see Figure 3). Select bzObjects as the class that will provide the data.
Next, I need to tell the ObjectDataSource which methods that I want to use for the standard Select, Insert, Update, and Delete operations. For this example, I'm only specifying a Select command, the method GetOrdersOnHold (see Figure 4).
Create a GridView on the Web page and set the DataSourceID property to the ObjectDataSource (see Figure 5). Also set the other GridView properties to the settings in Figure 5, including the options to allow/enable sorting and paging.
At this point, I could actually run the Web page. However, I may want to set column sizes and alignments, customizing the headings, etc. Load the property sheet for the GridView and select the Columns collection. Visual Studio 2005 will display the column/field designer (see Figure 6) for me to customize the GridView.
Finally, Figure 7 shows the results when I run the Web page.
Author's Note: Very important! Note that Figure 7 shows the page links for me to navigate to different pages. Also note that the column headings are set for me to click and sort. When I bind to an ObjectDataSource, Visual Studio 2005 automatically handles the sorting and paging for me! No additional code is required.
Figure 3: Right-click on the new ObjectDataSource to select the business object.
Figure 4: Select the method in the business object for retrieving data.
Figure 5: Create a GridView, and set the DataSourceID and other properties.
Figure 6: The selected fields are already established-you can customize the display.
This tip merely provides an introduction to the ObjectDataSource class. Other tasks I can perform using the ObjectDataSource are as follows:
Define a retrieve method in the business object that utilizes a parameter to retrieve a single order. In the ObjectDataSource designer, I can specify the method as the retrieve method and define the parameter to the value of a page control, a FormField value, a QueryString, or a Session variable.
Define methods in the business object to perform standard Insert, Update, and Delete functions. Again, in the ObjectDataSource designer, I can map the methods and define any parameters.
Finally, you may be wondering how to "capture" the data that the ObjectDataSource returns. For instance, you may want to display the number of rows in the grid caption. You can utilize the Selected event of the ObjectDataSource.