珂珂的个人博客 - 一个程序猿的个人网站
由于需将本地数据库同步到服务器上，其实微软有订阅和发布，不过我是没搞清楚，我只需要简单功能。之前的版本是这样的
后来发现有局限性，比如新增表后需要改代码重新编译，而且每次需要选择数据库
后来设计成了可配置的
其实原理很简单，已经可以达到我的目的了，两个数据库的表必须完全一样，当然列的顺序没关系，而且必须有一个唯一主键和一个最后修改时间列，时间列用来判断本地的数据是否比服务器新。先查找本地有的主键而且服务器没有则需要新增，如果本地有服务器也有但本地时间较新则需要update,如果服务器有主键而本地不存在则需要删除服务器上的。这个对于多种数据库都可以。
前台XAML代码
<Window x:Class="Blogs.Tools.DbSync.DbSyncWindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" Title="数据同步" Height="350" Width="525"> <Grid> <Grid.RowDefinitions> <RowDefinition Height="30"></RowDefinition> <RowDefinition Height="70"></RowDefinition> <RowDefinition Height="50"></RowDefinition> <RowDefinition Height="*"></RowDefinition> <RowDefinition Height="60"></RowDefinition> </Grid.RowDefinitions> <WrapPanel Grid.Row="0"> <ComboBox x:Name="ComboBox1" SelectionChanged="ComboBox1_SelectionChanged" Width="200" Margin="0"> </ComboBox> </WrapPanel> <StackPanel Grid.Row="1"> <TextBox IsEnabled="False" Text="" x:Name="txtDb1"></TextBox> <TextBox IsEnabled="False" Text="" x:Name="txtDb2" Margin="0,10,0,0"></TextBox> </StackPanel> <WrapPanel Grid.Row="2"> <TextBlock Text="条件" ></TextBlock> <TextBox Text="" x:Name="txtWhere" Width="200"></TextBox> <Button x:Name="btnSync" Content="同步" HorizontalAlignment="Left" VerticalAlignment="Top" Width="75" Click="btnSync_Click"/> <CheckBox Content="是否执行删除" x:Name="chkIsDeleteDest"></CheckBox> </WrapPanel> <ListBox x:Name="ListBox1" Grid.Row="3" > <ListBox.ContextMenu> <ContextMenu> <MenuItem Header="清除" Click="ClearMenuItem_Click"></MenuItem> </ContextMenu> </ListBox.ContextMenu> </ListBox> <DockPanel Grid.Row="4"> <ProgressBar DockPanel.Dock="Top" Height="30" x:Name="ProgressBar1"/> <TextBlock x:Name="txtStatus" Text="就绪"></TextBlock> </DockPanel> </Grid> <Window.TaskbarItemInfo> <TaskbarItemInfo x:Name="taskbarItemInfo1"> </TaskbarItemInfo> </Window.TaskbarItemInfo> </Window>
后台代码
using FYJ.Data;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using System.Windows.Shell;
using System.Xml;
namespace Blogs.Tools.DbSync
{
/// <summary>
/// DbSyncWindow.xaml 的交互逻辑
/// </summary>
public partial class DbSyncWindow : Window
{
public DbSyncWindow()
{
InitializeComponent();
}
protected override void OnInitialized(EventArgs e)
{
base.OnInitialized(e);
XmlDocument doc = new XmlDocument();
doc.Load(AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "config.xml");
XmlNodeList list = doc.SelectNodes("/config/sync");
foreach (XmlNode node in list)
{
this.ComboBox1.Items.Add(node.Attributes["name"].InnerText);
}
this.ComboBox1.SelectedIndex = 0;
}
private async void btnSync_Click(object sender, RoutedEventArgs e)
{
this.btnSync.IsEnabled = false;
this.txtStatus.Text = "正在同步...";
XmlDocument doc = new XmlDocument();
doc.Load(AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "config.xml");
string name = this.ComboBox1.SelectedItem.ToString();
XmlNode pnode = doc.SelectSingleNode("//sync[@name='" + name + "']");
XmlNode node1 = pnode.SelectSingleNode("localDB");
XmlNode node2 = pnode.SelectSingleNode("remoteDB");
IDbHelper db1 = (IDbHelper)Activator.CreateInstance(Type.GetType(node1.Attributes["type"].InnerText), new object[] { node1.Attributes["providerName"].InnerText, node1.Attributes["connectionString"].InnerText });
IDbHelper db2 = (IDbHelper)Activator.CreateInstance(Type.GetType(node2.Attributes["type"].InnerText), new object[] { node2.Attributes["providerName"].InnerText, node2.Attributes["connectionString"].InnerText });
string where = this.txtWhere.Text.Trim();
List<SyncInfo> infos = new List<SyncInfo>();
foreach (XmlNode n in pnode.SelectNodes("tables/table"))
{
SyncInfo s = new SyncInfo();
s.TableName = n.Attributes["tableName"].InnerText;
s.PrimaryKey = n.Attributes["primaryKey"].InnerText;
s.UpdateDateColumnName = n.Attributes["updateDateColumnName"].InnerText;
if (s.UpdateDateColumnName.Trim() == "")
{
s.UpdateDateColumnName = null;
}
if (s.TableName.Contains("portal_article_title"))
{
s.FixColumn = ((o) =>
{
if (o == "from")
{
return "`from`";
}
return o;
});
}
infos.Add(s);
};
SyncHelper helper = new SyncHelper(db1, db2, infos);
//helper.IsSupportTransaction = true;
helper.IsDeleteDest = chkIsDeleteDest.IsChecked.Value;
helper.ProgressEvent += helper_ProgressEvent;
helper.ExceptionEvent += helper_ExceptionEvent;
helper.MessageEvent += helper_MessageEvent;
await Task.Factory.StartNew(() =>
{
helper.Sync();
});
this.txtStatus.Text = "就绪";
this.btnSync.IsEnabled = true;
}
void helper_MessageEvent(string message)
{
this.ListBox1.Dispatcher.Invoke(() =>
{
this.ListBox1.Items.Add(message);
});
}
void helper_ExceptionEvent(Exception ex)
{
this.ListBox1.Dispatcher.Invoke(() =>
{
ListBoxItem item = new ListBoxItem();
item.Content = ex.Message;
//item.Background = new SolidColorBrush(Color.FromRgb(255,0,0));
item.Foreground = Brushes.Red;
item.Padding = new Thickness(2);
this.ListBox1.Items.Add(item);
//this.ListBox1.Items.Add(ex.Message);
});
}
void helper_ProgressEvent(int current, int total)
{
this.taskbarItemInfo1.Dispatcher.Invoke(() =>
{
this.taskbarItemInfo1.ProgressState = TaskbarItemProgressState.Normal;
this.taskbarItemInfo1.ProgressValue = current / (double)total;
});
this.ProgressBar1.Dispatcher.Invoke(new Action(() =>
{
this.ProgressBar1.Maximum = total;
this.ProgressBar1.Value = current;
}));
this.txtStatus.Dispatcher.Invoke(new Action(() =>
{
this.txtStatus.Text = "正在同步" + current + "/" + total + "...";
}));
if (current == total)
{
this.Dispatcher.Invoke(delegate()
{
this.txtStatus.Text = "就绪";
MessageBox.Show("完成");
});
}
}
private void ClearMenuItem_Click(object sender, RoutedEventArgs e)
{
this.ListBox1.Items.Clear();
}
private void ComboBox1_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
string name = this.ComboBox1.SelectedItem.ToString();
XmlDocument doc = new XmlDocument();
doc.Load(AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "config.xml");
XmlNode node = doc.SelectSingleNode("//sync[@name='" + name + "']").SelectSingleNode("localDB");
this.txtDb1.Text = node.Attributes["type"].InnerText + "----" + node.Attributes["connectionString"].InnerText;
XmlNode node2 = doc.SelectSingleNode("//sync[@name='" + name + "']").SelectSingleNode("remoteDB");
this.txtDb2.Text = node2.Attributes["type"].InnerText + "----" + node2.Attributes["connectionString"].InnerText;
}
}
}
5/22/2015 9:31:39 PM 113.119.174....
niub