1 #region Copyright © 2003-2008 Richard Beauchamp <rbeauchamp@gmail.com> 2 3 /* 4 * DomainObjects for .NET 5 * Copyright © 2003-2008 Richard Beauchamp 6 * 7 * This library is free software; you can redistribute it and/or 8 * modify it under the terms of the GNU Lesser General Public 9 * License as published by the Free Software Foundation; either 10 * version 2.1 of the License, or (at your option) any later version. 11 * 12 * This library is distributed in the hope that it will be useful, 13 * but WITHOUT ANY WARRANTY; without even the implied warranty of 14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 15 * Lesser General Public License for more details. 16 * 17 * You should have received a copy of the GNU Lesser General Public 18 * License along with this library; if not, write to the Free Software 19 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA 20 */ 21 22 #endregion 23 24 using System; 25 using System.Data; 26 using System.Linq; 27 using DomainObjects.Diagnostics; 28 using DomainObjects.Facade.Command; 29 using DomainObjects.Facade.Command.Expression; 30 using DomainObjects.Facade.Command.Function; 31 using DomainObjects.Facade.Test; 32 using DomainObjects.Test.Domain; 33 using NUnit.Framework; 34 35 namespace DomainObjects.Test.TestFixture 36 { 37 [TestFixture] 38 public class FunctionTests : DomainObjectsTestFixture 39 { 40 #region Case Function Tests 41 42 /// <summary> 43 /// Tests that the SQL CASE function is correctly 44 /// generated. Should generate a SELECT statement 45 /// that looks something like: 46 /// 47 /// SELECT 48 /// CASE 49 /// WHEN (PERSON01.FIRSTNAME = 'albert') THEN 'Found albert' 50 /// WHEN (PERSON01.FIRSTNAME = 'tom') THEN 'Found tom' 51 /// ELSE 'Did not find tom or albert' 52 /// END AS FirstName 53 /// FROM PERSON AS PERSON01 54 /// WHERE ((PERSON01.FIRSTNAME = 'albert' 55 /// OR (PERSON01.FIRSTNAME = 'tom'))) 56 /// ORDER BY 1 57 /// </summary> 58 [Test] 59 public void Case() 60 { 61 // create an empty query 62 Query<Person> query = new Query<Person>(); 63 64 // create a case instance 65 Case searchedCase = new Case(); 66 67 // define the first case 68 Criteria caseWhen = new Criteria(); 69 caseWhen.AddEqualTo(Types.Person.Field._firstname, "albert"); 70 // add the WHEN and THEN case 71 searchedCase.AddCase(caseWhen, "Found albert"); 72 73 // define the second case 74 Criteria caseWhen2 = new Criteria(); 75 caseWhen2.AddEqualTo(Types.Person.Field._firstname, "tom"); 76 // add the WHEN and THEN case 77 searchedCase.AddCase(caseWhen2, "Found tom"); 78 79 // add an else 80 searchedCase.AddElse("Did not find tom or albert"); 81 82 // add the case as a select field 83 query.AddSelectField(searchedCase.As("FirstName")); 84 85 // add a criteria to only return the rows for albert and tom 86 Criteria whereCriteria = new Criteria(); 87 whereCriteria.AddEqualTo(Types.Person.Field._firstname, "albert"); 88 Criteria whereCriteria2 = new Criteria(); 89 whereCriteria2.AddEqualTo(Types.Person.Field._firstname, "tom"); 90 whereCriteria.AddOrCriteria(whereCriteria2); 91 92 // order by the case function 93 whereCriteria.AddOrderBy(searchedCase); 94 95 // set the where criteria 96 query.Where = whereCriteria; 97 98 DataSet dataSet = query.FindDataSet(); 99 100 // the case function should have changed the return value 101 // from 'albert' to 'Found albert' for the first row 102 Assert.AreEqual("Found albert", dataSet.Tables[0].Rows[0]["FirstName"]); 103 104 // the case function should have changed the return value 105 // from 'tom' to 'Found tom' for the second row 106 Assert.AreEqual("Found tom", dataSet.Tables[0].Rows[1]["FirstName"]); 107 } 108 109 [Test] 110 public void CaseWithNull() 111 { 112 // create an empty query 113 Query<Person> query = new Query<Person>(); 114 115 // create a case instance 116 Case searchedCase = new Case(); 117 118 // define the first case 119 Criteria caseWhen = new Criteria(); 120 caseWhen.AddEqualTo(Types.Person.Field._firstname, "albert"); 121 // add the WHEN and THEN case 122 searchedCase.AddCase(caseWhen, Null.Instance); 123 124 // define the second case 125 Criteria caseWhen2 = new Criteria(); 126 caseWhen2.AddEqualTo(Types.Person.Field._firstname, "tom"); 127 // add the WHEN and THEN case 128 searchedCase.AddCase(caseWhen2, Null.Instance); 129 130 // add an else 131 searchedCase.AddElse("Did not find tom or albert"); 132 133 // add the case as a select field 134 query.AddSelectField(searchedCase.As("FirstName")); 135 136 // add a criteria to only return the rows for albert and tom 137 Criteria whereCriteria = new Criteria(); 138 whereCriteria.AddEqualTo(Types.Person.Field._firstname, "albert"); 139 Criteria whereCriteria2 = new Criteria(); 140 whereCriteria2.AddEqualTo(Types.Person.Field._firstname, "tom"); 141 whereCriteria.AddOrCriteria(whereCriteria2); 142 143 // order by the case function 144 whereCriteria.AddOrderBy(searchedCase); 145 146 // set the where criteria 147 query.Where = whereCriteria; 148 149 DataSet dataSet = query.FindDataSet(); 150 151 // the case function should have changed the return value 152 // from 'albert' to 'Found albert' for the first row 153 Assert.AreEqual(Convert.DBNull, dataSet.Tables[0].Rows[0]["FirstName"]); 154 155 // the case function should have changed the return value 156 // from 'tom' to 'Found tom' for the second row 157 Assert.AreEqual(Convert.DBNull, dataSet.Tables[0].Rows[1]["FirstName"]); 158 } 159 160 /// <summary> 161 /// Validate that an integer value is returned 162 /// by the case statement when specified in the 163 /// 'then' parameter of AddCase(). 164 /// </summary> 165 [Test] 166 public void CaseWithAnIntegerValueForThen() 167 { 168 // create an empty query 169 Query<Person> query = new Query<Person>(); 170 171 // create a case instance 172 Case searchedCase = new Case(); 173 174 // define the first case 175 Criteria caseWhen = new Criteria(); 176 caseWhen.AddEqualTo(Types.Person.Field._firstname, "albert"); 177 // add the WHEN and THEN case 178 searchedCase.AddCase(caseWhen, 1); 179 180 // define the second case 181 Criteria caseWhen2 = new Criteria(); 182 caseWhen2.AddEqualTo(Types.Person.Field._firstname, "tom"); 183 // add the WHEN and THEN case 184 searchedCase.AddCase(caseWhen2, 2); 185 186 // add an else 187 searchedCase.AddElse(3); 188 189 // add the case as a select field 190 query.AddSelectField(searchedCase.As("FirstNameToInteger")); 191 192 // add a criteria to only return the rows for albert and tom 193 Criteria whereCriteria = new Criteria(); 194 whereCriteria.AddEqualTo(Types.Person.Field._firstname, "albert"); 195 Criteria whereCriteria2 = new Criteria(); 196 whereCriteria2.AddEqualTo(Types.Person.Field._firstname, "tom"); 197 whereCriteria.AddOrCriteria(whereCriteria2); 198 199 // order by the case function 200 whereCriteria.AddOrderBy(searchedCase); 201 202 // set the where criteria 203 query.Where = whereCriteria; 204 205 DataSet dataSet = query.FindDataSet(); 206 207 //Comments: Access returns 1 as string. dkondratiuk 208 209 // the case function should have changed the return value 210 // from 'albert' to the integer 1 for the first row 211 Assert.AreEqual("1", dataSet.Tables[0].Rows[0]["FirstNameToInteger"].ToString()); 212 213 // the case function should have changed the return value 214 // from 'tom' to the integer 2 for the second row 215 Assert.AreEqual("2", dataSet.Tables[0].Rows[1]["FirstNameToInteger"].ToString()); 216 } 217 218 #endregion 219 220 #region Count Function Tests 221 222 /// <summary> 223 /// Test that the order by is ignored when doing a count. 224 /// </summary> 225 [Test] 226 public void Count_WithAnOrderBy() 227 { 228 Criteria criteria = new Criteria(); 229 criteria.AddEqualTo(Types.Product._productCategory.Field._name, "Dairy Products"); 230 criteria.AddOrderBy(Types.Product.Field._name); 231 232 int count = QueryFacade.GetCount<Product>(criteria); 233 234 Assert.AreEqual(16, count, "The correct count was returned."); 235 } 236 237 [Test] 238 public void Count_WithBetweenCriteria() 239 { 240 Criteria criteria = new Criteria(); 241 criteria.AddBetween(Types.Person.Field._primaryKey, 1, 5); 242 243 int count = QueryFacade.GetCount<Person>(criteria); 244 Assert.AreEqual(5, count); 245 } 246 247 #endregion 248 249 [Test] 250 public void Average() 251 { 252 Criteria criteria = new Criteria(); 253 criteria.AddEqualTo(Types.Person.Field._firstname, "tom"); 254 255 DataSet dataSet = QueryFacade.FindDataSet<Person>(criteria, Types.Person.Field._primaryKey.Average().As("AveragePrimaryKey")); 256 257 Assert.AreEqual(6m, dataSet.Tables[0].Rows[0]["AveragePrimaryKey"]); 258 } 259 260 /// <summary> 261 /// Validates that a Sum function of a Case function 262 /// generates the correct SQL statement. 263 /// Access 2000 does not support the distinct keyword inside a sum function. 264 /// </summary> 265 [Test] 266 [Category(UnitTestCategory.DoesNotWorkWithAccess)] 267 public void SumOfCaseFunction() 268 { 269 // create an empty query 270 Query<Person> query = new Query<Person>(); 271 272 // create a case instance 273 Case searchedCase = new Case(); 274 275 // define the first case 276 Criteria caseWhen = new Criteria(); 277 caseWhen.AddEqualTo(Types.Person.Field._firstname, "albert"); 278 // add the WHEN and THEN case 279 searchedCase.AddCase(caseWhen, 1); 280 281 // define the second case 282 Criteria caseWhen2 = new Criteria(); 283 caseWhen2.AddEqualTo(Types.Person.Field._firstname, "tom"); 284 // add the WHEN and THEN case 285 searchedCase.AddCase(caseWhen2, 2); 286 287 // add an else 288 searchedCase.AddElse(3); 289 290 // add the sum of the case as a select field 291 query.AddSelectField(searchedCase.Sum(false).As("FirstNameToSumOfInteger")); 292 293 // add a criteria to only return the rows for albert and tom 294 Criteria whereCriteria = new Criteria(); 295 whereCriteria.AddEqualTo(Types.Person.Field._firstname, "albert"); 296 Criteria whereCriteria2 = new Criteria(); 297 whereCriteria2.AddEqualTo(Types.Person.Field._firstname, "tom"); 298 whereCriteria.AddOrCriteria(whereCriteria2); 299 300 // group by the first name so that we can sum the case statements 301 whereCriteria.AddGroupBy(Types.Person.Field._firstname); 302 303 // order by the case function 304 whereCriteria.AddOrderBy(searchedCase); 305 306 // set the where criteria 307 query.Where = whereCriteria; 308 309 DataSet dataSet = query.FindDataSet(); 310 311 // the case function should have changed the return value 312 // from 'albert' to the integer 1 for the first row 313 Assert.AreEqual(1, dataSet.Tables[0].Rows[0]["FirstNameToSumOfInteger"]); 314 315 // the case function should have changed the return value 316 // from 'tom' to the integer 2 for the second row 317 Assert.AreEqual(4, dataSet.Tables[0].Rows[1]["FirstNameToSumOfInteger"]); 318 } 319 320 /// <summary> 321 /// Validate that the same constant value can be added 322 /// multiple times to a query without causing an exception. 323 /// Validates fix for bug [ 1537678 ] Exception while utilizing constant with same value in Case. 324 /// </summary> 325 [Test] 326 public void UtilizeSameConstantValueTwiceInQuery() 327 { 328 // create an empty query 329 Query<Person> query = new Query<Person>(); 330 331 // create a case instance 332 Case searchedCase = new Case(); 333 334 // define the first case 335 Criteria caseWhen = new Criteria(); 336 caseWhen.AddEqualTo(Types.Person.Field._firstname, "albert"); 337 // add the WHEN and THEN case 338 searchedCase.AddCase(caseWhen, 1); 339 340 // define the second case 341 Criteria caseWhen2 = new Criteria(); 342 caseWhen2.AddEqualTo(Types.Person.Field._firstname, "tom"); 343 // add the WHEN and THEN case 344 searchedCase.AddCase(caseWhen2, 1); 345 346 // add an else 347 searchedCase.AddElse(3); 348 349 // add the case as a select field 350 query.AddSelectField(searchedCase.As("FirstNameToInteger")); 351 352 // add a criteria to only return the rows for albert and tom 353 Criteria whereCriteria = new Criteria(); 354 whereCriteria.AddEqualTo(Types.Person.Field._firstname, "albert"); 355 Criteria whereCriteria2 = new Criteria(); 356 whereCriteria2.AddEqualTo(Types.Person.Field._firstname, "tom"); 357 whereCriteria.AddOrCriteria(whereCriteria2); 358 359 // order by the case function 360 whereCriteria.AddOrderBy(searchedCase); 361 362 // set the where criteria 363 query.Where = whereCriteria; 364 365 DataSet dataSet = query.FindDataSet(); 366 367 //Comments: Access returns 1 as string. dkondratiuk 368 369 // the case function should have changed the return value 370 // from 'albert' to the integer 1 for the first row 371 Assert.AreEqual("1", dataSet.Tables[0].Rows[0]["FirstNameToInteger"].ToString()); 372 373 // the case function should have changed the return value 374 // from 'tom' to the integer 1 for the second row 375 Assert.AreEqual("1", dataSet.Tables[0].Rows[1]["FirstNameToInteger"].ToString()); 376 } 377 378 [Test] 379 [Category(UnitTestCategory.SQLServerSpecific), Category(UnitTestCategory.DoesNotWorkWithOracle)] 380 public void AddBetweenWithGetDateFunction() 381 { 382 Criteria criteria = new Criteria(); 383 384 criteria.AddBetween(Types.LockedByVersion.Field._createdDate, DateTime.Now.AddYears(-10), new GetDate()); 385 386 QueryFacade.GetCount<LockedByVersion>(criteria); 387 } 388 389 [Test] 390 public void Max() 391 { 392 Criteria criteria = new Criteria(); 393 criteria.AddEqualTo(Types.Product._productCategory.Field._name, "Beverages"); 394 DataSet dataSet = QueryFacade.FindDataSet<Product>(criteria, Types.Product.Field._pricePerUnit.Max().As("MaxPricePerUnit")); 395 396 Assert.AreEqual(263.50m, dataSet.Tables[0].Rows[0]["MaxPricePerUnit"]); 397 } 398 399 [Test] 400 public void Sum() 401 { 402 Criteria criteria = new Criteria(); 403 criteria.AddEqualTo(Types.Product._productCategory.Field._name, "Dairy Products"); 404 DataSet dataSet = QueryFacade.FindDataSet<Product>(criteria, Types.Product.Field._pricePerUnit.Sum().As("SumPricePerUnit")); 405 406 Assert.AreEqual(416.24m, dataSet.Tables[0].Rows[0]["SumPricePerUnit"]); 407 } 408 409 [Test] 410 [Category(UnitTestCategory.DoesNotWorkWithOracle)] 411 public void DateDiff() 412 { 413 QueryFacade.FindDataSet<LockedByVersion>(null, new DateDiff(PartOfDateTime.Day, Types.LockedByVersion.Field._createdDate, new GetDate())); 414 } 415 416 [Test] 417 [Category(UnitTestCategory.DoesNotWorkWithOracle)] 418 public void DatePart() 419 { 420 // A first test verifying that the month is within the valid interval [1..12] 421 Criteria criteria = new Criteria(); 422 423 criteria.AddEqualTo(Types.LockedByVersion.Field._primaryKey, 1); 424 425 // The timestamp was set to the date and time when the row was actually created 426 DataSet dataSet = QueryFacade.FindDataSet<LockedByVersion>(criteria, Types.LockedByVersion.Field._createdDate.DatePart(PartOfDateTime.Month)); 427 428 Assert.IsNotNull(dataSet); 429 Assert.AreEqual(1, dataSet.Tables[0].Rows.Count); 430 431 int month = Convert.ToInt32(dataSet.Tables[0].Rows[0].ItemArray.GetValue(0)); 432 Assert.IsTrue(month >= 1 && month <= 12, "the month is within the valid range"); 433 434 // A second test verifying that the month is the expected one 435 criteria = new Criteria(); 436 437 criteria.AddEqualTo(Types.BaseTypeReference.Field._primaryKey, 1002); 438 439 // The timestamp was set to the date and time when the row was actually created 440 dataSet = QueryFacade.FindDataSet<BaseTypeReference>(criteria, Types.BaseTypeReference.Field._nullableType.DatePart(PartOfDateTime.Month)); 441 442 Assert.IsNotNull(dataSet); 443 Assert.AreEqual(1, dataSet.Tables[0].Rows.Count); 444 445 month = Convert.ToInt32(dataSet.Tables[0].Rows[0].ItemArray.GetValue(0)); 446 Assert.AreEqual(7, month, "the month was the expected one"); 447 } 448 449 [Test] 450 public void Upper() 451 { 452 Criteria criteria = new Criteria(); 453 criteria.AddEqualTo(Types.Product._productCategory.Field._name, "Beverages"); 454 DataSet dataSet = QueryFacade.FindDataSet<Product>(criteria, Types.Product._productCategory.Field._name.Upper().As("Category")); 455 456 Assert.AreEqual("BEVERAGES", dataSet.Tables[0].Rows[0]["Category"]); 457 } 458 459 [Test] 460 public void Lower() 461 { 462 Criteria criteria = new Criteria(); 463 criteria.AddEqualTo(Types.Product._productCategory.Field._name, "Beverages"); 464 DataSet dataSet = QueryFacade.FindDataSet<Product>(criteria, Types.Product._productCategory.Field._name.Lower().As("Category")); 465 466 Assert.AreEqual("beverages", dataSet.Tables[0].Rows[0]["Category"]); 467 } 468 469 [Test] 470 public void UpperInALikeExpr() 471 { 472 Criteria criteria = new Criteria(); 473 criteria.AddLike(Types.Product._productCategory.Field._name.Upper(), "BEVERAGES"); 474 DataSet dataSet = QueryFacade.FindDataSet<Product>(criteria, Types.Product._productCategory.Field._name.Upper().As("Category")); 475 476 Assert.AreEqual("BEVERAGES", dataSet.Tables[0].Rows[0]["Category"]); 477 } 478 479 [Test] 480 public void LowerInALikeExpr() 481 { 482 Criteria criteria = new Criteria(); 483 criteria.AddLike(Types.Product._productCategory.Field._name.Lower(), "beverages"); 484 DataSet dataSet = QueryFacade.FindDataSet<Product>(criteria, Types.Product._productCategory.Field._name.Lower().As("Category")); 485 486 Assert.AreEqual("beverages", dataSet.Tables[0].Rows[0]["Category"]); 487 } 488 489 /// <summary> 490 /// Tests ordering by the <see cref="NewId"/> function. 491 /// </summary> 492 [Category(UnitTestCategory.DoesNotWorkWithAccess)] 493 [Category(UnitTestCategory.DoesNotWorkWithOracle)] 494 [Category(UnitTestCategory.SQLServerSpecific)] 495 [Test] 496 public void OrderByNewId() 497 { 498 // Capture the SQL statement generated by the query 499 CommandMonitor commandMonitor = new CommandMonitor(); 500 501 // Create a new Query 502 Query<ProductCategory> query = new Query<ProductCategory>(); 503 504 // Order by the NewId function 505 query.Where.AddOrderBy(NewId.Instance); 506 507 // Execute the query 508 query.FindObjectSet(); 509 510 // Assert that the correct SQL statement was generated 511 Assert.IsTrue(commandMonitor.DatabaseEventArgList[0].CommandText.Contains("NEWID()")); 512 Assert.IsTrue(commandMonitor.DatabaseEventArgList[0].CommandText.Contains("ORDER BY 6")); 513 } 514 515 #region Convert Function Tests 516 517 [Category(UnitTestCategory.DoesNotWorkWithAccess)] 518 [Category(UnitTestCategory.DoesNotWorkWithOracle)] 519 [Category(UnitTestCategory.SQLServerSpecific)] 520 [Test] 521 public void ConvertTo() 522 { 523 Query<Product> productQuery = new Query<Product>(); 524 productQuery.Top = 1; 525 productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.VarChar, null, Style.MM_dd_yyyy).As("FormattedDate")); 526 DataSet result = productQuery.FindDataSet(); 527 string convertedValue = (string) result.Tables[0].Rows[0]["FormattedDate"]; 528 Assert.AreEqual(DateTime.Now.ToString("MM/dd/yyyy"), convertedValue, "The date was formatted according to the style Style.MM_dd_yyyy."); 529 530 productQuery = new Query<Product>(); 531 productQuery.Top = 1; 532 productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.VarChar, null, Style.MM_dd_yy)); 533 productQuery.FindDataSet(); 534 535 productQuery = new Query<Product>(); 536 productQuery.Top = 1; 537 productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.VarChar, 10, Style.MM_dd_yy)); 538 productQuery.FindDataSet(); 539 540 // Now test conversions to all other db 541 productQuery = new Query<Product>(); 542 productQuery.Top = 1; 543 productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.BigInt, null, null)); 544 productQuery.FindDataSet(); 545 546 productQuery = new Query<Product>(); 547 productQuery.Top = 1; 548 productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.Binary, null, null)); 549 productQuery.FindDataSet(); 550 551 productQuery = new Query<Product>(); 552 productQuery.Top = 1; 553 productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.Bit, null, null)); 554 productQuery.FindDataSet(); 555 556 productQuery = new Query<Product>(); 557 productQuery.Top = 1; 558 productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.Char, null, null)); 559 productQuery.FindDataSet(); 560 561 productQuery = new Query<Product>(); 562 productQuery.Top = 1; 563 productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.DateTime, null, null)); 564 productQuery.FindDataSet(); 565 566 productQuery = new Query<Product>(); 567 productQuery.Top = 1; 568 productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.Decimal, null, null)); 569 productQuery.FindDataSet(); 570 571 productQuery = new Query<Product>(); 572 productQuery.Top = 1; 573 productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.Float, null, null)); 574 productQuery.FindDataSet(); 575 576 productQuery = new Query<Product>(); 577 productQuery.Top = 1; 578 productQuery.AddSelectField(new ConvertTo("VarChar to Image conversion.", SqlDbType.Image, null, null)); 579 productQuery.FindDataSet(); 580 581 productQuery = new Query<Product>(); 582 productQuery.Top = 1; 583 productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.Int, null, null)); 584 productQuery.FindDataSet(); 585 586 productQuery = new Query<Product>(); 587 productQuery.Top = 1; 588 productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.Money, null, null)); 589 productQuery.FindDataSet(); 590 591 productQuery = new Query<Product>(); 592 productQuery.Top = 1; 593 productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.NChar, null, null)); 594 productQuery.FindDataSet(); 595 596 productQuery = new Query<Product>(); 597 productQuery.Top = 1; 598 productQuery.AddSelectField(new ConvertTo("VarChar to NText conversion", SqlDbType.NText, null, null)); 599 productQuery.FindDataSet(); 600 601 productQuery = new Query<Product>(); 602 productQuery.Top = 1; 603 productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.NVarChar, null, null)); 604 productQuery.FindDataSet(); 605 606 productQuery = new Query<Product>(); 607 productQuery.Top = 1; 608 productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.Real, null, null)); 609 productQuery.FindDataSet(); 610 611 productQuery = new Query<Product>(); 612 productQuery.Top = 1; 613 productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.SmallDateTime, null, null)); 614 productQuery.FindDataSet(); 615 616 productQuery = new Query<Product>(); 617 productQuery.Top = 1; 618 productQuery.AddSelectField(new ConvertTo(8, SqlDbType.SmallInt, null, null)); 619 productQuery.FindDataSet(); 620 621 productQuery = new Query<Product>(); 622 productQuery.Top = 1; 623 productQuery.AddSelectField(new ConvertTo(6, SqlDbType.SmallMoney, null, null)); 624 productQuery.FindDataSet(); 625 626 productQuery = new Query<Product>(); 627 productQuery.Top = 1; 628 productQuery.AddSelectField(new ConvertTo("VarChar to Text conversion", SqlDbType.Text, null, null)); 629 productQuery.FindDataSet(); 630 631 productQuery = new Query<Product>(); 632 productQuery.Top = 1; 633 productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.Timestamp, null, null)); 634 productQuery.FindDataSet(); 635 636 productQuery = new Query<Product>(); 637 productQuery.Top = 1; 638 productQuery.AddSelectField(new ConvertTo(7, SqlDbType.TinyInt, null, null)); 639 productQuery.FindDataSet(); 640 641 productQuery = new Query<Product>(); 642 productQuery.Top = 1; 643 productQuery.AddSelectField(new ConvertTo(Guid.NewGuid().ToString(), SqlDbType.UniqueIdentifier, null, null)); 644 productQuery.FindDataSet(); 645 646 productQuery = new Query<Product>(); 647 productQuery.Top = 1; 648 productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.VarBinary, null, null)); 649 productQuery.FindDataSet(); 650 651 productQuery = new Query<Product>(); 652 productQuery.Top = 1; 653 productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.VarChar, null, null)); 654 productQuery.FindDataSet(); 655 656 productQuery = new Query<Product>(); 657 productQuery.Top = 1; 658 productQuery.AddSelectField(new ConvertTo(new GetDate(), SqlDbType.Variant, null, null)); 659 productQuery.FindDataSet(); 660 661 productQuery = new Query<Product>(); 662 productQuery.Top = 1; 663 productQuery.AddSelectField(new ConvertTo("VarChar to XML conversion", SqlDbType.Xml, null, null)); 664 productQuery.FindDataSet(); 665 } 666 667 #endregion 668 669 [Test] 670 public void DateAdd() 671 { 672 // Test DateAdd when the interval is an integer 673 Query<LockedByVersion> dateAddQueryWithInteger = new Query<LockedByVersion>(); 674 dateAddQueryWithInteger.AddSelectField(new DateAdd(PartOfDateTime.Day, 1, new GetDate()).As("NewDate")); 675 676 DateTime newDate = (DateTime) dateAddQueryWithInteger.FindDataSet().Tables[0].Rows[0]["NewDate"]; 677 678 Assert.AreEqual(DateTime.Now.AddDays(1).ToShortDateString(), newDate.ToShortDateString(), "Today's date was incremented by one day."); 679 680 // Test DateAdd when the interval is a string 681 Query<LockedByVersion> dateAddQueryWithString = new Query<LockedByVersion>(); 682 dateAddQueryWithString.AddSelectField(new DateAdd(PartOfDateTime.Day, "1", new GetDate()).As("NewDate")); 683 684 DateTime newDate2 = (DateTime)dateAddQueryWithString.FindDataSet().Tables[0].Rows[0]["NewDate"]; 685 686 Assert.AreEqual(DateTime.Now.AddDays(1).ToShortDateString(), newDate2.ToShortDateString(), "Today's date was incremented by one day."); 687 } 688 689 [Test] 690 public void DateDiffWithDateAdd() 691 { 692 LockedByVersion lockedByVersion = new LockedByVersion(GenerateUniqueString(Types.LockedByVersion.Field._value), DateTime.Now); 693 694 PersistenceFacade.Persist(lockedByVersion); 695 696 Query<LockedByVersion> dateDiffWithDateAdd = new Query<LockedByVersion>(); 697 698 DateAdd tomorrow = new DateAdd(PartOfDateTime.Day, 1, new GetDate()); 699 700 DateDiff dateDiff = new DateDiff(PartOfDateTime.Day, new GetDate(), tomorrow); 701 702 dateDiffWithDateAdd.Where.AddGreaterThan(dateDiff, 0); 703 dateDiffWithDateAdd.Where.AddEqualTo(Types.LockedByVersion.Field._primaryKey, lockedByVersion.PrimaryKey[0]); 704 705 Assert.AreEqual(1, dateDiffWithDateAdd.GetObjectSetCount(), "The query was successfully generated when a field that requires a parameter is the first parameter of a criteria."); 706 } 707 708 [Test] 709 public void GetCountToStringTest() 710 { 711 Assert.AreNotEqual(Types.Person.Field._primaryKey.Count().ToString(), Types.Product.Field._primaryKey.Count().ToString(), "The ToString of the Count function are different"); 712 713 } 714 715 [Test] 716 public void Concat() 717 { 718 Query<Product> concatQuery1 = new Query<Product>(); 719 concatQuery1.AddSelectField((new Constant("StringOne") + "StringTwo" + "StringThree").As("ConcatenatedStrings")); 720 concatQuery1.Top = 1; 721 Assert.AreEqual("StringOneStringTwoStringThree", concatQuery1.FindDataRows().Single().Field<string>("ConcatenatedStrings"), "The strings should be concatenated."); 722 723 Query<Product> concatQuery2 = new Query<Product>(); 724 concatQuery2.AddSelectField(("StringOne" + new Constant("StringTwo") + "StringThree").As("ConcatenatedStrings")); 725 concatQuery2.Top = 1; 726 Assert.AreEqual("StringOneStringTwoStringThree", concatQuery2.FindDataRows().Single().Field<string>("ConcatenatedStrings"), "The strings should be concatenated."); 727 } 728 729 [Test] 730 public void Concat2() 731 { 732 Query<Product> concatQuery1 = new Query<Product>(); 733 concatQuery1.AddSelectField((new Constant("StringOne") + new Constant("StringTwo")).As("ConcatenatedStrings")); 734 concatQuery1.Top = 1; 735 Assert.AreEqual("StringOneStringTwo", concatQuery1.FindDataRows().Single().Field<string>("ConcatenatedStrings"), "The strings should be concatenated."); 736 } 737 738 [Test] 739 public void Add() 740 { 741 Query<Product> addQuery1 = new Query<Product>(); 742 addQuery1.AddSelectField((new Constant(2) + 1 + 1).As("AddedValues")); 743 addQuery1.Top = 1; 744 Assert.AreEqual(4, addQuery1.FindDataRows().Single().Field<int>("AddedValues"), "The numbers should be added."); 745 746 Query<Product> addQuery2 = new Query<Product>(); 747 addQuery2.AddSelectField((1 + new Constant(2) + 1).As("AddedValues")); 748 addQuery2.Top = 1; 749 Assert.AreEqual(4, addQuery2.FindDataRows().Single().Field<int>("AddedValues"), "The numbers should be added."); 750 } 751 752 [Test] 753 public void Add2() 754 { 755 Query<Product> addQuery = new Query<Product>(); 756 // Validate that we get a sum (and not a concat) even if the constant is cast to the type 'object' 757 addQuery.AddSelectField((new Constant("1") + (object)new Constant(2)).As("AddedValues")); 758 addQuery.Top = 1; 759 Assert.AreEqual(3, addQuery.FindDataRows().Single().Field<int>("AddedValues"), "The numbers should be added."); 760 } 761 762 [Test] 763 public void Replace() 764 { 765 Query<Product> replaceQuery = new Query<Product>(); 766 replaceQuery.AddSelectField(new Constant("XXAA").Replace("X", "A").As("ReplacedString")); 767 replaceQuery.Top = 1; 768 Assert.AreEqual("AAAA", replaceQuery.FindDataRows().Single().Field<string>("ReplacedString"), "The Xs should be replaced by As."); 769 } 770 771 [Test] 772 public void Min() 773 { 774 Query<Product> minQuery = new Query<Product>(); 775 minQuery.Where.AddEqualTo(Types.Product._productCategory.Field._name, "Beverages"); 776 minQuery.AddSelectField(Types.Product.Field._pricePerUnit.Min().As("MinPricePerUnit")); 777 778 Assert.AreEqual(0.45m, minQuery.FindDataRows().Single().Field<decimal>("MinPricePerUnit"), "The minimum value should be selected."); 779 } 780 781 [Test] 782 public void OrderByTheSameFieldButDifferentFunctions() 783 { 784 //Test using DatePart using the select fields 785 Criteria criteria = new Criteria(); 786 787 criteria.AddOrderBy(Types.LockedByVersion.Field._createdDate.DatePart(PartOfDateTime.Year)); 788 criteria.AddOrderBy(Types.LockedByVersion.Field._createdDate.DatePart(PartOfDateTime.Month)); 789 790 Field[] fields = new Field[] { Types.LockedByVersion.Field._createdDate.DatePart(PartOfDateTime.Year) , 791 Types.LockedByVersion.Field._createdDate.DatePart(PartOfDateTime.Month)}; 792 793 QueryFacade.FindDataSet<LockedByVersion>(criteria, fields); 794 795 //Test using DatePart without using the select fields 796 criteria = new Criteria(); 797 798 criteria.AddOrderBy(Types.LockedByVersion.Field._createdDate.DatePart(PartOfDateTime.Year)); 799 criteria.AddOrderBy(Types.LockedByVersion.Field._createdDate.DatePart(PartOfDateTime.Month)); 800 801 QueryFacade.FindObject<LockedByVersion>(criteria); 802 803 804 //Test using DateDiff 805 criteria = new Criteria(); 806 DateDiff dateDiff1 = new DateDiff(PartOfDateTime.Day, new GetDate(), new Constant(DateTime.Now.AddDays(2))); 807 DateDiff dateDiff2 = new DateDiff(PartOfDateTime.Day, new GetDate(), new Constant(DateTime.Now.AddDays(3))); 808 809 criteria.AddOrderBy(dateDiff1); 810 criteria.AddOrderBy(dateDiff2); 811 812 QueryFacade.FindObject<LockedByVersion>(criteria); 813 814 //Test using DateAdd 815 criteria = new Criteria(); 816 DateAdd dateAdd1 = new DateAdd(PartOfDateTime.Day, 1, new GetDate()); 817 DateAdd dateAdd2 = new DateAdd(PartOfDateTime.Day, 2, new GetDate()); 818 819 criteria.AddOrderBy(dateAdd1); 820 criteria.AddOrderBy(dateAdd2); 821 822 QueryFacade.FindObject<LockedByVersion>(criteria); 823 } 824 825 [Test] 826 public void RowNumber() 827 { 828 // Capture the SQL statement generated by the query 829 CommandMonitor commandMonitor = new CommandMonitor(); 830 831 Query<Product> productQuery = new Query<Product>(); 832 productQuery.AddSelectField(Types.Product.Field._name); 833 productQuery.AddSelectField(new RowNumber(Types.Product.Field._name)); 834 productQuery.FindDataSet(); 835 836 // Assert that the correct SQL statement was generated 837 Assert.IsTrue(commandMonitor.FirstCommandText.Contains("ROW_NUMBER() OVER ( ORDER BY Product02.NAME)"), "The command text should contain a ROW_NUMBER clause."); 838 } 839 } 840 }