搬家到博客园: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