{"id":7413,"date":"2004-07-29T11:06:00","date_gmt":"2004-07-29T11:06:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/vbteam\/2004\/07\/29\/updating-a-joined-query\/"},"modified":"2024-07-05T15:03:32","modified_gmt":"2024-07-05T22:03:32","slug":"updating-a-joined-query","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/vbteam\/updating-a-joined-query\/","title":{"rendered":"Updating a Joined Query"},"content":{"rendered":"

A statement we hear a lot is:<\/p>\n

<\/span><\/p>\n

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

<\/span><\/i><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

Using the Data Component Wizard<\/i><\/b>, which is essentially the 2003 DataAdapter Wizard, developers can create an updatable joined select statement.<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

Using Visual Studio 2005 Beta 1 you can use the following steps:<\/p>\n

<\/span><\/p>\n

    \n
  1. Create a Windows Forms Project\n<\/p>\n

    <\/span><\/li>\n

  2. Open the Data Sources Window\n<\/p>\n

    <\/span><\/li>\n

  3. Select Add New DataSource\n<\/p>\n

    <\/span><\/li>\n

  4. Select Database as the Data Source Type\n<\/p>\n

    <\/span><\/li>\n

  5. Choose a connection to your Database.  <\/span>For this example, we’ll use Northwind.  <\/span>
    (Note there are some bugs in beta 1 regarding SQL Authenticated connections.  <\/span>To avoid these bugs, use Windows Integrated Security.  <\/span>This has been fixed and will be available in the next community technology preview)\n<\/p>\n

    <\/i><\/span><\/li>\n

  6. Click next on the Save Connection String step and click [Next]\n<\/p>\n

    <\/span><\/li>\n

  7. In the Choose your Database Object step select the table you wish to update, we’ll use Order Details<\/b>, and click finish.  <\/span>
    Don’t worry about the joined table just yet.  <\/span>We first want to generate the Insert, Update and Delete Commands which we can only do if we have just 1 command.  <\/span>
    (We hope to make this better, see end of this blog)\n<\/p>\n

    <\/span><\/li>\n

  8. In the Data Sources Window select the 2nd<\/sup> toolbar named Edit Data Source in Designer<\/b> (Note: this should say edit DataSet in designer).  <\/span>\n<\/p>\n

    <\/span><\/li>\n

  9. At this point you’re looking at the new DataSet Designer.  <\/span>In addition to being a designer, designed for DataSets<\/i>, rather then using the XSD Schema editor for DataSets, we’ve also incorporated the DataAdapters.  <\/span>In 2005 we now generate typed DataAdapters called TableAdapters.  <\/span>I hope to write a blog on these as well.\n<\/p>\n

    <\/span><\/li>\n

  10. Select the OrderDetailsTableAdapter.  <\/span>Be careful to select the header of the TableAdapter to see all the associated properties, including the SelectCommand, InsertCommand, UpdateCommand, DeleteCommand, …  <\/span>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.\n<\/p>\n

    <\/span><\/li>\n

  11. With the TableAdapter selected, expand the InsertCommand and view the CommandText.  <\/span>Notice that it updates the single table you selected in the wizard.  <\/span>
    <\/span>INSERT INTO [dbo].[Order Details] ([OrderID], [ProductID], [UnitPrice], [Quantity], [Discount]) VALUES (@OrderID, @ProductID, @UnitPrice, @Quantity, @Discount)<\/span>\n<\/p>\n

    <\/span><\/li>\n

  12. On the TableAdapter, expand the SelectCommand and click the […] on the CommandText to open the Query Builder.\n<\/p>\n

    <\/span><\/li>\n

  13. Using the Query Builder select the top visual pane and using the context menu select Add Table…  <\/span>\n<\/p>\n

    <\/span><\/li>\n

  14. Add the Products table and check the ProductName column\n<\/p>\n

    <\/span><\/li>\n

  15. This now generates a joined query that will include the ProductName.  <\/span>This now gives you a SELECT statement like the following:
    <\/span>SELECT  <\/span>dbo.[Order Details].OrderID, dbo.[Order Details].ProductID, dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity, dbo.[Order Details].Discount, dbo.Products.ProductName
    FROM    <\/span>dbo.[Order Details] INNER JOIN
            <\/span>dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID\n<\/p>\n

    <\/span><\/li>\n

  16. Close the Query Builder by pressing [OK]\n<\/p>\n

    <\/span><\/li>\n

  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\n<\/p>\n

    <\/span><\/li>\n

  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)\n<\/p>\n

    <\/span><\/li>\n<\/ol>\n

    <\/p>\n

     <\/p>\n

    <\/span><\/p>\n

    This is the same model that works with the 2002 and 2003 DataAdapter wizards.  <\/span>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.  <\/span>(But that’s a topic for another entry…)<\/p>\n

    <\/span><\/p>\n

    <\/p>\n

     <\/p>\n

    <\/span><\/p>\n

    Supporting Joins directly in the wizard<\/p>\n

    <\/span><\/b><\/p>\n

    We have discussed how we might support this in the initial pass through the wizard.  <\/span>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.  <\/span>However, should our team get ahead of schedule we are considering the following:<\/p>\n

    <\/span><\/p>\n

    When we detect a joined query, we don’t really know which table you would want to update.  <\/span>However, we do have enough info to ask the user which table they’d like the Insert, Update and Delete statements created for.  <\/span>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.<\/p>\n

    <\/span><\/p>\n

    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.  <\/span>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.<\/p>\n

    <\/span><\/p>\n

    <\/p>\n

     <\/p>\n

    <\/span><\/p>\n

    Steve Lasker<\/p>\n

    <\/span><\/p>\n

    Program Manager<\/p>\n

    <\/span><\/p>\n

    Data Design-Time for Client apps.<\/span><\/p><\/p>\n","protected":false},"excerpt":{"rendered":"

    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 […]<\/p>\n","protected":false},"author":260,"featured_media":8818,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[192,195],"tags":[],"class_list":["post-7413","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-featured","category-visual-basic"],"acf":[],"blog_post_summary":"

    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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/7413","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/users\/260"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/comments?post=7413"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/7413\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media\/8818"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media?parent=7413"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/categories?post=7413"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/tags?post=7413"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}