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 else1026 {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); // 01057 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 empty1061 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) < 01075 && 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 fire1077 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 null1131 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 empty1148 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 01184 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 care1196 }1197 // stack is currently [command]1198 il.Emit(OpCodes.Pop); // stack is now empty1199 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 6181234 if (type == typeof(char))1235 { // this *does* need special handling, though1236 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 6181247 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 MethodInfo1255 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 PropInfo1261 {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 t1270 .GetProperties(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance)1271 .Select(p => new PropInfo1272 {1273 Name = p.Name,1274 Setter = p.DeclaringType == t ? p.GetSetMethod(true) : p.DeclaringType.GetProperty(p.Name).GetSetMethod(true),1275 Type = p.PropertyType1276 })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 CSHARP301288 Type type, IDataReader reader, int startBound, int length, bool returnNullIfFirstMissing1289 #else1290 Type type, IDataReader reader, int startBound = 0, int length = -1, bool returnNullIfFirstMissing = false1291 #endif1292 )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 names1323 let prop = properties.FirstOrDefault(p => string.Equals(p.Name, n, StringComparison.Ordinal)) // property case sensitive first1324 ?? properties.FirstOrDefault(p => string.Equals(p.Name, n, StringComparison.OrdinalIgnoreCase)) // property case insensitive second1325 let field = prop != null ? null : (fields.FirstOrDefault(p => string.Equals(p.Name, n, StringComparison.Ordinal)) // field case sensitive third1326 ?? fields.FirstOrDefault(p => string.Equals(p.Name, n, StringComparison.OrdinalIgnoreCase))) // field case insensitive fourth1327 select new { Name = n, Property = prop, Field = field }1328 ).ToList();1329 1330 int index = startBound;1331 1332 il.BeginExceptionBlock();1333 // stack is empty1334 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 else1360 {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 etc1366 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 else1405 {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 else1419 {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 else1432 {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 empty1457 il.MarkLabel(allDone);1458 il.BeginCatchBlock(typeof(Exception)); // stack is Exception1459 il.Emit(OpCodes.Ldloc_0); // stack is Exception, index1460 il.Emit(OpCodes.Ldarg_0); // stack is Exception, index, reader1461 il.EmitCall(OpCodes.Call, typeof(SqlMapper).GetMethod("ThrowDataException"), null);1462 il.Emit(OpCodes.Ldnull);1463 il.Emit(OpCodes.Stloc_1); // to make it verifiable1464 il.EndExceptionBlock();1465 1466 il.Emit(OpCodes.Ldloc_1); // stack is empty1467 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 else1483 {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 else1509 {1510 il.Emit(OpCodes.Ldc_I4, value);1511 }1512 break;1513 }1514 }1515 1516 public class GridReader : IDisposable1517 {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 results1530 /// </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 try1565 {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 finally1572 {1573 NextResult();1574 }1575 }1576 1577 #if CSHARP30 1578 public IEnumerable<TReturn> Read<TFirst, TSecond, TReturn>(Func<TFirst, TSecond, TReturn> func, string splitOn)1579 #else1580 public IEnumerable<TReturn> Read<TFirst, TSecond, TReturn>(Func<TFirst, TSecond, TReturn> func, string splitOn = "id")1581 #endif1582 {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 #else1589 public IEnumerable<TReturn> Read<TFirst, TSecond, TThird, TReturn>(Func<TFirst, TSecond, TThird, TReturn> func, string splitOn = "id")1590 #endif1591 {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 #else1598 public IEnumerable<TReturn> Read<TFirst, TSecond, TThird, TFourth, TReturn>(Func<TFirst, TSecond, TThird, TFourth, TReturn> func, string splitOn = "id")1599 #endif1600 {1601 return MultiReadInternal<TFirst, TSecond, TThird, TFourth, DontMap, TReturn>(func, splitOn);1602 }1603 1604 #if !CSHARP301605 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 #endif1610 1611 private IEnumerable<T> ReadDeferred<T>(int index, Func<IDataReader, object> deserializer, Identity typedIdentity, Func<Func<IDataReader, object>> deserializerGenerator)1612 {1613 try1614 {1615 while (index == gridIndex && reader.Read())1616 {1617 object next;1618 try1619 {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 rows1631 {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 else1648 {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.IDynamicParameters1670 {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 ParamInfo1677 {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 dynamic1697 /// EG: AddParams(new {A = 1, B = 2}) // will add property A and B to the dynamic1698 /// </summary>1699 /// <param name="param"></param>1700 public void AddDynamicParams(1701 #if CSHARP301702 object param1703 #else1704 dynamic param1705 #endif1706 )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 CSHARP301720 string name, object value, DbType? dbType, ParameterDirection? direction, int? size1721 #else1722 string name, object value = null, DbType? dbType = null, ParameterDirection? direction = null, int? size = null1723 #endif1724 )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.IDynamicParameters1810 {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 DbString1840 {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 else1860 {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 }
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