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();
}
}
}
7084
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
