ctransition.cs

可将ACCESS数据库导入到Oracle数据库

using System;
using System.Collections;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Runtime.InteropServices;
namespace DataC
{
	/// <summary>
	/// Summary description for TransInfo.
	/// </summary>
	public class CTransition
	{
		protected 
			const int WM_SendLog=0x400;//发送日志消息
			const int WM_TransOver=0x500;//转换完成
		protected  
			System.Collections.ArrayList _alSourceTable;
			System.Collections.ArrayList _alTargetTable;
			System.Collections.ArrayList _alFieldRelation;
			System.Collections.ArrayList _alSourceFieldList;
			System.Collections.ArrayList _alTargetFieldList;
			System.Collections.ArrayList _alTargetFieldListType;//目标字段列类型。
		protected 
			System.Data.OleDb.OleDbConnection  _oleSourceConn=null;
			System.Data.OleDb.OleDbConnection  _oleTargetConn=null;
		public CTransition()
		{
			//
			// TODO: Add constructor logic here
			//
			_alSourceTable=new System.Collections.ArrayList(1);//现在只支持1对1的转换，所以表数组只要一个空间。
			_alTargetTable=new System.Collections.ArrayList(1);
			_alFieldRelation = new System.Collections.ArrayList();
			_alSourceFieldList=new System.Collections.ArrayList();
			_alTargetFieldList=new System.Collections.ArrayList();
			_alTargetFieldListType=new System.Collections.ArrayList();
		}
		public System.Data.OleDb.OleDbConnection  SourceConn
		{
			get	{ return _oleSourceConn;}
			set { _oleSourceConn = value;}
		}
		public System.Data.OleDb.OleDbConnection  TargetConn
		{
			get { return _oleTargetConn;}
			set { _oleTargetConn = value;}
		}
		public System.Collections.ArrayList SourceTable
		{
			get { return _alSourceTable;}
			set { _alSourceTable = value;}
		}
		public System.Collections.ArrayList TargetTable
		{
			get { return _alTargetTable;}
			set { _alTargetTable = value;}
		}
		public System.Collections.ArrayList FieldRelation
		{
			get { return _alFieldRelation;}
			set { _alFieldRelation = value;}
		}
		public System.Collections.ArrayList SourceFieldList
		{
			get
			{
				_alSourceFieldList.Clear();
				for(int i=0;i<this.FieldRelation.Count;i++)
				{
					CFieldRelation relation=(CFieldRelation)this.FieldRelation[i];
					_alSourceFieldList.Add(relation.SourceField);
				}
				return _alSourceFieldList;
			}
		}
		public System.Collections.ArrayList TargetFieldList
		{
			get
			{
				_alTargetFieldList.Clear();
				_alTargetFieldListType.Clear();
				for(int i=0;i<this.FieldRelation.Count;i++)
				{
					CFieldRelation relation=(CFieldRelation)this.FieldRelation[i];
					_alTargetFieldList.Add(relation.TargetField);
					_alTargetFieldListType.Add(relation.TargetType);
				}
				return _alTargetFieldList;
			}
		}
		public virtual string GenerateSourceSql(CError ce)
		{
			ArrayList srcField=this.SourceFieldList;
			string strRtn="";
			if(srcField.Count<=0)
			{
				ce.Msg="Error *****没有设置要进行转换的表和字段，请重新设置！*****";
				throw ce;
			}
			strRtn="select ";
			strRtn+=srcField[0].ToString();
			for(int i=1;i<srcField.Count;i++)
			{
				strRtn+=","+srcField[i].ToString();
			}
			strRtn+=" from "+this.SourceTable[0].ToString();
			return strRtn;
		}
		public System.Collections.ArrayList TargetFieldListType
		{
			get
			{
				return _alTargetFieldListType;
			}
		}
		/// <summary>
		/// 导入发送消息的必要函数；
		/// </summary>
		/// <param name="hwnd"></param>
		/// <param name="wMsg"></param>
		/// <param name="wParam"></param>
		/// <param name="lParam"></param>
		/// <returns></returns>

		[System.Runtime.InteropServices.DllImport("user32.dll",CharSet=CharSet.Auto,EntryPoint="SendMessageA")]
		public static extern int SendMessage(int hwnd,int wMsg,System.IntPtr wParam,int lParam);
		[System.Runtime.InteropServices.DllImport("user32.dll",EntryPoint="FindWindow")]
		public static extern int FindWindow(string lpClassName ,string lpWindowName);

		public void TransitionBegin()
		{
			ArrayList strsMBZDL=this.TargetFieldList;
			ArrayList strsMBZDLXL=this.TargetFieldListType;
			string strTCmd=null;
			try
			{
				if(this.SourceConn.State==System.Data.ConnectionState.Closed)
					this.SourceConn.Open();
			}
			catch(System.Data.OleDb.OleDbException olee)
			{
				MessageBox.Show("Error *****没有正确的连接到源数据库上，请重新设置！*****");
				return;
			}
			OleDbCommand dcY=this.SourceConn.CreateCommand();
			try
			{
				if(this.TargetConn.State==System.Data.ConnectionState.Closed)
					this.TargetConn.Open();			
			}
			catch(System.Data.OleDb.OleDbException)
			{
				MessageBox.Show("Error *****没有正确的连接到目标数据库上，请重新设置！*****");
				return;
			}
			catch(System.InvalidOperationException)
			{
				MessageBox.Show("Error *****没有正确的连接到目标数据库上，请重新设置！*****");
				return;
			}
			CError cer=new CError();
			try
			{
				dcY.CommandText=this.GenerateSourceSql(cer);
			}
			catch(CError ce)
			{
				MessageBox.Show(ce.Msg);
				return;
			}
			OleDbDataReader drY=dcY.ExecuteReader();
			OleDbCommand dcM=this.TargetConn.CreateCommand();
			while(drY.Read())
			{
				try
				{
					strTCmd="insert into "+this.TargetTable[0].ToString()+" ("+strsMBZDL[0].ToString();
					for(int j=1;j<strsMBZDL.Count;j++)
					{
						strTCmd+=","+strsMBZDL[j].ToString();
					}
					strTCmd+=")";
					strTCmd+=" values(";
			
					for(int l=0;l<drY.FieldCount;l++)
					{
						CFieldRelation relation=(CFieldRelation)this.FieldRelation[l];
						if(relation.TransType=="直接转换")
						{
							if(strsMBZDLXL[l].ToString()=="System.String")
							{
								if(drY.IsDBNull(l))
									strTCmd+="' '";
								else
									strTCmd+="'"+drY.GetString(l)+"'";
							}
							else if(strsMBZDLXL[l].ToString()=="System.Decimal")
							{
								if(drY.IsDBNull(l))
									strTCmd+="0";
								else
									strTCmd+=drY.GetString(l);
							}
							else if(strsMBZDLXL[l].ToString()=="System.DateTime")
							{
								if(drY.IsDBNull(l))
									strTCmd+="''";
								else
									strTCmd+="'"+drY.GetString(l)+"'";
							}
							else if (strsMBZDLXL[l].ToString()=="在此处添加新的字段类型")
							{
								//to do list 
							}
							else
							{
								MessageBox.Show("ERROR:*****不支持该类型的数据转换,请与开发商联系*****");
								return;
							}

						}
						else if(relation.TransType=="定值转换")
						{
							if(strsMBZDLXL[l].ToString()=="System.String")
							{
								if(drY.IsDBNull(l))
								{
									strTCmd+="' '";
								}
								else
								{
									string t=relation.ValueMap[drY.GetString(l)].ToString();
									strTCmd+="'"+t+"'";
								}
							}
							else if(strsMBZDLXL[l].ToString()=="System.Decimal")
							{
								if(drY.IsDBNull(l))
									strTCmd+="0";
								else
								{
									string t=relation.ValueMap[drY.GetString(l)].ToString();
									strTCmd+="'"+t+"'";
								}
							}
							else if(strsMBZDLXL[l].ToString()=="System.DateTime")
							{
								if(drY.IsDBNull(l))
									strTCmd+="''";
								else
								{
									string t=relation.ValueMap[drY.GetString(l)].ToString();
									strTCmd+="'"+t+"'";
								}
							}
							else if (strsMBZDLXL[l].ToString()=="在此处添加新的字段类型")
							{
								//to do list 
							}
							else
							{
								MessageBox.Show("ERROR:*****不支持该类型的数据转换,请与开发商联系*****");
								return;
							}

						}
						if(l<drY.FieldCount-1)
							strTCmd+=",";
					}
					strTCmd+=")";
					dcM.CommandText=strTCmd;
					dcM.CommandType=System.Data.CommandType.Text;
					dcM.ExecuteNonQuery();
				}
				catch(System.InvalidCastException e)
				{
					string strLog=e.Message+"---"+strTCmd+"\n";
					//CErrorMsg em=new CErrorMsg(strLog);
					int hwnd=FindWindow(null,"数据转换工具");
					//SendMessage(hwnd,WM_SendLog,strLog,strLog.Length);
				}
				catch(System.Data.OleDb.OleDbException e)
				{
					string strLog=e.Message+"---"+strTCmd+"\n";
					int hwnd=FindWindow(null,"数据转换工具");
					SendMessage(hwnd,WM_SendLog,System.Runtime.InteropServices.Marshal.StringToHGlobalAnsi(strLog),0);
				}
				catch(System.Exception e)
				{
					string strLog=e.Message+"---"+strTCmd+"\n";
					int hwnd=FindWindow(null,"数据转换工具");
					SendMessage(hwnd,WM_SendLog,System.Runtime.InteropServices.Marshal.StringToHGlobalAnsi(strLog),0);
				}
			}
			drY.Close();
			dcM.Dispose();
			MessageBox.Show("数据转换完成 ");
		}
	}

	public class CError:System.Exception
	{
		private 
			string _strMsg;
		public CError(){}
		public CError(string msg)
		{
			_strMsg=msg;
		}
		public string Msg
		{
			get { return _strMsg;}
			set { _strMsg=value;}
		}
	}

}