3 Replies - 19365 Views - Last Post: 06 December 2009 - 11:54 AM Rate Topic: -----

#1 Brian2sos   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 28-October 09

Local and remote database sync

Post icon  Posted 05 December 2009 - 07:26 PM

Hey guys,

I'm posting this in the vb.net as I will be using visual studio to perform the task. So here is what I am trying to achieve or at least achieve it more efficiently than what I am doing now.

So I have two identical database setups, both with a table , let call it table tblUsers. The local database is edited through vb.net and is a Access database. The second database is hosted on a remote server and is a MySQL databse.

Currently I have no problem connecting and editing either database through vb.net. I'm using OLE DB to connect and handle the Access database and ODBC 5.1 to connect and handle the MySQL database.

Here is what I would like to do:

User opens the program

Presses the "update local" button

The program updates the local database to whatever the remote database is

The user uses the program to edit fields in the local database

When done, the user presses the "Update remote" button and the remote database fields that have been edited with the
program in the local database will be updated to the remote database.



Currently, the only way for me to do this is to go line by line in a for next and use a command builder to compare each field and update that way. But there has to be an easier way? Any ideas?

Is This A Good Question/Topic? 0
  • +

Replies To: Local and remote database sync

#2 Martyr2   User is offline

  • Programming Theoretician
  • member icon

Reputation: 5296
  • View blog
  • Posts: 14,142
  • Joined: 18-April 07

Re: Local and remote database sync

Posted 05 December 2009 - 08:49 PM

You do realize that .NET can store tables and such as local copies within the .NET framework right? When you query a table and get some results, internally .NET builds the results as a table and holds it in memory. When you make changes to that table, you are making local changes to the dataset which are then propagated back to the remote server at a time of your choosing. One of .NET's main design goals was to essentially replicate database structures locally so that the programmer can make multiple changes without having it eating up bandwidth and connection resources. It also can minimize multiple users from locking up tables and records from other users. Each machine makes changes to local copies and then when ready can save all changes at once using a single connection and very fast. A "get in and get out" mentality.

Now the only reason I can think of having the local copy is if you wanted to make changes over the periods of days, shutdowns, executions of the program and then change the database later. However, if you are just trying to keep things in sync when the user is making edits, there is no reason to maintain a second database locally when you can actually construct the tables and records in memory, make the changes and update the remote server on save.

The reason I am suggesting this rather than syncing the two is because each time you try and do the copy you are destroying all objects and rebuilding them which is inefficient and can be somewhat costly in the terms of time and resources. If you are looking to get this query, manipulate and then save method, I think that this can all be done with .NET and the use of datatables/datasets.

:)
Was This Post Helpful? 0
  • +
  • -

#3 Brian2sos   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 28-October 09

Re: Local and remote database sync

Posted 05 December 2009 - 08:59 PM

View PostMartyr2, on 5 Dec, 2009 - 07:49 PM, said:

You do realize that .NET can store tables and such as local copies within the .NET framework right?




Martyr2,

Thanks for the reply. Yes I understand what you are suggesting and this is how the program operated initially. The reason for holding the values in a local database is because the computer that the program is installed on may not be able to access the remote database for several weeks at a time. Also, as you said, the computer will be shutdown and restarted several times during that time period.

I am sure what I am trying to do could be done with datasets etc., but everything I try to this point simply rewrites one database or the other. Aside from the current method I am using which involves searching the database for each value and updating each field. This works but seem inefficient and "sloppy" but it works I guess.

This post has been edited by Brian2sos: 05 December 2009 - 09:02 PM

Was This Post Helpful? 0
  • +
  • -

#4 oldSwede   User is offline

  • D.I.C Regular
  • member icon

Reputation: 4
  • View blog
  • Posts: 464
  • Joined: 08-January 16

Re: Local and remote database sync

Posted 06 December 2009 - 11:54 AM

Hi!

If I understood your problem correctly one of the following should solve your problem:

1) Add an extra boolean/bit field to the local database. Fileld = true meaning the record have been changed. You can then SELECT all records WHERE chengedField=TRUE and write those to the remote database.

2) Save the updated records as new records in a different table or database and update from those. This has the advantage that you can compare your saved "original" records to the corresponding records in the remote database. Hereby ensuring that no one has updated the remote while you have been updating your local copy.

E.g:
Remote LocalOriginal LocalUpdate
18		 18			   23				 Ok to update
21		 19			   28				 Can not be updated.



Well, I hope my ramblings are understandable and of use.

Regards
/Jens
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1