Inefficiently Monitor SQL Database

[corteplaneta]

Hello,

I've been working on an application for my company, and implementing solutions to small problems along the way has caused me quite a bit of trouble.

I'm looking for a quick & dirty way to monitor a MSSQL database for changes. I need to take an image of this database table & at any later, arbitrary point in time, take another image & compare to determine the differences.

I know I could do this with the MSSQL event notification services, but this seems a bit too complex for the more simple application I'm creating.

What I was thinking about doing was creating a serializable object & a serializable strongly typed collection of this object & saving this to disk to store the current image of the db, then, using a timer, every 5 minutes querying the db for a new copy of this info, then comparing the two to see what's in the new db that wasn't in the original.

Can anyone think of a smarter or quicker way to implement this? Any comments would be greatly appreciated!

Thanks!!

-corteplaneta

 

[cjard]

Event notification seems harder than dumping the entire DB to disk every 5 minutes and running a compare routine?

Do you drink a lot of coffee?

 

[corteplaneta]

Haha, well the database table only has ~500 items, and after researching event notification I realized I'd have no idea where to start. If you have any tips I'd be more than glad to pursue that route!

And I don't drink coffee .

 

[cjard]

Coffee. Bilious substance. Good man.

Right.. Yep.. dumping it out once every five minutes is possible. I'd leave it in the db though, and every 5 minutes run:


SELECT * INTO myTableNow FROM myTable;

SELECT * FROM myTableNow
MINUS
SELECT * FROM myTable5minsAgo;

DROP TABLE myTable5minsAgo;

RENAME TABLE myTableNow TO myTable5minsAgo;


Note MINUS is Oracle's syntax. Something similar should be possible in SQLServer. If it won't do MINUS, then do:

Now LEFT JOIN 5mins ON Now.PK = 5Mins.PK WHERE 5mins.PK IS NULL

 

[MattP]

The T-SQL equivalent of MINUS is EXCEPT.

I think this is only available on SQL 2005+ though.

 

[corteplaneta]

Thanks MattP and cjard! I've got so little experience with SQL that I didn't even think of using SQL directly. Any idea the the LINQ to achieve the same end result?

I'm kind of irritated that LINQ doesn't allow me to query my strongly typed collections, either (isn't this ALL LINQ should work on!?!?! Strongly typed collections implement IEnumerable by default, do they not?)

 

[cjard]

Language Integrated Query - Wikipedia, the free encyclopedia seems to indicate it can? Are you using MyCollectableObjectCollection, or something like List<MyCollectableObject> ?