mcs/class/System.Data/Test/DataProviderTests/datareadertests/DataReaderBaseTest.cs

   1 //
   2 // DataProviderBaseTest.cs : A base class that provides the common
   3 //                           functionality of :
   4 //                             1) Reading a config file containing the
   5 //                                database connection parameters, different
   6 //                                tables and their description, Values that
   7 //                                the tables are populated with.
   8 //                             2) Retrieves data from these tables;
   9 //                             3) Compares the retrieved values against the ones
  10 //                                contained in the config file.
  11 //
  12 // A class specific to each database (and ODBC) are derived from this class.
  13 // These classes contain code specific to different databases (like establishing
  14 // a connection, comparing date values, etc).
  15 //
  16 // Author:
  17 //   Satya Sudha K (ksathyasudha@novell.com)
  18 //
  19 //
  20 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
  21 //
  22 // Permission is hereby granted, free of charge, to any person obtaining
  23 // a copy of this software and associated documentation files (the
  24 // "Software"), to deal in the Software without restriction, including
  25 // without limitation the rights to use, copy, modify, merge, publish,
  26 // distribute, sublicense, and/or sell copies of the Software, and to
  27 // permit persons to whom the Software is furnished to do so, subject to
  28 // the following conditions:
  29 //
  30 // The above copyright notice and this permission notice shall be
  31 // included in all copies or substantial portions of the Software.
  32 //
  33 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  34 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  35 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  36 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  37 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  38 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  39 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  40 //
  41
  42 using System;
  43 using System.Collections;
  44 using System.IO;
  45 using System.Xml;
  46 using System.Xml.XPath;
  47 using System.Data;
  48 using System.Configuration;
  49 using System.Text.RegularExpressions;
  50
  51 namespace MonoTests.System.Data {
  52
  53         public class BaseRetrieve {
  54
  55                 public IDbConnection con;
  56                 public IDbCommand    cmd;
  57                 public IDataReader   rdr;
  58                 protected XmlNode    configDoc;
  59
  60                 public BaseRetrieve (string database)
  61                 {
  62                         con = null;
  63                         cmd = null;
  64                         rdr = null;
  65                         configDoc = (XmlNode) ConfigurationSettings.GetConfig (database);
  66                 }
  67
  68                 void CreateCommand ()
  69                 {
  70                         if (con == null)
  71                                 return;
  72                         cmd = con.CreateCommand ();
  73                 }
  74
  75                 // Method that actually runs the entire test : Connects to a database,
  76                 // retrieves values from different tables, and compares them against
  77                 // the values that we had entered
  78                 public void RunTest ()
  79                 {
  80
  81                         GetConnection ();
  82                         if (con == null)
  83                                 return;
  84
  85                         CreateCommand ();
  86                         if (cmd == null)
  87                                 return;
  88
  89                         string noOfTables = null;
  90                         string tableName = null;
  91                         int [] columnNos = null;
  92
  93                         try {
  94                                 noOfTables = ConfigClass.GetElement (configDoc, "tables", "numTables");
  95                                 short numTables = Convert.ToInt16 (noOfTables);
  96                                 string noOfQueries = ConfigClass.GetElement (configDoc, "queries", "numQueries");
  97                                 Console.WriteLine ("**** Running Queries ****");
  98
  99                                 if (noOfQueries != null) {
 100
 101                                         int numQueries = Convert.ToInt32 (noOfQueries);
 102
 103                                         for (int index = 1; index <= numQueries; index++) {
 104                                                 string queryStr = ConfigClass.GetElement (configDoc, "queries", "query" + index);
 105                                                 int tableNum = 0;
 106                                                 rdr = RunQuery (queryStr, ref columnNos, ref tableNum);
 107                                                 if (rdr == null)
 108                                                         continue;
 109
 110                                                 CompareData (rdr, configDoc, columnNos, tableNum);
 111                                                 rdr.Close ();
 112                                         }
 113                                 }
 114
 115                                 string storedProc = null;
 116                                 try {
 117                                         storedProc = ConfigClass.GetElement (configDoc, "StoredProcExists");
 118                                 } catch (Exception e) {
 119                                         return;
 120                                 }
 121
 122                                 if (storedProc.Equals ("Y")) {
 123
 124                                         Console.WriteLine ("\n**** Running tests for stored procedures *****\n");
 125                                         int numStoredProc = Convert.ToInt32 (ConfigClass.GetElement(configDoc,
 126                                                                  "StoredProc", "NumStoredProc"));
 127                                         for (int index = 1; index <= numStoredProc; index++) {
 128
 129                                                 string storedProcTag = "StoredProc" + index;
 130                                                 string type = ConfigClass.GetElement (configDoc, "StoredProc",
 131                                                                  storedProcTag, "type");
 132                                                 string nameTemplate = ConfigClass.GetElement (configDoc, "StoredProc",
 133                                                                         storedProcTag, "name");
 134                                                 if (type.Equals("generic")) {
 135
 136                                                         // There is stored proc correspoding to each table
 137                                                        // Run all such stored proc
 138                                                         for (short i = 1; i <= numTables; i++) {
 139
 140                                                                 try {
 141                                                                         tableName = ConfigClass.GetElement (configDoc, "tables",
 142                                                                                 "table"+i, "name");
 143                                                                 } catch (XPathException e) {
 144                                                                         Console.WriteLine (e.Message);
 145                                                                         continue; // need not return here; try with the next one
 146                                                                 }
 147
 148                                                                 string storedProcName = nameTemplate.Replace ("{{TABLE}}", tableName);
 149                                                                 rdr = QueryUsingStoredProc (cmd, storedProcName, null);
 150                                                                 if (rdr == null)
 151                                                                         continue;
 152
 153                                                                 CompareData (rdr, configDoc, null, i);
 154                                                                 rdr.Close ();
 155                                                         }
 156                                                 }
 157                                         }
 158                                 }
 159
 160                         } catch (Exception e) {
 161                                 Console.WriteLine ("ERROR : " + e.Message);
 162                                 Console.WriteLine ("STACKTRACE : " + e.StackTrace);
 163                         } finally {
 164                                 con.Close ();
 165                                 con = null;
 166                         }
 167
 168                 }
 169
 170                 public virtual IDataReader QueryUsingStoredProc (IDbCommand cmd,
 171                                                                  string storedProcName,
 172                                                                  string paramName)
 173                 {
 174                         cmd.CommandType = CommandType.StoredProcedure;
 175                         cmd.CommandText = storedProcName;
 176                         IDataReader rdr = null;
 177
 178                         try {
 179                                 rdr = cmd.ExecuteReader ();
 180                         } catch (Exception e) {
 181                                 Console.WriteLine ("Could not execute command : " + cmd.CommandText);
 182                                 Console.WriteLine ("ERROR : " + e.Message);
 183                                 Console.WriteLine ("STACKTRACE : " + e.StackTrace);
 184                                 return null;
 185                         }
 186
 187                         return rdr;
 188                 }
 189
 190                 IDataReader RunQuery (string queryStr, ref int [] columnNos, ref int tableNum)
 191                 {
 192                         string regexp = "\\b(Select|select) (?<columnList>(COLUMNS|((COLUMN\\d+,)*(COLUMN\\d+)))) from (?<tableName>TABLE\\d+)( order by (?<OrderBy>COLUMN\\d+))*";
 193                         Match m = Regex.Match (queryStr, regexp, RegexOptions.ExplicitCapture);
 194                         if (!m.Success) {
 195                                 Console.WriteLine ("Incorrect query format!!!");
 196                                 return null;
 197                         }
 198
 199                         columnNos = null;
 200
 201                         while (m.Success) {
 202
 203                                 string tableTag = m.Result ("${tableName}");
 204                                 tableNum = Convert.ToInt32 (tableTag.Replace ("TABLE", ""));
 205                                 string tableName = ConfigClass.GetElement (configDoc, "tables", tableTag.ToLower (), "name");
 206                                 queryStr = queryStr.Replace (tableTag, tableName);
 207
 208                                 for (int i = 0; i<m.Groups.Count; i++) {
 209
 210                                         Group g = m.Groups [i];
 211                                         CaptureCollection cc = g.Captures;
 212
 213                                         for (int j = 0; j < cc.Count; j++) {
 214
 215                                                 string matchedVal = cc [j].Value;
 216
 217                                                 if (matchedVal.Equals ("COLUMNS")) {
 218                                                         string [] columnNames = ConfigClass.GetColumnNames (configDoc, tableNum);
 219                                                         queryStr = queryStr.Replace ("COLUMNS", String.Join (",", columnNames));
 220                                                         columnNos = new int [columnNames.Length];
 221                                                         for (int index = 1; index <= columnNos.Length; index++)
 222                                                                 columnNos [index - 1] = index;
 223
 224                                                 } else if (matchedVal.StartsWith ("COLUMN")) {
 225                                                         // May be a column name or a comma
 226                                                         // separated list of columns
 227                                                         string [] listOfColumns = matchedVal.Split (',');
 228                                                         if (columnNos == null) {
 229
 230                                                                 columnNos = new int [listOfColumns.Length];
 231                                                                 int colIndex = 0;
 232                                                                 foreach (string str in listOfColumns) {
 233                                                                         int columnNo = Convert.ToInt32 (str.Replace("COLUMN", ""));
 234                                                                         columnNos [colIndex ++] = columnNo;
 235                                                                 }
 236                                                         }
 237
 238                                                         foreach (string str in listOfColumns) {
 239
 240                                                                 string columnName = ConfigClass.GetElement (configDoc, "tables",
 241                                                                                         tableTag.ToLower (), str.ToLower (), "name");
 242                                                                 queryStr = queryStr.Replace (str, columnName);
 243                                                         }
 244                                                 }
 245                                         }
 246                                 }
 247
 248                                 m = m.NextMatch ();
 249                         }
 250
 251                         IDataReader rdr = null;
 252                         cmd.CommandText = queryStr;
 253                         try {
 254                                 rdr = cmd.ExecuteReader ();
 255                         } catch (Exception e) {
 256                                 Console.WriteLine ("ERROR : " + e.Message);
 257                                 Console.WriteLine ("\nSTACKTRACE : " + e.StackTrace);
 258                                 return null;
 259                         }
 260
 261                         return rdr;
 262
 263                 }
 264
 265                 void CompareData (IDataReader rdr,
 266                                   XmlNode doc,
 267                                   int [] columnNos,
 268                                   int numTable)
 269                 {
 270                         int rowNum = 0;
 271                         string errorMsg = "";
 272                         string tableName = null;
 273                         try {
 274                                 tableName = ConfigClass.GetElement (doc, "tables", "table"+numTable, "name");
 275                         } catch (Exception e) {
 276                                 Console.WriteLine ("ERROR : " + e.Message );
 277                                 Console.WriteLine ("STACKTRACE : " + e.StackTrace );
 278                                 return;
 279                         }
 280
 281                         while (rdr.Read()) {
 282                                 rowNum ++;
 283                                 string columnValue = null;
 284                                 for (int i = 0; i < rdr.FieldCount; i++) {
 285                                         errorMsg = "";
 286                                         int columnNum = 0;
 287                                         try {
 288                                                 if (columnNos == null)
 289                                                         columnNum = i+1;
 290                                                 else
 291                                                         columnNum = columnNos [i];
 292
 293                                                 columnValue = ConfigClass.GetElement (doc, "values", "table" + numTable,
 294                                                                  "row" + rowNum, "column" + columnNum);
 295                                         } catch (Exception e) {
 296                                                 Console.WriteLine ("ERROR : " + e.Message);
 297                                                 Console.WriteLine ("STACKTRACE : " + e.StackTrace);
 298                                         }
 299
 300                                         object obj = null;
 301                                         Console.Write ("Table : {0} : ROW: {1} COL: {2}", tableName, rowNum, columnNum);
 302                                         try {
 303                                                 obj = GetValue (rdr, i);
 304                                         } catch (Exception e) {
 305
 306                                                 Console.WriteLine ("...FAIL");
 307                                                 errorMsg = "ERROR : " + e.Message;
 308                                                 errorMsg += "\nSTACKTRACE : " + e.StackTrace;
 309                                                 errorMsg += "\nProbably the 'GetFieldType()' method returned a wrong type!!";
 310                                                 Console.WriteLine (errorMsg);
 311                                                 obj = null;
 312                                                 continue;
 313                                         }
 314
 315                                         if (AreEqual (obj, columnValue, ref errorMsg)) {
 316                                                 Console.WriteLine ("...OK");
 317                                         } else {
 318                                                 Console.WriteLine ("...FAIL");
 319                                                 if (!errorMsg.Equals ("")) {
 320                                                         // There was some exception
 321                                                         Console.WriteLine (errorMsg);
 322                                                 } else {
 323                                                         // Comparison failed
 324                                                         Console.WriteLine ("Expected : "+columnValue+" Got : "+obj);
 325                                                 }
 326                                         }
 327                                 }
 328                                 Console.WriteLine ("======================");
 329                         }
 330                 }
 331
 332                 public virtual object GetValue (IDataReader rdr, int columnIndex)
 333                 {
 334
 335                         object value = null;
 336
 337                         if (rdr.IsDBNull (columnIndex)) {
 338                                 return null;
 339                         }
 340
 341                         Type type = rdr.GetFieldType (columnIndex);
 342
 343                         switch (type.Name.ToLower ()) {
 344
 345                         case "byte"    : value = rdr.GetByte (columnIndex);
 346                                         break;
 347                         case "sbyte"   : value = rdr.GetInt16 (columnIndex);
 348                                         break;
 349                         case "boolean" : value = rdr.GetBoolean (columnIndex);
 350                                         break;
 351                         case "int16"   : value = rdr.GetInt16 (columnIndex);
 352                                         break;
 353                         case "uint16"  :
 354                         case "int32"   : value = rdr.GetInt32 (columnIndex);
 355                                         break;
 356                         case "uint32"  :
 357                         case "int64"   : value = rdr.GetInt64 (columnIndex);
 358                                         break;
 359                         case "single"  : value = rdr.GetFloat (columnIndex);
 360                                         break;
 361                         case "double"  : value = rdr.GetDouble (columnIndex);
 362                                         break;
 363                         case "uint64"  :
 364                         case "decimal" : value = rdr.GetDecimal (columnIndex);
 365                                         break;
 366                         case "datetime": value = rdr.GetDateTime (columnIndex);
 367                                         break;
 368                         case "string": value = rdr.GetString (columnIndex);
 369                                         break;
 370                         default :      value = rdr.GetValue (columnIndex);
 371                                         break;
 372                         }
 373
 374                         return value;
 375
 376                 }
 377
 378                 public virtual Boolean AreEqual (object obj, string value, ref string errorMsg)
 379                 {
 380
 381                         if ((obj == null) || (value.Equals("null"))) {
 382                                 if (obj == null && value.Equals ("null"))
 383                                         return true;
 384                                 return false;
 385                         }
 386
 387                         object valObj = ConvertToValueType (obj.GetType (), value, ref errorMsg);
 388                         if (valObj == null) {
 389                                 errorMsg = "Could not convert values!!\n" + errorMsg;
 390                                 return false;
 391                         }
 392                         return valObj.Equals (obj);
 393                 }
 394
 395                 public virtual object ConvertToValueType (Type objType, string value, ref string errorMsg)
 396                 {
 397
 398                         value = value.Trim ('\'');
 399                         value = value.Trim ('\"');
 400
 401                         switch (Type.GetTypeCode (objType)) {
 402
 403                         case TypeCode.Int16 :
 404                                 return ConvertToInt16 (objType, value, ref errorMsg);
 405                         case TypeCode.Int32 :
 406                                 return ConvertToInt32 (objType, value, ref errorMsg);
 407                         case TypeCode.Int64 :
 408                                 return ConvertToInt64 (objType, value, ref errorMsg);
 409                         case TypeCode.Boolean :
 410                                 return ConvertToBoolean (objType, value, ref errorMsg);
 411                         case TypeCode.Byte :
 412                                 return ConvertToByte (objType, value, ref errorMsg);
 413                         case TypeCode.DateTime :
 414                                 return ConvertToDateTime (objType, value, ref errorMsg);
 415                         case TypeCode.Decimal :
 416                                 return ConvertToDecimal (objType, value, ref errorMsg);
 417                         case TypeCode.Double :
 418                                 return ConvertToDouble (objType, value, ref errorMsg);
 419                         case TypeCode.Single :
 420                                 return ConvertToSingle (objType, value, ref errorMsg);
 421
 422                         }
 423
 424                         if ( objType.ToString () == "System.TimeSpan")
 425                                 return ConvertToTimespan (objType, value, ref errorMsg);
 426
 427                         return ConvertValue (objType, value, ref errorMsg);
 428                 }
 429
 430                 public virtual object ConvertValue (Type type, string value, ref string errorMsg)
 431                 {
 432                         object valObj = null;
 433
 434                         try {
 435                                 valObj = Convert.ChangeType (value, type);
 436                         } catch (InvalidCastException e) {
 437                                 errorMsg = "Cant compare values!! \n";
 438                                 errorMsg += "ERROR : " + e.Message;
 439                                 errorMsg += "\nSTACKTRACE : " + e.StackTrace;
 440                                 return false;
 441                         } catch (Exception e) {
 442                                 errorMsg = "ERROR : " + e.Message;
 443                                 errorMsg += "\nSTACKTRACE : " + e.StackTrace;
 444                                 return null;
 445                         }
 446
 447                         return valObj;
 448
 449                 }
 450
 451                 public virtual object ConvertToInt16 (Type type, string value, ref string errorMsg)
 452                 {
 453                         return ConvertValue (type, value, ref errorMsg);
 454                 }
 455
 456                 public virtual object ConvertToInt32 (Type type, string value, ref string errorMsg)
 457                 {
 458                         return ConvertValue (type, value, ref errorMsg);
 459                 }
 460
 461                 public virtual object ConvertToInt64 (Type type, string value, ref string errorMsg)
 462                 {
 463                         return ConvertValue (type, value, ref errorMsg);
 464                 }
 465
 466                 public virtual object ConvertToBoolean (Type type, string value, ref string errorMsg)
 467                 {
 468                         return ConvertValue (type, value, ref errorMsg);
 469                 }
 470
 471                 public virtual object ConvertToByte (Type type, string value, ref string errorMsg)
 472                 {
 473                         return ConvertValue (type, value, ref errorMsg);
 474                 }
 475
 476                 public virtual object ConvertToDateTime (Type type, string value, ref string errorMsg)
 477                 {
 478                         return ConvertValue (type, value, ref errorMsg);
 479                 }
 480
 481                 public virtual object ConvertToDecimal (Type type, string value, ref string errorMsg)
 482                 {
 483                         return ConvertValue (type, value, ref errorMsg);
 484                 }
 485
 486                 public virtual object ConvertToDouble (Type type, string value, ref string errorMsg)
 487                 {
 488                         return ConvertValue (type, value, ref errorMsg);
 489                 }
 490
 491                 public virtual object ConvertToSingle (Type type, string value, ref string errorMsg)
 492                 {
 493                         return ConvertValue (type, value, ref errorMsg);
 494                 }
 495
 496                 public virtual object ConvertToTimespan (Type type, string value, ref string errorMsg)
 497                 {
 498                         return ConvertValue (type, value, ref errorMsg);
 499                 }
 500
 501                 public virtual void GetConnection()
 502                 {
 503                 }
 504         }
 505 }