dapper支持oracle游标的方法介绍
如果玩过Oracle都知道，存储过程基本都是通过游标返回数据的，但是dapper原生操作游标会报异常，具体异常信息因为现在没有环境就不截图了。
public FactoryPriceComparisonPublishItem GetTodayFactoryBasePricePushInfo(string weiXinId)
{
using (var cnn = Database.Connection("ERPDataBase"))
{
var p = new OracleDynamicParameters();
p.Add("V_WEIXINID", weiXinId);
p.Add("RetCursor", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
return cnn.Query("PKG_M_STEELMILL.GetFactoryPCPublishItem", param: p, commandType: CommandType.StoredProcedure).SingleOrDefault();
}
}
以上是实际项目的代码片段，通过游标获取查询数据。
那如何去解决这个游标问题呢？就是这个OracleDynamicParameters类，全部内容如下：
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Dapper;
using Oracle.DataAccess.Client;
public class OracleDynamicParameters : SqlMapper.IDynamicParameters
{
private static Dictionary> paramReaderCache = new Dictionary>();
private Dictionary parameters = new Dictionary();
private List templates;
private class ParamInfo
{
public string Name { get; set; }
public object Value { get; set; }
public ParameterDirection ParameterDirection { get; set; }
public OracleDbType? DbType { get; set; }
public int? Size { get; set; }
public IDbDataParameter AttachedParam { get; set; }
}
///
/// construct a dynamic parameter bag
///
public OracleDynamicParameters()
{
}
///
/// construct a dynamic parameter bag
///
/// can be an anonymous type or a DynamicParameters bag
public OracleDynamicParameters(object template)
{
AddDynamicParams(template);
}
///
/// Append a whole object full of params to the dynamic
/// EG: AddDynamicParams(new {A = 1, B = 2}) // will add property A and B to the dynamic
///
///
public void AddDynamicParams(
#if CSHARP30
object param
#else
dynamic param
#endif
)
{
var obj = param as object;
if (obj != null)
{
var subDynamic = obj as OracleDynamicParameters;
if (subDynamic == null)
{
var dictionary = obj as IEnumerable>;
if (dictionary == null)
{
templates = templates ?? new List();
templates.Add(obj);
}
else
{
foreach (var kvp in dictionary)
{
#if CSHARP30
Add(kvp.Key, kvp.Value, null, null, null);
#else
Add(kvp.Key, kvp.Value);
#endif
}
}
}
else
{
if (subDynamic.parameters != null)
{
foreach (var kvp in subDynamic.parameters)
{
parameters.Add(kvp.Key, kvp.Value);
}
}
if (subDynamic.templates != null)
{
templates = templates ?? new List();
foreach (var t in subDynamic.templates)
{
templates.Add(t);
}
}
}
}
}
///
/// Add a parameter to this dynamic parameter list
///
///
///
///
///
///
public void Add(
#if CSHARP30
string name, object value, DbType? dbType, ParameterDirection? direction, int? size
#else
string name, object value = null, OracleDbType? dbType = null, ParameterDirection? direction = null, int? size = null
#endif
)
{
parameters[Clean(name)] = new ParamInfo() { Name = name, Value = value, ParameterDirection = direction ?? ParameterDirection.Input, DbType = dbType, Size = size };
}
private static string Clean(string name)
{
if (!string.IsNullOrEmpty(name))
{
switch (name[0])
{
case '@':
case ':':
case '?':
return name.Substring(1);
}
}
return name;
}
void SqlMapper.IDynamicParameters.AddParameters(IDbCommand command, SqlMapper.Identity identity)
{
AddParameters(command, identity);
}
///
/// Add all the parameters needed to the command just before it executes
///
/// The raw command prior to execution
/// Information about the query
protected void AddParameters(IDbCommand command, SqlMapper.Identity identity)
{
if (templates != null)
{
foreach (var template in templates)
{
var newIdent = identity.ForDynamicParameters(template.GetType());
Action appender;
lock (paramReaderCache)
{
if (!paramReaderCache.TryGetValue(newIdent, out appender))
{
appender = SqlMapper.CreateParamInfoGenerator(newIdent, false, false);
paramReaderCache[newIdent] = appender;
}
}
appender(command, template);
}
}
foreach (var param in parameters.Values)
{
string name = Clean(param.Name);
bool add = !((OracleCommand)command).Parameters.Contains(name);
OracleParameter p;
if (add)
{
p = ((OracleCommand)command).CreateParameter();
p.ParameterName = name;
}
else
{
p = ((OracleCommand)command).Parameters[name];
}
var val = param.Value;
p.Value = val ?? DBNull.Value;
p.Direction = param.ParameterDirection;
var s = val as string;
if (s != null)
{
if (s.Length <= 4000)
{
p.Size = 4000;
}
}
if (param.Size != null)
{
p.Size = param.Size.Value;
}
if (param.DbType != null)
{
p.OracleDbType = param.DbType.Value;
}
if (add)
{
command.Parameters.Add(p);
}
param.AttachedParam = p;
}
}
///
/// All the names of the param in the bag, use Get to yank them out
///
public IEnumerable ParameterNames
{
get
{
return parameters.Select(p => p.Key);
}
}
///
/// Get the value of a parameter
///
///
///
/// The value, note DBNull.Value is not returned, instead the value is returned as null
public T Get(string name)
{
var val = parameters[Clean(name)].AttachedParam.Value;
if (val == DBNull.Value)
{
if (default(T) != null)
{
throw new ApplicationException("Attempting to cast a DBNull to a non nullable type!");
}
return default(T);
}
return (T)val;
}
}
与 Oracle 使用 Dapper 存储过程返回游标的
其中一个去使用Dapper带有 Oracle 存储过程返回游标的？
var p = new DynamicParameters();
p.Add("foo", "bar");
p.Add("baz_cursor", dbType: DbType.? , direction: ParameterDirection.Output);
在这里，DbType 是没有光标成员的 System.Data.DbType。我已经尝试使用 DbType.Object，但这不能与 OracleClient 和 OracleDataAcess。
可能的方式，改为使用 OracleType 或 OracleDbType 是什么？
解决方案
您将必须执行：
public interface IDynamicParameters
{
void AddParameters(IDbCommand command, Identity identity);
}
然后在 AddParameters 回调将强制转换 IDbCommand 到 OracleCommand 和添加 DB 特定参数。
