PC Review


Reply
Thread Tools Rate Thread

Database Sync Question

 
 
wackyphill@yahoo.com
Guest
Posts: n/a
 
      5th Oct 2007
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?

 
Reply With Quote
 
 
 
 
Marc Gravell
Guest
Posts: n/a
 
      5th Oct 2007
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

 
Reply With Quote
 
 
 
 
wackyphill@yahoo.com
Guest
Posts: n/a
 
      5th Oct 2007
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.


 
Reply With Quote
 
Marc Gravell
Guest
Posts: n/a
 
      5th Oct 2007
> 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

 
Reply With Quote
 
wackyphill@yahoo.com
Guest
Posts: n/a
 
      5th Oct 2007

> 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.

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sync dataBase with C# - sync betweeb Outlook, Nokia and C# applica Gidi Microsoft C# .NET 0 3rd Feb 2010 10:40 AM
LINQ SQL DataBase Diagram to <DataBase Name>DataClasses.dbml sync viepia@nospam.com Microsoft C# .NET 2 20th Feb 2008 11:51 AM
Database Business Layer and Database Sync Design morleyc@gmail.com Microsoft C# .NET 8 19th May 2007 03:17 AM
Offline sync still trying to sync with old server Nick Windows XP General 1 20th Jan 2005 11:06 AM
Cannot get palm m515 to hot sync or use infrared hot sync =?Utf-8?B?U2tpcA==?= Windows XP Help 1 7th Feb 2004 10:50 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:59 AM.