mcs/class/System.Data/Test/System.Data/DataColumnTest.cs

   1 // DataColumnTest.cs - NUnit Test Cases for System.Data.DataColumn
   2 //
   3 // Authors:
   4 //   Franklin Wise <gracenote@earthlink.net>
   5 //   Rodrigo Moya <rodrigo@ximian.com>
   6 //   Daniel Morgan <danmorg@sc.rr.com>
   7 //   Martin Willemoes Hansen <mwh@sysrq.dk>
   8 //
   9 // (C) Copyright 2002 Franklin Wise
  10 // (C) Copyright 2002 Rodrigo Moya
  11 // (C) Copyright 2003 Daniel Morgan
  12 // (C) Copyright 2003 Martin Willemoes Hansen
  13 // (C) Copyright 2011 Xamarin Inc
  14
  15 //
  16 // Copyright 2011 Xamarin Inc (http://www.xamarin.com)
  17 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
  18 //
  19 // Permission is hereby granted, free of charge, to any person obtaining
  20 // a copy of this software and associated documentation files (the
  21 // "Software"), to deal in the Software without restriction, including
  22 // without limitation the rights to use, copy, modify, merge, publish,
  23 // distribute, sublicense, and/or sell copies of the Software, and to
  24 // permit persons to whom the Software is furnished to do so, subject to
  25 // the following conditions:
  26 //
  27 // The above copyright notice and this permission notice shall be
  28 // included in all copies or substantial portions of the Software.
  29 //
  30 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  31 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  32 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  33 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  34 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  35 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  36 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  37 //
  38
  39 using System;
  40 using System.ComponentModel;
  41 using System.Data;
  42 using System.Data.SqlTypes;
  43
  44 using NUnit.Framework;
  45
  46 namespace MonoTests.System.Data
  47 {
  48         [TestFixture]
  49         public class DataColumnTest
  50         {
  51                 private DataTable _tbl;
  52
  53                 [SetUp]
  54                 public void GetReady ()
  55                 {
  56                         _tbl = new DataTable();
  57                 }
  58
  59                 [Test]
  60                 public void Ctor()
  61                 {
  62                         string colName = "ColName";
  63                         DataColumn col = new DataColumn ();
  64
  65                         //These should all ctor without an exception
  66                         col = new DataColumn (colName);
  67                         col = new DataColumn (colName, typeof(int));
  68                         col = new DataColumn (colName, typeof(int), null);
  69                         col = new DataColumn (colName, typeof(int), null, MappingType.Attribute);
  70                 }
  71
  72                 [Test]
  73                 public void Constructor3_DataType_Null ()
  74                 {
  75                         try {
  76                                 new DataColumn ("ColName", (Type) null);
  77                                 Assert.Fail ("#1");
  78                         } catch (ArgumentNullException ex) {
  79                                 Assert.AreEqual (typeof (ArgumentNullException), ex.GetType (), "#2");
  80                                 Assert.IsNull (ex.InnerException, "#3");
  81                                 // Never premise English.
  82 //                              Assert.IsNotNull (ex.Message, "#4");
  83                                 Assert.IsNotNull (ex.ParamName, "#5");
  84                                 Assert.AreEqual ("dataType", ex.ParamName, "#6");
  85                         }
  86                 }
  87
  88                 [Test]
  89                 public void AllowDBNull()
  90                 {
  91                         DataColumn col = new DataColumn("NullCheck",typeof(int));
  92                         _tbl.Columns.Add(col);
  93                         col.AllowDBNull = true;
  94                         _tbl.Rows.Add(_tbl.NewRow());
  95                         _tbl.Rows[0]["NullCheck"] = DBNull.Value;
  96                         try {
  97                                 col.AllowDBNull = false;
  98                                 Assert.Fail ("DC8b: Failed to throw DataException.");
  99                         } catch (DataException) {
 100                         }
 101                 }
 102
 103                 [Test]
 104                 public void AllowDBNull1()
 105                 {
 106                         DataTable tbl = _tbl;
 107                         tbl.Columns.Add ("id", typeof (int));
 108                         tbl.Columns.Add ("name", typeof (string));
 109                         tbl.PrimaryKey = new DataColumn [] { tbl.Columns ["id"] };
 110                         tbl.Rows.Add (new object [] { 1, "RowState 1" });
 111                         tbl.Rows.Add (new object [] { 2, "RowState 2" });
 112                         tbl.Rows.Add (new object [] { 3, "RowState 3" });
 113                         tbl.AcceptChanges ();
 114                         // Update Table with following changes: Row0 unmodified,
 115                         // Row1 modified, Row2 deleted, Row3 added, Row4 not-present.
 116                         tbl.Rows [1] ["name"] = "Modify 2";
 117                         tbl.Rows [2].Delete ();
 118
 119                         DataColumn col = tbl.Columns ["name"];
 120                         col.AllowDBNull = true;
 121                         col.AllowDBNull = false;
 122
 123                         Assert.IsFalse (col.AllowDBNull);
 124                 }
 125
 126                 [Test]
 127                 public void AutoIncrement()
 128                 {
 129                         DataColumn col = new DataColumn("Auto",typeof (string));
 130                         col.AutoIncrement = true;
 131
 132                         //Check for Correct Default Values
 133                         Assert.AreEqual (0L, col.AutoIncrementSeed, "#1");
 134                         Assert.AreEqual (1L, col.AutoIncrementStep, "#2");
 135
 136                         //Check for auto type convert
 137                         Assert.AreEqual (typeof (int), col.DataType, "#3");
 138                 }
 139
 140                 [Test]
 141                 public void AutoIncrementExceptions()
 142                 {
 143                         DataColumn col = new DataColumn();
 144                         col.Expression = "SomeExpression";
 145
 146                         //if computed column exception is thrown
 147                         try {
 148                                 col.AutoIncrement = true;
 149                                 Assert.Fail ("DC12: Failed to throw ArgumentException");
 150                         } catch (ArgumentException) {
 151                         }
 152                 }
 153
 154                 [Test]
 155                 public void Caption()
 156                 {
 157                         DataColumn col = new DataColumn("ColName");
 158                         //Caption not set at this point
 159                         Assert.AreEqual (col.ColumnName, col.Caption, "#1");
 160
 161                         //Set caption
 162                         col.Caption = "MyCaption";
 163                         Assert.AreEqual ("MyCaption", col.Caption, "#2");
 164
 165                         //Clear caption
 166                         col.Caption = null;
 167                         Assert.AreEqual (string.Empty, col.Caption, "#3");
 168                 }
 169
 170                 [Test]
 171                 public void DateTimeMode_Valid ()
 172                 {
 173                         DataColumn col = new DataColumn ("birthdate", typeof (DateTime));
 174                         col.DateTimeMode = DataSetDateTime.Local;
 175                         Assert.AreEqual (DataSetDateTime.Local, col.DateTimeMode, "#1");
 176                         col.DateTimeMode = DataSetDateTime.Unspecified;
 177                         Assert.AreEqual (DataSetDateTime.Unspecified, col.DateTimeMode, "#2");
 178                         col.DateTimeMode = DataSetDateTime.Utc;
 179                         Assert.AreEqual (DataSetDateTime.Utc, col.DateTimeMode, "#3");
 180                 }
 181
 182                 [Test]
 183                 public void DateTime_DataType_Invalid ()
 184                 {
 185                         DataColumn col = new DataColumn ("birthdate", typeof (int));
 186                         try {
 187                                 col.DateTimeMode = DataSetDateTime.Local;
 188                                 Assert.Fail ("#1");
 189                         } catch (InvalidOperationException ex) {
 190                                 // The DateTimeMode can be set only on DataColumns
 191                                 // of type DateTime
 192                                 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
 193                                 Assert.IsNull (ex.InnerException, "#3");
 194                                 Assert.IsNotNull (ex.Message, "#4");
 195                                 Assert.IsTrue (ex.Message.IndexOf ("DateTimeMode") != -1, "#5");
 196                                 Assert.IsTrue (ex.Message.IndexOf ("DateTime") != -1, "#6");
 197                         }
 198                 }
 199
 200                 [Test]
 201                 public void DateTimeMode_Invalid ()
 202                 {
 203                         DataColumn col = new DataColumn ("birthdate", typeof (DateTime));
 204                         try {
 205                                 col.DateTimeMode = (DataSetDateTime) 666;
 206                                 Assert.Fail ("#1");
 207                         } catch (InvalidEnumArgumentException ex) {
 208                                 // The DataSetDateTime enumeration value, 666, is invalid
 209                                 Assert.AreEqual (typeof (InvalidEnumArgumentException), ex.GetType (), "#2");
 210                                 Assert.IsNull (ex.InnerException, "#3");
 211                                 Assert.IsNotNull (ex.Message, "#4");
 212                                 Assert.IsTrue (ex.Message.IndexOf ("DataSetDateTime") != -1, "#5");
 213                                 Assert.IsTrue (ex.Message.IndexOf ("666") != -1, "#6");
 214                                 Assert.IsNull (ex.ParamName, "#7");
 215                         }
 216                 }
 217
 218                 [Test]
 219                 public void ForColumnNameException()
 220                 {
 221                         DataColumn col = new DataColumn();
 222                         DataColumn col2 = new DataColumn();
 223                         DataColumn col3 = new DataColumn();
 224                         DataColumn col4 = new DataColumn();
 225
 226                         col.ColumnName = "abc";
 227                         Assert.AreEqual ("abc", col.ColumnName, "#1");
 228
 229                         _tbl.Columns.Add(col);
 230
 231                         //Duplicate name exception
 232                         try {
 233                                 col2.ColumnName = "abc";
 234                                 _tbl.Columns.Add(col2);
 235                                 Assert.AreEqual ("abc", col2.ColumnName, "#2");
 236                                 Assert.Fail ("#3");
 237                         } catch (DuplicateNameException) {
 238                         }
 239                         // Make sure case matters in duplicate checks
 240                         col3.ColumnName = "ABC";
 241                         _tbl.Columns.Add(col3);
 242                 }
 243
 244                 [Test]
 245                 public void DefaultValue()
 246                 {
 247                         DataTable tbl = new DataTable();
 248                         tbl.Columns.Add("MyCol", typeof(int));
 249
 250                         //Set default Value if Autoincrement is true
 251                         tbl.Columns[0].AutoIncrement = true;
 252                         try {
 253                                 tbl.Columns[0].DefaultValue = 2;
 254                                 Assert.Fail ("DC19: Failed to throw ArgumentException.");
 255                         } catch (ArgumentException) {
 256                         }
 257
 258                         tbl.Columns[0].AutoIncrement = false;
 259
 260                         //Set default value to an incompatible datatype
 261                         try {
 262                                 tbl.Columns[0].DefaultValue = "hello";
 263                                 Assert.Fail ("DC21: Failed to throw FormatException.");
 264                         } catch (FormatException) {
 265                         }
 266
 267                         //TODO: maybe add tests for setting default value for types that can implict
 268                         //cast
 269                 }
 270
 271                 [Test]
 272                 public void SetDataType ()
 273                 {
 274                         //test for DataAlready exists and change the datatype
 275                         //supported datatype
 276                         //AutoInc column dataType supported
 277                 }
 278
 279                 [Test]
 280                 public void Defaults1 ()
 281                 {
 282                         //Check for defaults - ColumnName not set at the beginning
 283                         DataTable table = new DataTable();
 284                         DataColumn column = new DataColumn();
 285
 286                         Assert.AreEqual (String.Empty, column.ColumnName, "#A1");
 287                         Assert.AreEqual (typeof (string), column.DataType, "#A2");
 288
 289                         table.Columns.Add(column);
 290
 291                         Assert.AreEqual ("Column1", table.Columns [0].ColumnName, "#B1");
 292                         Assert.AreEqual (typeof (string), table.Columns [0].DataType, "#B2");
 293
 294                         DataRow row = table.NewRow();
 295                         table.Rows.Add(row);
 296                         DataRow dataRow = table.Rows[0];
 297
 298                         object v = dataRow.ItemArray [0];
 299                         Assert.AreEqual (typeof (DBNull), v.GetType (), "#C1");
 300                         Assert.AreEqual (DBNull.Value, v, "#C2");
 301                 }
 302
 303                 [Test]
 304                 public void Defaults2 ()
 305                 {
 306                         //Check for defaults - ColumnName set at the beginning
 307                         string blah = "Blah";
 308                         //Check for defaults - ColumnName not set at the beginning
 309                         DataTable table = new DataTable();
 310                         DataColumn column = new DataColumn(blah);
 311
 312                         Assert.AreEqual (blah, column.ColumnName, "#A1");
 313                         Assert.AreEqual (typeof (string), column.DataType, "#A2");
 314
 315                         table.Columns.Add(column);
 316
 317                         Assert.AreEqual (blah, table.Columns[0].ColumnName, "#B1");
 318                         Assert.AreEqual (typeof (string), table.Columns[0].DataType, "#B2");
 319
 320                         DataRow row = table.NewRow();
 321                         table.Rows.Add(row);
 322                         DataRow dataRow = table.Rows[0];
 323
 324                         object v = dataRow.ItemArray[0];
 325                         Assert.AreEqual (typeof (DBNull), v.GetType (), "#C1");
 326                         Assert.AreEqual (DBNull.Value, v, "#C2");
 327                 }
 328
 329                 [Test]
 330                 public void Defaults3 ()
 331                 {
 332                         DataColumn col = new DataColumn ("foo", typeof (SqlBoolean));
 333                         Assert.AreEqual (SqlBoolean.Null, col.DefaultValue, "#1");
 334                         col.DefaultValue = SqlBoolean.True;
 335                         // FIXME: not working yet
 336                         //col.DefaultValue = true;
 337                         //Assert.AreEqual (SqlBoolean.True, col.DefaultValue, "#2"); // not bool but SqlBoolean
 338                         col.DefaultValue = DBNull.Value;
 339                         Assert.AreEqual (SqlBoolean.Null, col.DefaultValue, "#3"); // not DBNull
 340                 }
 341
 342                 [Test]
 343                 [ExpectedException (typeof (DataException))]
 344                 public void ChangeTypeAfterSettingDefaultValue ()
 345                 {
 346                         DataColumn col = new DataColumn ("foo", typeof (SqlBoolean));
 347                         col.DefaultValue = true;
 348                         col.DataType = typeof (int);
 349                 }
 350
 351                 [Test]
 352                 public void ExpressionSubstringlimits () {
 353                         DataTable t = new DataTable ();
 354                         t.Columns.Add ("aaa");
 355                         t.Rows.Add (new object [] {"xxx"});
 356                         DataColumn c = t.Columns.Add ("bbb");
 357                         try {
 358                                 c.Expression = "SUBSTRING(aaa, 6000000000000000, 2)";
 359                                 Assert.Fail ("#1");
 360                         } catch (OverflowException) {
 361                         }
 362                 }
 363
 364                 [Test]
 365                 public void ExpressionFunctions ()
 366                 {
 367                         DataTable T = new DataTable ("test");
 368                         DataColumn C = new DataColumn ("name");
 369                         T.Columns.Add (C);
 370                         C = new DataColumn ("age");
 371                         C.DataType = typeof (int);
 372                         T.Columns.Add (C);
 373                         C = new DataColumn ("id");
 374                         C.Expression = "substring (name, 1, 3) + len (name) + age";
 375                         T.Columns.Add (C);
 376
 377                         DataSet Set = new DataSet ("TestSet");
 378                         Set.Tables.Add (T);
 379
 380                         DataRow Row = null;
 381                         for (int i = 0; i < 100; i++) {
 382                                 Row = T.NewRow ();
 383                                 Row [0] = "human" + i;
 384                                 Row [1] = i;
 385                                 T.Rows.Add (Row);
 386                         }
 387
 388                         Row = T.NewRow ();
 389                         Row [0] = "h*an";
 390                         Row [1] = DBNull.Value;
 391                         T.Rows.Add (Row);
 392
 393                         Assert.AreEqual ("hum710", T.Rows [10] [2], "#A1");
 394                         Assert.AreEqual ("hum64", T.Rows [4] [2], "#A2");
 395                         C = T.Columns [2];
 396                         C.Expression = "isnull (age, 'succ[[]]ess')";
 397                         Assert.AreEqual ("succ[[]]ess", T.Rows [100] [2], "#A3");
 398
 399                         C.Expression = "iif (age = 24, 'hurrey', 'boo')";
 400                         Assert.AreEqual ("boo", T.Rows [50] [2], "#B1");
 401                         Assert.AreEqual ("hurrey", T.Rows [24] [2], "#B2");
 402
 403                         C.Expression = "convert (age, 'System.Boolean')";
 404                         Assert.AreEqual (Boolean.TrueString, T.Rows [50] [2], "#C1");
 405                         Assert.AreEqual (Boolean.FalseString, T.Rows [0] [2], "#C2");
 406
 407                         //
 408                         // Exceptions
 409                         //
 410
 411                         try {
 412                                 // The expression contains undefined function call iff().
 413                                 C.Expression = "iff (age = 24, 'hurrey', 'boo')";
 414                                 Assert.Fail ("#D");
 415                         } catch (EvaluateException) {
 416                         } catch (SyntaxErrorException) {
 417                         }
 418
 419                         //The following two cases fail on mono. MS.net evaluates the expression
 420                         //immediatly upon assignment. We don't do this yet hence we don't throw
 421                         //an exception at this point.
 422                         try {
 423                                 C.Expression = "iif (nimi = 24, 'hurrey', 'boo')";
 424                                 Assert.Fail ("#E1");
 425                         } catch (EvaluateException e) {
 426                                 Assert.AreEqual (typeof (EvaluateException), e.GetType (), "#E2");
 427                                 // Never premise English.
 428                                 //Assert.AreEqual ("Cannot find column [nimi].", e.Message, "#E3");
 429                         }
 430
 431                         try {
 432                                 C.Expression = "iif (name = 24, 'hurrey', 'boo')";
 433                                 Assert.Fail ("#F1");
 434                         } catch (EvaluateException e) {
 435                                 Assert.AreEqual (typeof (EvaluateException), e.GetType (), "#F2");
 436                                 //AssertEquals ("DC41", "Cannot perform '=' operation on System.String and System.Int32.", e.Message);
 437                         }
 438
 439                         try {
 440                                 C.Expression = "convert (age, Boolean)";
 441                                 Assert.Fail ("#G1");
 442                         } catch (EvaluateException e) {
 443                                 Assert.AreEqual (typeof (EvaluateException), e.GetType (), "#G2");
 444                                 // Never premise English.
 445                                 //Assert.AreEqual ("Invalid type name 'Boolean'.", e.Message, "#G3");
 446                         }
 447                 }
 448
 449                 [Test]
 450                 public void ExpressionAggregates ()
 451                 {
 452                         DataTable T = new DataTable ("test");
 453                         DataTable T2 = new DataTable ("test2");
 454
 455                         DataColumn C = new DataColumn ("name");
 456                         T.Columns.Add (C);
 457                         C = new DataColumn ("age");
 458                         C.DataType = typeof (int);
 459                         T.Columns.Add (C);
 460                         C = new DataColumn ("childname");
 461                         T.Columns.Add (C);
 462
 463                         C = new DataColumn ("expression");
 464                         T.Columns.Add (C);
 465
 466                         DataSet Set = new DataSet ("TestSet");
 467                         Set.Tables.Add (T);
 468                         Set.Tables.Add (T2);
 469
 470                         DataRow Row = null;
 471                         for (int i = 0; i < 100; i++) {
 472                                 Row = T.NewRow ();
 473                                 Row [0] = "human" + i;
 474                                 Row [1] = i;
 475                                 Row [2] = "child" + i;
 476                                 T.Rows.Add (Row);
 477                         }
 478
 479                         Row = T.NewRow ();
 480                         Row [0] = "h*an";
 481                         Row [1] = DBNull.Value;
 482                         T.Rows.Add (Row);
 483
 484                         C = new DataColumn ("name");
 485                         T2.Columns.Add (C);
 486                         C = new DataColumn ("age");
 487                         C.DataType = typeof (int);
 488                         T2.Columns.Add (C);
 489
 490                         for (int i = 0; i < 100; i++) {
 491                                 Row = T2.NewRow ();
 492                                 Row [0] = "child" + i;
 493                                 Row [1] = i;
 494                                 T2.Rows.Add (Row);
 495                                 Row = T2.NewRow ();
 496                                 Row [0] = "child" + i;
 497                                 Row [1] = i - 2;
 498                                 T2.Rows.Add (Row);
 499                         }
 500
 501                         DataRelation Rel = new DataRelation ("Rel", T.Columns [2], T2.Columns [0]);
 502                         Set.Relations.Add (Rel);
 503
 504                         C = T.Columns [3];
 505                         C.Expression = "Sum (Child.age)";
 506                         Assert.AreEqual ("-2", T.Rows [0] [3], "#A1");
 507                         Assert.AreEqual ("98", T.Rows [50] [3], "#A2");
 508
 509                         C.Expression = "Count (Child.age)";
 510                         Assert.AreEqual ("2", T.Rows [0] [3], "#B1");
 511                         Assert.AreEqual ("2", T.Rows [60] [3], "#B2");
 512
 513                         C.Expression = "Avg (Child.age)";
 514                         Assert.AreEqual ("-1", T.Rows [0] [3], "#C1");
 515                         Assert.AreEqual ("59", T.Rows [60] [3], "#C2");
 516
 517                         C.Expression = "Min (Child.age)";
 518                         Assert.AreEqual ("-2", T.Rows [0] [3], "#D1");
 519                         Assert.AreEqual ("58", T.Rows [60] [3], "#D2");
 520
 521                         C.Expression = "Max (Child.age)";
 522                         Assert.AreEqual ("0", T.Rows [0] [3], "#E1");
 523                         Assert.AreEqual ("60", T.Rows [60] [3], "#E2");
 524
 525                         C.Expression = "stdev (Child.age)";
 526                         Assert.AreEqual ((1.4142135623731).ToString (T.Locale), T.Rows [0] [3], "#F1");
 527                         Assert.AreEqual ((1.4142135623731).ToString (T.Locale), T.Rows [60] [3], "#F2");
 528
 529                         C.Expression = "var (Child.age)";
 530                         Assert.AreEqual ("2", T.Rows [0] [3], "#G1");
 531                         Assert.AreEqual ("2", T.Rows [60] [3], "#G2");
 532                 }
 533
 534                 [Test]
 535                 public void ExpressionOperator ()
 536                 {
 537                         DataTable T = new DataTable ("test");
 538                         DataColumn C = new DataColumn ("name");
 539                         T.Columns.Add (C);
 540                         C = new DataColumn ("age");
 541                         C.DataType = typeof (int);
 542                         T.Columns.Add (C);
 543                         C = new DataColumn ("id");
 544                         C.Expression = "substring (name, 1, 3) + len (name) + age";
 545                         T.Columns.Add (C);
 546
 547                         DataSet Set = new DataSet ("TestSet");
 548                         Set.Tables.Add (T);
 549
 550                         DataRow Row = null;
 551                         for (int i = 0; i < 100; i++) {
 552                                 Row = T.NewRow ();
 553                                 Row [0] = "human" + i;
 554                                 Row [1] = i;
 555                                 T.Rows.Add (Row);
 556                         }
 557
 558                         Row = T.NewRow ();
 559                         Row [0] = "h*an";
 560                         Row [1] = DBNull.Value;
 561                         T.Rows.Add (Row);
 562
 563                         C = T.Columns [2];
 564                         C.Expression = "age + 4";
 565                         Assert.AreEqual ("68", T.Rows [64] [2], "#A");
 566
 567                         C.Expression = "age - 4";
 568                         Assert.AreEqual ("60", T.Rows [64] [2], "#B");
 569
 570                         C.Expression = "age * 4";
 571                         Assert.AreEqual ("256", T.Rows [64] [2], "#C");
 572
 573                         C.Expression = "age / 4";
 574                         Assert.AreEqual ("16", T.Rows [64] [2], "#D");
 575
 576                         C.Expression = "age % 5";
 577                         Assert.AreEqual ("4", T.Rows [64] [2], "#E");
 578
 579                         C.Expression = "age in (5, 10, 15, 20, 25)";
 580                         Assert.AreEqual ("False", T.Rows [64] [2], "#F1");
 581                         Assert.AreEqual ("True", T.Rows [25] [2], "#F2");
 582
 583                         C.Expression = "name like 'human1%'";
 584                         Assert.AreEqual ("True", T.Rows [1] [2], "#G1");
 585                         Assert.AreEqual ("False", T.Rows [25] [2], "#G2");
 586
 587                         C.Expression = "age < 4";
 588                         Assert.AreEqual ("False", T.Rows [4] [2], "#H1");
 589                         Assert.AreEqual ("True", T.Rows [3] [2], "#H2");
 590
 591                         C.Expression = "age <= 4";
 592                         Assert.AreEqual ("True", T.Rows [4] [2], "#I1");
 593                         Assert.AreEqual ("False", T.Rows [5] [2], "#I2");
 594
 595                         C.Expression = "age > 4";
 596                         Assert.AreEqual ("False", T.Rows [4] [2], "#J1");
 597                         Assert.AreEqual ("True", T.Rows [5] [2], "#J2");
 598
 599                         C.Expression = "age >= 4";
 600                         Assert.AreEqual ("True", T.Rows [4] [2], "#K1");
 601                         Assert.AreEqual ("False", T.Rows [1] [2], "#K2");
 602
 603                         C.Expression = "age = 4";
 604                         Assert.AreEqual ("True", T.Rows [4] [2], "#L1");
 605                         Assert.AreEqual ("False", T.Rows [1] [2], "#L2");
 606
 607                         C.Expression = "age <> 4";
 608                         Assert.AreEqual ("False", T.Rows [4] [2], "#M1");
 609                         Assert.AreEqual ("True", T.Rows [1] [2], "#M2");
 610                 }
 611
 612                 [Test]
 613                 public void SetMaxLengthException ()
 614                 {
 615                         // Setting MaxLength on SimpleContent -> exception
 616                         DataSet ds = new DataSet("Example");
 617                         ds.Tables.Add("MyType");
 618                         ds.Tables["MyType"].Columns.Add(new DataColumn("Desc",
 619                                 typeof (string), "", MappingType.SimpleContent));
 620                         try {
 621                                 ds.Tables ["MyType"].Columns ["Desc"].MaxLength = 32;
 622                                 Assert.Fail ("#1");
 623                         } catch (ArgumentException) {
 624                         }
 625                 }
 626
 627                 [Test]
 628                 public void SetMaxLengthNegativeValue ()
 629                 {
 630                         // however setting MaxLength on SimpleContent is OK
 631                         DataSet ds = new DataSet ("Example");
 632                         ds.Tables.Add ("MyType");
 633                         ds.Tables ["MyType"].Columns.Add (
 634                                 new DataColumn ("Desc", typeof (string), "", MappingType.SimpleContent));
 635                         ds.Tables ["MyType"].Columns ["Desc"].MaxLength = -1;
 636                 }
 637
 638                 [Test]
 639                 public void AdditionToConstraintCollectionTest()
 640                 {
 641                         DataTable myTable = new DataTable("myTable");
 642                         DataColumn idCol = new DataColumn("id", typeof (int));
 643                         idCol.Unique = true;
 644                         myTable.Columns.Add(idCol);
 645                         ConstraintCollection cc = myTable.Constraints;
 646                         //cc just contains a single UniqueConstraint object.
 647                         UniqueConstraint uc = cc[0] as UniqueConstraint;
 648                         Assert.AreEqual ("id", uc.Columns[0].ColumnName);
 649                 }
 650
 651                 [Test] // bug #77025
 652                 public void CalcStatisticalFunction_SingleElement()
 653                 {
 654                         DataTable table = new DataTable ();
 655                         table.Columns.Add ("test", typeof (int));
 656
 657                         table.Rows.Add (new object [] {0});
 658                         table.Columns.Add ("result_var", typeof (double), "var(test)");
 659                         table.Columns.Add ("result_stdev", typeof (double), "stdev(test)");
 660
 661                         // Check DBNull.Value is set as the result
 662                         Assert.AreEqual (typeof (DBNull), (table.Rows[0]["result_var"]).GetType (), "#1");
 663                         Assert.AreEqual (typeof (DBNull), (table.Rows [0] ["result_stdev"]).GetType (), "#2");
 664                 }
 665
 666                 [Test]
 667                 public void Aggregation_CheckIfChangesDynamically()
 668                 {
 669                         DataTable table = new DataTable ();
 670
 671                         table.Columns.Add ("test", typeof (int));
 672                         table.Columns.Add ("result_count", typeof (int), "count(test)");
 673                         table.Columns.Add ("result_sum", typeof (int), "sum(test)");
 674                         table.Columns.Add ("result_avg", typeof (int), "avg(test)");
 675                         table.Columns.Add ("result_max", typeof (int), "max(test)");
 676                         table.Columns.Add ("result_min", typeof (int), "min(test)");
 677                         table.Columns.Add ("result_var", typeof (double), "var(test)");
 678                         table.Columns.Add ("result_stdev", typeof (double), "stdev(test)");
 679
 680                         // Adding the rows after all the expression columns are added
 681                         table.Rows.Add (new object[] {0});
 682                         Assert.AreEqual (1, table.Rows [0] ["result_count"], "#A1");
 683                         Assert.AreEqual (0, table.Rows [0] ["result_sum"], "#A2");
 684                         Assert.AreEqual (0, table.Rows [0] ["result_avg"], "#A3");
 685                         Assert.AreEqual (0, table.Rows [0] ["result_max"], "#A4");
 686                         Assert.AreEqual (0, table.Rows [0] ["result_min"], "#A5");
 687                         Assert.AreEqual (DBNull.Value, table.Rows [0] ["result_var"], "#A6");
 688                         Assert.AreEqual (DBNull.Value, table.Rows [0] ["result_stdev"], "#A7");
 689
 690                         table.Rows.Add (new object[] {1});
 691                         table.Rows.Add (new object[] {-2});
 692
 693                         // Check if the aggregate columns are updated correctly
 694                         Assert.AreEqual (3, table.Rows [0] ["result_count"], "#B1");
 695                         Assert.AreEqual (-1, table.Rows [0] ["result_sum"], "#B2");
 696                         Assert.AreEqual (0, table.Rows [0] ["result_avg"], "#B3");
 697                         Assert.AreEqual (1, table.Rows [0] ["result_max"], "#B4");
 698                         Assert.AreEqual (-2, table.Rows [0] ["result_min"], "#B5");
 699                         Assert.AreEqual ((7.0 / 3), table.Rows [0] ["result_var"], "#B6");
 700                         Assert.AreEqual (Math.Sqrt (7.0 / 3), table.Rows [0] ["result_stdev"], "#B7");
 701                 }
 702
 703                 [Test]
 704                 public void Aggregation_CheckIfChangesDynamically_ChildTable ()
 705                 {
 706                         DataSet ds = new DataSet ();
 707
 708                         DataTable table = new DataTable ();
 709                         DataTable table2 = new DataTable ();
 710                         ds.Tables.Add (table);
 711                         ds.Tables.Add (table2);
 712
 713                         table.Columns.Add ("test", typeof (int));
 714                         table2.Columns.Add ("test", typeof (int));
 715                         table2.Columns.Add ("val", typeof (int));
 716                         DataRelation rel = new DataRelation ("rel", table.Columns[0], table2.Columns[0]);
 717                         ds.Relations.Add (rel);
 718
 719                         table.Columns.Add ("result_count", typeof (int), "count(child.test)");
 720                         table.Columns.Add ("result_sum", typeof (int), "sum(child.test)");
 721                         table.Columns.Add ("result_avg", typeof (int), "avg(child.test)");
 722                         table.Columns.Add ("result_max", typeof (int), "max(child.test)");
 723                         table.Columns.Add ("result_min", typeof (int), "min(child.test)");
 724                         table.Columns.Add ("result_var", typeof (double), "var(child.test)");
 725                         table.Columns.Add ("result_stdev", typeof (double), "stdev(child.test)");
 726
 727                         table.Rows.Add (new object[] {1});
 728                         table.Rows.Add (new object[] {2});
 729                         // Add rows to the child table
 730                         for (int j=0; j<10; j++)
 731                                 table2.Rows.Add (new object[] {1,j});
 732
 733                         // Check the values for the expression columns in parent table
 734                         Assert.AreEqual (10, table.Rows [0] ["result_count"], "#A1");
 735                         Assert.AreEqual (0, table.Rows [1] ["result_count"], "#A2");
 736
 737                         Assert.AreEqual (10, table.Rows [0] ["result_sum"], "#B1");
 738                         Assert.AreEqual (DBNull.Value, table.Rows [1] ["result_sum"], "#B2");
 739
 740                         Assert.AreEqual (1, table.Rows [0] ["result_avg"], "#C1");
 741                         Assert.AreEqual (DBNull.Value, table.Rows [1] ["result_avg"], "#C2");
 742
 743                         Assert.AreEqual (1, table.Rows [0] ["result_max"], "#D1");
 744                         Assert.AreEqual (DBNull.Value, table.Rows [1] ["result_max"], "#D2");
 745
 746                         Assert.AreEqual (1, table.Rows [0] ["result_min"], "#E1");
 747                         Assert.AreEqual (DBNull.Value, table.Rows [1] ["result_min"], "#E2");
 748
 749                         Assert.AreEqual (0, table.Rows [0] ["result_var"], "#F1");
 750                         Assert.AreEqual (DBNull.Value, table.Rows [1] ["result_var"], "#F2");
 751
 752                         Assert.AreEqual (0, table.Rows [0] ["result_stdev"], "#G1");
 753                         Assert.AreEqual (DBNull.Value, table.Rows [1] ["result_stdev"], "#G2");
 754                 }
 755
 756                 [Test]
 757                 public void Aggregation_TestForSyntaxErrors ()
 758                 {
 759                         string error = "Aggregation functions cannot be called on Singular(Parent) Columns";
 760                         DataSet ds = new DataSet ();
 761                         DataTable table1 = new DataTable ();
 762                         DataTable table2 = new DataTable ();
 763                         DataTable table3 = new DataTable ();
 764
 765                         table1.Columns.Add ("test", typeof(int));
 766                         table2.Columns.Add ("test", typeof(int));
 767                         table3.Columns.Add ("test", typeof(int));
 768
 769                         DataRelation rel1 = new DataRelation ("rel1", table1.Columns[0], table2.Columns[0]);
 770                         DataRelation rel2 = new DataRelation ("rel2", table2.Columns[0], table3.Columns[0]);
 771
 772                         ds.Tables.Add (table1);
 773                         ds.Tables.Add (table2);
 774                         ds.Tables.Add (table3);
 775                         ds.Relations.Add (rel1);
 776                         ds.Relations.Add (rel2);
 777
 778                         error = "Aggregation Functions cannot be called on Columns Returning Single Row (Parent Column)";
 779                         try {
 780                                 table2.Columns.Add ("result", typeof (int), "count(parent.test)");
 781                                 Assert.Fail ("#1" + error);
 782                         } catch (SyntaxErrorException) {
 783                         }
 784
 785                         error = "Numerical or Functions cannot be called on Columns Returning Multiple Rows (Child Column)";
 786                         // Check arithematic operator
 787                         try {
 788                                 table2.Columns.Add ("result", typeof (int), "10*(child.test)");
 789                                 Assert.Fail ("#2" + error);
 790                         } catch (SyntaxErrorException) {
 791                         }
 792
 793                         // Check rel operator
 794                         try {
 795                                 table2.Columns.Add ("result", typeof (int), "(child.test) > 10");
 796                                 Assert.Fail ("#3" + error);
 797                         } catch (SyntaxErrorException) {
 798                         }
 799
 800                         // Check predicates
 801                         try {
 802                                 table2.Columns.Add ("result", typeof (int), "(child.test) IN (1,2,3)");
 803                                 Assert.Fail ("#4" + error);
 804                         } catch (SyntaxErrorException) {
 805                         }
 806
 807                         try {
 808                                 table2.Columns.Add ("result", typeof (int), "(child.test) LIKE 1");
 809                                 Assert.Fail ("#5" + error);
 810                         } catch (SyntaxErrorException) {
 811                         }
 812
 813                         try {
 814                                 table2.Columns.Add ("result", typeof (int), "(child.test) IS null");
 815                                 Assert.Fail ("#6" + error);
 816                         } catch (SyntaxErrorException) {
 817                         }
 818
 819                         // Check Calc Functions
 820                         try {
 821                                 table2.Columns.Add ("result", typeof (int), "isnull(child.test,10)");
 822                                 Assert.Fail ("#7" + error);
 823                         } catch (SyntaxErrorException) {
 824                         }
 825                 }
 826
 827                 [Test]
 828                 [Ignore ("passes in ms.net but looks more like a bug in ms.net")]
 829                 public void ExpressionColumns_CheckConversions ()
 830                 {
 831                         DataTable table = new DataTable ();
 832                         table.Columns.Add ("result_int_div", typeof (int), "(5/10) + 0.5");
 833                         table.Columns.Add ("result_float_div", typeof (int), "(5.0/10) + 0.5");
 834                         table.Rows.Add (new object[] {});
 835
 836                         // ms.net behavior.. seems to covert all numbers to double
 837                         Assert.AreEqual (1, table.Rows [0] [0], "#1");
 838                         Assert.AreEqual (1, table.Rows [0] [1], "#2");
 839                 }
 840
 841                 [Test]
 842                 public void CheckValuesAfterRemovedFromCollection ()
 843                 {
 844                         DataTable table = new DataTable ("table1");
 845                         DataColumn col1 = new DataColumn ("col1", typeof (int));
 846                         DataColumn col2 = new DataColumn ("col2", typeof (int));
 847
 848                         Assert.AreEqual (-1, col1.Ordinal, "#A1");
 849                         Assert.IsNull (col1.Table, "#A2");
 850
 851                         table.Columns.Add (col1);
 852                         table.Columns.Add (col2);
 853                         Assert.AreEqual (0, col1.Ordinal, "#B1");
 854                         Assert.AreEqual (table, col1.Table, "#B2");
 855
 856                         table.Columns.RemoveAt(0);
 857                         Assert.AreEqual (-1, col1.Ordinal, "#C1");
 858                         Assert.IsNull (col1.Table, "#C2");
 859
 860                         table.Columns.Clear ();
 861                         Assert.AreEqual (-1, col2.Ordinal, "#D1");
 862                         Assert.IsNull (col2.Table, "#D2");
 863                 }
 864
 865                 [Test]
 866                 public void B565616_NonIConvertibleTypeTest ()
 867                 {
 868                         try {
 869                                 DataTable dt = new DataTable ();
 870                                 Guid id = Guid.NewGuid();
 871                                 dt.Columns.Add ("ID", typeof(string));
 872                                 DataRow row = dt.NewRow ();
 873                                 row["ID"]= id;
 874                                 Assert.AreEqual (id.ToString(), row["ID"], "#N1");
 875                         } catch (InvalidCastException ex) {
 876                                 Assert.Fail ("#NonIConvertibleType Test");
 877                         }
 878                 }
 879
 880                 [Test]
 881                 public void B623451_SetOrdinalTest ()
 882                 {
 883                         try {
 884                                 DataTable t = new DataTable();
 885                                 t.Columns.Add("one");
 886                                 t.Columns.Add("two");
 887                                 t.Columns.Add("three");
 888                                 Assert.AreEqual ("one", t.Columns[0].ColumnName, "#SO1-1");
 889                                 Assert.AreEqual ("two", t.Columns[1].ColumnName, "#SO1-2");
 890                                 Assert.AreEqual ("three", t.Columns[2].ColumnName, "#SO1-3");
 891
 892                                 t.Columns["three"].SetOrdinal(0);
 893                                 Assert.AreEqual ("three", t.Columns[0].ColumnName, "S02-1");
 894                                 Assert.AreEqual ("one", t.Columns[1].ColumnName, "S02-2");
 895                                 Assert.AreEqual ("two", t.Columns[2].ColumnName, "S02-3");
 896
 897                                 t.Columns["three"].SetOrdinal(1);
 898                                 Assert.AreEqual ("one", t.Columns[0].ColumnName, "S03-1");
 899                                 Assert.AreEqual ("three", t.Columns[1].ColumnName, "S03-2");
 900                                 Assert.AreEqual ("two", t.Columns[2].ColumnName, "S03-3");
 901                         } catch (ArgumentOutOfRangeException ex) {
 902                                 Assert.Fail ("SetOrdinalTest failed");
 903                         }
 904                 }
 905
 906                 [Test]
 907                 public void Xamarin665 ()
 908                 {
 909                         var t = new DataTable() ;
 910                         var c1 = t.Columns.Add ("c1");
 911                         var c2 = t.Columns.Add ("c2");
 912                         c2.Expression = "TRIM(ISNULL(c1,' '))";
 913                         c2.Expression = "SUBSTRING(ISNULL(c1,' '), 1, 10)";
 914                 }
 915
 916                 DataColumn MakeColumn (string col, string test)
 917                 {
 918                         return new DataColumn () {
 919                                 ColumnName = col,
 920                                 Expression = test
 921                         };
 922                 }
 923
 924 #if false
 925 // Check Windows output for the row [0] value
 926                 [Test]
 927                 public void NullStrings ()
 928                 {
 929                         var a = MakeColumn ("nullbar", "null+'bar'");
 930                         var b = MakeColumn ("barnull", "'bar'+null");
 931                         var c = MakeColumn ("foobar", "'foo'+'bar'");
 932
 933                         var table = new DataTable();
 934
 935                         table.Columns.Add(a);
 936                         table.Columns.Add(b);
 937                         table.Columns.Add(c);
 938
 939                         var row = table.NewRow();
 940                         table.Rows.Add(row);
 941                         Assert.AreEqual (row [0], DBNull.Value, "#1");
 942                         Assert.AreEqual (row [1], DBNull.Value, "#2");
 943                         Assert.AreEqual (row [2], "foobar", "#3");
 944                 }
 945 #endif
 946         }
 947 }