C#:将用户定义的类型传递给 Oracle 存储过程 [英] C#: Pass a user-defined type to a Oracle stored procedure

问题描述

参考 Oracle:可变数量的参数到一个存储过程

我有将多个用户插入到用户表中的存储过程.该表的定义如下:

I have s stored procedure to insert multiple Users into a User table. The table is defined like:

CREATE TABLE "USER" 
   (
   "Name" VARCHAR2(50),
   "Surname" VARCHAR2(50),
   "Dt_Birth" DATE,
   )

插入多个用户的存储过程是:

The stored procedure to insert multiple Users is:

type userType is record (
  name varchar2(100),
...
);

type userList is table of userType index by binary_integer;

procedure array_insert (p_userList  in userList) is
begin
    forall i in p_userList.first..p_userList.last
    insert into users (username) values (p_userList(i) );
end array_insert;

如何通过传递 userType 的 userList 从 C# 调用存储过程?谢谢

How can I call the stored procedure from C# passing a userList of userType? Thanks

推荐答案

这是我曾经在 Oracle 中处理存储过程的一个助手:

Here is a helper I used to work with stored procedures in Oracle:

internal class OracleDataHelper
{
    #region Variables
    private static readonly string _connectionString;
    #endregion

    #region Constructors
    static OracleDataHelper()
    {
        //_connectionString = ConfigurationManager.ConnectionStrings["..."]
        //    .ConnectionString;
    }
    #endregion

    public static object ExecuteScalar(string query)
    {
        object result;
        using (OracleConnection conn = new OracleConnection(ConnectionString))
        {
            conn.Open();
            OracleCommand command = new OracleCommand(query, conn);
            command.CommandType = CommandType.Text;

            result = command.ExecuteScalar();

            command.Dispose();
            conn.Close();
        }

        return result;
    }

    public static object ExecuteScalar(
        string query, 
        params object[] parameters)
    {
        object result;
        using (OracleConnection conn = new OracleConnection(ConnectionString))
        {
            conn.Open();
            OracleCommand command = new OracleCommand(query, conn);
            command.CommandType = CommandType.Text;
            command.Parameters.AddRange(
                ConvertParameters(parameters)
            );

            result = command.ExecuteScalar();

            command.Dispose();
            conn.Close();
        }

        return result;
    }

    public static int ExecuteNonQuery(string query)
    {
        return ExecuteNonQuery(query, new List<OracleParameter>());
    }

    public static int ExecuteNonQuery(
        string query, 
        List<OracleParameter> parameters)
    {
        int result = 0;

        using (OracleConnection conn = new OracleConnection(ConnectionString))
        {
            conn.Open();
            OracleCommand command = new OracleCommand(query, conn);
            command.CommandType = CommandType.Text;
            command.BindByName = true;
            command.Parameters.AddRange(
                ConvertParameters(parameters.ToArray())
            );

            result = command.ExecuteNonQuery();

            command.Dispose();
            conn.Close();
        }

        return result;
    }

    public static int ExecuteNonQuery(
        string query,
        params object[] parameters)
    {
        int result = 0;
        using (OracleConnection conn = new OracleConnection(ConnectionString))
        {
            conn.Open();
            OracleCommand command = new OracleCommand(query, conn);
            command.BindByName = true;
            command.CommandType = CommandType.Text;
            command.Parameters.AddRange(ConvertParameters(parameters));

            result = command.ExecuteNonQuery();

            command.Dispose();
            conn.Close();
        }

        return result;
    }

    public static OracleDataReader ExecuteReader(
        OracleConnection conn,
        string commandText
        )
    {
        OracleCommand command = new OracleCommand(commandText, conn);

        return command.ExecuteReader();
    }

    public static IDataReader ExecuteReader(
        OracleConnection conn, 
        string spName, 
        out List<OracleParameter> outParameters, 
        params object[] parameters)
    {
        throw new NotImplementedException();
    }

    public static int ExecuteProcedure(
        string spName,
        out OutputParameters outputParameters,
        params object[] parameters)
    {
        int result = 0;

        using (OracleConnection conn = new OracleConnection(ConnectionString))
        {
            conn.Open();
            OracleCommand command = new OracleCommand(spName, conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddRange(ConvertParameters(parameters));

            result = command.ExecuteNonQuery();
            outputParameters = GetOutputParameters(command.Parameters);

            command.Dispose();
            conn.Close();
        }

        return result;
    }

    public static int ExecuteProcedure(
        string spName,
        params object[] parameters)
    {
        int result = 0;

        using (OracleConnection conn = new OracleConnection(ConnectionString))
        {
            conn.Open();
            OracleCommand command = new OracleCommand(spName, conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddRange(ConvertParameters(parameters));

            result = command.ExecuteNonQuery();

            command.Dispose();
            conn.Close();
        }

        return result;
    }

    public static OracleDataReader ExecuteProcedure(
        OracleConnection conn,
        string spName,
        out OutputParameters outputParameters,
        params object[] parameters
        )
    {
        OracleCommand command = new OracleCommand(spName, conn);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddRange(ConvertParameters(parameters));

        OracleDataReader reader = command.ExecuteReader();
        outputParameters = GetOutputParameters(command.Parameters);
        command.Dispose();

        return reader;
    }

    public static OracleDataReader ExecuteProcedure(
        OracleConnection conn,
        string spName,
        params object[] parameters
        )
    {
        OracleCommand command = new OracleCommand(spName, conn);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddRange(ConvertParameters(parameters));

        OracleDataReader reader = command.ExecuteReader();
        command.Dispose();

        return reader;
    }

    private static OracleParameter[] ConvertParameters(object[] parameters)
    {
        parameters = parameters ?? new object[] { };

        int parametersCount = parameters.Length;
        OracleParameter[] parametersArray = 
            new OracleParameter[parametersCount];

        for (int i = 0; i < parametersCount; i++)
        {
            object parameter = parameters[i];
            OracleParameter oracleParameter;

            if (parameter is OracleParameter)
            {
                oracleParameter = (OracleParameter)parameter;
                if (null == oracleParameter.Value)
                {
                    oracleParameter.Value = DBNull.Value;
                }
            }
            else
            {
                oracleParameter = new OracleParameter();

                oracleParameter.Value = parameter == null ?
                    DBNull.Value :
                    parameter;
            }

            // adding udt mapping for the parameter
            if (oracleParameter.Value != null && 
                oracleParameter.Value is IOracleCustomTypeFactory)
            {
                MemberInfo info = oracleParameter.Value.GetType();
                OracleCustomTypeMappingAttribute[] attributes =  
                        info.GetCustomAttributes(
                    typeof(OracleCustomTypeMappingAttribute), 
                        false
                    ) as OracleCustomTypeMappingAttribute[];
                if (null != attributes && attributes.Length > 0)
                {
                    oracleParameter.UdtTypeName = attributes[0].UdtTypeName;
                }
            }

            parametersArray[i] = oracleParameter;
        }

        return parametersArray;
    }

    private static OutputParameters GetOutputParameters(
        OracleParameterCollection parameters)
    {
        OutputParameters outputParameters = new OutputParameters();
        foreach (OracleParameter parameter in parameters)
        {
            if (parameter.Direction == ParameterDirection.Output)
                outputParameters.Add(parameter);
        }

        return outputParameters;
    }

    internal static string ConnectionString
    {
        get { return _connectionString; }
    }
}

这些方法适用于 UDT，也适用于简单的参数.

These methods work with UDT as well as they work with simple parameters.

以下是 UDT 实体的示例:

Here is an example of UDT entity:

[Serializable]
[OracleCustomTypeMappingAttribute("MDSYS.SDO_GEOMETRY")]
public class SdoGeometry : IOracleCustomTypeFactory, 
                           IOracleCustomType, 
                           ICloneable, INullable
{
    #region Variables
    private int _sdoGType;
    private int _sdoSrid;
    private SdoPoint _sdoPoint;
    private SdoElemInfo _sdoElemInfo;
    private SdoOrdinates _sdoOrdinate;

    private bool _sdoGTypeIsNull;
    private bool _sdoSridIsNull;
    #endregion

    #region Properties
    [OracleObjectMappingAttribute("SDO_GTYPE")]
    public int SdoGType
    {
        get { return _sdoGType; }
        set
        {
            _sdoGType = value;
            _sdoGTypeIsNull = false;
        }
    }

    public SdoGeometryType SdoGeometryType
    {
        get { return (Entities.Geometry.SdoGeometryType)(SdoGType % 100); }
    }

    public int Dimensions
    {
        get { return (int)(SdoGType / 1000); }
    }

    public int LrsDimensions
    {
        get { return (int)((SdoGType / 100) % 10); }
    }

    [OracleObjectMappingAttribute("SDO_SRID")]
    public int SdoSrid
    {
        get { return _sdoSrid; }
        set
        {
            _sdoSrid = value;
            _sdoSridIsNull = false;
        }
    }

    [OracleObjectMappingAttribute("SDO_POINT")]
    public SdoPoint SdoPoint
    {
        get { return _sdoPoint; }
        set { _sdoPoint = value; }
    }

    [OracleObjectMappingAttribute("SDO_ELEM_INFO")]
    public SdoElemInfo SdoElemInfo
    {
        get { return _sdoElemInfo; }
        set { _sdoElemInfo = value; }
    }

    [OracleObjectMappingAttribute("SDO_ORDINATES")]
    public SdoOrdinates SdoOrdinates
    {
        get { return _sdoOrdinate; }
        set { _sdoOrdinate = value; }
    }

    public static SdoGeometry Null
    {
        get
        {
            SdoGeometry obj = new SdoGeometry();

            return obj;
        }
    }
    #endregion

    #region Constructors
    public SdoGeometry()
    {
        _sdoGTypeIsNull = true;
        _sdoSridIsNull = true;
        _sdoElemInfo = SdoElemInfo.Null;
        _sdoOrdinate = SdoOrdinates.Null;
        _sdoPoint = SdoPoint.Null;
    }

    public SdoGeometry(SdoGeometry obj)
    {
        if (obj != null && this != obj)
        {
            SdoGType = obj.SdoGType;
            SdoSrid = obj.SdoSrid;
            SdoPoint = (SdoPoint)obj.SdoPoint.Clone();
            SdoElemInfo = (SdoElemInfo)obj.SdoElemInfo.Clone();
            SdoOrdinates = (SdoOrdinates)obj.SdoOrdinates.Clone();
        }
    }

    public SdoGeometry(
        int gType,
        int srid,
        SdoPoint point,
        SdoElemInfo elemInfo,
        SdoOrdinates ordinate)
    {
        SdoGType = gType;
        SdoSrid = srid;
        SdoPoint = (SdoPoint)point.Clone();
        SdoElemInfo = (SdoElemInfo)elemInfo.Clone();
        SdoOrdinates = (SdoOrdinates)ordinate.Clone();
    }
    #endregion

    #region ICloneable Members
    public object Clone()
    {
        return new SdoGeometry(this);
    }
    #endregion

    #region IOracleCustomType Members
    public void FromCustomObject(OracleConnection con, IntPtr pUdt)
    {
        if (!_sdoGTypeIsNull)
            OracleUdt.SetValue(con, pUdt, "SDO_GTYPE", SdoGType);
        if (!SdoOrdinates.IsNull)
            OracleUdt.SetValue(con, pUdt, "SDO_ORDINATES", SdoOrdinates);
        if (!SdoElemInfo.IsNull)
            OracleUdt.SetValue(con, pUdt, "SDO_ELEM_INFO", SdoElemInfo);
        if (!_sdoSridIsNull)
            OracleUdt.SetValue(con, pUdt, "SDO_SRID", SdoSrid);
        else
            OracleUdt.SetValue(con, pUdt, "SDO_SRID", DBNull.Value);
        if (!SdoPoint.IsNull)
            OracleUdt.SetValue(con, pUdt, "SDO_POINT", SdoPoint);
    }

    public void ToCustomObject(OracleConnection con, IntPtr pUdt)
    {
        object sdoGType = OracleUdt.GetValue(con, pUdt, "SDO_GTYPE");
        _sdoGTypeIsNull = sdoGType == null || sdoGType is DBNull;
        if (!_sdoGTypeIsNull)
            SdoGType = (int)sdoGType;
        SdoOrdinates = 
            (SdoOrdinates)OracleUdt.GetValue(con, pUdt, "SDO_ORDINATES");
        SdoElemInfo = 
            (SdoElemInfo)OracleUdt.GetValue(con, pUdt, "SDO_ELEM_INFO");
        object sdoSrid = OracleUdt.GetValue(con, pUdt, "SDO_SRID");
        if (!(sdoSrid == null || sdoSrid is DBNull))
            SdoSrid = (int)sdoSrid;
        SdoPoint = (SdoPoint)OracleUdt.GetValue(con, pUdt, "SDO_POINT");
    }
    #endregion

    #region INullable Members
    public bool IsNull
    {
        get { return _sdoGTypeIsNull; }
    }
    #endregion

    #region IOracleCustomTypeFactory Members
    public IOracleCustomType CreateObject()
    {
        return new SdoGeometry();
    }
    #endregion
}

附言在我的项目中，Oracle 发布了 3 个版本的 ODP.NET.有趣的是:适用于 2.111.6.10 版本的 Oracle.DataAcess.dll 的代码根本不适用于 2.111.6.20！

P.S. During my project Oracle released 3 versions of ODP.NET. The interesting thing: the code which worked for 2.111.6.10 version of Oracle.DataAcess.dll didn't work for 2.111.6.20 at all!

不知道现在哪个版本的 ODP.NET 是实际的，但我上面发布的示例适用于 2.111.6.10.

Don't know which version of ODP.NET is actual now but the samples I posted above works well with 2.111.6.10.

希望这会有所帮助.祝你好运！

Hope this helps. Good luck!

这篇关于C#:将用户定义的类型传递给 Oracle 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！