1 // DataColumnTest.cs - NUnit Test Cases for System.Data.DataColumn
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>
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
16 // Copyright 2011 Xamarin Inc (http://www.xamarin.com)
17 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
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:
27 // The above copyright notice and this permission notice shall be
28 // included in all copies or substantial portions of the Software.
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.
40 using System.ComponentModel;
42 using System.Data.SqlTypes;
44 using NUnit.Framework;
46 namespace MonoTests.System.Data
49 public class DataColumnTest
51 private DataTable _tbl;
54 public void GetReady ()
56 _tbl = new DataTable();
62 string colName = "ColName";
63 DataColumn col = new DataColumn ();
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);
73 public void Constructor3_DataType_Null ()
76 new DataColumn ("ColName", (Type) null);
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");
89 public void AllowDBNull()
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;
97 col.AllowDBNull = false;
98 Assert.Fail ("DC8b: Failed to throw DataException.");
99 } catch (DataException) {
104 public void AllowDBNull1()
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 ();
119 DataColumn col = tbl.Columns ["name"];
120 col.AllowDBNull = true;
121 col.AllowDBNull = false;
123 Assert.IsFalse (col.AllowDBNull);
127 public void AutoIncrement()
129 DataColumn col = new DataColumn("Auto",typeof (string));
130 col.AutoIncrement = true;
132 //Check for Correct Default Values
133 Assert.AreEqual (0L, col.AutoIncrementSeed, "#1");
134 Assert.AreEqual (1L, col.AutoIncrementStep, "#2");
136 //Check for auto type convert
137 Assert.AreEqual (typeof (int), col.DataType, "#3");
141 public void AutoIncrementExceptions()
143 DataColumn col = new DataColumn();
144 col.Expression = "SomeExpression";
146 //if computed column exception is thrown
148 col.AutoIncrement = true;
149 Assert.Fail ("DC12: Failed to throw ArgumentException");
150 } catch (ArgumentException) {
155 public void Caption()
157 DataColumn col = new DataColumn("ColName");
158 //Caption not set at this point
159 Assert.AreEqual (col.ColumnName, col.Caption, "#1");
162 col.Caption = "MyCaption";
163 Assert.AreEqual ("MyCaption", col.Caption, "#2");
167 Assert.AreEqual (string.Empty, col.Caption, "#3");
171 public void DateTimeMode_Valid ()
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");
183 public void DateTime_DataType_Invalid ()
185 DataColumn col = new DataColumn ("birthdate", typeof (int));
187 col.DateTimeMode = DataSetDateTime.Local;
189 } catch (InvalidOperationException ex) {
190 // The DateTimeMode can be set only on DataColumns
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");
201 public void DateTimeMode_Invalid ()
203 DataColumn col = new DataColumn ("birthdate", typeof (DateTime));
205 col.DateTimeMode = (DataSetDateTime) 666;
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");
219 public void ForColumnNameException()
221 DataColumn col = new DataColumn();
222 DataColumn col2 = new DataColumn();
223 DataColumn col3 = new DataColumn();
224 DataColumn col4 = new DataColumn();
226 col.ColumnName = "abc";
227 Assert.AreEqual ("abc", col.ColumnName, "#1");
229 _tbl.Columns.Add(col);
231 //Duplicate name exception
233 col2.ColumnName = "abc";
234 _tbl.Columns.Add(col2);
235 Assert.AreEqual ("abc", col2.ColumnName, "#2");
237 } catch (DuplicateNameException) {
239 // Make sure case matters in duplicate checks
240 col3.ColumnName = "ABC";
241 _tbl.Columns.Add(col3);
245 public void DefaultValue()
247 DataTable tbl = new DataTable();
248 tbl.Columns.Add("MyCol", typeof(int));
250 //Set default Value if Autoincrement is true
251 tbl.Columns[0].AutoIncrement = true;
253 tbl.Columns[0].DefaultValue = 2;
254 Assert.Fail ("DC19: Failed to throw ArgumentException.");
255 } catch (ArgumentException) {
258 tbl.Columns[0].AutoIncrement = false;
260 //Set default value to an incompatible datatype
262 tbl.Columns[0].DefaultValue = "hello";
263 Assert.Fail ("DC21: Failed to throw FormatException.");
264 } catch (FormatException) {
267 //TODO: maybe add tests for setting default value for types that can implict
272 public void SetDataType ()
274 //test for DataAlready exists and change the datatype
276 //AutoInc column dataType supported
280 public void Defaults1 ()
282 //Check for defaults - ColumnName not set at the beginning
283 DataTable table = new DataTable();
284 DataColumn column = new DataColumn();
286 Assert.AreEqual (String.Empty, column.ColumnName, "#A1");
287 Assert.AreEqual (typeof (string), column.DataType, "#A2");
289 table.Columns.Add(column);
291 Assert.AreEqual ("Column1", table.Columns [0].ColumnName, "#B1");
292 Assert.AreEqual (typeof (string), table.Columns [0].DataType, "#B2");
294 DataRow row = table.NewRow();
296 DataRow dataRow = table.Rows[0];
298 object v = dataRow.ItemArray [0];
299 Assert.AreEqual (typeof (DBNull), v.GetType (), "#C1");
300 Assert.AreEqual (DBNull.Value, v, "#C2");
304 public void Defaults2 ()
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);
312 Assert.AreEqual (blah, column.ColumnName, "#A1");
313 Assert.AreEqual (typeof (string), column.DataType, "#A2");
315 table.Columns.Add(column);
317 Assert.AreEqual (blah, table.Columns[0].ColumnName, "#B1");
318 Assert.AreEqual (typeof (string), table.Columns[0].DataType, "#B2");
320 DataRow row = table.NewRow();
322 DataRow dataRow = table.Rows[0];
324 object v = dataRow.ItemArray[0];
325 Assert.AreEqual (typeof (DBNull), v.GetType (), "#C1");
326 Assert.AreEqual (DBNull.Value, v, "#C2");
330 public void Defaults3 ()
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
343 [ExpectedException (typeof (DataException))]
344 public void ChangeTypeAfterSettingDefaultValue ()
346 DataColumn col = new DataColumn ("foo", typeof (SqlBoolean));
347 col.DefaultValue = true;
348 col.DataType = typeof (int);
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");
358 c.Expression = "SUBSTRING(aaa, 6000000000000000, 2)";
360 } catch (OverflowException) {
365 public void ExpressionFunctions ()
367 DataTable T = new DataTable ("test");
368 DataColumn C = new DataColumn ("name");
370 C = new DataColumn ("age");
371 C.DataType = typeof (int);
373 C = new DataColumn ("id");
374 C.Expression = "substring (name, 1, 3) + len (name) + age";
377 DataSet Set = new DataSet ("TestSet");
381 for (int i = 0; i < 100; i++) {
383 Row [0] = "human" + i;
390 Row [1] = DBNull.Value;
393 Assert.AreEqual ("hum710", T.Rows [10] [2], "#A1");
394 Assert.AreEqual ("hum64", T.Rows [4] [2], "#A2");
396 C.Expression = "isnull (age, 'succ[[]]ess')";
397 Assert.AreEqual ("succ[[]]ess", T.Rows [100] [2], "#A3");
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");
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");
412 // The expression contains undefined function call iff().
413 C.Expression = "iff (age = 24, 'hurrey', 'boo')";
415 } catch (EvaluateException) {
416 } catch (SyntaxErrorException) {
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.
423 C.Expression = "iif (nimi = 24, 'hurrey', 'boo')";
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");
432 C.Expression = "iif (name = 24, 'hurrey', 'boo')";
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);
440 C.Expression = "convert (age, Boolean)";
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");
450 public void ExpressionAggregates ()
452 DataTable T = new DataTable ("test");
453 DataTable T2 = new DataTable ("test2");
455 DataColumn C = new DataColumn ("name");
457 C = new DataColumn ("age");
458 C.DataType = typeof (int);
460 C = new DataColumn ("childname");
463 C = new DataColumn ("expression");
466 DataSet Set = new DataSet ("TestSet");
471 for (int i = 0; i < 100; i++) {
473 Row [0] = "human" + i;
475 Row [2] = "child" + i;
481 Row [1] = DBNull.Value;
484 C = new DataColumn ("name");
486 C = new DataColumn ("age");
487 C.DataType = typeof (int);
490 for (int i = 0; i < 100; i++) {
492 Row [0] = "child" + i;
496 Row [0] = "child" + i;
501 DataRelation Rel = new DataRelation ("Rel", T.Columns [2], T2.Columns [0]);
502 Set.Relations.Add (Rel);
505 C.Expression = "Sum (Child.age)";
506 Assert.AreEqual ("-2", T.Rows [0] [3], "#A1");
507 Assert.AreEqual ("98", T.Rows [50] [3], "#A2");
509 C.Expression = "Count (Child.age)";
510 Assert.AreEqual ("2", T.Rows [0] [3], "#B1");
511 Assert.AreEqual ("2", T.Rows [60] [3], "#B2");
513 C.Expression = "Avg (Child.age)";
514 Assert.AreEqual ("-1", T.Rows [0] [3], "#C1");
515 Assert.AreEqual ("59", T.Rows [60] [3], "#C2");
517 C.Expression = "Min (Child.age)";
518 Assert.AreEqual ("-2", T.Rows [0] [3], "#D1");
519 Assert.AreEqual ("58", T.Rows [60] [3], "#D2");
521 C.Expression = "Max (Child.age)";
522 Assert.AreEqual ("0", T.Rows [0] [3], "#E1");
523 Assert.AreEqual ("60", T.Rows [60] [3], "#E2");
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");
529 C.Expression = "var (Child.age)";
530 Assert.AreEqual ("2", T.Rows [0] [3], "#G1");
531 Assert.AreEqual ("2", T.Rows [60] [3], "#G2");
535 public void ExpressionOperator ()
537 DataTable T = new DataTable ("test");
538 DataColumn C = new DataColumn ("name");
540 C = new DataColumn ("age");
541 C.DataType = typeof (int);
543 C = new DataColumn ("id");
544 C.Expression = "substring (name, 1, 3) + len (name) + age";
547 DataSet Set = new DataSet ("TestSet");
551 for (int i = 0; i < 100; i++) {
553 Row [0] = "human" + i;
560 Row [1] = DBNull.Value;
564 C.Expression = "age + 4";
565 Assert.AreEqual ("68", T.Rows [64] [2], "#A");
567 C.Expression = "age - 4";
568 Assert.AreEqual ("60", T.Rows [64] [2], "#B");
570 C.Expression = "age * 4";
571 Assert.AreEqual ("256", T.Rows [64] [2], "#C");
573 C.Expression = "age / 4";
574 Assert.AreEqual ("16", T.Rows [64] [2], "#D");
576 C.Expression = "age % 5";
577 Assert.AreEqual ("4", T.Rows [64] [2], "#E");
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");
583 C.Expression = "name like 'human1%'";
584 Assert.AreEqual ("True", T.Rows [1] [2], "#G1");
585 Assert.AreEqual ("False", T.Rows [25] [2], "#G2");
587 C.Expression = "age < 4";
588 Assert.AreEqual ("False", T.Rows [4] [2], "#H1");
589 Assert.AreEqual ("True", T.Rows [3] [2], "#H2");
591 C.Expression = "age <= 4";
592 Assert.AreEqual ("True", T.Rows [4] [2], "#I1");
593 Assert.AreEqual ("False", T.Rows [5] [2], "#I2");
595 C.Expression = "age > 4";
596 Assert.AreEqual ("False", T.Rows [4] [2], "#J1");
597 Assert.AreEqual ("True", T.Rows [5] [2], "#J2");
599 C.Expression = "age >= 4";
600 Assert.AreEqual ("True", T.Rows [4] [2], "#K1");
601 Assert.AreEqual ("False", T.Rows [1] [2], "#K2");
603 C.Expression = "age = 4";
604 Assert.AreEqual ("True", T.Rows [4] [2], "#L1");
605 Assert.AreEqual ("False", T.Rows [1] [2], "#L2");
607 C.Expression = "age <> 4";
608 Assert.AreEqual ("False", T.Rows [4] [2], "#M1");
609 Assert.AreEqual ("True", T.Rows [1] [2], "#M2");
613 public void SetMaxLengthException ()
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));
621 ds.Tables ["MyType"].Columns ["Desc"].MaxLength = 32;
623 } catch (ArgumentException) {
628 public void SetMaxLengthNegativeValue ()
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;
639 public void AdditionToConstraintCollectionTest()
641 DataTable myTable = new DataTable("myTable");
642 DataColumn idCol = new DataColumn("id", typeof (int));
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);
652 public void CalcStatisticalFunction_SingleElement()
654 DataTable table = new DataTable ();
655 table.Columns.Add ("test", typeof (int));
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)");
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");
667 public void Aggregation_CheckIfChangesDynamically()
669 DataTable table = new DataTable ();
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)");
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");
690 table.Rows.Add (new object[] {1});
691 table.Rows.Add (new object[] {-2});
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");
704 public void Aggregation_CheckIfChangesDynamically_ChildTable ()
706 DataSet ds = new DataSet ();
708 DataTable table = new DataTable ();
709 DataTable table2 = new DataTable ();
710 ds.Tables.Add (table);
711 ds.Tables.Add (table2);
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);
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)");
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});
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");
737 Assert.AreEqual (10, table.Rows [0] ["result_sum"], "#B1");
738 Assert.AreEqual (DBNull.Value, table.Rows [1] ["result_sum"], "#B2");
740 Assert.AreEqual (1, table.Rows [0] ["result_avg"], "#C1");
741 Assert.AreEqual (DBNull.Value, table.Rows [1] ["result_avg"], "#C2");
743 Assert.AreEqual (1, table.Rows [0] ["result_max"], "#D1");
744 Assert.AreEqual (DBNull.Value, table.Rows [1] ["result_max"], "#D2");
746 Assert.AreEqual (1, table.Rows [0] ["result_min"], "#E1");
747 Assert.AreEqual (DBNull.Value, table.Rows [1] ["result_min"], "#E2");
749 Assert.AreEqual (0, table.Rows [0] ["result_var"], "#F1");
750 Assert.AreEqual (DBNull.Value, table.Rows [1] ["result_var"], "#F2");
752 Assert.AreEqual (0, table.Rows [0] ["result_stdev"], "#G1");
753 Assert.AreEqual (DBNull.Value, table.Rows [1] ["result_stdev"], "#G2");
757 public void Aggregation_TestForSyntaxErrors ()
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 ();
765 table1.Columns.Add ("test", typeof(int));
766 table2.Columns.Add ("test", typeof(int));
767 table3.Columns.Add ("test", typeof(int));
769 ds.Tables.Add (table1);
770 ds.Tables.Add (table2);
771 ds.Tables.Add (table3);
773 DataRelation rel1 = new DataRelation ("rel1", table1.Columns[0], table2.Columns[0]);
774 DataRelation rel2 = new DataRelation ("rel2", table2.Columns[0], table3.Columns[0]);
776 ds.Relations.Add (rel1);
777 ds.Relations.Add (rel2);
779 error = "Aggregation Functions cannot be called on Columns Returning Single Row (Parent Column)";
781 table2.Columns.Add ("result", typeof (int), "count(parent.test)");
782 Assert.Fail ("#1" + error);
783 } catch (SyntaxErrorException) {
786 error = "Numerical or Functions cannot be called on Columns Returning Multiple Rows (Child Column)";
787 // Check arithematic operator
789 table2.Columns.Add ("result", typeof (int), "10*(child.test)");
790 Assert.Fail ("#2" + error);
791 } catch (SyntaxErrorException) {
794 // Check rel operator
796 table2.Columns.Add ("result", typeof (int), "(child.test) > 10");
797 Assert.Fail ("#3" + error);
798 } catch (SyntaxErrorException) {
803 table2.Columns.Add ("result", typeof (int), "(child.test) IN (1,2,3)");
804 Assert.Fail ("#4" + error);
805 } catch (SyntaxErrorException) {
809 table2.Columns.Add ("result", typeof (int), "(child.test) LIKE 1");
810 Assert.Fail ("#5" + error);
811 } catch (SyntaxErrorException) {
815 table2.Columns.Add ("result", typeof (int), "(child.test) IS null");
816 Assert.Fail ("#6" + error);
817 } catch (SyntaxErrorException) {
820 // Check Calc Functions
822 table2.Columns.Add ("result", typeof (int), "isnull(child.test,10)");
823 Assert.Fail ("#7" + error);
824 } catch (SyntaxErrorException) {
829 [Ignore ("passes in ms.net but looks more like a bug in ms.net")]
830 public void ExpressionColumns_CheckConversions ()
832 DataTable table = new DataTable ();
833 table.Columns.Add ("result_int_div", typeof (int), "(5/10) + 0.5");
834 table.Columns.Add ("result_float_div", typeof (int), "(5.0/10) + 0.5");
835 table.Rows.Add (new object[] {});
837 // ms.net behavior.. seems to covert all numbers to double
838 Assert.AreEqual (1, table.Rows [0] [0], "#1");
839 Assert.AreEqual (1, table.Rows [0] [1], "#2");
843 public void CheckValuesAfterRemovedFromCollection ()
845 DataTable table = new DataTable ("table1");
846 DataColumn col1 = new DataColumn ("col1", typeof (int));
847 DataColumn col2 = new DataColumn ("col2", typeof (int));
849 Assert.AreEqual (-1, col1.Ordinal, "#A1");
850 Assert.IsNull (col1.Table, "#A2");
852 table.Columns.Add (col1);
853 table.Columns.Add (col2);
854 Assert.AreEqual (0, col1.Ordinal, "#B1");
855 Assert.AreEqual (table, col1.Table, "#B2");
857 table.Columns.RemoveAt(0);
858 Assert.AreEqual (-1, col1.Ordinal, "#C1");
859 Assert.IsNull (col1.Table, "#C2");
861 table.Columns.Clear ();
862 Assert.AreEqual (-1, col2.Ordinal, "#D1");
863 Assert.IsNull (col2.Table, "#D2");
867 public void B565616_NonIConvertibleTypeTest ()
870 DataTable dt = new DataTable ();
871 Guid id = Guid.NewGuid();
872 dt.Columns.Add ("ID", typeof(string));
873 DataRow row = dt.NewRow ();
875 Assert.AreEqual (id.ToString(), row["ID"], "#N1");
876 } catch (InvalidCastException ex) {
877 Assert.Fail ("#NonIConvertibleType Test");
882 public void B623451_SetOrdinalTest ()
885 DataTable t = new DataTable();
886 t.Columns.Add("one");
887 t.Columns.Add("two");
888 t.Columns.Add("three");
889 Assert.AreEqual ("one", t.Columns[0].ColumnName, "#SO1-1");
890 Assert.AreEqual ("two", t.Columns[1].ColumnName, "#SO1-2");
891 Assert.AreEqual ("three", t.Columns[2].ColumnName, "#SO1-3");
893 t.Columns["three"].SetOrdinal(0);
894 Assert.AreEqual ("three", t.Columns[0].ColumnName, "S02-1");
895 Assert.AreEqual ("one", t.Columns[1].ColumnName, "S02-2");
896 Assert.AreEqual ("two", t.Columns[2].ColumnName, "S02-3");
898 t.Columns["three"].SetOrdinal(1);
899 Assert.AreEqual ("one", t.Columns[0].ColumnName, "S03-1");
900 Assert.AreEqual ("three", t.Columns[1].ColumnName, "S03-2");
901 Assert.AreEqual ("two", t.Columns[2].ColumnName, "S03-3");
902 } catch (ArgumentOutOfRangeException ex) {
903 Assert.Fail ("SetOrdinalTest failed");
908 public void Xamarin665 ()
910 var t = new DataTable() ;
911 var c1 = t.Columns.Add ("c1");
912 var c2 = t.Columns.Add ("c2");
913 c2.Expression = "TRIM(ISNULL(c1,' '))";
914 c2.Expression = "SUBSTRING(ISNULL(c1,' '), 1, 10)";
917 DataColumn MakeColumn (string col, string test)
919 return new DataColumn () {
926 // Check Windows output for the row [0] value
928 public void NullStrings ()
930 var a = MakeColumn ("nullbar", "null+'bar'");
931 var b = MakeColumn ("barnull", "'bar'+null");
932 var c = MakeColumn ("foobar", "'foo'+'bar'");
934 var table = new DataTable();
936 table.Columns.Add(a);
937 table.Columns.Add(b);
938 table.Columns.Add(c);
940 var row = table.NewRow();
942 Assert.AreEqual (row [0], DBNull.Value, "#1");
943 Assert.AreEqual (row [1], DBNull.Value, "#2");
944 Assert.AreEqual (row [2], "foobar", "#3");