Compare values from two different database: MySQL and Oracle

Question

Hi I am working on a project that I have to write an application using c# to compare data from two databases. One of the database is MySQL and the other is Oracle. I know that I can use ODBC to establish separate connections with the two databases but what is the best way to compare the values from two database connection ? Is there something as simple as this? I am a beginner in writing c#. if (DatabaseA.column1 = DatabaseB.column1) then do this; end if; Hope to hear from you thanks!

Suthish Nair · Answer

As i suggested DBLink is the best way for you because i experienced the same. Am out of this discussion :)

Richard · Answer

no they are basically two different database storing data differently tables/columns/data format are all different i have to migrate the data under certain rules that's why the comparison I have to make for each and every filed for each and every record to see for example if that's a match if the oracle database value is null....etc

Sam Hobbs · Answer

Aha! So it is not a simple comparison. You say that there is at least one field that is in a different format in each of the databases. That makes a significant difference. Leaving out details such as that often result in suggestions that don't work. Is there a primary key that can be used to match the two? Is that data in the same format or do you need to reformat the primary key in one table to compare it to the other table?

Richard · Answer

it's more like there are 2 database and I have to synchronize the data from MySQL to Oracle but the data on MySQL is in a different representation (i.e. P1234) than the data in Oracle (i.e. 1234) though it means the same thing to us. So I have to do a comparison and check if the data matches (on many but not all fields) and make sure the data migrate from MySQL to Oracle under certain rules. So here I want to find the easiest way to compare and update if I can compare the two database values directly using just the ado .net/odbc connection then I don't have to fetch the data into dataset and put them in variables then compare. Thanks!

Suthish Nair · Answer

I think you can. Only you need two different adapters (Oracle & SQL) and later fill into single DataSet. I didnt tested and dont know will the Dataset tables will get overwritted.

Sam Hobbs · Answer

First in this context the term compare is imprecise. Must you compare every field and are the tables unequal if there is one field unequal? If not then the criteria needs to be defined. No you cannot have both tables in one DataSet since they are different databases. As far as I know it is not possible to use different database formats in one SQL and the closest thing there is to a direct comparison is to use SQL. In other words if both tables were in SQL Server maybe even different SQL Server databases I think it is possible to use SQL to compare but otherwise I think it is not possible. I don't have a lot of experience with that so I might be wrong.

Richard · Answer

thanks Sam I have read the ADO .net and see that can be useful. Correct me if I am wrong if I were to use ADO .net and TableAdapters I will have to use two dataset to fetch/hold the data within the application and compare. i.e. if (dataset1.row1.column1) == (dataset2.row1.column1) do this; end if; Is there any way that I can do direct comparison of the database without moving the data values in the dataset/application? i.e. i.e. if (connection1.table1.row1.column1) ==(connection2.table2.row2.column2) do this; end if;

Richard · Answer

thanks for the suggestion but the requirement is to code this in c# and make it an application so that anyone can do a 1 click comparison without knowing how the back works.

Suthish Nair · Answer

I will suggest to create a DBLink and access your destination DB from source. For example: You want to access Oracle DB from SQL Server then create the DBLink in SQL Server with Oracle DB connection details. Remember to open Oracle ports from SQL Server. Then create an OpenQuery to access the DBLink tables join both DB table columns and compare your values. Instead of doing it on code behind. This will help to debugg execute your queries before publishing your project.

Sam Hobbs · Answer

ADO is higher-level than ODBC. Look in this web site for articles about ADO .Net. I suggest using TableAdapters which use ADO .Net and makes things easier.