title: How Does One Fill a Typed DataSet, Keep it Synchronized, and Receive Updates When the Data Changes?
body: <p>So I'm developing an application that works as sort of a "sidekick" to a large proprietary application which I do not have the source code for nor the rights to modify. The proprietary application <em>does</em> store all of its data in a Microsoft SQL database (version 2008 R2 or higher, I believe), however, and I have a good idea what the data represents. What I need my application to do is to constantly monitor the data as it is being added, updated, and deleted, and then act on the data automatically (such as raising alerts).</p>
  
  <p>The issue is figuring out the best approach to receiving changes made to the database by the other application as they're happening, because I <em>don't wanna miss a beat</em>.</p>
  
  <p>Here is <em>what I have done so far</em>:</p>
  
  <ol>
  <li><strong>LINQ to SQL</strong>: As far as I know, each time I run a query, I receive a new set of data, but I do not get the ability to receive the changes only or be notified of changes.</li>
  <li><p><strong>Typed DataSet using DataSet.Load</strong>:</p>
  
  <pre><code>using (IDataReader reader = dataSetInstance.CreateDataReader())
  {
      dataSetInstance.Load(reader, LoadOption.OverwriteChanges, dataSetInstance.Table1, dataSetInstance.Table2, dataSetInstance.Table3);
  }
  </code></pre>
  
  <p>This didn't work out too well when I did it. <code>dataSetInstance</code> only contained a set of unfilled tables after calling the <code>Load</code> method. I was hoping to call <code>dataSetInstance.GetChanges</code> and <code>dataSetInstance.AcceptChanges</code> at regular intervals after the first call to <code>dataSetInstance.Load</code> to get only the changes. Am I doing it wrong?</p></li>
  <li><p><strong>Typed DataSet with tables filled individually using their associated table adapters</strong>:</p>
  
  <pre><code>using (Table1TableAdapter adapter = new Table1TableAdapter())
  {
      adapter.Fill(dataSetInstance.Table1);
  }
  
  using (Table2TableAdapter adapter = new Table2TableAdapter())
  {
      adapter.Fill(dataSetInstance.Table2);
  }
  
  using (Table3TableAdapter adapter = new Table3TableAdapter())
  {
      adapter.Fill(dataSetInstance.Table3);
  }
  </code></pre>
  
  <p>Of course, the problem is that there are actually way more than 3 tables which can add up to quite a lot of repetitive code (and maintenance work), but the real problem is that I will not receive any change notifications since I'm not using the <code>Load</code>/<code>AcceptChanges</code> methods (according to the documentation).</p></li>
  <li><strong>Row retrieval by date/time field</strong>: This was something I started work on, but something I stopped after observing the other application modify fields in the rows after creating them. Consider this:
      There is a row with a time stamp of a transaction and a boolean field that specifies if the transaction was canceled later on. If it is canceled, the other application simply goes back to that row and toggles the value. The time stamp remains the same, and my application will never know of the news. There is no <em>statute of limitations</em>; the other application can change this field any time in the future.</li>
  </ol>
  
  <p>By the way, I should mention that this other application does not implement any constraints within the database such as foreign and primary keys. I believe I read somewhere in the documentation that for row update events and such to fire on the typed DataTable classes, some sort of primary key is needed.</p>
  
  <p>There must be some way to do this!!!</p>