2 // SqlDataReaderTest.cs - NUnit Test Cases for testing the
5 // Umadevi S (sumadevi@novell.com)
6 // Kornél Pál <http://www.kornelpal.hu/>
7 // Sureshkumar T (tsureshkumar@novell.com)
8 // Senganal T (tsenganal@novell.com)
10 // Copyright (c) 2004 Novell Inc., and the individuals listed
11 // on the ChangeLog entries.
13 // Permission is hereby granted, free of charge, to any person obtaining
14 // a copy of this software and associated documentation files (the
15 // "Software"), to deal in the Software without restriction, including
16 // without limitation the rights to use, copy, modify, merge, publish,
17 // distribute, sublicense, and/or sell copies of the Software, and to
18 // permit persons to whom the Software is furnished to do so, subject to
19 // the following conditions:
21 // The above copyright notice and this permission notice shall be
22 // included in all copies or substantial portions of the Software.
24 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
25 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
26 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
27 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
28 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
29 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
30 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
36 using System.Data.SqlTypes;
37 using System.Data.Common;
38 using System.Data.SqlClient;
40 using NUnit.Framework;
42 namespace MonoTests.System.Data.SqlClient
46 [Category ("sqlserver")]
47 public class SqlDataReaderTest
49 SqlConnection conn = null;
50 SqlCommand cmd = null;
51 SqlDataReader reader = null;
52 String query = "Select type_{0},type_{1},convert({0},null) from numeric_family where id=1";
53 DataSet sqlDataset = null;
55 DataTable numericDataTable =null;
56 DataTable stringDataTable =null;
57 DataTable binaryDataTable =null;
58 DataTable datetimeDataTable =null;
60 DataRow numericRow = null;
61 DataRow stringRow = null;
62 DataRow binaryRow = null;
63 DataRow datetimeRow = null;
68 conn = new SqlConnection (ConnectionManager.Singleton.ConnectionString);
69 cmd = conn.CreateCommand ();
71 sqlDataset = (new DataProvider()).GetDataSet ();
73 numericDataTable = sqlDataset.Tables["numeric_family"];
74 stringDataTable = sqlDataset.Tables["string_family"];
75 binaryDataTable = sqlDataset.Tables["binary_family"];
76 datetimeDataTable = sqlDataset.Tables["datetime_family"];
78 numericRow = numericDataTable.Select ("id=1")[0];
79 stringRow = stringDataTable.Select ("id=1")[0];
80 binaryRow = binaryDataTable.Select ("id=1")[0];
81 datetimeRow = datetimeDataTable.Select ("id=1")[0];
90 public void TearDown ()
99 public void ReadEmptyNTextFieldTest () {
101 DBHelper.ExecuteNonQuery (conn, "create table #tmp_monotest (name ntext)");
102 DBHelper.ExecuteNonQuery (conn, "insert into #tmp_monotest values ('')");
104 SqlCommand cmd = (SqlCommand) conn.CreateCommand ();
105 cmd.CommandText = "select * from #tmp_monotest";
106 SqlDataReader dr = cmd.ExecuteReader ();
108 Assert.AreEqual("System.String",dr["NAME"].GetType().FullName);
110 Assert.AreEqual (false, dr.Read (), "#2");
112 ConnectionManager.Singleton.CloseConnection ();
117 public void ReadBingIntTest()
120 string query = "SELECT CAST(548967465189498 AS bigint) AS Value";
121 SqlCommand cmd = new SqlCommand();
122 cmd.Connection = conn;
123 cmd.CommandText = query;
124 SqlDataReader r = cmd.ExecuteReader();
126 Assert.AreEqual (true, r.Read(), "#1");
127 long id = r.GetInt64(0);
128 Assert.AreEqual(548967465189498, id, "#2");
129 id = r.GetSqlInt64(0).Value;
130 Assert.AreEqual(548967465189498, id, "#3");
133 ConnectionManager.Singleton.CloseConnection ();
139 // This method just helps in Calling common tests among all the Get* Methods
140 // without replicating code
142 void CallGetMethod (string s, int i)
145 case "Boolean" : reader.GetBoolean (i) ; break;
146 case "SqlBoolean": reader.GetSqlBoolean (i); break;
147 case "Int16" : reader.GetInt16 (i); break;
148 case "SqlInt16" : reader.GetSqlInt16 (i); break;
149 case "Int32" : reader.GetInt32 (i);break;
150 case "SqlInt32" : reader.GetSqlInt32(i);break;
151 case "Int64" : reader.GetInt64 (i);break;
152 case "SqlInt64" : reader.GetSqlInt64(i); break;
153 case "Decimal" : reader.GetDecimal(i);break;
154 case "SqlDecimal" : reader.GetSqlDecimal (i);break;
155 case "SqlMoney" : reader.GetSqlMoney (i);break;
156 case "Float" : reader.GetFloat (i);break;
157 case "SqlSingle" : reader.GetSqlSingle(i);break;
158 case "Double" : reader.GetDouble (i);break;
159 case "SqlDouble" : reader.GetSqlDouble(i);break;
160 case "Guid" : reader.GetGuid(i);break;
161 case "SqlGuid" : reader.GetSqlGuid(i);break;
162 case "String" : reader.GetString(i);break;
163 case "SqlString" : reader.GetSqlString(i);break;
164 case "Char" : reader.GetChar(i);break;
165 case "Byte" : reader.GetByte (i);break;
166 case "SqlByte" : reader.GetSqlByte(i); break;
167 case "DateTime" : reader.GetDateTime(i); break;
168 case "SqlDateTime" : reader.GetSqlDateTime(i); break;
169 case "SqlBinary" : reader.GetSqlBinary(i); break;
170 default : Console.WriteLine ("OOOOPSSSSSS {0}",s);break;
174 // This method just helps in Calling common tests among all the Get* Methods
175 // without replicating code
176 void GetMethodTests (string s)
180 CallGetMethod (s, 1);
181 Assert.Fail ("#1[Get"+s+"] InvalidCastException must be thrown");
182 }catch (AssertionException e) {
184 }catch (Exception e) {
185 Assert.AreEqual (typeof(InvalidCastException), e.GetType(),
186 "#2[Get"+s+"] Incorrect Exception : " + e);
189 // GetSql* Methods do not throw SqlNullValueException
190 // So, Testimg only for Get* Methods
191 if (!s.StartsWith("Sql")) {
193 CallGetMethod (s, 2);
194 Assert.Fail ("#3[Get"+s+"] Exception must be thrown");
195 }catch (AssertionException e) {
197 }catch (Exception e){
198 Assert.AreEqual (typeof(SqlNullValueException),e.GetType(),
199 "#4[Get"+s+"] Incorrect Exception : " + e);
204 CallGetMethod (s, 3);
205 Assert.Fail ("#5[Get"+s+"] IndexOutOfRangeException must be thrown");
206 }catch (AssertionException e) {
208 }catch (Exception e){
209 Assert.AreEqual (typeof(IndexOutOfRangeException), e.GetType(),
210 "#6[Get"+s+"] Incorrect Exception : " + e);
215 public void GetBooleanTest ()
217 cmd.CommandText = string.Format (query, "bit", "int");
218 reader = cmd.ExecuteReader ();
220 // Test for standard exceptions
221 GetMethodTests("Boolean");
223 // Test if data is returned correctly
224 Assert.AreEqual (numericRow["type_bit"], reader.GetBoolean(0),
225 "#2 DataValidation Failed");
227 // Test for standard exceptions
228 GetMethodTests("SqlBoolean");
230 // Test if data is returned correctly
231 Assert.AreEqual (numericRow["type_bit"], reader.GetSqlBoolean(0).Value,
232 "#4 DataValidation Failed");
237 public void GetByteTest ()
239 cmd.CommandText = string.Format (query, "tinyint", "int");
240 reader = cmd.ExecuteReader ();
242 // Test for standard exceptions
243 GetMethodTests("Byte");
245 // Test if data is returned correctly
246 Assert.AreEqual (numericRow["type_tinyint"], reader.GetByte(0),
247 "#2 DataValidation Failed");
249 // Test for standard exceptions
250 GetMethodTests("SqlByte");
252 // Test if data is returned correctly
253 Assert.AreEqual (numericRow["type_tinyint"], reader.GetSqlByte(0).Value,
254 "#4 DataValidation Failed");
259 public void GetInt16Test ()
261 cmd.CommandText = string.Format (query, "smallint", "int");
262 reader = cmd.ExecuteReader();
264 // Test for standard exceptions
265 GetMethodTests("Int16");
267 // Test if data is returned correctly
268 Assert.AreEqual (numericRow["type_smallint"], reader.GetInt16(0),
269 "#2 DataValidation Failed");
271 // Test for standard exceptions
272 GetMethodTests("SqlInt16");
274 // Test if data is returned correctly
275 Assert.AreEqual (numericRow["type_smallint"], reader.GetSqlInt16(0).Value,
276 "#4 DataValidation Failed");
281 public void GetInt32Test ()
283 cmd.CommandText = string.Format (query, "int", "bigint");
284 reader = cmd.ExecuteReader ();
286 // Test for standard exceptions
287 GetMethodTests("Int32");
289 // Test if data is returned correctly
290 Assert.AreEqual (numericRow["type_int"], reader.GetInt32(0),
291 "#2 DataValidation Failed");
293 // Test for standard exceptions
294 GetMethodTests("SqlInt32");
296 // Test if data is returned correctly
297 Assert.AreEqual (numericRow["type_int"], reader.GetSqlInt32(0).Value,
298 "#4 DataValidation Failed");
303 public void GetInt64Test ()
305 cmd.CommandText = string.Format (query, "bigint", "int");
306 reader = cmd.ExecuteReader ();
309 // Test for standard exceptions
310 GetMethodTests("Int64");
312 // Test if data is returned correctly
313 Assert.AreEqual (numericRow["type_bigint"], reader.GetInt64(0),
314 "#2 DataValidation Failed");
316 // Test for standard exceptions
317 GetMethodTests("SqlInt64");
319 // Test if data is returned correctly
320 Assert.AreEqual (numericRow["type_bigint"], reader.GetSqlInt64(0).Value,
321 "#4 DataValidation Failed");
326 public void GetDecimalTest ()
328 cmd.CommandText = string.Format (query, "decimal", "int");
329 reader = cmd.ExecuteReader ();
331 // Test for standard exceptions
332 GetMethodTests("Decimal");
334 // Test if data is returned correctly
335 Assert.AreEqual (numericRow["type_decimal"], reader.GetDecimal(0),
336 "#2 DataValidation Failed");
338 // Test for standard exceptions
339 GetMethodTests("SqlDecimal");
341 // Test if data is returned correctly
342 Assert.AreEqual (numericRow["type_decimal"], reader.GetSqlDecimal(0).Value,
343 "#4 DataValidation Failed");
348 public void GetSqlMoneyTest ()
350 cmd.CommandText = string.Format (query, "money", "int");
351 reader = cmd.ExecuteReader ();
353 // Test for standard exceptions
354 GetMethodTests("SqlMoney");
356 // Test if data is returned correctly
357 Assert.AreEqual (numericRow["type_money"], reader.GetSqlMoney(0).Value,
358 "#2 DataValidation Failed");
363 public void GetFloatTest ()
365 cmd.CommandText = "select type_float,type_double,convert(real,null)";
366 cmd.CommandText += "from numeric_family where id=1";
367 reader = cmd.ExecuteReader ();
369 // Test for standard exceptions
370 GetMethodTests("Float");
372 // Test if data is returned correctly
373 Assert.AreEqual (numericRow["type_float"], reader.GetFloat(0),
374 "#2 DataValidation Failed");
376 // Test for standard exceptions
377 GetMethodTests("SqlSingle");
379 // Test if data is returned correctly
380 Assert.AreEqual (numericRow["type_float"], reader.GetSqlSingle(0).Value,
381 "#2 DataValidation Failed");
386 public void GetDoubleTest ()
388 cmd.CommandText = "select type_double,type_float,convert(float,null)";
389 cmd.CommandText += " from numeric_family where id=1";
390 reader = cmd.ExecuteReader ();
392 // Test for standard exceptions
393 GetMethodTests("Double");
395 // Test if data is returned correctly
396 Assert.AreEqual (numericRow["type_double"], reader.GetDouble(0),
397 "#2 DataValidation Failed");
399 // Test for standard exceptions
400 GetMethodTests("SqlDouble");
402 // Test if data is returned correctly
403 Assert.AreEqual (numericRow["type_double"], reader.GetSqlDouble(0).Value,
404 "#4 DataValidation Failed");
409 public void GetBytesTest ()
411 cmd.CommandText = "Select type_text,type_ntext,convert(text,null) ";
412 cmd.CommandText += "from string_family where id=1";
413 reader = cmd.ExecuteReader ();
416 reader.GetBytes (0,0,null,0,0);
417 Assert.Fail ("#1 GetBytes shud be used only wth Sequential Access");
418 }catch (AssertionException e) {
420 }catch (Exception e) {
421 Assert.AreEqual (typeof(InvalidCastException), e.GetType (),
422 "#2 Incorrect Exception : " + e);
426 byte[] asciiArray = (new ASCIIEncoding ()).GetBytes ("text");
427 byte[] unicodeArray = (new UnicodeEncoding ()).GetBytes ("ntext");
428 byte[] buffer = null ;
431 reader = cmd.ExecuteReader (CommandBehavior.SequentialAccess);
433 size = reader.GetBytes (0,0,null,0,0);
434 Assert.AreEqual (asciiArray.Length, size, "#3 Data Incorrect");
436 buffer = new byte[size];
437 size = reader.GetBytes (0,0,buffer,0,(int)size);
438 for (int i=0;i<size; i++)
439 Assert.AreEqual (asciiArray[i], buffer[i], "#4 Data Incorrect");
441 size = reader.GetBytes (1,0,null,0,0);
442 Assert.AreEqual (unicodeArray.Length, size, "#5 Data Incorrect");
443 buffer = new byte[size];
444 size = reader.GetBytes (1,0,buffer,0,(int)size);
445 for (int i=0;i<size; i++)
446 Assert.AreEqual (unicodeArray[i], buffer[i], "#6 Data Incorrect");
448 // Test if msdotnet behavior s followed when null value
449 // is read using GetBytes
450 Assert.AreEqual (0, reader.GetBytes (2,0,null,0,0), "#7");
451 reader.GetBytes (2,0,buffer,0,10);
454 // do i need to test for image/binary values also ???
458 public void GetBytes_Binary ()
460 cmd.CommandText = "Select type_binary,type_varbinary,type_blob ";
461 cmd.CommandText += "from binary_family where id=1";
462 reader = cmd.ExecuteReader ();
464 byte[] binary = (byte[])reader.GetValue (0);
465 byte[] varbinary = (byte[])reader.GetValue (1);
466 byte[] image = (byte[])reader.GetValue (2);
469 reader = cmd.ExecuteReader (CommandBehavior.SequentialAccess);
473 len = (int)reader.GetBytes (0,0,null,0,0);
474 Assert.AreEqual (binary.Length, len, "#1");
475 arr = new byte [len];
476 reader.GetBytes (0,0,arr,0,len);
477 for (int i=0; i<len; ++i)
478 Assert.AreEqual (binary[i], arr[i], "#2");
481 len = (int)reader.GetBytes (1,0,null,0,0);
482 Assert.AreEqual (varbinary.Length, len, "#1");
483 arr = new byte [len];
484 reader.GetBytes (1,0,arr,0,len);
485 for (int i=0; i<len; ++i)
486 Assert.AreEqual (varbinary[i], arr[i], "#2");
488 len = (int)reader.GetBytes (2,0,null,0,0);
489 Assert.AreEqual (image.Length, len, "#1");
490 arr = new byte [len];
491 reader.GetBytes (2,0,arr,0,len);
492 for (int i=0; i<len; ++i)
493 Assert.AreEqual (image[i], arr[i], "#2");
497 cmd.CommandText = "Select type_binary,type_varbinary,type_blob ";
498 cmd.CommandText += "from binary_family where id=1";
500 reader = cmd.ExecuteReader (CommandBehavior.SequentialAccess);
503 len = (int)reader.GetBytes (0,0,null,0,0);
504 arr = new byte [100];
505 for (int i=0; i<len; ++i) {
506 Assert.AreEqual (len-i, reader.GetBytes (0, i, null, 0, 0), "#1_"+i);
507 Assert.AreEqual (1, reader.GetBytes (0, i, arr, 0, 1), "#2_"+i);
508 Assert.AreEqual (binary [i], arr [0], "#3_"+i);
510 Assert.AreEqual (0, reader.GetBytes (0, len+10, null, 0, 0));
515 public void GetChars ()
517 cmd.CommandText = "Select type_char, type_varchar,type_text, type_ntext ";
518 cmd.CommandText += "from string_family where id=1";
519 reader = cmd.ExecuteReader ();
521 string charstring = reader.GetString (0);
522 //string ncharstring = reader.GetString (1);
523 string varcharstring = reader.GetString (1);
524 //string nvarcharstring = reader.GetString (2);
525 string textstring = reader.GetString (2);
526 string ntextstring = reader.GetString (3);
529 reader = cmd.ExecuteReader (CommandBehavior.SequentialAccess);
534 len = (int)reader.GetChars (0,0,null,0,0);
535 Assert.AreEqual (charstring.Length, len, "#1");
536 arr = new char [len];
537 reader.GetChars (0,0,arr,0,len);
538 Assert.AreEqual (0, charstring.CompareTo (new String (arr)), "#2");
540 len = (int)reader.GetChars (1,0,null,0,0);
541 Assert.AreEqual (varcharstring.Length, len, "#3");
542 arr = new char [len];
543 reader.GetChars (1,0,arr,0,len);
544 Assert.AreEqual (0, varcharstring.CompareTo (new String (arr)), "#4");
546 len = (int)reader.GetChars (2,0,null,0,0);
547 Assert.AreEqual (textstring.Length, len, "#5");
548 arr = new char [len];
549 reader.GetChars (2,0,arr,0,len);
550 Assert.AreEqual (0, textstring.CompareTo (new String (arr)), "#6");
552 len = (int)reader.GetChars (3,0,null,0,0);
553 Assert.AreEqual (ntextstring.Length, len, "#7");
554 arr = new char [len];
555 reader.GetChars (3,0,arr,0,len);
556 Assert.AreEqual (0, ntextstring.CompareTo (new String (arr)), "#8");
560 reader = cmd.ExecuteReader (CommandBehavior.SequentialAccess);
563 len = (int)reader.GetChars (0,0,null,0,0);
565 for (int i=0; i<len; ++i) {
566 Assert.AreEqual (len-i, reader.GetChars (0, i, null, 0, 0), "#9_"+i);
567 Assert.AreEqual (1, reader.GetChars (0, i, arr, 0, 1), "#10_"+i);
568 Assert.AreEqual (charstring [i], arr [0], "#11_"+i);
570 Assert.AreEqual (0, reader.GetChars (0, len+10, null, 0, 0));
576 public void GetStringTest ()
578 cmd.CommandText = "Select type_varchar,10,convert(varchar,null)";
579 cmd.CommandText += "from string_family where id=1";
580 reader = cmd.ExecuteReader ();
582 // Test for standard exceptions
583 GetMethodTests("String");
585 // Test if data is returned correctly
586 Assert.AreEqual (stringRow["type_varchar"], reader.GetString(0),
587 "#2 DataValidation Failed");
589 // Test for standard exceptions
590 GetMethodTests("SqlString");
592 // Test if data is returned correctly
593 Assert.AreEqual (stringRow["type_varchar"], reader.GetSqlString(0).Value,
594 "#4 DataValidation Failed");
599 public void GetSqlBinaryTest ()
601 cmd.CommandText = "Select type_binary ,10 ,convert(binary,null)";
602 cmd.CommandText += "from binary_family where id=1";
603 reader = cmd.ExecuteReader ();
605 // Test for standard exceptions
606 GetMethodTests ("SqlBinary");
608 // Test if data is returned correctly
609 Assert.AreEqual (binaryRow["type_binary"], reader.GetSqlBinary(0).Value,
610 "#2 DataValidation Failed");
615 public void GetGuidTest ()
617 cmd.CommandText = "Select type_guid,id,convert(uniqueidentifier,null)";
618 cmd.CommandText += "from string_family where id=1";
619 reader = cmd.ExecuteReader ();
622 // Test for standard exceptions
623 GetMethodTests("Guid");
625 // Test if data is returned correctly
626 Assert.AreEqual (stringRow["type_guid"], reader.GetGuid(0),
627 "#2 DataValidation Failed");
629 // Test for standard exceptions
630 GetMethodTests("SqlGuid");
632 // Test if data is returned correctly
633 Assert.AreEqual (stringRow["type_guid"], reader.GetSqlGuid(0).Value,
634 "#4 DataValidation Failed");
639 public void GetDateTimeTest ()
641 cmd.CommandText = "Select type_datetime,10,convert(datetime,null)";
642 cmd.CommandText += "from datetime_family where id=1";
643 reader = cmd.ExecuteReader ();
646 // Test for standard exceptions
647 GetMethodTests("DateTime");
649 // Test if data is returned correctly
650 Assert.AreEqual (datetimeRow["type_datetime"], reader.GetDateTime(0),
651 "#2 DataValidation Failed");
653 // Test for standard exceptions
654 GetMethodTests("SqlDateTime");
656 // Test if data is returned correctly
657 Assert.AreEqual (datetimeRow["type_datetime"], reader.GetSqlDateTime(0).Value,
658 "#2 DataValidation Failed");
663 [Ignore ("Not Supported by msdotnet")]
664 public void GetCharTest ()
666 cmd.CommandText = "Select type_char,type_guid,convert(char,null)";
667 cmd.CommandText += "from string_family where id=1";
668 reader = cmd.ExecuteReader ();
670 // Test for standard exceptions
671 GetMethodTests ("Char");
676 public void GetValueTest ()
678 cmd.CommandText = "Select id, null from numeric_family where id=1";
679 reader = cmd.ExecuteReader ();
683 obj = reader.GetValue (0);
684 Assert.AreEqual ((byte)1, obj, "#1 Shud return the value of id");
685 obj = reader.GetValue (1);
686 Assert.AreEqual (DBNull.Value, obj, "#2 shud return DBNull");
691 public void GetSqlValueTest ()
693 cmd.CommandText = "Select id, type_tinyint, null from numeric_family where id=1";
694 reader = cmd.ExecuteReader ();
697 Assert.AreEqual ((byte)255, ((SqlByte) reader.GetSqlValue(1)).Value, "#1");
698 //Assert.AreEqual (DBNull.Value, reader.GetSqlValue(2), "#2");
704 public void GetValuesTest ()
706 cmd.CommandText = "Select 10,20,30 from numeric_family where id=1";
707 reader = cmd.ExecuteReader ();
713 count = reader.GetValues (arr);
714 Assert.AreEqual (10, (int)arr[0], "#1 Only first object shud be copied");
715 Assert.AreEqual (1, count, "#1 return value shud equal objects copied");
718 count = reader.GetValues (arr);
719 Assert.AreEqual (3, count, "#2 return value shud equal objects copied");
722 count = reader.GetValues (arr);
723 Assert.AreEqual (3, count, "#3 return value shud equal objects copied");
724 Assert.IsNull (arr[3], "#4 Only 3 objects shud be copied");
730 public void GetSqlValuesTest ()
732 cmd.CommandText = "Select 10,20,30 from numeric_family where id=1";
733 reader = cmd.ExecuteReader ();
739 count = reader.GetSqlValues (arr);
740 // Something is wrong with types ... gotta figure it out
741 //Assert.AreEqual (10, arr[0], "#1 Only first object shud be copied");
742 Assert.AreEqual (1, count, "#1 return value shud equal objects copied");
745 count = reader.GetSqlValues (arr);
746 Assert.AreEqual (3, count, "#2 return value shud equal objects copied");
749 count = reader.GetSqlValues (arr);
750 Assert.AreEqual (3, count, "#3 return value shud equal objects copied");
751 Assert.IsNull (arr[3], "#4 Only 3 objects shud be copied");
757 public void isDBNullTest ()
759 cmd.CommandText = "select id , null from numeric_family where id=1";
760 reader = cmd.ExecuteReader ();
763 Assert.IsFalse (reader.IsDBNull (0), "#1");
764 Assert.IsTrue (reader.IsDBNull (1) , "#2");
767 reader.IsDBNull (10);
768 Assert.Fail ("#1 Invalid Argument");
769 }catch (AssertionException e) {
771 }catch (Exception e) {
772 Assert.AreEqual (typeof(IndexOutOfRangeException), e.GetType(),
773 "#1 Incorrect Exception : " + e);
778 public void ReadTest ()
780 cmd.CommandText = "select id, type_bit from numeric_family where id=1" ;
781 reader = cmd.ExecuteReader ();
782 Assert.IsTrue (reader.Read () , "#1");
783 Assert.IsFalse (reader.Read (), "#2");
788 Assert.Fail ("#3 Exception shud be thrown : Reader is closed");
789 }catch (AssertionException e) {
791 }catch (Exception e) {
792 Assert.AreEqual (typeof(InvalidOperationException), e.GetType (),
793 "#4 Incorrect Exception : " + e);
798 public void NextResultTest ()
800 cmd.CommandText = "Select id from numeric_family where id=1";
801 reader = cmd.ExecuteReader ();
802 Assert.IsFalse (reader.NextResult (), "#1");
805 cmd.CommandText = "select id from numeric_family where id=1;";
806 cmd.CommandText += "select type_bit from numeric_family where id=2;";
807 reader = cmd.ExecuteReader ();
808 Assert.IsTrue (reader.NextResult (), "#2");
809 Assert.IsFalse (reader.NextResult (), "#3");
813 reader.NextResult ();
814 Assert.Fail ("#4 Exception shud be thrown : Reader is closed");
815 }catch (AssertionException e) {
817 }catch (Exception e) {
818 Assert.AreEqual (typeof(InvalidOperationException), e.GetType (),
819 "#5 Incorrect Exception : " + e);
824 public void GetNameTest ()
826 cmd.CommandText = "Select id,10 as gen,20 from numeric_family where id=1";
827 reader = cmd.ExecuteReader ();
829 Assert.AreEqual ("id" , reader.GetName(0) , "#1");
830 Assert.AreEqual ("gen" , reader.GetName(1) , "#2");
834 Assert.Fail ("#4 Exception shud be thrown");
835 }catch (AssertionException e) {
837 }catch (Exception e) {
838 Assert.AreEqual (typeof(IndexOutOfRangeException), e.GetType(),
839 "#5 Incorrect Exception : " + e);
844 public void GetOrdinalTest ()
846 //what is kana-width insensitive ?????
847 cmd.CommandText = "Select id,10 as gen,20 from numeric_family where id=1";
848 reader = cmd.ExecuteReader ();
850 Assert.AreEqual (0, reader.GetOrdinal ("id"), "#1");
851 Assert.AreEqual (0, reader.GetOrdinal ("ID"), "#2");
852 Assert.AreEqual (1, reader.GetOrdinal ("gen"), "#3");
853 // Would expect column1,columnn2 etc for unnamed columns,
854 // but msdotnet return empty string for unnamed columns
855 Assert.AreEqual (2, reader.GetOrdinal (""), "#4");
858 reader.GetOrdinal ("invalidname");
859 }catch (AssertionException e) {
861 }catch (Exception e) {
862 Assert.AreEqual (typeof (IndexOutOfRangeException),
863 e.GetType(), "#4 Incorrect Exception : " + e);
868 public void GetSchemaTableTest ()
870 cmd.CommandText = "Select type_decimal as decimal,id,10 ";
871 cmd.CommandText += "from numeric_family where id=1";
872 reader = cmd.ExecuteReader (CommandBehavior.KeyInfo);
873 DataTable schemaTable = reader.GetSchemaTable ();
874 DataRow row0 = schemaTable.Rows[0];
875 DataRow row1 = schemaTable.Rows[1];
877 Assert.AreEqual ("decimal", row0["ColumnName"], "#1");
878 Assert.AreEqual ("", schemaTable.Rows[2]["ColumnName"], "#2");
880 Assert.AreEqual (0, row0["ColumnOrdinal"], "#2");
881 Assert.AreEqual (17, row0["ColumnSize"], "#3");
882 Assert.AreEqual (38, row0["NumericPrecision"], "#4");
883 Assert.AreEqual (0, row0["NumericScale"], "#5");
885 Assert.AreEqual (false, row0["IsUnique"], "#6");
886 // msdotnet returns IsUnique as false for Primary key
887 // even though table consists of a single Primary Key
888 //Assert.AreEqual (true, row1["IsUnique"], "#7");
889 Assert.AreEqual (false, row0["IsKey"], "#8");
890 Assert.AreEqual (true, row1["IsKey"], "#9");
892 //Assert.AreEqual ("servername", row0["BaseServerName"], "#10");
893 //Assert.AreEqual ("monotest", row0["BaseCatalogName"], "#11");
894 Assert.AreEqual ("type_decimal", row0["BaseColumnName"], "#12");
895 //Assert.IsNull(row0["BaseSchemaName"], "#13");
896 Assert.AreEqual ("numeric_family", row0["BaseTableName"], "#14");
897 Assert.AreEqual (typeof (Decimal), row0["DataType"], "#15");
898 Assert.AreEqual (true, row0["AllowDBNull"], "#16");
899 Assert.AreEqual (false, row1["AllowDBNull"], "#17");
900 //Assert.IsNull(row0["ProviderType"], "#18");
901 Assert.AreEqual (true, row0["IsAliased"], "#19");
902 Assert.AreEqual (false, row1["IsAliased"], "#20");
904 Assert.AreEqual (false, row0["IsExpression"], "#21");
905 Assert.AreEqual (false, row0["IsIdentity"], "#22");
906 Assert.AreEqual (false, row0["IsAutoIncrement"], "#23");
907 Assert.AreEqual (false, row0["IsRowVersion"], "#24");
908 Assert.AreEqual (false, row0["IsHidden"], "#25");
909 Assert.AreEqual (false, row0["IsLong"], "#26");
910 Assert.AreEqual (false, row0["IsReadOnly"], "#27");
911 Assert.AreEqual (true, schemaTable.Rows[2]["IsReadOnly"], "#27");
913 // Test Exception is thrown when reader is closed
916 reader.GetSchemaTable ();
917 Assert.Fail ("#28 Exception shud be thrown" );
918 }catch (AssertionException e) {
920 }catch (Exception e) {
921 Assert.AreEqual (typeof(InvalidOperationException), e.GetType(),
922 "#29 Incorrect Exception");
927 public void GetDataTypeNameTest ()
929 cmd.CommandText = "Select id, type_tinyint, 10,null from numeric_family where id=1";
930 reader = cmd.ExecuteReader ();
932 Assert.AreEqual ("tinyint", reader.GetDataTypeName(1), "#1");
933 Assert.AreEqual ("int", reader.GetDataTypeName(2), "#2");
934 //need check on windows
935 Assert.AreEqual ("int", reader.GetDataTypeName(3), "#3");
937 reader.GetDataTypeName (10);
938 Assert.Fail ("#4 Exception shud be thrown");
939 }catch (AssertionException e) {
941 }catch (Exception e) {
942 Assert.AreEqual (typeof(IndexOutOfRangeException), e.GetType(),
943 "#5 Incorrect Exception : " + e);
948 public void GetFieldTypeTest ()
950 cmd.CommandText = "Select id , type_tinyint, 10 , null from numeric_family where id=1";
951 reader = cmd.ExecuteReader ();
953 Assert.AreEqual ("tinyint", reader.GetDataTypeName(1), "#1");
954 Assert.AreEqual ("int", reader.GetDataTypeName(2) , "#2");
955 Assert.AreEqual ("int", reader.GetDataTypeName(3), "#3");
957 reader.GetDataTypeName (10);
958 Assert.Fail ("#4 Exception shud be thrown");
959 }catch (AssertionException e) {
961 }catch (Exception e) {
962 Assert.AreEqual (typeof(IndexOutOfRangeException), e.GetType(),
963 "#5 Incorrect Exception : " + e);
967 // Need to populate the data from a config file
968 // Will be replaced later
969 void validateData(string sqlQuery, DataTable table)
971 string fmt = "#TAB[{0}] ROW[{1}] COL[{2}] Data Mismatch";
973 int noOfColumns = table.Columns.Count ;
976 cmd.CommandText = sqlQuery ;
977 reader = cmd.ExecuteReader ();
979 while (reader.Read ()){
980 for (int j=1; j< noOfColumns ; ++j)
981 Assert.AreEqual (table.Rows[i][j], reader[j],
982 String.Format (fmt, table.TableName, i+1, j));
990 public void NumericDataValidation ()
992 validateData ("select * from numeric_family order by id ASC",
997 public void StringDataValidation ()
999 validateData ("select * from string_family order by id ASC",
1004 public void BinaryDataValidation ()
1006 validateData ("select * from binary_family order by id ASC",
1011 public void DatetimeDataValidation ()
1013 validateData ("select * from datetime_family order by id ASC",