数据同步工具,一看你就懂的
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using System.Timers;
using MySql.Data.MySqlClient;
using System.Data.SqlClient;
using System.Data.OracleClient;
using IBM.Data.DB2;
namespace DBSync
{
public partial class main : Form
{
static TaskStruct[] Taskpool = null;
static int taskCount;
static System.DateTime appStartTime;
static string selectTaskListid = "-1";
public main()
{
InitializeComponent();
}
private void testmysql(int id)
{
try
{
DB2Connection db2connHandle = new DB2Connection("Database=db2qas;UserID=db2qas; Password=xqlzs-2011;Server=172.16.1.247");
db2connHandle.Open();
//XmlElement xe;
}
catch (System.Exception e)
{
MessageBox.Show( e.Message.ToString() );
}
finally
{
MessageBox.Show("xx");
}
/*
for (int i = 0; i <= taskCount - 1; i++)
{
if (Taskpool[i].id==id)
{
TaskExecuteFactoryByTaskObject(Taskpool[i]);
}
}*/
}
private void Form1_Load(object sender, EventArgs e)
{
// testmysql(1);
appStartTime = System.DateTime.Now;
this.listView1.BeginUpdate();
this.listView1.View = View.Details;
listView1.Columns.Add("任务编号", 80, HorizontalAlignment.Left);
listView1.Columns.Add("任务名称", 80, HorizontalAlignment.Left);
listView1.Columns.Add("激活状态", 80, HorizontalAlignment.Left);
listView1.Columns.Add("任务状态", 80, HorizontalAlignment.Left);
this.listView1.FullRowSelect = true;//选择一个单元格就选择一行
this.listView1.GridLines = true;
this.listView1.EndUpdate();
this.listView2.BeginUpdate();
this.listView2.View = View.Details;
listView2.Columns.Add("信息", 600, HorizontalAlignment.Left);
this.listView2.FullRowSelect = true;//选择一个单元格就选择一行
this.listView2.GridLines = true;
this.listView2.EndUpdate();
InitTaskPools();
CreateTimer();
CreateCheckTaskPoolTimer();
// testmysql(0);
}
private void InitTaskPools()
{
taskCount = GetXmlNodeCount();
XmlElement xe;
Taskpool = new TaskStruct[taskCount];
for (int i = 0; i <= taskCount - 1; i++)
{
xe = GetXmlNodeAttribById(i);
Taskpool[i] = new TaskStruct();
Taskpool[i].id = System.Convert.ToInt32(xe.GetAttribute("id"));
Taskpool[i].taskName = xe.GetAttribute("taskname");
Taskpool[i].taskDotype = System.Convert.ToInt32( xe.GetAttribute("taskdotype") );
Taskpool[i].actionType = System.Convert.ToInt32(xe.GetAttribute("actiontype"));
Taskpool[i].actionTime = xe.GetAttribute("actiontime");
Taskpool[i].sourceData = System.Convert.ToInt32(xe.GetAttribute("sourcedata"));
Taskpool[i].sourceTable = xe.GetAttribute("sourcetable");
Taskpool[i].sourceField = xe.GetAttribute("sourcefield");
Taskpool[i].targetData = System.Convert.ToInt32(xe.GetAttribute("targetdata"));
Taskpool[i].targetTable = xe.GetAttribute("targettable");
Taskpool[i].targetField = xe.GetAttribute("targetfield");
//Taskpool[i].lastTime = "1";
Taskpool[i].sourceFieldType = xe.GetAttribute("sourcefieldtype");
Taskpool[i].targetFieldType = xe.GetAttribute("targetfieldtype");
this.listView1.BeginUpdate();
ListViewItem lv = new ListViewItem(xe.GetAttribute("id"));
lv.SubItems.Add(xe.GetAttribute("taskname"));
lv.SubItems.Add("任务未激活");
lv.SubItems.Add("任务已停止");
listView1.Items.Add(lv);
this.listView1.EndUpdate();
}
}
/// <summary>
///任务执行工厂
/// </summary>
/// <param name="taskObj"></param>
private void TaskExecuteFactoryByTaskObject(TaskStruct taskObj)
{
//先判断任务动作类型
//构造SQL句子
if ( taskObj.taskDotype == 2 )//数据一致类型
{
//1:清空目标
switch (taskObj.targetData)
{
case 1://SQLSERVER
break;
case 2://MYSQL
string dbchar = "truncate " + taskObj.targetTable;
IMYSQL MYSQL = (IMYSQL)GetDatabaseObjectFactoryByTypeName("MYSQL");
if ( !MYSQL.Open() )
{
MessageBox.Show(MYSQL.errString);
}
else
{
MYSQL.ExecuteNonQuery(dbchar);
}
break;
case 3://ORA
string dbchar3 = "truncate table " + taskObj.targetTable;
IORACLE ORA = new IORACLE();
ORA.Open();
if (ORA.ExecuteNonQuery(dbchar3)<=0)
{
MessageBox.Show(ORA.errString);
}
break;
}
//2:读出源数据
switch (taskObj.sourceData)
{
case 1://SQLSERVER
string dbchar = "select " + taskObj.sourceField + " from " + taskObj.sourceTable +" order by id asc";
ISQLSERVER SQLSERVER = (ISQLSERVER)GetDatabaseObjectFactoryByTypeName("SQLSERVER");
if ( SQLSERVER.Open() )
{
// SqlDataReader sourceDatareader = SQLSERVER.GetReader(dbchar);
//数据集
DataTable sourceTable = SQLSERVER.GetDataTable(dbchar);
InsertDataToTarget(taskObj, sourceTable);
}
break;
case 2://MYSQL
break;
}
}
}
private void InsertDataToTarget(TaskStruct taskObj,DataTable sourceTableList)
{
switch (taskObj.targetData)
{
case 1://SQLSERVER
//string dbchar = "select " + taskObj.sourceField + " from " + taskObj.sourceTable;
break;
case 2://MYSQL
for (int i = 0; i < sourceTableList.Rows.Count; i++)
{
DataRow row = sourceTableList.Rows[i];
//判断数据类型没有做处理,直接从外部获取吧~都凌晨5点了。。
string dbchar = "insert into "+ taskObj.targetTable+"("+taskObj.targetField+")value(";
//每个数据格式假设如:1,xyz,对每行值类型判断,移动列时获取列数
for (int k = 0; k < sourceTableList.Columns.Count;k++ )
{
if (row[k].GetType().ToString() == "System.String")
{
dbchar += "'" + row[k]+ "'";
if (k < (sourceTableList.Columns.Count-1))
{
dbchar += ",";
}
}
else
{
dbchar += "" + row[k] + "";
if (k < (sourceTableList.Columns.Count-1))
{
dbchar += ",";
}
}
}
dbchar+=")";
IMYSQL MYSQL = new IMYSQL();
MYSQL.Open();
if ( MYSQL.ExecuteNonQuery(dbchar)<=0 )
{
return;
//MessageBox.Show(MYSQL.errString);
}
}
break;
case 3:
for (int i = 0; i < sourceTableList.Rows.Count; i++)
{
DataRow row = sourceTableList.Rows[i];
//判断数据类型没有做处理,直接从外部获取吧~都凌晨5点了。。
string dbchar = "insert into " + taskObj.targetTable + "(" + taskObj.targetField + ")values(";
//每个数据格式假设如:1,xyz,对每行值类型判断,移动列时获取列数
for (int k = 0; k < sourceTableList.Columns.Count; k++)
{
if (row[k].GetType().ToString() == "System.String")
{
dbchar += "'" + row[k] + "'";
if (k < (sourceTableList.Columns.Count - 1) )
{
dbchar += ",";
}
}
else
{
dbchar += "" + row[k] + "";
if (k < (sourceTableList.Columns.Count - 1))
{
dbchar += ",";
}
}
}
dbchar += ")";
IORACLE ORA = new IORACLE();
if (ORA.Open()!=null)
{
if (ORA.ExecuteNonQuery(dbchar) <= 0)
{
MessageBox.Show(ORA.errString);
return;
}
}else
{
MessageBox.Show(ORA.errString);
return;
}
}
break;
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
private object GetDatabaseObjectFactoryByTypeName(string dbtype)
{
object db = null;
if (dbtype=="MYSQL")
{
IMYSQL MYSQL = new IMYSQL();
db = MYSQL;
}
else if (dbtype == "SQLSERVER")
{
ISQLSERVER SQLSERVER = new ISQLSERVER();
db = SQLSERVER;
}
return db;
}
private XmlElement GetXmlNodeAttribById(int id)
{
string sid = System.Convert.ToString(id);
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load("task.xml");
XmlNode xmlNode = xmlDoc.SelectSingleNode("TASKROOT");
XmlNodeList xnl = xmlNode.ChildNodes;
foreach (XmlNode xnf in xnl)
{
XmlElement xes = (XmlElement)xnf;
if (xes.GetAttribute("id").ToString() == sid)
{
return xes;
}
}
return null;
}
private int GetXmlNodeCount()
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load("task.xml");
XmlNode xmlNode = xmlDoc.SelectSingleNode("TASKROOT");
XmlNodeList xnl = xmlNode.ChildNodes;
return xnl.Count;
}
//此函数的目的是每隔10秒钟检测一次任务最后执行时间
private void CreateTimer()
{
System.Timers.Timer aTimer = new System.Timers.Timer();
aTimer.Elapsed += new ElapsedEventHandler( OnTimedEvent );
aTimer.Interval = 10000;
aTimer.Enabled = true;
}
private void CreateCheckTaskPoolTimer()
{
System.Timers.Timer taskTimer = new System.Timers.Timer();
taskTimer.Elapsed += new ElapsedEventHandler( OnTaskTimedEvent );
taskTimer.Interval = 30;
taskTimer.Enabled = true;
}
private static void OnTimedEvent(object sender, EventArgs e)
{
//MessageBox.Show("xxxxx");
}
//定义更新控件委托
private delegate void UpdateTaskInfoList(string info);
private void UpdateTaskInfoListFun(string info)
{
this.listView2.BeginUpdate();
ListViewItem lv = new ListViewItem(info);
listView2.Items.Add(lv);
this.listView2.EndUpdate();
}
private void OnTaskTimedEvent(object sender, EventArgs e)
{
for (int i = 0; i < taskCount;i++ )
{
if (Taskpool[i].activeStated != false && Taskpool[i].taskThreadLock!=false)
{
if ( Taskpool[i].actionType == 1 )//按时间间隔
{
TimeSpan ts1 = new TimeSpan(Taskpool[i].lastTime.Ticks);
TimeSpan ts2 = new TimeSpan(System.DateTime.Now.Ticks);
TimeSpan ts = ts2 - ts1;
if ( ts.Seconds >= Convert.ToInt32(Taskpool[i].actionTime) )
{
Taskpool[i].lastTime = System.DateTime.Now;
Taskpool[i].taskThreadLock = true;//开启线程锁
this.listView2.BeginInvoke(new UpdateTaskInfoList(UpdateTaskInfoListFun), "任务" + Taskpool[i].taskName+"正在执行");
//投递到工厂执行 考虑多线程
TaskExecuteFactoryByTaskObject( Taskpool[i] );
this.listView2.BeginInvoke(new UpdateTaskInfoList(UpdateTaskInfoListFun), "任务" + Taskpool[i].taskName + "执行完成");
}
}
// this.listView2.Update();
}
}
}
private void listView1_MouseClick(object sender,EventArgs e)
{
if (this.listView1.SelectedItems != null) //判断ListView控件是否有项目选中
{
//保存当前选中的任务ID
selectTaskListid = this.listView1.SelectedItems[0].SubItems[0].Text;
}
}
//激活任务
private void button2_Click(object sender, EventArgs e)
{
if (selectTaskListid=="-1")
{
MessageBox.Show("未选择任何任务!","提示");
return ;
}
//遍历任务列表
string temp = selectTaskListid;
for ( int i = 0; i < taskCount; i++ )
{
if ( Taskpool[i].id == Convert.ToInt32(temp) )
{
Taskpool[i].activeStated = true;
Taskpool[i].lastTime = System.DateTime.Now;
}
}
//重新更新LIST1控件
listView1.Items.Clear();
for (int i = 0; i < taskCount; i++)
{
this.listView1.BeginUpdate();
ListViewItem lv = new ListViewItem( Taskpool[i].id.ToString() );
lv.SubItems.Add( Taskpool[i].taskName );
if (Taskpool[i].activeStated == false)
{
lv.SubItems.Add("任务已暂停");
}
else
{
lv.SubItems.Add("任务已激活");
}
lv.SubItems.Add("任务已停止");
listView1.Items.Add(lv);
this.listView1.EndUpdate();
}
}
private void button3_Click(object sender, EventArgs e)
{
if (selectTaskListid == "-1")
{
MessageBox.Show("未选择任何任务!", "提示");
return;
}
string temp = selectTaskListid;
//遍历任务列表
for (int i = 0; i < taskCount; i++)
{
if (Taskpool[i].id == Convert.ToInt32(temp))
{
Taskpool[i].activeStated = false;
// Taskpool[i].lastTime = System.DateTime.Now;
}
}
//重新更新LIST1控件
listView1.Items.Clear();
for (int i = 0; i < taskCount; i++)
{
this.listView1.BeginUpdate();
ListViewItem lv = new ListViewItem(Taskpool[i].id.ToString());
lv.SubItems.Add(Taskpool[i].taskName);
if (Taskpool[i].activeStated == false)
{
lv.SubItems.Add("任务已暂停");
}
else
{
lv.SubItems.Add("任务已激活");
}
lv.SubItems.Add("任务已停止");
listView1.Items.Add(lv);
this.listView1.EndUpdate();
}
}
}
}