mcs/class/System.Data.OracleClient/System.Data.OracleClient/OracleCommand.cs

   1 //
   2 // OracleCommand.cs
   3 //
   4 // Part of the Mono class libraries at
   5 // mcs/class/System.Data.OracleClient/System.Data.OracleClient
   6 //
   7 // Assembly: System.Data.OracleClient.dll
   8 // Namespace: System.Data.OracleClient
   9 //
  10 // Authors:
  11 //    Daniel Morgan <danielmorgan@verizon.net>
  12 //    Tim Coleman <tim@timcoleman.com>
  13 //
  14 // Copyright (C) Daniel Morgan, 2002, 2004-2005
  15 // Copyright (C) Tim Coleman , 2003
  16 //
  17 // Licensed under the MIT/X11 License.
  18 //
  19
  20 using System;
  21 using System.ComponentModel;
  22 using System.Data;
  23 using System.Data.OracleClient.Oci;
  24 using System.Drawing.Design;
  25 using System.Text;
  26
  27 namespace System.Data.OracleClient {
  28         [Designer ("Microsoft.VSDesigner.Data.VS.OracleCommandDesigner, " + Consts.AssemblyMicrosoft_VSDesigner)]
  29         [ToolboxItem (true)]
  30         public sealed class OracleCommand : Component, ICloneable, IDbCommand
  31         {
  32                 #region Fields
  33
  34                 CommandBehavior behavior;
  35                 string commandText;
  36                 CommandType commandType;
  37                 OracleConnection connection;
  38                 bool designTimeVisible;
  39                 OracleParameterCollection parameters;
  40                 OracleTransaction transaction;
  41                 UpdateRowSource updatedRowSource;
  42
  43                 private OciStatementHandle preparedStatement;
  44                 OciStatementType statementType;
  45
  46                 #endregion // Fields
  47
  48                 #region Constructors
  49
  50                 public OracleCommand ()
  51                         : this (String.Empty, null, null)
  52                 {
  53                 }
  54
  55                 public OracleCommand (string commandText)
  56                         : this (commandText, null, null)
  57                 {
  58                 }
  59
  60                 public OracleCommand (string commandText, OracleConnection connection)
  61                         : this (commandText, connection, null)
  62                 {
  63                 }
  64
  65                 public OracleCommand (string commandText, OracleConnection connection, OracleTransaction tx)
  66                 {
  67                         preparedStatement = null;
  68                         CommandText = commandText;
  69                         Connection = connection;
  70                         Transaction = tx;
  71                         CommandType = CommandType.Text;
  72                         UpdatedRowSource = UpdateRowSource.Both;
  73                         DesignTimeVisible = false;
  74
  75                         parameters = new OracleParameterCollection (this);
  76                 }
  77
  78                 #endregion // Constructors
  79
  80                 #region Properties
  81
  82                 [DefaultValue ("")]
  83                 [RefreshProperties (RefreshProperties.All)]
  84                 [Editor ("Microsoft.VSDesigner.Data.Oracle.Design.OracleCommandTextEditor, " + Consts.AssemblyMicrosoft_VSDesigner, typeof(UITypeEditor))]
  85                 public string CommandText {
  86                         get { return commandText; }
  87                         set { commandText = value; }
  88                 }
  89
  90                 [RefreshProperties (RefreshProperties.All)]
  91                 [DefaultValue (CommandType.Text)]
  92                 public CommandType CommandType {
  93                         get { return commandType; }
  94                         set {
  95                                 if (value == CommandType.TableDirect)
  96                                         throw new ArgumentException ("OracleClient provider does not support TableDirect CommandType.");
  97                                 commandType = value;
  98                         }
  99                 }
 100
 101                 [DefaultValue (null)]
 102                 [Editor ("Microsoft.VSDesigner.Data.Design.DbConnectionEditor, " + Consts.AssemblyMicrosoft_VSDesigner, typeof(UITypeEditor))]
 103                 public OracleConnection Connection {
 104                         get { return connection; }
 105                         set { connection = value; }
 106                 }
 107
 108                 [DefaultValue (true)]
 109                 [Browsable (false)]
 110                 [DesignOnly (true)]
 111                 public bool DesignTimeVisible {
 112                         get { return designTimeVisible; }
 113                         set { designTimeVisible = value; }
 114                 }
 115
 116                 internal OciEnvironmentHandle Environment {
 117                         get { return Connection.Environment; }
 118                 }
 119
 120                 internal OciErrorHandle ErrorHandle {
 121                         get { return Connection.ErrorHandle; }
 122                 }
 123
 124                 int IDbCommand.CommandTimeout {
 125                         get { return 0; }
 126                         set { }
 127                 }
 128
 129                 [Editor ("Microsoft.VSDesigner.Data.Design.DbConnectionEditor, " + Consts.AssemblyMicrosoft_VSDesigner, typeof(UITypeEditor))]
 130                 [DefaultValue (null)]
 131                 IDbConnection IDbCommand.Connection {
 132                         get { return Connection; }
 133                         set {
 134                                 if (!(value is OracleConnection))
 135                                         throw new InvalidCastException ("The value was not a valid OracleConnection.");
 136                                 Connection = (OracleConnection) value;
 137                         }
 138                 }
 139
 140                 IDataParameterCollection IDbCommand.Parameters {
 141                         get { return Parameters; }
 142                 }
 143
 144                 IDbTransaction IDbCommand.Transaction {
 145                         get { return Transaction; }
 146                         set {
 147                                 if (!(value is OracleTransaction))
 148                                         throw new ArgumentException ();
 149                                 Transaction = (OracleTransaction) value;
 150                         }
 151                 }
 152
 153                 [DesignerSerializationVisibility(DesignerSerializationVisibility.Content)]
 154                 public OracleParameterCollection Parameters {
 155                         get { return parameters; }
 156                 }
 157
 158                 [Browsable (false)]
 159                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 160                 public OracleTransaction Transaction {
 161                         get { return transaction; }
 162                         set { transaction = value; }
 163                 }
 164
 165                 [DefaultValue (UpdateRowSource.Both)]
 166                 public UpdateRowSource UpdatedRowSource {
 167                         get { return updatedRowSource; }
 168                         set { updatedRowSource = value; }
 169                 }
 170
 171                 #endregion
 172
 173                 #region Methods
 174
 175                 private void AssertCommandTextIsSet ()
 176                 {
 177                         if (CommandText == String.Empty || CommandText == null)
 178                                 throw new InvalidOperationException ("The command text for this Command has not been set.");
 179                 }
 180
 181                 private void AssertConnectionIsOpen ()
 182                 {
 183                         if (Connection == null || Connection.State == ConnectionState.Closed)
 184                                 throw new InvalidOperationException ("An open Connection object is required to continue.");
 185                 }
 186
 187                 private void AssertNoDataReader ()
 188                 {
 189                         if (Connection.DataReader != null)
 190                                 throw new InvalidOperationException ("There is already an open DataReader associated with this Connection which must be closed first.");
 191                 }
 192
 193                 private void AssertTransactionMatch ()
 194                 {
 195                         if (Connection.Transaction != null && Transaction != Connection.Transaction)
 196                                 throw new InvalidOperationException ("Execute requires the Command object to have a Transaction object when the Connection object assigned to the command is in a pending local transaction.  The Transaction property of the Command has not been initialized.");
 197                 }
 198
 199                 private void BindParameters (OciStatementHandle statement)
 200                 {
 201                         for (int p = 0; p < Parameters.Count; p++)
 202                                 Parameters[p].Bind (statement, Connection, (uint) p);
 203                 }
 204
 205                 [MonoTODO]
 206                 public void Cancel ()
 207                 {
 208                         throw new NotImplementedException ();
 209                 }
 210
 211                 [MonoTODO]
 212                 public object Clone ()
 213                 {
 214                         // create a new OracleCommand object with the same properties
 215
 216                         OracleCommand cmd = new OracleCommand ();
 217
 218                         cmd.CommandText = this.CommandText;
 219                         cmd.CommandType = this.CommandType;
 220
 221                         // FIXME: not sure if I should set the same object here
 222                         // or get a clone of these too
 223                         cmd.Connection = this.Connection;
 224                         cmd.Transaction = this.Transaction;
 225
 226                         foreach (OracleParameter parm in this.Parameters) {
 227
 228                                 OracleParameter newParm = cmd.CreateParameter ();
 229
 230                                 newParm.DbType = parm.DbType;
 231                                 newParm.Direction = parm.Direction;
 232                                 newParm.IsNullable = parm.IsNullable;
 233                                 newParm.Offset = parm.Offset;
 234                                 newParm.OracleType = parm.OracleType;
 235                                 newParm.ParameterName = parm.ParameterName;
 236                                 newParm.Precision = parm.Precision;
 237                                 newParm.Scale = parm.Scale;
 238                                 newParm.SourceColumn = parm.SourceColumn;
 239                                 newParm.SourceVersion = parm.SourceVersion;
 240                                 newParm.Value = parm.Value;
 241
 242                                 cmd.Parameters.Add (newParm);
 243                         }
 244
 245                         //cmd.Container = this.Container;
 246                         cmd.DesignTimeVisible = this.DesignTimeVisible;
 247                         //cmd.DesignMode = this.DesignMode;
 248                         cmd.Site = this.Site;
 249                         //cmd.UpdateRowSource = this.UpdateRowSource;
 250
 251                         return cmd;
 252                 }
 253
 254                 internal void UpdateParameterValues ()
 255                 {
 256                         if (Parameters.Count > 0) {
 257                                 foreach (OracleParameter parm in Parameters)
 258                                         parm.Update (this);
 259                         }
 260                 }
 261
 262                 internal void CloseDataReader ()
 263                 {
 264                         UpdateParameterValues ();
 265
 266                         Connection.DataReader = null;
 267                         if ((behavior & CommandBehavior.CloseConnection) != 0)
 268                                 Connection.Close ();
 269                 }
 270
 271                 public OracleParameter CreateParameter ()
 272                 {
 273                         return new OracleParameter ();
 274                 }
 275
 276                 internal void DeriveParameters ()
 277                 {
 278                         if (commandType != CommandType.StoredProcedure)
 279                                 throw new InvalidOperationException (String.Format ("OracleCommandBuilder DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
 280
 281                         //OracleParameterCollection localParameters = new OracleParameterCollection (this);
 282
 283                         throw new NotImplementedException ();
 284                 }
 285
 286                 private int ExecuteNonQueryInternal (OciStatementHandle statement, bool useAutoCommit)
 287                 {
 288                         if (preparedStatement == null)
 289                                 PrepareStatement (statement);
 290
 291                         bool isNonQuery = IsNonQuery (statement);
 292
 293                         BindParameters (statement);
 294                         if (isNonQuery == true)
 295                                 statement.ExecuteNonQuery (useAutoCommit);
 296                         else
 297                                 statement.ExecuteQuery (false);
 298
 299                         UpdateParameterValues ();
 300
 301                         int rowsAffected = statement.GetAttributeInt32 (OciAttributeType.RowCount, ErrorHandle);
 302
 303                         return rowsAffected;
 304                 }
 305
 306                 public int ExecuteNonQuery ()
 307                 {
 308                         AssertConnectionIsOpen ();
 309                         AssertTransactionMatch ();
 310                         AssertCommandTextIsSet ();
 311                         bool useAutoCommit = false;
 312
 313                         if (Transaction != null)
 314                                 Transaction.AttachToServiceContext ();
 315                         else
 316                                 useAutoCommit = true;
 317
 318                         OciStatementHandle statement = GetStatementHandle ();
 319                         try {
 320                                 return ExecuteNonQueryInternal (statement, useAutoCommit);
 321                         }
 322                         finally {
 323                                 SafeDisposeHandle (statement);
 324                         }
 325                 }
 326
 327                 public int ExecuteOracleNonQuery (out OracleString rowid)
 328                 {
 329                         AssertConnectionIsOpen ();
 330                         AssertTransactionMatch ();
 331                         AssertCommandTextIsSet ();
 332                         bool useAutoCommit = false;
 333
 334                         if (Transaction != null)
 335                                 Transaction.AttachToServiceContext ();
 336                         else
 337                                 useAutoCommit = true;
 338
 339                         OciStatementHandle statement = GetStatementHandle ();
 340
 341                         try {
 342                                 int retval = ExecuteNonQueryInternal (statement, useAutoCommit);
 343
 344                                 OciRowIdDescriptor descriptor = (OciRowIdDescriptor) Environment.Allocate (OciHandleType.RowId);
 345                                 descriptor.SetHandle (statement.GetAttributeIntPtr (OciAttributeType.RowId, ErrorHandle));
 346
 347                                 rowid = new OracleString (descriptor.GetRowId (ErrorHandle));
 348
 349                                 return retval;
 350                         }
 351                         finally {
 352                                 SafeDisposeHandle (statement);
 353                         }
 354                 }
 355
 356                 [MonoTODO]
 357                 public object ExecuteOracleScalar ()
 358                 {
 359                         object output = DBNull.Value;
 360
 361                         AssertConnectionIsOpen ();
 362                         AssertTransactionMatch ();
 363                         AssertCommandTextIsSet ();
 364
 365                         if (Transaction != null)
 366                                 Transaction.AttachToServiceContext ();
 367
 368                         OciStatementHandle statement = GetStatementHandle ();
 369                         try {
 370                                 if (preparedStatement == null)
 371                                         PrepareStatement (statement);
 372
 373                                 bool isNonQuery = IsNonQuery (statement);
 374
 375                                 BindParameters (statement);
 376
 377                                 if (isNonQuery == true)
 378                                         ExecuteNonQueryInternal (statement, false);
 379                                 else {
 380                                         statement.ExecuteQuery (false);
 381
 382                                         if (statement.Fetch ()) {
 383                                                 OciDefineHandle defineHandle = (OciDefineHandle) statement.Values [0];
 384                                                 if (!defineHandle.IsNull)
 385                                                         output = defineHandle.GetOracleValue ();
 386                                                 switch (defineHandle.DataType) {
 387                                                 case OciDataType.Blob:
 388                                                 case OciDataType.Clob:
 389                                                         ((OracleLob) output).connection = Connection;
 390                                                         break;
 391                                                 }
 392                                         }
 393                                         UpdateParameterValues ();
 394                                 }
 395
 396                                 return output;
 397                         }
 398                         finally {
 399                                 SafeDisposeHandle (statement);
 400                         }
 401                 }
 402
 403                 private bool IsNonQuery (OciStatementHandle statementHandle) \r
 404                 {
 405                         // assumes Prepare() has been called prior to calling this function
 406
 407                         OciStatementType statementType = statementHandle.GetStatementType ();
 408                         if (statementType.Equals (OciStatementType.Select))
 409                                 return false;
 410
 411                         return true;
 412                 }
 413
 414                 public OracleDataReader ExecuteReader ()
 415                 {
 416                         return ExecuteReader (CommandBehavior.Default);
 417                 }
 418
 419                 public OracleDataReader ExecuteReader (CommandBehavior behavior)
 420                 {
 421                         AssertConnectionIsOpen ();
 422                         AssertTransactionMatch ();
 423                         AssertCommandTextIsSet ();
 424                         AssertNoDataReader ();
 425                         bool hasRows = false;
 426
 427                         this.behavior = behavior;
 428
 429                         if (Transaction != null)
 430                                 Transaction.AttachToServiceContext ();
 431
 432                         OciStatementHandle statement = GetStatementHandle ();
 433                         OracleDataReader rd = null;
 434
 435                         try     {
 436                                 if (preparedStatement == null)
 437                                         PrepareStatement (statement);
 438                                 else
 439                                         preparedStatement = null;       // OracleDataReader releases the statement handle
 440
 441                                 bool isNonQuery = IsNonQuery (statement);
 442
 443                                 BindParameters (statement);
 444
 445                                 if (isNonQuery)
 446                                         ExecuteNonQueryInternal (statement, false);
 447                                 else {
 448                                         if ((behavior & CommandBehavior.SchemaOnly) != 0)
 449                                                 statement.ExecuteQuery (true);
 450                                         else
 451                                                 hasRows = statement.ExecuteQuery (false);
 452                                 }
 453
 454                                 rd = new OracleDataReader (this, statement, hasRows, behavior);
 455                         }
 456                         finally {
 457                                 if (statement != null && rd == null)
 458                                         statement.Dispose();
 459                         }
 460
 461                         return rd;
 462                 }
 463
 464                 public object ExecuteScalar ()
 465                 {
 466                         object output = DBNull.Value;
 467
 468                         AssertConnectionIsOpen ();
 469                         AssertTransactionMatch ();
 470                         AssertCommandTextIsSet ();
 471
 472                         if (Transaction != null)
 473                                 Transaction.AttachToServiceContext ();
 474
 475                         OciStatementHandle statement = GetStatementHandle ();
 476                         try {
 477                                 if (preparedStatement == null)
 478                                         PrepareStatement (statement);
 479
 480                                 bool isNonQuery = IsNonQuery (statement);
 481
 482                                 BindParameters (statement);
 483
 484                                 if (isNonQuery == true)
 485                                         ExecuteNonQueryInternal (statement, false);
 486                                 else {
 487                                         statement.ExecuteQuery (false);
 488
 489                                         if (statement.Fetch ()) {
 490                                                 OciDefineHandle defineHandle = (OciDefineHandle) statement.Values [0];
 491                                                 if (defineHandle.IsNull)
 492                                                         output = DBNull.Value;
 493                                                 else {
 494                                                         switch (defineHandle.DataType) {
 495                                                         case OciDataType.Blob:
 496                                                         case OciDataType.Clob:
 497                                                                 OracleLob lob = (OracleLob) defineHandle.GetValue ();
 498                                                                 lob.connection = Connection;
 499                                                                 output = lob.Value;
 500                                                                 lob.Close ();
 501                                                                 break;
 502                                                         default:
 503                                                                 output = defineHandle.GetValue ();
 504                                                                 break;
 505                                                         }
 506                                                 }
 507                                         }
 508                                         else
 509                                                 output = DBNull.Value;
 510                                         UpdateParameterValues ();
 511                                 }
 512                         }
 513                         finally {
 514                                 SafeDisposeHandle (statement);
 515                         }
 516
 517                         return output;
 518                 }
 519
 520                 private OciStatementHandle GetStatementHandle ()
 521                 {
 522                         AssertConnectionIsOpen ();
 523                         if (preparedStatement != null)
 524                                 return preparedStatement;
 525
 526                         OciStatementHandle h = (OciStatementHandle) Connection.Environment.Allocate (OciHandleType.Statement);
 527                         h.ErrorHandle = Connection.ErrorHandle;
 528                         h.Service = Connection.ServiceContext;
 529                         h.Command = this;
 530                         return h;
 531                 }
 532
 533                 private void SafeDisposeHandle (OciStatementHandle h)
 534                 {
 535                         if (h != null && h != preparedStatement)
 536                                 h.Dispose();
 537                 }
 538
 539                 IDbDataParameter IDbCommand.CreateParameter ()
 540                 {
 541                         return CreateParameter ();
 542                 }
 543
 544                 IDataReader IDbCommand.ExecuteReader ()
 545                 {
 546                         return ExecuteReader ();
 547                 }
 548
 549                 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
 550                 {
 551                         return ExecuteReader (behavior);
 552                 }
 553
 554                 void PrepareStatement (OciStatementHandle statement)
 555                 {
 556                         if (commandType == CommandType.StoredProcedure) {
 557                                 StringBuilder sb = new StringBuilder ();
 558                                 if (Parameters.Count > 0)\r
 559                                         foreach (OracleParameter parm in Parameters) {\r
 560                                                 if (sb.Length > 0)\r
 561                                                         sb.Append (",");\r
 562                                                 sb.Append (":" + parm.ParameterName);\r
 563                                         }\r
 564 \r
 565                                 string sql = "call " + commandText + "(" + sb.ToString() + ")";\r
 566                                 statement.Prepare (sql);
 567                         }
 568                         else    // Text
 569                                 statement.Prepare (commandText);
 570                 }
 571
 572                 public void Prepare ()
 573                 {
 574                         AssertConnectionIsOpen ();
 575                         OciStatementHandle statement = GetStatementHandle ();
 576                         PrepareStatement (statement);
 577                         preparedStatement = statement;
 578                 }
 579
 580                 #endregion // Methods
 581         }
 582 }