Dapper完美兼容Oracle，執行存儲過程，並返回結果集。，dapperoracle
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 gridIndex == other.gridIndex &&
364 type == other.type &&
365 sql == other.sql &&
366 commandType == other.commandType &&
367 connectionString == other.connectionString &&
368 parametersType == other.parametersType;
369 }
370 }
371
372 #if CSHARP30
373 /// <summary>
374 /// Execute parameterized SQL
375 /// </summary>
376 /// <returns>Number of rows affected</returns>
377 public static int Execute(this IDbConnection cnn, string sql, object param)
378 {
379 return Execute(cnn, sql, param, null, null, null);
380 }
381 /// <summary>
382 /// Executes a query, returning the data typed as per T
383 /// </summary>
384 /// <remarks>the dynamic param may seem a bit odd, but this works around a major usability issue in vs, if it is Object vs completion gets annoying. Eg type new <space> get new object</remarks>
385 /// <returns>A sequence of data of the supplied type; if a basic type (int, string, etc) is queried then the data from the first column in assumed, otherwise an instance is
386 /// created per row, and a direct column-name===member-name mapping is assumed (case insensitive).
387 /// </returns>
388 public static IEnumerable<T> Query<T>(this IDbConnection cnn, string sql, object param)
389 {
390 return Query<T>(cnn, sql, param, null, true, null, null);
391 }
392
393 #endif
394 /// <summary>
395 /// Execute parameterized SQL
396 /// </summary>
397 /// <returns>Number of rows affected</returns>
398 public static int Execute(
399 #if CSHARP30
400 this IDbConnection cnn, string sql, object param, IDbTransaction transaction, int? commandTimeout, CommandType? commandType
401 #else
402 this IDbConnection cnn, string sql, dynamic param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null
403 #endif
404 )
405 {
406 IEnumerable multiExec = (object)param as IEnumerable;
407 Identity identity;
408 CacheInfo info = null;
409 if (multiExec != null && !(multiExec is string))
410 {
411 bool isFirst = true;
412 int total = 0;
413 using (var cmd = SetupCommand(cnn, transaction, sql, null, null, commandTimeout, commandType))
414 {
415
416 string masterSql = null;
417 foreach (var obj in multiExec)
418 {
419 if (isFirst)
420 {
421 masterSql = cmd.CommandText;
422 isFirst = false;
423 identity = new Identity(sql, cmd.CommandType, cnn, null, obj.GetType(), null);
424 info = GetCacheInfo(identity);
425 }
426 else
427 {
428 cmd.CommandText = masterSql; // because we do magic replaces on "in" etc
429 cmd.Parameters.Clear(); // current code is Add-tastic
430 }
431 info.ParamReader(cmd, obj);
432 total += cmd.ExecuteNonQuery();
433 }
434 }
435 return total;
436 }
437
438 // nice and simple
439 identity = new Identity(sql, commandType, cnn, null, (object)param == null ? null : ((object)param).GetType(), null);
440 info = GetCacheInfo(identity);
441 return ExecuteCommand(cnn, transaction, sql, info.ParamReader, (object)param, commandTimeout, commandType);
442 }
443 #if !CSHARP30
444 /// <summary>
445 /// Return a list of dynamic objects, reader is closed after the call
446 /// </summary>
447 public static IEnumerable<dynamic> Query(this IDbConnection cnn, string sql, dynamic param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
448 {
449 return Query<FastExpando>(cnn, sql, param as object, transaction, buffered, commandTimeout, commandType);
450 }
451 #endif
452
453 /// <summary>
454 /// Executes a query, returning the data typed as per T
455 /// </summary>
456 /// <remarks>the dynamic param may seem a bit odd, but this works around a major usability issue in vs, if it is Object vs completion gets annoying. Eg type new <space> get new object</remarks>
457 /// <returns>A sequence of data of the supplied type; if a basic type (int, string, etc) is queried then the data from the first column in assumed, otherwise an instance is
458 /// created per row, and a direct column-name===member-name mapping is assumed (case insensitive).
459 /// </returns>
460 public static IEnumerable<T> Query<T>(
461 #if CSHARP30
462 this IDbConnection cnn, string sql, object param, IDbTransaction transaction, bool buffered, int? commandTimeout, CommandType? commandType
463 #else
464 this IDbConnection cnn, string sql, dynamic param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null
465 #endif
466 )
467 {
468 var data = QueryInternal<T>(cnn, sql, param as object, transaction, commandTimeout, commandType);
469 return buffered ? data.ToList() : data;
470 }
471
472 /// <summary>
473 /// Execute a command that returns multiple result sets, and access each in turn
474 /// </summary>
475 public static GridReader QueryMultiple(
476 #if CSHARP30
477 this IDbConnection cnn, string sql, object param, IDbTransaction transaction, int? commandTimeout, CommandType? commandType
478 #else
479 this IDbConnection cnn, string sql, dynamic param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null
480 #endif
481 )
482 {
483 Identity identity = new Identity(sql, commandType, cnn, typeof(GridReader), (object)param == null ? null : ((object)param).GetType(), null);
484 CacheInfo info = GetCacheInfo(identity);
485
486 IDbCommand cmd = null;
487 IDataReader reader = null;
488 try
489 {
490 cmd = SetupCommand(cnn, transaction, sql, info.ParamReader, (object)param, commandTimeout, commandType);
491 reader = cmd.ExecuteReader();
492 return new GridReader(cmd, reader, identity);
493 }
494 catch
495 {
496 if (reader != null) reader.Dispose();
497 if (cmd != null) cmd.Dispose();
498 throw;
499 }
500 }
501
502 /// <summary>
503 /// Return a typed list of objects, reader is closed after the call
504 /// </summary>
505 private static IEnumerable<T> QueryInternal<T>(this IDbConnection cnn, string sql, object param, IDbTransaction transaction, int? commandTimeout, CommandType? commandType)
506 {
507 var identity = new Identity(sql, commandType, cnn, typeof(T), param == null ? null : param.GetType(), null);
508 var info = GetCacheInfo(identity);
509
510 using (var cmd = SetupCommand(cnn, transaction, sql, info.ParamReader, param, commandTimeout, commandType))
511 {
512 using (var reader = cmd.ExecuteReader())
513 {
514 Func<Func<IDataReader, object>> cacheDeserializer = () =>
515 {
516 info.Deserializer = GetDeserializer(typeof(T), reader, 0, -1, false);
517 SetQueryCache(identity, info);
518 return info.Deserializer;
519 };
520
521 if (info.Deserializer == null)
522 {
523 cacheDeserializer();
524 }
525
526 var deserializer = info.Deserializer;
527
528 while (reader.Read())
529 {
530 object next;
531 try
532 {
533 next = deserializer(reader);
534 }
535 catch (DataException)
536 {
537 // give it another shot, in case the underlying schema changed
538 deserializer = cacheDeserializer();
539 next = deserializer(reader);
540 }
541 yield return (T)next;
542 }
543
544 }
545 }
546 }
547
548 /// <summary>
549 /// Maps a query to objects
550 /// </summary>
551 /// <typeparam name="T">The return type</typeparam>
552 /// <typeparam name="U"></typeparam>
553 /// <param name="cnn"></param>
554 /// <param name="sql"></param>
555 /// <param name="map"></param>
556 /// <param name="param"></param>
557 /// <param name="transaction"></param>
558 /// <param name="buffered"></param>
559 /// <param name="splitOn">The Field we should split and read the second object from (default: id)</param>
560 /// <param name="commandTimeout">Number of seconds before command execution timeout</param>
561 /// <returns></returns>
562 public static IEnumerable<TReturn> Query<TFirst, TSecond, TReturn>(
563 #if CSHARP30
564 this IDbConnection cnn, string sql, Func<TFirst, TSecond, TReturn> map, object param, IDbTransaction transaction, bool buffered, string splitOn, int? commandTimeout, CommandType? commandType
565 #else
566 this IDbConnection cnn, string sql, Func<TFirst, TSecond, TReturn> map, dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null
567 #endif
568 )
569 {
570 return MultiMap<TFirst, TSecond, DontMap, DontMap, DontMap, TReturn>(cnn, sql, map, param as object, transaction, buffered, splitOn, commandTimeout, commandType);
571 }
572
573 public static IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TReturn>(
574 #if CSHARP30
575 this IDbConnection cnn, string sql, Func<TFirst, TSecond, TThird, TReturn> map, object param, IDbTransaction transaction, bool buffered, string splitOn, int? commandTimeout, CommandType? commandType
576 #else
577 this IDbConnection cnn, string sql, Func<TFirst, TSecond, TThird, TReturn> map, dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null
578 #endif
579 )
580 {
581 return MultiMap<TFirst, TSecond, TThird, DontMap, DontMap, TReturn>(cnn, sql, map, param as object, transaction, buffered, splitOn, commandTimeout, commandType);
582 }
583
584 public static IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TFourth, TReturn>(
585 #if CSHARP30
586 this IDbConnection cnn, string sql, Func<TFirst, TSecond, TThird, TFourth, TReturn> map, object param, IDbTransaction transaction, bool buffered, string splitOn, int? commandTimeout, CommandType? commandType
587 #else
588 this IDbConnection cnn, string sql, Func<TFirst, TSecond, TThird, TFourth, TReturn> map, dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null
589 #endif
590 )
591 {
592 return MultiMap<TFirst, TSecond, TThird, TFourth, DontMap, TReturn>(cnn, sql, map, param as object, transaction, buffered, splitOn, commandTimeout, commandType);
593 }
594 #if !CSHARP30
595 public static IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(this IDbConnection cnn, string sql, Func<TFirst, TSecond, TThird, TFourth, TFifth, TReturn> map, dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)
596 {
597 return MultiMap<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(cnn, sql, map, param as object, transaction, buffered, splitOn, commandTimeout, commandType);
598 }
599 #endif
600 class DontMap { }
601 static IEnumerable<TReturn> MultiMap<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(
602 this IDbConnection cnn, string sql, object map, object param, IDbTransaction transaction, bool buffered, string splitOn, int? commandTimeout, CommandType? commandType)
603 {
604 var results = MultiMapImpl<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(cnn, sql, map, param, transaction, splitOn, commandTimeout, commandType, null, null);
605 return buffered ? results.ToList() : results;
606 }
607
608
609 static IEnumerable<TReturn> MultiMapImpl<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(this IDbConnection cnn, string sql, object map, object param, IDbTransaction transaction, string splitOn, int? commandTimeout, CommandType? commandType, IDataReader reader, Identity identity)
610 {
611 identity = identity ?? new Identity(sql, commandType, cnn, typeof(TFirst), (object)param == null ? null : ((object)param).GetType(), new[] { typeof(TFirst), typeof(TSecond), typeof(TThird), typeof(TFourth), typeof(TFifth) });
612 CacheInfo cinfo = GetCacheInfo(identity);
613
614 IDbCommand ownedCommand = null;
615 IDataReader ownedReader = null;
616
617 try
618 {
619 if (reader == null)
620 {
621 ownedCommand = SetupCommand(cnn, transaction, sql, cinfo.ParamReader, (object)param, commandTimeout, commandType);
622 ownedReader = ownedCommand.ExecuteReader();
623 reader = ownedReader;
624 }
625 Func<IDataReader, object> deserializer = null;
626 Func<IDataReader, object>[] otherDeserializers = null;
627
628 Action cacheDeserializers = () =>
629 {
630 var deserializers = GenerateDeserializers(new Type[] { typeof(TFirst), typeof(TSecond), typeof(TThird), typeof(TFourth), typeof(TFifth) }, splitOn, reader);
631 deserializer = cinfo.Deserializer = deserializers[0];
632 otherDeserializers = cinfo.OtherDeserializers = deserializers.Skip(1).ToArray();
633 SetQueryCache(identity, cinfo);
634 };
635
636 if ((deserializer = cinfo.Deserializer) == null || (otherDeserializers = cinfo.OtherDeserializers) == null)
637 {
638 cacheDeserializers();
639 }
640
641 Func<IDataReader, TReturn> mapIt = GenerateMapper<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(deserializer, otherDeserializers, map);
642
643 if (mapIt != null)
644 {
645 while (reader.Read())
646 {
647 TReturn next;
648 try
649 {
650 next = mapIt(reader);
651 }
652 catch (DataException)
653 {
654 cacheDeserializers();
655 mapIt = GenerateMapper<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(deserializer, otherDeserializers, map);
656 next = mapIt(reader);
657 }
658 yield return next;
659 }
660 }
661 }
662 finally
663 {
664 try
665 {
666 if (ownedReader != null)
667 {
668 ownedReader.Dispose();
669 }
670 }
671 finally
672 {
673 if (ownedCommand != null)
674 {
675 ownedCommand.Dispose();
676 }
677 }
678 }
679 }
680
681 private static Func<IDataReader, TReturn> GenerateMapper<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(Func<IDataReader, object> deserializer, Func<IDataReader, object>[] otherDeserializers, object map)
682 {
683 switch (otherDeserializers.Length)
684 {
685 case 1:
686 return r => ((Func<TFirst, TSecond, TReturn>)map)((TFirst)deserializer(r), (TSecond)otherDeserializers[0](r));
687 case 2:
688 return r => ((Func<TFirst, TSecond, TThird, TReturn>)map)((TFirst)deserializer(r), (TSecond)otherDeserializers[0](r), (TThird)otherDeserializers[1](r));
689 case 3:
690 return r => ((Func<TFirst, TSecond, TThird, TFourth, TReturn>)map)((TFirst)deserializer(r), (TSecond)otherDeserializers[0](r), (TThird)otherDeserializers[1](r), (TFourth)otherDeserializers[2](r));
691 #if !CSHARP30
692 case 4:
693 return r => ((Func<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>)map)((TFirst)deserializer(r), (TSecond)otherDeserializers[0](r), (TThird)otherDeserializers[1](r), (TFourth)otherDeserializers[2](r), (TFifth)otherDeserializers[3](r));
694 #endif
695 default:
696 throw new NotSupportedException();
697 }
698 }
699
700 private static Func<IDataReader, object>[] GenerateDeserializers(Type[] types, string splitOn, IDataReader reader)
701 {
702 int current = 0;
703 var splits = splitOn.Split(',').ToArray();
704 var splitIndex = 0;
705
706 Func<Type, int> nextSplit = type =>
707 {
708 var currentSplit = splits[splitIndex];
709 if (splits.Length > splitIndex + 1)
710 {
711 splitIndex++;
712 }
713
714 bool skipFirst = false;
715 int startingPos = current + 1;
716 // if our current type has the split, skip the first time you see it.
717 if (type != typeof(Object))
718 {
719 var props = GetSettableProps(type);
720 var fields = GetSettableFields(type);
721
722 foreach (var name in props.Select(p => p.Name).Concat(fields.Select(f => f.Name)))
723 {
724 if (string.Equals(name, currentSplit, StringComparison.OrdinalIgnoreCase))
725 {
726 skipFirst = true;
727 startingPos = current;
728 break;
729 }
730 }
731
732 }
733
734 int pos;
735 for (pos = startingPos; pos < reader.FieldCount; pos++)
736 {
737 // some people like ID some id ... assuming case insensitive splits for now
738 if (splitOn == "*")
739 {
740 break;
741 }
742 if (string.Equals(reader.GetName(pos), currentSplit, StringComparison.OrdinalIgnoreCase))
743 {
744 if (skipFirst)
745 {
746 skipFirst = false;
747 }
748 else
749 {
750 break;
751 }
752 }
753 }
754 current = pos;
755 return pos;
756 };
757
758 var deserializers = new List<Func<IDataReader, object>>();
759 int split = 0;
760 bool first = true;
761 foreach (var type in types)
762 {
763 if (type != typeof(DontMap))
764 {
765 int next = nextSplit(type);
766 deserializers.Add(GetDeserializer(type, reader, split, next - split, /* returnNullIfFirstMissing: */ !first));
767 first = false;
768 split = next;
769 }
770 }
771
772 return deserializers.ToArray();
773 }
774
775 private static CacheInfo GetCacheInfo(Identity identity)
776 {
777 CacheInfo info;
778 if (!TryGetQueryCache(identity, out info))
779 {
780 info = new CacheInfo();
781 if (identity.parametersType != null)
782 {
783 if (typeof(IDynamicParameters).IsAssignableFrom(identity.parametersType))
784 {
785 info.ParamReader = (cmd, obj) => { (obj as IDynamicParameters).AddParameters(cmd, identity); };
786 }
787 else
788 {
789 info.ParamReader = CreateParamInfoGenerator(identity);
790 }
791 }
792 SetQueryCache(identity, info);
793 }
794 return info;
795 }
796
797 private static Func<IDataReader, object> GetDeserializer(Type type, IDataReader reader, int startBound, int length, bool returnNullIfFirstMissing)
798 {
799 #if !CSHARP30
800 // dynamic is passed in as Object ... by c# design
801 if (type == typeof(object)
802 || type == typeof(FastExpando))
803 {
804 return GetDynamicDeserializer(reader, startBound, length, returnNullIfFirstMissing);
805 }
806 #endif
807
808 if (type.IsClass && type != typeof(string) && type != typeof(byte[]) && type != typeof(System.Data.Linq.Binary))
809 {
810 return GetClassDeserializer(type, reader, startBound, length, returnNullIfFirstMissing);
811 }
812 return GetStructDeserializer(type, startBound);
813
814 }
815 #if !CSHARP30
816 private class FastExpando : System.Dynamic.DynamicObject, IDictionary<string, object>
817 {
818 IDictionary<string, object> data;
819
820 public static FastExpando Attach(IDictionary<string, object> data)
821 {
822 return new FastExpando { data = data };
823 }
824
825 public override bool TrySetMember(System.Dynamic.SetMemberBinder binder, object value)
826 {
827 data[binder.Name] = value;
828 return true;
829 }
830
831 public override bool TryGetMember(System.Dynamic.GetMemberBinder binder, out object result)
832 {
833 return data.TryGetValue(binder.Name, out result);
834 }
835
836 #region IDictionary<string,object> Members
837
838 void IDictionary<string, object>.Add(string key, object value)
839 {
840 throw new NotImplementedException();
841 }
842
843 bool IDictionary<string, object>.ContainsKey(string key)
844 {
845 return data.ContainsKey(key);
846 }
847
848 ICollection<string> IDictionary<string, object>.Keys
849 {
850 get { return data.Keys; }
851 }
852
853 bool IDictionary<string, object>.Remove(string key)
854 {
855 throw new NotImplementedException();
856 }
857
858 bool IDictionary<string, object>.TryGetValue(string key, out object value)
859 {
860 return data.TryGetValue(key, out value);
861 }
862
863 ICollection<object> IDictionary<string, object>.Values
864 {
865 get { return data.Values; }
866 }
867
868 object IDictionary<string, object>.this[string key]
869 {
870 get
871 {
872 return data[key];
873 }
874 set
875 {
876 throw new NotImplementedException();
877 }
878 }
879
880 #endregion
881
882 #region ICollection<KeyValuePair<string,object>> Members
883
884 void ICollection<KeyValuePair<string, object>>.Add(KeyValuePair<string, object> item)
885 {
886 throw new NotImplementedException();
887 }
888
889 void ICollection<KeyValuePair<string, object>>.Clear()
890 {
891 throw new NotImplementedException();
892 }
893
894 bool ICollection<KeyValuePair<string, object>>.Contains(KeyValuePair<string, object> item)
895 {
896 return data.Contains(item);
897 }
898
899 void ICollection<KeyValuePair<string, object>>.CopyTo(KeyValuePair<string, object>[] array, int arrayIndex)
900 {
901 data.CopyTo(array, arrayIndex);
902 }
903
904 int ICollection<KeyValuePair<string, object>>.Count
905 {
906 get { return data.Count; }
907 }
908
909 bool ICollection<KeyValuePair<string, object>>.IsReadOnly
910 {
911 get { return true; }
912 }
913
914 bool ICollection<KeyValuePair<string, object>>.Remove(KeyValuePair<string, object> item)
915 {
916 throw new NotImplementedException();
917 }
918
919 #endregion
920
921 #region IEnumerable<KeyValuePair<string,object>> Members
922
923 IEnumerator<KeyValuePair<string, object>> IEnumerable<KeyValuePair<string, object>>.GetEnumerator()
924 {
925 return data.GetEnumerator();
926 }
927
928 #endregion
929
930 #region IEnumerable Members
931
932 IEnumerator IEnumerable.GetEnumerator()
933 {
934 return data.GetEnumerator();
935 }
936
937 #endregion
938 }
939
940
941 private static Func<IDataReader, object> GetDynamicDeserializer(IDataRecord reader, int startBound, int length, bool returnNullIfFirstMissing)
942 {
943 var fieldCount = reader.FieldCount;
944 if (length == -1)
945 {
946 length = fieldCount - startBound;
947 }
948
949 if (fieldCount <= startBound)
950 {
951 throw new ArgumentException("When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id", "splitOn");
952 }
953
954 return
955 r =>
956 {
957 IDictionary<string, object> row = new Dictionary<string, object>(length);
958 for (var i = startBound; i < startBound + length; i++)
959 {
960 var tmp = r.GetValue(i);
961 tmp = tmp == DBNull.Value ? null : tmp;
962 row[r.GetName(i)] = tmp;
963 if (returnNullIfFirstMissing && i == startBound && tmp == null)
964 {
965 return null;
966 }
967 }
968 //we know this is an object so it will not box
969 return FastExpando.Attach(row);
970 };
971 }
972 #endif
973 [Browsable(false), EditorBrowsable(EditorBrowsableState.Never)]
974 [Obsolete("This method is for internal usage only", false)]
975 public static char ReadChar(object value)
976 {
977 if (value == null || value is DBNull) throw new ArgumentNullException("value");
978 string s = value as string;
979 if (s == null || s.Length != 1) throw new ArgumentException("A single-character was expected", "value");
980 return s[0];
981 }
982 [Browsable(false), EditorBrowsable(EditorBrowsableState.Never)]
983 [Obsolete("This method is for internal usage only", false)]
984 public static char? ReadNullableChar(object value)
985 {
986 if (value == null || value is DBNull) return null;
987 string s = value as string;
988 if (s == null || s.Length != 1) throw new ArgumentException("A single-character was expected", "value");
989 return s[0];
990 }
991 [Browsable(false), EditorBrowsable(EditorBrowsableState.Never)]
992 [Obsolete("This method is for internal usage only", true)]
993 public static void PackListParameters(IDbCommand command, string namePrefix, object value)
994 {
995 // initially we tried TVP, however it performs quite poorly.
996 // keep in mind SQL support up to 2000 params easily in sp_executesql, needing more is rare
997
998 var list = value as IEnumerable;
999 var count = 0;
1000
1001 if (list != null)
1002 {
1003 bool isString = value is IEnumerable<string>;
1004 foreach (var item in list)
1005 {
1006 count++;
1007 var listParam = command.CreateParameter();
1008 listParam.ParameterName = namePrefix + count;
1009 listParam.Value = item ?? DBNull.Value;
1010 if (isString)
1011 {
1012 listParam.Size = 4000;
1013 if (item != null && ((string)item).Length > 4000)
1014 {
1015 listParam.Size = -1;
1016 }
1017 }
1018 command.Parameters.Add(listParam);
1019 }
1020
1021 if (count == 0)
1022 {
1023 command.CommandText = Regex.Replace(command.CommandText, @"[?@:]" + Regex.Escape(namePrefix), "(SELECT NULL WHERE 1 = 0)");
1024 }
1025 else
1026 {
1027 command.CommandText = Regex.Replace(command.CommandText, @"[?@:]" + Regex.Escape(namePrefix), match =>
1028 {
1029 var grp = match.Value;
1030 var sb = new StringBuilder("(").Append(grp).Append(1);
1031 for (int i = 2; i <= count; i++)
1032 {
1033 sb.Append(',').Append(grp).Append(i);
1034 }
1035 return sb.Append(')').ToString();
1036 });
1037 }
1038 }
1039
1040 }
1041
1042 private static IEnumerable<PropertyInfo> FilterParameters(IEnumerable<PropertyInfo> parameters, string sql)
1043 {
1044 return parameters.Where(p => Regex.IsMatch(sql, "[@:]" + p.Name + "([^a-zA-Z0-9_]+|$)", RegexOptions.IgnoreCase | RegexOptions.Multiline));
1045 }
1046
1047 public static Action<IDbCommand, object> CreateParamInfoGenerator(Identity identity)
1048 {
1049 Type type = identity.parametersType;
1050 bool filterParams = identity.commandType.GetValueOrDefault(CommandType.Text) == CommandType.Text;
1051
1052 var dm = new DynamicMethod(string.Format("ParamInfo{0}", Guid.NewGuid()), null, new[] { typeof(IDbCommand), typeof(object) }, type, true);
1053
1054 var il = dm.GetILGenerator();
1055
1056 il.DeclareLocal(type); // 0
1057 bool haveInt32Arg1 = false;
1058 il.Emit(OpCodes.Ldarg_1); // stack is now [untyped-param]
1059 il.Emit(OpCodes.Unbox_Any, type); // stack is now [typed-param]
1060 il.Emit(OpCodes.Stloc_0);// stack is now empty
1061
1062 il.Emit(OpCodes.Ldarg_0); // stack is now [command]
1063 il.EmitCall(OpCodes.Callvirt, typeof(IDbCommand).GetProperty("Parameters").GetGetMethod(), null); // stack is now [parameters]
1064
1065 IEnumerable<PropertyInfo> props = type.GetProperties().OrderBy(p => p.Name);
1066 if (filterParams)
1067 {
1068 props = FilterParameters(props, identity.sql);
1069 }
1070 foreach (var prop in props)
1071 {
1072 if (filterParams)
1073 {
1074 if (identity.sql.IndexOf("@" + prop.Name, StringComparison.InvariantCultureIgnoreCase) < 0
1075 && identity.sql.IndexOf(":" + prop.Name, StringComparison.InvariantCultureIgnoreCase) < 0)
1076 { // can't see the parameter in the text (even in a comment, etc) - burn it with fire
1077 continue;
1078 }
1079 }
1080 if (prop.PropertyType == typeof(DbString))
1081 {
1082 il.Emit(OpCodes.Ldloc_0); // stack is now [parameters] [typed-param]
1083 il.Emit(OpCodes.Callvirt, prop.GetGetMethod()); // stack is [parameters] [dbstring]
1084 il.Emit(OpCodes.Ldarg_0); // stack is now [parameters] [dbstring] [command]
1085 il.Emit(OpCodes.Ldstr, prop.Name); // stack is now [parameters] [dbstring] [command] [name]
1086 il.EmitCall(OpCodes.Callvirt, typeof(DbString).GetMethod("AddParameter"), null); // stack is now [parameters]
1087 continue;
1088 }
1089 DbType dbType = LookupDbType(prop.PropertyType, prop.Name);
1090 if (dbType == DbType.Xml)
1091 {
1092 // this actually represents special handling for list types;
1093 il.Emit(OpCodes.Ldarg_0); // stack is now [parameters] [command]
1094 il.Emit(OpCodes.Ldstr, prop.Name); // stack is now [parameters] [command] [name]
1095 il.Emit(OpCodes.Ldloc_0); // stack is now [parameters] [command] [name] [typed-param]
1096 il.Emit(OpCodes.Callvirt, prop.GetGetMethod()); // stack is [parameters] [command] [name] [typed-value]
1097 if (prop.PropertyType.IsValueType)
1098 {
1099 il.Emit(OpCodes.Box, prop.PropertyType); // stack is [parameters] [command] [name] [boxed-value]
1100 }
1101 il.EmitCall(OpCodes.Call, typeof(SqlMapper).GetMethod("PackListParameters"), null); // stack is [parameters]
1102 continue;
1103 }
1104 il.Emit(OpCodes.Dup); // stack is now [parameters] [parameters]
1105
1106 il.Emit(OpCodes.Ldarg_0); // stack is now [parameters] [parameters] [command]
1107 il.EmitCall(OpCodes.Callvirt, typeof(IDbCommand).GetMethod("CreateParameter"), null);// stack is now [parameters] [parameters] [parameter]
1108
1109 il.Emit(OpCodes.Dup);// stack is now [parameters] [parameters] [parameter] [parameter]
1110 il.Emit(OpCodes.Ldstr, prop.Name); // stack is now [parameters] [parameters] [parameter] [parameter] [name]
1111 il.EmitCall(OpCodes.Callvirt, typeof(IDataParameter).GetProperty("ParameterName").GetSetMethod(), null);// stack is now [parameters] [parameters] [parameter]
1112
1113 il.Emit(OpCodes.Dup);// stack is now [parameters] [parameters] [parameter] [parameter]
1114 EmitInt32(il, (int)dbType);// stack is now [parameters] [parameters] [parameter] [parameter] [db-type]
1115
1116 il.EmitCall(OpCodes.Callvirt, typeof(IDataParameter).GetProperty("DbType").GetSetMethod(), null);// stack is now [parameters] [parameters] [parameter]
1117
1118 il.Emit(OpCodes.Dup);// stack is now [parameters] [parameters] [parameter] [parameter]
1119 EmitInt32(il, (int)ParameterDirection.Input);// stack is now [parameters] [parameters] [parameter] [parameter] [dir]
1120 il.EmitCall(OpCodes.Callvirt, typeof(IDataParameter).GetProperty("Direction").GetSetMethod(), null);// stack is now [parameters] [parameters] [parameter]
1121
1122 il.Emit(OpCodes.Dup);// stack is now [parameters] [parameters] [parameter] [parameter]
1123 il.Emit(OpCodes.Ldloc_0); // stack is now [parameters] [parameters] [parameter] [parameter] [typed-param]
1124 il.Emit(OpCodes.Callvirt, prop.GetGetMethod()); // stack is [parameters] [parameters] [parameter] [parameter] [typed-value]
1125 bool checkForNull = true;
1126 if (prop.PropertyType.IsValueType)
1127 {
1128 il.Emit(OpCodes.Box, prop.PropertyType); // stack is [parameters] [parameters] [parameter] [parameter] [boxed-value]
1129 if (Nullable.GetUnderlyingType(prop.PropertyType) == null)
1130 { // struct but not Nullable<T>; boxed value cannot be null
1131 checkForNull = false;
1132 }
1133 }
1134 if (checkForNull)
1135 {
1136 if (dbType == DbType.String && !haveInt32Arg1)
1137 {
1138 il.DeclareLocal(typeof(int));
1139 haveInt32Arg1 = true;
1140 }
1141 // relative stack: [boxed value]
1142 il.Emit(OpCodes.Dup);// relative stack: [boxed value] [boxed value]
1143 Label notNull = il.DefineLabel();
1144 Label? allDone = dbType == DbType.String ? il.DefineLabel() : (Label?)null;
1145 il.Emit(OpCodes.Brtrue_S, notNull);
1146 // relative stack [boxed value = null]
1147 il.Emit(OpCodes.Pop); // relative stack empty
1148 il.Emit(OpCodes.Ldsfld, typeof(DBNull).GetField("Value")); // relative stack [DBNull]
1149 if (dbType == DbType.String)
1150 {
1151 EmitInt32(il, 0);
1152 il.Emit(OpCodes.Stloc_1);
1153 }
1154 if (allDone != null) il.Emit(OpCodes.Br_S, allDone.Value);
1155 il.MarkLabel(notNull);
1156 if (prop.PropertyType == typeof(string))
1157 {
1158 il.Emit(OpCodes.Dup); // [string] [string]
1159 il.EmitCall(OpCodes.Callvirt, typeof(string).GetProperty("Length").GetGetMethod(), null); // [string] [length]
1160 EmitInt32(il, 4000); // [string] [length] [4000]
1161 il.Emit(OpCodes.Cgt); // [string] [0 or 1]
1162 Label isLong = il.DefineLabel(), lenDone = il.DefineLabel();
1163 il.Emit(OpCodes.Brtrue_S, isLong);
1164 EmitInt32(il, 4000); // [string] [4000]
1165 il.Emit(OpCodes.Br_S, lenDone);
1166 il.MarkLabel(isLong);
1167 EmitInt32(il, -1); // [string] [-1]
1168 il.MarkLabel(lenDone);
1169 il.Emit(OpCodes.Stloc_1); // [string]
1170 }
1171 if (prop.PropertyType == typeof(System.Data.Linq.Binary))
1172 {
1173 il.EmitCall(OpCodes.Callvirt, typeof(System.Data.Linq.Binary).GetMethod("ToArray", BindingFlags.Public | BindingFlags.Instance), null);
1174 }
1175 if (allDone != null) il.MarkLabel(allDone.Value);
1176 // relative stack [boxed value or DBNull]
1177 }
1178 il.EmitCall(OpCodes.Callvirt, typeof(IDataParameter).GetProperty("Value").GetSetMethod(), null);// stack is now [parameters] [parameters] [parameter]
1179
1180 if (prop.PropertyType == typeof(string))
1181 {
1182 var endOfSize = il.DefineLabel();
1183 // don't set if 0
1184 il.Emit(OpCodes.Ldloc_1); // [parameters] [parameters] [parameter] [size]
1185 il.Emit(OpCodes.Brfalse_S, endOfSize); // [parameters] [parameters] [parameter]
1186
1187 il.Emit(OpCodes.Dup);// stack is now [parameters] [parameters] [parameter] [parameter]
1188 il.Emit(OpCodes.Ldloc_1); // stack is now [parameters] [parameters] [parameter] [parameter] [size]
1189 il.EmitCall(OpCodes.Callvirt, typeof(IDbDataParameter).GetProperty("Size").GetSetMethod(), null);// stack is now [parameters] [parameters] [parameter]
1190
1191 il.MarkLabel(endOfSize);
1192 }
1193
1194 il.EmitCall(OpCodes.Callvirt, typeof(IList).GetMethod("Add"), null); // stack is now [parameters]
1195 il.Emit(OpCodes.Pop); // IList.Add returns the new index (int); we don't care
1196 }
1197 // stack is currently [command]
1198 il.Emit(OpCodes.Pop); // stack is now empty
1199 il.Emit(OpCodes.Ret);
1200 return (Action<IDbCommand, object>)dm.CreateDelegate(typeof(Action<IDbCommand, object>));
1201 }
1202
1203 private static IDbCommand SetupCommand(IDbConnection cnn, IDbTransaction transaction, string sql, Action<IDbCommand, object> paramReader, object obj, int? commandTimeout, CommandType? commandType)
1204 {
1205 var cmd = cnn.CreateCommand();
1206 var bindByName = GetBindByName(cmd.GetType());
1207 if (bindByName != null) bindByName(cmd, true);
1208 cmd.Transaction = transaction;
1209 cmd.CommandText = sql;
1210 if (commandTimeout.HasValue)
1211 cmd.CommandTimeout = commandTimeout.Value;
1212 if (commandType.HasValue)
1213 cmd.CommandType = commandType.Value;
1214 if (paramReader != null)
1215 {
1216 paramReader(cmd, obj);
1217 }
1218 return cmd;
1219 }
1220
1221
1222 private static int ExecuteCommand(IDbConnection cnn, IDbTransaction tranaction, string sql, Action<IDbCommand, object> paramReader, object obj, int? commandTimeout, CommandType? commandType)
1223 {
1224 using (var cmd = SetupCommand(cnn, tranaction, sql, paramReader, obj, commandTimeout, commandType))
1225 {
1226 return cmd.ExecuteNonQuery();
1227 }
1228 }
1229
1230 private static Func<IDataReader, object> GetStructDeserializer(Type type, int index)
1231 {
1232 // no point using special per-type handling here; it boils down to the same, plus not all are supported anyway (see: SqlDataReader.GetChar - not supported!)
1233 #pragma warning disable 618
1234 if (type == typeof(char))
1235 { // this *does* need special handling, though
1236 return r => SqlMapper.ReadChar(r.GetValue(index));
1237 }
1238 if (type == typeof(char?))
1239 {
1240 return r => SqlMapper.ReadNullableChar(r.GetValue(index));
1241 }
1242 if (type == typeof(System.Data.Linq.Binary))
1243 {
1244 return r => new System.Data.Linq.Binary((byte[])r.GetValue(index));
1245 }
1246 #pragma warning restore 618
1247 return r =>
1248 {
1249 var val = r.GetValue(index);
1250 return val is DBNull ? null : Convert.ChangeType(val, type);
1251 };
1252 }
1253
1254 static readonly MethodInfo
1255 enumParse = typeof(Enum).GetMethod("Parse", new Type[] { typeof(Type), typeof(string), typeof(bool) }),
1256 getItem = typeof(IDataRecord).GetProperties(BindingFlags.Instance | BindingFlags.Public)
1257 .Where(p => p.GetIndexParameters().Any() && p.GetIndexParameters()[0].ParameterType == typeof(int))
1258 .Select(p => p.GetGetMethod()).First();
1259
1260 class PropInfo
1261 {
1262 public string Name { get; set; }
1263 public MethodInfo Setter { get; set; }
1264 public Type Type { get; set; }
1265 }
1266
1267 static List<PropInfo> GetSettableProps(Type t)
1268 {
1269 return t
1270 .GetProperties(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance)
1271 .Select(p => new PropInfo
1272 {
1273 Name = p.Name,
1274 Setter = p.DeclaringType == t ? p.GetSetMethod(true) : p.DeclaringType.GetProperty(p.Name).GetSetMethod(true),
1275 Type = p.PropertyType
1276 })
1277 .Where(info => info.Setter != null)
1278 .ToList();
1279 }
1280
1281 static List<FieldInfo> GetSettableFields(Type t)
1282 {
1283 return t.GetFields(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).ToList();
1284 }
1285
1286 public static Func<IDataReader, object> GetClassDeserializer(
1287 #if CSHARP30
1288 Type type, IDataReader reader, int startBound, int length, bool returnNullIfFirstMissing
1289 #else
1290 Type type, IDataReader reader, int startBound = 0, int length = -1, bool returnNullIfFirstMissing = false
1291 #endif
1292 )
1293 {
1294 var dm = new DynamicMethod(string.Format("Deserialize{0}", Guid.NewGuid()), type, new[] { typeof(IDataReader) }, true);
1295
1296 var il = dm.GetILGenerator();
1297 il.DeclareLocal(typeof(int));
1298 il.DeclareLocal(type);
1299 bool haveEnumLocal = false;
1300 il.Emit(OpCodes.Ldc_I4_0);
1301 il.Emit(OpCodes.Stloc_0);
1302 var properties = GetSettableProps(type);
1303 var fields = GetSettableFields(type);
1304 if (length == -1)
1305 {
1306 length = reader.FieldCount - startBound;
1307 }
1308
1309 if (reader.FieldCount <= startBound)
1310 {
1311 throw new ArgumentException("When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id", "splitOn");
1312 }
1313
1314 var names = new List<string>();
1315
1316 for (int i = startBound; i < startBound + length; i++)
1317 {
1318 names.Add(reader.GetName(i));
1319 }
1320
1321 var setters = (
1322 from n in names
1323 let prop = properties.FirstOrDefault(p => string.Equals(p.Name, n, StringComparison.Ordinal)) // property case sensitive first
1324 ?? properties.FirstOrDefault(p => string.Equals(p.Name, n, StringComparison.OrdinalIgnoreCase)) // property case insensitive second
1325 let field = prop != null ? null : (fields.FirstOrDefault(p => string.Equals(p.Name, n, StringComparison.Ordinal)) // field case sensitive third
1326 ?? fields.FirstOrDefault(p => string.Equals(p.Name, n, StringComparison.OrdinalIgnoreCase))) // field case insensitive fourth
1327 select new { Name = n, Property = prop, Field = field }
1328 ).ToList();
1329
1330 int index = startBound;
1331
1332 il.BeginExceptionBlock();
1333 // stack is empty
1334 il.Emit(OpCodes.Newobj, type.GetConstructor(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic, null, Type.EmptyTypes, null)); // stack is now [target]
1335 bool first = true;
1336 var allDone = il.DefineLabel();
1337 foreach (var item in setters)
1338 {
1339 if (item.Property != null || item.Field != null)
1340 {
1341 il.Emit(OpCodes.Dup); // stack is now [target][target]
1342 Label isDbNullLabel = il.DefineLabel();
1343 Label finishLabel = il.DefineLabel();
1344
1345 il.Emit(OpCodes.Ldarg_0); // stack is now [target][target][reader]
1346 EmitInt32(il, index); // stack is now [target][target][reader][index]
1347 il.Emit(OpCodes.Dup);// stack is now [target][target][reader][index][index]
1348 il.Emit(OpCodes.Stloc_0);// stack is now [target][target][reader][index]
1349 il.Emit(OpCodes.Callvirt, getItem); // stack is now [target][target][value-as-object]
1350
1351
1352 Type memberType = item.Property != null ? item.Property.Type : item.Field.FieldType;
1353
1354 if (memberType == typeof(char) || memberType == typeof(char?))
1355 {
1356 il.EmitCall(OpCodes.Call, typeof(SqlMapper).GetMethod(
1357 memberType == typeof(char) ? "ReadChar" : "ReadNullableChar", BindingFlags.Static | BindingFlags.Public), null); // stack is now [target][target][typed-value]
1358 }
1359 else
1360 {
1361 il.Emit(OpCodes.Dup); // stack is now [target][target][value][value]
1362 il.Emit(OpCodes.Isinst, typeof(DBNull)); // stack is now [target][target][value-as-object][DBNull or null]
1363 il.Emit(OpCodes.Brtrue_S, isDbNullLabel); // stack is now [target][target][value-as-object]
1364
1365 // unbox nullable enums as the primitive, i.e. byte etc
1366
1367 var nullUnderlyingType = Nullable.GetUnderlyingType(memberType);
1368 var unboxType = nullUnderlyingType != null && nullUnderlyingType.IsEnum ? nullUnderlyingType : memberType;
1369
1370 if (unboxType.IsEnum)
1371 {
1372 if (!haveEnumLocal)
1373 {
1374 il.DeclareLocal(typeof(string));
1375 haveEnumLocal = true;
1376 }
1377
1378 Label isNotString = il.DefineLabel();
1379 il.Emit(OpCodes.Dup); // stack is now [target][target][value][value]
1380 il.Emit(OpCodes.Isinst, typeof(string)); // stack is now [target][target][value-as-object][string or null]
1381 il.Emit(OpCodes.Dup);// stack is now [target][target][value-as-object][string or null][string or null]
1382 il.Emit(OpCodes.Stloc_2); // stack is now [target][target][value-as-object][string or null]
1383 il.Emit(OpCodes.Brfalse_S, isNotString); // stack is now [target][target][value-as-object]
1384
1385 il.Emit(OpCodes.Pop); // stack is now [target][target]
1386
1387
1388 il.Emit(OpCodes.Ldtoken, unboxType); // stack is now [target][target][enum-type-token]
1389 il.EmitCall(OpCodes.Call, typeof(Type).GetMethod("GetTypeFromHandle"), null);// stack is now [target][target][enum-type]
1390 il.Emit(OpCodes.Ldloc_2); // stack is now [target][target][enum-type][string]
1391 il.Emit(OpCodes.Ldc_I4_1); // stack is now [target][target][enum-type][string][true]
1392 il.EmitCall(OpCodes.Call, enumParse, null); // stack is now [target][target][enum-as-object]
1393
1394 il.Emit(OpCodes.Unbox_Any, unboxType); // stack is now [target][target][typed-value]
1395
1396 if (nullUnderlyingType != null)
1397 {
1398 il.Emit(OpCodes.Newobj, memberType.GetConstructor(new[] { nullUnderlyingType }));
1399 }
1400 if (item.Property != null)
1401 {
1402 il.Emit(OpCodes.Callvirt, item.Property.Setter); // stack is now [target]
1403 }
1404 else
1405 {
1406 il.Emit(OpCodes.Stfld, item.Field); // stack is now [target]
1407 }
1408 il.Emit(OpCodes.Br_S, finishLabel);
1409
1410
1411 il.MarkLabel(isNotString);
1412 }
1413 if (memberType == typeof(System.Data.Linq.Binary))
1414 {
1415 il.Emit(OpCodes.Unbox_Any, typeof(byte[])); // stack is now [target][target][byte-array]
1416 il.Emit(OpCodes.Newobj, typeof(System.Data.Linq.Binary).GetConstructor(new Type[] { typeof(byte[]) }));// stack is now [target][target][binary]
1417 }
1418 else
1419 {
1420 il.Emit(OpCodes.Unbox_Any, unboxType); // stack is now [target][target][typed-value]
1421 }
1422 if (nullUnderlyingType != null && nullUnderlyingType.IsEnum)
1423 {
1424 il.Emit(OpCodes.Newobj, memberType.GetConstructor(new[] { nullUnderlyingType }));
1425 }
1426 }
1427 if (item.Property != null)
1428 {
1429 il.Emit(OpCodes.Callvirt, item.Property.Setter); // stack is now [target]
1430 }
1431 else
1432 {
1433 il.Emit(OpCodes.Stfld, item.Field); // stack is now [target]
1434 }
1435
1436 il.Emit(OpCodes.Br_S, finishLabel); // stack is now [target]
1437
1438 il.MarkLabel(isDbNullLabel); // incoming stack: [target][target][value]
1439
1440 il.Emit(OpCodes.Pop); // stack is now [target][target]
1441 il.Emit(OpCodes.Pop); // stack is now [target]
1442
1443 if (first && returnNullIfFirstMissing)
1444 {
1445 il.Emit(OpCodes.Pop);
1446 il.Emit(OpCodes.Ldnull); // stack is now [null]
1447 il.Emit(OpCodes.Stloc_1);
1448 il.Emit(OpCodes.Br, allDone);
1449 }
1450
1451 il.MarkLabel(finishLabel);
1452 }
1453 first = false;
1454 index += 1;
1455 }
1456 il.Emit(OpCodes.Stloc_1); // stack is empty
1457 il.MarkLabel(allDone);
1458 il.BeginCatchBlock(typeof(Exception)); // stack is Exception
1459 il.Emit(OpCodes.Ldloc_0); // stack is Exception, index
1460 il.Emit(OpCodes.Ldarg_0); // stack is Exception, index, reader
1461 il.EmitCall(OpCodes.Call, typeof(SqlMapper).GetMethod("ThrowDataException"), null);
1462 il.Emit(OpCodes.Ldnull);
1463 il.Emit(OpCodes.Stloc_1); // to make it verifiable
1464 il.EndExceptionBlock();
1465
1466 il.Emit(OpCodes.Ldloc_1); // stack is empty
1467 il.Emit(OpCodes.Ret);
1468
1469 return (Func<IDataReader, object>)dm.CreateDelegate(typeof(Func<IDataReader, object>));
1470 }
1471 public static void ThrowDataException(Exception ex, int index, IDataReader reader)
1472 {
1473 string name = "(n/a)", value = "(n/a)";
1474 if (reader != null && index >= 0 && index < reader.FieldCount)
1475 {
1476 name = reader.GetName(index);
1477 object val = reader.GetValue(index);
1478 if (val == null || val is DBNull)
1479 {
1480 value = "<null>";
1481 }
1482 else
1483 {
1484 value = Convert.ToString(val) + " - " + Type.GetTypeCode(val.GetType());
1485 }
1486 }
1487 throw new DataException(string.Format("Error parsing column {0} ({1}={2})", index, name, value), ex);
1488 }
1489 private static void EmitInt32(ILGenerator il, int value)
1490 {
1491 switch (value)
1492 {
1493 case -1: il.Emit(OpCodes.Ldc_I4_M1); break;
1494 case 0: il.Emit(OpCodes.Ldc_I4_0); break;
1495 case 1: il.Emit(OpCodes.Ldc_I4_1); break;
1496 case 2: il.Emit(OpCodes.Ldc_I4_2); break;
1497 case 3: il.Emit(OpCodes.Ldc_I4_3); break;
1498 case 4: il.Emit(OpCodes.Ldc_I4_4); break;
1499 case 5: il.Emit(OpCodes.Ldc_I4_5); break;
1500 case 6: il.Emit(OpCodes.Ldc_I4_6); break;
1501 case 7: il.Emit(OpCodes.Ldc_I4_7); break;
1502 case 8: il.Emit(OpCodes.Ldc_I4_8); break;
1503 default:
1504 if (value >= -128 && value <= 127)
1505 {
1506 il.Emit(OpCodes.Ldc_I4_S, (sbyte)value);
1507 }
1508 else
1509 {
1510 il.Emit(OpCodes.Ldc_I4, value);
1511 }
1512 break;
1513 }
1514 }
1515
1516 public class GridReader : IDisposable
1517 {
1518 private IDataReader reader;
1519 private IDbCommand command;
1520 private Identity identity;
1521
1522 internal GridReader(IDbCommand command, IDataReader reader, Identity identity)
1523 {
1524 this.command = command;
1525 this.reader = reader;
1526 this.identity = identity;
1527 }
1528 /// <summary>
1529 /// Read the next grid of results
1530 /// </summary>
1531 public IEnumerable<T> Read<T>()
1532 {
1533 if (reader == null) throw new ObjectDisposedException(GetType().Name);
1534 if (consumed) throw new InvalidOperationException("Each grid can only be iterated once");
1535 var typedIdentity = identity.ForGrid(typeof(T), gridIndex);
1536 CacheInfo cache = GetCacheInfo(typedIdentity);
1537 var deserializer = cache.Deserializer;
1538
1539 Func<Func<IDataReader, object>> deserializerGenerator = () =>
1540 {
1541 deserializer = GetDeserializer(typeof(T), reader, 0, -1, false);
1542 cache.Deserializer = deserializer;
1543 return deserializer;
1544 };
1545
1546 if (deserializer == null)
1547 {
1548 deserializer = deserializerGenerator();
1549 }
1550 consumed = true;
1551 return ReadDeferred<T>(gridIndex, deserializer, typedIdentity, deserializerGenerator);
1552 }
1553
1554 private IEnumerable<TReturn> MultiReadInternal<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(object func, string splitOn)
1555 {
1556
1557 var identity = this.identity.ForGrid(typeof(TReturn), new Type[] {
1558 typeof(TFirst),
1559 typeof(TSecond),
1560 typeof(TThird),
1561 typeof(TFourth),
1562 typeof(TFifth)
1563 }, gridIndex);
1564 try
1565 {
1566 foreach (var r in SqlMapper.MultiMapImpl<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(null, null, func, null, null, splitOn, null, null, reader, identity))
1567 {
1568 yield return r;
1569 }
1570 }
1571 finally
1572 {
1573 NextResult();
1574 }
1575 }
1576
1577 #if CSHARP30
1578 public IEnumerable<TReturn> Read<TFirst, TSecond, TReturn>(Func<TFirst, TSecond, TReturn> func, string splitOn)
1579 #else
1580 public IEnumerable<TReturn> Read<TFirst, TSecond, TReturn>(Func<TFirst, TSecond, TReturn> func, string splitOn = "id")
1581 #endif
1582 {
1583 return MultiReadInternal<TFirst, TSecond, DontMap, DontMap, DontMap, TReturn>(func, splitOn);
1584 }
1585
1586 #if CSHARP30
1587 public IEnumerable<TReturn> Read<TFirst, TSecond, TThird, TReturn>(Func<TFirst, TSecond, TThird, TReturn> func, string splitOn)
1588 #else
1589 public IEnumerable<TReturn> Read<TFirst, TSecond, TThird, TReturn>(Func<TFirst, TSecond, TThird, TReturn> func, string splitOn = "id")
1590 #endif
1591 {
1592 return MultiReadInternal<TFirst, TSecond, TThird, DontMap, DontMap, TReturn>(func, splitOn);
1593 }
1594
1595 #if CSHARP30
1596 public IEnumerable<TReturn> Read<TFirst, TSecond, TThird, TFourth, TReturn>(Func<TFirst, TSecond, TThird, TFourth, TReturn> func, string splitOn)
1597 #else
1598 public IEnumerable<TReturn> Read<TFirst, TSecond, TThird, TFourth, TReturn>(Func<TFirst, TSecond, TThird, TFourth, TReturn> func, string splitOn = "id")
1599 #endif
1600 {
1601 return MultiReadInternal<TFirst, TSecond, TThird, TFourth, DontMap, TReturn>(func, splitOn);
1602 }
1603
1604 #if !CSHARP30
1605 public IEnumerable<TReturn> Read<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(Func<TFirst, TSecond, TThird, TFourth, TFifth, TReturn> func, string splitOn = "id")
1606 {
1607 return MultiReadInternal<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(func, splitOn);
1608 }
1609 #endif
1610
1611 private IEnumerable<T> ReadDeferred<T>(int index, Func<IDataReader, object> deserializer, Identity typedIdentity, Func<Func<IDataReader, object>> deserializerGenerator)
1612 {
1613 try
1614 {
1615 while (index == gridIndex && reader.Read())
1616 {
1617 object next;
1618 try
1619 {
1620 next = deserializer(reader);
1621 }
1622 catch (DataException)
1623 {
1624 deserializer = deserializerGenerator();
1625 next = deserializer(reader);
1626 }
1627 yield return (T)next;
1628 }
1629 }
1630 finally // finally so that First etc progresses things even when multiple rows
1631 {
1632 if (index == gridIndex)
1633 {
1634 NextResult();
1635 }
1636 }
1637 }
1638 private int gridIndex;
1639 private bool consumed;
1640 private void NextResult()
1641 {
1642 if (reader.NextResult())
1643 {
1644 gridIndex++;
1645 consumed = false;
1646 }
1647 else
1648 {
1649 Dispose();
1650 }
1651
1652 }
1653 public void Dispose()
1654 {
1655 if (reader != null)
1656 {
1657 reader.Dispose();
1658 reader = null;
1659 }
1660 if (command != null)
1661 {
1662 command.Dispose();
1663 command = null;
1664 }
1665 }
1666 }
1667 }
1668
1669 public class DynamicParameters : SqlMapper.IDynamicParameters
1670 {
1671 static Dictionary<SqlMapper.Identity, Action<IDbCommand, object>> paramReaderCache = new Dictionary<SqlMapper.Identity, Action<IDbCommand, object>>();
1672
1673 Dictionary<string, ParamInfo> parameters = new Dictionary<string, ParamInfo>();
1674 List<object> templates;
1675
1676 class ParamInfo
1677 {
1678 public string Name { get; set; }
1679 public object Value { get; set; }
1680 public ParameterDirection ParameterDirection { get; set; }
1681 public DbType? DbType { get; set; }
1682 public int? Size { get; set; }
1683 public IDbDataParameter AttachedParam { get; set; }
1684 }
1685
1686 public DynamicParameters() { }
1687 public DynamicParameters(object template)
1688 {
1689 if (template != null)
1690 {
1691 AddDynamicParams(template);
1692 }
1693 }
1694
1695 /// <summary>
1696 /// Append a whole object full of params to the dynamic
1697 /// EG: AddParams(new {A = 1, B = 2}) // will add property A and B to the dynamic
1698 /// </summary>
1699 /// <param name="param"></param>
1700 public void AddDynamicParams(
1701 #if CSHARP30
1702 object param
1703 #else
1704 dynamic param
1705 #endif
1706 )
1707 {
1708 object obj = param as object;
1709
1710 if (obj != null)
1711 {
1712 templates = templates ?? new List<object>();
1713 templates.Add(obj);
1714 }
1715 }
1716
1717
1718 public void Add(
1719 #if CSHARP30
1720 string name, object value, DbType? dbType, ParameterDirection? direction, int? size
1721 #else
1722 string name, object value = null, DbType? dbType = null, ParameterDirection? direction = null, int? size = null
1723 #endif
1724 )
1725 {
1726 parameters[Clean(name)] = new ParamInfo() { Name = name, Value = value, ParameterDirection = direction ?? ParameterDirection.Input, DbType = dbType, Size = size };
1727 }
1728
1729 static string Clean(string name)
1730 {
1731 if (!string.IsNullOrEmpty(name))
1732 {
1733 switch (name[0])
1734 {
1735 case '@':
1736 case ':':
1737 case '?':
1738 return name.Substring(1);
1739 }
1740 }
1741 return name;
1742 }
1743
1744 void SqlMapper.IDynamicParameters.AddParameters(IDbCommand command, SqlMapper.Identity identity)
1745 {
1746 if (templates != null)
1747 {
1748 foreach (var template in templates)
1749 {
1750 var newIdent = identity.ForDynamicParameters(template.GetType());
1751 Action<IDbCommand, object> appender;
1752
1753 lock (paramReaderCache)
1754 {
1755 if (!paramReaderCache.TryGetValue(newIdent, out appender))
1756 {
1757 appender = SqlMapper.CreateParamInfoGenerator(newIdent);
1758 paramReaderCache[newIdent] = appender;
1759 }
1760 }
1761
1762 appender(command, template);
1763 }
1764 }
1765
1766 foreach (var param in parameters.Values)
1767 {
1768 var p = command.CreateParameter();
1769 var val = param.Value;
1770 p.ParameterName = param.Name;
1771 p.Value = val ?? DBNull.Value;
1772 p.Direction = param.ParameterDirection;
1773 var s = val as string;
1774 if (s != null)
1775 {
1776 if (s.Length <= 4000)
1777 {
1778 p.Size = 4000;
1779 }
1780 }
1781 if (param.Size != null)
1782 {
1783 p.Size = param.Size.Value;
1784 }
1785 if (param.DbType != null)
1786 {
1787 p.DbType = param.DbType.Value;
1788 }
1789 command.Parameters.Add(p);
1790 param.AttachedParam = p;
1791 }
1792 }
1793
1794 public T Get<T>(string name)
1795 {
1796 var val = parameters[Clean(name)].AttachedParam.Value;
1797 if (val == DBNull.Value)
1798 {
1799 if (default(T) != null)
1800 {
1801 throw new ApplicationException("Attempting to cast a DBNull to a non nullable type!");
1802 }
1803 return default(T);
1804 }
1805 return (T)val;
1806 }
1807 }
1808
1809 public class OracleDynamicParameters : SqlMapper.IDynamicParameters
1810 {
1811 private readonly DynamicParameters dynamicParameters = new DynamicParameters();
1812
1813 private readonly List<OracleParameter> oracleParameters = new List<OracleParameter>();
1814
1815 public void Add(string name, object value = null, DbType? dbType = null, ParameterDirection? direction = null, int? size = null)
1816 {
1817 dynamicParameters.Add(name, value, dbType, direction, size);
1818 }
1819
1820 public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction)
1821 {
1822 var oracleParameter = new OracleParameter(name, oracleDbType, direction);
1823 oracleParameters.Add(oracleParameter);
1824 }
1825
1826 public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
1827 {
1828 ((SqlMapper.IDynamicParameters)dynamicParameters).AddParameters(command, identity);
1829
1830 var oracleCommand = command as OracleCommand;
1831
1832 if (oracleCommand != null)
1833 {
1834 oracleCommand.Parameters.AddRange(oracleParameters.ToArray());
1835 }
1836 }
1837 }
1838
1839 public sealed class DbString
1840 {
1841 public DbString() { Length = -1; }
1842 public bool IsAnsi { get; set; }
1843 public bool IsFixedLength { get; set; }
1844 public int Length { get; set; }
1845 public string Value { get; set; }
1846 public void AddParameter(IDbCommand command, string name)
1847 {
1848 if (IsFixedLength && Length == -1)
1849 {
1850 throw new InvalidOperationException("If specifying IsFixedLength, a Length must also be specified");
1851 }
1852 var param = command.CreateParameter();
1853 param.ParameterName = name;
1854 param.Value = (object)Value ?? DBNull.Value;
1855 if (Length == -1 && Value != null && Value.Length <= 4000)
1856 {
1857 param.Size = 4000;
1858 }
1859 else
1860 {
1861 param.Size = Length;
1862 }
1863 param.DbType = IsAnsi ? (IsFixedLength ? DbType.AnsiStringFixedLength : DbType.AnsiString) : (IsFixedLength ? DbType.StringFixedLength : DbType.String);
1864 command.Parameters.Add(param);
1865 }
1866 }
1867 }
View Code
ok，擴展寫完了，來一個單元測試，試一試：
1 /// <summary>
2 /// 執行帶參數存儲過程，並返回結果
3 /// </summary>
4 public static void ExectPro()
5 {
6 var p = new OracleDynamicParameters();
7 p.Add("beginTime", 201501);
8 p.Add("endTime", 201512);
9 p.Add("targetColumn", "tax");
10 p.Add("vCur", OracleDbType.RefCursor, ParameterDirection.Output);
11 using (IDbConnection conn = new OracleConnection(SqlConnOdp))
12 {
13 conn.Open();
14 var aa = conn.Query("p_123c", param: p, commandType: CommandType.StoredProcedure).ToList();
15 aa.ForEach(m => Console.WriteLine(m.C_NAME));
16 }
17 Console.ReadLine();
18 }
結果執行通過，並打印了首列的所有值。
那麼，Dapper的簡單擴展就完成了。
寫在後面
補充說明： 我用的Oracle驅動是ODP.NET，.net是4.0
這個ODP.NET的Oracle.DataAccess.dll推薦從你的目標服務器，復制回來，不要用本地的，反正我用本地的，就提示外部程序錯誤。猜測是版本問題或者是位數問題。
相關參考文章
http://stackoverflow.com/questions/6212992/using-dapper-with-oracle
https://stackoverflow.com/questions/15943389/using-dapper-with-oracle-user-defined-types
http://stackoverflow.com/questions/7390015/using-dapper-with-oracle-stored-procedures-which-return-cursors
