SERVER数据转换ORACLE问题!

类别:.NET开发 点击:0 评论:0 推荐:

大家做系统更新或者升级都可能要触及到数据的转换。
对于ORACLE中的数据转换可以用写存储过程来放之游标,将你要的数据一条一条的转换。
而对于SERVER虽然你可以将它转换到ORACLE中(升级的方式),但有问题你会发现有的表是不可以直接转换的!
所以我就用代码控制直接转换!也是一种比较容易控制容易写的方法!
首先你要在程序中建立连接(server的)

string strCon="workstation id=XJ;packet size=4096;user id=sa;data source=\"automap\\automap\";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;
  }
这里随你怎么写!无论是要什么值只要在其他表中有关联的字段,就可以获得你想要的数据!
程序写到这里也差不多完了。这是我想到的一种方法!
大家有什么好方法共享写大家一起学习!
多指教!!!

本文地址:http://com.8s8s.com/it/it43012.htm