使用C#操作OracleSpatial的SDO_GEOMETRY對像（讀取和寫入）

首先，這個需要使用ODAC，也就是Oracle.DataAccess.dll，新出的托管Oracle.ManagedDataAccess.dll不支持Object Type，無法使用 ODAC下載地址參考：http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html
ODAC使用方法參考：http://blog.csdn.net/rrrrssss00/article/details/7178515

代碼見附件http://www.kuaipan.cn/file/id_22823997376823621.htm，大致思路是：先根據SDO_GEOMETRY對象的內容，在C#中構建一個對應的類，然後在讀取和寫入時使用OracleParameter來操作這個類的對象，達到讀取和寫入數據庫SDO_GEOMETRY對象的目的
類名為SdoGeometry，主要代碼如下（其中還用到了自定義的SdoPoint，OracleArrayTypeFactory和OracleCustomTypeBase類，其代碼見附件的相應文件）

[OracleCustomTypeMappingAttribute("MDSYS.SDO_GEOMETRY")]
  public class SdoGeometry : OracleCustomTypeBase<SdoGeometry>
  {
    private enum OracleObjectColumns { SDO_GTYPE, SDO_SRID, SDO_POINT, SDO_ELEM_INFO, SDO_ORDINATES }
    private decimal? sdo_Gtype;
    [OracleObjectMappingAttribute(0)]
    public decimal? Sdo_Gtype
    {
      get { return sdo_Gtype; }
      set { sdo_Gtype = value; }
    }
    private decimal? sdo_Srid;
    [OracleObjectMappingAttribute(1)]
    public decimal? Sdo_Srid
    {
      get { return sdo_Srid; }
      set { sdo_Srid = value; }
    }
    private SdoPoint point;
    [OracleObjectMappingAttribute(2)]
    public SdoPoint Point
    {
      get { return point; }
      set { point = value; }
    }
    private decimal[] elemArray;
    [OracleObjectMappingAttribute(3)]
    public decimal[] ElemArray
    {
      get { return elemArray; }
      set { elemArray = value; }
    }
    private decimal[] ordinatesArray;
    [OracleObjectMappingAttribute(4)]
    public decimal[] OrdinatesArray
    {
      get { return ordinatesArray; }
      set { ordinatesArray = value; }
    }
    [OracleCustomTypeMappingAttribute("MDSYS.SDO_ELEM_INFO_ARRAY")]
    public class ElemArrayFactory : OracleArrayTypeFactoryBase<decimal> {}
    [OracleCustomTypeMappingAttribute("MDSYS.SDO_ORDINATE_ARRAY")]
    public class OrdinatesArrayFactory : OracleArrayTypeFactoryBase<decimal> {}
    public override void MapFromCustomObject()
    {
      SetValue((int)OracleObjectColumns.SDO_GTYPE, Sdo_Gtype);
      SetValue((int)OracleObjectColumns.SDO_SRID, Sdo_Srid);
      SetValue((int)OracleObjectColumns.SDO_POINT, Point);
      SetValue((int)OracleObjectColumns.SDO_ELEM_INFO, ElemArray);
      SetValue((int)OracleObjectColumns.SDO_ORDINATES, OrdinatesArray);
    }
    public override void MapToCustomObject()
    {
      Sdo_Gtype = GetValue<decimal?>((int)OracleObjectColumns.SDO_GTYPE);
      Sdo_Srid = GetValue<decimal?>((int)OracleObjectColumns.SDO_SRID);
      Point = GetValue<SdoPoint>((int)OracleObjectColumns.SDO_POINT);
      ElemArray = GetValue<decimal[]>((int)OracleObjectColumns.SDO_ELEM_INFO);
      OrdinatesArray = GetValue<decimal[]>((int)OracleObjectColumns.SDO_ORDINATES);
    }
  }

從數據庫裡讀取的代碼為（示例表只有兩列，id列為number類型,geo列為SDO_GEOMTRY類型）：

         OracleCommand cmd = new OracleCommand()
         cmd.Connection = con; 
          cmd.CommandType = CommandType.Text;
         cmd.CommandText = " select id,geo from geoinfo ";
          using (OracleDataReader readerGeoInfo = cmd.ExecuteReader())
          {
            while (readerGeoInfo.Read())
            {
              GeoInfo geoInfo = new GeoInfo();
              if (!readerGeoInfo.IsDBNull(0))
              {
                geoInfo.Id = readerGeoInfo.GetDecimal(0);
              }
              if (!readerGeoInfo.IsDBNull(1))
              {
                geoInfo.Geo = (SdoGeometry)readerGeoInfo.GetValue(1); 
              }
              geoInfoList.Add(geoInfo);
            }
            readerGeoInfo.Close();
          }

插入的代碼為：

cmd.CommandText = " insert into geoinfo values (geoinfo_seq.nextval,:param) ";
          cmd.Parameters.Clear();
          OracleParameter oracleParameterGeo = new OracleParameter();
          oracleParameterGeo.OracleDbType = OracleDbType.Object;
          oracleParameterGeo.UdtTypeName = "MDSYS.SDO_GEOMETRY";
          cmd.Parameters.Add(oracleParameterGeo);
          //creating point
          SdoGeometry geoPoint = new SdoGeometry();
          geoPoint.Sdo_Gtype = 2001; 
          geoPoint.Point = new SdoPoint();
          geoPoint.Point.X = 200;
          geoPoint.Point.Y = 400;
          oracleParameterGeo.Value = geoPoint;
          //insert point in table geoinfo
          cmd.ExecuteNonQuery();
          //creating polygon
          SdoGeometry geoPolygon = new SdoGeometry();
          geoPolygon.Sdo_Gtype = 2003;
          geoPolygon.ElemArray = new decimal[] { 1, 1003, 1 };
          geoPolygon.OrdinatesArray = new decimal[] { 3, 3, 3, 10, 10, 10, 10, 3, 3, 3 };
          oracleParameterGeo.Value = geoPolygon; 
          //insert polygon into table geoinfo 
          cmd.ExecuteNonQuery();

在實際使用中，使用DataAdapter的Fill方法將Select *的查詢結果放到DataTable中時，如果已經定義了SdoGeometry的類，查詢結果會自動地將DataTable的那列認為是SdoGeometry，非常方便 ，例如
OracleDataAdapter mAdp = new OracleDataAdapter("select * from geoinfo", con); DataTable mDst = new DataTable(); mAdp.Fill(mDst);
此時mDst的第二列數據類型即為SdoGeometry