mcs/class/System.Data/Test/ProviderTests/System.Data.OleDb.jvm/OleDbDataReader/OleDbDataReader_NextResult.cs

   1 //
   2 // Copyright (c) 2006 Mainsoft Co.
   3 //
   4 // Permission is hereby granted, free of charge, to any person obtaining
   5 // a copy of this software and associated documentation files (the
   6 // "Software"), to deal in the Software without restriction, including
   7 // without limitation the rights to use, copy, modify, merge, publish,
   8 // distribute, sublicense, and/or sell copies of the Software, and to
   9 // permit persons to whom the Software is furnished to do so, subject to
  10 // the following conditions:
  11 //
  12 // The above copyright notice and this permission notice shall be
  13 // included in all copies or substantial portions of the Software.
  14 //
  15 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  16 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  17 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  18 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  19 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  20 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  21 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  22 //
  23
  24 using System;
  25 using System.Text;
  26 using System.Data;
  27 using System.Data.OleDb ;
  28
  29 using MonoTests.System.Data.Utils;
  30
  31
  32 using NUnit.Framework;
  33
  34 namespace MonoTests.System.Data.OleDb
  35 {
  36         [TestFixture]
  37         public class OleDbDataReader_NextResult : ADONetTesterClass
  38         {
  39                 OleDbConnection con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
  40                 Exception exp = null;
  41
  42                 [SetUp]
  43                 public void SetUp() {
  44                         base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
  45                         con.Open();
  46                 }
  47
  48                 [TearDown]
  49                 public void TearDown() {
  50                         if (con.State == ConnectionState.Open) con.Close();
  51                 }
  52
  53                 public static void Main()
  54                 {
  55                         OleDbDataReader_NextResult tc = new OleDbDataReader_NextResult();
  56                         Exception exp = null;
  57                         try
  58                         {
  59                                 tc.BeginTest("OleDbDataReader_NextResult");
  60                                 tc.run();
  61                         }
  62                         catch(Exception ex){exp = ex;}
  63                         finally {tc.EndTest(exp);}
  64                 }
  65
  66                 public void run()
  67                 {
  68
  69
  70                         base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
  71                         con.Open();
  72
  73                         TestMultipleResultSetsWithSP();
  74                         TestMultipleResultSetsWithSQLText();
  75
  76                         if (con.State == ConnectionState.Open) con.Close();
  77
  78                 }
  79
  80                 [Test]
  81                 public void TestMultipleResultSetsWithSQLText()
  82                 {
  83
  84                         if (ConnectedDataProvider.GetDbType() == DataBaseServer.Oracle)
  85                         {
  86                                 this.Log("Multiple result sets by sql text is not tested in oracle.");
  87                                 return;
  88                         }
  89
  90                         if (ConnectedDataProvider.GetDbType() == DataBaseServer.DB2)
  91                         {
  92                                 this.Log("Multiple result sets using compound statement not supported at DB2.");
  93                                 return;
  94                         }
  95
  96                         bool NextResultExists = false;
  97                         OleDbDataReader rdr = null;
  98                         OleDbCommand cmd;
  99                         int TblResult0=-1;
 100                         int TblResult1=-1;
 101                         int TblResult2=-1;
 102                         try
 103                         {
 104                                 BeginCase("Setup: Get expected results.");
 105
 106                                 //get excpected results
 107                                 GetExcpectedResults(ref TblResult0, ref TblResult1, ref TblResult2);
 108                                 this.Pass("Setup: Get expected results ended.");
 109                         }
 110                         catch (Exception ex)
 111                         {
 112                                 exp = ex;
 113                         }
 114                         finally
 115                         {
 116                                 EndCase(exp);
 117                         }
 118
 119
 120                         string cmdTxt = BuildCommandText();
 121                         cmd = new OleDbCommand(cmdTxt, con);
 122                         cmd.CommandType = CommandType.Text;
 123                         rdr = cmd.ExecuteReader();
 124                         // -------------- ResultSet  1 ------------
 125                         try
 126                         {
 127                                 exp = null;
 128                                 BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 1 exists");
 129                                 Compare(rdr != null, true);
 130                         }
 131                         catch(Exception ex)
 132                         {
 133                                 exp = ex;
 134                         }
 135                         finally
 136                         {
 137                                 EndCase(exp);
 138                         }
 139                         try
 140                         {
 141                                 exp = null;
 142                                 BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 1 contains data");
 143                                 NextResultExists = rdr.Read();
 144                                 Compare(NextResultExists, true);
 145                         }
 146                         catch(Exception ex)
 147                         {
 148                                 exp = ex;
 149                         }
 150                         finally
 151                         {
 152                                 EndCase(exp);
 153                         }
 154                         int i = 1;
 155                         try
 156                         {
 157                                 exp = null;
 158                                 BeginCase("(Multiple Resultsets sql text) - Check ResultSet 1 Data");
 159                                 while (rdr.Read())
 160                                 {
 161                                         i++;
 162                                 }
 163                                 Compare(i, TblResult0);
 164                         }
 165                         catch(Exception ex)
 166                         {
 167                                 exp = ex;
 168                         }
 169                         finally
 170                         {
 171                                 EndCase(exp);
 172                         }
 173                         try
 174                         {
 175                                 exp = null;
 176                                 BeginCase("(Multiple Resultsets sql text) - Check ResultSet 1 Schema");
 177                                 Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "CUSTOMERID");
 178                         }
 179                         catch(Exception ex)
 180                         {
 181                                 exp = ex;
 182                         }
 183                         finally
 184                         {
 185                                 EndCase(exp);
 186                         }
 187                         // -------------- ResultSet  2 ------------
 188                         try
 189                         {
 190                                 exp = null;
 191                                 BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 2 exists");
 192                                 NextResultExists = rdr.NextResult();
 193                                 Compare(NextResultExists, true);
 194                         }
 195                         catch(Exception ex)
 196                         {
 197                                 exp = ex;
 198                         }
 199                         finally
 200                         {
 201                                 EndCase(exp);
 202                         }
 203                         try
 204                         {
 205                                 exp = null;
 206                                 BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 2 contains data");
 207                                 NextResultExists = rdr.Read();
 208                                 Compare(NextResultExists, true);
 209                         }
 210                         catch(Exception ex)
 211                         {
 212                                 exp = ex;
 213                         }
 214                         finally
 215                         {
 216                                 EndCase(exp);
 217                         }
 218                         try
 219                         {
 220                                 exp = null;
 221                                 BeginCase("(Multiple Resultsets sql text) - Check ResultSet 2 Data");
 222                                 i = 1;
 223                                 while (rdr.Read())
 224                                 {
 225                                         i++;
 226                                 }
 227                                 Compare(i, TblResult1);
 228                         }
 229                         catch(Exception ex)
 230                         {
 231                                 exp = ex;
 232                         }
 233                         finally
 234                         {
 235                                 EndCase(exp);
 236                         }
 237                         try
 238                         {
 239                                 exp = null;
 240                                 BeginCase("(Multiple Resultsets sql text) - Check ResultSet 2 Schema");
 241                                 Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "CATEGORYID");
 242                         }
 243                         catch(Exception ex)
 244                         {
 245                                 exp = ex;
 246                         }
 247                         // -------------- ResultSet  3 ------------
 248                         finally
 249                         {
 250                                 EndCase(exp);
 251                         }
 252                         try
 253                         {
 254                                 exp = null;
 255                                 BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 3 exists");
 256                                 NextResultExists = rdr.NextResult();
 257                                 Compare(NextResultExists, true);
 258                         }
 259                         catch(Exception ex)
 260                         {
 261                                 exp = ex;
 262                         }
 263                         finally
 264                         {
 265                                 EndCase(exp);
 266                         }
 267                         try
 268                         {
 269                                 exp = null;
 270                                 BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 3 contains data");
 271                                 NextResultExists = rdr.Read();
 272                                 Compare(NextResultExists, true);
 273                         }
 274                         catch(Exception ex)
 275                         {
 276                                 exp = ex;
 277                         }
 278                         finally
 279                         {
 280                                 EndCase(exp);
 281                         }
 282                         try
 283                         {
 284                                 exp = null;
 285                                 BeginCase("(Multiple Resultsets sql text) - Check ResultSet 3 Data");
 286                                 i = 1;
 287                                 while (rdr.Read())
 288                                 {
 289                                         i++;
 290                                 }
 291                                 Compare(i, TblResult2);
 292                         }
 293                         catch(Exception ex)
 294                         {
 295                                 exp = ex;
 296                         }
 297                         finally
 298                         {
 299                                 EndCase(exp);
 300                         }
 301                         try
 302                         {
 303                                 exp = null;
 304                                 BeginCase("(Multiple Resultsets sql text) - Check ResultSet 3 Schema");
 305                                 Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "REGIONID");
 306                         }
 307                         catch(Exception ex)
 308                         {
 309                                 exp = ex;
 310                         }
 311                         finally
 312                         {
 313                                 EndCase(exp);
 314                         }
 315                         try
 316                         {
 317                                 exp = null;
 318                                 BeginCase("(Multiple Resultsets sql text) - Check that resultset 4 does not exist.");
 319                                 NextResultExists = rdr.NextResult();
 320                                 Compare(NextResultExists, false);
 321                         }
 322                         catch(Exception ex)
 323                         {
 324                                 exp = ex;
 325                         }
 326                         finally
 327                         {
 328                                 EndCase(exp);
 329                         }
 330                         try
 331                         {
 332                                 exp = null;
 333                                 BeginCase("(Multiple Resultsets sql text) - Check that resultset 4 does not contain data.");
 334                                 NextResultExists = rdr.Read();
 335                                 Compare(NextResultExists, false);
 336                         }
 337                         catch(Exception ex)
 338                         {
 339                                 exp = ex;
 340                         }
 341                         finally
 342                         {
 343                                 EndCase(exp);
 344                         }
 345                         if (!rdr.IsClosed)
 346                                 rdr.Close();
 347                 }
 348
 349                 [Test]
 350                 public void TestMultipleResultSetsWithSP()
 351                 {
 352 #if !JAVA
 353                         if (ConnectedDataProvider.GetDbType() == DataBaseServer.Oracle)
 354                         {
 355                                 this.Log("Not testing Stored procedures with multiple ref-cursors on Oracle with .NET due to bug in .NET (only the first ref-cursor is retrived).");
 356                                 return;
 357                         }
 358
 359                         if (ConnectedDataProvider.GetDbType(con) == DataBaseServer.PostgreSQL)
 360                         {
 361                                 // fail to work on .NET OLEDB
 362                                 this.Log("Not testing PostgreSQL CommandType.StoredProcedure which return SETOF");
 363                                 return;
 364                         }
 365 #endif
 366
 367                         bool NextResultExists = false;
 368                         // transaction use was add for PostgreSQL
 369                         OleDbTransaction tr = con.BeginTransaction();
 370                         OleDbCommand cmd = new OleDbCommand("GH_MULTIRECORDSETS", con, tr);
 371                         cmd.CommandType = CommandType.StoredProcedure;
 372                         OleDbDataReader rdr = cmd.ExecuteReader();
 373
 374                         try
 375                         {
 376                                 exp = null;
 377                                 BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 1 exists");
 378                                 Compare(rdr != null, true);
 379                         }
 380                         catch(Exception ex)
 381                         {
 382                                 exp = ex;
 383                         }
 384                         finally
 385                         {
 386                                 EndCase(exp);
 387                         }
 388                         try
 389                         {
 390                                 exp = null;
 391                                 BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 1 contains data");
 392                                 NextResultExists = rdr.Read();
 393                                 Compare(NextResultExists, true);
 394                         }
 395                         catch(Exception ex)
 396                         {
 397                                 exp = ex;
 398                         }
 399                         finally
 400                         {
 401                                 EndCase(exp);
 402                         }
 403                         try
 404                         {
 405                                 exp = null;
 406                                 BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 1 Data");
 407                                 Compare(rdr.GetValue(1).ToString(), "Yavine");
 408                         }
 409                         catch(Exception ex)
 410                         {
 411                                 exp = ex;
 412                         }
 413                         finally
 414                         {
 415                                 EndCase(exp);
 416                         }
 417                         try
 418                         {
 419                                 exp = null;
 420                                 BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 1 Schema");
 421                                 Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "EMPLOYEEID");
 422                         }
 423                         catch(Exception ex)
 424                         {
 425                                 exp = ex;
 426                         }
 427                         finally
 428                         {
 429                                 EndCase(exp);
 430                         }
 431
 432
 433                         // -------------- ResultSet  2 ------------
 434                         try
 435                         {
 436                                 exp = null;
 437                                 BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 2 exists");
 438                                 NextResultExists = rdr.NextResult();
 439                                 Compare(NextResultExists, true);
 440                         }
 441                         catch(Exception ex)
 442                         {
 443                                 exp = ex;
 444                         }
 445                         finally
 446                         {
 447                                 EndCase(exp);
 448                         }
 449                         try
 450                         {
 451                                 exp = null;
 452                                 BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 2 contains data");
 453                                 NextResultExists = rdr.Read();
 454                                 Compare(NextResultExists, true);
 455                         }
 456                         catch(Exception ex)
 457                         {
 458                                 exp = ex;
 459                         }
 460                         finally
 461                         {
 462                                 EndCase(exp);
 463                         }
 464                         try
 465                         {
 466                                 exp = null;
 467                                 BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 2 Data");
 468                                 Compare(rdr.GetValue(1).ToString(), "Morgenstern Gesundkost");
 469                         }
 470                         catch(Exception ex)
 471                         {
 472                                 exp = ex;
 473                         }
 474                         finally
 475                         {
 476                                 EndCase(exp);
 477                         }
 478                         try
 479                         {
 480                                 exp = null;
 481                                 BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 2 Schema");
 482                                 Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "CUSTOMERID");
 483                         }
 484                         catch(Exception ex)
 485                         {
 486                                 exp = ex;
 487                         }
 488                         finally
 489                         {
 490                                 EndCase(exp);
 491                         }
 492
 493                         // -------------- ResultSet  3 ------------
 494                         try
 495                         {
 496                                 exp = null;
 497                                 BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 3 exists");
 498                                 NextResultExists = rdr.NextResult();
 499                                 Compare(NextResultExists, true);
 500                         }
 501                         catch(Exception ex)
 502                         {
 503                                 exp = ex;
 504                         }
 505                         finally
 506                         {
 507                                 EndCase(exp);
 508                         }
 509                         try
 510                         {
 511                                 exp = null;
 512                                 BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 3 contains data");
 513                                 NextResultExists = rdr.Read();
 514                                 Compare(NextResultExists, false);
 515                         }
 516                         catch(Exception ex)
 517                         {
 518                                 exp = ex;
 519                         }
 520                         finally
 521                         {
 522                                 EndCase(exp);
 523                         }
 524                         try
 525                         {
 526                                 exp = null;
 527                                 BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 3 Schema");
 528                                 Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "ORDERID");
 529                         }
 530                         catch(Exception ex)
 531                         {
 532                                 exp = ex;
 533                         }
 534                         finally
 535                         {
 536                                 EndCase(exp);
 537                         }
 538                         try
 539                         {
 540                                 exp = null;
 541                                 BeginCase("(Multiple Resultsets stored proc.) - Check that resultset 4 does not exist.");
 542                                 NextResultExists = rdr.NextResult();
 543                                 Compare(NextResultExists, false);
 544                         }
 545                         catch(Exception ex)
 546                         {
 547                                 exp = ex;
 548                         }
 549                         finally
 550                         {
 551                                 EndCase(exp);
 552                         }
 553                         try
 554                         {
 555                                 exp = null;
 556                                 BeginCase("(Multiple Resultsets stored proc.) - Check that resultset 4 does not contain data.");
 557                                 NextResultExists = rdr.Read();
 558                                 Compare(NextResultExists, false);
 559                         }
 560                         catch(Exception ex)
 561                         {
 562                                 exp = ex;
 563                         }
 564                         finally
 565                         {
 566                                 EndCase(exp);
 567                         }
 568
 569                         //Cleanup:
 570                         if (!rdr.IsClosed)
 571                         {
 572                                 rdr.Close();
 573                         }
 574
 575                         // transaction use was add for PostgreSQL
 576                         tr.Commit();
 577
 578                 }
 579
 580
 581                 #region "Private Utilities"
 582                 private string BuildCommandText()
 583                 {
 584                         string beginStatement;
 585                         string endStatement;
 586                         string commandDelimiter;
 587                         string[] commands = new string[] {"select * from Customers", "select * from Categories", "select * from Region"};
 588
 589                         GetDBSpecificSyntax(ConnectedDataProvider.GetDbType(), out beginStatement, out endStatement, out commandDelimiter);
 590
 591                         StringBuilder cmdBuilder = new StringBuilder();
 592                         cmdBuilder.Append(beginStatement);
 593                         cmdBuilder.Append(" ");
 594                         foreach (string statement in commands)
 595                         {
 596                                 cmdBuilder.Append(statement);
 597                                 cmdBuilder.Append(commandDelimiter);
 598                                 cmdBuilder.Append(" ");
 599                         }
 600                         cmdBuilder.Append(endStatement);
 601
 602                         return cmdBuilder.ToString();
 603                 }
 604                 private void GetDBSpecificSyntax(DataBaseServer dbServer, out string beginStatement, out string endStatement, out string commandDelimiter)
 605                 {
 606                         switch (dbServer)
 607                         {
 608                                 case DataBaseServer.SQLServer:
 609                                         beginStatement = "BEGIN";
 610                                         endStatement = "END";
 611                                         commandDelimiter = ";";
 612                                         break;
 613                                 case DataBaseServer.Sybase:
 614                                         beginStatement = "BEGIN";
 615                                         endStatement = "END";
 616                                         commandDelimiter = "\r\n";
 617                                         break;
 618                                 case DataBaseServer.Oracle:
 619                                         beginStatement = "BEGIN";
 620                                         endStatement = "END;";
 621                                         commandDelimiter = ";";
 622                                         break;
 623
 624                                 case DataBaseServer.DB2:
 625                                         {
 626                                                 beginStatement = "";
 627                                                 endStatement = "";
 628                                         }
 629                                         commandDelimiter = ";";
 630                                         break;
 631
 632                                 case DataBaseServer.PostgreSQL:
 633                                         beginStatement = "";
 634                                         endStatement = "";
 635                                         commandDelimiter = ";";
 636                                         break;
 637
 638                                 default:
 639                                         this.Fail("Unknown DataBaseServer type");
 640                                         throw new ApplicationException("Unknown DataBaseServer type");
 641                         }
 642                 }
 643                 private void GetExcpectedResults(ref int TblResult0, ref int TblResult1, ref int TblResult2)
 644                 {
 645                         // get excpected results
 646
 647                         // transaction use was add for PostgreSQL
 648                         OleDbTransaction tr = con.BeginTransaction();
 649                         OleDbCommand cmd = new OleDbCommand("", con,tr);
 650                         cmd.CommandText = "Select count(*) from Customers";
 651                         TblResult0 = Int32.Parse(cmd.ExecuteScalar().ToString());
 652                         cmd.CommandText = "Select count(*) from Categories";
 653                         TblResult1 = Int32.Parse(cmd.ExecuteScalar().ToString());
 654                         cmd.CommandText = "Select count(*) from Region";
 655                         TblResult2 = Int32.Parse(cmd.ExecuteScalar().ToString());
 656                         tr.Commit();
 657                 }
 658
 659                 #endregion
 660         }
 661 }