Database Sync Question

Discussion in 'Microsoft C# .NET' started by wackyphill@yahoo.com, Oct 5, 2007.

  1. Guest

    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?
     
    , Oct 5, 2007
    #1
    1. Advertisements

  2. Marc Gravell Guest

    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
     
    Marc Gravell, Oct 5, 2007
    #2
    1. Advertisements

  3. Guest

    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.
     
    , Oct 5, 2007
    #3
  4. Marc Gravell Guest

    > 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
     
    Marc Gravell, Oct 5, 2007
    #4
  5. Guest


    > 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.
     
    , Oct 5, 2007
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. newbie

    Collection of objects to sync with database

    newbie, Mar 5, 2004, in forum: Microsoft C# .NET
    Replies:
    0
    Views:
    143
    newbie
    Mar 5, 2004
  2. Guest
    Replies:
    5
    Views:
    1,456
    Guest
    Jan 24, 2005
  3. Replies:
    8
    Views:
    383
    =?ISO-8859-1?Q?Arne_Vajh=F8j?=
    May 19, 2007
  4. Replies:
    2
    Views:
    471
  5. Gidi
    Replies:
    0
    Views:
    507
Loading...

Share This Page