SERVER数据转换ORACLE问题

大家做系统更新或者升级都可能要触及到数据的转换。对于Oracle中的数据转换可以用写存储过程来放之游标，将你要的数据一条一条的转换。而对于SERVER虽然你可以将它转换到ORACLE中（升级的方式），但有问题你会发现有的表是不可以直接转换的！所以我就用代码控制直接转换！也是一种比较轻易控制轻易写的方法！首先你要在程序中建立连接(server的)string strCon='workstation id=XJ;packet size=4096;user id=sa;data source='automapautomap';persist security info=True;initial catalog=bgxtrmt;passWord=sa';string strTxt = 'SELECT tworder_code, stru_b, layer_b, area_ub, area_b, east, south, west, north, area_sb, densy_b, grean_b, park, park_bike, other_area,grean_b,sp_build_p FROM bgxtpab2_1';SqlConnection SqlCon = new SqlConnection(strCon);SqlCommand SqlCmd = new SqlCommand(strTxt,SqlCon);SqlCon.Open();然后将你要读的数据用dataReader.reader方法读进来！SqlDataReader SqlDR = SqlCmd.ExecuteReader(CommandBehavior.CloseConnection);while(SqlDR.Read()){try{OracleCon.Open();Bgxtpab2_1 bg = new Bgxtpab2_1();//Doc d = new Doc();//int i = -1;bg.Tworder_Code = SqlDR.GetString(0).Trim();bg.Stru_B = SqlDR.IsDBNull(1)?'':SqlDR.GetString(1).Trim();bg.Layer_B = SqlDR.IsDBNull(2)?0:SqlDR.GetInt32(2);bg.Area_UB = SqlDR.IsDBNull(3)?'':SqlDR.GetString(3);bg.Area_B = SqlDR.IsDBNull(4)?'':SqlDR.GetString(4);bg.East_B = SqlDR.IsDBNull(5)?'':SqlDR.GetString(5).Trim();bg.South_B = SqlDR.IsDBNull(6)?'':SqlDR.GetString(6).Trim();bg.West_B = SqlDR.IsDBNull(7)?'':SqlDR.GetString(7).Trim();bg.North_B = SqlDR.IsDBNull(8)?'':SqlDR.GetString(8).Trim();//bg.Layer_BD = SqlDR.IsDBNull(++i)?0.0F:SqlDR.GetInt32(i);bg.Area_SB = SqlDR.IsDBNull(9)?'':SqlDR.GetString(9);bg.Densy_B = SqlDR.IsDBNull(10)?'':SqlDR.GetString(10);bg.Park = SqlDR.IsDBNull(11)?0.0F:SqlDR.GetFloat(11);bg.Park_Bike = SqlDR.IsDBNull(12)?0.0F:SqlDR.GetFloat(12);bg.OTher_Area = SqlDR.IsDBNull(13)?0.0F:SqlDR.GetFloat(13); bg.Grean_B = SqlDR.IsDBNull(14)?0.0F:SqlDR.GetFloat(14); bg.Sp_build_P = SqlDR.IsDBNull(15)?'':SqlDR.GetString(15); 这样你就获得了你要的数据，还要提到的是！你必须为你想读的表写个数据表的类！using System;namespace ProjectBulid{/// <summary>/// Bgxtpab2_1工程数据表/// </summary>public class Bgxtpab2_1{string tworder_code;//string project_b;string densy_b; string stru_b; int layer_b; string area_ub; string area_b; string usage_b; string east; string south; string west; string north; string area_sb; float grean_b; float park; float park_bike; // decimal other_area_b; string sp_build_p; float other_area; public Bgxtpab2_1(){tworder_code = '';//project_b= '';stru_b = '';layer_b = 0;area_ub = '';area_b = '';usage_b = '';east = ''; south= '';west = '';north = '';//layer_db = 0;area_sb = '';densy_b = '';grean_b = 0.0F;park = 0.0F;park_bike = 0.0F;sp_build_p = '';other_area = 0.0F;}public string Tworder_Code{get{return tworder_code;}set{tworder_code = value;}}public string Densy_B{get{return densy_b;}set{densy_b = value;}}// public int Project_B// {// get// {// return project_b;// }// set// {// project_b = value;// }// }public string Stru_B{get{return stru_b;}set{stru_b = value;}}public int Layer_B{get{return layer_b;}set{layer_b = value;}}public string Area_UB{get{return area_ub;}set{area_ub = value;}}public string Area_B{get{return area_b;}set{area_b = value;}}public string Usage_B{get{return usage_b;}set{usage_b = value;}}public string East_B{get{return east;}set{east = value;}}public string South_B{get{return south;}set{south = value;}}public string West_B{get{return west;}set{west = value;}}public string North_B{get{return north;}set{north = value;}}// public int Layer_BD// {// get// {// return layer_db;// }// set// {// layer_db = value;// }// }public string Area_SB{get{return area_sb;}set{area_sb = value;}}public float Grean_B{get{return grean_b;}set{grean_b = value;}}public float Park{get{return park;}set{park = value;}}public float Park_Bike{get{return park_bike;}set{park_bike = value;}}public string Sp_build_P{get{return sp_build_p;}set{sp_build_p = value;}}public float OTher_Area{get{return other_area;}set{other_area = value;}}}}下面要做的就是往oracle中写你要的数据了！建立连接:string OracleString = 'user id=constrUCt;data source=oracle;password=rose';string OracleText = “在这里写你要插入的sql语句“OracleConnection OracleCon = new OracleConnection(OracleString);OracleCommand OracleCmd = new OracleCommand(OracleText,OracleCon);然后帮oracle表中构造字段:#region BuildingEnnginger参数设置OracleCmd.Parameters.Clear();OracleCmd.Parameters.Add(':DOCID',OracleType.Int32);OracleCmd.Parameters.Add(':CONSTRUCTDENSITY',OracleType.Float);OracleCmd.Parameters.Add(':BUILDINGTOTALHEIGHT',OracleType.Float); OracleCmd.Parameters.Add(':GREENAREA',OracleType.Float);OracleCmd.Parameters.Add(':PARKINGAREA',OracleType.Float);OracleCmd.Parameters.Add(':MINIPARKINGAREA',OracleType.Float);OracleCmd.Parameters.Add(':OTHERESTABLISHMENTAREA',OracleType.Float);OracleCmd.Parameters.Add(':CUBAGERATE',OracleType.Float);OracleCmd.Parameters.Add(':LANDUSAGE',OracleType.Int32);OracleCmd.Parameters.Add(':AGROUNDFLOORCOUNT',OracleType.Int32);OracleCmd.Parameters.Add(':UNDERGROUNDFLOORCOUNT',OracleType.Int32);OracleCmd.Parameters.Add(':EAST',OracleType.VarChar);OracleCmd.Parameters.Add(':WEST',OracleType.VarChar);OracleCmd.Parameters.Add(':SOUTH',OracleType.VarChar);OracleCmd.Parameters.Add(':NORTH',OracleType.VarChar);OracleCmd.Parameters.Add(':ORIGINALUSELANDAREA',OracleType.VarChar);OracleCmd.Parameters.Add(':ORIGINALBUILDINGAREA',OracleType.Float);OracleCmd.Parameters.Add(':ORIGINALSTRUCTURETYPE',OracleType.VarChar);OracleCmd.Parameters.Add(':ORIGINALFLOORAMOUNT',OracleType.Int32);OracleCmd.Parameters.Add(':ORIGINALEAST',OracleType.VarChar);OracleCmd.Parameters.Add(':ORIGINALWEST',OracleType.VarChar);OracleCmd.Parameters.Add(':ORIGINALSOUTH',OracleType.VarChar);OracleCmd.Parameters.Add(':ORIGINALNORTH',OracleType.VarChar);OracleCmd.Parameters.Add(':BUILDINGMEMO',OracleType.VarChar);OracleCmd.Parameters.Add(':ALLPLINTHAREA',OracleType.Float);OracleCmd.Parameters.Add(':DESIGNAGROUNDFLOOR',OracleType.Int32);OracleCmd.Parameters.Add(':DESIGNUNDERGROUNDFLOOR',OracleType.Int32);OracleCmd.Parameters.Add(':DESIGNSPECIALTIESFLOOR',OracleType.VarChar);#endregion #region 参数附值OracleCmd.Parameters[':DOCID'].Value = SelectInsert(bg.Tworder_Code).ToString(); //这里是传参数进去获得想得到的数据后面的就是你俯值的具体代码了；（ 如bg.Densy_B）if(bg.Densy_B.ToString() == '')OracleCmd.Parameters[':CONSTRUCTDENSITY'].Value = DBNull.Value;elseOracleCmd.Parameters[':CONSTRUCTDENSITY'].Value = bg.Densy_B;OracleCmd.Parameters[':BUILDINGTOTALHEIGHT'].Value = DBNull.Value;if(bg.Grean_B.ToString() == '')OracleCmd.Parameters[':GREENAREA'].Value = DBNull.Value;elseOracleCmd.Parameters[':GREENAREA'].Value = bg.Grean_B;if(bg.Park.ToString() =='')OracleCmd.Parameters[':PARKINGAREA'].Value = DBNull.Value;elseOracleCmd.Parameters[':PARKINGAREA'].Value = bg.Park;if(bg.Park_Bike.ToString() =='')OracleCmd.Parameters[':MINIPARKINGAREA'].Value = DBNull.Value;elseOracleCmd.Parameters[':MINIPARKINGAREA'].Value = bg.Park_Bike;if(bg.OTher_Area.ToString() == '')OracleCmd.Parameters[':OTHERESTABLISHMENTAREA'].Value = DBNull.Value;elseOracleCmd.Parameters[':OTHERESTABLISHMENTAREA'].Value = bg.OTher_Area;OracleCmd.Parameters[':CUBAGERATE'].Value = DBNull.Value;OracleCmd.Parameters[':LANDUSAGE'].Value = DBNull.Value; //词典if(bg.Layer_B.ToString() == '')OracleCmd.Parameters[':AGROUNDFLOORCOUNT'].Value = DBNull.Value;elseOracleCmd.Parameters[':AGROUNDFLOORCOUNT'].Value = bg.Layer_B;OracleCmd.Parameters[':UNDERGROUNDFLOORCOUNT'].Value = DBNull.Value; if(bg.East_B == '')OracleCmd.Parameters[':EAST'].Value = DBNull.Value;elseOracleCmd.Parameters[':EAST'].Value = bg.East_B;if(bg.West_B == '')OracleCmd.Parameters[':WEST'].Value = DBNull.Value;elseOracleCmd.Parameters[':WEST'].Value = bg.West_B;if(bg.South_B == '')OracleCmd.Parameters[':SOUTH'].Value = DBNull.Value;elseOracleCmd.Parameters[':SOUTH'].Value = bg.South_B;if(bg.North_B == '')OracleCmd.Parameters[':NORTH'].Value = DBNull.Value;elseOracleCmd.Parameters[':NORTH'].Value = bg.North_B;if(bg.Area_UB == '')OracleCmd.Parameters[':ORIGINALUSELANDAREA'].Value = DBNull.Value;elseOracleCmd.Parameters[':ORIGINALUSELANDAREA'].Value = bg.Area_UB;if(bg.Area_B == '')OracleCmd.Parameters[':ORIGINALBUILDINGAREA'].Value = DBNull.Value;elseOracleCmd.Parameters[':ORIGINALBUILDINGAREA'].Value = bg.Area_B;if(bg.Stru_B == '')OracleCmd.Parameters[':ORIGINALSTRUCTURETYPE'].Value = DBNull.Value;elseOracleCmd.Parameters[':ORIGINALSTRUCTURETYPE'].Value = bg.Stru_B;OracleCmd.Parameters[':ORIGINALFLOORAMOUNT'].Value = DBNull.Value;OracleCmd.Parameters[':ORIGINALEAST'].Value = DBNull.Value;OracleCmd.Parameters[':ORIGINALWEST'].Value = DBNull.Value;OracleCmd.Parameters[':ORIGINALSOUTH'].Value = DBNull.Value;OracleCmd.Parameters[':ORIGINALNORTH'].Value = DBNull.Value;OracleCmd.Parameters[':BUILDINGMEMO'].Value = DBNull.Value;OracleCmd.Parameters[':ALLPLINTHAREA'].Value = DBNull.Value;OracleCmd.Parameters[':DESIGNAGROUNDFLOOR'].Value = DBNull.Value;OracleCmd.Parameters[':DESIGNUNDERGROUNDFLOOR'].Value = DBNull.Value;OracleCmd.Parameters[':DESIGNSPECIALTIESFLOOR'].Value = DBNull.Value;最后要讲到的就是写函数来获得你要从其他表中得到的数据!public int SelectInsert(string tm){int id = 0;try{string OracleString = 'user id=construct;data source=oracle;password=rose';OracleConnection OracleConn = new OracleConnection(OracleString);OracleConn.Open();//string temp;string str = 'SELECT ID FROM document.DOC WHERE TM = '' +tm.Trim() +'''; OracleCommand OracleComd = new OracleCommand(str,OracleConn);Object obj = OracleComd.ExecuteScalar();if(obj != null){id = Convert.ToInt32(obj); }//id = int.Parse(OracleComd.ExecuteOracleScalar().ToString());#region// Object obj = OracleComd.ExecuteReader();// if(obj!=null)// {// id = Convert.ToInt32(obj);// }// if(obj >1)// {// id = Convert.ToInt32(obj) + 100;// }#endregionOracleConn.Close();OracleConn.Dispose();//DR1.Dispose();}catch(Exception ex){Debug.Fail(ex.Message +'-'+ ex.StackTrace);}return id;}这里随你怎么写！无论是要什么值只要在其他表中有关联的字段，就可以获得你想要的数据！程序写到这里也差不多完了。这是我想到的一种方法！大家有什么好方法共享写大家一起学习！多指教！！！