2 // Copyright (c) 2006 Mainsoft Co.
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:
12 // The above copyright notice and this permission notice shall be
13 // included in all copies or substantial portions of the Software.
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.
27 using System.Data.OracleClient ;
29 using MonoTests.System.Data.Utils;
32 using NUnit.Framework;
34 namespace MonoTests.System.Data.OracleClient
37 public class OracleDataReader_NextResult : ADONetTesterClass
39 OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
44 base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
49 public void TearDown() {
50 if (con.State == ConnectionState.Open) con.Close();
53 public static void Main()
55 OracleDataReader_NextResult tc = new OracleDataReader_NextResult();
59 tc.BeginTest("OracleDataReader_NextResult");
62 catch(Exception ex){exp = ex;}
63 finally {tc.EndTest(exp);}
70 base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
73 TestMultipleResultSetsWithSP();
74 TestMultipleResultSetsWithSQLText();
76 if (con.State == ConnectionState.Open) con.Close();
81 public void TestMultipleResultSetsWithSQLText()
84 if (ConnectedDataProvider.GetDbType() == DataBaseServer.Oracle)
86 this.Log("Multiple result sets by sql text is not tested in oracle.");
90 if (ConnectedDataProvider.GetDbType() == DataBaseServer.DB2)
92 this.Log("Multiple result sets using compound statement not supported at DB2.");
96 bool NextResultExists = false;
97 OracleDataReader rdr = null;
104 BeginCase("Setup: Get expected results.");
106 //get excpected results
107 GetExcpectedResults(ref TblResult0, ref TblResult1, ref TblResult2);
108 this.Pass("Setup: Get expected results ended.");
120 string cmdTxt = BuildCommandText();
121 cmd = new OracleCommand(cmdTxt, con);
122 cmd.CommandType = CommandType.Text;
123 rdr = cmd.ExecuteReader();
124 // -------------- ResultSet 1 ------------
128 BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 1 exists");
129 Compare(rdr != null, true);
142 BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 1 contains data");
143 NextResultExists = rdr.Read();
144 Compare(NextResultExists, true);
158 BeginCase("(Multiple Resultsets sql text) - Check ResultSet 1 Data");
163 Compare(i, TblResult0);
176 BeginCase("(Multiple Resultsets sql text) - Check ResultSet 1 Schema");
177 Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "CUSTOMERID");
187 // -------------- ResultSet 2 ------------
191 BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 2 exists");
192 NextResultExists = rdr.NextResult();
193 Compare(NextResultExists, true);
206 BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 2 contains data");
207 NextResultExists = rdr.Read();
208 Compare(NextResultExists, true);
221 BeginCase("(Multiple Resultsets sql text) - Check ResultSet 2 Data");
227 Compare(i, TblResult1);
240 BeginCase("(Multiple Resultsets sql text) - Check ResultSet 2 Schema");
241 Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "CATEGORYID");
247 // -------------- ResultSet 3 ------------
255 BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 3 exists");
256 NextResultExists = rdr.NextResult();
257 Compare(NextResultExists, true);
270 BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 3 contains data");
271 NextResultExists = rdr.Read();
272 Compare(NextResultExists, true);
285 BeginCase("(Multiple Resultsets sql text) - Check ResultSet 3 Data");
291 Compare(i, TblResult2);
304 BeginCase("(Multiple Resultsets sql text) - Check ResultSet 3 Schema");
305 Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "REGIONID");
318 BeginCase("(Multiple Resultsets sql text) - Check that resultset 4 does not exist.");
319 NextResultExists = rdr.NextResult();
320 Compare(NextResultExists, false);
333 BeginCase("(Multiple Resultsets sql text) - Check that resultset 4 does not contain data.");
334 NextResultExists = rdr.Read();
335 Compare(NextResultExists, false);
350 public void TestMultipleResultSetsWithSP()
353 if (ConnectedDataProvider.GetDbType() == DataBaseServer.Oracle)
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).");
359 if (ConnectedDataProvider.GetDbType(con) == DataBaseServer.PostgreSQL)
361 // fail to work on .NET OLEDB
362 this.Log("Not testing PostgreSQL CommandType.StoredProcedure which return SETOF");
367 bool NextResultExists = false;
368 // transaction use was add for PostgreSQL
369 OracleTransaction tr = con.BeginTransaction();
370 OracleCommand cmd = new OracleCommand("GH_MULTIRECORDSETS", con, tr);
371 cmd.Parameters.Add(new OracleParameter("RCT_Employees", OracleType.Cursor)).Direction = ParameterDirection.Output;
372 cmd.Parameters.Add(new OracleParameter("RCT_Customers", OracleType.Cursor)).Direction = ParameterDirection.Output;
373 cmd.Parameters.Add(new OracleParameter("RCT_Orders", OracleType.Cursor)).Direction = ParameterDirection.Output;
374 cmd.CommandType = CommandType.StoredProcedure;
375 OracleDataReader rdr = cmd.ExecuteReader();
380 BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 1 exists");
381 Compare(rdr != null, true);
394 BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 1 contains data");
395 NextResultExists = rdr.Read();
396 Compare(NextResultExists, true);
409 BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 1 Data");
410 Compare(rdr.GetValue(1).ToString(), "Yavine");
423 BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 1 Schema");
424 Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "EMPLOYEEID");
436 // -------------- ResultSet 2 ------------
440 BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 2 exists");
441 NextResultExists = rdr.NextResult();
442 Compare(NextResultExists, true);
455 BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 2 contains data");
456 NextResultExists = rdr.Read();
457 Compare(NextResultExists, true);
470 BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 2 Data");
471 Compare(rdr.GetValue(1).ToString(), "Morgenstern Gesundkost");
484 BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 2 Schema");
485 Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "CUSTOMERID");
496 // -------------- ResultSet 3 ------------
500 BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 3 exists");
501 NextResultExists = rdr.NextResult();
502 Compare(NextResultExists, true);
515 BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 3 contains data");
516 NextResultExists = rdr.Read();
517 Compare(NextResultExists, false);
530 BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 3 Schema");
531 Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "ORDERID");
544 BeginCase("(Multiple Resultsets stored proc.) - Check that resultset 4 does not exist.");
545 NextResultExists = rdr.NextResult();
546 Compare(NextResultExists, false);
559 BeginCase("(Multiple Resultsets stored proc.) - Check that resultset 4 does not contain data.");
560 NextResultExists = rdr.Read();
561 Compare(NextResultExists, false);
578 // transaction use was add for PostgreSQL
584 #region "Private Utilities"
585 private string BuildCommandText()
587 string beginStatement;
589 string commandDelimiter;
590 string[] commands = new string[] {"select * from Customers", "select * from Categories", "select * from Region"};
592 GetDBSpecificSyntax(ConnectedDataProvider.GetDbType(), out beginStatement, out endStatement, out commandDelimiter);
594 StringBuilder cmdBuilder = new StringBuilder();
595 cmdBuilder.Append(beginStatement);
596 cmdBuilder.Append(" ");
597 foreach (string statement in commands)
599 cmdBuilder.Append(statement);
600 cmdBuilder.Append(commandDelimiter);
601 cmdBuilder.Append(" ");
603 cmdBuilder.Append(endStatement);
605 return cmdBuilder.ToString();
607 private void GetDBSpecificSyntax(DataBaseServer dbServer, out string beginStatement, out string endStatement, out string commandDelimiter)
611 case DataBaseServer.SQLServer:
612 beginStatement = "BEGIN";
613 endStatement = "END";
614 commandDelimiter = ";";
616 case DataBaseServer.Sybase:
617 beginStatement = "BEGIN";
618 endStatement = "END";
619 commandDelimiter = "\r\n";
621 case DataBaseServer.Oracle:
622 beginStatement = "BEGIN";
623 endStatement = "END;";
624 commandDelimiter = ";";
627 case DataBaseServer.DB2:
632 commandDelimiter = ";";
635 case DataBaseServer.PostgreSQL:
638 commandDelimiter = ";";
642 this.Fail("Unknown DataBaseServer type");
643 throw new ApplicationException("Unknown DataBaseServer type");
646 private void GetExcpectedResults(ref int TblResult0, ref int TblResult1, ref int TblResult2)
648 // get excpected results
650 // transaction use was add for PostgreSQL
651 OracleTransaction tr = con.BeginTransaction();
652 OracleCommand cmd = new OracleCommand("", con,tr);
653 cmd.CommandText = "Select count(*) from Customers";
654 TblResult0 = Int32.Parse(cmd.ExecuteScalar().ToString());
655 cmd.CommandText = "Select count(*) from Categories";
656 TblResult1 = Int32.Parse(cmd.ExecuteScalar().ToString());
657 cmd.CommandText = "Select count(*) from Region";
658 TblResult2 = Int32.Parse(cmd.ExecuteScalar().ToString());