mcs/class/System.Data/Test/DataProviderTests/dataadaptertests/DataAdapterBaseTest.cs

   1 //
   2 // DataAdapterBaseTest.cs : Defines a base class 'BaseAdapter' 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 (Fills a dataset).
   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) is 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.Data.Common;
  49 using System.Configuration;
  50 using System.Text.RegularExpressions;
  51
  52 namespace MonoTests.System.Data {
  53
  54         public class BaseAdapter {
  55
  56                 public IDbConnection con;
  57                 public IDbCommand cmd;
  58                 public DbDataAdapter dataAdapter;
  59                 public DataSet dataset;
  60                 string [,] setOfChanges;
  61                 protected XmlNode configDoc;
  62
  63                 public BaseAdapter (string database)
  64                 {
  65                         con = null;
  66                         cmd = null;
  67                         dataAdapter = null;
  68                         dataset = null;
  69                         setOfChanges = null;
  70                         configDoc = (XmlNode) ConfigurationSettings.GetConfig (database);
  71                 }
  72
  73                 void CreateCommand ()
  74                 {
  75                         if (con == null)
  76                                 return;
  77                         cmd = con.CreateCommand ();
  78                 }
  79
  80                 // Method that actually runs the entire test : Connects to a database,
  81                 // retrieves values from different tables, and compares them against
  82                 // the values that we had entered
  83                 public void RunTest ()
  84                 {
  85
  86                         GetConnection ();
  87                         if (con == null)
  88                                 return;
  89
  90                         CreateCommand ();
  91                         if (cmd == null)
  92                                 return;
  93
  94                         string noOfQueries = null;
  95                         string errorMsg = "";
  96                         string query = null;
  97
  98                         try {
  99                                 noOfQueries = ConfigClass.GetElement (configDoc, "queries", "numQueries");
 100                                 int numQueries = Convert.ToInt32 (noOfQueries);
 101                                 string tableName = null;
 102                                 int [] columnNos = null;
 103                                 int tableNum = 0;
 104                                 Console.WriteLine ("\n**** Testing Data Retrieval using datasets*****\n");
 105
 106                                 for (int i = 1; i <= numQueries; i++) {
 107                                         errorMsg = "";
 108                                         try {
 109                                                 query = ConfigClass.GetElement (configDoc, "queries", "query" + i);
 110                                                 query = FrameQuery (query, ref columnNos, ref tableNum);
 111                                                 tableName = ConfigClass.GetElement (configDoc, "tables", "table" + tableNum, "name");
 112                                         } catch (XPathException e) {
 113                                                 Console.WriteLine (e.Message);
 114                                                 continue; // need not return here; try with the next one
 115                                         }
 116
 117                                         try {
 118                                                 PopulateDataSetFromTable (query, tableName);
 119                                         } catch (Exception e) {
 120                                                 Console.WriteLine ("Table : {0} : Unable to fill the dataset!!!", tableName);
 121                                                 Console.WriteLine ("ERROR : " + e.Message);
 122                                                 Console.WriteLine ("STACKTRACE : " + e.StackTrace);
 123                                                 continue;
 124                                         }
 125
 126                                         CompareData (tableNum, setOfChanges, columnNos);
 127                                 }
 128
 129                                 string [] columnNames = null;
 130                                 string noOfTables = ConfigClass.GetElement (configDoc, "tables", "numTables");
 131                                 int numTables = 0;
 132                                 if (noOfTables != null)
 133                                         numTables = Convert.ToInt32 (noOfTables);
 134
 135                                 for (int i = 1; i <= numTables; i++) {
 136
 137                                         setOfChanges = null;
 138                                         try {
 139                                                 tableName = ConfigClass.GetElement (configDoc, "tables", "table" + i, "name");
 140                                                 columnNames = ConfigClass.GetColumnNames (configDoc, i);
 141                                         } catch (XPathException e) {
 142                                                 Console.WriteLine (e.Message);
 143                                                 continue; // need not return here; try with the next one
 144                                         }
 145
 146                                         try {
 147                                                 query = "Select " + String.Join (",", columnNames) + " from " + tableName;
 148                                                 PopulateDataSetFromTable (query, tableName);
 149                                         } catch (Exception e) {
 150                                                 Console.WriteLine ("Table : {0} : Unable to fill the dataset after " +
 151                                                         "updating the database!!!", tableName);
 152                                                 Console.WriteLine ("ERROR : " + e.Message);
 153                                                 Console.WriteLine ("STACKTRACE : " + e.StackTrace);
 154                                                 continue;
 155                                         }
 156
 157                                         if (dataset == null) {
 158                                                 Console.WriteLine ("Unable to populate the dataset!!!");
 159                                                 continue;
 160                                         }
 161
 162                                         MakeChanges (i, ref errorMsg);
 163
 164                                         if (dataset.HasChanges() == false) {
 165                                                 Console.WriteLine ("\nTable : {0} : No Changes for this table in the config file",
 166                                                         tableName);
 167                                                 continue;
 168                                         } else {
 169                                                 if (ReconcileChanges (tableName, ref errorMsg) == false) {
 170                                                         Console.WriteLine ("Table : {0} : Unable to " +
 171                                                                 "update the database !!!", tableName);
 172                                                         Console.WriteLine (errorMsg);
 173                                                         continue;
 174                                                 } else {
 175                                                         Console.WriteLine ("\nTable : {0} : Updated " +
 176                                                                 "using datasets", tableName);
 177                                                 }
 178                                         }
 179
 180                                         Console.WriteLine ("\nTable : {0} : Refilling the dataset\n", tableName);
 181                                         // Clear the data in the dataset
 182                                         dataset.Clear ();
 183                                         //Fill again from the database
 184                                         dataAdapter.Fill (dataset, tableName);
 185                                         CompareData (i, setOfChanges, null);
 186                                 }
 187
 188                         } catch (Exception e) {
 189                                 Console.WriteLine ("ERROR : " + e.Message);
 190                                 Console.WriteLine ("STACKTRACE : " + e.StackTrace);
 191                         } finally {
 192                                 con.Close ();
 193                                 con = null;
 194                         }
 195
 196                 }
 197
 198                 public virtual IDataReader QueryUsingStoredProc (IDbCommand cmd,
 199                                                                  string storedProcName,
 200                                                                  string paramName)
 201                 {
 202
 203                         cmd.CommandType = CommandType.StoredProcedure;
 204                         cmd.CommandText = storedProcName;
 205                         IDataReader rdr = null;
 206                         try {
 207                                 rdr = cmd.ExecuteReader ();
 208                         } catch (Exception e) {
 209                                 Console.WriteLine ("Could not execute command : " + cmd.CommandText);
 210                                 Console.WriteLine ("ERROR : " + e.Message);
 211                                 Console.WriteLine ("STACKTRACE : " + e.StackTrace);
 212                                 return null;
 213                         }
 214
 215                         return rdr;
 216                 }
 217
 218                 protected string FrameQuery (string queryStr,
 219                                              ref int [] columnNos,
 220                                              ref int tableNum)
 221                 {
 222                         string regexp = "\\b(Select|select) (?<columnList>(COLUMNS|((COLUMN\\d+,)*(COLUMN\\d+)))) from (?<tableName>TABLE\\d+)( order by (?<OrderBy>COLUMN\\d+))*";
 223                         Match m = Regex.Match (queryStr, regexp, RegexOptions.ExplicitCapture);
 224
 225                         if (!m.Success) {
 226                                 Console.WriteLine ("Incorrect query format!!!");
 227                                 return null;
 228                         }
 229
 230                         columnNos = null;
 231                         while (m.Success) {
 232
 233                                 string tableTag = m.Result ("${tableName}");
 234                                 tableNum = Convert.ToInt32 (tableTag.Replace ("TABLE", ""));
 235                                 string tableName = ConfigClass.GetElement (configDoc, "tables", tableTag.ToLower (), "name");
 236                                 queryStr = queryStr.Replace (tableTag, tableName);
 237
 238                                 for (int i = 0; i < m.Groups.Count; i++) {
 239
 240                                         Group g = m.Groups [i];
 241                                         CaptureCollection cc = g.Captures;
 242
 243                                         for (int j = 0; j < cc.Count; j++) {
 244
 245                                                 string matchedVal = cc [j].Value;
 246
 247                                                 if (matchedVal.Equals ("COLUMNS")) {
 248                                                         string [] columnNames = ConfigClass.GetColumnNames (configDoc, tableNum);
 249                                                         queryStr = queryStr.Replace ("COLUMNS", String.Join (",", columnNames));
 250                                                         columnNos = new int [columnNames.Length];
 251                                                         for (int index = 1; index <= columnNos.Length; index++) {
 252                                                                 columnNos [index - 1] = index;
 253                                                         }
 254                                                 } else if (matchedVal.StartsWith ("COLUMN")) {
 255                                                         // May be a column name or a comma
 256                                                         // separated list of columns
 257                                                         string [] listOfColumns = matchedVal.Split (',');
 258
 259                                                         if (columnNos == null) {
 260
 261                                                                 columnNos = new int [listOfColumns.Length];
 262                                                                 int colIndex = 0;
 263                                                                 foreach (string str in listOfColumns) {
 264                                                                         int columnNo = Convert.ToInt32 (str.Replace ("COLUMN", ""));
 265                                                                         columnNos [colIndex++] = columnNo;
 266                                                                 }
 267                                                         }
 268
 269                                                         foreach (string str in listOfColumns) {
 270                                                                 string columnName = ConfigClass.GetElement (configDoc, "tables",
 271                                                                                 tableTag.ToLower (), str.ToLower (), "name");
 272                                                                 queryStr = queryStr.Replace (str, columnName);
 273                                                         }
 274                                                 }
 275                                         }
 276                                 }
 277
 278                                 m = m.NextMatch ();
 279                         }
 280
 281                         return queryStr;
 282                 }
 283
 284                 public virtual bool ReconcileChanges (string tableName, ref string errorMsg)
 285                 {
 286                         return false;
 287                 }
 288
 289                 public virtual void PopulateDataSetFromTable (string queryStr, string tableName)
 290                 {
 291                         return;
 292                 }
 293
 294                 public virtual void MakeChanges (int tableNum, ref string errorMsg)
 295                 {
 296                         string numchanges = null;
 297                         try {
 298                                 numchanges = ConfigClass.GetElement (configDoc, "values", "table" + tableNum,                                                   "changes", "numChanges");
 299                         } catch (Exception e) {
 300                                 return;
 301                         }
 302
 303                         int noChanges = Convert.ToInt32 (numchanges);
 304                         string tableName = ConfigClass.GetElement (configDoc, "values", "table" + tableNum, "tableName");
 305                         int numRows = Convert.ToInt32 (ConfigClass.GetElement (configDoc, "values", "table" + tableNum, "numRows"));
 306                         int numCols = Convert.ToInt32 (ConfigClass.GetElement (configDoc, "values", "table" + tableNum, "numCols"));
 307                         setOfChanges = new string [numRows,numCols];
 308
 309                         for (int x = 0; x < numRows; x++)
 310                                 for (int y = 0; y < numCols; y++)
 311                                         setOfChanges [x,y] = null;
 312
 313                         int dbTableNo = -1;
 314
 315                         foreach (DataTable dbTable in dataset.Tables) {
 316
 317                                 dbTableNo ++;
 318                                 if (tableName.Equals (dbTable.TableName))
 319                                         break;
 320                         }
 321
 322                         for (int index = 1; index <= noChanges; index++) {
 323
 324                                 string tagname = "change" + index;
 325                                 int row = Convert.ToInt32 (ConfigClass.GetElement (configDoc, "values",
 326                                         "table" + tableNum, "changes", tagname, "row"));
 327                                 int col = Convert.ToInt32 (ConfigClass.GetElement (configDoc, "values",
 328                                         "table" + tableNum, "changes", tagname, "col"));
 329                                 string value = ConfigClass.GetElement (configDoc, "values",
 330                                         "table" + tableNum, "changes", tagname, "value");
 331                                 setOfChanges [row - 1,col - 1] = value;
 332                                 DataRow drow = dataset.Tables [dbTableNo].Rows [row - 1];
 333                                 DataColumn dcol = dataset.Tables [dbTableNo].Columns [col - 1];
 334                                 object dataSetValue = drow [dcol];
 335                                 try {
 336                                         drow [dcol] = ConvertToType (dataSetValue.GetType (), value, ref errorMsg);
 337                                 } catch (Exception e) {
 338                                         drow [dcol] = DBNull.Value;
 339                                 }
 340                         }
 341                 }
 342
 343                 public virtual object ConvertValue (string value, Type type)
 344                 {
 345                         return Convert.ChangeType (value, type);
 346                 }
 347
 348                 void CompareData (int numTable, string [,] setOfChanges, int [] columnNos)
 349                 {
 350                         int row = 0;
 351                         string errorMsg = "";
 352                         string tableName = null;
 353                         try {
 354                                 tableName = ConfigClass.GetElement (configDoc, "tables", "table"+numTable, "name");
 355                         } catch (Exception e) {
 356                                 Console.WriteLine ("ERROR : " + e.Message );
 357                                 Console.WriteLine ("STACKTRACE : " + e.StackTrace );
 358                                 return;
 359                         }
 360
 361                         foreach (DataTable dbTable in dataset.Tables) {
 362                                 if (!tableName.Equals (dbTable.TableName))
 363                                         continue;
 364                         row = 0;
 365                         foreach (DataRow datarow in dbTable.Rows) {
 366                                 row ++;
 367                                 string columnValue = null;
 368                                 int column = 0;
 369                                 foreach (DataColumn datacolumn in dbTable.Columns) {
 370                                         column ++;
 371                                         errorMsg = "";
 372                                         int columnNo = column;
 373                                         if (columnNos != null) {
 374                                                 columnNo = columnNos [column - 1];
 375 }
 376                                                 if ((setOfChanges != null ) && (setOfChanges [row - 1, columnNo - 1] !=null)) {
 377                                                         columnValue = setOfChanges [row - 1, columnNo - 1];
 378                                                 } else {
 379                                                         try {
 380                                                                 columnValue = ConfigClass.GetElement (configDoc, "values",
 381                                                                         "table" + numTable, "row" + row, "column" + columnNo);
 382                                                         } catch (Exception e) {
 383                                                                 Console.WriteLine ("ERROR : " + e.Message);
 384                                                                 Console.WriteLine ("STACKTRACE : " + e.StackTrace);
 385                                                         }
 386                                                 }
 387
 388                                                 object obj = null;
 389                                                 Console.Write ("Table: {0} : ROW: {1}  COL: {2}", tableName, row , columnNo);
 390                                                 try {
 391                                                         obj = datarow [datacolumn];
 392                                                 } catch (Exception e) {
 393
 394                                                         Console.WriteLine ("...FAIL");
 395                                                         errorMsg = "ERROR : " + e.Message;
 396                                                         errorMsg += "\nSTACKTRACE : " + e.StackTrace;
 397                                                         errorMsg += "\nProbably the 'DataType' property returned a wrong type!!";
 398                                                         Console.WriteLine (errorMsg);
 399                                                         obj = null;
 400                                                         continue;
 401                                                 }
 402
 403                                                 if (AreEqual (obj, columnValue, ref errorMsg)) {
 404                                                         Console.WriteLine ("...OK");
 405                                                 } else {
 406                                                         Console.WriteLine ("...FAIL");
 407
 408                                                         if (!errorMsg.Equals ("")) {
 409                                                                 // There was some exception
 410                                                                 Console.WriteLine (errorMsg);
 411                                                         } else {
 412                                                                 // Comparison failed
 413                                                                 Console.WriteLine ("Expected : {0} Got: {1}", columnValue, obj);
 414                                                         }
 415                                                 }
 416                                         }
 417                                         Console.WriteLine ("======================");
 418                                 }
 419                         }
 420                 }
 421
 422                 public virtual object GetValue (IDataReader rdr, int columnIndex)
 423                 {
 424
 425                         object value = null;
 426
 427                         if (rdr.IsDBNull (columnIndex))
 428                                 return null;
 429
 430                         Type type = rdr.GetFieldType (columnIndex);
 431
 432                         switch (type.Name.ToLower ()) {
 433
 434                         case "byte"    : value = rdr.GetByte (columnIndex);
 435                                         break;
 436                         case "sbyte"   : value = rdr.GetInt16 (columnIndex);
 437                                         break;
 438                         case "boolean" : value = rdr.GetBoolean (columnIndex);
 439                                         break;
 440                         case "int16"   : value = rdr.GetInt16 (columnIndex);
 441                                         break;
 442                         case "uint16"  :
 443                         case "int32"   : value = rdr.GetInt32 (columnIndex);
 444                                         break;
 445                         case "uint32"  :
 446                         case "int64"   : value = rdr.GetInt64 (columnIndex);
 447                                         break;
 448                         case "single"  : value = rdr.GetFloat (columnIndex);
 449                                         break;
 450                         case "double"  : value = rdr.GetDouble (columnIndex);
 451                                         break;
 452                         case "uint64"  :
 453                         case "decimal" : value = rdr.GetDecimal (columnIndex);
 454                                         break;
 455                         case "datetime": value = rdr.GetDateTime (columnIndex);
 456                                         break;
 457                         case "string": value = rdr.GetString (columnIndex);
 458                                         break;
 459                         default :      value = rdr.GetValue (columnIndex);
 460                                         break;
 461                         }
 462
 463                         return value;
 464
 465                 }
 466
 467                 public virtual object ConvertToType (Type type, string value, ref string errorMsg)
 468                 {
 469                         if (value.Equals ("null"))
 470                                 return DBNull.Value;
 471
 472                         switch (Type.GetTypeCode (type)) {
 473                         case TypeCode.Int16 :
 474                                 return ConvertToInt16 (type, value, ref errorMsg);
 475                         case TypeCode.Int32 :
 476                                 return  ConvertToInt32 (type, value, ref errorMsg);
 477                         case TypeCode.Int64 :
 478                                 return ConvertToInt64 (type, value, ref errorMsg);
 479                         case TypeCode.String :
 480                                 return value;
 481                         case TypeCode.Boolean :
 482                                 return ConvertToBoolean (type, value, ref errorMsg);
 483                         case TypeCode.Byte :
 484                                 return ConvertToByte (type, value, ref errorMsg);
 485                         case TypeCode.DateTime :
 486                                 return ConvertToDateTime (type, value, ref errorMsg);
 487                         case TypeCode.Decimal :
 488                                 return ConvertToDecimal (type, value, ref errorMsg);
 489                         case TypeCode.Double :
 490                                 return ConvertToDouble (type, value, ref errorMsg);
 491                         case TypeCode.Single :
 492                                 return ConvertToSingle (type, value, ref errorMsg);
 493                         }
 494
 495                         if (type.ToString () == "System.TimeSpan")
 496                                 return ConvertToTimeSpan (type, value, ref errorMsg);
 497
 498                         return ConvertValue (type, value, ref errorMsg);
 499                 }
 500
 501                 public virtual Boolean AreEqual (object obj, string value, ref string errorMsg)
 502                 {
 503
 504                         if (obj.Equals (DBNull.Value)  || (value.Equals ("null"))) {
 505                                 if (obj.Equals (DBNull.Value) && value.Equals ("null"))
 506                                         return true;
 507                                 return false;
 508                         }
 509                         Type objType = obj.GetType ();
 510                         value = value.Trim ('\'');
 511                         value = value.Trim ('\"');
 512                         object valObj = ConvertToType (objType, value, ref errorMsg);
 513                         return valObj.Equals (obj);
 514                 }
 515
 516                 public virtual object ConvertValue (Type type, string value, ref string errorMsg)
 517                 {
 518
 519                         object valObj = null;
 520
 521                         try {
 522                                 valObj = Convert.ChangeType (value, type);
 523                         } catch (InvalidCastException e) {
 524                                 errorMsg = "Cant convert values!! \n";
 525                                 errorMsg += "ERROR : " + e.Message;
 526                                 errorMsg += "\nSTACKTRACE : " + e.StackTrace;
 527                                 return false;
 528                         } catch (Exception e) {
 529                                 errorMsg = "ERROR : " + e.Message;
 530                                 errorMsg += "\nSTACKTRACE : " + e.StackTrace;
 531                                 return false;
 532                         }
 533
 534                         return valObj;
 535
 536                 }
 537
 538                 public virtual object ConvertToInt16 (Type type, string value, ref string errorMsg)
 539                 {
 540                         return ConvertValue (type, value, ref errorMsg);
 541                 }
 542
 543                 public virtual object ConvertToInt32 (Type type, string value, ref string errorMsg)
 544                 {
 545                         return ConvertValue (type, value, ref errorMsg);
 546                 }
 547
 548                 public virtual object ConvertToInt64 (Type type, string value, ref string errorMsg)
 549                 {
 550                         return ConvertValue (type, value, ref errorMsg);
 551                 }
 552
 553                 public virtual object ConvertToBoolean (Type type, string value, ref string errorMsg)
 554                 {
 555                         return ConvertValue (type, value, ref errorMsg);
 556                 }
 557
 558                 public virtual object ConvertToByte (Type type, string value, ref string errorMsg)
 559                 {
 560                         return ConvertValue (type, value, ref errorMsg);
 561                 }
 562
 563                 public virtual object ConvertToDateTime (Type type, string value, ref string errorMsg)
 564                 {
 565                         return ConvertValue (type, value, ref errorMsg);
 566                 }
 567
 568                 public virtual object ConvertToDecimal (Type type, string value, ref string errorMsg)
 569                 {
 570                         return ConvertValue (type, value, ref errorMsg);
 571                 }
 572
 573                 public virtual object ConvertToDouble (Type type, string value, ref string errorMsg)
 574                 {
 575                         return ConvertValue (type, value, ref errorMsg);
 576                 }
 577
 578                 public virtual object ConvertToSingle (Type type, string value, ref string errorMsg)
 579                 {
 580                         return ConvertValue (type, value, ref errorMsg);
 581                 }
 582
 583                 public virtual object ConvertToTimeSpan (Type type, string value, ref string errorMsg)
 584                 {
 585                         return ConvertValue (type, value, ref errorMsg);
 586                 }
 587
 588                 public virtual void GetConnection ()
 589                 {
 590                 }
 591         }
 592 }