Syncing MYSQL database from sqldump

See more:

C#

I am trying to extend this solution towards two databases and now I'm stuck.!

I have a db1 and I'm creating a sqldump of that, and using that dump I create another database db2.
Then later, the db1 gets updated or modified and I take the dump of it again and using that second dump of db1, I should make the same changes as here (MySQL Data Synchronisation from different Databases in C#[^])[i.e. inserting/updating the recent rows] to the already existing db2 (which was created before in the first step from first dump).
All the operations are done locally on machine2 that has db2 from the sqldump of db1 from machine1 in a usb drive.! db1 and db2 have the same schema.

I was thinking of creating a dummy database from the new dump (from the 2nd updated db1) and using it to compare and update accordingly into db2. but is there a way to directly compare the dumpsql's tables and rows or what is a better way to do it.!?

The code till now that I've tried is posted below.
Could anyone please help me out with some ideas??

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 DBsync3
{
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; }
}

public Form1()
{
InitializeComponent();
Init();
}

void create_backup()
{

string constring1 = "SERVER=localhost;" + "DATABASE=;" + "UID=root;" + "PASSWORD=;" + "Convert Zero Datetime=True;" +
"Allow Zero Datetime=True";
string file1 = "C:\\Users\\Santhanam\\Documents\\Visual Studio 2010\\Projects\\DBsync3\\backup.sql";
using (MySqlConnection conn = new MySqlConnection(constring1))
{
using (MySqlCommand cmd = new MySqlCommand())
{
using (MySqlBackup mb = new MySqlBackup(cmd))
{
// if (file1.Length == 0)
{
cmd.Connection = conn;
conn.Open();
//cmd.CommandText = "SET SESSION max_allowed_packet=1024*1024*1024;";
mb.ExportInfo.MaxSqlLength = 1024 * 1024; // 1MB
mb.ExportInfo.AddCreateDatabase = true;
mb.ExportInfo.ExportTableStructure = true;
mb.ExportInfo.ExportRows = true;
mb.ExportToFile(file1);
}
}
}
}
}

void load_from_backup()
{

string constring2 = "SERVER=localhost;" + "DATABASE=;" + "UID=root;" + "PASSWORD=;" + "Convert Zero Datetime=True;" +
"Allow Zero Datetime=True";

string file2 = "C:\\Users\\Santhanam\\Documents\\Visual Studio 2010\\Projects\\DBsync3\\backup.sql";
using (MySqlConnection conn = new MySqlConnection(constring2))
{
using (MySqlCommand cmd = new MySqlCommand())
{
using (MySqlBackup mb = new MySqlBackup(cmd))
{
cmd.Connection = conn;
conn.Open();
// cmd.CommandText = "SET SESSION max_allowed_packet=1024*1024*1024;";
mb.ImportInfo.TargetDatabase = "_create_from_dump_dummy";
mb.ImportInfo.DatabaseDefaultCharSet = "utf8";
mb.ImportFromFile(file2);
}
}
}
}

void Init()
{


try




{
create_backup();
load_from_backup();



cs1 = "SERVER=localhost;" + "DATABASE=;" + "UID=root;" + "PASSWORD=;" + "Convert Zero Datetime=True;" +
"Allow Zero Datetime=True"; //existing database
cs2 = "SERVER=localhost;" + "DATABASE=;" + "UID=root;" + "PASSWORD=;" + "Convert Zero Datetime=True;" +
"Allow Zero Datetime=True"; //dumped database

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.



// 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 A = new List();
using (MySqlDataReader reader = cmd1.ExecuteReader())
{

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 " + tbl1;
MySqlCommand cmd2 = new MySqlCommand(stmt2, conn2);

List B = new List();
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 List1 = new List();
List List2 = new List();
foreach (IdTsEntry b in B)
{
var a = A.FirstOrDefault(e => e.fid.Equals(b.fid));
if (a == null)
List2.Add(b.fid); // b.id not in A -> new row
else if (!a.fdate.Equals(b.fdate))
List1.Add(b.fid); // b.id in A but other timestamp -> altered row
}


foreach (Int64 id in List1)
{
// Read all entry values into parameters

DateTime time = DateTime.Now;



cmd1.CommandText = "SELECT variante,charge,fdate FROM " + tbl1 + " WHERE fid = " + id.ToString();

cmd2.Parameters.Clear();
int i = 0;
using (MySqlDataReader reader = cmd1.ExecuteReader())
{
if (!reader.Read()) continue;
for (int n = 0; n < reader.FieldCount; n++)
{
cmd2.Parameters.AddWithValue(String.Format("val{0}", ++i),
reader.IsDBNull(n) ? DBNull.Value : reader.GetValue(n));
if (n == 2)
time = DateTime.Parse(reader.GetValue(n).ToString());


}

}
// Update row


cmd2.CommandText = "UPDATE " + tbl1 + " SET variante = @val1,charge = @val2, fdate = '" + time.ToString("yyyy-MM-dd HH:mm:ss") + "' WHERE seriennummer = " + id.ToString();
cmd2.ExecuteNonQuery();
}


// Insert new rows

cmd2.CommandText = "INSERT INTO " + tbl1 + " (seriennummer,variante,charge,fdate) " +
"VALUES (@val1, @val2, @val3, @val4)";
foreach (Int64 id in List2)
{
// Read all values into parameters

DateTime time = DateTime.Now;

cmd1.CommandText = "SELECT fid,variante,charge,fdate FROM " + tbl1 + " WHERE fid = " + id.ToString();

cmd2.Parameters.Clear();
int i = 0;
using (MySqlDataReader reader = cmd1.ExecuteReader())

{
if (!reader.Read()) continue;
for (int n = 0; n < reader.FieldCount; n++)
{
cmd2.Parameters.AddWithValue(String.Format("val{0}", ++i),
reader.IsDBNull(n) ? DBNull.Value : reader.GetValue(n));
if (n == 2)
time = DateTime.Parse(reader.GetValue(n).ToString());

}
}
// Insert row
cmd2.ExecuteNonQuery();
}


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

}


}


}

}