星空网 > 软件开发 > 数据库

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

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 类,如下图:

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

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

继续看看接口:

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

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

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

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

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

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

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

最终修改版如下(代码多,展开了直接复制代码贴到你的文件里面):

Dapper完美兼容Oracle,执行存储过程,并返回结果集。Dapper完美兼容Oracle,执行存储过程,并返回结果集。
  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  292         return DbType. 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.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 }

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




原标题:Dapper完美兼容Oracle,执行存储过程,并返回结果集。

关键词:oracle

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

Discoverly:https://www.ikjzd.com/w/2438
LikeAlyzer:https://www.ikjzd.com/w/2439
日本kakaku价格网_比价网:https://www.ikjzd.com/w/244
Tailwind:https://www.ikjzd.com/w/2440
Deep Social:https://www.ikjzd.com/w/2441
Ritase:https://www.ikjzd.com/w/2442
北京奥森公园桃花谷坐地铁怎么走?:https://www.vstour.cn/a/335170.html
深圳梧桐山属于哪个区哪个街道哪个社区:https://www.vstour.cn/a/335171.html
相关文章
我的浏览记录
最新相关资讯
跨境电商服务平台 | 深圳旅行社 | 东南亚物流