CODEWITHSUNDEEP
c#phpdatabasemysql-8.0
ropenrom24
ropenrom24

Reputation: 545

Database Sync - Local and Online without Replication

So I have this code that checks if new data is added to online database by comparing the rows of online and local database. If new data is found it inserts the new data to local database.

public class Reservation
{
    public string res_no { get; set; }
    public string mem_fname { get; set; }
}

My Code :

private async void updateDineList()
    {
            DBconnector.OpenConnection();

            //Gets data from online database
            HttpClient client = new HttpClient();
            var response = await client.GetStringAsync("http://example.com/Reservation/view_pending_reservation");
            var persons = JsonConvert.DeserializeObject<List<Reservation>>(response);

            //Gets data from Local database
            string string_reservation = "SELECT res_no,mem_fname  FROM res_no WHERE res_status='pending';";
            DataTable reservation_table = new DataTable();
            MySqlDataAdapter adapter_reservartion = new MySqlDataAdapter(string_reservation, DBconnector.Connection);
            adapter_reservartion.Fill(reservation_table);

            //Gets the row of each table
            int local = reservation_table.Rows.Count;
            int online = persons.Count;

            //Compares rows of online and local database
            if (local < online)
            {   
            //if the rows of online database is greater than local database
            //inserts the new data from local database
                string Command_membership = "INSERT INTO reservation_details (res_no,mem_fname) VALUES (@res_no, @mem_fname);";                    
                for (int i = local; i < online; i++)
                {
                    //inserts new data from online to local database
                    using (MySqlCommand myCmd = new MySqlCommand(Command_membership, DBconnector.Connection))
                    {
                        myCmd.CommandType = CommandType.Text;
                        myCmd.Parameters.AddWithValue("@res_no", persons[i].res_no);
                        myCmd.Parameters.AddWithValue("@mem_fname", persons[i].mem_fname);
                        myCmd.ExecuteNonQuery();
                    }
                }
                MessageBox.Show("New Records Found");
            }
            else
            {
                MessageBox.Show("No new Records");
            }

            DBconnector.Connection.Close();
    }

So my question is there any problem could occur with this code, it works fine but is there any way to improve this. I know MySQL replication is better but I am only using free Web Hosting with few MySQL privileges.

Upvotes: 0

Views: 125

Answers (1)

TomC
TomC

Reputation: 2814

The clear improvement is not to create a new command for every row. You should either create the command and parameters once and then set the parameters and call for each row, or better still package the set of updates into a single structure, like and xml string, and then pass the whole lot to the database via a stored procedure call.

Other probably problematic issue is that you are checking purely based on row counts. Don't know if that is valid in your scenario but it sounds dangerous. What if rows are deleted? or is that not possible in your scenario. Some other way of checking last updates would probably be preferable.

Without more context that's about all I can see.

Upvotes: 1

Related Questions