来自C＃中不同数据库的MySQL数据同步 [英] MySQL Data Synchronisation from different Databases in C#
问题描述
我应该创建一个C＃解决方案来比较来自两个不同数据库的数据（表，列和行），并按如下方式更新第二个：
1.只包含行最近更新（重复）
2.检查第一个中的新/更新列并相应地更新/删除第二个或完全删除表并从第一个复制。
3.检查第一个中的新/更新表并相应更新/删除第二个或完全删除表并从第一个复制。
所以基本上我必须同步数据这两个数据库。
截至目前，我只需要连接数据库部分。我不知道如何继续（我应该从数据库中转储数据并检查还是有更简单的方法？）。!!!
任何帮助都会感谢。
谢谢！
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!
推荐答案
你好Revathy
如果你有一个标识列和一个时间戳（其中包含行更新的时间）列然后...
你可以检索更新的行列表在一个特定的时间戳之后
然后你可以在其他数据库表中更新或添加新行[在第二个数据库中保持行的id相同]。
你可以设置sql job它在指定的时间运行并同步数据。
快乐编码！
：）
hello Revathy
if You have an identity column and a time-stamp(which contains time of row update) column in table then...
you can retrieve list of rows which are updated after a particular time stamp
and then you can update or add new rows in other db table [keep id of row same in second db].
you can set sql job which runs on specified time and synchronize data.
Happy Coding!
:)
倾倒可能是最简单的方法，虽然它可以很快地获得数据和时间。所以我建议只更新并插入更改，即使它有点复杂 - 特别是如果你要在大桌子上工作。
start：两个数据库是否在同一台服务器上？如果是这种情况，您可以使用以下类似的方法处理此问题：
Dumping might be the easiest way to go here, although it can get data and time consuming quite quickly. So I'd suggest to just update and insert the changes even if it's a bit more complicated - especially if you're going to work on big tables.
For a start: Are the two databases on the same server? If that's the case, you might be able to handle this with SQL using something like this:
-- Update existing rows
UPDATE table1, table2
SET table1.col1 = table2.col1,
table1.col2 = table2.col2,
table1.col3 = table2.col3,
...
table1.col_last = table2.col_last
-- do not use the id field here!
WHERE table1.id = table2.id AND table1.time_stamp <> table2.time_stamp
-- Insert new rows
INSERT INTO table1 (col1, col2, col3, ... col_last) -- use id field here!
SELECT col1, col2, col3, ... col_last FROM table2
WHERE NOT EXISTS (SELECT * FROM table2 WHERE table2.id = table1.id)
I我不熟悉MySQL，我无法测试它。所以请在使用之前做一些测试！
如果你需要两个不同的连接，那就太难了。我尝试做类似于SQL语句暗示的事情：
- 获取所有id及其时间戳的列表：
- 来自table1（目标）的列表A
- 来自table2的列表B（来源）
- 过滤列表以查找：
- List1：存在于A和B但时间戳不同的ID
- List2 ：B中不存在的A中的ID
- 从table2加载所有条目，其中id在List1中并更新table1中的条目
- 从table1加载所有条目，其中id在List2中，并在table1中插入
class IdTsEntry
{
public int Id { get; set; }
public DateTime TimeStamp { get; set; }
}
using (DbCommand cmd1 = conn1.CreateCommand())
using (DbCommand cmd2 = conn2.CreateCommand())
{
// Load target list's ids and timestamps
cmd1.CommandText = "SELECT id, time_stamp FROM table1";
List<idtsentry> A = new List<idtsentry>();
using (DbDataReader reader = cmd1.ExecuteReader())
{
while (reader.Read())
{ // I assume the fields are set to NOT NULL
A.Add(new IdTsEntry() {
Id = reader.GetInt32(0),
TimeStamp = reader.GetTimeStamp(1)
});
}
}
// Load source list's ids and timestamps
cmd2.CommandText = "SELECT id, time_stamp FROM table2";
List<idtsentry> B = new List<idtsentry>();
using (DbDataReader reader = cmd2.ExecuteReader())
{
while (reader.Read())
{ // I assume the fields are set to NOT NULL
A.Add(new IdTsEntry() {
Id = reader.GetInt32(0),
TimeStamp = reader.GetTimeStamp(1)
});
}
}
// Filter lists
List<int> List1 = new List<int>();
List<int> List2 = new List<int>();
foreach (IdTsEntry b in B)
{
var a = A.FirstOrDefault(e => e.Id.Equals(b.Id));
if (a == null)
List2.Add(b.Id); // b.id not in A -> new row
else if (!a.TimeStamp.Equals(b.TimeStamp))
List1.Add(b.Id); // b.id in A but other timestamp -> altered row
}
// Update altered rows
string ct1 = "SELECT col1, col2, ... col_last FROM table2 WHERE id = {0}";
string ct2 = "UPDATE table1 SET col1 = @val1, col2 = @val2, ... " +
"col_last = @val_last WHERE id = {0}";
foreach (int id in List1)
{
// Read all entry values into parameters
cmd1.CommandText = String.Format(ct1, id);
cmd2.Parameters.Clear();
int i = 0;
using (DbDataReader reader = cmd1.ExecuteReader())
{
if (!reader.Read()) continue;
for (int n = 0; n < reader.FieldCount; n++)
{
cmd2.Parameters.Add(String.Format("val{0}", ++i),
reader.IsDBNull(n) ? DBNull.Value : reader.GetValue(n));
}
}
// Update row
cmd2.CommandText = String.Format(ct2, id);
cmd2.ExecuteNonQuery();
}
// Insert new rows
ct1 = "SELECT id, col1, col2, ... col_last FROM table2 WHERE id = {0}";
ct2 = "INSERT INTO table1 (id, col1, col2, ... col_last) " +
"VALUES (@val1, @val2, @val3, ... @val_last)";
cmd2.CommandText = ct2;
foreach (int id in List2)
{
// Read all values into parameters
cmd1.CommandText = String.Format(ct1, id);
cmd2.Parameters.Clear();
int i = 0;
using (DbDataReader reader = cmd1.ExecuteReader())
{
if (!reader.Read()) continue;
for (int n = 0; n < reader.FieldCount; n++)
{
cmd2.Parameters.Add(String.Format("val{0}", ++i),
reader.IsDBNull(n) ? DBNull.Value : reader.GetValue(n));
}
}
// Insert row
cmd2.ExecuteNonQuery();
}
}
</int></int></int></int></idtsentry></idtsentry></idtsentry></idtsentry>
和SQL语句一样：我没有测试过这个，我写的很快。希望它会帮助你。您还可以更改WHERE语句以仅覆盖上次同步后的日期范围 - 这将加快速度。
Like the SQL statement: I haven't tested this and I wrote that quickly. Hopefully it'll help you though. You can also alter the WHERE statements to cover only the date range after the last sync - that'll speed things up.
这篇关于来自C＃中不同数据库的MySQL数据同步的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
