How to compare two tables?

[Guest]

Hi, Our database is constantly updated (input data) from another DB, and
sometimes it crashes our ASP.NET applications. My boss told me to write a DB
utility app to check DB and make sure all apps are still running.

The first step of few checks is to compare current DB tables with
yesterday’s tables. I store yesterday’s tables at a XML file (DB is
maintained by another guy, and certainly they don’t want me to make back copy
for few tables in DB everyday).

I thought it’s trivial, and just do loop and compare two XML DOM
(XmlDocument) objects and make a new one. But the algorithm is more complex
than I thought. The new rows can be inserted or deleted based on primary key.

So, my question is: Is there a sample code to compare two tables (either
DataTable in DataSet, or XmlDocument)?

Another question is: Can I set color for individual cell in DataGrid UI,
because I want to use different color to show new, deleted or modified field
dynamically?

Thanks
Shaw

 

[Guest]

Why not use OleDbConnection.GetOleDbSchemaTable, the info, can then be saved
to either XML or a temp table, you can then get updates for it the next day
and make a comparison.

I would do it that way, and even add a DATE field to it, to determine which
one is the more current one.

 

[Guest]

Thanks ae,

I think I am going to store tables in XML file, and put date on XML file
name. So I can let user to choose the XML file to compare, or maybe write a
function to determinate yesterday’s file name.

The real question is the algorithm to compare two table contents, either by
XML format or DataTable format since they are easy to convert each others. I
think, the comparison of two tables is a popular algorithm, and someone must
already implement it many times. Maybe I can borrow the code. If not, I have
to write my own code.

The another question is: How to show the difference? The easy way is to
color the individual row or filed. For ex, blue to inserted row, yellow for
deleted row, red for modified field (not whole column)… But, can we specify a
color for individual cell in datagrid control? I mean set color by code, can
we?.

Shaw

 

[Daniel Faensen]

Thanks ae,

I think I am going to store tables in XML file, and put date on XML
file name. So I can let user to choose the XML file to compare, or
maybe write a function to determinate yesterday’s file name.

The real question is the algorithm to compare two table contents,
either by XML format or DataTable format since they are easy to
convert each others. I think, the comparison of two tables is a
popular algorithm, and someone must already implement it many times.
Maybe I can borrow the code. If not, I have to write my own code.

The another question is: How to show the difference? The easy way is
to color the individual row or filed. For ex, blue to inserted row,
yellow for deleted row, red for modified field (not whole column)…
But, can we specify a color for individual cell in datagrid control? I
mean set color by code, can we?.

Shaw

Why do you want to re-invent the wheel?
There are tools available that show you the difference between XML
files.
I recommend XMLSpy by Altova. You can download a free evaluation copy
from Altova's web site.

Or try http://www.alphaworks.ibm.com/tech/xmldiffmerge

Or, if you don't want to install some software, try
http://apps.gotdotnet.com/xmltools/xmldiff/

 

[Guest]

First of all if you need to check if your app is running , comparing data
state is not the way to go, you need a monitoring app that mimic human
behaviour and check your app send you/page emails. You can write yours to
test http response of db connecting pages, or there are commercial ones.

Second you should back up your database everyday no matter what. I suggest
any dba against daily backups should be surgically removed.

Third storing data in XML just to compare versions is very unefficient. You
should dump data into another table and run a join select statement if you
have anything new in the production table.

Emre

 

[Guest]

Thanks all of you for helping me out. I agree almost all what you said. We
should guard DB more carefully, and should avoid the problem in the first
place. But unfortunately, I am a contract SW engineer and do what they told
me to do mostly, and arguer less to client, especially in bad job market.

There is no guarantee that the program still runs if something is changed,
either code or data. That’s what auto test software tries to do. Plus, you
need some manual tests to make sure the business logical still works.

I think, my goal for this project is to catch problems as many as possible.
First step is to determinate whether database is changed. Second step is to
run some stored procedure (SP) to see if it still works. We have hundreds
stored procedure, and most data through stored procedure, except few bogy
codes which use query directly.

I think about two level stored procedure tests. One is the general test.
That means to put some typical parameters and to run stored procedure to see
if they still work. Other is to do little bit smart test. According database
comparison, I use the changed data as parameters and run related SP. But it’s
also headache to supply right parameters.

Anyway, the more time, the better program. Thanks again for your helps.
Shaw