how to avoid duplicates in remote db while syncing with local database in c#

Question

I tried the code and failed because of the duplication of rows in remote database, while inserting from local database.

using System.Data.SQLite;
using System.Net.NetworkInformation;
using MySql.Data.MySqlClient;
namespace super
{
public partial class neworupload : Form
{
     SQLiteConnection con = new SQLiteConnection(@"connection string");
     MySqlConnection conn;
     String connstring;
     public neworupload()
     {
          InitializeComponent();
     }
     private void button1_Click(object sender, EventArgs e)
     {
          bool connection = NetworkInterface.GetIsNetworkAvailable();
          if (connection == true)
          {
               connstring = "SERVER=your ip;PORT=port;DATABASE=dbname;UID=userid;PASSWORD=password;SslMode = none;";
               try
               {
                    con.Open();
                    MessageBox.Show("Connection success");
                    String qu = "select * from users ";
                    SQLiteCommand cmd = new SQLiteCommand(qu, con);
                    SQLiteDataReader data = cmd.ExecuteReader();
                    while (data.Read())
                    {
                         String a = data["iduser"].ToString();
                         String b = data["name"].ToString();
                         String c = data["type"].ToString();
                         String d = data["email"].ToString();
                         String f = data["hash"].ToString();
                         Console.WriteLine(a);
                         Console.WriteLine(b);
                         Console.WriteLine(c);
                         Console.WriteLine(d);
                         Console.WriteLine(f);
                         conn = new MySqlConnection();
                         conn.ConnectionString = connstring;
                         conn.Open();
                         String qudemo = "select iduser from users";
                         Console.WriteLine("dddd" + qudemo);
                         String qu1 = "INSERT INTO users(iduser,name,type,email,hash) Values(@iduser,@name,@type,@email,@hash)";
                         MySqlCommand cmd1 = new MySqlCommand(qu1, conn);
                         cmd1.Parameters.AddWithValue("@iduser", a);
                         cmd1.Parameters.AddWithValue("@name", b);
                         cmd1.Parameters.AddWithValue("@type", c);
                         cmd1.Parameters.AddWithValue("@email", d);
                         cmd1.Parameters.AddWithValue("@hash", f);
                         cmd1.ExecuteNonQuery();
                         conn.Close();
                   }
                   con.Close();
               }
               catch (MySql.Data.MySqlClient.MySqlException ex)
               {
                   Console.WriteLine(ex);
               }
           }
           else
           {
                MessageBox.Show("Check connectivity and try again!");
           }
       }
   }
}

Here local database contain 7 rows and remote database contain 5 rows. The first 5 rows in local and remote database are the same. So while we inserting query failed with duplication of rows. Actually the extra 2 rows must be inserted to the remote database. How to avoid this problem?

How do you know if the rows are duplicate ? Is there a primary / unique key which can be used to identify the duplicates ? — Madhur Bhaiya, Nov 22 '18 at 04:33
You can use `insert ignore`; `replace into` etc. Please refer this tutorial: https://www.tutorialspoint.com/mysql/mysql-handling-duplicates.htm and official documentation at: https://dev.mysql.com/doc/refman/5.5/en/insert.html Following answer may also help: https://stackoverflow.com/a/548570/2469308 — Madhur Bhaiya, Nov 22 '18 at 04:36

score 0 · Answer 1 · answered Nov 22 '18 at 07:10

0

Store last saved User ID in your local db and when you again go to remote db to sync your data, put this where clause in your query where RemoteDB.UserID > local.LastStoredUserID. So it will only fetch new records...

answered Nov 22 '18 at 07:10

Aamir Nakhwa

393
1
12

how to avoid duplicates in remote db while syncing with local database in c#

1 Answers1