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?

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

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.

> and wouldn't work out well.

Which; SqlDependency? I agree... minimising the data you need at the
client, however, seems pretty OK ;-p

> And I want to minimize network traffic.

So why drag an entire table over? Keep the data at the server, and
just hold conversations with it - either to the database server
itself, or a web-service.

If you really must do it your way, then a delete trigger that pumps
into a "deleted" table may be of use - you can use the identity in the
table to get just the updates you haven't seen, and can purge any
values older than your supported range - perhaps half an hour. Then in
the same way that you query for updates/inserts, just query for
deletes too.

Marc

> Which; SqlDependency?
Yeah.

> > And I want to minimize network traffic.
>
> So why drag an entire table over? Keep the data at the server, and
> just hold conversations with it - either to the database server
> itself, or a web-service.

Well to keep the UI responsive my thought was to simply load the table
up front when the app starts. Then maybe save the table to disk when
it exits. Then the next time it starts, load from the saved file and
only pull down any changes from the DB which would likely be much
smaller than the whole table we had to download the first time.


> If you really must do it your way, then a delete trigger that pumps
> into a "deleted" table may be of use - you can use the identity in the
> table to get just the updates you haven't seen, and can purge any
> values older than your supported range - perhaps half an hour. Then in
> the same way that you query for updates/inserts, just query for
> deletes too.

Yeah thats an interesting idea.

It's not like I wouldn't rather query the DB everytime I needed
something. It would be easier I agree. Its just that the process is
very time consuming given the UI and the bandwidth available. So I was
just investigating other ideas. We're talking about maybe 4 tables w/
aprox 500 records in each. Not a massive DB. I felt caching it on the
client given their distance from the DB (over a WAN) might make sense.