 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / Languages / C# / October 2007Database Sync Question
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/library/system.data.sqlclient.sqldependency.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. Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.