How do I compare 2 similar tables in 2 different MS Access databases in C#?

Question

I was asked to create a desktop tool to compare an older access DB with a newer "updated" access DB and create an excel file of all new or altered records.

the DB tables I need to compare are structured
Primary key | Description.

I created a simple Windows form that allows the user to select the old and new databases and I figured out how to open the DB's and run SQL queries against them however I am stuck on how to do the comparison without making thousands of SQL calls to each DB.

With SQL server and a Web server I would be done by now but creating this as a desktop application and MS Access has me a bit confused.

Answer 1

Are you stuck with the user's choice of technologies? If there's just one older db and one newer one to compare, you don't need a desktop application. You'll need it if there's multiple older and newer ones.

You could import the Access dbs to SQL Server, if that helps.

Also consider creating a third Access db with links to the tables in the first two. Then you have both data sets in one place and you can write queries to compare them.

The idea's the same whether you bring them into memory or SQL Server, just easier to keep track of the queries on a back end than in code.

Answer 2

If you are using .Net Framework 3.5 or higher, one way is to create two lists and put the records from the two db's to compare in those two lists. After that, you can use the Linq Except and Union extension methods to quickly find the differences in the two sets. See here for these extension methods.

Answer 3

This is exactly the same in Access as it would be in SQL Server. Just write a query that does the comparison for you:

select t1.key, t1.description, t2.description
 from t1
   full outer join t2 on t1.key=t2.key
 where t1.description <> t2.description
    or t1.description is null
    or t2.description is null;

That will produce a list of all records that have identical keys but differing values. Then just dump the recordset to a file or a gridview or something.

You could also do the UI for this in pure MS Access, and skip the C# part entirely. Then it would be very fast to do, and MS Access is pretty good for building native UIs.

Answer 4

Here is how I ended up doing it.

I recieved the Old and New database file information from users in a simple form. Then I opend the Olddatabase and added each record to a Dictionary "dictionaryOld" useing code from Ahmad Mageed's answer. I then created a Second Dictionary "dictionaryNew" to hold the records that were new or changed. I iterated through the New Database and compared each record to dictionaryOld adding the records that did not exist or were changed.

I am sure there are much better ways of doing this but it looks like it works for what I need thank you all for pointing me in a direction!

Now off to figureout how to write this out to a file and catch errors etc...

Here is the code

    Removed source, new and improved version comming....