0

I am supposed to create a C# solution to compare the data (table, column and row) from two different databases and update the 2nd one as follows: 1. with just the rows that are updated recently (incase of duplicates) 2. check for new/updated column in 1st one and update/delete in 2nd accordingly or drop the table entirely and copy it from 1st one. 3. check for new/updated table in 1st one and update/delete in 2nd accordingly or drop the table entirely and copy it from 1st one. So basically I have to synchronize the data between the two databases.

As of now, I have just got to connecting the database part. I am not sure how to proceed (should I be dumping the data from the database and checking or is there a simpler way?).!!!

Any help would be appreciated.

Thank you!

8
  • That won't be "integrity safe" unless you completely lock all tables in the source database to stop changes while you make the updates in the destination. Rather then reinventing the wheel you could use existing technology to keep databases in sync and try the built-in replication support: dev.mysql.com/doc/refman/5.0/en/replication.html Apr 2 '15 at 8:33
  • seems fair, but im asked to build a C# solution to do the above stuff..! according to my understanding (correct me if I'm wrong), replication is a process done on master/slave machines, correct? so how will that be a solution for what i want :( Apr 2 '15 at 9:36
  • That's why I used a comment: I've suggested an alternative rather than answering the actual question! If the result wanted is "an updated copy of the DB from <source> to exist at <destination>" then replication will achieve that as efficiently as anything hand-rolled (unless you have some business logic that states not all tables are to be included in which case a hand-rolled routine may transfer much less). You can do log shipping and control when log backups are taken & shipped & restored so you can have a static copy at a particular time rather than having to run fully dynamic replication. Apr 2 '15 at 13:19
  • I would call this exercise reinventing the wheel. There are tools in the wild that will perform this checksum and sync for you. Check pt-toolkit's pt-table-checksum and pt-table-sync for this functionality.
    – eroomydna
    Apr 2 '15 at 14:09
  • @DavidSpillett i do not want an exact copy from source to destination, but just the rows which are recently updated. in the case of replication, i will have all duplicates copied to my destination (which i don't want). i want only the updated rows from source to destination, along with new ones (if added in source). I'm not sure if that is a possible scenario with replication, please let me know.! thank you. Apr 2 '15 at 14:21
0
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient; 

namespace DBsync2
{
      //table2 is the Master(with duplicates) Table and table1 is the one without duplicates 

<pre>
public partial class Form1 : Form
{
    private MySqlConnection conn1 = null;
    private MySqlConnection conn2 = null;
    string cs1,cs2;

    class IdTsEntry
    {
        public Int64 fid { get; set; }
        public String fdate { get; set; }
    }

    void Init()
    {
        //initialize cs1, cs2 to suit the database connection details.

        try
        {
            string servername1 = server1.ToString();
            string uname1 = uid1.ToString();
            string pass1 = pwd1.ToString();
            string dbName1 = dbname1.ToString();

            string tbl1 = table1.ToString();
            string tbl2 = table2.ToString();

            conn1 = new MySqlConnection(cs1);
            conn1.Open();
            conn2 = new MySqlConnection(cs2);
            conn2.Open();

            tbl1 = "abc";
            tbl2 = "def";

            // Load target list's ids and timestamps
            string stmt1 = "SELECT seriennummer, DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl1;
            MySqlCommand cmd1 = new MySqlCommand(stmt1, conn1);
            List&lt;IdTsEntry&gt; A = new List&lt;IdTsEntry&gt;();
            using (MySqlDataReader reader = cmd1.ExecuteReader())
            {
                //while (reader.Read())
                //{ // I assume the fields are set to NOT NULL
                //    A.Add(new IdTsEntry()
                //    {
                //        fid = reader.GetInt64(0),
                //        fdate = reader.GetString(1)
                //    });
                //}

                while (reader.Read())
                { // I assume the fields are set to NOT NULL
                    A.Add(new IdTsEntry()
                    {
                        fid = reader.IsDBNull(0) ? -1L : reader.GetInt64(0),
                        fdate = reader.IsDBNull(1) ? "" : reader.GetString(1)
                    });
                }
            }
            // Load source list's ids and timestamps
            string stmt2 = "SELECT fid, DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2;
            MySqlCommand cmd2 = new MySqlCommand(stmt2, conn2);

            List&lt;IdTsEntry&gt; B = new List&lt;IdTsEntry&gt;();
            using (MySqlDataReader reader = cmd2.ExecuteReader())
            {
                while (reader.Read())
                { // I assume the fields are set to NOT NULL
                    B.Add(new IdTsEntry()
                    {
                        fid = reader.GetInt64(0),
                        fdate = reader.GetString(1)
                    });
                }

            }

            // Filter lists
            List&lt;Int64&gt; List1 = new List&lt;Int64&gt;();
            List&lt;Int64&gt; List2 = new List&lt;Int64&gt;();
            foreach (IdTsEntry b in B)
            {
                var a = A.FirstOrDefault(e =&gt; e.fid.Equals(b.fid));
                if (a == null) 
                    List2.Add(b.fid); // b.id not in A -&amp;gt; new row
                else if (!a.fdate.Equals(b.fdate))
                    List1.Add(b.fid); // b.id in A but other timestamp -&amp;gt; altered row
            }

                        // Update altered rows
            //string ct1 = "SELECT variante,charge,DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2 + " WHERE fid = {0}";
            //string ct2 = "UPDATE " + tbl1 + " SET variante = @val1,charge = @val2, fdate = @val3 WHERE seriennummer = {0}";
            foreach (Int64 id in List1)
            {
                // Read all entry values into parameters
               // cmd1.CommandText = String.Format(ct1, id);
                cmd1.CommandText = "SELECT variante,charge,DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2 + " WHERE fid = " + id.ToString();

               // MessageBox.Show(cmd1.CommandText);
                cmd2.Parameters.Clear();
                int i = 0;
                using (MySqlDataReader reader = cmd1.ExecuteReader())
                {
                    if (!reader.Read()) continue; 
                    for (int n = 0; n &lt; reader.FieldCount; n++)
                    {
                        cmd2.Parameters.AddWithValue(String.Format("val{0}", ++i),
                            reader.IsDBNull(n) ? DBNull.Value : reader.GetValue(n));
                    }

                }
                // Update row
                //cmd2.CommandText = String.Format(ct2, id);
                cmd2.CommandText = "UPDATE " + tbl1 + " SET variante = @val1,charge = @val2, fdate = @val3 WHERE seriennummer = " + id.ToString();
                cmd2.ExecuteNonQuery();
            }

                            // Insert new rows
            //ct1 = "SELECT fid, variante,charge,DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2 + " WHERE fid = {0}";
            //ct2 = "INSERT INTO " + tbl1 + " (seriennummer, variante,charge,fdate) " +
            //    "VALUES (@val1, @val2, @val3, @val4)";
            //    cmd2.CommandText = ct2;
                cmd2.CommandText = "INSERT INTO " + tbl1 + " (seriennummer,variante,charge,fdate) " +
                    "VALUES (@val1, @val2, @val3, @val4)";
                foreach (Int64 id in List2)
                {
                    // Read all values into parameters
                    //cmd1.CommandText = String.Format(ct1, id);

                    cmd1.CommandText = "SELECT fid,variante,charge,DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2 + " WHERE fid = " + id.ToString();
                    cmd2.Parameters.Clear();
                    int i = 0;
                    using (MySqlDataReader reader = cmd1.ExecuteReader())
                    {
                        if (!reader.Read()) continue;
                        for (int n = 0; n &lt; reader.FieldCount; n++)
                        {
                            cmd2.Parameters.AddWithValue(String.Format("val{0}", ++i),
                                reader.IsDBNull(n) ? DBNull.Value : reader.GetValue(n));
                        }
                    }
                    // Insert row
                    cmd2.ExecuteNonQuery();
                }

        }

        catch (MySqlException ex)
        {
            MessageBox.Show("Error: " + ex.ToString());

        }

    }
    public Form1()
    {
        InitializeComponent();

        Init();

    }

}


}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.