c# - Oracle 和 C# - UDT 数组作为返回值
问题描述
我试图让 C# 读取一个 PL/SQL 用户定义类型的数组。
这是数组每个对象的定义:
CREATE OR REPLACE TYPE "REPORTDATUM" AS OBJECT
(
FIELD1 VARCHAR2(20),
FIELD2 VARCHAR2(3000),
FIELD3 NUMBER(15),
FIELD4 DATE,
FIELD5 DATE,
FIELD6 VARCHAR2(4000),
FIELD7 TIMESTAMP,
FIELD8 VARCHAR2(10),
FIELD9 NUMBER(35),
FIELD10 VARCHAR2(80),
FIELD11 VARCHAR2(50)
);
CREATE OR REPLACE TYPE "REPORTARRAY" IS TABLE OF REPORTDATUM;
这是我试图调用的函数的签名:
FUNCTION getReport(startTime IN DATE, endTime IN DATE) RETURN REPORTARRAY;
现在,根据我在这里找到的指南:http: //appsjack.blogspot.com/2010/09/pass-custom-udt-types-to-oracle-stored.html
我创建了 ReportDatum.cs 类,如下所示:
public class ReportDatum : INullable, IOracleCustomType {
private bool objectIsNull;
[OracleObjectMappingAttribute(Constants.ReportDatum.Aliases.Field1)]
public string Field1 { get; set; }
[OracleObjectMappingAttribute(Constants.ReportDatum.Aliases.Field2)]
public string Field2 { get; set; }
[...]
[OracleObjectMappingAttribute(Constants.ReportDatum.Aliases.Field11)]
public string Field11 { get; set; }
public static ReportDatum Null {
get {
ReportDatum reportDatum = new ReportDatum();
reportDatum.objectIsNull = true;
return reportDatum;
}
}
public bool IsNull {
get { return objectIsNull; }
}
public void FromCustomObject(OracleConnection databaseConnection, IntPtr userDefinedTypePointer) {
if (string.IsNullOrEmpty(this.Field1) == false ) {
OracleUdt.SetValue(databaseConnection, userDefinedTypePointer, Constants.ReportDatum.Aliases.Field1, this.Field1);
}
if (string.IsNullOrEmpty(this.Field2) == false) {
OracleUdt.SetValue(databaseConnection, userDefinedTypePointer, Constants.ReportDatum.Aliases.Field2, this.Field2);
}
[...]
if (string.IsNullOrEmpty(this.Field11) == false) {
OracleUdt.SetValue(databaseConnection, userDefinedTypePointer, Constants.ReportDatum.Aliases.Field11, this.Field11);
}
}
public void ToCustomObject(OracleConnection databaseConnection, IntPtr userDefinedTypePointer) {
this.Field1 = parseString(databaseConnection, userDefinedTypePointer, Constants.ReportDatum.Aliases.Field1);
this.Field2 = parseString(databaseConnection, userDefinedTypePointer, Constants.ReportDatum.Aliases.Field2);
[...]
this.Field11 = parseString(databaseConnection, userDefinedTypePointer, Constants.ReportDatum.Aliases.Field11);
}
private string parseString(OracleConnection databaseConnection, IntPtr userDefinedTypePointer, string columnName) {
string valueToReturn = String.Empty;
try {
valueToReturn = ( String ) OracleUdt.GetValue(databaseConnection, userDefinedTypePointer, columnName);
}
catch (Exception e) {
error("Exception while attempting to parse string value of column [" + columnName + "]. Message [" + (e == null || e.Message == null ? "NULL" : e.Message) + "]", e);
}
return valueToReturn;
}
}
我还着手创建 ReportDatumFactory.cs,如下所示:
public class ReportDatumFactory : IOracleCustomTypeFactory {
public IOracleCustomType CreateObject() {
return new ReportDatum();
}
}
根据我上面链接的指南,这应该足以处理组成数组的每个项目。
为了处理数组本身,我创建了以下类。
报告数组.cs
public class ReportArray : INullable, IOracleCustomType {
[OracleArrayMapping()]
public ReportDatum[] reportArray;
private bool objectIsNull;
public bool IsNull {
get { return objectIsNull; }
}
public void FromCustomObject(OracleConnection databaseConnection, IntPtr userDefinedTypePointer) {
try {
OracleUdt.SetValue(databaseConnection, userDefinedTypePointer, 0, reportArray);
}
catch (Exception e) {
error("Exception while populating OracleUdt from ReportArray.", e);
}
}
public void ToCustomObject(OracleConnection databaseConnection, IntPtr userDefinedTypePointer) {
try {
reportArray = ( ReportDatum[] ) OracleUdt.GetValue(databaseConnection, userDefinedTypePointer, 0);
}
catch (Exception e) {
error("Exception while reading values from OracleUdt related to ReportArray.", e);
}
}
}
以及相关的工厂类
ReportArrayFactory.cs
public class ReportArrayFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory {
public IOracleCustomType CreateObject() {
return new ReportArray();
}
public Array CreateArray(int numberOfElements) {
return new ReportArray[numberOfElements];
}
public Array CreateStatusArray(int numberOfElements) {
return null;
}
}
现在,关于调用存储过程的方法,这就是我所拥有的:
报告.cs
private void executeStoredProcedure2(OracleConnection databaseConnection, string schema, string storedProcedureName, DateTime startTime, DateTime endTime, int numberOfElements) {
OracleCommand databaseCommand = new OracleCommand();
ReportArray reportArray = new ReportArray();
debug("Attempting to populate the report array");
if (databaseConnection != null && databaseConnection.State == ConnectionState.Open) {
try {
databaseCommand.Connection = databaseConnection;
databaseCommand.CommandType = CommandType.StoredProcedure;
databaseCommand.CommandText = schema + "." + storedProcedureName;
databaseCommand.Parameters.Add(createUserDefinedParameter(numberOfElements, reportArray));
databaseCommand.Parameters.Add(Constants.ReportDatum.ParameterNames.startTime, OracleDbType.Date, startTime, ParameterDirection.Input);
databaseCommand.Parameters.Add(Constants.ReportDatum.ParameterNames.endTime, OracleDbType.Date, endTime, ParameterDirection.Input);
databaseCommand.ExecuteNonQuery();
//How do I populate the reportArray object?
}
catch (Exception exception) {
error("Exception while executing stored procedure [" + storedProcedure2 + "].", exception);
}
finally {
databaseCommand.Dispose();
}
}
}
private OracleParameter createUserDefinedParameter(int size, ReportArray reportArray) {
OracleParameter valueToReturn = null;
try {
valueToReturn = new OracleParameter();
valueToReturn.ParameterName = "reportData"; //this is the name of the REPORTARRAY object in the Oracle function that is being returned.
valueToReturn.OracleDbType = OracleDbType.Array;
valueToReturn.Direction = ParameterDirection.ReturnValue;
valueToReturn.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
valueToReturn.UdtTypeName = Constants.ReportDatum.arrayDataTypeDefinition;
valueToReturn.ArrayBindSize = new int[size];
valueToReturn.ArrayBindStatus = new OracleParameterStatus[size];
valueToReturn.Size = size;
valueToReturn.Value = reportArray.reportArray;
}
catch (Exception e) {
error("Exception while attempting to create user defined parameter.", e);
}
return valueToReturn;
}
我面临的问题是我无法在 C# 中重新获取数组。有没有人遇到过这个问题?我错过了什么?
我有理由相信 SQL 代码正在运行。目前只有 C# 造成了问题。
解决方案
看起来有些地方不对劲。
首先,工厂需要OracleCustomTypeMapping
属性。没有它们,Oracle 就不会认为应该使用这些映射工厂类:
[OracleCustomTypeMapping("SCHEMA.REPORTDATUM")]
public class ReportDatum ...
和
[OracleCustomTypeMapping("SCHEMA.REPORTARRAY")]
public class ReportArray ...
其次,数组映射工厂有个bug:在创建数组的时候,要创建一个ReportDatum
对象数组。目前,您正在创建一个ReportArray
对象数组,即您不想要的数组数组。换句话说,替换
public Array CreateArray(int numberOfElements) {
return new ReportArray[numberOfElements];
}
和
public Array CreateArray(int numberOfElements) {
return new ReportDatum[numberOfElements];
}
三、删除线
CollectionType = OracleCollectionType.PLSQLAssociativeArray,
从你的createUserDefinedParameter
方法。返回的值不是 PL/SQL 关联数组(它是不同类型的集合),因此不应使用此属性。如果您使用此属性,您可能会发现从存储函数返回的返回值是一个空object
数组。
最后,要从存储的函数调用中取回数组,请在调用后添加以下行databaseCommand.ExecuteNonQuery()
:
reportArray = (ReportArray)databaseCommand.Parameters["reportData"].Value;
推荐阅读
- json - jq:仅按顺序打印特定的键和值
- c++ - 协议缓冲区 ParseFromString 不检查消息的结尾
- filter - 演示过滤器与社区过滤器
- c# - ASP.net Core 2 自定义身份验证 (IsAuthenticated = false)
- javascript - 文件输入没有正确更新状态反应?
- html - Html 将所有内容水平放置
- android - 如何在android中使用jsoup从html获取脚本
- azure - 如何在 Azure Blob 存储上部署 Elasticsearch
- laravel - 将原始 SQL 添加到数据库查询构建器
- c# - C#反射获取接口属性的实现属性