Database Sync Question

[wackyphill]

I'd like to pull down a decent sized table into memory from a DB. I
want to store the table in a DataTable. This could take some time so
I'd like to do the full table only once.

And then when I wish to refresh my memory copy perform some type of
query that will only return the changes that have occured to the DB
copy so that I can update my memory copy.

I figure I can use a timestamp column to only return rows from the DB
that have a bigger timestamp than the largest timestamp in my memory
DataTable.

I think this would cover updates and Inserts that occured in the DB
since my last refresh.

My problem is how to tell if records had been deleted from the DB, so
I can delete them from my DataTable. All w/o incuring the time
necessary to do a full SELECT * of the DB table again.

Any ideas of how to do this? Or am I just talking crazy?

[Marc Gravell]

It sounds like you are basically talking about SqlDependency:

http://msdn2.microsoft.com/en-us/lib...ependency.aspx

However, in general I have found that the whole idea of holding a big
table in memory to be more trouble than it is worth; it simply doesn't
scale very well (either with data volume or computer numbers), and has
many concurrency issues. In short, that is already the job of the
database - why duplicate it? In most cases, try to work with the
minimum amount of data you need, under a pretty-stateless transaction
model. Of course, some scenarios do warrant large data size, but even
then I have rarely found DataTable the right solution.

Marc

[wackyphill]

No, this is for a client app and wouldn't work out well.

It's not that the table is huge but that the connection is very slow.
And I want to minimize network traffic.