Typed Data Sets

Typed data sets are a form of strongly typed business objects that fetch data from the database and expose it via a set of methods. They provide the main functionality of a business and data layer, with methods to fetch and modify data, so they save you from having to create your own layer. You create typed data sets by adding a new item to the App_Code folder, selecting the DataSet option from the Add New Item dialog (see Figure 4.6).

Figure 4.6. Adding a DataSet

When a data set is created, the TableAdapter Configuration Wizard launches, the first screen of which (see Figure 4.7) allows you to define the SQL statement to fetch the data. The Advanced Options button shows another window (see Figure 4.8) allowing you to select whether data modification methods are added to the dataset, whether optimistic concurrency will be used (covered in more detail in Chapter 5), and whether the data should be refreshed after changes to ensure that any IDENTITY fields are updated.

Figure 4.7. Defining the SQL statement for the DataSet

Figure 4.8. The Advanced Options of DataSet configuration

After the query and advanced options are set, you can configure the methods that will be generated (see Figure 4.9). The first option allows you to have a method that accepts a DataTable or DataSet object to be filled with data. The second option allows you to have a method (called GetCustomers, here) that returns a DataTable objectthis is similar to the methods manually created on the data layer earlier in the chapter. In a similar vein, the final option allows creation of the insert, update, and delete methods.

Figure 4.9. Generating the DataSet methods

When the configuration wizard has finished, the data set is shown in the design window (see Figure 4.10).

Figure 4.10. The DataSet Designer

You can see that each of the columns is represented as a property, and that the GetCustomers method shows as a method of a subtypeCustomersTableAdapter. The typed data set doesn't generate a class, but instead generates an XML definition of what the class will contain; you can see this if you select View Code from the menu when viewing the data set. Since the data set is in the App_Code directory, the class is automatically generated from the XML at compile time.

You can use the data set objects directly in code, a shown in Listing 4.12.

Listing 4.12. Using a Typed DataSet from Code

CustomersTableAdapters.CustomersTableAdapter c =   new CustomersTableAdapters.CustomersTableAdapter(); Customers.CustomersDataTable ct = c.GetCustomers(); Customers.CustomersRow cr = ct.Rows[0]; cr.CompanyName = "New Company name"; c.Update(cr); c.Update(1, "New Company Name", "New Contact Name", ...); c.Insert(10, "Another New Company Name",              "Another New Contact Name", ...);

You can see that you can access the data at a variety of different levels. You can create a CustomersDataTable to represent the table, and then use a CustomersRow to access individual rows, each of which has properties representing the table columns. Alternatively, you can use the CustomersTableAdapter methods directly, calling Update to update columns, or Insert to insert a new row. One issue with the created methods is that they represent the default casethe methods and parameters that Microsoft add. For example, the first parameter of the Insert method is the CustomerID, which isn't required; it is an auto-generated field. You might also wish to add additional methods, perhaps overloading the existing ones, or add completely new ones for new functionality. You can achieve this by creating a partial class with the same name as the existing class, as shown in Listing 4.13.

Listing 4.13. Adding functionality to a Typed DataSet

namespace CustomersTableAdapters {   public partial class CustomersTableAdapter   {     public void Update(string NewCompanyName)     {     }     public void MyMethod()     {     }   } }

Here the namespace and class match those of the generated class. The new Update and MyMethod methods would now appear on the class (even in IntelliSense). The use of a partial class means that the same class is split across multiple physical files, something that wasn't possible in previous versions of the framework. This means that your custom code is standalone, and will not be lost if you regenerate the data set, for example, by adding a new column to the table.

Using Typed DataSets with the ObjectDataSource Control

Using typed DataSets with the ObjectDataSource control is similar to using standard classes, except there are a couple of things to watch out for. You can use the Configure DataSource tasks, which will fill in the properties and set the parameters to pick the object and method, as shown in Listing 4.14.

Listing 4.14. An ObjectDataSource Using the Typed DataSet

<asp:ObjectDataSource  runat="server"   TypeName="CustomersTableAdapters.CustomersTableAdapter"   DeleteMethod="Delete" SelectMethod="GetCustomers"   InsertMethod="Insert" UpdateMethod="Update"   OldValuesParameterFormatString="original_{0}">   <DeleteParameters>     <asp:Parameter Name="Original_CustomerID" Type="String" />   </DeleteParameters>   <UpdateParameters>     <asp:Parameter Name="CustomerID" Type="String" />     <asp:Parameter Name="CompanyName" Type="String" />     <asp:Parameter Name="ContactName" Type="String" />     <asp:Parameter Name="ContactTitle" Type="String" />     <asp:Parameter Name="Address" Type="String" />     <asp:Parameter Name="City" Type="String" />     <asp:Parameter Name="Region" Type="String" />     <asp:Parameter Name="PostalCode" Type="String" />     <asp:Parameter Name="Country" Type="String" />     <asp:Parameter Name="Phone" Type="String" />     <asp:Parameter Name="Fax" Type="String" />     <asp:Parameter Name="Original_CustomerID" Type="String" />   </UpdateParameters>   <InsertParameters>     <asp:Parameter Name="CustomerID" Type="String" />     <asp:Parameter Name="CompanyName" Type="String" />     <asp:Parameter Name="ContactName" Type="String" />     <asp:Parameter Name="ContactTitle" Type="String" />     <asp:Parameter Name="Address" Type="String" />     <asp:Parameter Name="City" Type="String" />     <asp:Parameter Name="Region" Type="String" />     <asp:Parameter Name="PostalCode" Type="String" />     <asp:Parameter Name="Country" Type="String" />     <asp:Parameter Name="Phone" Type="String" />     <asp:Parameter Name="Fax" Type="String" />   </InsertParameters> </asp:ObjectDataSource>

The one thing that is different from earlier examples is the use of the OldValuesParameterFormatString property, which defines the format of the parameter holding old valuesi.e., the values that are not being updated. This is used most often when optimistic concurrency checking is in place, but in this example, the old value is the CustomerIDthe ID value is auto-generated, so not updateable. Within the OldValuesParameterFormatString property, the {0} is a placeholder, and is replaced with the actual parameter name, which you can see in the DeleteParameters and for the last parameter in the UpdateParameters.

The problem with the way the ObjectDataSource is configured is when used in conjunction with a GridView. This is shown in Listing 4.15, which was generated by binding the GridView to the ObjectDataSource.

Listing 4.15. A GridView Bound to a Typed DataSet ObjectDataSource

<asp:GridView  runat="server"   AutoGenerateColumns="False" DataKeyNames="CustomerID"   DataSource>   <Columns>     <asp:CommandField ShowDeleteButton="True"       ShowEditButton="True" />     <asp:BoundField DataField="CustomerID"       HeaderText="CustomerID" ReadOnly="True"       SortExpression="CustomerID" />     <asp:BoundField DataField="CompanyName"       HeaderText="CompanyName" SortExpression="CompanyName" />     <asp:BoundField DataField="ContactName"       HeaderText="ContactName" SortExpression="ContactName" />     <asp:BoundField DataField="ContactTitle"       HeaderText="ContactTitle" SortExpression="ContactTitle" />     <asp:BoundField DataField="Address" HeaderText="Address"       SortExpression="Address" />     <asp:BoundField DataField="City" HeaderText="City"       SortExpression="City" />     <asp:BoundField DataField="Region" HeaderText="Region"       SortExpression="Region" />     <asp:BoundField DataField="PostalCode" HeaderText="PostalCode"       SortExpression="PostalCode" />     <asp:BoundField DataField="Country" HeaderText="Country"       SortExpression="Country" />     <asp:BoundField DataField="Phone" HeaderText="Phone"       SortExpression="Phone" />     <asp:BoundField DataField="Fax" HeaderText="Fax"       SortExpression="Fax" />   </Columns> </asp:GridView>

The columns have been automatically generated, and the important column to note is the CustomerID, shown in bold. Notice that the ReadOnly property is set to TRue. The grid knows that this is auto-generated and thus marks it as read-only, but the typed DataSet expects CustomerID as the first parameter to the Update method; there's a bit of difference in the way the two objects work.

There are two ways around this problem. The first is to allow updates to the CustomerID field, which probably isn't the best solution, because updates would fail in the SQL Server when they tried to update the IDENTITY column. The second solution is to modify the SQL commands that the typed DataSet uses.

Changing the SQL for the typed DataSet can be done by first opening the XSD file, and selecting the CustomersTableAdapter (Figure 4.11), and viewing the properties. Then, because each of the commands can be modified, you can either edit the CommandText inline or click the small button (see Figure 4.12) to open the QueryBuilder (see Figure 4.13). Then you can simply remove the CustomerID column from the SQL statement.

Figure 4.11. Selecting the CustomersTableAdapter

Figure 4.12. Viewing the CommandText of the UpdateCommand

Figure 4.13. Using the Query Builder to modify commands

Once you have changed, and saved, the typed DataSet, you can reconfigure the ObjectDataSource and grid, and the update command will now work. The same problem exists with the insert command, but that isn't used with the GridView, but would need changing if you are using a DetailsView or FormView to perform your editing.