What a good waste of half of a day of my life.  Actually, it wasn't even that good - but it was wasted.

I am working on a utility that includes a function to move data between databases so I need to copy data from one DataSet to another then merge that data into an existing set of DataTables and Update the physical database with the changes.

Believe it or not, this is not the simple process that you would think.  I ran into all sorts of strange issues that either generated an error or didn't work ( no database update ).

Background

If you are in the practice of retrieving multiple tables using a single SQL Adapter ( Or OLE, for that matter ), you need to take into account the possibility that you may need to update those tables at some point in the future.  I discovered, thanks to Chris' article, that the DataSet.Merge and DataAdapter.Update do not function very well when you have retrieved multiple tables using a single query using multiple selects, like this: "SELECT * FROM Customers; SELECT * FROM Orders"

Issues

There were at least three separate issues that I ran into:

1) RowState not being set in such a way that the Update command thought there was anything to do.

2) The Adapter getting very confused about which table we were actually trying to update.

3) My physical database tables needed to have a Primary Key defined.

What made this so very complicated, at least to me, was the fact that there is so little code involved here, really just a hand-full of lines, so which one was causing the exact issue or issues?

Solutions

After wasting most of the afternoon on the code and Googling for what seemed like 200 pages and posts, I finally tracked down the following article by Chris Pietschmann:

Fill DataSet with multiple Tables and update them with DataAdapter

The solution, as Chris mentions, is to create a function that will retrieve a single table then update the data in that table alone.  That was indeed the solution.  Here is the function I created to do the work ( exception handling removed for readability ):

1.
public int CreateMigrateDataset(OleDbConnection dbConnDest, ref DataSet ds, int ID)


2.
{


3.
    string sourceTableName = TABLE_NAMES[ID];


4.
    string destTableName = MIGRATE_TABLE_NAMES[ID];


5.
    DataSet dsDest = new DataSet();


6.
    string queryString2 = String.Format("select * from {0}; ", destTableName);


7.
 


8.
    OleDbDataAdapter daDest = new OleDbDataAdapter();


9.
    OleDbCommandBuilder builder = new OleDbCommandBuilder(daDest);


10.
 


11.
    daDest.AcceptChangesDuringFill = false;


12.
    daDest.SelectCommand = new OleDbCommand(queryString2, dbConnDest);


13.
    daDest.InsertCommand = builder.GetInsertCommand();


14.
    daDest.UpdateCommand = builder.GetUpdateCommand();


15.
    daDest.TableMappings.Add("Table", destTableName);


16.
 


17.
    daDest.Fill(dsDest);


18.
    DataTable dtFolder1 = ds.Tables[sourceTableName].Copy();


19.
    dtFolder1.TableName = destTableName;


20.
    dsDest.Merge(dtFolder1);


21.
 


22.
    int updateCount = daDest.Update(dsDest, destTableName);


23.
 


24.
    return updateCount;


25.
}

How It Works

One of the most important first steps is actually in the function that retrieves my original DataSet.  You need to set the following DataAdapter property:

daSource.AcceptChangesDuringFill = false;

This instructs the DataAdapter not to automatically perform an AcceptChanges command after the DataSet has been filled.  This is very important because we need the RowState for each of the rows in the DataTable to remain as they were.

On Line 18, you will notice that we are creating a new table called dtFolder1 and populating the table's Schema and Data by performing a Copy command.  On Line 20, we are performing a Merge command to merge the Destination DataTable data with that of the Source DataTable.  If we had not set the AcceptChangesDuringFill to false, the RowState for the copied records would have been set to Unchanged and we needed each RowState to be set to Added.

So why is this so important?  Well, mostly because the Update command we perform on Line 22 looks at the RowState for each row in the DataTable and only updates the physical database with rows that are in some way different or changed.  This was what was causing much of my waste of time: the database wasn't getting updated, and I found no indication as to why this was happening.

Moving Data Alternative Method

A secondary method for moving data between DataTables in separate DataSets is to do it Row by Row, as in the following example:

1.
DataTable dtFolder1 = ds.Tables[sourceTableName].Clone();


2.
dtFolder1.TableName = destTableName;


3.
 


4.
foreach (DataRow oRow in ds.Tables[sourceTableName].Rows)


5.
{


6.
    DataRow newRow = dtFolder1.NewRow();


7.
    newRow.ItemArray = oRow.ItemArray;


8.
    dtFolder1.Rows.Add(newRow);


9.
}


10.
 


11.
dsDest.Merge(dtFolder1);


12.
 

In this example we're using the Clone command to clone the Schema from the Source DataTable and moving the data, one row at a time, to the Destination DataTable.  Adding rows to the Destination DataTable automatically sets the RowState to Added so the Update command will treat them as new rows and put them into the database.

Other Notes on the Code

I pass in a reference to my Source DataSet when invoking the function.

Line 15 create a table name mapping between the default table, called "Table" to the name of my destination table.  In this application, my source and destination table names are different, so I must rename the table after it is copied, which I do on Line 19. If your table names are the same, you may skip this step.

Lines 13 and 14 will instruct the Adapter to dynamically create Insert and Update commands required for this DataTable.

Note: You must have a Primary Key defined for your database table. The Primary Key is used by the DataAdapter to locate records for update and/or insert. If you don't have a Primary Key, you will get an error message stating the case and aborting your Update operation.

Final Note

I'm writing with VS 2003 and .NET 1.1 so I'm not sure if any of this has been corrected or changed in .NET 2.0.