Dapper完美兼容Oracle，执行存储过程，并返回结果集。

搬家到博客园：https://www.cnblogs.com/likeli/p/4875341.html

Dapper完美兼容Oracle，执行存储过程，并返回结果集。

 

这个问题，困扰了我整整两天。

刚刚用到Dapper的时候，感觉非常牛掰。特别是配合.net 4.0新特性dynamic，让我生成泛型集合，再转json一气呵成。

不过，各种ORM总有让人吐槽的地方。。。

比如，我之前在SqlServer上写测试，搞封装，没有任何问题。CURD、批量操作、存储过程、事物等。

可是以转到Oracle上，就出问题了【喂~不是说好的支持Oracle的么】

在写Dapper+Oracle单元测试的前期，是没有问题的，也就是说普通的Sql操作是没有任何问题的。

然后，我写到存储过程的单元测试的时候，就蛋疼了。

因为原版采用的DbType数据类型枚举。Sqlserver返回结果集并没有输出游标。

但是Oracle输出结果集，就需要用游标了。那么，这里问题就来了。给OracleParameter设置参数类型，DbType并没有Cursor游标类型。

关于Dapper的文档也是不多，而且大部分都集中在SqlServer上，可能应为服务于.Net平台，比较侧重于微软的配套数据库。

好吧，问题来了，那就解决。反正是开源的。源代码都有。

先根据问题来搜索【我不喜欢用百度，因为百度搜出来一大堆不相关的东西，铜臭味太重。google在国内有无法访问，我就选择了Bing，结果效果还不错。】

经过网上搜集，发现Dapper确实是支持Oracle的，但是对于调用Oracle存储过程的内容却没有。

好吧，没有的话，先自己分析分析。

既然是参数类型不支持，那么换成支持的不就成了？

原版的是这样的：

1 DynamicParameters dp = new DynamicParameters();
2 dp.Add("RoleId", "1");
3 dp.Add("RoleName", "", DbType.String, ParameterDirection.Output);

这是Dapper原版中，声明parameter的部分，上面代码红色部分，就是指定参数类型。

在system.data.oracleclient 中，有OracleType这个枚举有Cursor类型。

然后，去查看 DynamicParameters 类，如下图：

可以看到，这个类，是实现了一个接口的。说明，原作者给我们预留了接口去自己实现其他内容。

继续看看接口：

接口的内容很简单，就是一个AddParameters方法。

那么，可以确定，上面的猜测是对的。

我们直接扩展实现这个接口就可以了。如图：

自己去创建一个实现了IDynamicParameters的类OracleDynamicParameters。

然后参照原作者提供的DynamicParameters类来实现这个接口。

最终修改版如下（代码多，展开了直接复制代码贴到你的文件里面）：

   1 /*
   2  License: http://www.apache.org/licenses/LICENSE-2.0 
   3  Home page: http://code.google.com/p/dapper-dot-net/
   4 
   5  Note: to build on C# 3.0 + .NET 3.5, include the CSHARP30 compiler symbol (and yes,
   6  I know the difference between language and runtime versions; this is a compromise).
   7  * 
   8  * 增加Oracle存储过程支持
   9  * 李科笠 2015年10月13日 17:43:54
  10  */
  11 using System;
  12 using System.Collections;
  13 using System.Collections.Generic;
  14 using System.ComponentModel;
  15 using System.Data;
  16 using System.Linq;
  17 using System.Reflection;
  18 using System.Reflection.Emit;
  19 using System.Text;
  20 using System.Threading;
  21 using System.Text.RegularExpressions;
  22 using Oracle.DataAccess.Client;
  23 
  24 namespace Dapper
  25 {
  26     public static partial class SqlMapper
  27     {
  28         public interface IDynamicParameters
  29         {
  30             void AddParameters(IDbCommand command, Identity identity);
  31         }
  32         static Link<Type, Action<IDbCommand, bool>> bindByNameCache;
  33         static Action<IDbCommand, bool> GetBindByName(Type commandType)
  34         {
  35             if (commandType == null) return null; // GIGO
  36             Action<IDbCommand, bool> action;
  37             if (Link<Type, Action<IDbCommand, bool>>.TryGet(bindByNameCache, commandType, out action))
  38             {
  39                 return action;
  40             }
  41             var prop = commandType.GetProperty("BindByName", BindingFlags.Public | BindingFlags.Instance);
  42             action = null;
  43             ParameterInfo[] indexers;
  44             MethodInfo setter;
  45             if (prop != null && prop.CanWrite && prop.PropertyType == typeof(bool)
  46                 && ((indexers = prop.GetIndexParameters()) == null || indexers.Length == 0)
  47                 && (setter = prop.GetSetMethod()) != null
  48                 )
  49             {
  50                 var method = new DynamicMethod(commandType.Name + "_BindByName", null, new Type[] { typeof(IDbCommand), typeof(bool) });
  51                 var il = method.GetILGenerator();
  52                 il.Emit(OpCodes.Ldarg_0);
  53                 il.Emit(OpCodes.Castclass, commandType);
  54                 il.Emit(OpCodes.Ldarg_1);
  55                 il.EmitCall(OpCodes.Callvirt, setter, null);
  56                 il.Emit(OpCodes.Ret);
  57                 action = (Action<IDbCommand, bool>)method.CreateDelegate(typeof(Action<IDbCommand, bool>));
  58             }
  59             // cache it            
  60             Link<Type, Action<IDbCommand, bool>>.TryAdd(ref bindByNameCache, commandType, ref action);
  61             return action;
  62         }
  63         /// <summary>
  64         /// This is a micro-cache; suitable when the number of terms is controllable (a few hundred, for example),
  65         /// and strictly append-only; you cannot change existing values. All key matches are on **REFERENCE**
  66         /// equality. The type is fully thread-safe.
  67         /// </summary>
  68         class Link<TKey, TValue> where TKey : class
  69         {
  70             public static bool TryGet(Link<TKey, TValue> link, TKey key, out TValue value)
  71             {
  72                 while (link != null)
  73                 {
  74                     if ((object)key == (object)link.Key)
  75                     {
  76                         value = link.Value;
  77                         return true;
  78                     }
  79                     link = link.Tail;
  80                 }
  81                 value = default(TValue);
  82                 return false;
  83             }
  84             public static bool TryAdd(ref Link<TKey, TValue> head, TKey key, ref TValue value)
  85             {
  86                 bool tryAgain;
  87                 do
  88                 {
  89                     var snapshot = Interlocked.CompareExchange(ref head, null, null);
  90                     TValue found;
  91                     if (TryGet(snapshot, key, out found))
  92                     { // existing match; report the existing value instead
  93                         value = found;
  94                         return false;
  95                     }
  96                     var newNode = new Link<TKey, TValue>(key, value, snapshot);
  97                     // did somebody move our cheese?
  98                     tryAgain = Interlocked.CompareExchange(ref head, newNode, snapshot) != snapshot;
  99                 } while (tryAgain);
 100                 return true;
 101             }
 102             private Link(TKey key, TValue value, Link<TKey, TValue> tail)
 103             {
 104                 Key = key;
 105                 Value = value;
 106                 Tail = tail;
 107             }
 108             public TKey Key { get; private set; }
 109             public TValue Value { get; private set; }
 110             public Link<TKey, TValue> Tail { get; private set; }
 111         }
 112         class CacheInfo
 113         {
 114             public Func<IDataReader, object> Deserializer { get; set; }
 115             public Func<IDataReader, object>[] OtherDeserializers { get; set; }
 116             public Action<IDbCommand, object> ParamReader { get; set; }
 117             private int hitCount;
 118             public int GetHitCount() { return Interlocked.CompareExchange(ref hitCount, 0, 0); }
 119             public void RecordHit() { Interlocked.Increment(ref hitCount); }
 120         }
 121 
 122         public static event EventHandler QueryCachePurged;
 123         private static void OnQueryCachePurged()
 124         {
 125             var handler = QueryCachePurged;
 126             if (handler != null) handler(null, EventArgs.Empty);
 127         }
 128 #if CSHARP30
 129         private static readonly Dictionary<Identity, CacheInfo> _queryCache = new Dictionary<Identity, CacheInfo>();
 130         // note: conflicts between readers and writers are so short-lived that it isn't worth the overhead of
 131         // ReaderWriterLockSlim etc; a simple lock is faster
 132         private static void SetQueryCache(Identity key, CacheInfo value)
 133         {
 134             lock (_queryCache) { _queryCache[key] = value; }
 135         }
 136         private static bool TryGetQueryCache(Identity key, out CacheInfo value)
 137         {
 138             lock (_queryCache) { return _queryCache.TryGetValue(key, out value); }
 139         }
 140         public static void PurgeQueryCache()
 141         {
 142             lock (_queryCache)
 143             {
 144                  _queryCache.Clear();
 145             }
 146             OnQueryCachePurged();
 147         }
 148 #else
 149         static readonly System.Collections.Concurrent.ConcurrentDictionary<Identity, CacheInfo> _queryCache = new System.Collections.Concurrent.ConcurrentDictionary<Identity, CacheInfo>();
 150         private static void SetQueryCache(Identity key, CacheInfo value)
 151         {
 152             if (Interlocked.Increment(ref collect) == COLLECT_PER_ITEMS)
 153             {
 154                 CollectCacheGarbage();
 155             }
 156             _queryCache[key] = value;
 157         }
 158 
 159         private static void CollectCacheGarbage()
 160         {
 161             try
 162             {
 163                 foreach (var pair in _queryCache)
 164                 {
 165                     if (pair.Value.GetHitCount() <= COLLECT_HIT_COUNT_MIN)
 166                     {
 167                         CacheInfo cache;
 168                         _queryCache.TryRemove(pair.Key, out cache);
 169                     }
 170                 }
 171             }
 172 
 173             finally
 174             {
 175                 Interlocked.Exchange(ref collect, 0);
 176             }
 177         }
 178 
 179         private const int COLLECT_PER_ITEMS = 1000, COLLECT_HIT_COUNT_MIN = 0;
 180         private static int collect;
 181         private static bool TryGetQueryCache(Identity key, out CacheInfo value)
 182         {
 183             if (_queryCache.TryGetValue(key, out value))
 184             {
 185                 value.RecordHit();
 186                 return true;
 187             }
 188             value = null;
 189             return false;
 190         }
 191 
 192         public static void PurgeQueryCache()
 193         {
 194             _queryCache.Clear();
 195             OnQueryCachePurged();
 196         }
 197 
 198         public static int GetCachedSQLCount()
 199         {
 200             return _queryCache.Count;
 201         }
 202 
 203 
 204         public static IEnumerable<Tuple<string, string, int>> GetCachedSQL(int ignoreHitCountAbove = int.MaxValue)
 205         {
 206             var data = _queryCache.Select(pair => Tuple.Create(pair.Key.connectionString, pair.Key.sql, pair.Value.GetHitCount()));
 207             if (ignoreHitCountAbove < int.MaxValue) data = data.Where(tuple => tuple.Item3 <= ignoreHitCountAbove);
 208             return data;
 209         }
 210 
 211         public static IEnumerable<Tuple<int, int>> GetHashCollissions()
 212         {
 213             var counts = new Dictionary<int, int>();
 214             foreach (var key in _queryCache.Keys)
 215             {
 216                 int count;
 217                 if (!counts.TryGetValue(key.hashCode, out count))
 218                 {
 219                     counts.Add(key.hashCode, 1);
 220                 }
 221                 else
 222                 {
 223                     counts[key.hashCode] = count + 1;
 224                 }
 225             }
 226             return from pair in counts
 227                    where pair.Value > 1
 228                    select Tuple.Create(pair.Key, pair.Value);
 229 
 230         }
 231 #endif
 232 
 233 
 234         static readonly Dictionary<Type, DbType> typeMap;
 235 
 236         static SqlMapper()
 237         {
 238             typeMap = new Dictionary<Type, DbType>();
 239             typeMap[typeof(byte)] = DbType.Byte;
 240             typeMap[typeof(sbyte)] = DbType.SByte;
 241             typeMap[typeof(short)] = DbType.Int16;
 242             typeMap[typeof(ushort)] = DbType.UInt16;
 243             typeMap[typeof(int)] = DbType.Int32;
 244             typeMap[typeof(uint)] = DbType.UInt32;
 245             typeMap[typeof(long)] = DbType.Int64;
 246             typeMap[typeof(ulong)] = DbType.UInt64;
 247             typeMap[typeof(float)] = DbType.Single;
 248             typeMap[typeof(double)] = DbType.Double;
 249             typeMap[typeof(decimal)] = DbType.Decimal;
 250             typeMap[typeof(bool)] = DbType.Boolean;
 251             typeMap[typeof(string)] = DbType.String;
 252             typeMap[typeof(char)] = DbType.StringFixedLength;
 253             typeMap[typeof(Guid)] = DbType.Guid;
 254             typeMap[typeof(DateTime)] = DbType.DateTime;
 255             typeMap[typeof(DateTimeOffset)] = DbType.DateTimeOffset;
 256             typeMap[typeof(byte[])] = DbType.Binary;
 257             typeMap[typeof(byte?)] = DbType.Byte;
 258             typeMap[typeof(sbyte?)] = DbType.SByte;
 259             typeMap[typeof(short?)] = DbType.Int16;
 260             typeMap[typeof(ushort?)] = DbType.UInt16;
 261             typeMap[typeof(int?)] = DbType.Int32;
 262             typeMap[typeof(uint?)] = DbType.UInt32;
 263             typeMap[typeof(long?)] = DbType.Int64;
 264             typeMap[typeof(ulong?)] = DbType.UInt64;
 265             typeMap[typeof(float?)] = DbType.Single;
 266             typeMap[typeof(double?)] = DbType.Double;
 267             typeMap[typeof(decimal?)] = DbType.Decimal;
 268             typeMap[typeof(bool?)] = DbType.Boolean;
 269             typeMap[typeof(char?)] = DbType.StringFixedLength;
 270             typeMap[typeof(Guid?)] = DbType.Guid;
 271             typeMap[typeof(DateTime?)] = DbType.DateTime;
 272             typeMap[typeof(DateTimeOffset?)] = DbType.DateTimeOffset;
 273             typeMap[typeof(System.Data.Linq.Binary)] = DbType.Binary;
 274         }
 275 
 276         private static DbType LookupDbType(Type type, string name)
 277         {
 278             DbType dbType;
 279             var nullUnderlyingType = Nullable.GetUnderlyingType(type);
 280             if (nullUnderlyingType != null) type = nullUnderlyingType;
 281             if (type.IsEnum)
 282             {
 283                 type = Enum.GetUnderlyingType(type);
 284             }
 285             if (typeMap.TryGetValue(type, out dbType))
 286             {
 287                 return dbType;
 288             }
 289             if (typeof(IEnumerable).IsAssignableFrom(type))
 290             {
 291                 // use xml to denote its a list, hacky but will work on any DB
 292                 return DbType.Xml;
 293             }
 294 
 295 
 296             throw new NotSupportedException(string.Format("The member {0} of type {1} cannot be used as a parameter value", name, type));
 297         }
 298 
 299         public class Identity : IEquatable<Identity>
 300         {
 301             internal Identity ForGrid(Type primaryType, int gridIndex)
 302             {
 303                 return new Identity(sql, commandType, connectionString, primaryType, parametersType, null, gridIndex);
 304             }
 305 
 306             internal Identity ForGrid(Type primaryType, Type[] otherTypes, int gridIndex)
 307             {
 308                 return new Identity(sql, commandType, connectionString, primaryType, parametersType, otherTypes, gridIndex);
 309             }
 310 
 311             public Identity ForDynamicParameters(Type type)
 312             {
 313                 return new Identity(sql, commandType, connectionString, this.type, type, null, -1);
 314             }
 315 
 316             internal Identity(string sql, CommandType? commandType, IDbConnection connection, Type type, Type parametersType, Type[] otherTypes)
 317                 : this(sql, commandType, connection.ConnectionString, type, parametersType, otherTypes, 0)
 318             { }
 319             private Identity(string sql, CommandType? commandType, string connectionString, Type type, Type parametersType, Type[] otherTypes, int gridIndex)
 320             {
 321                 this.sql = sql;
 322                 this.commandType = commandType;
 323                 this.connectionString = connectionString;
 324                 this.type = type;
 325                 this.parametersType = parametersType;
 326                 this.gridIndex = gridIndex;
 327                 unchecked
 328                 {
 329                     hashCode = 17; // we *know* we are using this in a dictionary, so pre-compute this
 330                     hashCode = hashCode * 23 + commandType.GetHashCode();
 331                     hashCode = hashCode * 23 + gridIndex.GetHashCode();
 332                     hashCode = hashCode * 23 + (sql == null ? 0 : sql.GetHashCode());
 333                     hashCode = hashCode * 23 + (type == null ? 0 : type.GetHashCode());
 334                     if (otherTypes != null)
 335                     {
 336                         foreach (var t in otherTypes)
 337                         {
 338                             hashCode = hashCode * 23 + (t == null ? 0 : t.GetHashCode());
 339                         }
 340                     }
 341                     hashCode = hashCode * 23 + (connectionString == null ? 0 : connectionString.GetHashCode());
 342                     hashCode = hashCode * 23 + (parametersType == null ? 0 : parametersType.GetHashCode());
 343                 }
 344             }
 345             public override bool Equals(object obj)
 346             {
 347                 return Equals(obj as Identity);
 348             }
 349             public readonly string sql;
 350             public readonly CommandType? commandType;
 351             public readonly int hashCode, gridIndex;
 352             private readonly Type type;
 353             public readonly string connectionString;
 354             public readonly Type parametersType;
 355             public override int GetHashCode()
 356             {
 357                 return hashCode;
 358             }
 359             public bool Equals(Identity other)
 360             {
 361                 return
 362                     other != null &&
 363                     gr