电脑技术学习

SERVER数据转换ORACLE问题

dn001
大家做系统更新或者升级都可能要触及到数据的转换。
对于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;
else
OracleCmd.Parameters[":CONSTRUCTDENSITY"].Value = bg.Densy_B;
OracleCmd.Parameters[":BUILDINGTOTALHEIGHT"].Value = DBNull.Value;
if(bg.Grean_B.ToString() == "")
OracleCmd.Parameters[":GREENAREA"].Value = DBNull.Value;
else
OracleCmd.Parameters[":GREENAREA"].Value = bg.Grean_B;
if(bg.Park.ToString() =="")
OracleCmd.Parameters[":PARKINGAREA"].Value = DBNull.Value;
else
OracleCmd.Parameters[":PARKINGAREA"].Value = bg.Park;
if(bg.Park_Bike.ToString() =="")
OracleCmd.Parameters[":MINIPARKINGAREA"].Value = DBNull.Value;
else
OracleCmd.Parameters[":MINIPARKINGAREA"].Value = bg.Park_Bike;
if(bg.OTher_Area.ToString() == "")
OracleCmd.Parameters[":OTHERESTABLISHMENTAREA"].Value = DBNull.Value;
else
OracleCmd.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;
else
OracleCmd.Parameters[":AGROUNDFLOORCOUNT"].Value = bg.Layer_B; OracleCmd.Parameters[":UNDERGROUNDFLOORCOUNT"].Value = DBNull.Value;

if(bg.East_B == "")
OracleCmd.Parameters[":EAST"].Value = DBNull.Value;
else
OracleCmd.Parameters[":EAST"].Value = bg.East_B;
if(bg.West_B == "")
OracleCmd.Parameters[":WEST"].Value = DBNull.Value;
else
OracleCmd.Parameters[":WEST"].Value = bg.West_B;
if(bg.South_B == "")
OracleCmd.Parameters[":SOUTH"].Value = DBNull.Value;
else
OracleCmd.Parameters[":SOUTH"].Value = bg.South_B;
if(bg.North_B == "")
OracleCmd.Parameters[":NORTH"].Value = DBNull.Value;
else
OracleCmd.Parameters[":NORTH"].Value = bg.North_B;
if(bg.Area_UB == "")
OracleCmd.Parameters[":ORIGINALUSELANDAREA"].Value = DBNull.Value;
else
OracleCmd.Parameters[":ORIGINALUSELANDAREA"].Value = bg.Area_UB;
if(bg.Area_B == "")
OracleCmd.Parameters[":ORIGINALBUILDINGAREA"].Value = DBNull.Value;
else
OracleCmd.Parameters[":ORIGINALBUILDINGAREA"].Value = bg.Area_B;
if(bg.Stru_B == "")
OracleCmd.Parameters[":ORIGINALSTRUCTURETYPE"].Value = DBNull.Value;
else
OracleCmd.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;
// }
#endregion
OracleConn.Close();
OracleConn.Dispose();
//DR1.Dispose(); }
catch(Exception ex)
{
Debug.Fail(ex.Message +"-"+ ex.StackTrace);
}
return id;
}
这里随你怎么写！无论是要什么值只要在其他表中有关联的字段，就可以获得你想要的数据！
程序写到这里也差不多完了。这是我想到的一种方法！
大家有什么好方法共享写大家一起学习！
多指教！！！

上一篇 Oracle平台下的数据分片技术解析

下一篇 Oracle数据库字符集问题解析

相关文章