cupdatetransition.cs

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

using System;
using System.Windows.Forms;
using System.Collections;
using System.Data.OleDb;

namespace DataC
{
	public struct TFieldAssociate
	{
		public string m_strTargetField;
		public string m_strSourceField;
		public string m_strTargetFieldType;
		public string m_strSourceFieldType;
	}
	/// <summary>
	/// 更新转换类
	/// 编制人：朱铭 2005-5-17
	/// 功能：实现更新转换，即在第一次导入数据之后，对剩余数据字段的导入。
	/// 成员变量：_alFieldAssociate---是一个TFieldAssociate的结构数组。每个元素保存着在更新转换时所需要的信息，即update中的where子句。
	/// </summary>
	public class CUpdateTransition:CTransition
	{
		protected 
			System.Collections.ArrayList _alFieldAssociate;
			System.Collections.Hashtable _htTargetFieldAndType;
		public CUpdateTransition()
		{
			_alFieldAssociate=new ArrayList();
			_htTargetFieldAndType=new Hashtable();
		}
		public System.Collections.ArrayList FieldAssociate
		{
			get { return _alFieldAssociate;}
			set { _alFieldAssociate=value;}
		}
		public System.Collections.Hashtable TargetFieldAndType
		{
			get { return _htTargetFieldAndType;}
			set { _htTargetFieldAndType=value;}
		}
		/// <summary>
		/// 更新转换函数。
		/// 问题：在关联键值（即原标的主键为空值）为空时的处理方法。
		/// </summary>
		public void UpdateTransStart()
		{
			ArrayList strsMBZDL=this.TargetFieldList;
			ArrayList strsMBZDLXL=this.TargetFieldListType;
			string strTCmd=null;
			string strSet;;
			string strWhere;
			string strValue="";
			if(this.SourceConn.State==System.Data.ConnectionState.Closed)
				this.SourceConn.Open();
			CError ce1=new CError();
			OleDbCommand dcY=this.SourceConn.CreateCommand();
			try
			{
				dcY.CommandText=this.GenerateSourceSql(ce1);
			}	
			catch(CError ce)
			{
				MessageBox.Show(ce.Msg);
				return;
			}
			OleDbDataReader drY=dcY.ExecuteReader();

			if(this.TargetConn.State==System.Data.ConnectionState.Closed)
				this.TargetConn.Open();
			OleDbCommand dcM=this.TargetConn.CreateCommand();
			while(drY.Read())
			{
				strSet=" set ";
				try
				{
					strTCmd="update "+this.TargetTable[0].ToString();
					for(int j=0;j<strsMBZDL.Count;j++)
					{
						strSet+=strsMBZDL[j].ToString();
						CFieldRelation relation=new CFieldRelation();
						relation=(CFieldRelation)this.FieldRelation[j];
						if(relation.TransType=="直接转换")
						{
							if(strsMBZDLXL[j].ToString()=="System.String")
							{
								if(drY.IsDBNull(j+this.FieldAssociate.Count))
									strValue="";
								else
									//strValue=(string)drY.GetValue(j+this.FieldAssociate.Count);
									strValue=drY[j+this.FieldAssociate.Count].ToString();
								strSet+="='"+strValue+"'";
							}
							else if(strsMBZDLXL[j].ToString()=="System.Decimal")
							{
								if(drY.IsDBNull(j+this.FieldAssociate.Count))
									strValue="0";
								else
									strValue=(string)drY.GetValue(j+this.FieldAssociate.Count);
								strSet+="="+strValue;
							}							
							else if(strsMBZDLXL[j].ToString()=="System.DateTime")
							{
								if(drY.IsDBNull(j+this.FieldAssociate.Count))
									strValue="";
								else
									strValue=(string)drY.GetValue(j+this.FieldAssociate.Count);
								strSet+="='"+strValue+"'";
							}
							else if (strsMBZDLXL[j].ToString()=="在此处添加新的字段类型")
							{
								//to do list 
							}
							else
							{
								MessageBox.Show("ERROR:*****不支持该类型的数据转换,请与开发商联系*****");
								return;
							}
						}
						else if(relation.TransType=="定值转换")
						{
							if(strsMBZDLXL[j].ToString()=="System.String")
							{
								if(drY.IsDBNull(j+this.FieldAssociate.Count))
									strValue="";
								else
									strValue=(string)relation.ValueMap[drY.GetString(j+this.FieldAssociate.Count)];
								strSet+="='"+strValue+"'";
							}
							else if(strsMBZDLXL[j].ToString()=="System.Decimal")
							{
								if(drY.IsDBNull(j+this.FieldAssociate.Count))
									strValue="0";
								else
									strValue=(string)relation.ValueMap[drY.GetValue(j+this.FieldAssociate.Count)];
								strSet+="="+strValue;
							}
							else if(strsMBZDLXL[j].ToString()=="System.DateTime")
							{
								if(drY.IsDBNull(j+this.FieldAssociate.Count))
									strValue="";
								else
									strValue=(string)relation.ValueMap[drY.GetValue(j+this.FieldAssociate.Count)];
								strSet+="='"+strValue+"'";
							}
							else if (strsMBZDLXL[j].ToString()=="在此处添加新的字段类型")
							{
								//to do list 
							}
							else
							{
								MessageBox.Show("ERROR:*****不支持该类型的数据转换,请与开发商联系*****");
								return;
							}
						}
						if(j<strsMBZDL.Count-1)
							strSet+=",";
					}
					strWhere=" where ";

					for(int i=0;i<this.FieldAssociate.Count;i++)
					{
						TFieldAssociate t=(TFieldAssociate)this.FieldAssociate[i];
						strWhere+=t.m_strTargetField+"=";
						if(t.m_strTargetFieldType=="System.String")
						{
							if(drY.IsDBNull(i))
							{
								throw new System.InvalidCastException("原关联键值为空，无法跟新值");
							}
							else
							{
								strWhere+="'"+drY.GetString(i)+"'";
							}
						}
						else
						{
						}
					}
					strTCmd+=strSet+strWhere;
					dcM.CommandText=strTCmd;
					dcM.CommandType=System.Data.CommandType.Text;
					dcM.ExecuteNonQuery();
				}
				catch(System.InvalidCastException e)
				{
					string strLog=e.Message+"---"+strTCmd+"\n";
					int hwnd=FindWindow(null,"数据转换工具");
					SendMessage(hwnd,WM_SendLog,System.Runtime.InteropServices.Marshal.StringToHGlobalAnsi(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),strLog.Length);
				}
			}
			drY.Close();
			dcM.Dispose();
			MessageBox.Show("数据转换完成 ");
		}
		public string GenerateSourceSql(CError ce)
		{
			System.Collections.ArrayList srcField=this.SourceFieldList;
			string strRtn="";
			if(srcField.Count<=0||this.FieldAssociate.Count<=0)
			{
				ce.Msg="源字段为空或关联键为空！";
				throw ce;
				return strRtn;
			}
			strRtn="select ";
			for(int i=0;i<this.FieldAssociate.Count;i++)
			{
				TFieldAssociate t=(TFieldAssociate)this.FieldAssociate[i];
				strRtn+=t.m_strSourceField+",";
			}
			strRtn+=srcField[0].ToString();
			for(int i=1;i<srcField.Count;i++)
			{
				strRtn+=","+srcField[i].ToString();
			}
			strRtn+=" from "+this.SourceTable[0].ToString();
			return strRtn;
		}
	}
}