As always, some history first:
Database A (Access database) - Holds a table which has information I want from only two posts. The data from all of these two posts is required to have an application that'll be utilized by people who cannot access database A.
Database B (Access database) - Holds a table that consists of only two posts (mirrors as to the we want from table A). Database B is obtainable to any or all customers from the application. One problem is the fact that on from the column names is different then it is incorporated in the table from Database A.
What I have to do is transfer the required data using a utility which will run instantly, say once per week (the 2 databases don't have to be totally synchronized, just close). The transfer utility is going to be run from the user account that can access both databases (clearly).
Here's the approach I have taken (again if there's an easy method, please suggest away):
Grab the information from database A. It is simply the 2 posts in the necessary table.
Write the information to [tablename].txt file utilizing a DataReader object and WriterStream object. I have carried this out in order to make use of a schema.ini file and pressure the information posts to achieve the same title as they'll be in Database B.
Produce a DataSet object, that contains a DataTable that mirrors the table from Database B.
Suck the data in the .txt file in to the DataTable while using Microsoft.Jet.OLEDB.4. provider with extended qualities of text, hdr=yes and fmt=delimited (to complement the way i possess the schema.ini file setup and also the .txt file setup). I am utilizing a DataAdapter to fill the DataTable.
Create another DataSet object, that contains a DataTable that mirrors the table from Database B.
Suck within the information from Database B to ensure that it consists of all of the current data based in the table that should be up-to-date from Database A. Again I am utilizing a DataAdapter to fill this DataTable (another from Step Five, being that they are both using different data sources).
Merge the DataTable store the data from Database A (or even the .txt file, technically).
Update Database B's table using the changes.
I have written update, remove and place instructions by hand for that DataAdapter that's repsonsible for speaking to Database B. However, this logic isn't used since the DataSet-From-Database-B.Merge(Dataset-From-TxtFile[tableName]) does not switch the HasChanges flag. What this means is the DataSet-From-Database-B.Update does not fire the instructions.
Same with there in whatever way I'm able to obtain the data from DataSet-From-TxtFile to merge and affect Database B while using method I am using? Shall We Be Held missing an important step here?
I understand I possibly could always remove all of the records from Database B's table after which just place all of the records in the text file (even when I needed to loop through each record within the DataSet and apply row.SetAdded to make sure it triggers the HasChanges flag), but I'd favour it apply Just the changes every time.
I am using c# and also the 2. Framework (that we realize means I'm able to use DataTables and TableAdapters rather than DataSets and DataAdapters since I am only handling a single table, but anyway).
Putting aside as it were which i would use SQLServer and just possess a single table with multiple sights controlling who often see what information inside it to prevent the entire synchronization problem...
I believe that @Mitchel is correct here. Just write a course that connects to both databases, load A table and B table, correspondingly. Then, for every element (column pair) inside a make certain it's in B. Otherwise, then place it in B. Then, for every aspect in B, make certain it's inside a. Otherwise, then take it out of B. The save B. I do not see the necessity to visit a file first.
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
Why don't you simply employ an information readers, and loop with the records, doing manual card inserts as needed into database B?
Instead of dealing with datasets, merging, etc..