Author Topic: Keep the same ID in Database synch  (Read 846 times)

JackIlPazzo

  • Newbie
  • *
  • Posts: 1
  • Karma: +0/-0
    • View Profile
Keep the same ID in Database synch
« on: December 25, 2015, 07:47:36 PM »
I've created a web scheduler that allow my customers to take appointments with my company, all working well. Now I've another application in vb.net that doing the same with other functions for my providers, this application is used only by my providers, so in my own company and not to my customers.
Sorry for the word around. Anyway, what I'm trying to do is create a class in vb.net that synchronize the client data to the web database. Both db are MySql, so I take an appointment in my client app and my code executed a query that add the same record to the online database.
Here no problem, all working well, infact I've created two string connection one for client and another for the web.
Now, the big problem is that if I create eg. an appointment in my client app that have ID (auto_increment structure) 5, the query that add this record also in the web database insert a different ID. This is a problem for me 'cause I can't recognize the specific record in the future.
Another problem is the creation of record in web database, infact If I add the record (in local database) that have ID 5 I must check if the ID already exists online...

More details:

Currently the two databases have on each record the field lastUpdated, this field helps me to understand when a particular record is updated.

I would like to figure out how I can achieve this in vb.net, a system that circles the conflicts between the two databases and update the corresponding database, eg:

Local database:

Quote
| id | Name  | lastUpdated
  5    John    19/12/2015 17:32
  6    Denis   18/12/2015 16:33

Web database:

Quote
| id | Name  | lastUpdated
  5    Triss   19/12/2015 18:32
  6    Denis   18/12/2015 16:33

As you can see the local database contains a record date with id 5 that must be updated from the web database.

Has someone worked on a similar thing? Could suggest the best way how to do this?

I also wanted to ask you something about adding data. Especially when I create a record from client I perform a task that I automatically updates the records online, but it happens sometimes that there is no connection.
I need something with a thread that regularly check which fields are added or missing between the two databases and identify which database needs adding or updating a record.

I think anyway, that lastUpdated field isn't a good idea, infact if two users update a record in the same time all will going to down.

Any awesome ideas?

mkaatr

  • Administrator
  • Full Member
  • *****
  • Posts: 115
  • Karma: +1/-0
    • View Profile
Re: Keep the same ID in Database synch
« Reply #1 on: February 21, 2016, 01:37:55 PM »
I've created a web scheduler that allow my customers to take appointments with my company, all working well. Now I've another application in vb.net that doing the same with other functions for my providers, this application is used only by my providers, so in my own company and not to my customers.
Sorry for the word around. Anyway, what I'm trying to do is create a class in vb.net that synchronize the client data to the web database. Both db are MySql, so I take an appointment in my client app and my code executed a query that add the same record to the online database.
Here no problem, all working well, infact I've created two string connection one for client and another for the web.
Now, the big problem is that if I create eg. an appointment in my client app that have ID (auto_increment structure) 5, the query that add this record also in the web database insert a different ID. This is a problem for me 'cause I can't recognize the specific record in the future.
Another problem is the creation of record in web database, infact If I add the record (in local database) that have ID 5 I must check if the ID already exists online...

More details:

Currently the two databases have on each record the field lastUpdated, this field helps me to understand when a particular record is updated.

I would like to figure out how I can achieve this in vb.net, a system that circles the conflicts between the two databases and update the corresponding database, eg:

Local database:

Quote
| id | Name  | lastUpdated
  5    John    19/12/2015 17:32
  6    Denis   18/12/2015 16:33

Web database:

Quote
| id | Name  | lastUpdated
  5    Triss   19/12/2015 18:32
  6    Denis   18/12/2015 16:33

As you can see the local database contains a record date with id 5 that must be updated from the web database.

Has someone worked on a similar thing? Could suggest the best way how to do this?

I also wanted to ask you something about adding data. Especially when I create a record from client I perform a task that I automatically updates the records online, but it happens sometimes that there is no connection.
I need something with a thread that regularly check which fields are added or missing between the two databases and identify which database needs adding or updating a record.

I think anyway, that lastUpdated field isn't a good idea, infact if two users update a record in the same time all will going to down.

Any awesome ideas?

Hello sir...

Sorry for the late response, I am being busy... probably you do not need an answer by now however I am leaving an answer...

The LastUpdated column is a good idea. The way I do this is as follow - assuming the update goes from client to server only - :

1- I do not connect to the database directly, but create a web service that receives an insert request and updates the DB. I do this for security reasons.
2- I add a column represents the row version number
3- I can ask the web server to return the max row version number
4- if the web max number is smaller than the client, then an update happened. So, with a simple query on version column, you could identify the rows that got updates. The only problem is row deleteion. So you need to keep track which rows are deleted. - BTW: make sure you update rows with lower id number then the higher ones.
5- As for the web db, there is no need to autoincrement column.
6- As for the update thread you should use windows service. This allows updating the web server even if the client is not running.

These are just the basic ideas, however if you decide to go with this, you need more details.

yours sincerely