zhaolei
4 days ago 0b51bf5178442b800fd76f4ff1df5b8e47af7525
commit | author | age
4a2e5b 1 using System;
Z 2 using System.Collections.Generic;
3 using System.Data;
4 using System.Data.Common;
5 using System.Dynamic;
6 using System.Linq;
7 using System.Reflection;
8 using System.Text;
9 using System.Threading;
10 using System.Threading.Tasks;
11 using PetaPoco.Core;
12 using PetaPoco.Internal;
13 using PetaPoco.Utilities;
14 #if !NETSTANDARD
15 using System.Configuration;
16
17 #endif
18
19 namespace PetaPoco
20 {
21     /// <inheritdoc />
22     public class Database : IDatabase
23     {
24 #region Internal operations
25
26         internal void DoPreExecute(IDbCommand cmd)
27         {
28             if (CommandTimeout > 0 || OneTimeCommandTimeout > 0)
29             {
30                 cmd.CommandTimeout = OneTimeCommandTimeout > 0 ? OneTimeCommandTimeout : CommandTimeout;
31                 OneTimeCommandTimeout = 0;
32             }
33
34             _provider.PreExecute(cmd);
35             OnExecutingCommand(cmd);
36
37             _lastSql = cmd.CommandText;
38             _lastArgs = cmd.Parameters.Cast<IDataParameter>().Select(parameter => parameter.Value).ToArray();
39         }
40
41 #endregion
42
43 #region Member Fields
44
45         private IMapper _defaultMapper;
46         private string _connectionString;
47         private IProvider _provider;
48         private IDbConnection _sharedConnection;
49         private IDbTransaction _transaction;
50         private int _sharedConnectionDepth;
51         private int _transactionDepth;
52         private bool _transactionCancelled;
53         private string _lastSql;
54         private object[] _lastArgs;
55         private string _paramPrefix;
56         private DbProviderFactory _factory;
57         private IsolationLevel? _isolationLevel;
58
59 #endregion
60
61 #region Constructors
62
63 #if !NETSTANDARD
64         /// <summary>
65         ///     Constructs an instance using the first connection string found in the app/web configuration file.
66         /// </summary>
67         /// <param name="defaultMapper">The default mapper to use when no specific mapper has been registered.</param>
68         /// <exception cref="InvalidOperationException">Thrown when no connection strings can registered.</exception>
69         public Database(IMapper defaultMapper = null)
70         {
71             if (ConfigurationManager.ConnectionStrings.Count == 0)
72                 throw new InvalidOperationException("One or more connection strings must be registered to use the no-parameter constructor");
73
74             var entry = ConfigurationManager.ConnectionStrings[0];
75             _connectionString = entry.ConnectionString;
76             InitialiseFromEntry(entry, defaultMapper);
77         }
78
79         /// <summary>
80         ///     Constructs an instance using a supplied connection string name. The actual connection string and provider will be
81         ///     read from app/web.config.
82         /// </summary>
83         /// <param name="connectionStringName">The name of the connection.</param>
84         /// <param name="defaultMapper">The default mapper to use when no specific mapper has been registered.</param>
85         /// <exception cref="ArgumentException">Thrown when <paramref name="connectionStringName" /> is null or empty.</exception>
86         /// <exception cref="InvalidOperationException">Thrown when a connection string cannot be found.</exception>
87         public Database(string connectionStringName, IMapper defaultMapper = null)
88         {
89             if (string.IsNullOrEmpty(connectionStringName))
90                 throw new ArgumentException("Connection string name must not be null or empty", nameof(connectionStringName));
91
92             var entry = ConfigurationManager.ConnectionStrings[connectionStringName];
93
94             if (entry == null)
95                 throw new InvalidOperationException(string.Format("Can't find a connection string with the name '{0}'", connectionStringName));
96
97             _connectionString = entry.ConnectionString;
98             InitialiseFromEntry(entry, defaultMapper);
99         }
100
101         private void InitialiseFromEntry(ConnectionStringSettings entry, IMapper defaultMapper)
102         {
103             var providerName = !string.IsNullOrEmpty(entry.ProviderName) ? entry.ProviderName : "System.Data.SqlClient";
104             Initialise(DatabaseProvider.Resolve(providerName, false, _connectionString), defaultMapper);
105         }
106 #endif
107
108         /// <summary>
109         ///     Constructs an instance using a supplied IDbConnection.
110         /// </summary>
111         /// <param name="connection">The IDbConnection to use.</param>
112         /// <param name="defaultMapper">The default mapper to use when no specific mapper has been registered.</param>
113         /// <remarks>
114         ///     The supplied IDbConnection will not be closed/disposed by PetaPoco - that remains
115         ///     the responsibility of the caller.
116         /// </remarks>
117         /// <exception cref="ArgumentException">Thrown when <paramref name="connection" /> is null or empty.</exception>
118         public Database(IDbConnection connection, IMapper defaultMapper = null)
119         {
120             if (connection == null)
121                 throw new ArgumentNullException(nameof(connection));
122
123             SetupFromConnection(connection);
124             Initialise(DatabaseProvider.Resolve(_sharedConnection.GetType(), false, _connectionString), defaultMapper);
125         }
126
127         private void SetupFromConnection(IDbConnection connection)
128         {
129             _sharedConnection = connection;
130             _connectionString = connection.ConnectionString;
131
132             // Prevent closing external connection
133             _sharedConnectionDepth = 2;
134         }
135
136         /// <summary>
137         ///     Constructs an instance using a supplied connection string and provider name.
138         /// </summary>
139         /// <param name="connectionString">The database connection string.</param>
140         /// <param name="providerName">The database provider name.</param>
141         /// <param name="defaultMapper">The default mapper to use when no specific mapper has been registered.</param>
142         /// <remarks>
143         ///     PetaPoco will automatically close and dispose any connections it creates.
144         /// </remarks>
145         /// <exception cref="ArgumentException">Thrown when <paramref name="connectionString" /> is null or empty.</exception>
146         public Database(string connectionString, string providerName, IMapper defaultMapper = null)
147         {
148             if (string.IsNullOrEmpty(connectionString))
149                 throw new ArgumentException("Connection string must not be null or empty", nameof(connectionString));
150             if (string.IsNullOrEmpty(providerName))
151                 throw new ArgumentException("Provider name must not be null or empty", nameof(providerName));
152
153             _connectionString = connectionString;
154             Initialise(DatabaseProvider.Resolve(providerName, false, _connectionString), defaultMapper);
155         }
156
157         /// <summary>
158         ///     Constructs an instance using the supplied connection string and DbProviderFactory.
159         /// </summary>
160         /// <param name="connectionString">The database connection string.</param>
161         /// <param name="factory">The DbProviderFactory to use for instantiating IDbConnections.</param>
162         /// <param name="defaultMapper">The default mapper to use when no specific mapper has been registered.</param>
163         /// <exception cref="ArgumentException">Thrown when <paramref name="connectionString" /> is null or empty.</exception>
164         /// <exception cref="ArgumentNullException">Thrown when <paramref name="factory" /> is null.</exception>
165         public Database(string connectionString, DbProviderFactory factory, IMapper defaultMapper = null)
166         {
167             if (string.IsNullOrEmpty(connectionString))
168                 throw new ArgumentException("Connection string must not be null or empty", nameof(connectionString));
169
170             if (factory == null)
171                 throw new ArgumentNullException(nameof(factory));
172
173             _connectionString = connectionString;
174             Initialise(DatabaseProvider.Resolve(DatabaseProvider.Unwrap(factory).GetType(), false, _connectionString), defaultMapper);
175         }
176
177         /// <summary>
178         ///     Constructs an instance using the supplied provider and optional default mapper.
179         /// </summary>
180         /// <param name="connectionString">The database connection string.</param>
181         /// <param name="provider">The provider to use.</param>
182         /// <param name="defaultMapper">The default mapper to use when no specific mapper has been registered.</param>
183         /// <exception cref="ArgumentException">Thrown when <paramref name="connectionString" /> is null or empty.</exception>
184         /// <exception cref="ArgumentNullException">Thrown when <paramref name="provider" /> is null.</exception>
185         public Database(string connectionString, IProvider provider, IMapper defaultMapper = null)
186         {
187             if (string.IsNullOrEmpty(connectionString))
188                 throw new ArgumentException("Connection string must not be null or empty", nameof(connectionString));
189
190             if (provider == null)
191                 throw new ArgumentNullException(nameof(provider));
192
193             _connectionString = connectionString;
194             Initialise(provider, defaultMapper);
195         }
196
197         /// <summary>
198         ///     Constructs an instance using the supplied <paramref name="configuration" />.
199         /// </summary>
200         /// <param name="configuration">The configuration for constructing an instance.</param>
201         /// <exception cref="ArgumentNullException">Thrown when <paramref name="configuration" /> is null.</exception>
202         /// <exception cref="InvalidOperationException">
203         ///     Thrown when no configuration string is configured and app/web config does
204         ///     any connection string registered.
205         /// </exception>
206         /// <exception cref="InvalidOperationException">Thrown when a connection string configured and no provider is configured.</exception>
207         public Database(IDatabaseBuildConfiguration configuration)
208         {
209             if (configuration == null)
210                 throw new ArgumentNullException(nameof(configuration));
211
212             var settings = (IBuildConfigurationSettings) configuration;
213
214             IMapper defaultMapper = null;
215             settings.TryGetSetting<IMapper>(DatabaseConfigurationExtensions.DefaultMapper, v => defaultMapper = v);
216
217             IProvider provider = null;
218             IDbConnection connection = null;
219             string providerName = null;
220 #if !NETSTANDARD
221             ConnectionStringSettings entry = null;
222 #endif
223
224             settings.TryGetSetting<IProvider>(DatabaseConfigurationExtensions.Provider, p => provider = p);
225             settings.TryGetSetting<IDbConnection>(DatabaseConfigurationExtensions.Connection, c => connection = c);
226             settings.TryGetSetting<string>(DatabaseConfigurationExtensions.ProviderName, pn => providerName = pn);
227
228             if (connection != null)
229             {
230                 SetupFromConnection(connection);
231             }
232             else
233             {
234                 settings.TryGetSetting<string>(DatabaseConfigurationExtensions.ConnectionString, cs => _connectionString = cs);
235
236 #if !NETSTANDARD
237                 if (_connectionString == null)
238                 {
239                     string connectionStringName = null;
240                     settings.TryGetSetting<string>(DatabaseConfigurationExtensions.ConnectionStringName, n => connectionStringName = n);
241
242                     if (connectionStringName != null)
243                     {
244                         entry = ConfigurationManager.ConnectionStrings[connectionStringName];
245                         if (entry == null)
246                             throw new InvalidOperationException($"Can't find a connection string with the name '{connectionStringName}'");
247                     }
248                     else
249                     {
250                         if (ConfigurationManager.ConnectionStrings.Count == 0)
251                             throw new InvalidOperationException("One or more connection strings must be registered, when not providing a connection string");
252
253                         entry = ConfigurationManager.ConnectionStrings[0];
254                     }
255
256                     _connectionString = entry.ConnectionString;
257                 }
258 #else
259                 if (_connectionString == null)
260                     throw new InvalidOperationException("A connection string is required.");
261 #endif
262             }
263
264             if (provider != null)
265                 Initialise(provider, defaultMapper);
266             else if (providerName != null)
267                 Initialise(DatabaseProvider.Resolve(providerName, false, _connectionString), defaultMapper);
268 #if !NETSTANDARD
269             else if (entry != null)
270                 InitialiseFromEntry(entry, defaultMapper);
271 #endif
272             else if (connection != null)
273                 Initialise(DatabaseProvider.Resolve(_sharedConnection.GetType(), false, _connectionString), defaultMapper);
274             else
275                 throw new InvalidOperationException("Unable to locate a provider.");
276
277             settings.TryGetSetting<bool>(DatabaseConfigurationExtensions.EnableNamedParams, v => EnableNamedParams = v);
278             settings.TryGetSetting<bool>(DatabaseConfigurationExtensions.EnableAutoSelect, v => EnableAutoSelect = v);
279             settings.TryGetSetting<int>(DatabaseConfigurationExtensions.CommandTimeout, v => CommandTimeout = v);
280             settings.TryGetSetting<IsolationLevel>(DatabaseConfigurationExtensions.IsolationLevel, v => IsolationLevel = v);
281
282             settings.TryGetSetting<EventHandler<DbConnectionEventArgs>>(DatabaseConfigurationExtensions.ConnectionOpened, v => ConnectionOpened += v);
283             settings.TryGetSetting<EventHandler<DbConnectionEventArgs>>(DatabaseConfigurationExtensions.ConnectionClosing, v => ConnectionClosing += v);
284             settings.TryGetSetting<EventHandler<DbTransactionEventArgs>>(DatabaseConfigurationExtensions.TransactionStarted, v => TransactionStarted += v);
285             settings.TryGetSetting<EventHandler<DbTransactionEventArgs>>(DatabaseConfigurationExtensions.TransactionEnding, v => TransactionEnding += v);
286             settings.TryGetSetting<EventHandler<DbCommandEventArgs>>(DatabaseConfigurationExtensions.CommandExecuting, v => CommandExecuting += v);
287             settings.TryGetSetting<EventHandler<DbCommandEventArgs>>(DatabaseConfigurationExtensions.CommandExecuted, v => CommandExecuted += v);
288             settings.TryGetSetting<EventHandler<ExceptionEventArgs>>(DatabaseConfigurationExtensions.ExceptionThrown, v => ExceptionThrown += v);
289         }
290
291         /// <summary>
292         ///     Provides common initialization for the various constructors.
293         /// </summary>
294         private void Initialise(IProvider provider, IMapper mapper)
295         {
296             // Reset
297             _transactionDepth = 0;
298             EnableAutoSelect = true;
299             EnableNamedParams = true;
300
301             // What character is used for delimiting parameters in SQL
302             _provider = provider;
303             _paramPrefix = _provider.GetParameterPrefix(_connectionString);
304             _factory = _provider.GetFactory();
305
306             _defaultMapper = mapper ?? new ConventionMapper();
307         }
308
309 #endregion
310
311 #region Connection Management
312
313         /// <summary>
314         ///     When set to true the first opened connection is kept alive until <see cref="CloseSharedConnection" />
315         ///     or <see cref="Dispose" /> is called.
316         /// </summary>
317         /// <seealso cref="OpenSharedConnection" />
318         public bool KeepConnectionAlive { get; set; }
319
320         /// <summary>
321         ///     Provides access to the currently open shared connection.
322         /// </summary>
323         /// <returns>
324         ///     The currently open connection, or <c>Null</c>.
325         /// </returns>
326         /// <seealso cref="OpenSharedConnection" />
327         /// <seealso cref="CloseSharedConnection" />
328         /// <seealso cref="KeepConnectionAlive" />
329         public IDbConnection Connection => _sharedConnection;
330
331         /// <summary>
332         ///     Opens a connection that will be used for all subsequent queries.
333         /// </summary>
334         /// <remarks>
335         ///     Calls to <see cref="OpenSharedConnection" />/<see cref="CloseSharedConnection" /> are reference
336         ///     counted and should be balanced
337         /// </remarks>
338         /// <seealso cref="Connection" />
339         /// <seealso cref="CloseSharedConnection" />
340         /// <seealso cref="KeepConnectionAlive" />
341         public void OpenSharedConnection()
342         {
343             if (_sharedConnectionDepth == 0)
344             {
345                 _sharedConnection = _factory.CreateConnection();
346                 _sharedConnection.ConnectionString = _connectionString;
347
348                 if (_sharedConnection.State == ConnectionState.Broken)
349                     _sharedConnection.Close();
350
351                 if (_sharedConnection.State == ConnectionState.Closed)
352                     _sharedConnection.Open();
353
354                 _sharedConnection = OnConnectionOpened(_sharedConnection);
355
356                 if (KeepConnectionAlive)
357                     _sharedConnectionDepth++;
358             }
359
360             _sharedConnectionDepth++;
361         }
362
363 #if ASYNC
364         /// <summary>
365         ///     The async version of <see cref="OpenSharedConnection" />.
366         /// </summary>
367         public Task OpenSharedConnectionAsync()
368             => OpenSharedConnectionAsync(CancellationToken.None);
369
370         /// <summary>
371         ///     The async version of <see cref="OpenSharedConnection" />.
372         /// </summary>
373         public async Task OpenSharedConnectionAsync(CancellationToken cancellationToken)
374         {
375             if (_sharedConnectionDepth == 0)
376             {
377                 _sharedConnection = _factory.CreateConnection();
378                 _sharedConnection.ConnectionString = _connectionString;
379
380                 if (_sharedConnection.State == ConnectionState.Broken)
381                     _sharedConnection.Close();
382
383                 if (_sharedConnection.State == ConnectionState.Closed)
384                 {
385                     var con = _sharedConnection as DbConnection;
386                     if (con != null)
387                         await con.OpenAsync(cancellationToken).ConfigureAwait(false);
388                     else
389                         _sharedConnection.Open();
390                 }
391
392                 _sharedConnection = OnConnectionOpened(_sharedConnection);
393
394                 if (KeepConnectionAlive)
395                     _sharedConnectionDepth++;
396             }
397
398             _sharedConnectionDepth++;
399         }
400 #endif
401
402         /// <summary>
403         ///     Releases the shared connection.
404         /// </summary>
405         /// <remarks>
406         ///     Calls to <see cref="OpenSharedConnection" />/<see cref="CloseSharedConnection" /> are reference
407         ///     counted and should be balanced
408         /// </remarks>
409         /// <seealso cref="Connection" />
410         /// <seealso cref="OpenSharedConnection" />
411         /// <seealso cref="KeepConnectionAlive" />
412         public void CloseSharedConnection()
413         {
414             if (_sharedConnectionDepth > 0)
415             {
416                 _sharedConnectionDepth--;
417                 if (_sharedConnectionDepth == 0)
418                 {
419                     OnConnectionClosing(_sharedConnection);
420                     _sharedConnection.Dispose();
421                     _sharedConnection = null;
422                 }
423             }
424         }
425
426         /// <summary>
427         ///     Alias for <see cref="CloseSharedConnection" />.
428         /// </summary>
429         /// <remarks>
430         ///     Called implicitly when making use of the .NET `using` language feature.
431         /// </remarks>
432         public void Dispose()
433         {
434             // Automatically close one open connection reference
435             //  (Works with KeepConnectionAlive and manually opening a shared connection)
436             CloseSharedConnection();
437         }
438
439 #endregion
440
441 #region Transaction Management
442
443         /// <inheritdoc />
444         IDbTransaction ITransactionAccessor.Transaction => _transaction;
445
446         /// <inheritdoc />
447         public ITransaction GetTransaction()
448             => new Transaction(this);
449
450         /// <summary>
451         ///     Called when a transaction starts.
452         /// </summary>
453         public virtual void OnBeginTransaction()
454         {
455             TransactionStarted?.Invoke(this, new DbTransactionEventArgs(_transaction));
456         }
457
458         /// <summary>
459         ///     Called when a transaction ends.
460         /// </summary>
461         public virtual void OnEndTransaction()
462         {
463             TransactionEnding?.Invoke(this, new DbTransactionEventArgs(_transaction));
464         }
465
466         /// <inheritdoc />
467         public void BeginTransaction()
468         {
469             _transactionDepth++;
470
471             if (_transactionDepth == 1)
472             {
473                 OpenSharedConnection();
474                 _transaction = !_isolationLevel.HasValue ? _sharedConnection.BeginTransaction() : _sharedConnection.BeginTransaction(_isolationLevel.Value);
475                 _transactionCancelled = false;
476                 OnBeginTransaction();
477             }
478         }
479
480 #if ASYNC
481         /// <inheritdoc />
482         public Task BeginTransactionAsync()
483             => BeginTransactionAsync(CancellationToken.None);
484
485         /// <inheritdoc />
486         public async Task BeginTransactionAsync(CancellationToken cancellationToken)
487         {
488             _transactionDepth++;
489
490             if (_transactionDepth == 1)
491             {
492                 await OpenSharedConnectionAsync(cancellationToken).ConfigureAwait(false);
493                 _transaction = !_isolationLevel.HasValue ? _sharedConnection.BeginTransaction() : _sharedConnection.BeginTransaction(_isolationLevel.Value);
494                 _transactionCancelled = false;
495                 OnBeginTransaction();
496             }
497         }
498 #endif
499
500         /// <summary>
501         ///     Internal helper to cleanup transaction
502         /// </summary>
503         private void CleanupTransaction()
504         {
505             OnEndTransaction();
506
507             if (_transactionCancelled)
508                 _transaction.Rollback();
509             else
510                 _transaction.Commit();
511
512             _transaction.Dispose();
513             _transaction = null;
514
515             CloseSharedConnection();
516         }
517
518         /// <inheritdoc />
519         public void AbortTransaction()
520         {
521             _transactionCancelled = true;
522             if ((--_transactionDepth) == 0)
523                 CleanupTransaction();
524         }
525
526         /// <inheritdoc />
527         public void CompleteTransaction()
528         {
529             if ((--_transactionDepth) == 0)
530                 CleanupTransaction();
531         }
532
533 #endregion
534
535 #region Command Management
536
537         /// <summary>
538         ///     Add a parameter to a DB command
539         /// </summary>
540         /// <param name="cmd">A reference to the IDbCommand to which the parameter is to be added</param>
541         /// <param name="value">The value to assign to the parameter</param>
542         /// <param name="pi">Optional, a reference to the property info of the POCO property from which the value is coming.</param>
543         private void AddParam(IDbCommand cmd, object value, PropertyInfo pi)
544         {
545             // Convert value to from poco type to db type
546             if (pi != null)
547             {
548                 var mapper = Mappers.GetMapper(pi.DeclaringType, _defaultMapper);
549                 var fn = mapper.GetToDbConverter(pi);
550                 if (fn != null)
551                     value = fn(value);
552             }
553
554             // Support passed in parameters
555             if (value is IDbDataParameter idbParam)
556             {
557                 if (cmd.CommandType == CommandType.Text)
558                     idbParam.ParameterName = cmd.Parameters.Count.EnsureParamPrefix(_paramPrefix);
559                 else if (idbParam.ParameterName?.StartsWith(_paramPrefix) != true)
560                     idbParam.ParameterName = idbParam.ParameterName.EnsureParamPrefix(_paramPrefix);
561
562                 cmd.Parameters.Add(idbParam);
563             }
564             else
565             {
566                 var p = cmd.CreateParameter();
567                 p.ParameterName = cmd.Parameters.Count.EnsureParamPrefix(_paramPrefix);
568                 SetParameterProperties(p, value, pi);
569
570                 cmd.Parameters.Add(p);
571             }
572         }
573
574         private void SetParameterProperties(IDbDataParameter p, object value, PropertyInfo pi)
575         {
576             // Assign the parameter value
577             if (value == null)
578             {
579                 p.Value = DBNull.Value;
580
581                 if (pi?.PropertyType.Name == "Byte[]")
582                     p.DbType = DbType.Binary;
583             }
584             else
585             {
586                 // Give the database type first crack at converting to DB required type
587                 value = _provider.MapParameterValue(value);
588
589                 var t = value.GetType();
590                 if (t.IsEnum) // PostgreSQL .NET driver wont cast enum to int
591                 {
592                     p.Value = Convert.ChangeType(value, ((Enum) value).GetTypeCode());
593                 }
594                 else if (t == typeof(Guid) && !_provider.HasNativeGuidSupport)
595                 {
596                     p.Value = value.ToString();
597                     p.DbType = DbType.String;
598                     p.Size = 40;
599                 }
600                 else if (t == typeof(string))
601                 {
602                     // out of memory exception occurs if trying to save more than 4000 characters to SQL Server CE NText column. Set before attempting to set Size, or Size will always max out at 4000
603                     if ((value as string).Length + 1 > 4000 && p.GetType().Name == "SqlCeParameter")
604                         p.GetType().GetProperty("SqlDbType").SetValue(p, SqlDbType.NText, null);
605
606                     p.Size = Math.Max((value as string).Length + 1, 4000); // Help query plan caching by using common size
607                     p.Value = value;
608                 }
609                 else if (t == typeof(AnsiString))
610                 {
611                     var asValue = (value as AnsiString).Value;
612                     if (asValue == null)
613                     {
614                         p.Size = 0;
615                         p.Value = DBNull.Value;
616                     }
617                     else
618                     {
619                         p.Size = Math.Max(asValue.Length + 1, 4000);
620                         p.Value = asValue;
621                     }
622                     // Thanks @DataChomp for pointing out the SQL Server indexing performance hit of using wrong string type on varchar
623                     p.DbType = DbType.AnsiString;
624                 }
625                 else if (value.GetType().Name == "SqlGeography") //SqlGeography is a CLR Type
626                 {
627                     p.GetType().GetProperty("UdtTypeName").SetValue(p, "geography", null); //geography is the equivalent SQL Server Type
628                     p.Value = value;
629                 }
630                 else if (value.GetType().Name == "SqlGeometry") //SqlGeometry is a CLR Type
631                 {
632                     p.GetType().GetProperty("UdtTypeName").SetValue(p, "geometry", null); //geography is the equivalent SQL Server Type
633                     p.Value = value;
634                 }
635                 else
636                 {
637                     p.Value = value;
638                 }
639             }
640         }
641
642         public IDbCommand CreateCommand(IDbConnection connection, string sql, params object[] args)
643             => CreateCommand(connection, CommandType.Text, sql, args);
644
645         public IDbCommand CreateCommand(IDbConnection connection, CommandType commandType, string sql, params object[] args)
646         {
647             var cmd = connection.CreateCommand();
648             cmd.Connection = connection;
649             cmd.CommandType = commandType;
650             cmd.Transaction = _transaction;
651
652             switch (commandType)
653             {
654                 case CommandType.Text:
655                     // Perform named argument replacements
656                     if (EnableNamedParams)
657                     {
658                         var newArgs = new List<object>();
659                         sql = ParametersHelper.ProcessQueryParams(sql, args, newArgs);
660                         args = newArgs.ToArray();
661                     }
662
663                     // Perform parameter prefix replacements
664                     if (_paramPrefix != "@")
665                         sql = sql.ReplaceParamPrefix(_paramPrefix);
666                     sql = sql.Replace("@@", "@"); // <- double @@ escapes a single @
667                     break;
668                 case CommandType.StoredProcedure:
669                     args = ParametersHelper.ProcessStoredProcParams(cmd, args, SetParameterProperties);
670                     break;
671                 case CommandType.TableDirect:
672                     break;
673             }
674
675             cmd.CommandText = sql;
676
677             foreach (var item in args)
678                 AddParam(cmd, item, null);
679
680             return cmd;
681         }
682
683 #endregion
684
685 #region Exception Reporting and Logging
686
687         /// <summary>
688         ///     Called if an exception occurs during processing of a DB operation.  Override to provide custom logging/handling.
689         /// </summary>
690         /// <param name="x">The exception instance</param>
691         /// <returns>True to re-throw the exception, false to suppress it</returns>
692         public virtual bool OnException(Exception x)
693         {
694             System.Diagnostics.Debug.WriteLine(x.ToString());
695             System.Diagnostics.Debug.WriteLine(LastCommand);
696
697             var args = new ExceptionEventArgs(x);
698             ExceptionThrown?.Invoke(this, new ExceptionEventArgs(x));
699             return args.Raise;
700         }
701
702         /// <summary>
703         ///     Called when DB connection opened
704         /// </summary>
705         /// <param name="conn">The newly-opened IDbConnection</param>
706         /// <returns>The same or a replacement IDbConnection</returns>
707         /// <remarks>
708         ///     Override this method to provide custom logging of opening connection, or
709         ///     to provide a proxy IDbConnection.
710         /// </remarks>
711         public virtual IDbConnection OnConnectionOpened(IDbConnection conn)
712         {
713             var args = new DbConnectionEventArgs(conn);
714             ConnectionOpened?.Invoke(this, args);
715             return args.Connection;
716         }
717
718         /// <summary>
719         ///     Called when DB connection closed
720         /// </summary>
721         /// <param name="conn">The soon-to-be-closed IDBConnection</param>
722         public virtual void OnConnectionClosing(IDbConnection conn)
723         {
724             ConnectionClosing?.Invoke(this, new DbConnectionEventArgs(conn));
725         }
726
727         /// <summary>
728         ///     Called just before an DB command is executed
729         /// </summary>
730         /// <param name="cmd">The command to be executed</param>
731         /// <remarks>
732         ///     Override this method to provide custom logging of commands and/or
733         ///     modification of the IDbCommand before it's executed
734         /// </remarks>
735         public virtual void OnExecutingCommand(IDbCommand cmd)
736         {
737             CommandExecuting?.Invoke(this, new DbCommandEventArgs(cmd));
738         }
739
740         /// <summary>
741         ///     Called on completion of command execution
742         /// </summary>
743         /// <param name="cmd">The IDbCommand that finished executing</param>
744         public void OnExecutedCommand(IDbCommand cmd)
745         {
746             CommandExecuted?.Invoke(this, new DbCommandEventArgs(cmd));
747         }
748
749 #endregion
750
751 #region operation: Execute
752
753         /// <inheritdoc />
754         public int Execute(string sql, params object[] args)
755             => ExecuteInternal(CommandType.Text, sql, args);
756
757         /// <inheritdoc />
758         public int Execute(Sql sql)
759             => Execute(sql.SQL, sql.Arguments);
760
761         protected virtual int ExecuteInternal(CommandType commandType, string sql, params object[] args)
762         {
763             try
764             {
765                 OpenSharedConnection();
766                 try
767                 {
768                     using (var cmd = CreateCommand(_sharedConnection, commandType, sql, args))
769                     {
770                         return ExecuteNonQueryHelper(cmd);
771                     }
772                 }
773                 finally
774                 {
775                     CloseSharedConnection();
776                 }
777             }
778             catch (Exception x)
779             {
780                 if (OnException(x))
781                     throw;
782                 return -1;
783             }
784         }
785
786 #if ASYNC
787
788         public Task<int> ExecuteAsync(string sql, params object[] args)
789             => ExecuteInternalAsync(CancellationToken.None, CommandType.Text, sql, args);
790
791         public Task<int> ExecuteAsync(CancellationToken cancellationToken, string sql, params object[] args)
792             => ExecuteInternalAsync(cancellationToken, CommandType.Text, sql, args);
793
794         public Task<int> ExecuteAsync(Sql sql)
795             => ExecuteInternalAsync(CancellationToken.None, CommandType.Text, sql.SQL, sql.Arguments);
796
797         public Task<int> ExecuteAsync(CancellationToken cancellationToken, Sql sql)
798             => ExecuteInternalAsync(cancellationToken, CommandType.Text, sql.SQL, sql.Arguments);
799
800         protected virtual async Task<int> ExecuteInternalAsync(CancellationToken cancellationToken, CommandType commandType, string sql, params object[] args)
801         {
802             try
803             {
804                 await OpenSharedConnectionAsync(cancellationToken).ConfigureAwait(false);
805                 try
806                 {
807                     using (var cmd = CreateCommand(_sharedConnection, commandType, sql, args))
808                     {
809                         return await ExecuteNonQueryHelperAsync(cancellationToken, cmd).ConfigureAwait(false);
810                     }
811                 }
812                 finally
813                 {
814                     CloseSharedConnection();
815                 }
816             }
817             catch (Exception x)
818             {
819                 if (OnException(x))
820                     throw;
821                 return -1;
822             }
823         }
824
825 #endif
826
827 #endregion
828
829 #region operation: ExecuteScalar
830
831         /// <inheritdoc />
832         public T ExecuteScalar<T>(string sql, params object[] args)
833             => ExecuteScalarInternal<T>(CommandType.Text, sql, args);
834
835         /// <inheritdoc />
836         public T ExecuteScalar<T>(Sql sql)
837             => ExecuteScalar<T>(sql.SQL, sql.Arguments);
838
839         protected virtual T ExecuteScalarInternal<T>(CommandType commandType, string sql, params object[] args)
840         {
841             try
842             {
843                 OpenSharedConnection();
844                 try
845                 {
846                     using (var cmd = CreateCommand(_sharedConnection, commandType, sql, args))
847                     {
848                         var val = ExecuteScalarHelper(cmd);
849
850                         // Handle nullable types
851                         var u = Nullable.GetUnderlyingType(typeof(T));
852                         if (u != null && (val == null || val == DBNull.Value))
853                             return default(T);
854
855                         return (T) Convert.ChangeType(val, u == null ? typeof(T) : u);
856                     }
857                 }
858                 finally
859                 {
860                     CloseSharedConnection();
861                 }
862             }
863             catch (Exception x)
864             {
865                 if (OnException(x))
866                     throw;
867                 return default(T);
868             }
869         }
870
871 #if ASYNC
872
873         /// <inheritdoc />
874         public Task<T> ExecuteScalarAsync<T>(string sql, params object[] args)
875             => ExecuteScalarInternalAsync<T>(CancellationToken.None, CommandType.Text, sql, args);
876
877         /// <inheritdoc />
878         public Task<T> ExecuteScalarAsync<T>(CancellationToken cancellationToken, string sql, params object[] args)
879             => ExecuteScalarInternalAsync<T>(cancellationToken, CommandType.Text, sql, args);
880
881         /// <inheritdoc />
882         public Task<T> ExecuteScalarAsync<T>(Sql sql)
883             => ExecuteScalarInternalAsync<T>(CancellationToken.None, CommandType.Text, sql.SQL, sql.Arguments);
884
885         /// <inheritdoc />
886         public Task<T> ExecuteScalarAsync<T>(CancellationToken cancellationToken, Sql sql)
887             => ExecuteScalarInternalAsync<T>(cancellationToken, CommandType.Text, sql.SQL, sql.Arguments);
888
889         protected virtual async Task<T> ExecuteScalarInternalAsync<T>(CancellationToken cancellationToken, CommandType commandType, string sql,
890                                                                       params object[] args)
891         {
892             try
893             {
894                 await OpenSharedConnectionAsync(cancellationToken).ConfigureAwait(false);
895                 try
896                 {
897                     using (var cmd = CreateCommand(_sharedConnection, commandType, sql, args))
898                     {
899                         var val = await ExecuteScalarHelperAsync(cancellationToken, cmd).ConfigureAwait(false);
900
901                         var u = Nullable.GetUnderlyingType(typeof(T));
902                         if (u != null && (val == null || val == DBNull.Value))
903                             return default(T);
904
905                         return (T) Convert.ChangeType(val, u == null ? typeof(T) : u);
906                     }
907                 }
908                 finally
909                 {
910                     CloseSharedConnection();
911                 }
912             }
913             catch (Exception x)
914             {
915                 if (OnException(x))
916                     throw;
917                 return default(T);
918             }
919         }
920
921 #endif
922
923 #endregion
924
925 #region operation: Fetch
926
927         /// <inheritdoc />
928         public List<T> Fetch<T>()
929             => Fetch<T>(string.Empty);
930
931         /// <inheritdoc />
932         public List<T> Fetch<T>(string sql, params object[] args)
933             => Query<T>(sql, args).ToList();
934
935         /// <inheritdoc />
936         public List<T> Fetch<T>(Sql sql)
937             => Fetch<T>(sql.SQL, sql.Arguments);
938
939         /// <inheritdoc />
940         public List<T> Fetch<T>(long page, long itemsPerPage)
941             => Fetch<T>(page, itemsPerPage, string.Empty);
942
943         /// <inheritdoc />
944         public List<T> Fetch<T>(long page, long itemsPerPage, string sql, params object[] args)
945             => SkipTake<T>((page - 1) * itemsPerPage, itemsPerPage, sql, args);
946
947         /// <inheritdoc />
948         public List<T> Fetch<T>(long page, long itemsPerPage, Sql sql)
949             => SkipTake<T>((page - 1) * itemsPerPage, itemsPerPage, sql.SQL, sql.Arguments);
950
951 #if ASYNC
952         /// <inheritdoc />
953         public Task<List<T>> FetchAsync<T>()
954             => FetchAsync<T>(CancellationToken.None, CommandType.Text, string.Empty);
955
956         /// <inheritdoc />
957         public Task<List<T>> FetchAsync<T>(CommandType commandType)
958             => FetchAsync<T>(CancellationToken.None, CommandType.Text, string.Empty);
959
960         /// <inheritdoc />
961         public Task<List<T>> FetchAsync<T>(CancellationToken cancellationToken)
962             => FetchAsync<T>(cancellationToken, CommandType.Text, string.Empty);
963
964         /// <inheritdoc />
965         public Task<List<T>> FetchAsync<T>(CancellationToken cancellationToken, CommandType commandType)
966             => FetchAsync<T>(cancellationToken, commandType, string.Empty);
967
968         /// <inheritdoc />
969         public Task<List<T>> FetchAsync<T>(string sql, params object[] args)
970             => FetchAsync<T>(CancellationToken.None, CommandType.Text, sql, args);
971
972         /// <inheritdoc />
973         public Task<List<T>> FetchAsync<T>(CommandType commandType, string sql, params object[] args)
974             => FetchAsync<T>(CancellationToken.None, commandType, sql, args);
975
976         /// <inheritdoc />
977         public Task<List<T>> FetchAsync<T>(CancellationToken cancellationToken, string sql, params object[] args)
978             => FetchAsync<T>(CancellationToken.None, CommandType.Text, sql, args);
979
980         /// <inheritdoc />
981         public async Task<List<T>> FetchAsync<T>(CancellationToken cancellationToken, CommandType commandType, string sql, params object[] args)
982         {
983             var pocos = new List<T>();
984             await QueryAsync<T>(p => pocos.Add(p), cancellationToken, commandType, sql, args).ConfigureAwait(false);
985             return pocos;
986         }
987
988         /// <inheritdoc />
989         public Task<List<T>> FetchAsync<T>(Sql sql)
990             => FetchAsync<T>(CancellationToken.None, CommandType.Text, sql.SQL, sql.Arguments);
991
992         /// <inheritdoc />
993         public Task<List<T>> FetchAsync<T>(CommandType commandType, Sql sql)
994             => FetchAsync<T>(CancellationToken.None, commandType, sql.SQL, sql.Arguments);
995
996         /// <inheritdoc />
997         public Task<List<T>> FetchAsync<T>(CancellationToken cancellationToken, Sql sql)
998             => FetchAsync<T>(cancellationToken, CommandType.Text, sql.SQL, sql.Arguments);
999
1000         /// <inheritdoc />
1001         public Task<List<T>> FetchAsync<T>(CancellationToken cancellationToken, CommandType commandType, Sql sql)
1002             => FetchAsync<T>(cancellationToken, commandType, sql.SQL, sql.Arguments);
1003
1004         /// <inheritdoc />
1005         public Task<List<T>> FetchAsync<T>(long page, long itemsPerPage)
1006             => FetchAsync<T>(page, itemsPerPage, string.Empty);
1007
1008         /// <inheritdoc />
1009         public Task<List<T>> FetchAsync<T>(CancellationToken cancellationToken, long page, long itemsPerPage)
1010             => FetchAsync<T>(cancellationToken, page, itemsPerPage, string.Empty);
1011
1012         /// <inheritdoc />
1013         public Task<List<T>> FetchAsync<T>(long page, long itemsPerPage, string sql, params object[] args)
1014             => FetchAsync<T>(CancellationToken.None, page, itemsPerPage, sql, args);
1015
1016         /// <inheritdoc />
1017         public Task<List<T>> FetchAsync<T>(CancellationToken cancellationToken, long page, long itemsPerPage, string sql, params object[] args)
1018             => SkipTakeAsync<T>(cancellationToken, (page - 1) * itemsPerPage, itemsPerPage, sql, args);
1019
1020         /// <inheritdoc />
1021         public Task<List<T>> FetchAsync<T>(long page, long itemsPerPage, Sql sql)
1022             => FetchAsync<T>(CancellationToken.None, page, itemsPerPage, sql);
1023
1024         /// <inheritdoc />
1025         public Task<List<T>> FetchAsync<T>(CancellationToken cancellationToken, long page, long itemsPerPage, Sql sql)
1026             => SkipTakeAsync<T>(cancellationToken, (page - 1) * itemsPerPage, itemsPerPage, sql.SQL, sql.Arguments);
1027
1028 #endif
1029
1030 #endregion
1031
1032 #region operation: Page
1033
1034         /// <summary>
1035         ///     Starting with a regular SELECT statement, derives the SQL statements required to query a
1036         ///     DB for a page of records and the total number of records
1037         /// </summary>
1038         /// <typeparam name="T">The Type representing a row in the result set</typeparam>
1039         /// <param name="skip">The number of rows to skip before the start of the page</param>
1040         /// <param name="take">The number of rows in the page</param>
1041         /// <param name="sql">The original SQL select statement</param>
1042         /// <param name="args">Arguments to any embedded parameters in the SQL</param>
1043         /// <param name="sqlCount">Outputs the SQL statement to query for the total number of matching rows</param>
1044         /// <param name="sqlPage">Outputs the SQL statement to retrieve a single page of matching rows</param>
1045         protected virtual void BuildPageQueries<T>(long skip, long take, string sql, ref object[] args, out string sqlCount, out string sqlPage)
1046         {
1047             if (EnableAutoSelect)
1048                 sql = AutoSelectHelper.AddSelectClause<T>(_provider, sql, _defaultMapper);
1049
1050             SQLParts parts;
1051             if (!Provider.PagingUtility.SplitSQL(sql, out parts))
1052                 throw new Exception("Unable to parse SQL statement for paged query");
1053
1054             sqlPage = _provider.BuildPageQuery(skip, take, parts, ref args);
1055             sqlCount = parts.SqlCount;
1056         }
1057
1058         /// <inheritdoc />
1059         public Page<T> Page<T>(long page, long itemsPerPage, string sqlCount, object[] countArgs, string sqlPage, object[] pageArgs)
1060         {
1061             // Save the one-time command time out and use it for both queries
1062             var saveTimeout = OneTimeCommandTimeout;
1063
1064             // Setup the paged result
1065             var result = new Page<T>
1066             {
1067                 CurrentPage = page,
1068                 ItemsPerPage = itemsPerPage,
1069                 TotalItems = ExecuteScalar<long>(sqlCount, countArgs)
1070             };
1071             result.TotalPages = result.TotalItems / itemsPerPage;
1072
1073             if (result.TotalItems % itemsPerPage != 0)
1074                 result.TotalPages++;
1075
1076             OneTimeCommandTimeout = saveTimeout;
1077
1078             result.Items = Fetch<T>(sqlPage, pageArgs);
1079
1080             return result;
1081         }
1082
1083         /// <inheritdoc />
1084         public Page<T> Page<T>(long page, long itemsPerPage)
1085             => Page<T>(page, itemsPerPage, string.Empty);
1086
1087         /// <inheritdoc />
1088         public Page<T> Page<T>(long page, long itemsPerPage, string sql, params object[] args)
1089         {
1090             BuildPageQueries<T>((page - 1) * itemsPerPage, itemsPerPage, sql, ref args, out var sqlCount, out var sqlPage);
1091             return Page<T>(page, itemsPerPage, sqlCount, args, sqlPage, args);
1092         }
1093
1094         /// <inheritdoc />
1095         public Page<T> Page<T>(long page, long itemsPerPage, Sql sql)
1096             => Page<T>(page, itemsPerPage, sql.SQL, sql.Arguments);
1097
1098         /// <inheritdoc />
1099         public Page<T> Page<T>(long page, long itemsPerPage, Sql sqlCount, Sql sqlPage)
1100             => Page<T>(page, itemsPerPage, sqlCount.SQL, sqlCount.Arguments, sqlPage.SQL, sqlPage.Arguments);
1101
1102 #if ASYNC
1103
1104         /// <inheritdoc />
1105         public async Task<Page<T>> PageAsync<T>(CancellationToken cancellationToken, long page, long itemsPerPage, string sqlCount, object[] countArgs,
1106                                                 string sqlPage, object[] pageArgs)
1107         {
1108             var saveTimeout = OneTimeCommandTimeout;
1109
1110             var result = new Page<T>
1111             {
1112                 CurrentPage = page,
1113                 ItemsPerPage = itemsPerPage,
1114                 TotalItems = await ExecuteScalarAsync<long>(cancellationToken, sqlCount, countArgs).ConfigureAwait(false)
1115             };
1116             result.TotalPages = result.TotalItems / itemsPerPage;
1117
1118             if (result.TotalItems % itemsPerPage != 0)
1119                 result.TotalPages++;
1120
1121             OneTimeCommandTimeout = saveTimeout;
1122
1123             result.Items = await FetchAsync<T>(cancellationToken, sqlPage, pageArgs).ConfigureAwait(false);
1124
1125             return result;
1126         }
1127
1128         /// <inheritdoc />
1129         public Task<Page<T>> PageAsync<T>(long page, long itemsPerPage, string sqlCount, object[] countArgs, string sqlPage, object[] pageArgs)
1130             => PageAsync<T>(CancellationToken.None, page, itemsPerPage, sqlCount, countArgs, sqlPage, pageArgs);
1131
1132         /// <inheritdoc />
1133         public Task<Page<T>> PageAsync<T>(CancellationToken cancellationToken, long page, long itemsPerPage)
1134             => PageAsync<T>(cancellationToken, page, itemsPerPage, string.Empty);
1135
1136         /// <inheritdoc />
1137         public Task<Page<T>> PageAsync<T>(long page, long itemsPerPage)
1138             => PageAsync<T>(CancellationToken.None, page, itemsPerPage, string.Empty);
1139
1140         /// <inheritdoc />
1141         public Task<Page<T>> PageAsync<T>(CancellationToken cancellationToken, long page, long itemsPerPage, string sql, params object[] args)
1142         {
1143             BuildPageQueries<T>((page - 1) * itemsPerPage, itemsPerPage, sql, ref args, out var sqlCount, out var sqlPage);
1144             return PageAsync<T>(cancellationToken, page, itemsPerPage, sqlCount, args, sqlPage, args);
1145         }
1146
1147         /// <inheritdoc />
1148         public Task<Page<T>> PageAsync<T>(long page, long itemsPerPage, string sql, params object[] args)
1149             => PageAsync<T>(CancellationToken.None, page, itemsPerPage, sql, args);
1150
1151         /// <inheritdoc />
1152         public Task<Page<T>> PageAsync<T>(CancellationToken cancellationToken, long page, long itemsPerPage, Sql sql)
1153             => PageAsync<T>(cancellationToken, page, itemsPerPage, sql.SQL, sql.Arguments);
1154
1155         /// <inheritdoc />
1156         public Task<Page<T>> PageAsync<T>(long page, long itemsPerPage, Sql sql)
1157             => PageAsync<T>(CancellationToken.None, page, itemsPerPage, sql.SQL, sql.Arguments);
1158
1159         /// <inheritdoc />
1160         public Task<Page<T>> PageAsync<T>(CancellationToken cancellationToken, long page, long itemsPerPage, Sql sqlCount, Sql sqlPage)
1161             => PageAsync<T>(cancellationToken, page, itemsPerPage, sqlCount.SQL, sqlCount.Arguments, sqlPage.SQL, sqlPage.Arguments);
1162
1163         /// <inheritdoc />
1164         public Task<Page<T>> PageAsync<T>(long page, long itemsPerPage, Sql sqlCount, Sql sqlPage)
1165             => PageAsync<T>(CancellationToken.None, page, itemsPerPage, sqlCount.SQL, sqlCount.Arguments, sqlPage.SQL, sqlPage.Arguments);
1166
1167 #endif
1168
1169 #endregion
1170
1171 #region operation: SkipTake
1172
1173         /// <inheritdoc />
1174         public List<T> SkipTake<T>(long skip, long take)
1175             => SkipTake<T>(skip, take, string.Empty);
1176
1177         /// <inheritdoc />
1178         public List<T> SkipTake<T>(long skip, long take, Sql sql)
1179             => SkipTake<T>(skip, take, sql.SQL, sql.Arguments);
1180
1181         /// <inheritdoc />
1182         public List<T> SkipTake<T>(long skip, long take, string sql, params object[] args)
1183         {
1184             BuildPageQueries<T>(skip, take, sql, ref args, out var sqlCount, out var sqlPage);
1185             return Fetch<T>(sqlPage, args);
1186         }
1187
1188 #if ASYNC
1189
1190         /// <inheritdoc />
1191         public Task<List<T>> SkipTakeAsync<T>(CancellationToken cancellationToken, long skip, long take)
1192             => SkipTakeAsync<T>(cancellationToken, skip, take, string.Empty);
1193
1194         /// <inheritdoc />
1195         public Task<List<T>> SkipTakeAsync<T>(long skip, long take)
1196             => SkipTakeAsync<T>(CancellationToken.None, skip, take, string.Empty);
1197
1198         /// <inheritdoc />
1199         public Task<List<T>> SkipTakeAsync<T>(CancellationToken cancellationToken, long skip, long take, string sql, params object[] args)
1200         {
1201             BuildPageQueries<T>(skip, take, sql, ref args, out var sqlCount, out var sqlPage);
1202             return FetchAsync<T>(cancellationToken, sqlPage, args);
1203         }
1204
1205         /// <inheritdoc />
1206         public Task<List<T>> SkipTakeAsync<T>(long skip, long take, string sql, params object[] args)
1207             => SkipTakeAsync<T>(CancellationToken.None, skip, take, sql, args);
1208
1209         /// <inheritdoc />
1210         public Task<List<T>> SkipTakeAsync<T>(CancellationToken cancellationToken, long skip, long take, Sql sql)
1211             => SkipTakeAsync<T>(cancellationToken, skip, take, sql.SQL, sql.Arguments);
1212
1213         /// <inheritdoc />
1214         public Task<List<T>> SkipTakeAsync<T>(long skip, long take, Sql sql)
1215             => SkipTakeAsync<T>(skip, take, sql.SQL, sql.Arguments);
1216
1217 #endif
1218
1219 #endregion
1220
1221 #region operation: Query
1222
1223         /// <inheritdoc />
1224         public IEnumerable<T> Query<T>()
1225             => Query<T>(string.Empty);
1226
1227         /// <inheritdoc />
1228         public IEnumerable<T> Query<T>(string sql, params object[] args)
1229         {
1230             if (EnableAutoSelect)
1231                 sql = AutoSelectHelper.AddSelectClause<T>(_provider, sql, _defaultMapper);
1232
1233             return ExecuteReader<T>(CommandType.Text, sql, args);
1234         }
1235
1236         /// <inheritdoc />
1237         public IEnumerable<T> Query<T>(Sql sql)
1238             => Query<T>(sql.SQL, sql.Arguments);
1239
1240 #if ASYNC
1241         /// <inheritdoc />
1242         public Task QueryAsync<T>(Action<T> receivePocoCallback)
1243             => QueryAsync(receivePocoCallback, CancellationToken.None, CommandType.Text, string.Empty);
1244
1245         /// <inheritdoc />
1246         public Task QueryAsync<T>(Action<T> receivePocoCallback, CommandType commandType)
1247             => QueryAsync(receivePocoCallback, CancellationToken.None, commandType, string.Empty);
1248
1249         /// <inheritdoc />
1250         public Task QueryAsync<T>(Action<T> receivePocoCallback, CancellationToken cancellationToken)
1251             => QueryAsync(receivePocoCallback, cancellationToken, CommandType.Text, string.Empty);
1252
1253         /// <inheritdoc />
1254         public Task QueryAsync<T>(Action<T> receivePocoCallback, CancellationToken cancellationToken, CommandType commandType)
1255             => QueryAsync(receivePocoCallback, cancellationToken, commandType, string.Empty);
1256
1257         /// <inheritdoc />
1258         public Task QueryAsync<T>(Action<T> receivePocoCallback, string sql, params object[] args)
1259             => QueryAsync(receivePocoCallback, CancellationToken.None, CommandType.Text, sql, args);
1260
1261         /// <inheritdoc />
1262         public Task QueryAsync<T>(Action<T> receivePocoCallback, CommandType commandType, string sql, params object[] args)
1263             => QueryAsync(receivePocoCallback, CancellationToken.None, commandType, sql, args);
1264
1265         /// <inheritdoc />
1266         public Task QueryAsync<T>(Action<T> receivePocoCallback, CancellationToken cancellationToken, string sql, params object[] args)
1267             => QueryAsync(receivePocoCallback, CancellationToken.None, CommandType.Text, sql, args);
1268
1269         /// <inheritdoc />
1270         public Task QueryAsync<T>(Action<T> receivePocoCallback, CancellationToken cancellationToken, CommandType commandType, string sql, params object[] args)
1271         {
1272             if (EnableAutoSelect)
1273                 sql = AutoSelectHelper.AddSelectClause<T>(_provider, sql, _defaultMapper);
1274             return ExecuteReaderAsync(receivePocoCallback, cancellationToken, commandType, sql, args);
1275         }
1276
1277         /// <inheritdoc />
1278         public Task QueryAsync<T>(Action<T> receivePocoCallback, Sql sql)
1279             => QueryAsync(receivePocoCallback, CancellationToken.None, CommandType.Text, sql.SQL, sql.Arguments);
1280
1281         /// <inheritdoc />
1282         public Task QueryAsync<T>(Action<T> receivePocoCallback, CommandType commandType, Sql sql)
1283             => QueryAsync(receivePocoCallback, CancellationToken.None, commandType, sql.SQL, sql.Arguments);
1284
1285         /// <inheritdoc />
1286         public Task QueryAsync<T>(Action<T> receivePocoCallback, CancellationToken cancellationToken, Sql sql)
1287             => QueryAsync(receivePocoCallback, cancellationToken, CommandType.Text, sql.SQL, sql.Arguments);
1288
1289         /// <inheritdoc />
1290         public Task QueryAsync<T>(Action<T> receivePocoCallback, CancellationToken cancellationToken, CommandType commandType, Sql sql)
1291             => QueryAsync(receivePocoCallback, cancellationToken, commandType, sql.SQL, sql.Arguments);
1292
1293         /// <inheritdoc />
1294         public Task<IAsyncReader<T>> QueryAsync<T>()
1295             => QueryAsync<T>(CancellationToken.None, CommandType.Text, string.Empty);
1296
1297         /// <inheritdoc />
1298         public Task<IAsyncReader<T>> QueryAsync<T>(CommandType commandType)
1299             => QueryAsync<T>(CancellationToken.None, commandType, string.Empty);
1300
1301         /// <inheritdoc />
1302         public Task<IAsyncReader<T>> QueryAsync<T>(CancellationToken cancellationToken)
1303             => QueryAsync<T>(cancellationToken, CommandType.Text, string.Empty);
1304
1305         /// <inheritdoc />
1306         public Task<IAsyncReader<T>> QueryAsync<T>(CancellationToken cancellationToken, CommandType commandType)
1307             => QueryAsync<T>(cancellationToken, commandType, string.Empty);
1308
1309         /// <inheritdoc />
1310         public Task<IAsyncReader<T>> QueryAsync<T>(string sql, params object[] args)
1311             => QueryAsync<T>(CancellationToken.None, CommandType.Text, sql, args);
1312
1313         /// <inheritdoc />
1314         public Task<IAsyncReader<T>> QueryAsync<T>(CommandType commandType, string sql, params object[] args)
1315             => QueryAsync<T>(CancellationToken.None, commandType, sql, args);
1316
1317         /// <inheritdoc />
1318         public Task<IAsyncReader<T>> QueryAsync<T>(CancellationToken cancellationToken, string sql, params object[] args)
1319             => QueryAsync<T>(CancellationToken.None, CommandType.Text, sql, args);
1320
1321         /// <inheritdoc />
1322         public Task<IAsyncReader<T>> QueryAsync<T>(CancellationToken cancellationToken, CommandType commandType, string sql, params object[] args)
1323         {
1324             if (EnableAutoSelect)
1325                 sql = AutoSelectHelper.AddSelectClause<T>(_provider, sql, _defaultMapper);
1326
1327             return ExecuteReaderAsync<T>(cancellationToken, commandType, sql, args);
1328         }
1329
1330         /// <inheritdoc />
1331         public Task<IAsyncReader<T>> QueryAsync<T>(Sql sql)
1332             => QueryAsync<T>(CancellationToken.None, CommandType.Text, sql.SQL, sql.Arguments);
1333
1334         /// <inheritdoc />
1335         public Task<IAsyncReader<T>> QueryAsync<T>(CommandType commandType, Sql sql)
1336             => QueryAsync<T>(CancellationToken.None, commandType, sql.SQL, sql.Arguments);
1337
1338         /// <inheritdoc />
1339         public Task<IAsyncReader<T>> QueryAsync<T>(CancellationToken cancellationToken, Sql sql)
1340             => QueryAsync<T>(cancellationToken, CommandType.Text, sql.SQL, sql.Arguments);
1341
1342         /// <inheritdoc />
1343         public Task<IAsyncReader<T>> QueryAsync<T>(CancellationToken cancellationToken, CommandType commandType, Sql sql)
1344             => QueryAsync<T>(cancellationToken, commandType, sql.SQL, sql.Arguments);
1345
1346         protected virtual async Task ExecuteReaderAsync<T>(Action<T> processPoco, CancellationToken cancellationToken, CommandType commandType, string sql,
1347                                                            object[] args)
1348         {
1349             await OpenSharedConnectionAsync(cancellationToken).ConfigureAwait(false);
1350             try
1351             {
1352                 using (var cmd = CreateCommand(_sharedConnection, commandType, sql, args))
1353                 {
1354                     IDataReader reader;
1355                     var pd = PocoData.ForType(typeof(T), _defaultMapper);
1356                     
1357                     try
1358                     {
1359                         reader = await ExecuteReaderHelperAsync(cancellationToken, cmd).ConfigureAwait(false);
1360                     }
1361                     catch (Exception e)
1362                     {
1363                         if (OnException(e))
1364                             throw;
1365                         return;
1366                     }
1367
1368                     var readerAsync = reader as DbDataReader;
1369                     var factory =
1370                         pd.GetFactory(cmd.CommandText, _sharedConnection.ConnectionString, 0, reader.FieldCount, reader,
1371                             _defaultMapper) as Func<IDataReader, T>;
1372
1373                     using (reader)
1374                     {
1375                         while (true)
1376                         {
1377                             T poco;
1378                             try
1379                             {
1380                                 if (readerAsync != null)
1381                                 {
1382                                     if (!await readerAsync.ReadAsync(cancellationToken).ConfigureAwait(false))
1383                                         return;
1384                                 }
1385                                 else
1386                                 {
1387                                     if (!reader.Read())
1388                                         return;
1389                                 }
1390
1391                                 poco = factory(reader);
1392                                 processPoco(poco);
1393                             }
1394                             catch (Exception e)
1395                             {
1396                                 if (OnException(e))
1397                                     throw;
1398                                 return;
1399                             }
1400                         }
1401                     }
1402                 }
1403             }
1404             finally
1405             {
1406                 CloseSharedConnection();
1407             }
1408         }
1409
1410         protected virtual async Task<IAsyncReader<T>> ExecuteReaderAsync<T>(CancellationToken cancellationToken, CommandType commandType, string sql,
1411                                                                             object[] args)
1412         {
1413             await OpenSharedConnectionAsync(cancellationToken).ConfigureAwait(false);
1414             var cmd = CreateCommand(_sharedConnection, commandType, sql, args);
1415             IDataReader reader = null;
1416             var pd = PocoData.ForType(typeof(T), _defaultMapper);
1417
1418             try
1419             {
1420                 reader = await ExecuteReaderHelperAsync(cancellationToken, cmd).ConfigureAwait(false);
1421             }
1422             catch (Exception e)
1423             {
1424                 if (OnException(e))
1425                     throw;
1426                 try
1427                 {
1428                     cmd?.Dispose();
1429                     reader?.Dispose();
1430                 }
1431                 catch
1432                 {
1433                     // ignored
1434                 }
1435
1436                 return AsyncReader<T>.Empty();
1437             }
1438
1439             var factory =
1440                 pd.GetFactory(cmd.CommandText, _sharedConnection.ConnectionString, 0, reader.FieldCount, reader, _defaultMapper) as Func<IDataReader, T>;
1441
1442             return new AsyncReader<T>(this, cmd, reader, factory);
1443         }
1444
1445 #endif
1446
1447         protected virtual IEnumerable<T> ExecuteReader<T>(CommandType commandType, string sql, params object[] args)
1448         {
1449             OpenSharedConnection();
1450             try
1451             {
1452                 using (var cmd = CreateCommand(_sharedConnection, commandType, sql, args))
1453                 {
1454                     IDataReader r;
1455                     var pd = PocoData.ForType(typeof(T), _defaultMapper);
1456                     try
1457                     {
1458                         r = ExecuteReaderHelper(cmd);
1459                     }
1460                     catch (Exception x)
1461                     {
1462                         if (OnException(x))
1463                             throw;
1464                         yield break;
1465                     }
1466
1467                     var factory = pd.GetFactory(cmd.CommandText, _sharedConnection.ConnectionString, 0, r.FieldCount, r,
1468                         _defaultMapper) as Func<IDataReader, T>;
1469                     using (r)
1470                     {
1471                         while (true)
1472                         {
1473                             T poco;
1474                             try
1475                             {
1476                                 if (!r.Read())
1477                                     yield break;
1478                                 poco = factory(r);
1479                             }
1480                             catch (Exception x)
1481                             {
1482                                 if (OnException(x))
1483                                     throw;
1484                                 yield break;
1485                             }
1486
1487                             yield return poco;
1488                         }
1489                     }
1490                 }
1491             }
1492             finally
1493             {
1494                 CloseSharedConnection();
1495             }
1496         }
1497
1498 #endregion
1499
1500 #region operation: Exists
1501
1502         /// <inheritdoc />
1503         public bool Exists<T>(string sqlCondition, params object[] args)
1504         {
1505             var poco = PocoData.ForType(typeof(T), _defaultMapper).TableInfo;
1506
1507             if (sqlCondition.TrimStart().StartsWith("where", StringComparison.OrdinalIgnoreCase))
1508                 sqlCondition = sqlCondition.TrimStart().Substring(5);
1509
1510             return ExecuteScalar<int>(string.Format(_provider.GetExistsSql(), Provider.EscapeTableName(poco.TableName), sqlCondition), args) != 0;
1511         }
1512
1513         /// <inheritdoc />
1514         public bool Exists<T>(object primaryKey)
1515         {
1516             var poco = PocoData.ForType(typeof(T), _defaultMapper);
1517             return Exists<T>($"{_provider.EscapeSqlIdentifier(poco.TableInfo.PrimaryKey)}=@0",
1518                 primaryKey is T ? poco.Columns[poco.TableInfo.PrimaryKey].GetValue(primaryKey) : primaryKey);
1519         }
1520
1521 #if ASYNC
1522         public Task<bool> ExistsAsync<T>(object primaryKey)
1523             => ExistsAsync<T>(CancellationToken.None, primaryKey);
1524
1525         public Task<bool> ExistsAsync<T>(CancellationToken cancellationToken, object primaryKey)
1526         {
1527             var poco = PocoData.ForType(typeof(T), _defaultMapper);
1528             return ExistsAsync<T>(cancellationToken, $"{_provider.EscapeSqlIdentifier(poco.TableInfo.PrimaryKey)}=@0",
1529                 primaryKey is T ? poco.Columns[poco.TableInfo.PrimaryKey].GetValue(primaryKey) : primaryKey);
1530         }
1531
1532         public Task<bool> ExistsAsync<T>(string sqlCondition, params object[] args)
1533             => ExistsAsync<T>(CancellationToken.None, sqlCondition, args);
1534
1535         public async Task<bool> ExistsAsync<T>(CancellationToken cancellationToken, string sqlCondition, params object[] args)
1536         {
1537             var poco = PocoData.ForType(typeof(T), _defaultMapper).TableInfo;
1538
1539             if (sqlCondition.TrimStart().StartsWith("where", StringComparison.OrdinalIgnoreCase))
1540                 sqlCondition = sqlCondition.TrimStart().Substring(5);
1541
1542             return await ExecuteScalarAsync<int>(cancellationToken,
1543                        string.Format(_provider.GetExistsSql(), Provider.EscapeTableName(poco.TableName), sqlCondition), args).ConfigureAwait(false) != 0;
1544         }
1545 #endif
1546
1547 #endregion
1548
1549 #region operation: Linq style (Exists, Single, SingleOrDefault etc...)
1550
1551         /// <inheritdoc />
1552         public T Single<T>(object primaryKey)
1553             => Single<T>(GenerateSingleByKeySql<T>(primaryKey));
1554
1555         /// <inheritdoc />
1556         public T SingleOrDefault<T>(object primaryKey)
1557             => SingleOrDefault<T>(GenerateSingleByKeySql<T>(primaryKey));
1558
1559         /// <inheritdoc />
1560         public T Single<T>(string sql, params object[] args)
1561             => Query<T>(sql, args).Single();
1562
1563         /// <inheritdoc />
1564         public T SingleOrDefault<T>(string sql, params object[] args)
1565             => Query<T>(sql, args).SingleOrDefault();
1566
1567         /// <inheritdoc />
1568         public T First<T>(string sql, params object[] args)
1569             => Query<T>(sql, args).First();
1570
1571         /// <inheritdoc />
1572         public T FirstOrDefault<T>(string sql, params object[] args)
1573             => Query<T>(sql, args).FirstOrDefault();
1574
1575         /// <inheritdoc />
1576         public T Single<T>(Sql sql)
1577             => Query<T>(sql).Single();
1578
1579         /// <inheritdoc />
1580         public T SingleOrDefault<T>(Sql sql)
1581             => Query<T>(sql).SingleOrDefault();
1582
1583         /// <inheritdoc />
1584         public T First<T>(Sql sql)
1585             => Query<T>(sql).First();
1586
1587         /// <inheritdoc />
1588         public T FirstOrDefault<T>(Sql sql)
1589             => Query<T>(sql).FirstOrDefault();
1590
1591 #if ASYNC
1592
1593         /// <inheritdoc />
1594         public Task<T> SingleAsync<T>(object primaryKey)
1595             => SingleAsync<T>(CancellationToken.None, primaryKey);
1596
1597         /// <inheritdoc />
1598         public Task<T> SingleAsync<T>(CancellationToken cancellationToken, object primaryKey)
1599             => SingleAsync<T>(cancellationToken, GenerateSingleByKeySql<T>(primaryKey));
1600
1601         /// <inheritdoc />
1602         public Task<T> SingleAsync<T>(string sql, params object[] args)
1603             => SingleAsync<T>(CancellationToken.None, sql, args);
1604
1605         /// <inheritdoc />
1606         public async Task<T> SingleAsync<T>(CancellationToken cancellationToken, string sql, params object[] args)
1607             => (await FetchAsync<T>(cancellationToken, sql, args).ConfigureAwait(false)).Single();
1608
1609         /// <inheritdoc />
1610         public Task<T> SingleAsync<T>(Sql sql)
1611             => SingleAsync<T>(CancellationToken.None, sql);
1612
1613         /// <inheritdoc />
1614         public Task<T> SingleAsync<T>(CancellationToken cancellationToken, Sql sql)
1615             => SingleAsync<T>(cancellationToken, sql.SQL, sql.Arguments);
1616
1617         /// <inheritdoc />
1618         public Task<T> SingleOrDefaultAsync<T>(Sql sql)
1619             => SingleOrDefaultAsync<T>(CancellationToken.None, sql);
1620
1621         /// <inheritdoc />
1622         public Task<T> SingleOrDefaultAsync<T>(CancellationToken cancellationToken, Sql sql)
1623             => SingleOrDefaultAsync<T>(cancellationToken, sql.SQL, sql.Arguments);
1624
1625         /// <inheritdoc />
1626         public Task<T> SingleOrDefaultAsync<T>(object primaryKey)
1627             => SingleOrDefaultAsync<T>(CancellationToken.None, primaryKey);
1628
1629         /// <inheritdoc />
1630         public Task<T> SingleOrDefaultAsync<T>(CancellationToken cancellationToken, object primaryKey)
1631             => SingleOrDefaultAsync<T>(cancellationToken, GenerateSingleByKeySql<T>(primaryKey));
1632
1633         /// <inheritdoc />
1634         public Task<T> SingleOrDefaultAsync<T>(string sql, params object[] args)
1635             => SingleOrDefaultAsync<T>(CancellationToken.None, sql, args);
1636
1637         /// <inheritdoc />
1638         public async Task<T> SingleOrDefaultAsync<T>(CancellationToken cancellationToken, string sql, params object[] args)
1639             => (await FetchAsync<T>(cancellationToken, sql, args).ConfigureAwait(false)).SingleOrDefault();
1640
1641         /// <inheritdoc />
1642         public Task<T> FirstAsync<T>(string sql, params object[] args)
1643             => FirstAsync<T>(CancellationToken.None, sql, args);
1644
1645         /// <inheritdoc />
1646         public async Task<T> FirstAsync<T>(CancellationToken cancellationToken, string sql, params object[] args)
1647             => (await FetchAsync<T>(cancellationToken, sql, args).ConfigureAwait(false)).First();
1648
1649         /// <inheritdoc />
1650         public Task<T> FirstAsync<T>(Sql sql)
1651             => FirstAsync<T>(CancellationToken.None, sql);
1652
1653         /// <inheritdoc />
1654         public Task<T> FirstAsync<T>(CancellationToken cancellationToken, Sql sql)
1655             => FirstAsync<T>(cancellationToken, sql.SQL, sql.Arguments);
1656
1657         /// <inheritdoc />
1658         public Task<T> FirstOrDefaultAsync<T>(string sql, params object[] args)
1659             => FirstOrDefaultAsync<T>(CancellationToken.None, sql, args);
1660
1661         /// <inheritdoc />
1662         public async Task<T> FirstOrDefaultAsync<T>(CancellationToken cancellationToken, string sql, params object[] args)
1663             => (await FetchAsync<T>(cancellationToken, sql, args).ConfigureAwait(false)).FirstOrDefault();
1664
1665         /// <inheritdoc />
1666         public Task<T> FirstOrDefaultAsync<T>(Sql sql)
1667             => FirstOrDefaultAsync<T>(CancellationToken.None, sql);
1668
1669         /// <inheritdoc />
1670         public Task<T> FirstOrDefaultAsync<T>(CancellationToken cancellationToken, Sql sql)
1671             => FirstOrDefaultAsync<T>(cancellationToken, sql.SQL, sql.Arguments);
1672
1673 #endif
1674
1675         private Sql GenerateSingleByKeySql<T>(object primaryKey)
1676         {
1677             string pkName = _provider.EscapeSqlIdentifier(PocoData.ForType(typeof(T), _defaultMapper).TableInfo.PrimaryKey);
1678             var sql = $"WHERE {pkName} = @0";
1679
1680             if (!EnableAutoSelect)
1681                 // We're going to be nice and add the SELECT anyway
1682                 sql = AutoSelectHelper.AddSelectClause<T>(_provider, sql, _defaultMapper);
1683
1684             return new Sql(sql, primaryKey);
1685         }
1686
1687 #endregion
1688
1689 #region operation: Insert
1690
1691         /// <inheritdoc />
1692         public object Insert(string tableName, object poco)
1693         {
1694             if (string.IsNullOrEmpty(tableName))
1695                 throw new ArgumentNullException(nameof(tableName));
1696
1697             if (poco == null)
1698                 throw new ArgumentNullException(nameof(poco));
1699
1700             var pd = PocoData.ForType(poco.GetType(), _defaultMapper);
1701
1702             return ExecuteInsert(tableName, pd == null ? null : pd.TableInfo.PrimaryKey, pd != null && pd.TableInfo.AutoIncrement, poco);
1703         }
1704
1705         /// <inheritdoc />
1706         public object Insert(string tableName, string primaryKeyName, object poco)
1707         {
1708             if (string.IsNullOrEmpty(tableName))
1709                 throw new ArgumentNullException(nameof(tableName));
1710
1711             if (string.IsNullOrEmpty(primaryKeyName))
1712                 throw new ArgumentNullException(nameof(primaryKeyName));
1713
1714             if (poco == null)
1715                 throw new ArgumentNullException(nameof(poco));
1716
1717             var t = poco.GetType();
1718             var pd = PocoData.ForType(poco.GetType(), _defaultMapper);
1719             var autoIncrement = pd == null || pd.TableInfo.AutoIncrement || t.Name.Contains("AnonymousType") &&
1720                                 !t.GetProperties().Any(p => p.Name.Equals(primaryKeyName, StringComparison.OrdinalIgnoreCase));
1721
1722             return ExecuteInsert(tableName, primaryKeyName, autoIncrement, poco);
1723         }
1724
1725         /// <inheritdoc />
1726         public object Insert(string tableName, string primaryKeyName, bool autoIncrement, object poco)
1727         {
1728             if (string.IsNullOrEmpty(tableName))
1729                 throw new ArgumentNullException(nameof(tableName));
1730
1731             if (string.IsNullOrEmpty(primaryKeyName))
1732                 throw new ArgumentNullException(nameof(primaryKeyName));
1733
1734             if (poco == null)
1735                 throw new ArgumentNullException(nameof(poco));
1736
1737             return ExecuteInsert(tableName, primaryKeyName, autoIncrement, poco);
1738         }
1739
1740         /// <inheritdoc />
1741         public object Insert(object poco)
1742         {
1743             if (poco == null)
1744                 throw new ArgumentNullException(nameof(poco));
1745
1746             var pd = PocoData.ForType(poco.GetType(), _defaultMapper);
1747             return ExecuteInsert(pd.TableInfo.TableName, pd.TableInfo.PrimaryKey, pd.TableInfo.AutoIncrement, poco);
1748         }
1749
1750         private object ExecuteInsert(string tableName, string primaryKeyName, bool autoIncrement, object poco)
1751         {
1752             try
1753             {
1754                 OpenSharedConnection();
1755                 try
1756                 {
1757                     using (var cmd = CreateCommand(_sharedConnection, string.Empty))
1758                     {
1759                         var pd = PocoData.ForObject(poco, primaryKeyName, _defaultMapper);
1760                         var names = new List<string>();
1761                         var values = new List<string>();
1762
1763                         PrepareExecuteInsert(tableName, primaryKeyName, autoIncrement, poco, pd, names, values, cmd);
1764
1765                         if (!autoIncrement)
1766                         {
1767                             ExecuteNonQueryHelper(cmd);
1768
1769                             if (primaryKeyName != null && pd.Columns.TryGetValue(primaryKeyName, out var pkColumn))
1770                                 return pkColumn.GetValue(poco);
1771                             else
1772                                 return null;
1773                         }
1774
1775                         var id = _provider.ExecuteInsert(this, cmd, primaryKeyName);
1776
1777                         // Assign the ID back to the primary key property
1778                         if (primaryKeyName != null && !poco.GetType().Name.Contains("AnonymousType"))
1779                             if (pd.Columns.TryGetValue(primaryKeyName, out var pc))
1780                                 pc.SetValue(poco, pc.ChangeType(id));
1781
1782                         return id;
1783                     }
1784                 }
1785                 finally
1786                 {
1787                     CloseSharedConnection();
1788                 }
1789             }
1790             catch (Exception x)
1791             {
1792                 if (OnException(x))
1793                     throw;
1794                 return null;
1795             }
1796         }
1797
1798         private void PrepareExecuteInsert(string tableName, string primaryKeyName, bool autoIncrement, object poco, PocoData pd, List<string> names,
1799                                           List<string> values, IDbCommand cmd)
1800         {
1801             var index = 0;
1802             foreach (var i in pd.Columns)
1803             {
1804                 // Don't insert result columns
1805                 if (i.Value.ResultColumn)
1806                     continue;
1807
1808                 // Don't insert the primary key (except under oracle where we need bring in the next sequence value)
1809                 if (autoIncrement && primaryKeyName != null && string.Compare(i.Key, primaryKeyName, StringComparison.OrdinalIgnoreCase) == 0)
1810                 {
1811                     // Setup auto increment expression
1812                     var autoIncExpression = _provider.GetAutoIncrementExpression(pd.TableInfo);
1813                     if (autoIncExpression != null)
1814                     {
1815                         names.Add(i.Key);
1816                         values.Add(autoIncExpression);
1817                     }
1818
1819                     continue;
1820                 }
1821
1822                 names.Add(_provider.EscapeSqlIdentifier(i.Key));
1823                 values.Add(string.Format(i.Value.InsertTemplate ?? "{0}{1}", _paramPrefix, index++));
1824                 AddParam(cmd, i.Value.GetValue(poco), i.Value.PropertyInfo);
1825             }
1826
1827             var outputClause = string.Empty;
1828             if (autoIncrement)
1829                 outputClause = _provider.GetInsertOutputClause(primaryKeyName);
1830
1831             cmd.CommandText =
1832                 $"INSERT INTO {_provider.EscapeTableName(tableName)} ({string.Join(",", names.ToArray())}){outputClause} VALUES ({string.Join(",", values.ToArray())})";
1833         }
1834
1835 #if ASYNC
1836
1837         public Task<object> InsertAsync(string tableName, object poco)
1838             => InsertAsync(CancellationToken.None, tableName, poco);
1839
1840         public Task<object> InsertAsync(CancellationToken cancellationToken, string tableName, object poco)
1841         {
1842             if (tableName == null)
1843                 throw new ArgumentNullException(nameof(tableName));
1844             if (poco == null)
1845                 throw new ArgumentNullException(nameof(poco));
1846
1847             var pd = PocoData.ForType(poco.GetType(), _defaultMapper);
1848             return ExecuteInsertAsync(cancellationToken, tableName, pd?.TableInfo.PrimaryKey, pd != null && pd.TableInfo.AutoIncrement, poco);
1849         }
1850
1851         public Task<object> InsertAsync(string tableName, string primaryKeyName, object poco)
1852             => InsertAsync(CancellationToken.None, tableName, primaryKeyName, poco);
1853
1854         public Task<object> InsertAsync(CancellationToken cancellationToken, string tableName, string primaryKeyName, object poco)
1855         {
1856             if (tableName == null)
1857                 throw new ArgumentNullException(nameof(tableName));
1858             if (primaryKeyName == null)
1859                 throw new ArgumentNullException(nameof(primaryKeyName));
1860             if (poco == null)
1861                 throw new ArgumentNullException(nameof(poco));
1862
1863             var t = poco.GetType();
1864             var pd = PocoData.ForType(poco.GetType(), _defaultMapper);
1865             var autoIncrement = pd == null || pd.TableInfo.AutoIncrement || t.Name.Contains("AnonymousType") &&
1866                                 !t.GetProperties().Any(p => p.Name.Equals(primaryKeyName, StringComparison.OrdinalIgnoreCase));
1867
1868             return ExecuteInsertAsync(cancellationToken, tableName, primaryKeyName, autoIncrement, poco);
1869         }
1870
1871         public Task<object> InsertAsync(string tableName, string primaryKeyName, bool autoIncrement, object poco)
1872             => InsertAsync(CancellationToken.None, tableName, primaryKeyName, autoIncrement, poco);
1873
1874         public Task<object> InsertAsync(CancellationToken cancellationToken, string tableName, string primaryKeyName, bool autoIncrement, object poco)
1875         {
1876             if (tableName == null)
1877                 throw new ArgumentNullException(nameof(tableName));
1878             if (primaryKeyName == null)
1879                 throw new ArgumentNullException(nameof(primaryKeyName));
1880             if (poco == null)
1881                 throw new ArgumentNullException(nameof(poco));
1882
1883             return ExecuteInsertAsync(cancellationToken, tableName, primaryKeyName, autoIncrement, poco);
1884         }
1885
1886         public Task<object> InsertAsync(object poco)
1887             => InsertAsync(CancellationToken.None, poco);
1888
1889         public Task<object> InsertAsync(CancellationToken cancellationToken, object poco)
1890         {
1891             if (poco == null)
1892                 throw new ArgumentNullException(nameof(poco));
1893
1894             var pd = PocoData.ForType(poco.GetType(), _defaultMapper);
1895             return ExecuteInsertAsync(cancellationToken, pd.TableInfo.TableName, pd.TableInfo.PrimaryKey, pd.TableInfo.AutoIncrement, poco);
1896         }
1897
1898         private async Task<object> ExecuteInsertAsync(CancellationToken cancellationToken, string tableName, string primaryKeyName, bool autoIncrement,
1899                                                       object poco)
1900         {
1901             try
1902             {
1903                 await OpenSharedConnectionAsync(cancellationToken).ConfigureAwait(false);
1904                 try
1905                 {
1906                     using (var cmd = CreateCommand(_sharedConnection, string.Empty))
1907                     {
1908                         var pd = PocoData.ForObject(poco, primaryKeyName, _defaultMapper);
1909                         var names = new List<string>();
1910                         var values = new List<string>();
1911
1912                         PrepareExecuteInsert(tableName, primaryKeyName, autoIncrement, poco, pd, names, values, cmd);
1913
1914                         if (!autoIncrement)
1915                         {
1916                             await ExecuteNonQueryHelperAsync(cancellationToken, cmd).ConfigureAwait(false);
1917
1918                             if (primaryKeyName != null && pd.Columns.TryGetValue(primaryKeyName, out var pkColumn))
1919                                 return pkColumn.GetValue(poco);
1920                             else
1921                                 return null;
1922                         }
1923
1924                         var id = await _provider.ExecuteInsertAsync(cancellationToken, this, cmd, primaryKeyName).ConfigureAwait(false);
1925
1926                         // Assign the ID back to the primary key property
1927                         if (primaryKeyName != null && !poco.GetType().Name.Contains("AnonymousType"))
1928                             if (pd.Columns.TryGetValue(primaryKeyName, out var pc))
1929                                 pc.SetValue(poco, pc.ChangeType(id));
1930
1931                         return id;
1932                     }
1933                 }
1934                 finally
1935                 {
1936                     CloseSharedConnection();
1937                 }
1938             }
1939             catch (Exception x)
1940             {
1941                 if (OnException(x))
1942                     throw;
1943                 return null;
1944             }
1945         }
1946
1947 #endif
1948
1949         #endregion
1950
1951         #region operation: Update
1952
1953         /// <inheritdoc />
1954         public int Update(string tableName, string primaryKeyName, object poco, object primaryKeyValue)
1955             => Update(tableName, primaryKeyName, poco, primaryKeyValue, null);
1956
1957         /// <inheritdoc />
1958         public int Update(string tableName, string primaryKeyName, object poco, object primaryKeyValue, IEnumerable<string> columns)
1959         {
1960             if (string.IsNullOrEmpty(tableName))
1961                 throw new ArgumentNullException(nameof(tableName));
1962
1963             if (string.IsNullOrEmpty(primaryKeyName))
1964                 throw new ArgumentNullException(nameof(primaryKeyName));
1965
1966             if (poco == null)
1967                 throw new ArgumentNullException(nameof(poco));
1968
1969             if (columns?.Any() == false)
1970                 return 0;
1971
1972             return ExecuteUpdate(tableName, primaryKeyName, poco, primaryKeyValue, columns);
1973         }
1974
1975         /// <inheritdoc />
1976         public int Update(string tableName, string primaryKeyName, object poco)
1977             => Update(tableName, primaryKeyName, poco, null);
1978
1979         /// <inheritdoc />
1980         public int Update(string tableName, string primaryKeyName, object poco, IEnumerable<string> columns)
1981             => Update(tableName, primaryKeyName, poco, null, columns);
1982         
1983         /// <inheritdoc />
1984         public int Update(object poco, IEnumerable<string> columns)
1985             => Update(poco, null, columns);
1986
1987         /// <inheritdoc />
1988         public int Update(object poco)
1989             => Update(poco, null, null);
1990
1991         /// <inheritdoc />
1992         public int Update(object poco, object primaryKeyValue)
1993             => Update(poco, primaryKeyValue, null);
1994
1995         /// <inheritdoc />
1996         public int Update(object poco, object primaryKeyValue, IEnumerable<string> columns)
1997         {
1998             if (poco == null)
1999                 throw new ArgumentNullException(nameof(poco));
2000
2001             if (columns?.Any() == false)
2002                 return 0;
2003
2004             var pd = PocoData.ForType(poco.GetType(), _defaultMapper);
2005             return ExecuteUpdate(pd.TableInfo.TableName, pd.TableInfo.PrimaryKey, poco, primaryKeyValue, columns);
2006         }
2007
2008         /// <inheritdoc />
2009         public int Update<T>(string sql, params object[] args)
2010         {
2011             if (string.IsNullOrEmpty(sql))
2012                 throw new ArgumentNullException(nameof(sql));
2013
2014             var pd = PocoData.ForType(typeof(T), _defaultMapper);
2015             return Execute($"UPDATE {_provider.EscapeTableName(pd.TableInfo.TableName)} {sql}", args);
2016         }
2017
2018         /// <inheritdoc />
2019         public int Update<T>(Sql sql)
2020         {
2021             if (sql == null)
2022                 throw new ArgumentNullException(nameof(sql));
2023
2024             var pd = PocoData.ForType(typeof(T), _defaultMapper);
2025             return Execute(new Sql($"UPDATE {_provider.EscapeTableName(pd.TableInfo.TableName)}").Append(sql));
2026         }
2027
2028         private int ExecuteUpdate(string tableName, string primaryKeyName, object poco, object primaryKeyValue, IEnumerable<string> columns)
2029         {
2030             try
2031             {
2032                 OpenSharedConnection();
2033                 try
2034                 {
2035                     using (var cmd = CreateCommand(_sharedConnection, string.Empty))
2036                     {
2037                         PreExecuteUpdate(tableName, primaryKeyName, poco, primaryKeyValue, columns, cmd);
2038                         return ExecuteNonQueryHelper(cmd);
2039                     }
2040                 }
2041                 finally
2042                 {
2043                     CloseSharedConnection();
2044                 }
2045             }
2046             catch (Exception x)
2047             {
2048                 if (OnException(x))
2049                     throw;
2050                 return -1;
2051             }
2052         }
2053
2054         private void PreExecuteUpdate(string tableName, string primaryKeyName, object poco, object primaryKeyValue, IEnumerable<string> columns, IDbCommand cmd)
2055         {
2056             var sb = new StringBuilder();
2057             var index = 0;
2058             var pd = PocoData.ForObject(poco, primaryKeyName, _defaultMapper);
2059             if (columns == null)
2060             {
2061                 foreach (var i in pd.Columns)
2062                 {
2063                     // Don't update the primary key, but grab the value if we don't have it
2064                     if (string.Compare(i.Key, primaryKeyName, StringComparison.OrdinalIgnoreCase) == 0)
2065                     {
2066                         if (primaryKeyValue == null)
2067                             primaryKeyValue = i.Value.GetValue(poco);
2068                         continue;
2069                     }
2070
2071                     // Dont update result only columns
2072                     if (i.Value.ResultColumn)
2073                         continue;
2074
2075                     // Build the sql
2076                     if (index > 0)
2077                         sb.Append(", ");
2078                     sb.AppendFormat(i.Value.UpdateTemplate ?? "{0} = {1}{2}", _provider.EscapeSqlIdentifier(i.Key), _paramPrefix, index++);
2079
2080                     // Store the parameter in the command
2081                     AddParam(cmd, i.Value.GetValue(poco), i.Value.PropertyInfo);
2082                 }
2083             }
2084             else
2085             {
2086                 foreach (var colname in columns)
2087                 {
2088                     var pc = pd.Columns[colname];
2089
2090                     // Build the sql
2091                     if (index > 0)
2092                         sb.Append(", ");
2093                     sb.AppendFormat(pc.UpdateTemplate ?? "{0} = {1}{2}", _provider.EscapeSqlIdentifier(colname), _paramPrefix, index++);
2094
2095                     // Store the parameter in the command
2096                     AddParam(cmd, pc.GetValue(poco), pc.PropertyInfo);
2097                 }
2098
2099                 // Grab primary key value
2100                 if (primaryKeyValue == null)
2101                 {
2102                     var pc = pd.Columns[primaryKeyName];
2103                     primaryKeyValue = pc.GetValue(poco);
2104                 }
2105             }
2106
2107             // Find the property info for the primary key
2108             PropertyInfo pkpi = null;
2109             if (primaryKeyName != null)
2110             {
2111                 PocoColumn col;
2112                 pkpi = pd.Columns.TryGetValue(primaryKeyName, out col) ? col.PropertyInfo : new { Id = primaryKeyValue }.GetType().GetProperties()[0];
2113             }
2114
2115             cmd.CommandText =
2116                 $"UPDATE {_provider.EscapeTableName(tableName)} SET {sb} WHERE {_provider.EscapeSqlIdentifier(primaryKeyName)} = {_paramPrefix}{index++}";
2117             AddParam(cmd, primaryKeyValue, pkpi);
2118         }
2119
2120 #if ASYNC
2121
2122         /// <inheritdoc />
2123         public Task<int> UpdateAsync(string tableName, string primaryKeyName, object poco, object primaryKeyValue)
2124             => UpdateAsync(CancellationToken.None, tableName, primaryKeyName, poco, primaryKeyValue);
2125
2126         /// <inheritdoc />
2127         public Task<int> UpdateAsync(CancellationToken cancellationToken, string tableName, string primaryKeyName, object poco, object primaryKeyValue)
2128             => UpdateAsync(cancellationToken, tableName, primaryKeyName, poco, primaryKeyValue, null);
2129
2130         /// <inheritdoc />
2131         public Task<int> UpdateAsync(string tableName, string primaryKeyName, object poco, object primaryKeyValue, IEnumerable<string> columns)
2132             => UpdateAsync(CancellationToken.None, tableName, primaryKeyName, poco, primaryKeyValue, columns);
2133
2134         /// <inheritdoc />
2135         public Task<int> UpdateAsync(CancellationToken cancellationToken, string tableName, string primaryKeyName, object poco, object primaryKeyValue,
2136                                      IEnumerable<string> columns)
2137         {
2138             if (string.IsNullOrEmpty(tableName))
2139                 throw new ArgumentNullException(nameof(tableName));
2140
2141             if (string.IsNullOrEmpty(primaryKeyName))
2142                 throw new ArgumentNullException(nameof(primaryKeyName));
2143
2144             if (poco == null)
2145                 throw new ArgumentNullException(nameof(poco));
2146
2147             if (columns?.Any() == false)
2148                 return Task.FromResult(0);
2149
2150             return ExecuteUpdateAsync(cancellationToken, tableName, primaryKeyName, poco, primaryKeyValue, columns);
2151         }
2152
2153         /// <inheritdoc />
2154         public Task<int> UpdateAsync(string tableName, string primaryKeyName, object poco)
2155             => UpdateAsync(CancellationToken.None, tableName, primaryKeyName, poco);
2156
2157         /// <inheritdoc />
2158         public Task<int> UpdateAsync(CancellationToken cancellationToken, string tableName, string primaryKeyName, object poco)
2159             => UpdateAsync(cancellationToken, tableName, primaryKeyName, poco, null);
2160
2161         /// <inheritdoc />
2162         public Task<int> UpdateAsync(string tableName, string primaryKeyName, object poco, IEnumerable<string> columns)
2163             => UpdateAsync(CancellationToken.None, tableName, primaryKeyName, poco, columns);
2164
2165         /// <inheritdoc />
2166         public Task<int> UpdateAsync(CancellationToken cancellationToken, string tableName, string primaryKeyName, object poco, IEnumerable<string> columns)
2167             => UpdateAsync(cancellationToken, tableName, primaryKeyName, poco, null, columns);
2168
2169         /// <inheritdoc />
2170         public Task<int> UpdateAsync(object poco, IEnumerable<string> columns)
2171             => UpdateAsync(CancellationToken.None, poco, columns);
2172
2173         /// <inheritdoc />
2174         public Task<int> UpdateAsync(CancellationToken cancellationToken, object poco, IEnumerable<string> columns)
2175             => UpdateAsync(cancellationToken, poco, null, columns);
2176
2177         /// <inheritdoc />
2178         public Task<int> UpdateAsync(object poco)
2179             => UpdateAsync(CancellationToken.None, poco);
2180
2181         /// <inheritdoc />
2182         public Task<int> UpdateAsync(CancellationToken cancellationToken, object poco)
2183             => UpdateAsync(cancellationToken, poco, null, null);
2184
2185         /// <inheritdoc />
2186         public Task<int> UpdateAsync(object poco, object primaryKeyValue)
2187             => UpdateAsync(CancellationToken.None, poco, primaryKeyValue);
2188
2189         /// <inheritdoc />
2190         public Task<int> UpdateAsync(CancellationToken cancellationToken, object poco, object primaryKeyValue)
2191             => UpdateAsync(cancellationToken, poco, primaryKeyValue, null);
2192
2193         /// <inheritdoc />
2194         public Task<int> UpdateAsync(object poco, object primaryKeyValue, IEnumerable<string> columns)
2195             => UpdateAsync(CancellationToken.None, poco, primaryKeyValue, columns);
2196
2197         /// <inheritdoc />
2198         public Task<int> UpdateAsync(CancellationToken cancellationToken, object poco, object primaryKeyValue, IEnumerable<string> columns)
2199         {
2200             if (poco == null)
2201                 throw new ArgumentNullException(nameof(poco));
2202
2203             if (columns?.Any() == false)
2204                 return Task.FromResult(0);
2205
2206             var pd = PocoData.ForType(poco.GetType(), _defaultMapper);
2207             return ExecuteUpdateAsync(cancellationToken, pd.TableInfo.TableName, pd.TableInfo.PrimaryKey, poco, primaryKeyValue, columns);
2208         }
2209
2210         /// <inheritdoc />
2211         public Task<int> UpdateAsync<T>(string sql, params object[] args)
2212             => UpdateAsync<T>(CancellationToken.None, sql, args);
2213
2214         /// <inheritdoc />
2215         public Task<int> UpdateAsync<T>(CancellationToken cancellationToken, string sql, params object[] args)
2216         {
2217             if (string.IsNullOrEmpty(sql))
2218                 throw new ArgumentNullException(nameof(sql));
2219
2220             var pd = PocoData.ForType(typeof(T), _defaultMapper);
2221             return ExecuteAsync(cancellationToken, $"UPDATE {_provider.EscapeTableName(pd.TableInfo.TableName)} {sql}", args);
2222         }
2223
2224         /// <inheritdoc />
2225         public Task<int> UpdateAsync<T>(Sql sql)
2226             => UpdateAsync<T>(CancellationToken.None, sql);
2227
2228         /// <inheritdoc />
2229         public Task<int> UpdateAsync<T>(CancellationToken cancellationToken, Sql sql)
2230         {
2231             if (sql == null)
2232                 throw new ArgumentNullException(nameof(sql));
2233
2234             var pd = PocoData.ForType(typeof(T), _defaultMapper);
2235             return ExecuteAsync(cancellationToken, new Sql($"UPDATE {_provider.EscapeTableName(pd.TableInfo.TableName)}").Append(sql));
2236         }
2237
2238         private async Task<int> ExecuteUpdateAsync(CancellationToken cancellationToken, string tableName, string primaryKeyName, object poco,
2239                                                    object primaryKeyValue, IEnumerable<string> columns)
2240         {
2241             try
2242             {
2243                 await OpenSharedConnectionAsync(cancellationToken).ConfigureAwait(false);
2244                 try
2245                 {
2246                     using (var cmd = CreateCommand(_sharedConnection, string.Empty))
2247                     {
2248                         PreExecuteUpdate(tableName, primaryKeyName, poco, primaryKeyValue, columns, cmd);
2249                         return await ExecuteNonQueryHelperAsync(cancellationToken, cmd).ConfigureAwait(false);
2250                     }
2251                 }
2252                 finally
2253                 {
2254                     CloseSharedConnection();
2255                 }
2256             }
2257             catch (Exception x)
2258             {
2259                 if (OnException(x))
2260                     throw;
2261                 return -1;
2262             }
2263         }
2264
2265 #endif
2266
2267 #endregion
2268
2269 #region operation: Delete
2270
2271         /// <inheritdoc />
2272         public int Delete(string tableName, string primaryKeyName, object poco)
2273             => Delete(tableName, primaryKeyName, poco, null);
2274
2275         /// <inheritdoc />
2276         public int Delete(string tableName, string primaryKeyName, object poco, object primaryKeyValue)
2277         {
2278             if (primaryKeyValue == null)
2279             {
2280                 var pd = PocoData.ForObject(poco, primaryKeyName, _defaultMapper);
2281                 if (pd.Columns.TryGetValue(primaryKeyName, out var pc))
2282                     primaryKeyValue = pc.GetValue(poco);
2283             }
2284
2285             var sql = $"DELETE FROM {_provider.EscapeTableName(tableName)} WHERE {_provider.EscapeSqlIdentifier(primaryKeyName)}=@0";
2286             return Execute(sql, primaryKeyValue);
2287         }
2288
2289         /// <inheritdoc />
2290         public int Delete(object poco)
2291         {
2292             var pd = PocoData.ForType(poco.GetType(), _defaultMapper);
2293             return Delete(pd.TableInfo.TableName, pd.TableInfo.PrimaryKey, poco);
2294         }
2295
2296         /// <inheritdoc />
2297         public int Delete<T>(object pocoOrPrimaryKey)
2298         {
2299             if (pocoOrPrimaryKey.GetType() == typeof(T))
2300                 return Delete(pocoOrPrimaryKey);
2301
2302             var pd = PocoData.ForType(typeof(T), _defaultMapper);
2303
2304             if (pocoOrPrimaryKey.GetType().Name.Contains("AnonymousType"))
2305             {
2306                 var pi = pocoOrPrimaryKey.GetType().GetProperty(pd.TableInfo.PrimaryKey);
2307
2308                 if (pi == null)
2309                     throw new InvalidOperationException($"Anonymous type does not contain an id for PK column `{pd.TableInfo.PrimaryKey}`.");
2310
2311                 pocoOrPrimaryKey = pi.GetValue(pocoOrPrimaryKey, new object[0]);
2312             }
2313
2314             return Delete(pd.TableInfo.TableName, pd.TableInfo.PrimaryKey, null, pocoOrPrimaryKey);
2315         }
2316
2317         /// <inheritdoc />
2318         public int Delete<T>(string sql, params object[] args)
2319         {
2320             var pd = PocoData.ForType(typeof(T), _defaultMapper);
2321             return Execute($"DELETE FROM {_provider.EscapeTableName(pd.TableInfo.TableName)} {sql}", args);
2322         }
2323
2324         /// <inheritdoc />
2325         public int Delete<T>(Sql sql)
2326         {
2327             var pd = PocoData.ForType(typeof(T), _defaultMapper);
2328             return Execute(new Sql($"DELETE FROM {_provider.EscapeTableName(pd.TableInfo.TableName)}").Append(sql));
2329         }
2330
2331 #if ASYNC
2332
2333         /// <inheritdoc />
2334         public Task<int> DeleteAsync(string tableName, string primaryKeyName, object poco)
2335             => DeleteAsync(CancellationToken.None, tableName, primaryKeyName, poco);
2336
2337         /// <inheritdoc />
2338         public Task<int> DeleteAsync(CancellationToken cancellationToken, string tableName, string primaryKeyName, object poco)
2339             => DeleteAsync(cancellationToken, tableName, primaryKeyName, poco, null);
2340
2341         /// <inheritdoc />
2342         public Task<int> DeleteAsync(string tableName, string primaryKeyName, object poco, object primaryKeyValue)
2343             => DeleteAsync(CancellationToken.None, tableName, primaryKeyName, poco, primaryKeyValue);
2344
2345         /// <inheritdoc />
2346         public Task<int> DeleteAsync(CancellationToken cancellationToken, string tableName, string primaryKeyName, object poco, object primaryKeyValue)
2347         {
2348             if (primaryKeyValue == null)
2349             {
2350                 var pd = PocoData.ForObject(poco, primaryKeyName, _defaultMapper);
2351                 if (pd.Columns.TryGetValue(primaryKeyName, out var pc))
2352                     primaryKeyValue = pc.GetValue(poco);
2353             }
2354
2355             var sql = $"DELETE FROM {_provider.EscapeTableName(tableName)} WHERE {_provider.EscapeSqlIdentifier(primaryKeyName)}=@0";
2356             return ExecuteAsync(cancellationToken, sql, primaryKeyValue);
2357         }
2358
2359         /// <inheritdoc />
2360         public Task<int> DeleteAsync(object poco)
2361             => DeleteAsync(CancellationToken.None, poco);
2362
2363         /// <inheritdoc />
2364         public Task<int> DeleteAsync(CancellationToken cancellationToken, object poco)
2365         {
2366             var pd = PocoData.ForType(poco.GetType(), _defaultMapper);
2367             return DeleteAsync(cancellationToken, pd.TableInfo.TableName, pd.TableInfo.PrimaryKey, poco);
2368         }
2369
2370         /// <inheritdoc />
2371         public Task<int> DeleteAsync<T>(object pocoOrPrimaryKey)
2372             => DeleteAsync<T>(CancellationToken.None, pocoOrPrimaryKey);
2373
2374         /// <inheritdoc />
2375         public Task<int> DeleteAsync<T>(CancellationToken cancellationToken, object pocoOrPrimaryKey)
2376         {
2377             if (pocoOrPrimaryKey.GetType() == typeof(T))
2378                 return DeleteAsync(cancellationToken, pocoOrPrimaryKey);
2379
2380             var pd = PocoData.ForType(typeof(T), _defaultMapper);
2381
2382             if (pocoOrPrimaryKey.GetType().Name.Contains("AnonymousType"))
2383             {
2384                 var pi = pocoOrPrimaryKey.GetType().GetProperty(pd.TableInfo.PrimaryKey);
2385
2386                 if (pi == null)
2387                     throw new InvalidOperationException($"Anonymous type does not contain an id for PK column `{pd.TableInfo.PrimaryKey}`.");
2388
2389                 pocoOrPrimaryKey = pi.GetValue(pocoOrPrimaryKey, new object[0]);
2390             }
2391
2392             return DeleteAsync(cancellationToken, pd.TableInfo.TableName, pd.TableInfo.PrimaryKey, null, pocoOrPrimaryKey);
2393         }
2394
2395         /// <inheritdoc />
2396         public Task<int> DeleteAsync<T>(string sql, params object[] args)
2397             => DeleteAsync<T>(CancellationToken.None, sql, args);
2398
2399         /// <inheritdoc />
2400         public Task<int> DeleteAsync<T>(CancellationToken cancellationToken, string sql, params object[] args)
2401         {
2402             var pd = PocoData.ForType(typeof(T), _defaultMapper);
2403             return ExecuteAsync(cancellationToken, $"DELETE FROM {_provider.EscapeTableName(pd.TableInfo.TableName)} {sql}", args);
2404         }
2405
2406         /// <inheritdoc />
2407         public Task<int> DeleteAsync<T>(Sql sql)
2408             => DeleteAsync<T>(CancellationToken.None, sql);
2409
2410         /// <inheritdoc />
2411         public Task<int> DeleteAsync<T>(CancellationToken cancellationToken, Sql sql)
2412         {
2413             var pd = PocoData.ForType(typeof(T), _defaultMapper);
2414             return ExecuteAsync(cancellationToken, new Sql($"DELETE FROM {_provider.EscapeTableName(pd.TableInfo.TableName)}").Append(sql));
2415         }
2416
2417 #endif
2418
2419 #endregion
2420
2421 #region operation: IsNew
2422
2423         /// <inheritdoc />
2424         public bool IsNew(string primaryKeyName, object poco)
2425         {
2426             if (poco == null)
2427                 throw new ArgumentNullException(nameof(poco));
2428
2429             if (string.IsNullOrEmpty(primaryKeyName))
2430                 throw new ArgumentException("primaryKeyName");
2431
2432             return IsNew(primaryKeyName, PocoData.ForObject(poco, primaryKeyName, _defaultMapper), poco);
2433         }
2434
2435         /// <inheritdoc />
2436         public bool IsNew(object poco)
2437         {
2438             if (poco == null)
2439                 throw new ArgumentNullException(nameof(poco));
2440
2441             var pd = PocoData.ForType(poco.GetType(), _defaultMapper);
2442             return IsNew(pd.TableInfo.PrimaryKey, pd, poco);
2443         }
2444
2445         protected virtual bool IsNew(string primaryKeyName, PocoData pd, object poco)
2446         {
2447             if (string.IsNullOrEmpty(primaryKeyName) || poco is ExpandoObject)
2448                 throw new InvalidOperationException("IsNew() and Save() are only supported on tables with identity (inc auto-increment) primary key columns");
2449
2450             object pk;
2451             PocoColumn pc;
2452             PropertyInfo pi;
2453             if (pd.Columns.TryGetValue(primaryKeyName, out pc))
2454             {
2455                 pk = pc.GetValue(poco);
2456                 pi = pc.PropertyInfo;
2457             }
2458             else
2459             {
2460                 pi = poco.GetType().GetProperty(primaryKeyName);
2461                 if (pi == null)
2462                     throw new ArgumentException(string.Format("The object doesn't have a property matching the primary key column name '{0}'", primaryKeyName));
2463                 pk = pi.GetValue(poco, null);
2464             }
2465
2466             var type = pk != null ? pk.GetType() : pi.PropertyType;
2467
2468             if (type == typeof(string))
2469                 return string.IsNullOrEmpty((string)pk);
2470             if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>) || !type.IsValueType)
2471                 return pk == null;
2472             if (!pi.PropertyType.IsValueType)
2473                 return pk == null;
2474             if (type == typeof(long))
2475                 return (long) pk == default(long);
2476             if (type == typeof(int))
2477                 return (int) pk == default(int);
2478             if (type == typeof(Guid))
2479                 return (Guid) pk == default(Guid);
2480             if (type == typeof(ulong))
2481                 return (ulong) pk == default(ulong);
2482             if (type == typeof(uint))
2483                 return (uint) pk == default(uint);
2484             if (type == typeof(short))
2485                 return (short) pk == default(short);
2486             if (type == typeof(ushort))
2487                 return (ushort) pk == default(ushort);
2488             if (type == typeof(decimal))
2489                 return (decimal) pk == default(decimal);
2490
2491             // Create a default instance and compare
2492             return pk == Activator.CreateInstance(pk.GetType());
2493         }
2494
2495 #endregion
2496
2497 #region operation: Save
2498
2499         /// <inheritdoc />
2500         public void Save(string tableName, string primaryKeyName, object poco)
2501         {
2502             if (IsNew(primaryKeyName, poco))
2503                 Insert(tableName, primaryKeyName, true, poco);
2504             else
2505                 Update(tableName, primaryKeyName, poco);
2506         }
2507
2508         /// <inheritdoc />
2509         public void Save(object poco)
2510         {
2511             var pd = PocoData.ForType(poco.GetType(), _defaultMapper);
2512             Save(pd.TableInfo.TableName, pd.TableInfo.PrimaryKey, poco);
2513         }
2514
2515 #if ASYNC
2516
2517         /// <inheritdoc />
2518         public Task SaveAsync(string tableName, string primaryKeyName, object poco)
2519             => SaveAsync(CancellationToken.None, tableName, primaryKeyName, poco);
2520
2521         /// <inheritdoc />
2522         public Task SaveAsync(CancellationToken cancellationToken, string tableName, string primaryKeyName, object poco)
2523         {
2524             if (IsNew(primaryKeyName, poco))
2525                 return InsertAsync(cancellationToken, tableName, primaryKeyName, true, poco);
2526
2527             return UpdateAsync(cancellationToken, tableName, primaryKeyName, poco);
2528         }
2529
2530         /// <inheritdoc />
2531         public Task SaveAsync(object poco)
2532             => SaveAsync(CancellationToken.None, poco);
2533
2534         /// <inheritdoc />
2535         public Task SaveAsync(CancellationToken cancellationToken, object poco)
2536         {
2537             var pd = PocoData.ForType(poco.GetType(), _defaultMapper);
2538             return SaveAsync(cancellationToken, pd.TableInfo.TableName, pd.TableInfo.PrimaryKey, poco);
2539         }
2540
2541 #endif
2542
2543 #endregion
2544
2545 #region operation: Multi-Poco Query/Fetch
2546
2547         /// <inheritdoc />
2548         public List<TRet> Fetch<T1, T2, TRet>(Func<T1, T2, TRet> cb, string sql, params object[] args)
2549             => Query(cb, sql, args).ToList();
2550
2551         /// <inheritdoc />
2552         public List<TRet> Fetch<T1, T2, T3, TRet>(Func<T1, T2, T3, TRet> cb, string sql, params object[] args)
2553             => Query(cb, sql, args).ToList();
2554
2555         /// <inheritdoc />
2556         public List<TRet> Fetch<T1, T2, T3, T4, TRet>(Func<T1, T2, T3, T4, TRet> cb, string sql, params object[] args)
2557             => Query(cb, sql, args).ToList();
2558
2559         /// <inheritdoc />
2560         public List<TRet> Fetch<T1, T2, T3, T4, T5, TRet>(Func<T1, T2, T3, T4, T5, TRet> cb, string sql, params object[] args)
2561             => Query(cb, sql, args).ToList();
2562
2563         /// <inheritdoc />
2564         public IEnumerable<TRet> Query<T1, T2, TRet>(Func<T1, T2, TRet> cb, string sql, params object[] args)
2565             => Query<TRet>(new[] { typeof(T1), typeof(T2) }, cb, sql, args);
2566
2567         /// <inheritdoc />
2568         public IEnumerable<TRet> Query<T1, T2, T3, TRet>(Func<T1, T2, T3, TRet> cb, string sql, params object[] args)
2569             => Query<TRet>(new[] { typeof(T1), typeof(T2), typeof(T3) }, cb, sql, args);
2570
2571         /// <inheritdoc />
2572         public IEnumerable<TRet> Query<T1, T2, T3, T4, TRet>(Func<T1, T2, T3, T4, TRet> cb, string sql, params object[] args)
2573             => Query<TRet>(new[] { typeof(T1), typeof(T2), typeof(T3), typeof(T4) }, cb, sql, args);
2574
2575         public IEnumerable<TRet> Query<T1, T2, T3, T4, T5, TRet>(Func<T1, T2, T3, T4, T5, TRet> cb, string sql, params object[] args)
2576             => Query<TRet>(new[] { typeof(T1), typeof(T2), typeof(T3), typeof(T4), typeof(T5) }, cb, sql, args);
2577
2578         /// <inheritdoc />
2579         public List<TRet> Fetch<T1, T2, TRet>(Func<T1, T2, TRet> cb, Sql sql)
2580             => Query(cb, sql.SQL, sql.Arguments).ToList();
2581
2582         /// <inheritdoc />
2583         public List<TRet> Fetch<T1, T2, T3, TRet>(Func<T1, T2, T3, TRet> cb, Sql sql)
2584             => Query(cb, sql.SQL, sql.Arguments).ToList();
2585
2586         /// <inheritdoc />
2587         public List<TRet> Fetch<T1, T2, T3, T4, TRet>(Func<T1, T2, T3, T4, TRet> cb, Sql sql)
2588             => Query(cb, sql.SQL, sql.Arguments).ToList();
2589
2590         public List<TRet> Fetch<T1, T2, T3, T4, T5, TRet>(Func<T1, T2, T3, T4, T5, TRet> cb, Sql sql)
2591             => Query(cb, sql.SQL, sql.Arguments).ToList();
2592
2593         /// <inheritdoc />
2594         public IEnumerable<TRet> Query<T1, T2, TRet>(Func<T1, T2, TRet> cb, Sql sql)
2595             => Query<TRet>(new[] { typeof(T1), typeof(T2) }, cb, sql.SQL, sql.Arguments);
2596
2597         /// <inheritdoc />
2598         public IEnumerable<TRet> Query<T1, T2, T3, TRet>(Func<T1, T2, T3, TRet> cb, Sql sql)
2599             => Query<TRet>(new[] { typeof(T1), typeof(T2), typeof(T3) }, cb, sql.SQL, sql.Arguments);
2600
2601         /// <inheritdoc />
2602         public IEnumerable<TRet> Query<T1, T2, T3, T4, TRet>(Func<T1, T2, T3, T4, TRet> cb, Sql sql)
2603             => Query<TRet>(new[] { typeof(T1), typeof(T2), typeof(T3), typeof(T4) }, cb, sql.SQL, sql.Arguments);
2604
2605         /// <inheritdoc />
2606         public IEnumerable<TRet> Query<T1, T2, T3, T4, T5, TRet>(Func<T1, T2, T3, T4, T5, TRet> cb, Sql sql)
2607             => Query<TRet>(new[] { typeof(T1), typeof(T2), typeof(T3), typeof(T4), typeof(T5) }, cb, sql.SQL, sql.Arguments);
2608
2609         /// <inheritdoc />
2610         public List<T1> Fetch<T1, T2>(string sql, params object[] args)
2611             => Query<T1, T2>(sql, args).ToList();
2612
2613         /// <inheritdoc />
2614         public List<T1> Fetch<T1, T2, T3>(string sql, params object[] args)
2615             => Query<T1, T2, T3>(sql, args).ToList();
2616
2617         /// <inheritdoc />
2618         public List<T1> Fetch<T1, T2, T3, T4>(string sql, params object[] args)
2619             => Query<T1, T2, T3, T4>(sql, args).ToList();
2620
2621         /// <inheritdoc />
2622         public List<T1> Fetch<T1, T2, T3, T4, T5>(string sql, params object[] args)
2623             => Query<T1, T2, T3, T4, T5>(sql, args).ToList();
2624
2625         /// <inheritdoc />
2626         public IEnumerable<T1> Query<T1, T2>(string sql, params object[] args)
2627             => Query<T1>(new[] { typeof(T1), typeof(T2) }, null, sql, args);
2628
2629         /// <inheritdoc />
2630         public IEnumerable<T1> Query<T1, T2, T3>(string sql, params object[] args)
2631             => Query<T1>(new[] { typeof(T1), typeof(T2), typeof(T3) }, null, sql, args);
2632
2633         /// <inheritdoc />
2634         public IEnumerable<T1> Query<T1, T2, T3, T4>(string sql, params object[] args)
2635             => Query<T1>(new[] { typeof(T1), typeof(T2), typeof(T3), typeof(T4) }, null, sql, args);
2636
2637         /// <inheritdoc />
2638         public IEnumerable<T1> Query<T1, T2, T3, T4, T5>(string sql, params object[] args)
2639             => Query<T1>(new[] { typeof(T1), typeof(T2), typeof(T3), typeof(T4), typeof(T5) }, null, sql, args);
2640
2641         /// <inheritdoc />
2642         public List<T1> Fetch<T1, T2>(Sql sql)
2643             => Query<T1, T2>(sql.SQL, sql.Arguments).ToList();
2644
2645         /// <inheritdoc />
2646         public List<T1> Fetch<T1, T2, T3>(Sql sql)
2647             => Query<T1, T2, T3>(sql.SQL, sql.Arguments).ToList();
2648
2649         /// <inheritdoc />
2650         public List<T1> Fetch<T1, T2, T3, T4>(Sql sql)
2651             => Query<T1, T2, T3, T4>(sql.SQL, sql.Arguments).ToList();
2652
2653         /// <inheritdoc />
2654         public List<T1> Fetch<T1, T2, T3, T4, T5>(Sql sql)
2655             => Query<T1, T2, T3, T4, T5>(sql.SQL, sql.Arguments).ToList();
2656
2657         /// <inheritdoc />
2658         public IEnumerable<T1> Query<T1, T2>(Sql sql)
2659             => Query<T1>(new[] { typeof(T1), typeof(T2) }, null, sql.SQL, sql.Arguments);
2660
2661         /// <inheritdoc />
2662         public IEnumerable<T1> Query<T1, T2, T3>(Sql sql)
2663             => Query<T1>(new[] { typeof(T1), typeof(T2), typeof(T3) }, null, sql.SQL, sql.Arguments);
2664
2665         public IEnumerable<T1> Query<T1, T2, T3, T4>(Sql sql)
2666             => Query<T1>(new[] { typeof(T1), typeof(T2), typeof(T3), typeof(T4) }, null, sql.SQL, sql.Arguments);
2667
2668         /// <inheritdoc />
2669         public IEnumerable<T1> Query<T1, T2, T3, T4, T5>(Sql sql)
2670             => Query<T1>(new[] { typeof(T1), typeof(T2), typeof(T3), typeof(T4), typeof(T5) }, null, sql.SQL, sql.Arguments);
2671
2672         /// <inheritdoc />
2673         public IEnumerable<TRet> Query<TRet>(Type[] types, object cb, string sql, params object[] args)
2674         {
2675             OpenSharedConnection();
2676             try
2677             {
2678                 using (var cmd = CreateCommand(_sharedConnection, sql, args))
2679                 {
2680                     IDataReader r;
2681                     try
2682                     {
2683                         r = ExecuteReaderHelper(cmd);
2684                     }
2685                     catch (Exception x)
2686                     {
2687                         if (OnException(x))
2688                             throw;
2689                         yield break;
2690                     }
2691
2692                     var factory = MultiPocoFactory.GetFactory<TRet>(types, _sharedConnection.ConnectionString, sql, r, _defaultMapper);
2693                     if (cb == null)
2694                         cb = MultiPocoFactory.GetAutoMapper(types.ToArray());
2695                     var bNeedTerminator = false;
2696                     using (r)
2697                     {
2698                         while (true)
2699                         {
2700                             TRet poco;
2701                             try
2702                             {
2703                                 if (!r.Read())
2704                                     break;
2705                                 poco = factory(r, cb);
2706                             }
2707                             catch (Exception x)
2708                             {
2709                                 if (OnException(x))
2710                                     throw;
2711                                 yield break;
2712                             }
2713
2714                             if (poco != null)
2715                                 yield return poco;
2716                             else
2717                                 bNeedTerminator = true;
2718                         }
2719
2720                         if (bNeedTerminator)
2721                         {
2722                             var poco = (TRet) (cb as Delegate).DynamicInvoke(new object[types.Length]);
2723                             if (poco != null)
2724                                 yield return poco;
2725                             else
2726                                 yield break;
2727                         }
2728                     }
2729                 }
2730             }
2731             finally
2732             {
2733                 CloseSharedConnection();
2734             }
2735         }
2736
2737 #endregion
2738
2739 #region operation: Multi-Result Set
2740
2741         public IGridReader QueryMultiple(Sql sql)
2742             => QueryMultiple(sql.SQL, sql.Arguments);
2743
2744         public IGridReader QueryMultiple(string sql, params object[] args)
2745         {
2746             OpenSharedConnection();
2747
2748             GridReader result = null;
2749
2750             var cmd = CreateCommand(_sharedConnection, sql, args);
2751
2752             try
2753             {
2754                 var reader = ExecuteReaderHelper(cmd);
2755                 result = new GridReader(this, cmd, reader, _defaultMapper);
2756             }
2757             catch (Exception x)
2758             {
2759                 if (OnException(x))
2760                     throw;
2761             }
2762
2763             return result;
2764         }
2765
2766 #endregion
2767
2768 #region operation: StoredProc
2769
2770         /// <inheritdoc />
2771         public IEnumerable<T> QueryProc<T>(string storedProcedureName, params object[] args)
2772             => ExecuteReader<T>(CommandType.StoredProcedure, storedProcedureName, args);
2773
2774         /// <inheritdoc />
2775         public List<T> FetchProc<T>(string storedProcedureName, params object[] args)
2776             => QueryProc<T>(storedProcedureName, args).ToList();
2777
2778         /// <inheritdoc />
2779         public T ExecuteScalarProc<T>(string storedProcedureName, params object[] args)
2780             => ExecuteScalarInternal<T>(CommandType.StoredProcedure, storedProcedureName, args);
2781
2782         /// <inheritdoc />
2783         public int ExecuteNonQueryProc(string storedProcedureName, params object[] args)
2784             => ExecuteInternal(CommandType.StoredProcedure, storedProcedureName, args);
2785
2786 #if ASYNC
2787         /// <inheritdoc />
2788         public Task QueryProcAsync<T>(Action<T> receivePocoCallback, string storedProcedureName, params object[] args)
2789             => QueryProcAsync(receivePocoCallback, CancellationToken.None, storedProcedureName, args);
2790
2791         /// <inheritdoc />
2792         public Task QueryProcAsync<T>(Action<T> receivePocoCallback, CancellationToken cancellationToken, string storedProcedureName, params object[] args)
2793             => ExecuteReaderAsync(receivePocoCallback, cancellationToken, CommandType.StoredProcedure, storedProcedureName, args);
2794
2795         /// <inheritdoc />
2796         public Task<IAsyncReader<T>> QueryProcAsync<T>(string storedProcedureName, params object[] args)
2797             => QueryProcAsync<T>(CancellationToken.None, storedProcedureName, args);
2798
2799         /// <inheritdoc />
2800         public Task<IAsyncReader<T>> QueryProcAsync<T>(CancellationToken cancellationToken, string storedProcedureName, params object[] args)
2801             => ExecuteReaderAsync<T>(cancellationToken, CommandType.StoredProcedure, storedProcedureName, args);
2802
2803         /// <inheritdoc />
2804         public Task<List<T>> FetchProcAsync<T>(string storedProcedureName, params object[] args)
2805             => FetchProcAsync<T>(CancellationToken.None, storedProcedureName, args);
2806
2807         /// <inheritdoc />
2808         public async Task<List<T>> FetchProcAsync<T>(CancellationToken cancellationToken, string storedProcedureName, params object[] args)
2809         {
2810             var pocos = new List<T>();
2811             await ExecuteReaderAsync<T>(p => pocos.Add(p), cancellationToken, CommandType.StoredProcedure, storedProcedureName, args);
2812             return pocos;
2813         }
2814
2815         /// <inheritdoc />
2816         public Task<T> ExecuteScalarProcAsync<T>(string storedProcedureName, params object[] args)
2817             => ExecuteScalarProcAsync<T>(CancellationToken.None, storedProcedureName, args);
2818
2819         /// <inheritdoc />
2820         public Task<T> ExecuteScalarProcAsync<T>(CancellationToken cancellationToken, string storedProcedureName, params object[] args)
2821             => ExecuteScalarInternalAsync<T>(cancellationToken, CommandType.StoredProcedure, storedProcedureName, args);
2822
2823         /// <inheritdoc />
2824         public Task<int> ExecuteNonQueryProcAsync(string storedProcedureName, params object[] args)
2825             => ExecuteNonQueryProcAsync(CancellationToken.None, storedProcedureName, args);
2826
2827         /// <inheritdoc />
2828         public Task<int> ExecuteNonQueryProcAsync(CancellationToken cancellationToken, string storedProcedureName, params object[] args)
2829             => ExecuteInternalAsync(cancellationToken, CommandType.StoredProcedure, storedProcedureName, args);
2830 #endif
2831
2832 #endregion
2833
2834 #region Last Command
2835
2836         /// <summary>
2837         ///     Retrieves the SQL of the last executed statement
2838         /// </summary>
2839         public string LastSQL => _lastSql;
2840
2841         /// <summary>
2842         ///     Retrieves the arguments to the last execute statement
2843         /// </summary>
2844         public object[] LastArgs => _lastArgs;
2845
2846         /// <summary>
2847         ///     Returns a formatted string describing the last executed SQL statement and its argument values
2848         /// </summary>
2849         public string LastCommand => FormatCommand(_lastSql, _lastArgs);
2850
2851 #endregion
2852
2853 #region FormatCommand
2854
2855         /// <summary>
2856         ///     Formats the contents of a DB command for display
2857         /// </summary>
2858         /// <param name="cmd"></param>
2859         /// <returns></returns>
2860         public string FormatCommand(IDbCommand cmd)
2861         {
2862             return FormatCommand(cmd.CommandText, (from IDataParameter parameter in cmd.Parameters select parameter.Value).ToArray());
2863         }
2864
2865         /// <summary>
2866         ///     Formats an SQL query and its arguments for display
2867         /// </summary>
2868         /// <param name="sql"></param>
2869         /// <param name="args"></param>
2870         /// <returns></returns>
2871         public string FormatCommand(string sql, object[] args)
2872         {
2873             var sb = new StringBuilder();
2874             if (sql == null)
2875                 return "";
2876             sb.Append(sql);
2877             if (args != null && args.Length > 0)
2878             {
2879                 sb.Append("\n");
2880                 for (int i = 0; i < args.Length; i++)
2881                 {
2882                     sb.AppendFormat("\t -> {0}{1} [{2}] = \"{3}\"\n", _paramPrefix, i, args[i].GetType().Name, args[i]);
2883                 }
2884
2885                 sb.Remove(sb.Length - 1, 1);
2886             }
2887
2888             return sb.ToString();
2889         }
2890
2891 #endregion
2892
2893 #region Public Properties
2894
2895         /// <summary>
2896         ///     Gets the default mapper.
2897         /// </summary>
2898         public IMapper DefaultMapper => _defaultMapper;
2899
2900         /// <summary>
2901         ///     When set to true, PetaPoco will automatically create the "SELECT columns" part of any query that looks like it
2902         ///     needs it
2903         /// </summary>
2904         public bool EnableAutoSelect { get; set; }
2905
2906         /// <summary>
2907         ///     When set to true, parameters can be named ?myparam and populated from properties of the passed-in argument values.
2908         /// </summary>
2909         public bool EnableNamedParams { get; set; }
2910
2911         /// <summary>
2912         ///     Sets the timeout value for all SQL statements.
2913         /// </summary>
2914         public int CommandTimeout { get; set; }
2915
2916         /// <summary>
2917         ///     Sets the timeout value for the next (and only next) SQL statement
2918         /// </summary>
2919         public int OneTimeCommandTimeout { get; set; }
2920
2921         /// <summary>
2922         ///     Gets the loaded database provider. <seealso cref="Provider" />.
2923         /// </summary>
2924         /// <returns>
2925         ///     The loaded database type.
2926         /// </returns>
2927         public IProvider Provider => _provider;
2928
2929         /// <summary>
2930         ///     Gets the connection string.
2931         /// </summary>
2932         /// <returns>
2933         ///     The connection string.
2934         /// </returns>
2935         public string ConnectionString => _connectionString;
2936
2937         /// <summary>
2938         ///     Gets or sets the transaction isolation level.
2939         /// </summary>
2940         /// <remarks>
2941         ///     When value is null, the underlying providers default isolation level is used.
2942         /// </remarks>
2943         public IsolationLevel? IsolationLevel
2944         {
2945             get => _isolationLevel;
2946             set
2947             {
2948                 if (_transaction != null)
2949                     throw new InvalidOperationException("Isolation level can't be changed during a transaction.");
2950
2951                 _isolationLevel = value;
2952             }
2953         }
2954
2955         #endregion
2956
2957         #region Helpers
2958         internal protected IDataReader ExecuteReaderHelper(IDbCommand cmd)
2959         {
2960             return (IDataReader)CommandHelper(cmd, c => c.ExecuteReader());
2961         }
2962
2963         internal protected int ExecuteNonQueryHelper(IDbCommand cmd)
2964         {
2965             return (int)CommandHelper(cmd, c => c.ExecuteNonQuery());
2966         }
2967
2968         internal protected object ExecuteScalarHelper(IDbCommand cmd)
2969         {
2970             return CommandHelper(cmd, c => c.ExecuteScalar());
2971         }
2972
2973         private object CommandHelper(IDbCommand cmd, Func<IDbCommand, object> cmdFunc)
2974         {            
2975             DoPreExecute(cmd);
2976             var result = cmdFunc(cmd);
2977             OnExecutedCommand(cmd);
2978             return result;
2979         }
2980
2981 #if ASYNC
2982         internal protected async Task<IDataReader> ExecuteReaderHelperAsync(CancellationToken cancellationToken, IDbCommand cmd)
2983         {
2984             if (cmd is DbCommand dbCommand)
2985             {
2986                 var task = CommandHelper(cancellationToken, dbCommand, 
2987                     async (t, c) => await c.ExecuteReaderAsync(t).ConfigureAwait(false));
2988                 return (IDataReader)await task.ConfigureAwait(false);
2989             }
2990             else
2991                 return ExecuteReaderHelper(cmd);
2992         }
2993
2994         internal protected async Task<int> ExecuteNonQueryHelperAsync(CancellationToken cancellationToken, IDbCommand cmd)
2995         {
2996             if (cmd is DbCommand dbCommand)
2997             {
2998                 var task = CommandHelper(cancellationToken, dbCommand, 
2999                     async (t, c) => await c.ExecuteNonQueryAsync(t).ConfigureAwait(false));
3000                 return (int)await task.ConfigureAwait(false);
3001             }
3002             else
3003                 return ExecuteNonQueryHelper(cmd);
3004         }
3005
3006         internal protected Task<object> ExecuteScalarHelperAsync(CancellationToken cancellationToken, IDbCommand cmd)
3007         {
3008             if (cmd is DbCommand dbCommand)
3009                 return CommandHelper(cancellationToken, dbCommand, 
3010                     async (t, c) => await c.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false));                
3011             else
3012                 return Task.FromResult(ExecuteScalarHelper(cmd));
3013         }
3014
3015         private async Task<object> CommandHelper(CancellationToken cancellationToken, DbCommand cmd, 
3016             Func<CancellationToken, DbCommand, Task<object>> cmdFunc)
3017         {
3018             DoPreExecute(cmd);
3019             var result = await cmdFunc(cancellationToken, cmd).ConfigureAwait(false);
3020             OnExecutedCommand(cmd);
3021             return result;            
3022         }
3023 #endif
3024         #endregion
3025
3026         #region Events
3027
3028         /// <summary>
3029         ///     Occurs when a new transaction has started.
3030         /// </summary>
3031         public event EventHandler<DbTransactionEventArgs> TransactionStarted;
3032
3033         /// <summary>
3034         ///     Occurs when a transaction is about to be rolled back or committed.
3035         /// </summary>
3036         public event EventHandler<DbTransactionEventArgs> TransactionEnding;
3037
3038         /// <summary>
3039         ///     Occurs when a database command is about to be executed.
3040         /// </summary>
3041         public event EventHandler<DbCommandEventArgs> CommandExecuting;
3042
3043         /// <summary>
3044         ///     Occurs when a database command has been executed.
3045         /// </summary>
3046         public event EventHandler<DbCommandEventArgs> CommandExecuted;
3047
3048         /// <summary>
3049         ///     Occurs when a database connection is about to be closed.
3050         /// </summary>
3051         public event EventHandler<DbConnectionEventArgs> ConnectionClosing;
3052
3053         /// <summary>
3054         ///     Occurs when a database connection has been opened.
3055         /// </summary>
3056         public event EventHandler<DbConnectionEventArgs> ConnectionOpened;
3057
3058         /// <summary>
3059         ///     Occurs when a database exception has been thrown.
3060         /// </summary>
3061         public event EventHandler<ExceptionEventArgs> ExceptionThrown;
3062
3063 #endregion
3064     }
3065
3066     public class Database<TDatabaseProvider> : Database where TDatabaseProvider : IProvider
3067     {
3068         /// <summary>
3069         ///     Constructs an instance using a supplied connection string and provider type.
3070         /// </summary>
3071         /// <param name="connectionString">The database connection string.</param>
3072         /// <param name="defaultMapper">The default mapper to use when no specific mapper has been registered.</param>
3073         /// <exception cref="ArgumentException">Thrown when <paramref name="connectionString" /> is null or empty.</exception>
3074         public Database(string connectionString, IMapper defaultMapper = null)
3075             : base(connectionString, typeof(TDatabaseProvider).Name, defaultMapper)
3076         {
3077         }
3078     }
0b51bf 3079 }