Updating a Joined Query

A statement we hear a lot is:

“The Typed DataSet is great, but the DataAdapters don’t support joined queries.  I need a relational model in the database, but I need my application model to flatten things out:”

Using the Data Component Wizard, which is essentially the 2003 DataAdapter Wizard, developers can create an updatable joined select statement.

Using Visual Studio 2005 Beta 1 you can use the following steps:

1. Create a Windows Forms Project
2. Open the Data Sources Window
3. Select Add New DataSource
4. Select Database as the Data Source Type
5. Choose a connection to your Database.  For this example, we’ll use Northwind. 
   (Note there are some bugs in beta 1 regarding SQL Authenticated connections.  To avoid these bugs, use Windows Integrated Security.  This has been fixed and will be available in the next community technology preview)
6. Click next on the Save Connection String step and click [Next]
7. In the Choose your Database Object step select the table you wish to update, we’ll use Order Details, and click finish. 
   Don’t worry about the joined table just yet.  We first want to generate the Insert, Update and Delete Commands which we can only do if we have just 1 command. 
   (We hope to make this better, see end of this blog)
8. In the Data Sources Window select the 2^nd toolbar named Edit Data Source in Designer (Note: this should say edit DataSet in designer). 
9. At this point you’re looking at the new DataSet Designer.  In addition to being a designer, designed for DataSets, rather then using the XSD Schema editor for DataSets, we’ve also incorporated the DataAdapters.  In 2005 we now generate typed DataAdapters called TableAdapters.  I hope to write a blog on these as well.
10. Select the OrderDetailsTableAdapter.  Be careful to select the header of the TableAdapter to see all the associated properties, including the SelectCommand, InsertCommand, UpdateCommand, DeleteCommand, …  If you select the Fill, GetData() you’ll only see the properties for the SelectCommand which is ok for what we’re actually going to change, but I want to show the other commands as well.
11. With the TableAdapter selected, expand the InsertCommand and view the CommandText.  Notice that it updates the single table you selected in the wizard. 
    INSERT INTO [dbo].[Order Details] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount]) VALUES (@OrderID, @ProductID, @UnitPrice, @Quantity, @Discount)
12. On the TableAdapter, expand the SelectCommand and click the […] on the CommandText to open the Query Builder.
13. Using the Query Builder select the top visual pane and using the context menu select Add Table… 
14. Add the Products table and check the ProductName column
15. This now generates a joined query that will include the ProductName.  This now gives you a SELECT statement like the following:
    SELECT  dbo.[Order Details].OrderID, dbo.[Order Details].ProductID, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity, dbo.[Order Details].Discount, dbo.Products.ProductName
    FROM    dbo.[Order Details] INNER JOIN
            dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID
16. Close the Query Builder by pressing [OK]
17. Although the DataTable doesn’t resize (bug), you can use the context menu to select Autosize Selected Controls and you’ll now see the ProductName column in the typed DataTable
18. With the TableAdapter selected, look at the Insert, Update and Delete commands. Notice how they still only operate on the base table (Order Details)

This is the same model that works with the 2002 and 2003 DataAdapter wizards.  While this only updates a single table it is possible to update multiple tables using the RowUpdated and RowUpating events which are exposed through the partial class.  (But that’s a topic for another entry…)

Supporting Joins directly in the wizard

We have discussed how we might support this in the initial pass through the wizard.  As with all the great ideas we’ve received from customers, we need to figure out when to stop so we can just ship already.  However, should our team get ahead of schedule we are considering the following:

When we detect a joined query, we don’t really know which table you would want to update.  However, we do have enough info to ask the user which table they’d like the Insert, Update and Delete statements created for.  We would prompt the user with a dialog listing the tables within the query and once they choose one we would continue as we did above.

No promises as we still have lots of bugs and we really want to make sure we finish up enabling scenarios that are otherwise unachievable.  With the above work around, we don’t see this as a ship killer, but it is one of my pet peeves and I hope we have time to fix it.

Steve Lasker

Program Manager

Data Design-Time for Client apps.