title: lost with DataSets and merging in C#
body: <p>NEWEST EDIT!!!</p>
  
  <p>PLEASE...  I have been through many permutations of this code.  I am attempting to take some certain related records from a database (SQL server) and export to XML (which seems to work!), and take them to a disconnected copy of that database, and import them to that database, merging them by primary key (updating if the key exists and inserting if it doesn't).</p>
  
  <p>There are many tables in the database, and I want all the tables that relate to a certain list of entities that have been changed.  I figured out early on that I couldn't do a large complicated query with joins to get all the columns I wanted to fill my export DataSet, because I lost all the table structure.  I also had the Visual Studio 2005 DataSet designer create a typed dataset, which I'm not sure I need to do, and please tell me if I shouldn't.</p>
  
  <p>Here is what I'm doing to export the data to xml:</p>
  
  <pre><code>
  
  public void exportData(string filename, List sxOrgs) {
  
      MyGeneratedDataSet ds = new MyGeneratedDataSet();
      SqlDataAdapter adapter = new SqlDataAdapter();
      String query;
      String orgList = "(";
      //create the string query list of sxOrgs
  
      foreach (String sx in sxOrgs)
      {
          orgList += "'" + sx + "', ";
      }
  
      orgList = orgList.Remove(orgList.Length - 2);
      orgList += ")";
  
      try
          {
  
          //tblOrganization
          query = "select * from tblOrganization where tblOrganization.sxOrganization in " 
               + orgList;
          adapter.SelectCommand = new SqlCommand(query, Connectivity.Connection());
          adapter.Fill(ds.tblOrganization);
  
          //tblCategory
          query = "select * from tblCategory where sxCategory in " +
              "(select sxCategory from lnkOrganizationCategory " +
              "where lnkOrganizationCategory.sxOrganization in " +
              orgList + ")";
          adapter.SelectCommand = new SqlCommand(query, Connectivity.Connection());
          adapter.Fill(ds.tblCategory);
  
          //lnkOrganizationCategory
          query = "select * from lnkOrganizationCategory where sxOrganization in " + orgList;
              adapter.SelectCommand = new SqlCommand(query, Connectivity.Connection());
              adapter.Fill(ds.lnkOrganizationCategory);
  
          //tblContact
          query = "select * from tblContact where sxContact in " +
              "(select sxContact from lnkOrganizationContact " +
              "where lnkOrganizationContact.sxOrganization in " +
              orgList + ")";
          adapter.SelectCommand = new SqlCommand(query, Connectivity.Connection());
          adapter.Fill(ds.tblContact);
  
          //lnkOrganizationContact
          query = "select * from lnkOrganizationContact where lnkOrganizationContact.sxOrganization in " + orgList;
          adapter.SelectCommand = new SqlCommand(query, Connectivity.Connection());
          adapter.Fill(ds.lnkOrganizationContact);
      } catch(Exception ex) {
          Debug.WriteLine(ex.Message);
      }
  
      ds.WriteXml(filename, XmlWriteMode.IgnoreSchema);
  }
  
  </code></pre>
  
  <p>As you can see, I'm reusing the same TableAdapeter over and over and the XML that is written at the end seems to look fine and nice, having a tag for each table, and within it a tag for each field, exactly what I want, and to be able to merge it back in.  Here is the import code:</p>
  
  <pre><code>
  
  //reads data from an xml file and merges it into existing db
  public static void ImportData(string data)
  {
      try
      {
  
      MyGeneratedDataSet newData = new MyGeneratedDataSet();
  
      StreamWriter sw = new StreamWriter("newdata.xml", false);
      Debug.WriteLine(data);
      sw.Write(data);
      sw.Close();
      XmlTextReader reader = new XmlTextReader(new MemoryStream(ASCIIEncoding.Default.GetBytes(data)));
      newData.ReadXml("newData.xml");
  
      MyGeneratedDataSet currentData = new MyGeneratedDataSet();
  
      //tblOrganization
      SqlDataAdapter tblOrganizationDataAdapter = new SqlDataAdapter("select * from tblOrganization", Connectivity.Connection());
      SqlCommandBuilder tblOrganizationCommandBuilder = new SqlCommandBuilder(tblOrganizationDataAdapter);
      tblOrganizationDataAdapter.Fill(currentData, "tblOrganization");
  
      //tblContact
      SqlDataAdapter tblContactDataAdapter = new SqlDataAdapter("select * from tblContact", Connectivity.Connection());
      SqlCommandBuilder tblContactCommandBuilder = new SqlCommandBuilder(tblContactDataAdapter);
      tblContactDataAdapter.Fill(currentData, "tblContact");
  
      //tblCategory
      SqlDataAdapter tblCategoryDataAdapter = new SqlDataAdapter("select * from tblCategory", Connectivity.Connection());
      SqlCommandBuilder tblCategoryCommandBuilder = new SqlCommandBuilder(tblCategoryDataAdapter);
      tblCategoryDataAdapter.Fill(currentData, "tblCategory");
  
      //lnkOrganizationCategory
      SqlDataAdapter lnkOrganizationCategoryDataAdapter = new SqlDataAdapter("select * from lnkOrganizationCategory", Connectivity.Connection());
      SqlCommandBuilder lnkOrganizationCategoryCommandBuilder = new SqlCommandBuilder(lnkOrganizationCategoryDataAdapter);
      lnkOrganizationCategoryDataAdapter.Fill(currentData, "lnkOrganizationCategory");
  
      //lnkOrganizationContact
      SqlDataAdapter lnkOrganizationContactDataAdapter = new SqlDataAdapter("select * from lnkOrganizationContact", Connectivity.Connection());
      SqlCommandBuilder lnkOrganizationContactCommandBuilder = new SqlCommandBuilder(lnkOrganizationContactDataAdapter);
      lnkOrganizationContactDataAdapter.Fill(currentData, "lnkOrganizationContact");
  
      Debug.WriteLine(tblOrganizationDataAdapter.SelectCommand.CommandText);
      Debug.WriteLine(tblOrganizationDataAdapter.UpdateCommand.CommandText);
  
      currentData.Merge(newData);
  
      tblOrganizationDataAdapter.Update(currentData);
      tblContactDataAdapter.Update(currentData);
      tblCategoryDataAdapter.Update(currentData);
      lnkOrganizationCategoryDataAdapter.Update(currentData);
      lnkOrganizationContactDataAdapter.Update(currentData);
  
  
      } catch (Exception ex) {
          Debug.WriteLine(ex.Message);
      }
  
  }
  
  </code></pre>
  
  <p>At the moment, the Debug.WriteLine near the end of the import function shows that the UpdateCommand of the tblOrganizationTableAdapter is null.  The visual designer had told me that it created that for, though if all I have to do is create it, sure, I'll do that, but I've rewritten this so many times now (and there's lots more tables than this), and I still don't understand what's going on.  How SHOULD I be doing this?!</p>
  
  <p>THANK YOU SO MUCH!
  Joshua</p>
  
  <hr>
  
  <p>This code almost works.  It now doesn't fail anymore, however, I end up with all duplicate rows, instead of updating via the primary key comparison!</p>