lost with DataSets and merging in C#

Question

NEWEST EDIT!!!

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).

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.

Here is what I'm doing to export the data to xml:

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);
}

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:

//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);
    }

}

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?!

THANK YOU SO MUCH! Joshua

Answer 1

update would need to be called at some point:

adapter.Update(currentData, "table1")

in general its probably easier to merge 2 tables directly in sql, but your app may need to process xml files..

examples of using Merge/Update:

http://msdn.microsoft.com/en-us/library/aa325628%28VS.71%29.aspx

in t-sql, you can do this in one statement with MERGE:

http://technet.microsoft.com/en-us/library/bb510625.aspx

tweak it to suit the conditions when you want the row changed / updated

Answer 2

Blah:

• Load your dataset from the xml rows.
• Make sure the RowState flag is correct for each row:
  • Unchanged, Modified, Added, or Deleted
• Don't call AcceptChanges(), it sets the row sate for every row to Unchanged and removes rows marked Deleted(). This is done internally by...

Code:

using (SqlDataAdapter dap = new SqlDataAdapater(myConnection, "SELECT * FROM MyTable"))
    dap.Update(myDataTable)

Answer 3

What you are doing sounds right. I'm not sure what your exact problem is, but here is a breakdown of the flow:

1. Load new data into a DataSet (like you've done)
2. Get current data from database into DataSet (like you've done)
3. Call CurrentData.Merge(NewData) (like you've done)
4. Save the CurrentData back to the database (I don't see this part)

When you perform step #4, the table adapters look at each table in the DataSet and find out which rows have changed. It knows because each DataRow in each DataTable will have its DataRowState set to Added, Modified or Deleted as a result of your call to Merge(). The table adapter then performs the requested change in the underlying database and sets the DataRowState to Unchanged for the DataRow (except in the case of Deleted, of course). When this process completes, the underlying database should have all the changes in it that the NewData dataset contained.

Answer 4

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!