title: How to update a multi-table DataSet in ADO.NET with CASCADE UPDATE
body: <h1>Question</h1>
  <p>If I understand correctly, ADO.NET is geared for the following workflow:</p>
  <ol>
  <li>Query multiple tables from the database into a local model.</li>
  <li>Make edits to the local model.</li>
  <li>Push edits back to the database using optimistic concurrency.</li>
  </ol>
  <p>What is the standard way for (3) to work on multiple tables when CASCADE UPDATE is present?</p>
  <h1>Minimal Example</h1>
  <p>Consider the following tables <code>Product</code> and <code>Price</code>.  There are two products &quot;Apples&quot; and &quot;Oranges&quot;, with a price of $3.50 for &quot;Oranges&quot;.  The column <code>Price.ProductName</code> references <code>Product.Name</code>, with <code>ON UPDATE CASCADE</code>.</p>
  <p><a href="https://i.stack.imgur.com/NGZYk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NGZYk.png" alt="Product table containing two rows with Name &quot;Apples&quot; and &quot;Oranges&quot;, and Price table containing a single row with ProductName &quot;Oranges&quot; and Amount $3.50" /></a></p>
  <p>Let's say we update both tables locally, from &quot;Oranges&quot; to &quot;Orange Is&quot;, and from $3.50 to $9.99.  Then we push edits back to the database.</p>
  <p>The problem is, no matter which table we push first, the operation fails.</p>
  <p><strong>Case 1: Push <code>Product</code> first, then <code>Price</code>.</strong>  First, <code>Product</code> is updated, from &quot;Oranges&quot; to &quot;Orange Is&quot;.  The change also propagates automatically to <code>Price.ProductName</code>, due to the CASCADE.  Across the board, the name is &quot;Orange Is&quot;.  But then the update from $3.50 to $9.99 <em>fails</em> with zero rows affected because the optimistic concurrency is still trying to update rows in <code>Price</code> with &quot;Oranges&quot;, but that <code>ProductName</code> no longer exists.</p>
  <p><a href="https://i.stack.imgur.com/bCyQs.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bCyQs.png" alt="Both Product.Name and Price.ProductName already updated as &quot;Orange Is&quot;, but Amount cannot be updated" /></a></p>
  <p><strong>Case 2: Push <code>Price</code> first, then <code>Product</code>.</strong>  The attempt to update <code>Price.ProductName</code> from &quot;Oranges&quot; to &quot;Orange Is&quot; fails immediately because no <code>Product.Name</code> exist yet with the name &quot;Orange Is&quot;; it still has the old value &quot;Oranges&quot;.  The foreign key constraint prevents the update.</p>
  <p><a href="https://i.stack.imgur.com/4DUGQ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4DUGQ.png" alt="Update to Price.ProductName cannot be completed due to foreign key constraint" /></a></p>
  <h1>Sample Code</h1>
  <p><strong>SQL</strong></p>
  <pre><code>CREATE TABLE Product(
      Name VARCHAR(100) NOT NULL PRIMARY KEY
  );
  
  CREATE TABLE Price(
      ProductName VARCHAR(100) NOT NULL,
      Amount DECIMAL(18,2),
      CONSTRAINT FK_Price_Product
          FOREIGN KEY (ProductName)
          REFERENCES Product(Name)
          ON UPDATE CASCADE
  );
  
  INSERT INTO Product
  VALUES ('Apples'), ('Oranges');
  INSERT INTO Price
  VALUES ('Oranges', 3.50);
  </code></pre>
  <p><strong>C#</strong></p>
  <pre><code>using System.Data;
  using System.Data.Common;
  
  void DoEdit(DbConnection conn)
  {
      using (var model = new DataSet())
      using (var productAdapter = CreateDataAdapter(conn, &quot;Product&quot;, &quot;SELECT * FROM [Product]&quot;))
      using (var priceAdapter = CreateDataAdapter(conn, &quot;Price&quot;, &quot;SELECT * FROM [Price]&quot;))
      {
          QueryFromDatabase(conn, model, productAdapter, priceAdapter);
          EditLocalModel(model);
          PushEditsToDatabase(conn, model, productAdapter, priceAdapter);
      }
  }
  
  void QueryFromDatabase(DbConnection conn, DataSet model, DataAdapter productAdapter, DataAdapter priceAdapter)
  {
      conn.Open();
      productAdapter.Fill(model);
      priceAdapter.Fill(model);
      conn.Close();
  
      model.Relations.Add(new DataRelation(&quot;Price References Product&quot;,
          model.Tables[&quot;Product&quot;].Columns[&quot;Name&quot;],
          model.Tables[&quot;Price&quot;].Columns[&quot;ProductName&quot;]
      ));
  }
  
  void EditLocalModel(DataSet model)
  {
      // Edit both tables, foreign key cascades.
      model.Tables[&quot;Product&quot;].Rows.Find(&quot;Oranges&quot;)[&quot;Name&quot;] = &quot;Orange Is&quot;;
      model.Tables[&quot;Price&quot;].Rows[0][&quot;Amount&quot;] = 9.99M;
  }
  
  void PushEditsToDatabase(DbConnection conn, DataSet model, DataAdapter productAdapter, DataAdapter priceAdapter)
  {
      conn.Open();
  
      productAdapter.Update(model);
      priceAdapter.Update(model);
  
      //// Or, reverse order
      // priceAdapter.Update(model);
      // productAdapter.Update(model);
  
      conn.Close();
  }
  
  DbDataAdapter CreateDataAdapter(DbConnection conn, string tableName, string selectSql)
  {
      var adapter = DbProviderFactories.GetFactory(conn).CreateDataAdapter();
      adapter.SelectCommand = conn.CreateCommand();
      adapter.SelectCommand.CommandText = selectSql;
      adapter.TableMappings.Add(&quot;Table&quot;, tableName);
      adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
      DbProviderFactories.GetFactory(conn).CreateCommandBuilder().DataAdapter = adapter;
      return adapter;
  }
  </code></pre>