oracle 如何在C#中声明MDSYS.sdo_Geometry

xeufq47z 于 2022-11-03 发布在 Oracle

关注(0)|答案(2)|浏览(213)

我在Oracle数据库中有一个存储过程，它将MDSYS.Sdo_Geometry和数字类型作为输入，并给予t_t_out_house_details对象和p_return_cd编号。

PROCEDURE prc_get_impacted_houses(p_polygon_geom      IN MDSYS.Sdo_Geometry,
                                p_imkl_id        IN NUMBER,
                                p_out_house_details OUT     t_t_out_house_details,
                                p_return_cd         OUT NUMBER) IS
v_proj_dist_limit   NUMBER;
v_out_house_details t_t_out_house_details := t_t_out_house_details();
v_net_cd            VARCHAR2(6);
v_subnet_nr         NUMBER(5);
v_cable_number      NUMBER(3);
invalid_geometry EXCEPTION;

我想从C#调用这个过程，并将结果（返回）作为输出显示。我想声明MDSYS.Sdo_Geometry数据类型。我不知道应该声明什么类型。我试图使用空间类geomtry声明，但我不知道如何使用它。我在互联网上找不到任何这样的例子。
这是我C#代码：

static public void CallingStoredProcedure(GeometryPolygon PPolygonGeom, int PImklId)
    { 
    //conection to the database

        using (OracleConnection conn = new OracleConnection("Data Source=; User ID=; Password="))
        {                
            OracleCommand cmd = new OracleCommand();
            conn.Open();
            cmd = new OracleCommand("prc_get_impacted_houses", conn);

            OracleParameter op = null;

            cmd.CommandType = CommandType.StoredProcedure;

            //cmd.Parameters.Add("p_polygon_geom", OracleType.).Value = PPolygonGeom;
            cmd.Parameters.Add("p_imkl_id", OracleDbType.Int32).Value = PImklId;
            op = new OracleParameter("p_return_cd", OracleDbType.Int32);
            op.Direction = ParameterDirection.Output;

            cmd.Parameters.Add(op);

            //CALL PROCEDURE
            try
            {
                cmd.ExecuteNonQuery();

                Console.WriteLine(cmd.Parameters["p_return_cd"].Value);
            }
            catch (Exception ex)
            {
                throw new Exception("Error " + ex.Message);
            }
            finally 
            {
                conn.Close();
            }
        }
    }

oracle

来源：https://stackoverflow.com/questions/29160849/how-to-declare-mdsys-sdo-geometry-in-c-sharp

2条答案

按热度按时间

koaltpgm1#

解决方案http://www.orafaq.com/forum/mv/msg/27794/296419/#msg_296419您应该定义UDT：MDSYS.SDO_几何图形：

using System;
using System.Collections.Generic;
using System.Text;
using Oracle.DataAccess.Types;
using Oracle.DataAccess.Client;
using System.Data;

namespace some.namespace
{
  [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);
    }

    public static OracleParameter CreateSdoGeometry(string parameterName, SdoGeometry objGeometry, ParameterDirection direction = ParameterDirection.Input)
    {
        OracleParameter customParameter = new OracleParameter();

        customParameter.ParameterName = parameterName;
        customParameter.OracleDbType = OracleDbType.Object;
        customParameter.UdtTypeName = "MDSYS.SDO_GEOMETRY";
        customParameter.Direction = direction;
        customParameter.Value = objGeometry; 

        return customParameter;
    }
  }
}

和MDSYS.SDO点类型：

using System;
using System.Collections.Generic;
using System.Text;
using Oracle.DataAccess.Types;

namespace some.namespace
{
  [OracleCustomTypeMappingAttribute("MDSYS.SDO_POINT_TYPE")]
  public class SdoPoint : OracleCustomTypeBase<SdoPoint>
  {
    private decimal? x;

    [OracleObjectMappingAttribute("X")]
    public decimal? X
    {
      get { return x; }
      set { x = value; }
    }

    private decimal? y;

    [OracleObjectMappingAttribute("Y")]
    public decimal? Y
    {
      get { return y; }
      set { y = value; }
    }

    private decimal? z;

    [OracleObjectMappingAttribute("Z")]
    public decimal? Z
    {
      get { return z; }
      set { z = value; }
    }

    public override void MapFromCustomObject()
    {
      SetValue("X", x);
      SetValue("Y", y);
      SetValue("Z", z);
    }

    public override void MapToCustomObject()
    {
      X = GetValue<decimal?>("X");
      Y = GetValue<decimal?>("Y");
      Z = GetValue<decimal?>("Z");
    }
  }
}

赞(0）回复(0）举报 2022-11-03

c2e8gylq2#

Oracle现在也可以使用他们的托管驱动程序了。我已经做了一个名为ManagedNetSdoGeometry的NetSdoGeometry实现的修改版本。您可以在这里找到它，
https://github.com/flappah/ManagedNetSdoGeometry

赞(0）回复(0）举报 2022-11-03