/PetaPoco/PetaPoco.cs

Large files files are truncated, but you can click here to view the full file

   1/* PetaPoco v4.0.3 - A Tiny ORMish thing for your POCO's.
   2 * Copyright © 2011 Topten Software.  All Rights Reserved.
   3 * 
   4 * Apache License 2.0 - http://www.toptensoftware.com/petapoco/license
   5 * 
   6 * Special thanks to Rob Conery (@robconery) for original inspiration (ie:Massive) and for 
   7 * use of Subsonic's T4 templates, Rob Sullivan (@DataChomp) for hard core DBA advice 
   8 * and Adam Schroder (@schotime) for lots of suggestions, improvements and Oracle support
   9 */
  10
  11// Define PETAPOCO_NO_DYNAMIC in your project settings on .NET 3.5
  12
  13using System;
  14using System.Collections.Generic;
  15using System.Linq;
  16using System.Text;
  17using System.Configuration;
  18using System.Data.Common;
  19using System.Data;
  20using System.Text.RegularExpressions;
  21using System.Reflection;
  22using System.Reflection.Emit;
  23using System.Linq.Expressions;
  24
  25
  26namespace PetaPoco
  27{
  28	// Poco's marked [Explicit] require all column properties to be marked
  29	[AttributeUsage(AttributeTargets.Class)]
  30	public class ExplicitColumnsAttribute : Attribute
  31	{
  32	}
  33	// For non-explicit pocos, causes a property to be ignored
  34	[AttributeUsage(AttributeTargets.Property)]
  35	public class IgnoreAttribute : Attribute
  36	{
  37	}
  38
  39	// For explicit pocos, marks property as a column and optionally supplies column name
  40	[AttributeUsage(AttributeTargets.Property)]
  41	public class ColumnAttribute : Attribute
  42	{
  43		public ColumnAttribute() { }
  44		public ColumnAttribute(string name) { Name = name; }
  45		public string Name { get; set; }
  46	}
  47
  48	// For explicit pocos, marks property as a result column and optionally supplies column name
  49	[AttributeUsage(AttributeTargets.Property)]
  50	public class ResultColumnAttribute : ColumnAttribute
  51	{
  52		public ResultColumnAttribute() { }
  53		public ResultColumnAttribute(string name) : base(name) { }
  54	}
  55
  56	// Specify the table name of a poco
  57	[AttributeUsage(AttributeTargets.Class)]
  58	public class TableNameAttribute : Attribute
  59	{
  60		public TableNameAttribute(string tableName)
  61		{
  62			Value = tableName;
  63		}
  64		public string Value { get; private set; }
  65	}
  66
  67	// Specific the primary key of a poco class (and optional sequence name for Oracle)
  68	[AttributeUsage(AttributeTargets.Class)]
  69	public class PrimaryKeyAttribute : Attribute
  70	{
  71		public PrimaryKeyAttribute(string primaryKey)
  72		{
  73			Value = primaryKey;
  74			autoIncrement = true;
  75		}
  76
  77		public string Value { get; private set; }
  78		public string sequenceName { get; set; }
  79		public bool autoIncrement { get; set; }
  80	}
  81
  82	[AttributeUsage(AttributeTargets.Property)]
  83	public class AutoJoinAttribute : Attribute
  84	{
  85		public AutoJoinAttribute() { }
  86	}
  87
  88	// Results from paged request
  89	public class Page<T> 
  90	{
  91		public long CurrentPage { get; set; }
  92		public long TotalPages { get; set; }
  93		public long TotalItems { get; set; }
  94		public long ItemsPerPage { get; set; }
  95		public List<T> Items { get; set; }
  96		public object Context { get; set; }
  97	}
  98
  99	// Pass as parameter value to force to DBType.AnsiString
 100	public class AnsiString
 101	{
 102		public AnsiString(string str)
 103		{
 104			Value = str;
 105		}
 106		public string Value { get; private set; }
 107	}
 108
 109	// Used by IMapper to override table bindings for an object
 110	public class TableInfo
 111	{
 112		public string TableName { get; set; }
 113		public string PrimaryKey { get; set; }
 114		public bool AutoIncrement { get; set; }
 115		public string SequenceName { get; set; }
 116	}
 117
 118	// Optionally provide an implementation of this to Database.Mapper
 119	public interface IMapper
 120	{
 121		void GetTableInfo(Type t, TableInfo ti);
 122		bool MapPropertyToColumn(PropertyInfo pi, ref string columnName, ref bool resultColumn);
 123		Func<object, object> GetFromDbConverter(PropertyInfo pi, Type SourceType);
 124		Func<object, object> GetToDbConverter(Type SourceType);
 125	}
 126
 127	// This will be merged with IMapper in the next major version
 128	public interface IMapper2 : IMapper
 129	{
 130		Func<object, object> GetFromDbConverter(Type DestType, Type SourceType);
 131	}
 132
 133	// Database class ... this is where most of the action happens
 134	public class Database : IDisposable
 135	{
 136		public Database(IDbConnection connection)
 137		{
 138			_sharedConnection = connection;
 139			_connectionString = connection.ConnectionString;
 140			_sharedConnectionDepth = 2;		// Prevent closing external connection
 141			CommonConstruct();
 142		}
 143
 144		public Database(string connectionString, string providerName)
 145		{
 146			_connectionString = connectionString;
 147			_providerName = providerName;
 148			CommonConstruct();
 149		}
 150
 151		public Database(string connectionString, DbProviderFactory provider)
 152		{
 153			_connectionString = connectionString;
 154			_factory = provider;
 155			CommonConstruct();
 156		}
 157
 158		public Database(string connectionStringName)
 159		{
 160			// Use first?
 161			if (connectionStringName == "")
 162				connectionStringName = ConfigurationManager.ConnectionStrings[0].Name;
 163
 164			// Work out connection string and provider name
 165			var providerName = "System.Data.SqlClient";
 166			if (ConfigurationManager.ConnectionStrings[connectionStringName] != null)
 167			{
 168				if (!string.IsNullOrEmpty(ConfigurationManager.ConnectionStrings[connectionStringName].ProviderName))
 169					providerName = ConfigurationManager.ConnectionStrings[connectionStringName].ProviderName;
 170			}
 171			else
 172			{
 173				throw new InvalidOperationException("Can't find a connection string with the name '" + connectionStringName + "'");
 174			}
 175
 176			// Store factory and connection string
 177			_connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
 178			_providerName = providerName;
 179			CommonConstruct();
 180		}
 181
 182		enum DBType
 183		{
 184			SqlServer,
 185			SqlServerCE,
 186			MySql,
 187			PostgreSQL,
 188			Oracle,
 189            SQLite
 190		}
 191		DBType _dbType = DBType.SqlServer;
 192
 193		// Common initialization
 194		private void CommonConstruct()
 195		{
 196			_transactionDepth = 0;
 197			EnableAutoSelect = true;
 198			EnableNamedParams = true;
 199			ForceDateTimesToUtc = true;
 200
 201			if (_providerName != null)
 202				_factory = DbProviderFactories.GetFactory(_providerName);
 203
 204			string dbtype = (_factory == null ? _sharedConnection.GetType() : _factory.GetType()).Name;
 205
 206			// Try using type name first (more reliable)
 207			if (dbtype.StartsWith("MySql")) _dbType = DBType.MySql;
 208			else if (dbtype.StartsWith("SqlCe")) _dbType = DBType.SqlServerCE;
 209			else if (dbtype.StartsWith("Npgsql")) _dbType = DBType.PostgreSQL;
 210			else if (dbtype.StartsWith("Oracle")) _dbType = DBType.Oracle;
 211			else if (dbtype.StartsWith("SQLite")) _dbType = DBType.SQLite;
 212			else if (dbtype.StartsWith("System.Data.SqlClient.")) _dbType = DBType.SqlServer;
 213			// else try with provider name
 214			else if (_providerName.IndexOf("MySql", StringComparison.InvariantCultureIgnoreCase) >= 0) _dbType = DBType.MySql;
 215			else if (_providerName.IndexOf("SqlServerCe", StringComparison.InvariantCultureIgnoreCase) >= 0) _dbType = DBType.SqlServerCE;
 216			else if (_providerName.IndexOf("Npgsql", StringComparison.InvariantCultureIgnoreCase) >= 0) _dbType = DBType.PostgreSQL;
 217			else if (_providerName.IndexOf("Oracle", StringComparison.InvariantCultureIgnoreCase) >= 0) _dbType = DBType.Oracle;
 218			else if (_providerName.IndexOf("SQLite", StringComparison.InvariantCultureIgnoreCase) >= 0) _dbType = DBType.SQLite;
 219
 220			if (_dbType == DBType.MySql && _connectionString != null && _connectionString.IndexOf("Allow User Variables=true") >= 0)
 221				_paramPrefix = "?";
 222			if (_dbType == DBType.Oracle)
 223				_paramPrefix = ":";
 224		}
 225
 226		// Automatically close one open shared connection
 227		public void Dispose()
 228		{
 229			// Automatically close one open connection reference
 230			//  (Works with KeepConnectionAlive and manually opening a shared connection)
 231			CloseSharedConnection();
 232		}
 233
 234		// Set to true to keep the first opened connection alive until this object is disposed
 235		public bool KeepConnectionAlive { get; set; }
 236
 237		// Open a connection (can be nested)
 238		public void OpenSharedConnection()
 239		{
 240			if (_sharedConnectionDepth == 0)
 241			{
 242				_sharedConnection = _factory.CreateConnection();
 243				_sharedConnection.ConnectionString = _connectionString;
 244				_sharedConnection.Open();
 245
 246				_sharedConnection = OnConnectionOpened(_sharedConnection);
 247
 248				if (KeepConnectionAlive)
 249					_sharedConnectionDepth++;		// Make sure you call Dispose
 250			}
 251			_sharedConnectionDepth++;
 252		}
 253
 254		// Close a previously opened connection
 255		public void CloseSharedConnection()
 256		{
 257			if (_sharedConnectionDepth > 0)
 258			{
 259				_sharedConnectionDepth--;
 260				if (_sharedConnectionDepth == 0)
 261				{
 262					OnConnectionClosing(_sharedConnection);
 263					_sharedConnection.Dispose();
 264					_sharedConnection = null;
 265				}
 266			}
 267		}
 268
 269		// Access to our shared connection
 270		public IDbConnection Connection
 271		{
 272			get { return _sharedConnection; }
 273		}
 274
 275		// Helper to create a transaction scope
 276		public Transaction GetTransaction()
 277		{
 278			return new Transaction(this);
 279		}
 280
 281		// Use by derived repo generated by T4 templates
 282		public virtual void OnBeginTransaction() { }
 283		public virtual void OnEndTransaction() { }
 284
 285		// Start a new transaction, can be nested, every call must be
 286		//	matched by a call to AbortTransaction or CompleteTransaction
 287		// Use `using (var scope=db.Transaction) { scope.Complete(); }` to ensure correct semantics
 288		public void BeginTransaction()
 289		{
 290			_transactionDepth++;
 291
 292			if (_transactionDepth == 1)
 293			{
 294				OpenSharedConnection();
 295				_transaction = _sharedConnection.BeginTransaction();
 296				_transactionCancelled = false;
 297				OnBeginTransaction();
 298			}
 299
 300		}
 301
 302		// Internal helper to cleanup transaction stuff
 303		void CleanupTransaction()
 304		{
 305			OnEndTransaction();
 306
 307			if (_transactionCancelled)
 308				_transaction.Rollback();
 309			else
 310				_transaction.Commit();
 311
 312			_transaction.Dispose();
 313			_transaction = null;
 314
 315			CloseSharedConnection();
 316		}
 317
 318		// Abort the entire outer most transaction scope
 319		public void AbortTransaction()
 320		{
 321			_transactionCancelled = true;
 322			if ((--_transactionDepth) == 0)
 323				CleanupTransaction();
 324		}
 325
 326		// Complete the transaction
 327		public void CompleteTransaction()
 328		{
 329			if ((--_transactionDepth) == 0)
 330				CleanupTransaction();
 331		}
 332
 333		// Helper to handle named parameters from object properties
 334		static Regex rxParams = new Regex(@"(?<!@)@\w+", RegexOptions.Compiled);
 335		public static string ProcessParams(string _sql, object[] args_src, List<object> args_dest)
 336		{
 337			return rxParams.Replace(_sql, m =>
 338			{
 339				string param = m.Value.Substring(1);
 340
 341				object arg_val;
 342
 343				int paramIndex;
 344				if (int.TryParse(param, out paramIndex))
 345				{
 346					// Numbered parameter
 347					if (paramIndex < 0 || paramIndex >= args_src.Length)
 348						throw new ArgumentOutOfRangeException(string.Format("Parameter '@{0}' specified but only {1} parameters supplied (in `{2}`)", paramIndex, args_src.Length, _sql));
 349					arg_val = args_src[paramIndex];
 350				}
 351				else
 352				{
 353					// Look for a property on one of the arguments with this name
 354					bool found = false;
 355					arg_val = null;
 356					foreach (var o in args_src)
 357					{
 358						var pi = o.GetType().GetProperty(param);
 359						if (pi != null)
 360						{
 361							arg_val = pi.GetValue(o, null);
 362							found = true;
 363							break;
 364						}
 365					}
 366
 367					if (!found)
 368						throw new ArgumentException(string.Format("Parameter '@{0}' specified but none of the passed arguments have a property with this name (in '{1}')", param, _sql));
 369				}
 370
 371				// Expand collections to parameter lists
 372				if ((arg_val as System.Collections.IEnumerable) != null && 
 373					(arg_val as string) == null && 
 374					(arg_val as byte[]) == null)
 375				{
 376					var sb = new StringBuilder();
 377					foreach (var i in arg_val as System.Collections.IEnumerable)
 378					{
 379						sb.Append((sb.Length == 0 ? "@" : ",@") + args_dest.Count.ToString());
 380						args_dest.Add(i);
 381					}
 382					return sb.ToString();
 383				}
 384				else
 385				{
 386					args_dest.Add(arg_val);
 387					return "@" + (args_dest.Count - 1).ToString();
 388				}
 389			}
 390			);
 391		}
 392
 393		// Add a parameter to a DB command
 394		void AddParam(IDbCommand cmd, object item, string ParameterPrefix)
 395		{
 396			// Convert value to from poco type to db type
 397			if (Database.Mapper != null && item!=null)
 398			{
 399				var fn = Database.Mapper.GetToDbConverter(item.GetType());
 400				if (fn!=null)
 401					item = fn(item);
 402			}
 403
 404			// Support passed in parameters
 405			var idbParam = item as IDbDataParameter;
 406			if (idbParam != null)
 407			{
 408				idbParam.ParameterName = string.Format("{0}{1}", ParameterPrefix, cmd.Parameters.Count);
 409				cmd.Parameters.Add(idbParam);
 410				return;
 411			}
 412
 413			var p = cmd.CreateParameter();
 414			p.ParameterName = string.Format("{0}{1}", ParameterPrefix, cmd.Parameters.Count);
 415			if (item == null)
 416			{
 417				p.Value = DBNull.Value;
 418			}
 419			else
 420			{
 421				var t = item.GetType();
 422				if (t.IsEnum)		// PostgreSQL .NET driver wont cast enum to int
 423				{
 424					p.Value = (int)item;
 425				}
 426				else if (t == typeof(Guid))
 427				{
 428					p.Value = item.ToString();
 429					p.DbType = DbType.String;
 430					p.Size = 40;
 431				}
 432				else if (t == typeof(string))
 433				{
 434					p.Size = Math.Max((item as string).Length + 1, 4000);		// Help query plan caching by using common size
 435					p.Value = item;
 436				}
 437				else if (t == typeof(AnsiString))
 438				{
 439					// Thanks @DataChomp for pointing out the SQL Server indexing performance hit of using wrong string type on varchar
 440					p.Size = Math.Max((item as AnsiString).Value.Length + 1, 4000);
 441					p.Value = (item as AnsiString).Value;
 442					p.DbType = DbType.AnsiString;
 443				}
 444				else if (t == typeof(bool) && _dbType != DBType.PostgreSQL)
 445				{
 446					p.Value = ((bool)item) ? 1 : 0;
 447				}
 448				else if (item.GetType().Name == "SqlGeography") //SqlGeography is a CLR Type
 449				{
 450					p.GetType().GetProperty("UdtTypeName").SetValue(p, "geography", null); //geography is the equivalent SQL Server Type
 451					p.Value = item;
 452				}
 453
 454				else if (item.GetType().Name == "SqlGeometry") //SqlGeometry is a CLR Type
 455				{
 456					p.GetType().GetProperty("UdtTypeName").SetValue(p, "geometry", null); //geography is the equivalent SQL Server Type
 457					p.Value = item;
 458				}
 459				else
 460				{
 461					p.Value = item;
 462				}
 463			}
 464
 465			cmd.Parameters.Add(p);
 466		}
 467
 468		// Create a command
 469		static Regex rxParamsPrefix = new Regex(@"(?<!@)@\w+", RegexOptions.Compiled);
 470		public IDbCommand CreateCommand(IDbConnection connection, string sql, params object[] args)
 471		{
 472			// Perform named argument replacements
 473			if (EnableNamedParams)
 474			{
 475				var new_args = new List<object>();
 476				sql = ProcessParams(sql, args, new_args);
 477				args = new_args.ToArray();
 478			}
 479
 480			// Perform parameter prefix replacements
 481			if (_paramPrefix != "@")
 482				sql = rxParamsPrefix.Replace(sql, m => _paramPrefix + m.Value.Substring(1));
 483			sql = sql.Replace("@@", "@");		   // <- double @@ escapes a single @
 484
 485			// Create the command and add parameters
 486			IDbCommand cmd = connection.CreateCommand();
 487			cmd.Connection = connection;
 488			cmd.CommandText = sql;
 489			cmd.Transaction = _transaction;
 490			foreach (var item in args)
 491			{
 492				AddParam(cmd, item, _paramPrefix);
 493			}
 494
 495			if (_dbType == DBType.Oracle)
 496			{
 497				cmd.GetType().GetProperty("BindByName").SetValue(cmd, true, null);
 498			}
 499
 500			if (!String.IsNullOrEmpty(sql))
 501				DoPreExecute(cmd);
 502
 503			return cmd;
 504		}
 505
 506		// Override this to log/capture exceptions
 507		public virtual void OnException(Exception x)
 508		{
 509			System.Diagnostics.Debug.WriteLine(x.ToString());
 510			System.Diagnostics.Debug.WriteLine(LastCommand);
 511		}
 512
 513		// Override this to log commands, or modify command before execution
 514		public virtual IDbConnection OnConnectionOpened(IDbConnection conn) { return conn; }
 515		public virtual void OnConnectionClosing(IDbConnection conn) { }
 516		public virtual void OnExecutingCommand(IDbCommand cmd) { }
 517		public virtual void OnExecutedCommand(IDbCommand cmd) { }
 518
 519		// Execute a non-query command
 520		public int Execute(string sql, params object[] args)
 521		{
 522			try
 523			{
 524				OpenSharedConnection();
 525				try
 526				{
 527					using (var cmd = CreateCommand(_sharedConnection, sql, args))
 528					{
 529						var retv=cmd.ExecuteNonQuery();
 530						OnExecutedCommand(cmd);
 531						return retv;
 532					}
 533				}
 534				finally
 535				{
 536					CloseSharedConnection();
 537				}
 538			}
 539			catch (Exception x)
 540			{
 541				OnException(x);
 542				throw;
 543			}
 544		}
 545
 546		public int Execute(Sql sql)
 547		{
 548			return Execute(sql.SQL, sql.Arguments);
 549		}
 550
 551		// Execute and cast a scalar property
 552		public T ExecuteScalar<T>(string sql, params object[] args)
 553		{
 554			try
 555			{
 556				OpenSharedConnection();
 557				try
 558				{
 559					using (var cmd = CreateCommand(_sharedConnection, sql, args))
 560					{
 561						object val = cmd.ExecuteScalar();
 562						OnExecutedCommand(cmd);
 563						return (T)Convert.ChangeType(val, typeof(T));
 564					}
 565				}
 566				finally
 567				{
 568					CloseSharedConnection();
 569				}
 570			}
 571			catch (Exception x)
 572			{
 573				OnException(x);
 574				throw;
 575			}
 576		}
 577
 578		public T ExecuteScalar<T>(Sql sql)
 579		{
 580			return ExecuteScalar<T>(sql.SQL, sql.Arguments);
 581		}
 582
 583		Regex rxSelect = new Regex(@"\A\s*(SELECT|EXECUTE|CALL)\s", RegexOptions.Compiled | RegexOptions.Singleline | RegexOptions.IgnoreCase | RegexOptions.Multiline);
 584		Regex rxFrom = new Regex(@"\A\s*FROM\s", RegexOptions.Compiled | RegexOptions.Singleline | RegexOptions.IgnoreCase | RegexOptions.Multiline);
 585		string AddSelectClause<T>(string sql)
 586		{
 587			if (sql.StartsWith(";"))
 588				return sql.Substring(1);
 589
 590			if (!rxSelect.IsMatch(sql))
 591			{
 592				var pd = PocoData.ForType(typeof(T));
 593				var tableName = EscapeTableName(pd.TableInfo.TableName);
 594				string cols = string.Join(", ", (from c in pd.QueryColumns select tableName + "." + EscapeSqlIdentifier(c)).ToArray());
 595				if (!rxFrom.IsMatch(sql))
 596					sql = string.Format("SELECT {0} FROM {1} {2}", cols, tableName, sql);
 597				else
 598					sql = string.Format("SELECT {0} {1}", cols, sql);
 599			}
 600			return sql;
 601		}
 602
 603		public bool EnableAutoSelect { get; set; }
 604		public bool EnableNamedParams { get; set; }
 605		public bool ForceDateTimesToUtc { get; set; }
 606
 607		// Return a typed list of pocos
 608		public List<T> Fetch<T>(string sql, params object[] args) 
 609		{
 610			return Query<T>(sql, args).ToList();
 611		}
 612
 613		public List<T> Fetch<T>(Sql sql) 
 614		{
 615			return Fetch<T>(sql.SQL, sql.Arguments);
 616		}
 617
 618		static Regex rxColumns = new Regex(@"\A\s*SELECT\s+((?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|.)*?)(?<!,\s+)\bFROM\b", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
 619		static Regex rxOrderBy = new Regex(@"\bORDER\s+BY\s+(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+(?:\s+(?:ASC|DESC))?(?:\s*,\s*(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+(?:\s+(?:ASC|DESC))?)*", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
 620		static Regex rxDistinct = new Regex(@"\ADISTINCT\s", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
 621		public static bool SplitSqlForPaging(string sql, out string sqlCount, out string sqlSelectRemoved, out string sqlOrderBy)
 622		{
 623			sqlSelectRemoved = null;
 624			sqlCount = null;
 625			sqlOrderBy = null;
 626
 627			// Extract the columns from "SELECT <whatever> FROM"
 628			var m = rxColumns.Match(sql);
 629			if (!m.Success)
 630				return false;
 631
 632			// Save column list and replace with COUNT(*)
 633			Group g = m.Groups[1];
 634			sqlSelectRemoved = sql.Substring(g.Index);
 635
 636			if (rxDistinct.IsMatch(sqlSelectRemoved))
 637				sqlCount = sql.Substring(0, g.Index) + "COUNT(" + m.Groups[1].ToString().Trim() + ") " + sql.Substring(g.Index + g.Length);
 638			else
 639				sqlCount = sql.Substring(0, g.Index) + "COUNT(*) " + sql.Substring(g.Index + g.Length);
 640
 641
 642			// Look for an "ORDER BY <whatever>" clause
 643			m = rxOrderBy.Match(sqlCount);
 644			if (!m.Success)
 645			{
 646				sqlOrderBy = null;
 647			}
 648			else
 649			{
 650				g = m.Groups[0];
 651				sqlOrderBy = g.ToString();
 652				sqlCount = sqlCount.Substring(0, g.Index) + sqlCount.Substring(g.Index + g.Length);
 653			}
 654
 655			return true;
 656		}
 657
 658		public void BuildPageQueries<T>(long skip, long take, string sql, ref object[] args, out string sqlCount, out string sqlPage) 
 659		{
 660			// Add auto select clause
 661			if (EnableAutoSelect)
 662				sql = AddSelectClause<T>(sql);
 663
 664			// Split the SQL into the bits we need
 665			string sqlSelectRemoved, sqlOrderBy;
 666			if (!SplitSqlForPaging(sql, out sqlCount, out sqlSelectRemoved, out sqlOrderBy))
 667				throw new Exception("Unable to parse SQL statement for paged query");
 668			if (_dbType == DBType.Oracle && sqlSelectRemoved.StartsWith("*"))
 669                throw new Exception("Query must alias '*' when performing a paged query.\neg. select t.* from table t order by t.id");
 670
 671			// Build the SQL for the actual final result
 672			if (_dbType == DBType.SqlServer || _dbType == DBType.Oracle)
 673			{
 674				sqlSelectRemoved = rxOrderBy.Replace(sqlSelectRemoved, "");
 675				if (rxDistinct.IsMatch(sqlSelectRemoved))
 676				{
 677					sqlSelectRemoved = "peta_inner.* FROM (SELECT " + sqlSelectRemoved + ") peta_inner";
 678				}
 679				sqlPage = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) peta_rn, {1}) peta_paged WHERE peta_rn>@{2} AND peta_rn<=@{3}",
 680										sqlOrderBy==null ? "ORDER BY (SELECT NULL)" : sqlOrderBy, sqlSelectRemoved, args.Length, args.Length + 1);
 681				args = args.Concat(new object[] { skip, skip+take }).ToArray();
 682			}
 683			else if (_dbType == DBType.SqlServerCE)
 684			{
 685				sqlPage = string.Format("{0}\nOFFSET @{1} ROWS FETCH NEXT @{2} ROWS ONLY", sql, args.Length, args.Length + 1);
 686				args = args.Concat(new object[] { skip, take }).ToArray();
 687			}
 688			else
 689			{
 690				sqlPage = string.Format("{0}\nLIMIT @{1} OFFSET @{2}", sql, args.Length, args.Length + 1);
 691				args = args.Concat(new object[] { take, skip }).ToArray();
 692			}
 693
 694		}
 695
 696		// Fetch a page	
 697		public Page<T> Page<T>(long page, long itemsPerPage, string sql, params object[] args) 
 698		{
 699			string sqlCount, sqlPage;
 700			BuildPageQueries<T>((page-1)*itemsPerPage, itemsPerPage, sql, ref args, out sqlCount, out sqlPage);
 701
 702			// Save the one-time command time out and use it for both queries
 703			int saveTimeout = OneTimeCommandTimeout;
 704
 705			// Setup the paged result
 706			var result = new Page<T>();
 707			result.CurrentPage = page;
 708			result.ItemsPerPage = itemsPerPage;
 709			result.TotalItems = ExecuteScalar<long>(sqlCount, args);
 710			result.TotalPages = result.TotalItems / itemsPerPage;
 711			if ((result.TotalItems % itemsPerPage) != 0)
 712				result.TotalPages++;
 713
 714			OneTimeCommandTimeout = saveTimeout;
 715
 716			// Get the records
 717			result.Items = Fetch<T>(sqlPage, args);
 718
 719			// Done
 720			return result;
 721		}
 722
 723		public Page<T> Page<T>(long page, long itemsPerPage, Sql sql) 
 724		{
 725			return Page<T>(page, itemsPerPage, sql.SQL, sql.Arguments);
 726		}
 727
 728
 729		public List<T> Fetch<T>(long page, long itemsPerPage, string sql, params object[] args)
 730		{
 731			return SkipTake<T>((page - 1) * itemsPerPage, itemsPerPage, sql, args);
 732		}
 733
 734		public List<T> Fetch<T>(long page, long itemsPerPage, Sql sql)
 735		{
 736			return SkipTake<T>((page - 1) * itemsPerPage, itemsPerPage, sql.SQL, sql.Arguments);
 737		}
 738
 739		public List<T> SkipTake<T>(long skip, long take, string sql, params object[] args)
 740		{
 741			string sqlCount, sqlPage;
 742			BuildPageQueries<T>(skip, take, sql, ref args, out sqlCount, out sqlPage);
 743			return Fetch<T>(sqlPage, args);
 744		}
 745
 746		public List<T> SkipTake<T>(long skip, long take, Sql sql)
 747		{
 748			return SkipTake<T>(skip, take, sql.SQL, sql.Arguments);
 749		}
 750
 751		// Return an enumerable collection of pocos
 752		public IEnumerable<T> Query<T>(string sql, params object[] args) 
 753		{
 754			if (EnableAutoSelect)
 755				sql = AddSelectClause<T>(sql);
 756
 757			OpenSharedConnection();
 758			try
 759			{
 760				using (var cmd = CreateCommand(_sharedConnection, sql, args))
 761				{
 762					IDataReader r;
 763					var pd = PocoData.ForType(typeof(T));
 764					try
 765					{
 766						r = cmd.ExecuteReader();
 767						OnExecutedCommand(cmd);
 768					}
 769					catch (Exception x)
 770					{
 771						OnException(x);
 772						throw;
 773					}
 774					var factory = pd.GetFactory(cmd.CommandText, _sharedConnection.ConnectionString, ForceDateTimesToUtc, 0, r.FieldCount, r) as Func<IDataReader, T>;
 775					using (r)
 776					{
 777						while (true)
 778						{
 779							T poco;
 780							try
 781							{
 782								if (!r.Read())
 783									yield break;
 784								poco = factory(r);
 785							}
 786							catch (Exception x)
 787							{
 788								OnException(x);
 789								throw;
 790							}
 791
 792							yield return poco;
 793						}
 794					}
 795				}
 796			}
 797			finally
 798			{
 799				CloseSharedConnection();
 800			}
 801		}
 802
 803		// Multi Fetch
 804		public List<TRet> Fetch<T1, T2, TRet>(Func<T1, T2, TRet> cb, string sql, params object[] args) { return Query<T1, T2, TRet>(cb, sql, args).ToList(); }
 805		public List<TRet> Fetch<T1, T2, T3, TRet>(Func<T1, T2, T3, TRet> cb, string sql, params object[] args) { return Query<T1, T2, T3, TRet>(cb, sql, args).ToList(); }
 806		public List<TRet> Fetch<T1, T2, T3, T4, TRet>(Func<T1, T2, T3, T4, TRet> cb, string sql, params object[] args) { return Query<T1, T2, T3, T4, TRet>(cb, sql, args).ToList(); }
 807
 808		// Multi Query
 809		public IEnumerable<TRet> Query<T1, T2, TRet>(Func<T1, T2, TRet> cb, string sql, params object[] args) { return Query<TRet>(new Type[] { typeof(T1), typeof(T2) }, cb, sql, args); }
 810		public IEnumerable<TRet> Query<T1, T2, T3, TRet>(Func<T1, T2, T3, TRet> cb, string sql, params object[] args) { return Query<TRet>(new Type[] { typeof(T1), typeof(T2), typeof(T3)}, cb, sql, args); }
 811		public IEnumerable<TRet> Query<T1, T2, T3, T4, TRet>(Func<T1, T2, T3, T4, TRet> cb, string sql, params object[] args) { return Query<TRet>(new Type[] { typeof(T1), typeof(T2), typeof(T3), typeof(T4)}, cb, sql, args); }
 812
 813		// Multi Fetch (SQL builder)
 814		public List<TRet> Fetch<T1, T2, TRet>(Func<T1, T2, TRet> cb, Sql sql) { return Query<T1, T2, TRet>(cb, sql.SQL, sql.Arguments).ToList(); }
 815		public List<TRet> Fetch<T1, T2, T3, TRet>(Func<T1, T2, T3, TRet> cb, Sql sql) { return Query<T1, T2, T3, TRet>(cb, sql.SQL, sql.Arguments).ToList(); }
 816		public List<TRet> Fetch<T1, T2, T3, T4, TRet>(Func<T1, T2, T3, T4, TRet> cb, Sql sql) { return Query<T1, T2, T3, T4, TRet>(cb, sql.SQL, sql.Arguments).ToList(); }
 817
 818		// Multi Query (SQL builder)
 819		public IEnumerable<TRet> Query<T1, T2, TRet>(Func<T1, T2, TRet> cb, Sql sql) { return Query<TRet>(new Type[] { typeof(T1), typeof(T2) }, cb, sql.SQL, sql.Arguments); }
 820		public IEnumerable<TRet> Query<T1, T2, T3, TRet>(Func<T1, T2, T3, TRet> cb, Sql sql) { return Query<TRet>(new Type[] { typeof(T1), typeof(T2), typeof(T3) }, cb, sql.SQL, sql.Arguments); }
 821		public IEnumerable<TRet> Query<T1, T2, T3, T4, TRet>(Func<T1, T2, T3, T4, TRet> cb, Sql sql) { return Query<TRet>(new Type[] { typeof(T1), typeof(T2), typeof(T3), typeof(T4) }, cb, sql.SQL, sql.Arguments); }
 822
 823		// Multi Fetch (Simple)
 824		public List<T1> Fetch<T1, T2>(string sql, params object[] args) { return Query<T1, T2>(sql, args).ToList(); }
 825		public List<T1> Fetch<T1, T2, T3>(string sql, params object[] args) { return Query<T1, T2, T3>(sql, args).ToList(); }
 826		public List<T1> Fetch<T1, T2, T3, T4>(string sql, params object[] args) { return Query<T1, T2, T3, T4>(sql, args).ToList(); }
 827
 828		// Multi Query (Simple)
 829		public IEnumerable<T1> Query<T1, T2>(string sql, params object[] args) { return Query<T1>(new Type[] { typeof(T1), typeof(T2) }, null, sql, args); }
 830		public IEnumerable<T1> Query<T1, T2, T3>(string sql, params object[] args) { return Query<T1>(new Type[] { typeof(T1), typeof(T2), typeof(T3) }, null, sql, args); }
 831		public IEnumerable<T1> Query<T1, T2, T3, T4>(string sql, params object[] args) { return Query<T1>(new Type[] { typeof(T1), typeof(T2), typeof(T3), typeof(T4) }, null, sql, args); }
 832
 833		// Multi Fetch (Simple) (SQL builder)
 834		public List<T1> Fetch<T1, T2>(Sql sql) { return Query<T1, T2>(sql.SQL, sql.Arguments).ToList(); }
 835		public List<T1> Fetch<T1, T2, T3>(Sql sql) { return Query<T1, T2, T3>(sql.SQL, sql.Arguments).ToList(); }
 836		public List<T1> Fetch<T1, T2, T3, T4>(Sql sql) { return Query<T1, T2, T3, T4>(sql.SQL, sql.Arguments).ToList(); }
 837
 838		// Multi Query (Simple) (SQL builder)
 839		public IEnumerable<T1> Query<T1, T2>(Sql sql) { return Query<T1>(new Type[] { typeof(T1), typeof(T2) }, null, sql.SQL, sql.Arguments); }
 840		public IEnumerable<T1> Query<T1, T2, T3>(Sql sql) { return Query<T1>(new Type[] { typeof(T1), typeof(T2), typeof(T3) }, null, sql.SQL, sql.Arguments); }
 841		public IEnumerable<T1> Query<T1, T2, T3, T4>(Sql sql) { return Query<T1>(new Type[] { typeof(T1), typeof(T2), typeof(T3), typeof(T4) }, null, sql.SQL, sql.Arguments); }
 842
 843		// Automagically guess the property relationships between various POCOs and create a delegate that will set them up
 844		object GetAutoMapper(Type[] types)
 845		{
 846			// Build a key
 847			var kb = new StringBuilder();
 848			foreach (var t in types)
 849			{
 850				kb.Append(t.ToString());
 851				kb.Append(":");
 852			}
 853			var key = kb.ToString();
 854
 855			// Check cache
 856			RWLock.EnterReadLock();
 857			try
 858			{
 859				object mapper;
 860				if (AutoMappers.TryGetValue(key, out mapper))
 861					return mapper;
 862			}
 863			finally
 864			{
 865				RWLock.ExitReadLock();
 866			}
 867
 868			// Create it
 869			RWLock.EnterWriteLock();
 870			try
 871			{
 872				// Try again
 873				object mapper;
 874				if (AutoMappers.TryGetValue(key, out mapper))
 875					return mapper;
 876
 877				// Create a method
 878				var m = new DynamicMethod("petapoco_automapper", types[0], types, true);
 879				var il = m.GetILGenerator();
 880
 881				for (int i = 1; i < types.Length; i++)
 882				{
 883					bool handled = false;
 884					for (int j = i - 1; j >= 0; j--)
 885					{
 886						// Find the property
 887						var candidates = from p in types[j].GetProperties() where p.PropertyType == types[i] select p;
 888						if (candidates.Count() == 0)
 889							continue;
 890						if (candidates.Count() > 1)
 891							throw new InvalidOperationException(string.Format("Can't auto join {0} as {1} has more than one property of type {0}", types[i], types[j]));
 892
 893						// Generate code
 894						il.Emit(OpCodes.Ldarg_S, j);
 895						il.Emit(OpCodes.Ldarg_S, i);
 896						il.Emit(OpCodes.Callvirt, candidates.First().GetSetMethod(true));
 897						handled = true;
 898					}
 899
 900					if (!handled)
 901						throw new InvalidOperationException(string.Format("Can't auto join {0}", types[i]));
 902				}
 903
 904				il.Emit(OpCodes.Ldarg_0);
 905				il.Emit(OpCodes.Ret);
 906
 907				// Cache it
 908				var del = m.CreateDelegate(Expression.GetFuncType(types.Concat(types.Take(1)).ToArray()));
 909				AutoMappers.Add(key, del);
 910				return del;
 911			}
 912			finally
 913			{
 914				RWLock.ExitWriteLock();
 915			}
 916		}
 917
 918		// Find the split point in a result set for two different pocos and return the poco factory for the first
 919		Delegate FindSplitPoint(Type typeThis, Type typeNext, string sql, IDataReader r, ref int pos)
 920		{
 921			// Last?
 922			if (typeNext == null)
 923				return PocoData.ForType(typeThis).GetFactory(sql, _sharedConnection.ConnectionString, ForceDateTimesToUtc, pos, r.FieldCount - pos, r);
 924
 925			// Get PocoData for the two types
 926			PocoData pdThis = PocoData.ForType(typeThis);
 927			PocoData pdNext = PocoData.ForType(typeNext);
 928
 929			// Find split point
 930			int firstColumn = pos;
 931			var usedColumns = new Dictionary<string, bool>();
 932			for (; pos < r.FieldCount; pos++)
 933			{
 934				// Split if field name has already been used, or if the field doesn't exist in current poco but does in the next
 935				string fieldName = r.GetName(pos);
 936				if (usedColumns.ContainsKey(fieldName) || (!pdThis.Columns.ContainsKey(fieldName) && pdNext.Columns.ContainsKey(fieldName)))
 937				{
 938					return pdThis.GetFactory(sql, _sharedConnection.ConnectionString, ForceDateTimesToUtc, firstColumn, pos - firstColumn, r);
 939				}
 940				usedColumns.Add(fieldName, true);
 941			}
 942
 943			throw new InvalidOperationException(string.Format("Couldn't find split point between {0} and {1}", typeThis, typeNext));
 944		}
 945
 946		// Instance data used by the Multipoco factory delegate - essentially a list of the nested poco factories to call
 947		class MultiPocoFactory
 948		{
 949			public List<Delegate> m_Delegates;
 950			public Delegate GetItem(int index) { return m_Delegates[index]; }
 951		}
 952
 953		// Create a multi-poco factory
 954		Func<IDataReader, object, TRet> CreateMultiPocoFactory<TRet>(Type[] types, string sql, IDataReader r)
 955		{
 956			var m = new DynamicMethod("petapoco_multipoco_factory", typeof(TRet), new Type[] { typeof(MultiPocoFactory), typeof(IDataReader), typeof(object) }, typeof(MultiPocoFactory));
 957			var il = m.GetILGenerator();
 958
 959			// Load the callback
 960			il.Emit(OpCodes.Ldarg_2);
 961
 962			// Call each delegate
 963			var dels = new List<Delegate>();
 964			int pos = 0;
 965			for (int i=0; i<types.Length; i++)
 966			{
 967				// Add to list of delegates to call
 968				var del = FindSplitPoint(types[i], i + 1 < types.Length ? types[i + 1] : null, sql, r, ref pos);
 969				dels.Add(del);
 970
 971				// Get the delegate
 972				il.Emit(OpCodes.Ldarg_0);													// callback,this
 973				il.Emit(OpCodes.Ldc_I4, i);													// callback,this,Index
 974				il.Emit(OpCodes.Callvirt, typeof(MultiPocoFactory).GetMethod("GetItem"));	// callback,Delegate
 975				il.Emit(OpCodes.Ldarg_1);													// callback,delegate, datareader
 976
 977				// Call Invoke
 978				var tDelInvoke = del.GetType().GetMethod("Invoke");
 979				il.Emit(OpCodes.Callvirt, tDelInvoke);										// Poco left on stack
 980			}
 981
 982			// By now we should have the callback and the N pocos all on the stack.  Call the callback and we're done
 983			il.Emit(OpCodes.Callvirt, Expression.GetFuncType(types.Concat(new Type[] { typeof(TRet) }).ToArray()).GetMethod("Invoke"));
 984			il.Emit(OpCodes.Ret);
 985
 986			// Finish up
 987			return (Func<IDataReader, object, TRet>)m.CreateDelegate(typeof(Func<IDataReader, object, TRet>), new MultiPocoFactory() { m_Delegates = dels });
 988		}
 989
 990		// Various cached stuff
 991		static Dictionary<string, object> MultiPocoFactories = new Dictionary<string, object>();
 992		static Dictionary<string, object> AutoMappers = new Dictionary<string, object>();
 993		static System.Threading.ReaderWriterLockSlim RWLock = new System.Threading.ReaderWriterLockSlim();
 994
 995		// Get (or create) the multi-poco factory for a query
 996		Func<IDataReader, object, TRet> GetMultiPocoFactory<TRet>(Type[] types, string sql, IDataReader r)
 997		{
 998			// Build a key string  (this is crap, should address this at some point)
 999			var kb = new StringBuilder();
1000			kb.Append(typeof(TRet).ToString());
1001			kb.Append(":");
1002			foreach (var t in types)
1003			{
1004				kb.Append(":");
1005				kb.Append(t.ToString());
1006			}
1007			kb.Append(":"); kb.Append(_sharedConnection.ConnectionString);
1008			kb.Append(":"); kb.Append(ForceDateTimesToUtc);
1009			kb.Append(":"); kb.Append(sql);
1010			string key = kb.ToString();
1011
1012			// Check cache
1013			RWLock.EnterReadLock();
1014			try
1015			{
1016				object oFactory;
1017				if (MultiPocoFactories.TryGetValue(key, out oFactory))
1018					return (Func<IDataReader, object, TRet>)oFactory;
1019			}
1020			finally
1021			{
1022				RWLock.ExitReadLock();
1023			}
1024
1025			// Cache it
1026			RWLock.EnterWriteLock();
1027			try
1028			{
1029				// Check again
1030				object oFactory;
1031				if (MultiPocoFactories.TryGetValue(key, out oFactory))
1032					return (Func<IDataReader, object, TRet>)oFactory;
1033
1034				// Create the factory
1035				var Factory = CreateMultiPocoFactory<TRet>(types, sql, r);
1036
1037				MultiPocoFactories.Add(key, Factory);
1038				return Factory;
1039			}
1040			finally
1041			{
1042				RWLock.ExitWriteLock();
1043			}
1044
1045		}
1046
1047		// Actual implementation of the multi-poco query
1048		public IEnumerable<TRet> Query<TRet>(Type[] types, object cb, string sql, params object[] args)
1049		{
1050			OpenSharedConnection();
1051			try
1052			{
1053				using (var cmd = CreateCommand(_sharedConnection, sql, args))
1054				{
1055					IDataReader r;
1056					try
1057					{
1058						r = cmd.ExecuteReader();
1059						OnExecutedCommand(cmd);
1060					}
1061					catch (Exception x)
1062					{
1063						OnException(x);
1064						throw;
1065					}
1066					var factory = GetMultiPocoFactory<TRet>(types, sql, r);
1067					if (cb == null)
1068						cb = GetAutoMapper(types.ToArray());
1069					bool bNeedTerminator=false;
1070					using (r)
1071					{
1072						while (true)
1073						{
1074							TRet poco;
1075							try
1076							{
1077								if (!r.Read())
1078									break;
1079								poco = factory(r, cb);
1080							}
1081							catch (Exception x)
1082							{
1083								OnException(x);
1084								throw;
1085							}
1086
1087							if (poco != null)
1088								yield return poco;
1089							else
1090								bNeedTerminator = true;
1091						}
1092						if (bNeedTerminator)
1093						{
1094							var poco = (TRet)(cb as Delegate).DynamicInvoke(new object[types.Length]);
1095							if (poco != null)
1096								yield return poco;
1097							else
1098								yield break;
1099						}
1100					}
1101				}
1102			}
1103			finally
1104			{
1105				CloseSharedConnection();
1106			}
1107		}
1108
1109			
1110		public IEnumerable<T> Query<T>(Sql sql) 
1111		{
1112			return Query<T>(sql.SQL, sql.Arguments);
1113		}
1114
1115		public bool Exists<T>(object primaryKey) 
1116		{
1117			return FirstOrDefault<T>(string.Format("WHERE {0}=@0", EscapeSqlIdentifier(PocoData.ForType(typeof(T)).TableInfo.PrimaryKey)), primaryKey) != null;
1118		}
1119		public T Single<T>(object primaryKey) 
1120		{
1121			return Single<T>(string.Format("WHERE {0}=@0", EscapeSqlIdentifier(PocoData.ForType(typeof(T)).TableInfo.PrimaryKey)), primaryKey);
1122		}
1123		public T SingleOrDefault<T>(object primaryKey) 
1124		{
1125			return SingleOrDefault<T>(string.Format("WHERE {0}=@0", EscapeSqlIdentifier(PocoData.ForType(typeof(T)).TableInfo.PrimaryKey)), primaryKey);
1126		}
1127		public T Single<T>(string sql, params object[] args) 
1128		{
1129			return Query<T>(sql, args).Single();
1130		}
1131		public T SingleOrDefault<T>(string sql, params object[] args) 
1132		{
1133			return Query<T>(sql, args).SingleOrDefault();
1134		}
1135		public T First<T>(string sql, params object[] args) 
1136		{
1137			return Query<T>(sql, args).First();
1138		}
1139		public T FirstOrDefault<T>(string sql, params object[] args) 
1140		{
1141			return Query<T>(sql, args).FirstOrDefault();
1142		}
1143
1144		public T Single<T>(Sql sql) 
1145		{
1146			return Query<T>(sql).Single();
1147		}
1148		public T SingleOrDefault<T>(Sql sql) 
1149		{
1150			return Query<T>(sql).SingleOrDefault();
1151		}
1152		public T First<T>(Sql sql) 
1153		{
1154			return Query<T>(sql).First();
1155		}
1156		public T FirstOrDefault<T>(Sql sql) 
1157		{
1158			return Query<T>(sql).FirstOrDefault();
1159		}
1160
1161		public string EscapeTableName(string str)
1162		{
1163			// Assume table names with "dot" are already escaped
1164			return str.IndexOf('.') >= 0 ? str : EscapeSqlIdentifier(str);
1165		}
1166		public string EscapeSqlIdentifier(string str)
1167		{
1168			switch (_dbType)
1169			{
1170				case DBType.MySql:
1171					return string.Format("`{0}`", str);
1172
1173				case DBType.PostgreSQL:
1174					return string.Format("\"{0}\"", str);
1175
1176				case DBType.Oracle:
1177					return string.Format("\"{0}\"", str.ToUpperInvariant());
1178
1179				default:
1180					return string.Format("[{0}]", str);
1181			}
1182		}
1183
1184		public object Insert(string tableName, string primaryKeyName, object poco)
1185		{
1186			return Insert(tableName, primaryKeyName, true, poco);
1187		}
1188
1189		// Insert a poco into a table.  If the poco has a property with the same name 
1190		// as the primary key the id of the new record is assigned to it.  Either way,
1191		// the new id is returned.
1192		public object Insert(string tableName, string primaryKeyName, bool autoIncrement, object poco)
1193		{
1194			try
1195			{
1196				OpenSharedConnection();
1197				try
1198				{
1199					using (var cmd = CreateCommand(_sharedConnection, ""))
1200					{
1201						var pd = PocoData.ForObject(poco, primaryKeyName);
1202						var names = new List<string>();
1203						var values = new List<string>();
1204						var index = 0;
1205						foreach (var i in pd.Columns)
1206						{
1207							// Don't insert result columns
1208							if (i.Value.ResultColumn)
1209								continue;
1210
1211							// Don't insert the primary key (except under oracle where we need bring in the next sequence value)
1212							if (autoIncrement && primaryKeyName != null && string.Compare(i.Key, primaryKeyName, true)==0)
1213							{
1214								if (_dbType == DBType.Oracle && !string.IsNullOrEmpty(pd.TableInfo.SequenceName))
1215								{
1216									names.Add(i.Key);
1217									values.Add(string.Format("{0}.nextval", pd.TableInfo.SequenceName));
1218								}
1219								continue;
1220							}
1221
1222							names.Add(EscapeSqlIdentifier(i.Key));
1223							values.Add(string.Format("{0}{1}", _paramPrefix, index++));
1224							AddParam(cmd, i.Value.GetValue(poco), _paramPrefix);
1225						}
1226
1227						cmd.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2})",
1228								EscapeTableName(tableName),
1229								string.Join(",", names.ToArray()),
1230								string.Join(",", values.ToArray())
1231								);
1232
1233						if (!autoIncrement)
1234						{
1235							DoPreExecute(cmd);
1236							cmd.ExecuteNonQuery();
1237							OnExecutedCommand(cmd);
1238							return true;
1239						}
1240
1241
1242						object id;
1243						switch (_dbType)
1244						{
1245							case DBType.SqlServerCE:
1246								DoPreExecute(cmd);
1247								cmd.ExecuteNonQuery();
1248								OnExecutedCommand(cmd);
1249								id = ExecuteScalar<object>("SELECT @@@IDENTITY AS NewID;");
1250								break;
1251							case DBType.SqlServer:
1252								cmd.CommandText += ";\nSELECT SCOPE_IDENTITY() AS NewID;";
1253								DoPreExecute(cmd);
1254								id = cmd.ExecuteScalar();
1255								OnExecutedCommand(cmd);
1256								break;
1257							case DBType.PostgreSQL:
1258								if (primaryKeyName != null)
1259								{
1260									cmd.CommandText += string.Format("returning {0} as NewID", EscapeSqlIdentifier(primaryKeyName));
1261									DoPreExecute(cmd);
1262									id = cmd.ExecuteScalar();
1263								}
1264								else
1265								{
1266									id = -1;
1267									DoPreExecute(cmd);
1268									cmd.ExecuteNonQuery();
1269								}
1270								OnExecutedCommand(cmd);
1271								break;
1272							case DBType.Oracle:
1273								if (primaryKeyName != null)
1274								{
1275									cmd.CommandText += string.Format(" returning {0} into :newid", EscapeSqlIdentifier(primaryKeyName));
1276									var param = cmd.CreateParameter();
1277									param.ParameterName = ":newid";
1278									param.Value = DBNull.Value;
1279									param.Direction = ParameterDirection.ReturnValue;
1280									param.DbType = DbType.Int64;
1281									cmd.Parameters.Add(param);
1282									DoPreExecute(cmd);
1283									cmd.ExecuteNonQuery();
1284									id = param.Value;
1285								}
1286								else
1287								{
1288									id = -1;
1289									DoPreExecute(cmd);
1290									cmd.ExecuteNonQuery();
1291								}
1292								OnExecutedCommand(cmd);
1293								break;
1294                            case DBType.SQLite:
1295                                if (primaryKeyName != null)
1296                                {
1297                                    cmd.CommandText += ";\nSELECT last_insert_rowid();";
1298                                    DoPreExecute(cmd);
1299                                    id = cmd.ExecuteScalar();
1300                                }
1301                                else
1302                                {
1303                                    id = -1;
1304                                    DoPreExecute(cmd);
1305                                    cmd.ExecuteNonQuery();
1306                                }
1307                                OnExecutedCommand(cmd);
1308                                break;
1309							default:
1310								cmd.CommandText += ";\nSELECT @@IDENTITY AS NewID;";
1311								DoPreExecute(cmd);
1312								id = cmd.ExecuteScalar();
1313								OnExecutedCommand(cmd);
1314								break;
1315						}
1316
1317
1318						// Assign the ID back to the primary key property
1319						if (primaryKeyName != null)
1320						{
1321							PocoColumn pc;
1322							if (pd.Columns.TryGetValue(primaryKeyName, out pc))
1323							{
1324								pc.SetValue(poco, pc.ChangeType(id));
1325							}
1326						}
1327
1328						return id;
1329					}
1330				}
1331				finally
1332				{
1333					CloseSharedConnection();
1334				}
1335			}
1336			catch (Exception x)
1337			{
1338				OnException(x);
1339				throw;
1340			}
1341		}
1342
1343		// Insert an annotated poco object
1344		public object Insert(object poco)
1345		{
1346			var pd = PocoData.ForType(poco.GetType());
1347			return Insert(pd.TableInfo.TableName, pd.TableInfo.PrimaryKey, pd.TableInfo.AutoIncrement, poco);
1348		}
1349
1350		public int Update(string tableName, string primaryKeyName, object poco, object primaryKeyValue)
1351		{
1352			return Update(tableName, primaryKeyName, poco, primaryKeyValue, null);
1353		}
1354
1355
1356		// Update a record with values from a poco.  primary key value can be either supplied or read from the poco
1357		public int Update(string tableName, string primaryKeyName, object poco, object primaryKeyValue, IEnumerable<string> columns)
1358		{
1359			try
1360			{
1361				OpenSharedConnection();
1362				try
1363				{
1364					using (var cmd = CreateCommand(_sharedConnection, ""))
1365					{
1366						var sb = new StringBuilder();
1367						var index = 0;
1368						var pd = PocoData.ForObject(poco,primaryKeyName);
1369						if (columns == null)
1370						{
1371							foreach (var i in pd.Columns)
1372							{
1373								// Don't update the primary key, but grab the value if we don't have it
1374								if (string.Compare(i.Key, primaryKeyName, true) == 0)
1375								{
1376									if (primaryKeyValue == null)
1377										primaryKeyValue = i.Value.GetValue(poco);
1378									continue;
1379								}
1380
1381								// Dont update result only columns
1382								if (i.Value.ResultColumn)
1383									continue;
1384
1385								// Build the sql
1386								if (index > 0)
1387									sb.Append(", ");
1388								sb.AppendFormat("{0} = {1}{2}", EscapeSqlIdentifier(i.Key), _paramPrefix, index++);
1389
1390								// Store the parameter in the command
1391								AddParam(cmd, i.Value.GetValue(poco), _paramPrefix);
1392							}
1393						}
1394						else
1395						{
1396							foreach (var colname in columns)
1397							{
1398								var pc = pd.Columns[colname];
1399
1400								// Build the sql
1401								if (index > 0)
1402									sb.Append(", ");
1403								sb.AppendFormat("{0} = {1}{2}", EscapeSqlIdentifier(colname), _paramPrefix, index++);
1404
1405								// Store the parameter in the command
1406								AddParam(cmd, pc.GetValue(poco), _paramPrefix);
1407							}
1408
1409							// Grab primary key value
1410							if (primaryKeyValue == null)
1411							{
1412								var pc = pd.Columns[primaryKeyName];
1413								primaryKeyValue = pc.GetValue(poco);
1414							}
1415
1416						}
1417
1418						cmd.CommandText = string.Format("UPDATE {0} SET {1} WHERE {2} = {3}{4}",
1419											EscapeTableName(tableName), sb.ToString(), EscapeSqlIdentifier(primaryKeyName), _paramPrefix, index++);
1420						AddParam(cmd, primaryKeyValue, _paramPrefix);
1421
1422						DoPreExecute(cmd);
1423
1424						// Do it
1425						var retv=cmd.ExecuteNonQuery();
1426						OnExecutedCommand(cmd);
1427						return retv;
1428					}
1429				}
1430				finally
1431				{
1432					CloseSharedConnection();
1433				}
1434			}
1435			catch (Exception x)
1436			{
1437				OnException(x);
1438				throw;
1439			}
1440		}
1441
1442		public int Update(string tableName, string primaryKeyName, object poco)
1443		{
1444			return Update(tableName, primaryKeyName, poco, null);
1445		}
1446
1447		public int Update(string tableName, string primaryKeyName, object poco, IEnumerable<string> columns)
1448		{
1449			return Update(tableName, primaryKeyName, poco, null, columns);
1450		}
1451
1452		public int Update(object poco, IEnumerable<string> columns)
1453		{
1454			return Update(poco, null, columns);
1455		}
1456
1457		public int Update(object poco)
1458		{
1459			return Update(poco, null, null);
1460		}
1461
1462		public int Update(object poco, object primaryKeyValue)
1463		{
1464			return Update(poco, primaryKeyValue, null);
1465		}
1466		public int Update(object poco, object primaryKeyValue, IEnumerable<string> columns)
1467		{
1468			var pd = PocoData.ForType(poco.GetType());
1469			return Update(pd.TableInfo.TableName, pd.TableInfo.PrimaryKey, poco, primaryKeyValue, columns);
1470		}
1471
1472		public int Update<T>(string sql, params object[] args)
1473		{
1474			var pd = PocoData.ForType(typeof(T));
1475			return Execute(string.Format("UPDATE {0} {1}", EscapeTableName(pd.TableInfo.TableName), sql), args);
1476		}
1477
1478		public int Update<T>(Sql sql)
1479		{
1480			var pd = PocoData.ForType(typeof(T));
1481			return Execute(new Sql(string.Format("UPDATE {0}", EscapeTableName(pd.TableInfo.TableName))).Append(sql));
1482		}
1483
1484		public int Delete(string tableName, string primaryKeyName, object poco)
1485		{
1486			return Delete(tableName, primaryKeyName, poco, null);
1487		}
1488
1489		public int Delete(string tableName, string primaryKeyName, object poco, object primaryKeyValue)
1490		{
1491			// If primary key value not specified, pick it up from the object
1492			if (primaryKeyValue == null)
1493			{
1494				var pd = PocoData.ForObject(poco,primaryKeyName);
1495				PocoColumn pc;
1496				if (pd.Columns.TryGetValue(primaryKeyName, out pc))
1497				{
1498					primaryKeyValue = pc.GetValue(poco);
1499				}
1500			}
1501
1502			// Do it
1503			var sql = string.Format("DELETE FROM {0} WHERE {1}=@0", EscapeTableName(tableName), EscapeSqlIdentifier(primaryKeyName));
1504			return Execute(sql, primaryKeyValue);
1505		}
1506
1507		public int Delete(object poco)
1508		{
1509			var pd = PocoData.ForType(poco.GetType());
1510			return Delete(pd.TableInfo.TableName, pd.TableInfo.PrimaryKey, poco);
1511		}
1512
1513		public int Delete<T>(object pocoOrPrimaryKey)
1514		{
1515			if (pocoOrPrimaryKey.GetType() == typeof(T))
1516				return Delete(pocoOrPrimaryKey);
1517			var pd = PocoData.ForType(typeof(T));
1518			return Delete(pd.TableInfo.TableName, pd.TableInfo.PrimaryKey, null, pocoOrPrimaryKey);
1519		}
1520
1521		public int Delete<T>(string sql, params object[] args)
1522		{
1523			var pd = PocoData.ForType(typeof(T));
1524			return Execute(string.Format("DELETE FROM {0} {1}", EscapeTableName(pd.TableInfo.TableName), sql), args);
1525		}
1526
1527		public int Delete<T>(Sql sql)
1528		{
1529			var pd = PocoData.ForType(typeof(T));
1530			return Execute(new Sql(string.Format("DELETE FROM {0}", EscapeTableName(pd.TableInfo.TableName))).Append(sql));
1531		}
1532
1533		// Check if a poco represents a new record
1534		public bool IsNew(string primaryKeyName, object poco)
1535		{
1536			var pd = PocoData.ForObject(poco, primaryKeyName);
1537			object pk;
1538			PocoColumn pc;
1539			if (pd.Columns.TryGetValue(primaryKeyName, out pc))
1540			{
1541				pk = pc.GetValue(poco);
1542			}
1543#if !PETAPOCO_NO_DYNAMIC
1544			else if (poco.GetType() == typeof(System.Dynamic.ExpandoObject))
1545			{
1546				return true;
1547			}
1548#endif
1549			else
1550			{
1551				var pi = poco.GetType().GetProperty(primaryKeyName);
1552				if (pi == null)
1553					throw new ArgumentException(string.Format("The object doesn't have a property matching the primary key column name '{0}'", primaryKeyName));
1554				pk = pi.GetValue(poco, null);
1555			}
1556
1557			if (pk == null)
1558				return true;
1559
1560			var type = pk.GetType();
1561
1562			if (type.IsValueType)
1563			{
1564				// Common primary key types
1565				if (type == typeof(long))
1566					return (long)pk == 0;
1567				else if (type == typeof(ulong))
1568					return (ulong)pk == 0;
1569				else if (type == typeof(int))
1570					return (int)pk == 0;
1571				else if (type == typeof(uint))
1572					return (uint)pk == 0;
1573
1574				// Create a default instance and compare
1575				return pk == Activator.CreateInstance(pk.GetType());
1576			}
1577			else
1578			{
1579				return pk == null;
1580			}
1581		}
1582
1583		public bool IsNew(object poco)
1584		{
1585			var pd = PocoData.ForType(poco.GetType());
1586			if (!pd.TableInfo.AutoIncrement)
1587				throw new InvalidOperationException("IsNew() and Save() are only supported on tables with auto-increment/identity primary key columns");
1588			return IsNew(pd.TableInfo.PrimaryKey, poco);
1589		}
1590
1591		// Insert new record or Update existing record
1592		public void Save(string tableName, string primaryKeyName, object poco)
1593		{
1594			if (IsNew(primaryKeyName, poco))
1595			{
1596				Insert(tableName, primaryKeyName, true, poco);
1597			}
1598			else
1599			{
1600				Update(tableName, pri…