从 ASP.NET 调用 Oracle 存储过程并映射用户定义类型 (UDTs)
5.00/5 (3投票s)
一个关于如何从 ASP.NET MVC / REST API 调用包含 UDTs 的 Oracle 存储过程的示例
引言
首次调用使用 UDTs 作为输入和输出参数的 Oracle 存储过程可能是一项艰巨的任务。可用的示例很少，文档也不多，而且存在一些容易陷入并且难以诊断和理解的陷阱。我的目标是帮助各位开发者避免像我一样，为这个问题而烦恼三天。
背景
对于中等技能的开发者来说，从 .NET 程序调用存储过程本身是轻而易举的，但调用一个使用 UDTs 作为输入和输出类型的过程，特别是从 ASP.NET MVC / REST API 调用，由于这些项目类型的某些特定方面，会更加困难。
Oracle 提供了 一些文档 来指导如何实现这一点，并且这里 CodeProject 上也有 一些很好的文章 介绍了如何做到这一点。
然而，这两篇文章都没有提到一个我希望帮助您避免的常见错误。
常见问题
第一个问题源于 Oracle 的托管驱动程序不支持 UDTs。所以您无法使用从 Nuget 获取的驱动程序。您需要使用非托管的驱动程序，这些驱动程序可以通过下载并安装 Oracle Visual Studio 2017 开发人员工具（此处）获得。数据提供程序可以在安装目录下的 %installation directory%\Oracle_12_1-32\odp.net\bin\. 中找到。
第二个问题源于使用正确版本的数据提供程序。有两个数据提供程序：2.x 版本适用于旧的 .NET Framework，4 版本适用于 .NET Framework 4.0 及更高版本。请务必使用正确的版本。我不得不从 GAC 中移除 2.x 版本才能使应用程序以 4.0 .NET 版本运行。您可以通过以管理员模式打开开发人员命令提示符并运行以下命令来做到这一点。
要查看 GAC 中 DLL 的版本，请运行以下命令
gacutil /l Oracle.DataAccess
要删除特定版本，请运行以下命令
gacutil /u Oracle.DataAccess Version=x.y.z.i, PublicKeyToken=abcdefghijklmnopqrstuvwxyz
显然，请将版本和公钥令牌值替换为您想要删除的值。有关 gacutil 的更多信息可以在 此处 找到。
最后一个也是最大的问题（至少对我来说）来自于 Oracle 使用反射的方式以及 Microsoft 使用 System.Reflection.Emit 来生成动态创建的代码到 IL 的方式。这导致了一个难以调试的问题，当映射 UDTs 时：System.IO.FileNotFoundException: “无法加载文件或程序集“Anonymously Hosted DynamicMethods Assembly, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null”或其依赖项之一。系统找不到指定的文件。”
将 UDTs 映射到 C# 类
Oracle 使用反射来映射 UDT 类型到类。这就是为什么 Oracle 要求您在充当 UDT 类型的类和属性上添加属性，并实现几个接口。这些接口和属性允许 Oracle 的数据提供程序查找 UDTs 并将其映射到您的类。
您需要实现 2 个接口，并使用 2 个属性。这些接口称为 IOracleCustomType 和 IOracleCustomTypeFactory 接口。这些属性称为 OracleCustomTypeMapping 和 OracleObjectMapping。
我相信通过示例学习，让我们开始吧。
定义 UDTs
示例 UDT 在 Oracle 的 PL/SQL 语言中定义如下
create or replace TYPE GENERIC_REPLY_OUT AS OBJECT (
REPLY_HDR REPLY_HDR_DEF,
GRIDCOLS GRIDCOLS_TBL,
GRIDROWS GRIDROWS_TBL
) NOT FINAL;
其中 REPLY_HDR_DEF 定义为
create or replace TYPE REPLY_HDR_DEF AS OBJECT (
START_TS VARCHAR2(22),
END_TS VARCHAR2(22),
RUN_STATUS VARCHAR(3),
ERROR_TEXT VARCHAR2 (200),
USER_ID VARCHAR2(20),
RUN_ID VARCHAR2(32)
) NOT FINAL;
以及 GRIDCOLS_TBL 和 GRIDROWS_TBL，如下所示
create or replace TYPE GRIDCOLS_TBL IS TABLE OF GRIDCOLS_DEF
create or replace TYPE GRIDROWS_TBL IS TABLE OF GRIDROWS_DEF
以及 GRIDCOLS_DEF 和 GRIDROWS_DEF
create or replace TYPE GRIDCOLS_DEF AS OBJECT (
COL_NAME VARCHAR2(30),
COL_TYPE VARCHAR(20)
) NOT FINAL
create or replace TYPE GRIDROWS_DEF AS OBJECT (
COL_VALS COL_VALS_TBL,
ROW_STATUS VARCHAR2(10)
) NOT FINAL
最后，COL_VALS_TBL 和 COL_VALS_DEF
create or replace TYPE COL_VALS_TBL IS TABLE OF COL_VALS_DEF
create or replace TYPE COL_VALS_DEF AS OBJECT (
COL_VAL VARCHAR2(200)
) NOT FINAL
匹配的 C# 类
首先，为了避免重复，我添加了一个实现必要接口的 abstract 类
public abstract class CustomTypeBase<T> :
IOracleCustomType, IOracleCustomTypeFactory, INullable where T : CustomTypeBase<T>, new()
{
private bool _isNull;
public bool IsNull
{
get { return this._isNull; }
}
public static T Null
{
get { return new T { _isNull = true }; }
}
public IOracleCustomType CreateObject()
{
return new T();
}
public abstract void FromCustomObject(OracleConnection con, IntPtr pUdt);
public abstract void ToCustomObject(OracleConnection con, IntPtr pUdt);
}
这是我为表类型定义的 abstract 类
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];
}
}
现在，映射到 GENERIC_REPLY_OUT 的通用回复类
[OracleCustomTypeMapping("TEST.GENERIC_REPLY_OUT")]
public class GenericReply : CustomTypeBase<GenericReply>
{
public GenericReply()
{
ReplyHeader = new ReplyHeaderDefinition();
GridCols = new GridColsTable();
GridRows = new GridRowsTable();
}
[OracleObjectMapping("REPLY_HDR")]
public ReplyHeaderDefinition ReplyHeader { get; set; }
[OracleObjectMapping("GRIDCOLS")]
public GridColsTable GridCols { get; set; }
[OracleObjectMapping("GRIDROWS")]
public GridRowsTable GridRows { get; set; }
public override void FromCustomObject(OracleConnection con, IntPtr pUdt)
{
OracleUdt.SetValue(con, pUdt, "REPLY_HDR", ReplyHeader);
OracleUdt.SetValue(con, pUdt, "GRIDCOLS", GridCols);
OracleUdt.SetValue(con, pUdt, "GRIDROWS", GridRows);
}
public override void ToCustomObject(OracleConnection con, IntPtr pUdt)
{
ReplyHeader = (ReplyHeaderDefinition)(OracleUdt.GetValue(con, pUdt, "REPLY_HDR"));
GridCols = (GridColsTable)(OracleUdt.GetValue(con, pUdt, "GRIDCOLS"));
GridRows = (GridRowsTable)(OracleUdt.GetValue(con, pUdt, "GRIDROWS"));
}
}
以及映射到 REPLY_HDR_DEF 的回复头定义类
[OracleCustomTypeMapping("TEST.REPLY_HDR_DEF")]
public class ReplyHeaderDefinition : CustomTypeBase<ReplyHeaderDefinition>
{
public ReplyHeaderDefinition()
{
StartTimestamp = "";
EndTimestamp = "";
RunStatus = "";
ErrorText = "";
UserId = "";
RunId = "";
}
[OracleObjectMapping("START_TS")]
public string StartTimestamp { get; set; }
[OracleObjectMapping("END_TS")]
public string EndTimestamp { get; set; }
[OracleObjectMapping("RUN_STATUS")]
public string RunStatus { get; set; }
[OracleObjectMapping("ERROR_TEXT")]
public string ErrorText { get; set; }
[OracleObjectMapping("USER_ID")]
public string UserId { get; set; }
[OracleObjectMapping("RUN_ID")]
public string RunId { get; set; }
public override void FromCustomObject(OracleConnection con, IntPtr pUdt)
{
OracleUdt.SetValue(con, pUdt, "START_TS", StartTimestamp);
OracleUdt.SetValue(con, pUdt, "END_TS", EndTimestamp);
OracleUdt.SetValue(con, pUdt, "RUN_STATUS", RunStatus);
OracleUdt.SetValue(con, pUdt, "ERROR_TEXT", ErrorText);
OracleUdt.SetValue(con, pUdt, "USER_ID", UserId);
OracleUdt.SetValue(con, pUdt, "RUN_ID", RunId);
}
public override void ToCustomObject(OracleConnection con, IntPtr pUdt)
{
StartTimestamp = (string)(OracleUdt.GetValue(con, pUdt, "START_TS"));
EndTimestamp = (string)(OracleUdt.GetValue(con, pUdt, "END_TS"));
RunStatus = (string)(OracleUdt.GetValue(con, pUdt, "RUN_STATUS"));
ErrorText = (string)(OracleUdt.GetValue(con, pUdt, "ERROR_TEXT"));
UserId = (string)(OracleUdt.GetValue(con, pUdt, "USER_ID"));
RunId = (string)(OracleUdt.GetValue(con, pUdt, "RUN_ID"));
}
}
现在是时候为映射到 GRIDCOLS_TBL 和 GRIDROWS_TBL 的类了
[OracleCustomTypeMapping("TEST.GRIDCOLS_TBL")]
public class GridColsTable : CustomCollectionTypeBase<GridColsTable, GridColsDefinition>
{
}
[OracleCustomTypeMapping("TEST.GRIDROWS_TBL")]
public class GridRowsTable : CustomCollectionTypeBase<GridRowsTable, GridRowsDefinition>
{
}
GRIDCOLS_DEF:
[OracleCustomTypeMapping("TEST.GRIDCOLS_DEF")]
public class GridColsDefinition : CustomTypeBase<GridColsDefinition>
{
[OracleObjectMapping("COL_NAME")]
public string Name { get; set; }
[OracleObjectMapping("COL_TYPE")]
public string Type { get; set; }
public override void FromCustomObject(OracleConnection con, IntPtr pUdt)
{
OracleUdt.SetValue(con, pUdt, "COL_NAME", Name);
OracleUdt.SetValue(con, pUdt, "COL_TYPE", Type);
}
public override void ToCustomObject(OracleConnection con, IntPtr pUdt)
{
Type = ((string)(OracleUdt.GetValue(con, pUdt, "COL_TYPE")));
Name = ((string)(OracleUdt.GetValue(con, pUdt, "COL_NAME")));
}
}
GRIDROWS_DEF:
[OracleCustomTypeMapping("TEST.GRIDROWS_DEF")]
public class GridRowsDefinition : CustomTypeBase<GridRowsDefinition>
{
public GridRowsDefinition()
{
ColumnValuesTable = new ColumnValuesTable();
}
[OracleObjectMapping("COL_VALS")]
public ColumnValuesTable ColumnValuesTable { get; set; }
public override void FromCustomObject(OracleConnection con, IntPtr pUdt)
{
OracleUdt.SetValue(con, pUdt, "COL_VALS", ColumnValuesTable);
}
public override void ToCustomObject(OracleConnection con, IntPtr pUdt)
{
ColumnValuesTable = ((ColumnValuesTable)(OracleUdt.GetValue(con, pUdt, "COL_VALS")));
}
}
最后，列值表 COLVALS_TBL 和列值定义 COLVALS_DEF
[OracleCustomTypeMapping("TEST.COL_VALS_TBL")]
public class ColumnValuesTable : CustomCollectionTypeBase<ColumnValuesTable, ColumnValueDefinition>
{
}
public class ColumnValueDefinition : CustomTypeBase<ColumnValueDefinition>
{
public ColumnValueDefinition()
{
Value = "";
}
[OracleObjectMapping("COL_VAL")]
public string Value { get; set; }
public override void FromCustomObject(OracleConnection con, IntPtr pUdt)
{
OracleUdt.SetValue(con, pUdt, "COL_VAL", Value);
}
public override void ToCustomObject(OracleConnection con, IntPtr pUdt)
{
Value = ((string)(OracleUdt.GetValue(con, pUdt, "COL_VAL")));
}
}
最后，这就是我们调用存储过程的方式。我们假设存储过程名为 Test，定义如下
PROCEDURE Test3 (REP_o OUT GENERIC_REPLY_OUT);
因此，在一个控制器或数据访问层中，我们可以编写一个函数，如下所示
private Object RunOracleProcedure(string procedureFullName, Object outputObject, string outputUDTType)
{
OracleTransaction transaction = null;
OracleCommand cmd = null;
//Start transaction
transaction = Connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
//Create command
cmd = new OracleCommand();
cmd.Connection = Connection;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = procedureFullName;
//Create output object//Create output Parameter
OracleParameter parameter_out = cmd.CreateParameter();
parameter_out.OracleDbType = OracleDbType.Object;
parameter_out.Direction = ParameterDirection.Output;
parameter_out.ParameterName = "REP_o";
parameter_out.UdtTypeName = outputUDTType;
parameter_out.OracleDbTypeEx = OracleDbType.Object;
cmd.Parameters.Add(parameter_out);
//Run command and print result.
cmd.ExecuteNonQuery();
outputObject = parameter_out.Value;
transaction.Commit();
return outputObject;
}
并像这样调用它
private GenericReply RunTestProcedure ()
{
string<code> </code>procedureFullName;
GenericReply outputObject;
procedureFullName = "TEST.Test3";
outputObject = (GenericReply ) RunOracleProcedure
(procedureFullName, outputObject, "TEST.GENERIC_REPLY_OUT");
return outputObject;
}
呼！这可真是一项艰巨的工作，但现在应该可以工作了吧？实际上，在控制台应用程序或 WCF 服务中，它会工作，但在 ASP.NET MVC 或 ASP.NET REST API 后端项目中则不行。
ASP.NET MVC / REST API 项目中的问题
Oracle.DataAccess.dll 使用反射，并遍历每个程序集中的每个类，以查看哪些类具有必要的映射属性。
这会引起问题，因为 Microsoft ASP.NET 项目会动态地将代码发射到 IL，在这个过程中，它会创建一个动态（或虚拟？）程序集，该程序集不存在于磁盘上。它是为动态添加的 IL 指令的一个占位符。现在，当 Oracle 尝试加载这个虚拟程序集时，由于它不在磁盘上而无法加载，整个调用都会惨败。
您可以尝试一下。您会收到以下异常
System.IO.FileNotFoundException: 'Could not load file or assembly 'Anonymously Hosted DynamicMethods
Assembly, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies.
The system cannot find the file specified.'
如果您查看堆栈跟踪，您会发现调用源自 Oracle.DataAccess.dll。
mscorlib.dll!System.Reflection.RuntimeAssembly.nLoad(System.Reflection.AssemblyName fileName,
string codeBase, System.Security.Policy.Evidence assemblySecurity,
System.Reflection.RuntimeAssembly locationHint, ref System.Threading.StackCrawlMark stackMark,
System.IntPtr pPrivHostBinder, bool throwOnFileNotFound, bool forIntrospection,
bool suppressSecurityChecks) Unknown
mscorlib.dll!System.Reflection.RuntimeAssembly.InternalLoadAssemblyName
(System.Reflection.AssemblyName assemblyRef, System.Security.Policy.Evidence assemblySecurity,
System.Reflection.RuntimeAssembly reqAssembly, ref System.Threading.StackCrawlMark stackMark,
System.IntPtr pPrivHostBinder, bool throwOnFileNotFound, bool forIntrospection,
bool suppressSecurityChecks) Unknown
mscorlib.dll!System.Reflection.RuntimeAssembly.InternalLoad
(string assemblyString, System.Security.Policy.Evidence assemblySecurity,
ref System.Threading.StackCrawlMark stackMark, System.IntPtr pPrivHostBinder,
bool forIntrospection) Unknown
mscorlib.dll!System.Reflection.RuntimeAssembly.InternalLoad
(string assemblyString, System.Security.Policy.Evidence assemblySecurity,
ref System.Threading.StackCrawlMark stackMark, bool forIntrospection) Unknown
mscorlib.dll!System.Reflection.Assembly.Load(string assemblyString) Unknown
Oracle.DataAccess.dll!Oracle.DataAccess.Types.OracleUdt.GetAllReferencedAssemblies() Unknown
Oracle.DataAccess.dll!Oracle.DataAccess.Types.OracleUdt.SetCustomTypeMappings() Unknown
Oracle.DataAccess.dll!Oracle.DataAccess.Types.OracleUdt.GetUdtName
(string customTypeName, string dataSource) Unknown
Oracle.DataAccess.dll!Oracle.DataAccess.Client.OracleParameter.SetUDTFromCustomObject
(Oracle.DataAccess.Client.OracleConnection conn, Oracle.DataAccess.Types.IOracleCustomType customObj,
int i) Unknown
Oracle.DataAccess.dll!Oracle.DataAccess.Client.OracleParameter.PreBind_OracleObject
(Oracle.DataAccess.Client.OracleConnection conn) Unknown
Oracle.DataAccess.dll!Oracle.DataAccess.Client.OracleParameter.PreBind_Object
(Oracle.DataAccess.Client.OracleConnection conn) Unknown
Oracle.DataAccess.dll!Oracle.DataAccess.Client.OracleParameter.PreBind
(Oracle.DataAccess.Client.OracleConnection conn, System.IntPtr errCtx, int arraySize,
bool bIsFromEF, bool bIsSelectStmt) Unknown
Oracle.DataAccess.dll!Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() Unknown
那么，我们如何阻止这种情况发生呢？显然，我们需要寻找替代方法来将 UDTs 映射到类，或者我们需要阻止 DynamicAssembly 出现在程序集列表中。
幸运的是，Oracle 确实提供了另一种方法，可以使用 XML 来映射类到 UDT 类型，但他们对此没有任何文档说明，这让我感到不安。
后一种解决方案可能会以某种方式破坏 ASP.NET 应用程序，所以我甚至没有考虑过它。
使用 XML 将类映射到 UDTs
您可以在 app.config 或 web.config 文件中将 UDTs 映射到类。Oracle 确实提供了一个示例 XML 和 简短的说明，但我仍然需要为此费一些周折。
首先，最重要的一点是，XML 映射是主要使用的，而通过属性和反射的映射是次要的，所以您可以保留类中的属性，只需将以下 xml 片段添加到您的 web.config 文件中
<oracle.dataaccess.client>
<settings>
<add name="ReplyHeaderDefinition"
value="udtMapping factoryName='TestApp.Data.DbTypes.ReplyHeaderDefinition,TestApp'
typeName='REPLY_HDR_DEF'
schemaName='TEST' dataSource='mydb.localhost'"/>
<add name="GenericReplyOut"
value="udtMapping factoryName='TestApp.Data.DbTypes.GenericReplyOut,TestApp'
typeName='GENERIC_REPLY_OUT'
schemaName='TEST' dataSource='mydb.localhost'"/>
<add name="GridColsTable"
value="udtMapping factoryName='TestApp.Data.DbTypes.GridColsTable,TestApp'
typeName='GRIDCOLS_TBL'
schemaName='TEST' dataSource='mydb.localhost'"/>
<add name="GridRowsTable"
value="udtMapping factoryName='TestApp.Data.DbTypes.GridRowsTable,TestApp'
typeName='GRIDROWS_TBL'
schemaName='TEST' dataSource='mydb.localhost'"/>
<add name="GridColsDefinition"
value="udtMapping factoryName='TestApp.Data.DbTypes.GridColsDefinition,TestApp'
typeName='GRIDCOLS_DEF'
schemaName='TEST' dataSource='mydb.localhost'"/>
<add name="GridRowsDefinition"
value="udtMapping factoryName='TestApp.Data.DbTypes.GridRowsDefinition,TestApp'
typeName='GRIDROWS_DEF'
schemaName='TEST' dataSource='mydb.localhost'"/>
<add name="ColumnValuesTable"
value="udtMapping factoryName='TestApp.Data.DbTypes.ColumnValuesTable,TestApp'
typeName='COL_VALS_TBL'
schemaName='TEST' dataSource='mydb.localhost'"/>
<add name="ColumValueDefinition"
value="udtMapping factoryName='TestApp.Data.DbTypes.ColumValueDefinition,TestApp'
typeName='COL_VALS_DEF'
schemaName='TEST' dataSource='mydb.localhost'"/>
</settings>
</oracle.dataaccess.client>
请注意，这并不能完全禁用反射和对属性的需求，因为 XML 只将程序集和类映射到特定的 UDT。它仍然需要映射属性，所以您需要 OracleObjectMapping 属性。
关注点
将 Oracle UDTs 映射到 C# 类可能有点麻烦，但这是可以做到的，并且可以在现代 ASP.NET 应用程序中正常工作。
我不知道为什么 Microsoft 需要直接将代码发射到 IL，但幸运的是，这并不是使用 UDTs 和 Oracle 存储过程的障碍，而无需创建一些中间层，如 WCF 服务之类的。
我希望我的文章能为您节省几天的时间，避免像我一样撞墙。
祝您编码愉快！