mcs/class/System.Data/Test/ProviderTests/System.Data.OleDb.jvm/OleDbCommand/OleDbCommand_CommandType.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.Data;
  26 using System.Data.OleDb ;
  27
  28 using MonoTests.System.Data.Utils;
  29
  30
  31 using NUnit.Framework;
  32 #if DAAB
  33 using Microsoft.ApplicationBlocks.Data;
  34 #endif
  35
  36 namespace MonoTests.System.Data.OleDb
  37 {
  38         [TestFixture]
  39         public class OleDbCommand_CommandType : ADONetTesterClass
  40         {
  41                 OleDbConnection con;
  42                 // transaction is must on PostgreSQL
  43                 OleDbTransaction tr;
  44                 OleDbCommand cmd;
  45                 OleDbDataReader dr = null;
  46                 DataBaseServer dbServerType;
  47
  48                 [SetUp]
  49                 public void SetUp()
  50                 {
  51                         Exception exp = null;
  52                         BeginCase("Setup");
  53                         try
  54                         {
  55                                 con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
  56                                 con.Open();
  57                                 tr = con.BeginTransaction();
  58                                 cmd = new OleDbCommand("", con, tr);
  59                                 dbServerType = ConnectedDataProvider.GetDbType(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
  60                                 Assert.AreEqual("Setup", "Setup");
  61                         }
  62                         catch(Exception ex)     {exp = ex;}
  63                         finally {EndCase(exp); exp = null;}
  64                 }
  65                 [TearDown]
  66                 public void TearDown()
  67                 {
  68                         if (con != null)
  69                         {
  70                                 if (con.State == ConnectionState.Open) con.Close();
  71                         }
  72                 }
  73
  74                 public static void Main()
  75                 {
  76                         OleDbCommand_CommandType tc = new OleDbCommand_CommandType();
  77                         Exception exp = null;
  78                         try
  79                         {
  80                                 tc.BeginTest("OleDbCommand_CommandType");
  81                                 tc.SetUp();
  82                                 tc.run();
  83                                 tc.TearDown();
  84                         }
  85                         catch(Exception ex){exp = ex;}
  86                         finally {tc.EndTest(exp);}
  87                 }
  88
  89                 [Test]
  90                 public void run()
  91                 {
  92                         Exception exp = null;
  93
  94                         OleDbCommand cmd = new OleDbCommand();
  95                         try
  96                         {
  97                                 BeginCase("CommandType - default");
  98                                 Assert.AreEqual(cmd.CommandType , CommandType.Text );
  99                         }
 100                         catch(Exception ex){exp = ex;}
 101                         finally{EndCase(exp); exp = null;}
 102
 103                         try
 104                         {
 105                                 BeginCase("CommandType - TableDirect");
 106                                 cmd.CommandType = CommandType.TableDirect;
 107                                 Assert.AreEqual(cmd.CommandType , CommandType.TableDirect);
 108                         }
 109                         catch(Exception ex){exp = ex;}
 110                         finally{EndCase(exp); exp = null;}
 111
 112                         try
 113                         {
 114                                 BeginCase("CommandType - Text");
 115                                 cmd.CommandType = CommandType.Text  ;
 116                                 Assert.AreEqual(cmd.CommandType , CommandType.Text);
 117                         }
 118                         catch(Exception ex){exp = ex;}
 119                         finally{EndCase(exp); exp = null;}
 120
 121
 122
 123                         #region         ---- CommandType.Text using Parameters.Add ----
 124                         try
 125                         {
 126                                 BeginCase("CommandType.Text using Parameters.Add");
 127
 128                                 cmd = new OleDbCommand();
 129                                 cmd.Connection = con;
 130                                 cmd.Transaction = tr;
 131                                 cmd.CommandType = CommandType.Text;
 132                                 switch (dbServerType)
 133                                 {
 134                                         case DataBaseServer.PostgreSQL:
 135                                                 cmd.CommandText = "SELECT * FROM GH_REFCURSOR3(?)";
 136                                                 break;
 137                                         default:
 138                                                 cmd.CommandText = "{call GH_REFCURSOR3(?)}";
 139                                                 break;
 140                                 }
 141
 142                                 OleDbParameter param1 = cmd.Parameters.Add("IN_LASTNAME", OleDbType.VarChar,20);
 143                                 param1.Direction = ParameterDirection.Input;
 144                                 param1.Value = "Yavine";
 145 #if DAAB
 146 #if !JAVA
 147                                 if ((dbServerType == DataBaseServer.PostgreSQL))
 148                                 {
 149                                         dr = PostgresOleDbHelper.OLEDB4ODBCExecuteReader(cmd,true);
 150                                 }
 151                                 else
 152 #endif
 153 #endif
 154                                 {
 155                                         dr = cmd.ExecuteReader();
 156                                 }
 157
 158                                 if (dr.HasRows)
 159                                 {
 160                                         dr.Read();
 161                                         Assert.AreEqual(dr.GetValue(0).ToString(),"1");
 162                                         Assert.AreEqual(dr.GetString(1),"Yavine");
 163                                 }
 164                                 else
 165                                         Assert.AreEqual("error","HasRows=0");
 166
 167                         }
 168                         catch(Exception ex)
 169                         {
 170                                 exp = ex;
 171                         }
 172                         finally
 173                         {
 174                                 if (dr != null)dr.Close();
 175                                 if (con != null)
 176                                 {if (con.State == ConnectionState.Open) con.Close();}
 177
 178                                 EndCase(exp);
 179                                 exp = null;
 180                         }
 181                         #endregion
 182
 183                         CommandTypeSP_Manual_InOutParameters();
 184
 185                         #region         ---- ORACLE CommandType.StoredProcedure using DeriveParameters ----
 186                         if (ConnectedDataProvider.GetDbType(con) == MonoTests.System.Data.Utils.DataBaseServer.Oracle)
 187                         {
 188                                 try
 189                                 {
 190                                         BeginCase("ORACLE CommandType.StoredProcedure using DeriveParameters");
 191
 192                                         con.Open();
 193                                         cmd = new OleDbCommand();
 194                                         cmd.Connection = con;
 195                                         cmd.Transaction = tr;
 196                                         cmd.CommandType = CommandType.StoredProcedure;
 197                                         cmd.CommandText = "GH_REFCURSOR3";
 198
 199                                         OleDbCommandBuilder.DeriveParameters(cmd);
 200                                         cmd.Parameters[0].Value = "Yavine";
 201                                         cmd.Parameters.RemoveAt(1); // the ORACLE DAAB trick is to remove the out parameter
 202
 203                                         dr = cmd.ExecuteReader();
 204                                         if (dr.HasRows)
 205                                         {
 206                                                 dr.Read();
 207                                                 Assert.AreEqual(dr.GetValue(0).ToString(),"1");
 208                                                 Assert.AreEqual(dr.GetString(1),"Yavine");
 209                                         }
 210                                         else
 211                                                 Assert.AreEqual("error","HasRows=0");
 212
 213                                 }
 214                                 catch(Exception ex)
 215                                 {
 216                                         exp = ex;
 217                                 }
 218                                 finally
 219                                 {
 220                                         if (dr != null)dr.Close();
 221                                         if (con != null)
 222                                         {if (con.State == ConnectionState.Open) con.Close();}
 223
 224                                         EndCase(exp);
 225                                         exp = null;
 226                                 }
 227                         }
 228                         #endregion
 229
 230                         #region CommandType.StoredProcedure in order to repreduce bug 4003
 231                         if (ConnectedDataProvider.GetDbType(con) == MonoTests.System.Data.Utils.DataBaseServer.SQLServer)
 232                         {
 233                                 exp = null;
 234                                 try
 235                                 {
 236                                         if (con.State == ConnectionState.Closed) con.Open();
 237                                         BeginCase("Bug 4003");
 238                                         OleDbCommand cmd4003 = new OleDbCommand("[mainsoft].[GH_DUMMY]",con);
 239                                         cmd4003.CommandType = CommandType.StoredProcedure;
 240                                         cmd4003.Parameters.Add("@EmployeeIDPrm","1");
 241                                         cmd4003.ExecuteReader();
 242
 243                                 }
 244                                 catch (Exception ex)
 245                                 {
 246                                         exp=ex;
 247                                 }
 248                                 finally
 249                                 {
 250                                         if (con.State == ConnectionState.Open) con.Close();
 251                                         EndCase(exp);
 252                                 }
 253
 254                         }
 255
 256                         #endregion
 257                 }
 258
 259                 #region         ---- CommandType.StoredProcedure manual in out parameters ----
 260                 public void CommandTypeSP_Manual_InOutParameters()
 261                 {
 262                         Exception exp = null;
 263                         try
 264                         {
 265                                 BeginCase("CommandType.StoredProcedure manual in out parameters");
 266
 267                                 if (ConnectedDataProvider.GetDbType(con) == MonoTests.System.Data.Utils.DataBaseServer.PostgreSQL)
 268                                 {
 269                                         this.Log("CommandType.StoredProcedure manual in out parameters is not tested in oracle.");
 270                                         return;
 271                                 }
 272
 273                                 con.Open();
 274                                 cmd = new OleDbCommand();
 275                                 cmd.Connection = con;
 276
 277                                 cmd.CommandType = CommandType.StoredProcedure;
 278                                 cmd.CommandText = "GH_INOUT1";
 279
 280                                 //RETURN_VALUE for SQLServer
 281                                 if (ConnectedDataProvider.GetDbType(con) == MonoTests.System.Data.Utils.DataBaseServer.SQLServer)
 282                                 {
 283                                         OleDbParameter param0 = cmd.Parameters.Add("@RETURN_VALUE", OleDbType.Integer);
 284                                         param0.Direction = ParameterDirection.ReturnValue;
 285                                 }
 286
 287                                 OleDbParameter param1 = cmd.Parameters.Add("@INPARAM", OleDbType.VarChar,20);
 288                                 param1.Direction = ParameterDirection.Input;
 289                                 param1.Value = Convert.ToString("dummy");
 290
 291                                 OleDbParameter param2 = cmd.Parameters.Add("@OUTPARAM", OleDbType.Integer);//VarNumeric);
 292                                 param2.Direction = ParameterDirection.Output;
 293
 294                                 int ret = cmd.ExecuteNonQuery();
 295                                 int intReturn;
 296                                 intReturn = Convert.ToInt32(cmd.Parameters["@OUTPARAM"].Value);
 297                                 Assert.AreEqual(intReturn,100);
 298
 299                         }
 300                         catch(Exception ex)
 301                         {
 302                                 exp = ex;
 303                         }
 304                         finally
 305                         {
 306                                 if (dr != null)dr.Close();
 307                                 if (con != null)
 308                                 {if (con.State == ConnectionState.Open) con.Close();}
 309
 310                                 EndCase(exp);
 311                                 exp = null;
 312                         }
 313                 }
 314                 #endregion
 315         }
 316
 317 }