数据库同步工具-珂珂的个人博客 - 一个程序猿的个人网站

由于需将本地数据库同步到服务器上，其实微软有订阅和发布，不过我是没搞清楚，我只需要简单功能。之前的版本是这样的

后来发现有局限性，比如新增表后需要改代码重新编译，而且每次需要选择数据库

后来设计成了可配置的

其实原理很简单，已经可以达到我的目的了，两个数据库的表必须完全一样，当然列的顺序没关系，而且必须有一个唯一主键和一个最后修改时间列，时间列用来判断本地的数据是否比服务器新。先查找本地有的主键而且服务器没有则需要新增，如果本地有服务器也有但本地时间较新则需要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;
        }
 
    }
}

珂珂的个人博客 - 一个程序猿的个人网站

数据库同步工具

1 评论

给个评论吧

去顶部