同步数据

C/S同步数据篇


TODO:

关于同步数据篇:主要解说理清业务思路,下一篇:同步数据(根据业务需求):【将数据库中sys_Menu数据加入到sys_Menu_Copy】

TODO:

1,表【sys_Menu_Copy】已存在数据。过滤

2,表【sys_Menu_Copy】未存在数据。加入

解说篇:1。窗口设计器生成的代码2,后台cs代码:

窗口设计器生成的代码

namespace DataSynchronousBWokerUI
{
    partial class MainForm
    {
        /// <summary>
        /// 必需的设计器变量。
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary>
        /// 清理全部正在使用的资源。

/// </summary> /// <param name="disposing">假设应释放托管资源,为 true;否则为 false。</param> protected override void Dispose(bool disposing) { if (disposing && (components != null)) { components.Dispose(); } base.Dispose(disposing); } #region Windows 窗口设计器生成的代码 /// <summary> /// 设计器支持所需的方法 - 不要 /// 使用代码编辑器改动此方法的内容。

/// </summary> private void InitializeComponent() { System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(MainForm)); this.label = new System.Windows.Forms.Label(); this.btStop = new System.Windows.Forms.Button(); this.btStart = new System.Windows.Forms.Button(); this.label4 = new System.Windows.Forms.Label(); this.label5 = new System.Windows.Forms.Label(); this.label6 = new System.Windows.Forms.Label(); this.listBox1 = new System.Windows.Forms.ListBox(); this.progressBar1 = new System.Windows.Forms.ProgressBar(); this.tableLayoutPanel1 = new System.Windows.Forms.TableLayoutPanel(); this.tableLayoutPanel1.SuspendLayout(); this.SuspendLayout(); // // label // this.label.AutoSize = true; this.label.Location = new System.Drawing.Point(170, 67); this.label.Name = "label"; this.label.Size = new System.Drawing.Size(0, 12); this.label.TabIndex = 13; // // btStop // this.btStop.Anchor = System.Windows.Forms.AnchorStyles.None; this.btStop.BackgroundImage = ((System.Drawing.Image)(resources.GetObject("btStop.BackgroundImage"))); this.btStop.Font = new System.Drawing.Font("宋体", 14.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0))); this.btStop.Location = new System.Drawing.Point(212, 53); this.btStop.Name = "btStop"; this.btStop.Size = new System.Drawing.Size(203, 44); this.btStop.TabIndex = 12; this.btStop.Text = "取消同步"; this.btStop.Click += new System.EventHandler(this.btStop_Click); // // btStart // this.btStart.Anchor = System.Windows.Forms.AnchorStyles.None; this.btStart.BackgroundImage = ((System.Drawing.Image)(resources.GetObject("btStart.BackgroundImage"))); this.btStart.Font = new System.Drawing.Font("宋体", 14.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0))); this.btStart.Location = new System.Drawing.Point(3, 53); this.btStart.Name = "btStart"; this.btStart.Size = new System.Drawing.Size(203, 44); this.btStart.TabIndex = 11; this.btStart.Text = "同步数据"; this.btStart.Click += new System.EventHandler(this.btStart_Click); // // label4 // this.label4.AutoSize = true; this.label4.Location = new System.Drawing.Point(33, 143); this.label4.Name = "label4"; this.label4.Size = new System.Drawing.Size(0, 12); this.label4.TabIndex = 14; // // label5 // this.label5.AutoSize = true; this.label5.Image = ((System.Drawing.Image)(resources.GetObject("label5.Image"))); this.label5.Location = new System.Drawing.Point(122, 142); this.label5.Name = "label5"; this.label5.Size = new System.Drawing.Size(0, 12); this.label5.TabIndex = 15; // // label6 // this.label6.AutoSize = true; this.label6.Image = ((System.Drawing.Image)(resources.GetObject("label6.Image"))); this.label6.Location = new System.Drawing.Point(209, 142); this.label6.Name = "label6"; this.label6.Size = new System.Drawing.Size(0, 12); this.label6.TabIndex = 16; // // listBox1 // this.listBox1.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom) | System.Windows.Forms.AnchorStyles.Left) | System.Windows.Forms.AnchorStyles.Right))); this.listBox1.BackColor = System.Drawing.Color.White; this.listBox1.ForeColor = System.Drawing.SystemColors.InactiveCaptionText; this.listBox1.FormattingEnabled = true; this.listBox1.ItemHeight = 12; this.listBox1.Location = new System.Drawing.Point(1, 57); this.listBox1.Name = "listBox1"; this.listBox1.Size = new System.Drawing.Size(417, 184); this.listBox1.TabIndex = 17; // // progressBar1 // this.progressBar1.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) | System.Windows.Forms.AnchorStyles.Right))); this.progressBar1.Location = new System.Drawing.Point(1, 12); this.progressBar1.Name = "progressBar1"; this.progressBar1.Size = new System.Drawing.Size(417, 30); this.progressBar1.TabIndex = 18; // // tableLayoutPanel1 // this.tableLayoutPanel1.ColumnCount = 2; this.tableLayoutPanel1.ColumnStyles.Add(new System.Windows.Forms.ColumnStyle(System.Windows.Forms.SizeType.Percent, 50F)); this.tableLayoutPanel1.ColumnStyles.Add(new System.Windows.Forms.ColumnStyle(System.Windows.Forms.SizeType.Percent, 50F)); this.tableLayoutPanel1.Controls.Add(this.btStart, 0, 1); this.tableLayoutPanel1.Controls.Add(this.btStop, 1, 1); this.tableLayoutPanel1.Dock = System.Windows.Forms.DockStyle.Bottom; this.tableLayoutPanel1.Location = new System.Drawing.Point(0, 243); this.tableLayoutPanel1.Name = "tableLayoutPanel1"; this.tableLayoutPanel1.RowCount = 2; this.tableLayoutPanel1.RowStyles.Add(new System.Windows.Forms.RowStyle(System.Windows.Forms.SizeType.Percent, 50F)); this.tableLayoutPanel1.RowStyles.Add(new System.Windows.Forms.RowStyle(System.Windows.Forms.SizeType.Percent, 50F)); this.tableLayoutPanel1.Size = new System.Drawing.Size(419, 100); this.tableLayoutPanel1.TabIndex = 19; // // MainForm // this.BackgroundImage = global::DataSynchronousBWokerUI.Properties.Resources._1234564578798798798797987987987987; this.ClientSize = new System.Drawing.Size(419, 343); this.Controls.Add(this.tableLayoutPanel1); this.Controls.Add(this.progressBar1); this.Controls.Add(this.listBox1); this.Controls.Add(this.label6); this.Controls.Add(this.label5); this.Controls.Add(this.label4); this.Controls.Add(this.label); this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle; this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon"))); this.Name = "MainForm"; this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen; this.Text = "測试:同步数据"; this.tableLayoutPanel1.ResumeLayout(false); this.ResumeLayout(false); this.PerformLayout(); } #endregion private System.Windows.Forms.Label label; private System.Windows.Forms.Button btStop; private System.Windows.Forms.Button btStart; private System.Windows.Forms.Label label4; private System.Windows.Forms.Label label5; private System.Windows.Forms.Label label6; private System.Windows.Forms.ListBox listBox1; private System.Windows.Forms.ProgressBar progressBar1; private System.Windows.Forms.TableLayoutPanel tableLayoutPanel1; } }

后台cs代码

定义线程名。线程開始标识,连接server成功标识,退出程序标识。源数据库。目标数据库

private Thread preg;//线程名
        private bool tureOfalse;//线程运行标识
        public bool conYes;//连接服务成功失败标识
        private bool one = false;//退出程序标识
        string strdbHipis = System.Configuration.ConfigurationManager.AppSettings["NewHmFrameWork_QHS_SQ"].ToString();//源数据库
        //string strdbPreg = System.Configuration.ConfigurationManager.AppSettings["NewHmFrameWork_QHS_SQ_QingHai"].ToString();//目标数据库
        string strdbPreg = System.Configuration.ConfigurationManager.ConnectionStrings["NewHmFrameWork_QHS_SQ"].ToString();//目标数据库

主方法实行调用:

public MainForm()
        {
            InitializeComponent();
            btStop.Enabled = false;//未开启进程,不同意取消进程
            //托付
            InitTrayIcon();//图标托盘托付
            Control.CheckForIllegalCrossThreadCalls = false;//线程托付
            this.FormClosing += new FormClosingEventHandler(form_Closing);//退出程序托付
        }

图标声明

//图标声明
        NotifyIcon trayIcon = new NotifyIcon();
        //图标路径C:\Users\Administrator\Desktop\DataSynchronousBWokerUI\DataSynchronousBWokerUI\Images\_net_32.ico
        private Icon mNetTrayIcon = new Icon("..//Images//_net_32.ico");

线程開始

//线程開始
        private void btStart_Click(object sender, EventArgs e)
        {
            tureOfalse = true;
            preg = new Thread(new ThreadStart(PREG));
            preg.Start();

            btStart.Enabled = false;
            btStop.Enabled = true;
        }

线程方法

//线程方法
        private void PREG()
        {
            do
            {
                listBox1.Items.Add("正在连接源数据库... ...!");
                listBox1.Items.Add("正在连接目标数据库... ...!");
                ISopenCon();
            } while (!conYes);
            DoWork_select();
        }

线程停止

//线程停止
        private void btStop_Click(object sender, EventArgs e)
        {
            tureOfalse = false;
            preg.Abort();
            /*2015/2/2 调整:点击[取消同步]。清空listbox当前数据*/
            this.listBox1.Items.Clear();
            listBox1.Items.Add("待同步... ...");
            //控件显示隐藏

            btStart.Enabled = true;
            btStop.Enabled = false;
        }

连接目标数据库失败将信息写入LOG

/// <summary>
        /// 连接目标数据库失败将信息写入LOG
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void fslog(Exception ex)
        {
            string filepath = DateTime.Now.ToString("yyyyMMdd") + ".log";
            FileStream fs = new FileStream(filepath, FileMode.Append, FileAccess.Write);
            StreamWriter sw = new StreamWriter(fs);
            DateTime time = DateTime.Now;
            sw.WriteLine(time);
            //sw.WriteLine("连接目标数据库失败");
            sw.WriteLine(ex.Message);
            sw.WriteLine("---------------------------------------------------------");
            sw.Close();
            fs.Close();
        }

尝试连接源、目标数据库

/// <summary>
        /// 尝试连接源、目标数据库
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ISopenCon()
        {

            try
            {
                SqlConnection conhipis = new SqlConnection(strdbHipis);
                conhipis.Open();
                conYes = true;
                conhipis.Close();
                SqlConnection conPreg = new SqlConnection(strdbPreg);
                conPreg.Open();
                conYes = true;
                conPreg.Close();

            }
            catch (Exception ex)
            {
                conYes = false;
                fslog(ex);
                listBox1.Items.Add("连接数据库失败......");
                Thread.Sleep(60000);
            }

        }

尝试删除目标数据库

/// <summary>
        /// 尝试删除目标数据库
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void DelPreg()
        {

            //try
            //{
            //bool delResult = true;
            ////插入数据之前。先删除当前社区的已有数据
            //string strDelSQL = "delete from dbo.sys_Menu ";
            //strDelSQL += "delete from dbo.sys_MenuInRoles ";
            //strDelSQL += "delete from dbo.sys_UsersInRoles ";
            //delResult = ExecuteSql(strDelSQL);
            //if (delResult == false)
            //{
            //    //this.label4.Text = "删除目标数据库失败......";
            //    listBox1.Items.Add("删除目标数据库失败......");
            //}
            //else
            //{
            //    //this.label4.Text = "删除目标数据库成功......";
            //    listBox1.Items.Add("删除目标数据库成功......");
            //}


            /*測试数据:删除dbo.sys_Menu_Copy在測试期间录入的数据便于二次录入<多次录入>*/
            SqlConnection hipiscon;
            SqlCommand SQLcom;
            hipiscon = new SqlConnection(strdbHipis);
            string strDelSQL = "delete from dbo.sys_Menu_Copy ";
            SQLcom = new SqlCommand(strDelSQL, hipiscon);
            try
            {
                hipiscon.Open();
                SQLcom.ExecuteNonQuery();
                hipiscon.Close();
                listBox1.Items.Add("删除目标数据库成功......");
            }
            catch (Exception ex)
            {
                listBox1.Items.Add("删除目标数据库失败......");
                fslog(ex);
                //Thread.Sleep(300000);
            }
            //}
            //catch (Exception ex)
            //{
            //    conYes = false;
            //    fslog(ex);
            //    listBox1.Items.Add("删除目标数据库失败......");
            //    Thread.Sleep(60000);
            //}
        }

对源库操作。对目标库操作

/// <summary>
        /// 对源库操作
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private DataTable IsOpenDT(string strSQL, string strConn)
        {
            SqlConnection hipiscon;
            SqlCommand SQLcom;
            DataTable DT;
            SqlDataAdapter SQLADA;
            hipiscon = new SqlConnection(strConn);
            SQLcom = new SqlCommand(strSQL, hipiscon);
            try
            {
                hipiscon.Open();
                SQLcom.ExecuteNonQuery();
                conYes = true;
                hipiscon.Close();
            }
            catch (Exception ex)
            {
                conYes = false;
                fslog(ex);
                //Thread.Sleep(300000);
            }
            DT = new DataTable();
            SQLADA = new SqlDataAdapter(SQLcom);
            SQLADA.Fill(DT);
            return DT;
        }
        /// <summary>
        /// 对目标库操作
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void IsOpen(string strsql, string strconn)
        {
            try
            {
                SqlConnection pregcon = new SqlConnection(strconn);
                SqlCommand sqlcom = new SqlCommand(strsql, pregcon);
                pregcon.Open();
                sqlcom.ExecuteNonQuery();
                conYes = true;
                pregcon.Close();

            }
            catch (Exception ex)
            {
                conYes = false;
                fslog(ex);
                //Thread.Sleep(300000);
            }
        }

详细操作方法

/// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="sender"></param>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void DoWork_select()
        {

            //string strtb = System.Configuration.ConfigurationManager.AppSettings["strtable"].ToString();
            //string strupdt = System.Configuration.ConfigurationManager.AppSettings["updt"].ToString();
            //string strwhere = System.Configuration.ConfigurationManager.AppSettings["where"].ToString();


            while (tureOfalse)
            {
                ISopenCon();
                /*2015/2/2 调整:不能把菜单所有删除,没有的加入到server上去,已存在的过滤掉*/
                //DelPreg();
                //DataTable dt = IsOpenDT(strSQL, strdbHipis);

                //String[] tablenamekey = new String[3] { "dbo.sys_Menu", "dbo.sys_MenuInRoles", "dbo.sys_UsersInRoles" };//源数据库
                //String[] tablenamekeypreg = new String[3] { "dbo.sys_Menu_Copy", "dbo.sys_MenuInRoles_Copy", "dbo.sys_UsersInRoles_Copy" };//目标数据库

                //測试数据
                String[] tablenamekey = new String[1] { "dbo.sys_Menu" };//源数据库
                String[] tablenamekeypreg = new String[1] { "dbo.sys_Menu_Copy" };//目标数据库
                if (tablenamekey.Length > 0)
                {
                    for (int i = 0; i < tablenamekey.Length; i++)
                    {
                        string strtablenamekey = tablenamekey[i];
                        string strSQL1 = "select * from ";
                        strSQL1 += strtablenamekey;
                        strSQL1 += " where (1=1)";

                        insert_OR_update(strdbHipis, ref strSQL1, tablenamekeypreg[i]);
                    }
                }
            }
        }

        /// <summary>
        /// 插入,更新 最新数据到PIS数据库
        /// </summary>
        /// <param name="con">须要更新的表的连接字符串</param>
        /// <param name="lastSQL">最后SQL语句</param>
        /// <param name="tbName">须要操作的表</param>
        private void insert_OR_update(string strdbHipis, ref string strSQL, string tbName)
        {
            int a = 0;
            int b = 0;
            string lastSQL;
            DataTable dttb;
            DataTable dttb1;
            dttb = IsOpenDT(strSQL, strdbHipis);

            /*2015/2/2 调整:新增[进度条]追加显示载入信息*/
            progressBar1.Maximum = dttb.Rows.Count;//设置最大长度值
            progressBar1.Value = 0;//设置当前值
            progressBar1.Step = 1;//设置每次增长1条数据

            listBox1.Items.Add("连接源数据库成功......");
            listBox1.Items.Add("连接目标数据库成功......");
            listBox1.Items.Add("正在同步... ...");
            try
            {
                for (int i = 0; i < dttb.Rows.Count; i++)//依据推断条件循环更新数据库中的信息
                {
                    /*2015/2/2 调整:不能把菜单所有删除,没有的加入到server上去,已存在的过滤掉*/
                    lastSQL = "select * from " + tbName + " where sCode= '" + dttb.Rows[i][0].ToString().Trim() + "'";
                    //更新dttb1:一条记录 推断是否存在<过滤:新增>
                    /*測试数据库:源库和目标库一致 后期调整目标库连接字符串*/
                    dttb1 = IsOpenDT(lastSQL, strdbPreg);
                    //目标库中存在一条数据 取消
                    if (dttb1 != null && dttb1.Rows.Count > 0)
                    {
                        a = a + 1;
                        listBox1.Items.Add("已过滤" + a.ToString() + "条。");
                    }
                    else
                    {
                        string valuse = "";
                        string allcolumName = "";
                        for (int j = 0; j < dttb1.Columns.Count; j++)
                        {
                            if (j == dttb1.Columns.Count - 1)
                            {
                                allcolumName += dttb1.Columns[j].ColumnName.ToString();
                            }
                            else
                            {
                                allcolumName += dttb1.Columns[j].ColumnName.ToString() + ",";
                            }
                        }

                        //假设在线库中的时间为空则进行插入操作


                        for (int j = 0; j < dttb.Columns.Count; j++)
                        {
                            if (j == dttb.Columns.Count - 1)
                            {
                                valuse += "'" + dttb.Rows[i][j].ToString() + "'";
                            }
                            else
                            {
                                valuse += "'" + dttb.Rows[i][j] + "',";
                            }
                        }

                        lastSQL = "insert into " + tbName + " (" + allcolumName + ") values (" + valuse + ")";

                        IsOpen(lastSQL, strdbPreg);
                        b = b + 1;
                        listBox1.Items.Add("已插入" + b.ToString() + "条!");
                    }
                    /*2015/2/2 调整:新增[进度条]追加显示载入信息*/
                    progressBar1.Value += progressBar1.Step;//让进度条添加一次
                }
            }
            catch (Exception ex)
            {
                fslog(ex);
            }
            if ((a + b) > 0)
            {
                listBox1.Items.Add(tbName + "成功过滤" + a + "条数据条数据,成功插入" + b + "条数据。");
                listBox1.Items.Add(tbName + "同步数据成功。");
                //tureOfalse = false;
                preg.Abort();//线程运行标识:同步成功 关闭线程
                //UPINlog(a, b, tbName);
            }
            else
            {
                listBox1.Items.Add("");
            }

        }

此段代码相当于sqlhelper.cs功能【连接数据库】

/// <summary>
        /// 运行SQL语句
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>bool值,成功返回true,失败返回false</returns>
        public static bool ExecuteSql(string SQLString)
        {
            bool isSucc = false;
            using (SqlConnection connection = new SqlConnection("Data Source=192.168.44.161;Initial Catalog=NewHmFrameWork_QHS_SQ_QingHai;Persist Security Info=True;User ID=sa;Password=jqkj123$%^;"))
            {
                connection.Open();

                using (SqlTransaction trans = connection.BeginTransaction())
                {
                    using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                    {

                        cmd.Transaction = trans;
                        int rows = cmd.ExecuteNonQuery();
                        trans.Commit();
                        isSucc = true;


                    }
                }

            }
            return isSucc;
        }

初始化托盘图标

/// <summary>
        /// 初始化托盘图标
        /// </summary>
        public void InitTrayIcon()
        {

            // 设置托盘图标的菜单
            trayIcon.Icon = mNetTrayIcon;
            trayIcon.Text = "单击最大化程序";
            trayIcon.Visible = true;
            trayIcon.Click += new EventHandler(trayIcon_Click);
            MenuItem[] mnuItems = new MenuItem[4];
            mnuItems[0] = new MenuItem();
            mnuItems[0].Text = "显示";
            mnuItems[0].Click += new EventHandler(Item0_Click);

            mnuItems[1] = new MenuItem();
            mnuItems[1].Text = "隐藏";
            mnuItems[1].Click += new EventHandler(Item1_Click);

            mnuItems[2] = new MenuItem("-");

            mnuItems[3] = new MenuItem();
            mnuItems[3].Text = "退出程序";
            mnuItems[3].Click += new EventHandler(Item3_Click);
            trayIcon.ContextMenu = new ContextMenu(mnuItems);
        }

显示窗口,隐藏窗口。退出事件,点击托盘图标,单击X退出程序

/// <summary>
        /// 显示窗口
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void Item0_Click(object sender, EventArgs e)
        {
            this.Show();

        }

        /// <summary>
        /// 隐藏窗口
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void Item1_Click(object sender, EventArgs e)
        {
            this.Hide();
            this.trayIcon.ShowBalloonTip(5, "提示", "单击最大化程序", ToolTipIcon.Info);
        }

        /// <summary>
        /// 退出事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void Item3_Click(object sender, EventArgs e)
        {

            preg.Abort();
            Application.Exit();
        }

        /// <summary>
        /// 点击托盘图标
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void trayIcon_Click(object sender, EventArgs e)
        {
            this.Visible = true;
            this.WindowState = FormWindowState.Maximized;

        }
        /// <summary>
        /// 单击X退出程序
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void form_Closing(object sender, FormClosingEventArgs e)
        {
            if (one == false)
            {
                DialogResult dia = MessageBox.Show("是否真的退出程序!", " ", MessageBoxButtons.OKCancel);
                if (dia == DialogResult.Cancel)
                {
                    e.Cancel = true;
                    this.Show();
                    this.trayIcon.Visible = true;
                }
                if (dia == DialogResult.OK)
                {
                    one = true;
                    /*2015/2/2 调整:退出[未同步时。直接退出]*/
                    preg = new Thread(new ThreadStart(PREG));
                    preg.Start();
                    preg.Abort();
                    Application.Exit();
                }
            }
        }


效果图



posted @ 2017-08-13 11:36  yxysuanfa  阅读(526)  评论(0编辑  收藏  举报