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
{
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()
{
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";
string stmt1 = "SELECT seriennummer, DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl1;
MySqlCommand cmd1 = new MySqlCommand(stmt1, conn1);
List<IdTsEntry> A = new List<IdTsEntry>();
using (MySqlDataReader reader = cmd1.ExecuteReader())
{
while (reader.Read())
{
A.Add(new IdTsEntry()
{
fid = reader.IsDBNull(0) ? -1L : reader.GetInt64(0),
fdate = reader.IsDBNull(1) ? "" : reader.GetString(1)
});
}
}
string stmt2 = "SELECT fid, DATE_FORMAT(fdate, '%Y-%M-%D %H:%i:%s') FROM " + tbl2;
MySqlCommand cmd2 = new MySqlCommand(stmt2, conn2);
List<IdTsEntry> B = new List<IdTsEntry>();
using (MySqlDataReader reader = cmd2.ExecuteReader())
{
while (reader.Read())
{
B.Add(new IdTsEntry()
{
fid = reader.GetInt64(0),
fdate = reader.GetString(1)
});
}
}
List<Int64> List1 = new List<Int64>();
List<Int64> List2 = new List<Int64>();
foreach (IdTsEntry b in B)
{
var a = A.FirstOrDefault(e => e.fid.Equals(b.fid));
if (a == null)
List2.Add(b.fid);
else if (!a.fdate.Equals(b.fdate))
List1.Add(b.fid);
}
foreach (Int64 id in List1)
{
cmd1.CommandText = "SELECT 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 < reader.FieldCount; n++)
{
cmd2.Parameters.AddWithValue(String.Format("val{0}", ++i),
reader.IsDBNull(n) ? DBNull.Value : reader.GetValue(n));
}
}
cmd2.CommandText = "UPDATE " + tbl1 + " SET variante = @val1,charge = @val2, fdate = @val3 WHERE seriennummer = " + id.ToString();
cmd2.ExecuteNonQuery();
}
cmd2.CommandText = "INSERT INTO " + tbl1 + " (seriennummer,variante,charge,fdate) " +
"VALUES (@val1, @val2, @val3, @val4)";
foreach (Int64 id in List2)
{
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 < reader.FieldCount; n++)
{
cmd2.Parameters.AddWithValue(String.Format("val{0}", ++i),
reader.IsDBNull(n) ? DBNull.Value : reader.GetValue(n));
}
}
cmd2.ExecuteNonQuery();
}
}
catch (MySqlException ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
}
public Form1()
{
InitializeComponent();
Init();
}
}
}