How do I transfer data from one database to another using a DataSet?

Question

As usual, some background information first:

Database A (Access database) - Holds a table that has information I need from only two columns. The information from these two columns is needed for an application that will be used by people that cannot access database A.

Database B (Access database) - Holds a table that contains only two columns (mirrors to what we need from table A). Database B is accessible to all users of the application. One issue is that on of the column names is not the same as it is in the table from Database A.

What I need to do is transfer the necessary data via a utility that will run automatically, say once a week (the two databases don't need to be totally in sync, just close). The transfer utility will be run from a user account that has access to both databases (obviously).

Here's the approach I've taken (again if there is a better way, please suggest away):

1. Grab the data from database A. It is only the two columns from the necessary table.

2. Write the data out to [tablename].txt file using a DataReader object and WriterStream object. I've done this so I can use a schema.ini file and force the data columns to have the same name as they will be in Database B.

3. Create a DataSet object, containing a DataTable that mirrors the table from Database B.

4. Suck the information from the .txt file into the DataTable using the Microsoft.Jet.OLEDB.4.0 provider with extended properties of text, hdr=yes and fmt=delimited (to match how I have the schema.ini file setup and the .txt file setup). I'm using a DataAdapter to fill the DataTable.

5. Create another DataSet object, containing a DataTable that mirrors the table from Database B.

6. Suck in the information from Database B so that it contains all the current data found in the table that needs to be updated from Database A. Again I'm using a DataAdapter to fill this DataTable (a different one from Step 5, since they are both using different data sources).

7. Merge the DataTable that holds the data from Database A (or the .txt file, technically).

8. Update Database B's table with the changes.

I've written update, delete and insert commands manually for the DataAdapter that is repsonsible for talking to Database B. However, this logic is never used because the DataSet-From-Database-B.Merge(Dataset-From-TxtFile[tableName]) doesn't flip the HasChanges flag. This means the DataSet-From-Database-B.Update doesn't fire any of the commands.

So is there any way I can get the data from DataSet-From-TxtFile to merge and apply to Database B using the method I'm using? Am I missing a crucial step here?

I know I could always delete all the records from Database B's table and then just insert all the records from the text file (even if I had to loop through each record in the DataSet and apply row.SetAdded to ensure it triggers the HasChanges flag), but I'd rather have it apply ONLY the changes each time.

I'm using c# and the 2.0 Framework (which I realize means I can use DataTables and TableAdapters instead of DataSets and DataAdapters since I'm only dealing with a single table, but anyway).

TIA

Answer 1

Why not simply use a data reader, and loop through the records, doing manual inserts if needed into database B?

Rather than working with datasets, merging, etc..

Answer 2

Setting aside for a moment that I would use SQLServer and only have a single table with multiple views controlling who could see what information in it to avoid the whole synchronization problem...

I think that @Mitchel is correct here. Just write a program that connects to both databases, load A table and B table, respectively. Then, for each element (column pair) in A make sure it is in B. If not, then insert it in B. Then, for each element in B, make sure it is in A. If not, then remove it from B. The save B. I don't see the need to go to a file first.

Pseudocode:

DataTable A = load table from A
DataTable B = load table from B

foreach row in A
   col1 = row[col1]
   col2 = row[col2]
   matchRow = B.select( "col1 = " + col1 + " and col2 = " + col2)
   if not matchRow exists
      add new row to B with col1,col2
   end
end

foreach row in B
   col1 = row[col1]
   col2 = row[col2]
   matchRow = A.select( "col1 = " + col1 + " and col2 = " + col2)
   if not matchRow exists
      remove row from B
   end
end

update B

Answer 3

Hi there,

For those interested in transferring data from one database to another, check out the Data Moving Tool at http://www.sersoftware.com/prod/data-moving-tool-overview.php

Regards JB