将自定义类型从Oracle转换为C#

本文关键字：转换 Oracle 自定义 类型 | 更新日期: 2023-09-27 17:57:43

我有一些oracle sql代码，它指定了类型，并像这样返回它们（请原谅简化的示例）。

create or replace type varchar2Row as table of varchar2(1000);
/
create or replace type varchar2Matrix as table of varchar2Row;
/
create or replace function get_myTableResult(in_name varchar2) return varchar2Matrix is
  l_result varchar2Matrix := varchar2Matrix();
begin
  l_result.extend(2);
  l_result(1) := varchar2Row(in_name, in_name || '1', in_name || '2');
  l_result(2) := varchar2Row(in_name, '1' || in_name, '2' || in_name);
  return l_result;
end;

我希望能够从C#层调用它，然后将varchar2Matrix转换为字符串的2d数组。这样的事情可能吗？

将自定义类型从Oracle转换为C#

只需为UDTs:定义类

[OracleCustomTypeMapping("<schema>.VARCHAR2MATRIX")]
public class Varchar2Matrix : CustomCollectionTypeBase<Varchar2Matrix, Varchar2Row>
{
}
[OracleCustomTypeMapping("<schema>.VARCHAR2ROW")]
public class Varchar2Row : CustomCollectionTypeBase<Varchar2Row, string>
{
}
public abstract class CustomCollectionTypeBase<TType, TValue> : CustomTypeBase<TType>, IOracleArrayTypeFactory where TType : CustomTypeBase<TType>, new()
{
    [OracleArrayMapping()]
    public TValue[] Values;
    public override void FromCustomObject(OracleConnection connection, IntPtr pointerUdt)
    {
        OracleUdt.SetValue(connection, pointerUdt, 0, Values);
    }
    public override void ToCustomObject(OracleConnection connection, IntPtr pointerUdt)
    {
        Values = (TValue[])OracleUdt.GetValue(connection, pointerUdt, 0);
    }
    public Array CreateArray(int elementCount)
    {
        return new TValue[elementCount];
    }
    public Array CreateStatusArray(int elementCount)
    {
        return new OracleUdtStatus[elementCount];
    }
}
public abstract class CustomTypeBase<T> : IOracleCustomType, IOracleCustomTypeFactory, INullable where T : CustomTypeBase<T>, new()
{
    private bool _isNull;
    public IOracleCustomType CreateObject()
    {
        return new T();
    }
    public abstract void FromCustomObject(OracleConnection connection, IntPtr pointerUdt);
    public abstract void ToCustomObject(OracleConnection connection, IntPtr pointerUdt);
    public bool IsNull
    {
        get { return this._isNull; }
    }
    public static T Null
    {
        get { return new T { _isNull = true }; }
    }
}

然后调用函数，例如：

using (var connection = new OracleConnection("connection string"))
{
    connection.Open();
    using (var command = connection.CreateCommand())
    {
        command.BindByName = true;
        command.CommandText = "SELECT get_myTableResult(:p) FROM dual";
        var parameter = command.CreateParameter();
        parameter.Direction = ParameterDirection.Input;
        parameter.ParameterName = "p";
        parameter.Value = "string value";
        command.Parameters.Add(parameter);
        var = result command.ExecuteScalar();
        // access the data from the result object
    }
}
相关文章：