达珀Oracle Clob类型 [英] Dapper & Oracle Clob type

问题描述

我正在使用dapper进行一些oracle访问.我有一种情况，我必须具有类型为OracleDbType.Clob的输出参数.当我使用dapper并因此使用基本DbType枚举时，我在使用DbType.Object枚举，如此处建议 http://docs.oracle.com/html/B14164_01/featOraCommand.htm 代表OracleDbType.Clob.

但是，这会将命令参数(在dapper中深入)设置为DbType对象和oracle类型Blob(作为DbConnection提供程序的具体OracleParameter).问题是此Oracle proc仅在此参数的类型为Clob而不是Blob时有效.

纯ADO代码的工作原理很吸引人(使用OracleParameter和OracleConnection等)，但是似乎没有办法在dapper中设置具体类型或挂接到DbParameter创建过程中，以在返回的CommandParameter上更改此OracleType?

这有效:

using (OracleConnection conn = new OracleConnection("some connection string"))
{
      conn.Open();
      var cmd = new OracleCommand("ProcName", conn);
      cmd.CommandType = CommandType.StoredProcedure;
      var paramOne = new OracleParameter("ReturnValue", OracleDbType.Clob, int.MaxValue, null, ParameterDirection.Output);
      cmd.Parameters.Add(paramOne);
      cmd.ExecuteNonQuery();
      var value1 = paramOne.Value;
 }

此操作失败:

DynamicParameters dyanmicParameters = new DynamicParameters();
dyanmicParameters.Add("ReturnValue", null, DbType.Object, ParameterDirection.Output);
connection.Execute("ProcName", dyanmicParameters, commandType: CommandType.StoredProcedure);
var val = dynamicParameters.Get<object>("ReturnValue");

任何想法?

谢谢

乔恩

解决方案

我知道您很久以前就问过这个问题.但是，我在使用不同的数据库类型时遇到了相同的问题.

基本上，您遇到了Dapper的问题之一.这是一个微观的规范，对事物的工作方式有些怀疑.尽管它声称它适用于任何数据库类型，但它似乎主要是考虑到MS SQL Server编写的.这在大多数情况下是正确的，但是，当您开始使用Clob，Blob，Geospatial等更深奥的数据类型时，事情就会像您看到的那样崩溃.

解决此问题的唯一方法是创建自定义查询参数.您可以在以下示例中查看ICustomQueryParameter源:

I am using dapper to do some oracle access. I have a scenario where I have to have an output parameter with a type of OracleDbType.Clob. As I am using dapper and thus using the base DbType enumeration I am using the DbType.Object enum as suggested here http://docs.oracle.com/html/B14164_01/featOraCommand.htm to stand in for OracleDbType.Clob.

However, this sets the command parameter (deep down in dapper) to be of DbType object and oracle type Blob (as the DbConnection providers a concrete OracleParameter). The problem being this Oracle proc only works if this parameter is of type Clob not Blob.

The pure ADO code works like a charm (Using OracleParameter and OracleConnection etc) but there appears to be no way to set the concrete type or hook into this DbParameter creation process in dapper to change this OracleType on the returned CommandParameter?

This works:

using (OracleConnection conn = new OracleConnection("some connection string"))
{
      conn.Open();
      var cmd = new OracleCommand("ProcName", conn);
      cmd.CommandType = CommandType.StoredProcedure;
      var paramOne = new OracleParameter("ReturnValue", OracleDbType.Clob, int.MaxValue, null, ParameterDirection.Output);
      cmd.Parameters.Add(paramOne);
      cmd.ExecuteNonQuery();
      var value1 = paramOne.Value;
 }

This fails:

DynamicParameters dyanmicParameters = new DynamicParameters();
dyanmicParameters.Add("ReturnValue", null, DbType.Object, ParameterDirection.Output);
connection.Execute("ProcName", dyanmicParameters, commandType: CommandType.StoredProcedure);
var val = dynamicParameters.Get<object>("ReturnValue");

Any Ideas??

Thanks,

Jon

解决方案

I know you asked this a long time ago. However I've encountered the same issue with a different database type.

Basically your running into one of the issues with Dapper. It's a micro-orm, thats somewhat opinionated about how things should work. It seems to have been written primarily with MS SQL Server in mind, even though it claims it works with any database type. Which for the most part is true, however when you start to get to more esoteric data types such as Clob's, Blob's, Geospatial, etc things start to break down as you have seen.

The only way around this would be to create a custom Query parameter. You can look at the ICustomQueryParameter source for an example here: https://github.com/SamSaffron/dapper-dot-net/blob/master/Dapper%20NET40/SqlMapper.cs

Go down to this line:

sealed partial class DbString : Dapper.SqlMapper.ICustomQueryParameter

You would basically write your own that uses OracleDbType.Clob and then use it like this:

Query<Thing>("select * from Thing where Name = @Name", new { Name = new OracleClob { Value = "abcde" } });

这篇关于达珀Oracle Clob类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！