Office Test - Tutorial 10
The 10th page of the OfficeTest General Tests tutorial looks again at using Strongly Typed DataSets and ObjectDataSource in place of SqlDataSource. This time however we will review a slightly more complex example. If you want to review the other tutorials, the central page can be found here Firebird and .NET 2.0 development Example.
10. ObjectDataSource, Strongly Typed DataSets and Relations
- This tutorial is a cross between the Employee: Simple and BillingType: Strongly Typed DataSets tutorials. Essentially this web page has the same functionality and setup as the Employee: Simple but uses the ObjectDataSource and Strongly Typed DataSets method used in BillingType: Strongly Typed DataSets.
- Eval("JobPositionID"): Gives us the value of the JobPositionID for the current row in the DetailsView, which we then cast as an int type.
- (new ExampleDataSetsTableAdapters.JobPositionTableAdapter()): Here I create a new instance of the JobPosition TableAdapter from the schema. This is the only way to gain access to the data in the table adapter but is not really efficient. I wouldn't recommend this for a large dataset.
- GetDataByID: I added this function to the JobPosition TableAdapter so we could select a single row base on the JobPositionID.
- (ExampleDataSets.JobPositionDataTable): We need to cast the result of the call to GetDataByID as a JobPositionDataTable so we can access the fields correctly.
- Rows[0]["JobTitle"].ToString(): Since we called GetDataByID to return the DataTable we know that it will have exactly 1 row accessed at index=0. We want the "JobTitle" field from that row.
Employee: Strongly Typed DataSets.
If you've been going through the earlier tutorials then the design of this screen should be really familiar. Again we have a GridView that handles display of all the records and a DetailsView which handles all editing for the currently selected record. This time however we are using Strongly Typed DataSets.
Taking a quick look at the DataSet schema again we can see the Employee table and the JobPosition table plus a foreign key link joining the 2 tables.
You can add a relation like this by right-clicking on a particular table object in the schema and selecting "Add" and "Relation". This will open dialog with options for defining the relationship.
The basic setup of the web page including the GridView and DetailsView are essentially the same as before. The code in the EmployeeStronglyTypedDataSet.aspx.cs file is the same except we don't need the parameter binding fix as we did with SqlDataSource.
You should notice in this case that the GridView only shows the JobPositionID, not the JobTitle. I left this as is to demonstrate that the Strongly Typed DataSets don't provide any simple relation benefit that gives us access to this information. We could modify the select for the Employee table to include that information but that would de normalize our dataset schema.
In the DetailsView I handle this limitation so we'll concentrate on that code for the moment.
<asp:DetailsView ID="dvEmployee" runat="server" AutoGenerateRows="False" DataKeyNames="EmployeeID" DataSourceID="odsCurrentEmployee">
<Fields>
<asp:TemplateField HeaderText="JobPositionID" SortExpression="JobPositionID">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="odsAllJobPositions"
DataTextField="JobTitle" DataValueField="JobPositionID" SelectedValue='<%# Bind("JobPositionID") %>'>
</asp:DropDownList>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="odsAllJobPositions"
DataTextField="JobTitle" DataValueField="JobPositionID" SelectedValue='<%# Bind("JobPositionID") %>'>
</asp:DropDownList>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# ((ExampleDataSets.JobPositionDataTable)((new ExampleDataSetsTableAdapters.JobPositionTableAdapter()).GetDataByID((int)Eval("JobPositionID")))).Rows[0]["JobTitle"].ToString() %>'>
</asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Fields>
</asp:DetailsView>
I removed all the parameters and tags we don't need to focus on from the DetailsView HTML. If you look at the JobPositionID field you'll notice we have the typical 3 templates. The EditItemTemplate and InsertItemTemplate templates are actually completely standard. The data source they use is an ObjectDataSource linked to the JobPosition strongly typed dataset but the setup is still exactly the same as we've seen before.
Lets look at the ItemTemplate.
<asp:Label ID="Label1" runat="server" Text='<%# ((ExampleDataSets.JobPositionDataTable)((new ExampleDataSetsTableAdapters.JobPositionTableAdapter()).GetDataByID((int)Eval("JobPositionID")))).Rows[0]["JobTitle"].ToString() %>'>
</asp:Label>
This template contains a single label that will display the JobTitle from the JobPosition strongly typed dataset. Your first thought should be WOW that's quite the EVAL you got going there. Lets break this down a little.
This is a pretty ugly way to get access to something as simple as the JobTitle. There are other ways to do this. One way would be to simply select the JobTitle back in the Employee select. This seems to be the standard method most developers follow because it's the simplest. I would tend to agree although I hate the idea that I need to dirty the Employee table when the JobPosition table and relation information are already defined. No matter what method you use to handle your data, when it comes to accessing sub table or sub object information or properties I would say Microsoft really dropped the ball in their design.
If you decide to follow the method of selecting back the JobTitle you have to keep in mind that joins to other tables removes the ability to perform Inserts, Updates and Deletes. Ya, I know it makes no sense, since we could just supply the XSD interface with the SQL for those.
Sub select in place of a join
If you want to get around this problem with SQLServer you can perform a subselect on the column -
I'm not sure what the equivelant syntax is in Firebird if its even supported.
SELECT EmployeeID, FirstName, LastName, HireDT, JobPosition,
(SELECT JobTitle FROM JobPosition
WHERE JobPosition.JobPositionID = Employee.JobPositionID) AS JobTitle
FROM Employee There is another way to provide more functionality to your TableAdapter. If you open the ClassView while in the dataset design tab you will get something like the following:
Here we can navigate to the ExampleDataSetsTableAdapter branch and expand it. Below is the 3 datasets we have defined. If you select the EmployeeTableAdapter class then the list below (to the right in the image) shows the methods for that class. You can double click on a method to jump to the associated code. The code for these classes is actually generated at runtime or while debugging so you can't really modify the class directly. However if you either make your own subclass or define it as a partial class you can override or add your own methods.
Knowing this we could add a new column to the Employee TableAdapter called JobTitle and then override the Fill method and make it also get the JobTitle from the JobPosition TableAdapter. We could also just add a GetJob function that returns the JobTitle from the JobPosition TableAdapter and use that function in the field EVAL on the DetailsView.
I still don't like these solutions because retrieving lookup table (sub table or foreign key table) column information is very common and really you don't want to start messing around with adding overrides and partial classes to all this generic or generated code. If your going to start doing all that work it quickly becomes easier to just write your own business objects.
See next Tutorial...JobPosition: Custom Business/DataAccess objects which uses ObjectDataSource with custom made business classes instead of datasets.