/PetaPoco/PetaPoco.cs
C# | 2329 lines | 1840 code | 298 blank | 191 comment | 339 complexity | 40ba2c0e1aae7c23f0034aea3c16481d MD5 | raw file
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…
Large files files are truncated, but you can click here to view the full file