How to sync local server database to cloud server database

dip s

6y4.2k.NET

Hello,

We have a web application. Front end is in angular 8 and backend is in asp.net mvc 5 and database is mysql.

Many of our clients have internet problem. So, we are thinking like, we will save/update all transactions on local server and after some time interval we will update or sync that local server DB to cloud server DB.

We will check which data is changed on local server and we will update only that data on cloud server.

We have almost 40 tables in mysql database. Can we sync whole local database with cloud server database?

Does anyone has any idea about how to do this in C#.net?

Thank you.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

dip s6y ago
@laxmidhar sahoo Thank you very much for your reply
Laxmidhar Sahoo6y ago
1. download download mysqlclient from net and add reference.
3. using MySql.Data.MySqlClient;
4. Using System.Data;
9. private void button1_Click(object sender, EventArgs e)
10. {
12. //tablesarray is the array of table names
14. for(int i =0;i< tablesarray.length;i++)
15. {
16. sendtocloud(tablesarray[i])
17. }
19. }
21. private void sendtocloud(string tablename)
22. {
23. //server = your cloud server ip according to place your user id ,password and database
25. string CloudconnetionString = "server=127.0.0.1;uid=root;" + "pwd=12345;database=test";
26. String LocalconnetionString = "server=localhost;database=testDB;uid=root;pwd=abc123;";
27. MySqlConnection cnn ;
29. try
30. {
33. MySqlConnection cnn = new MySqlConnection(LocalconnetionString);
35. string query = "select * from " + tablename + " where isuploaded = false ";
37. cnn.Open();
38. MySqlDataAdapter adr = new MySqlDataAdapter(query, cnn);
39. adr.SelectCommand.CommandType = CommandType.Text;
40. Dataset ds = new Dataset();
41. adr.Fill(ds); //opens and
42. cnn.Close();
44. Cloudcnn = new MySqlConnection(CloudconnetionString);
45. Cloudcnn.Open();
46. for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
47. {
48. string col1 = ds.Tables[0].Rows[i]["Column name"].ToString();
49. string col2 = ds.Tables[0].Rows[i]["Column name1"].ToString();
50. if (Cloudcnn.State != ConnectionState.Open)
51. {
52. Cloudcnn.Close();
53. Cloudcnn.Open();
54. }
56. //here the inser statemnet to cloud table and use the above column vales
57. string insertquery = "inser into " + tablename + " values(" + col1 + "," + col2.......... + ")";
58. MySqlCommand cmd = new MySqlCommand(insertquery , mcon);
59. cmd.ExecuteNonQuery();
60. }
62. Cloudcnn.Close();
63. }
64. catch (Exception ex)
65. {
66. MessageBox.Show("Can not open connection ! ");
67. }
70. }

dip s6y ago

@laxmidhar sahoo thank you for your reply. We are going to do the same thing that you have suggested. but how to sync or upload data from local to cloud server?

Laxmidhar Sahoo6y ago

For each table place a extra column isuploaded as boolen (Bit) for local.

Bydefault the value is 0(False. when upload the value convert to 1(True).

Select the records where isuploaded = false and send to cloud .Imidiate turns the value of isuploaded = true

for those records