mcs/class/System.Data/Test/System.Data.Odbc/OdbcDataReaderTest.cs

   1 //\r
   2 // OdbcDataReaderTest.cs - NUnit Test Cases for testing the\r
   3 // OdbcDataReader class\r
   4 //\r
   5 // Author: \r
   6 //      Sureshkumar T (TSureshkumar@novell.com)\r
   7 // \r
   8 // Copyright (c) 2004 Novell Inc., and the individuals listed\r
   9 // on the ChangeLog entries.\r
  10 //\r
  11 // Permission is hereby granted, free of charge, to any person obtaining\r
  12 // a copy of this software and associated documentation files (the\r
  13 // "Software"), to deal in the Software without restriction, including\r
  14 // without limitation the rights to use, copy, modify, merge, publish,\r
  15 // distribute, sublicense, and/or sell copies of the Software, and to\r
  16 // permit persons to whom the Software is furnished to do so, subject to\r
  17 // the following conditions:\r
  18 //\r
  19 // The above copyright notice and this permission notice shall be\r
  20 // included in all copies or substantial portions of the Software.\r
  21 //\r
  22 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,\r
  23 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF\r
  24 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND\r
  25 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE\r
  26 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION\r
  27 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION\r
  28 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.\r
  29 //\r
  30 \r
  31 \r
  32 using System;\r
  33 using System.Text;\r
  34 using System.Data;\r
  35 using System.Data.Odbc;\r
  36 \r
  37 using NUnit.Framework;\r
  38 \r
  39 namespace MonoTests.System.Data.Odbc\r
  40 {\r
  41 \r
  42   [TestFixture]\r
  43   public class OdbcDataReaderTest : MySqlOdbcBaseClient \r
  44   {\r
  45           \r
  46           [SetUp]\r
  47           public void GetReady () {\r
  48                 OpenConnection ();\r
  49                 CreateTestSetup (); // create database & test tables\r
  50           }\r
  51 \r
  52           [TearDown]\r
  53           public void Clean () {\r
  54                 CleanTestSetup (); // clean test database;\r
  55                 CloseConnection ();\r
  56           }\r
  57 \r
  58         /// <summary>\r
  59         /// Tests the return value of GetByte method of OdbcDataReader\r
  60         /// </summary>\r
  61         [Test]\r
  62         public void GetByteTest () \r
  63         {\r
  64                 OdbcDataReader reader = null;\r
  65                 try {\r
  66                         // For this Test, you must create sample table\r
  67                         // called test, with a column of name 'col_int'.\r
  68                         // and the table with atleast a row with a minimum value for col_int as 0xff\r
  69                         // This tries to read a int column using GetByte method\r
  70                         OdbcCommand cmd = conn.CreateCommand ();\r
  71                         string query = "select col_int from test order by col_int;";\r
  72                         cmd.CommandText = query;\r
  73                         reader = cmd.ExecuteReader ();\r
  74                         if (reader.Read ()) {\r
  75                                 byte b = reader.GetByte (0); \r
  76                                 Assertion.AssertEquals ("GetByte returns wrong result!", 0xff, b);\r
  77                         } else // This should not happen while testing\r
  78                                 Assertion.AssertEquals ("test table doens not have a test data!", true, true);\r
  79                     } finally { // try/catch is necessary to gracefully close connections\r
  80                         if (reader != null && reader.IsClosed)\r
  81                                 reader.Close ();\r
  82                         CleanTestSetup ();\r
  83                         CloseConnection ();\r
  84                   }\r
  85         }\r
  86 \r
  87         /// <summary>\r
  88         /// Tests the return column type of data reader\r
  89         /// To test the bugzilla id 49340\r
  90         /// </summary>\r
  91         [Test]\r
  92         public void ColumnDataTypeTest () \r
  93         {\r
  94                 OdbcCommand dbcmd = conn.CreateCommand ();\r
  95                 string sql = "SELECT  * from test";\r
  96                 dbcmd.CommandText = sql;\r
  97                 IDataReader reader = dbcmd.ExecuteReader ();\r
  98                 try {\r
  99                         Assertion.AssertEquals ("GetDataTypeName returns invalid Type for column #1", \r
 100                                         "TinyInt", reader.GetDataTypeName (0));\r
 101                         Assertion.AssertEquals ("GetDataTypeName returns invalid Type for column #2", \r
 102                                         "VarChar", reader.GetDataTypeName (1));\r
 103                         // Test via method GetFieldType.ToString\r
 104                         Assertion.AssertEquals ("GetFieldType returns invalid Type for column #1", \r
 105                                         "System.Byte", reader.GetFieldType (0).ToString ());\r
 106                         Assertion.AssertEquals ("GetFieldType returns invalid Type for column #2", \r
 107                                         "System.String", reader.GetFieldType (1).ToString ());\r
 108 \r
 109                         // Test via method GetSchemaTable\r
 110                         reader = dbcmd.ExecuteReader ();\r
 111                         DataTable schemaTable = reader.GetSchemaTable ();\r
 112                         Assertion.AssertEquals ("GetSchemaTable.ColumnDataType failes for column #1", \r
 113                                         typeof (Byte), schemaTable.Rows [0]["DataType"]);\r
 114                         Assertion.AssertEquals ("GetSchemaTable.ColumnDataType failes for column #1", \r
 115                                         typeof (String), schemaTable.Rows [1]["DataType"]);\r
 116               } finally {\r
 117                         // clean up\r
 118                         if (reader != null && !reader.IsClosed)\r
 119                                 reader.Close ();\r
 120                         reader = null;\r
 121                         CleanTestSetup ();\r
 122                         CloseConnection ();\r
 123               }\r
 124         }\r
 125 \r
 126         [Test]\r
 127         public void GetNameTest () \r
 128         {\r
 129               OdbcCommand dbcmd = conn.CreateCommand ();\r
 130               string sql = "SELECT  * from test";\r
 131               dbcmd.CommandText = sql;\r
 132               OdbcDataReader reader = dbcmd.ExecuteReader ();\r
 133               try {\r
 134                       Assertion.AssertEquals ("GetName failes ", "pk_tint", reader.GetName (0));\r
 135               } finally {\r
 136                       // clean up\r
 137                       if (reader != null && !reader.IsClosed)\r
 138                               reader.Close ();\r
 139                       reader = null;\r
 140                       CleanTestSetup ();\r
 141                       CloseConnection ();\r
 142               }\r
 143       }\r
 144 \r
 145       [Test]\r
 146       public void GetBytesTest ()\r
 147       {\r
 148                 OdbcCommand cmd = conn.CreateCommand ();\r
 149                 string sql = "SELECT * FROM test";\r
 150                 cmd.CommandText = sql;\r
 151                 OdbcDataReader reader = cmd.ExecuteReader (CommandBehavior.SequentialAccess);\r
 152                 try {\r
 153                         if (reader.Read ()) {\r
 154                                 // Get By Parts for the column blob\r
 155                                 int totalsize = 100;\r
 156                                 int buffsize = 5;\r
 157                                 int buffstart = 0;\r
 158                                 long retval = 0;\r
 159                                 long start = 0;\r
 160                                 byte [] val = new byte [totalsize];\r
 161                                 retval = reader.GetBytes (3, start, val, buffstart, buffsize);\r
 162                                 while (retval == buffsize) {\r
 163                                         start += buffsize;\r
 164                                         buffstart += buffsize;\r
 165                                         retval = reader.GetBytes (3, start, val, buffstart, buffsize);\r
 166                                 }\r
 167                                 buffstart += (int) retval;\r
 168 \r
 169                                 // assemble here.\r
 170                                 string col = Encoding.Default.GetString (val, 0, buffstart);\r
 171                                 \r
 172                                 Assertion.AssertEquals ("The assembled value length does not match", \r
 173                                               39, col.Length);\r
 174                         }\r
 175                 } finally {\r
 176                         // clean up\r
 177                         if (reader != null && !reader.IsClosed)\r
 178                                 reader.Close ();\r
 179                         reader = null;\r
 180                         CleanTestSetup ();\r
 181                         CloseConnection ();\r
 182                 }\r
 183       }\r
 184 \r
 185       [Test]\r
 186       public void GetBytesNullBufferTest ()\r
 187       {\r
 188                 OdbcCommand cmd = conn.CreateCommand ();\r
 189                 string sql = "SELECT * FROM test";\r
 190                 cmd.CommandText = sql;\r
 191                 OdbcDataReader reader = cmd.ExecuteReader (CommandBehavior.SequentialAccess);\r
 192                 try {\r
 193                         if (reader.Read ()) {\r
 194                                 Assertion.AssertEquals ("GetBytes on a fixed length column does not work!", \r
 195                                                   11, reader.GetBytes (1,0,null,0,0));\r
 196                                 Assertion.AssertEquals ("GetBytes with non null column does not work!", \r
 197                                                   39, reader.GetBytes (3,0,null,0,0));\r
 198                         }\r
 199                         // for null value, length in bytes should return 0\r
 200                         if (reader.Read ()) \r
 201                                 Assertion.AssertEquals ("GetBytes with null column does not return -1" ,\r
 202                                                   -1, reader.GetBytes (3,0,null,0,0));\r
 203                 } finally {\r
 204                         // clean up\r
 205                         if (reader != null && !reader.IsClosed )\r
 206                                 reader.Close ();\r
 207                         reader = null;\r
 208                         CleanTestSetup ();\r
 209                         CloseConnection ();\r
 210                 }\r
 211       }\r
 212 \r
 213       [Test]\r
 214       public void GetValueBinaryTest ()\r
 215       {\r
 216                 OdbcCommand cmd = conn.CreateCommand ();\r
 217                 string sql = "SELECT * FROM test";\r
 218                 cmd.CommandText = sql;\r
 219                 OdbcDataReader reader = cmd.ExecuteReader (CommandBehavior.SequentialAccess);\r
 220                 try {\r
 221                         if (reader.Read ()) {\r
 222                                 object ob = reader.GetValue (3);\r
 223                                 Assertion.AssertEquals ("Type of binary column is wrong!", \r
 224                                                 "System.Byte[]", ob.GetType ().ToString () );\r
 225                         }\r
 226                 } finally {\r
 227                         // clean up\r
 228                         if (reader != null && !reader.IsClosed )\r
 229                                 reader.Close ();\r
 230                         reader = null;\r
 231                         CleanTestSetup ();\r
 232                         CloseConnection ();\r
 233                 }\r
 234       }\r
 235 \r
 236       [Test]\r
 237       public void GetDateTimeTest ()\r
 238       {\r
 239                 OdbcCommand cmd = conn.CreateCommand ();\r
 240                 string sql = "SELECT * FROM test";\r
 241                 cmd.CommandText = sql;\r
 242                 OdbcDataReader reader = cmd.ExecuteReader (CommandBehavior.Default);\r
 243                 try {\r
 244                         if (reader.Read ()) {\r
 245                                 object ob = reader["col_datetime"];\r
 246                                 Assertion.AssertEquals ("Type of datetime column is wrong!", \r
 247                                                 "System.DateTime", ob.GetType ().ToString () );\r
 248                                 ob = reader["col_date"];\r
 249                                 Assertion.AssertEquals ("Type of date column is wrong!", \r
 250                                                 "System.DateTime", ob.GetType ().ToString () );\r
 251                                 // FIXME : Once TIME data type is fixed, enable this check\r
 252                                 //ob = reader["col_time"];\r
 253                                 //Assertion.AssertEquals ("Type of time column is wrong!", \r
 254                                                 //"System.DateTime", ob.GetType ().ToString () );\r
 255 \r
 256                                 DateTime dt = reader.GetDateTime (4);\r
 257                                 Assertion.AssertEquals ("DateValue (SQL_TIMESTAMP) is wrong", new DateTime (2004, 8, 22, 0, 0, 0), dt);\r
 258                                 dt = reader.GetDateTime (5);\r
 259                                 Assertion.AssertEquals ("DateValue (SQL_DATE) is wrong", new DateTime (2004, 8, 22, 0, 0, 0), dt);\r
 260                                 // FIXME : Once TIME data type is fixed, enable this check\r
 261                                 //dt = reader.GetDateTime (7);\r
 262                                 //Assertion.AssertEquals ("DateValue is wrong", "2004-08-22", dt.ToString ());\r
 263                         }\r
 264                 } finally {\r
 265                         // clean up\r
 266                         if (reader != null && !reader.IsClosed )\r
 267                                 reader.Close ();\r
 268                         reader = null;\r
 269                         CleanTestSetup ();\r
 270                         CloseConnection ();\r
 271                 }\r
 272       }\r
 273 \r
 274 \r
 275                 \r
 276       [Test]\r
 277       public void NumericTest()\r
 278      {\r
 279                 using(IDbConnection dbConnection = new OdbcConnection\r
 280                 (connectionString))\r
 281                 {\r
 282                         dbConnection.Open();\r
 283                         IDbCommand dbCommand = dbConnection.CreateCommand();\r
 284                         //note this will fail if the table already exists, ie if the test has failed.   \r
 285                         dbCommand.CommandText = "CREATE TABLE NumericTable (NumericField NUMERIC(10) NOT NULL)";\r
 286                         dbCommand.ExecuteNonQuery();\r
 287                         dbCommand.CommandText = "INSERT INTO NumericTable (NumericField) VALUES (125)";\r
 288                         dbCommand.ExecuteNonQuery();\r
 289                         dbCommand.CommandText = "SELECT * FROM NumericTable";\r
 290                         using(IDataReader reader = dbCommand.ExecuteReader())\r
 291                         {\r
 292                                 while(reader.Read()) \r
 293                                 {\r
 294                                         for(int index = 0; index < reader.FieldCount; index++)\r
 295                                         {\r
 296                                                 Object dataValue = reader.GetValue(index);\r
 297                                                 Assert.AreEqual("System.Decimal",dataValue.GetType().ToString());\r
 298                                                 Assert.AreEqual("125", dataValue.ToString());   \r
 299                                 } \r
 300                         }\r
 301                 }\r
 302 \r
 303                 dbCommand.CommandText = "DROP TABLE NumericTable";\r
 304                 dbCommand.ExecuteNonQuery();\r
 305            }    \r
 306      }\r
 307 \r
 308 \r
 309       /// <summary>\r
 310       /// This test for the return type &amp; value for GetValue\r
 311       /// in case of Odbc Data type TINYINT\r
 312       /// </summary>\r
 313       [Test]\r
 314       public void TinyIntTest ()\r
 315       {\r
 316                 OdbcCommand cmd = conn.CreateCommand ();\r
 317                 string sql = "SELECT * FROM test";\r
 318                 cmd.CommandText = sql;\r
 319                 OdbcDataReader reader = cmd.ExecuteReader (CommandBehavior.SequentialAccess);\r
 320                 try {\r
 321                         if (reader.Read ()) {\r
 322                                 object ob = reader.GetValue (0);\r
 323                                 Assertion.AssertEquals ("Type of tinyInt column is wrong!", \r
 324                                                 "System.Byte", ob.GetType ().ToString () );\r
 325                                 Assertion.AssertEquals ("Value of tinyInt column is wrong!", \r
 326                                                 1, Convert.ToInt16(ob) );\r
 327                         }\r
 328                 } finally {\r
 329                         // clean up\r
 330                         if (reader != null && !reader.IsClosed )\r
 331                                 reader.Close ();\r
 332                         reader = null;\r
 333                         CleanTestSetup ();\r
 334                         CloseConnection ();\r
 335                 }\r
 336       }\r
 337 \r
 338 \r
 339   }\r
 340 }\r