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                                 // InvalidCastException is expected when types do not match
 135                                 Connection = (OracleConnection) value;
 136                         }
 137                 }
 138
 139                 IDataParameterCollection IDbCommand.Parameters {
 140                         get { return Parameters; }
 141                 }
 142
 143                 IDbTransaction IDbCommand.Transaction {
 144                         get { return Transaction; }
 145                         set {
 146                                 // InvalidCastException is expected when types do not match
 147                                 Transaction = (OracleTransaction) value;
 148                         }
 149                 }
 150
 151                 [DesignerSerializationVisibility(DesignerSerializationVisibility.Content)]
 152                 public OracleParameterCollection Parameters {
 153                         get { return parameters; }
 154                 }
 155
 156                 [Browsable (false)]
 157                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 158                 public OracleTransaction Transaction {
 159                         get { return transaction; }
 160                         set { transaction = value; }
 161                 }
 162
 163                 [DefaultValue (UpdateRowSource.Both)]
 164                 public UpdateRowSource UpdatedRowSource {
 165                         get { return updatedRowSource; }
 166                         set { updatedRowSource = value; }
 167                 }
 168
 169                 #endregion
 170
 171                 #region Methods
 172
 173                 private void AssertCommandTextIsSet ()
 174                 {
 175                         if (CommandText == String.Empty || CommandText == null)
 176                                 throw new InvalidOperationException ("The command text for this Command has not been set.");
 177                 }
 178
 179                 private void AssertConnectionIsOpen ()
 180                 {
 181                         if (Connection == null || Connection.State == ConnectionState.Closed)
 182                                 throw new InvalidOperationException ("An open Connection object is required to continue.");
 183                 }
 184
 185                 private void AssertNoDataReader ()
 186                 {
 187                         if (Connection.DataReader != null)
 188                                 throw new InvalidOperationException ("There is already an open DataReader associated with this Connection which must be closed first.");
 189                 }
 190
 191                 private void AssertTransactionMatch ()
 192                 {
 193                         if (Connection.Transaction != null && Transaction != Connection.Transaction)
 194                                 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.");
 195                 }
 196
 197                 private void BindParameters (OciStatementHandle statement)
 198                 {
 199                         for (int p = 0; p < Parameters.Count; p++)
 200                                 Parameters[p].Bind (statement, Connection, (uint) p);
 201                 }
 202
 203                 [MonoTODO]
 204                 public void Cancel ()
 205                 {
 206                         throw new NotImplementedException ();
 207                 }
 208
 209                 [MonoTODO]
 210                 public object Clone ()
 211                 {
 212                         // create a new OracleCommand object with the same properties
 213
 214                         OracleCommand cmd = new OracleCommand ();
 215
 216                         cmd.CommandText = this.CommandText;
 217                         cmd.CommandType = this.CommandType;
 218
 219                         // FIXME: not sure if I should set the same object here
 220                         // or get a clone of these too
 221                         cmd.Connection = this.Connection;
 222                         cmd.Transaction = this.Transaction;
 223
 224                         foreach (OracleParameter parm in this.Parameters) {
 225
 226                                 OracleParameter newParm = cmd.CreateParameter ();
 227
 228                                 newParm.DbType = parm.DbType;
 229                                 newParm.Direction = parm.Direction;
 230                                 newParm.IsNullable = parm.IsNullable;
 231                                 newParm.Offset = parm.Offset;
 232                                 newParm.OracleType = parm.OracleType;
 233                                 newParm.ParameterName = parm.ParameterName;
 234                                 newParm.Precision = parm.Precision;
 235                                 newParm.Scale = parm.Scale;
 236                                 newParm.SourceColumn = parm.SourceColumn;
 237                                 newParm.SourceVersion = parm.SourceVersion;
 238                                 newParm.Value = parm.Value;
 239
 240                                 cmd.Parameters.Add (newParm);
 241                         }
 242
 243                         //cmd.Container = this.Container;
 244                         cmd.DesignTimeVisible = this.DesignTimeVisible;
 245                         //cmd.DesignMode = this.DesignMode;
 246                         cmd.Site = this.Site;
 247                         //cmd.UpdateRowSource = this.UpdateRowSource;
 248
 249                         return cmd;
 250                 }
 251
 252                 internal void UpdateParameterValues ()
 253                 {
 254                         if (Parameters.Count > 0) {
 255                                 foreach (OracleParameter parm in Parameters)
 256                                         parm.Update (this);
 257                         }
 258                 }
 259
 260                 internal void CloseDataReader ()
 261                 {
 262                         UpdateParameterValues ();
 263
 264                         Connection.DataReader = null;
 265                         if ((behavior & CommandBehavior.CloseConnection) != 0)
 266                                 Connection.Close ();
 267                 }
 268
 269                 public OracleParameter CreateParameter ()
 270                 {
 271                         return new OracleParameter ();
 272                 }
 273
 274                 internal void DeriveParameters ()
 275                 {
 276                         if (commandType != CommandType.StoredProcedure)
 277                                 throw new InvalidOperationException (String.Format ("OracleCommandBuilder DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
 278
 279                         //OracleParameterCollection localParameters = new OracleParameterCollection (this);
 280
 281                         throw new NotImplementedException ();
 282                 }
 283
 284                 private int ExecuteNonQueryInternal (OciStatementHandle statement, bool useAutoCommit)
 285                 {
 286                         if (preparedStatement == null)
 287                                 PrepareStatement (statement);
 288
 289                         bool isNonQuery = IsNonQuery (statement);
 290
 291                         BindParameters (statement);
 292                         if (isNonQuery == true)
 293                                 statement.ExecuteNonQuery (useAutoCommit);
 294                         else
 295                                 statement.ExecuteQuery (false);
 296
 297                         UpdateParameterValues ();
 298
 299                         int rowsAffected = statement.GetAttributeInt32 (OciAttributeType.RowCount, ErrorHandle);
 300
 301                         return rowsAffected;
 302                 }
 303
 304                 public int ExecuteNonQuery ()
 305                 {
 306                         AssertConnectionIsOpen ();
 307                         AssertTransactionMatch ();
 308                         AssertCommandTextIsSet ();
 309                         bool useAutoCommit = false;
 310
 311                         if (Transaction != null)
 312                                 Transaction.AttachToServiceContext ();
 313                         else
 314                                 useAutoCommit = true;
 315
 316                         OciStatementHandle statement = GetStatementHandle ();
 317                         try {
 318                                 return ExecuteNonQueryInternal (statement, useAutoCommit);
 319                         }
 320                         finally {
 321                                 SafeDisposeHandle (statement);
 322                         }
 323                 }
 324
 325                 public int ExecuteOracleNonQuery (out OracleString rowid)
 326                 {
 327                         AssertConnectionIsOpen ();
 328                         AssertTransactionMatch ();
 329                         AssertCommandTextIsSet ();
 330                         bool useAutoCommit = false;
 331
 332                         if (Transaction != null)
 333                                 Transaction.AttachToServiceContext ();
 334                         else
 335                                 useAutoCommit = true;
 336
 337                         OciStatementHandle statement = GetStatementHandle ();
 338
 339                         try {
 340                                 int retval = ExecuteNonQueryInternal (statement, useAutoCommit);
 341
 342                                 OciRowIdDescriptor descriptor = (OciRowIdDescriptor) Environment.Allocate (OciHandleType.RowId);
 343                                 descriptor.SetHandle (statement.GetAttributeIntPtr (OciAttributeType.RowId, ErrorHandle));
 344
 345                                 rowid = new OracleString (descriptor.GetRowId (ErrorHandle));
 346
 347                                 return retval;
 348                         }
 349                         finally {
 350                                 SafeDisposeHandle (statement);
 351                         }
 352                 }
 353
 354                 [MonoTODO]
 355                 public object ExecuteOracleScalar ()
 356                 {
 357                         object output = DBNull.Value;
 358
 359                         AssertConnectionIsOpen ();
 360                         AssertTransactionMatch ();
 361                         AssertCommandTextIsSet ();
 362
 363                         if (Transaction != null)
 364                                 Transaction.AttachToServiceContext ();
 365
 366                         OciStatementHandle statement = GetStatementHandle ();
 367                         try {
 368                                 if (preparedStatement == null)
 369                                         PrepareStatement (statement);
 370
 371                                 bool isNonQuery = IsNonQuery (statement);
 372
 373                                 BindParameters (statement);
 374
 375                                 if (isNonQuery == true)
 376                                         ExecuteNonQueryInternal (statement, false);
 377                                 else {
 378                                         statement.ExecuteQuery (false);
 379
 380                                         if (statement.Fetch ()) {
 381                                                 OciDefineHandle defineHandle = (OciDefineHandle) statement.Values [0];
 382                                                 if (!defineHandle.IsNull)
 383                                                         output = defineHandle.GetOracleValue (Connection.SessionFormatProvider);
 384                                                 switch (defineHandle.DataType) {
 385                                                 case OciDataType.Blob:
 386                                                 case OciDataType.Clob:
 387                                                         ((OracleLob) output).connection = Connection;
 388                                                         break;
 389                                                 }
 390                                         }
 391                                         UpdateParameterValues ();
 392                                 }
 393
 394                                 return output;
 395                         }
 396                         finally {
 397                                 SafeDisposeHandle (statement);
 398                         }
 399                 }
 400
 401                 private bool IsNonQuery (OciStatementHandle statementHandle)
 402                 {
 403                         // assumes Prepare() has been called prior to calling this function
 404
 405                         OciStatementType statementType = statementHandle.GetStatementType ();
 406                         if (statementType.Equals (OciStatementType.Select))
 407                                 return false;
 408
 409                         return true;
 410                 }
 411
 412                 public OracleDataReader ExecuteReader ()
 413                 {
 414                         return ExecuteReader (CommandBehavior.Default);
 415                 }
 416
 417                 public OracleDataReader ExecuteReader (CommandBehavior behavior)
 418                 {
 419                         AssertConnectionIsOpen ();
 420                         AssertTransactionMatch ();
 421                         AssertCommandTextIsSet ();
 422                         AssertNoDataReader ();
 423                         bool hasRows = false;
 424
 425                         this.behavior = behavior;
 426
 427                         if (Transaction != null)
 428                                 Transaction.AttachToServiceContext ();
 429
 430                         OciStatementHandle statement = GetStatementHandle ();
 431                         OracleDataReader rd = null;
 432
 433                         try     {
 434                                 if (preparedStatement == null)
 435                                         PrepareStatement (statement);
 436                                 else
 437                                         preparedStatement = null;       // OracleDataReader releases the statement handle
 438
 439                                 bool isNonQuery = IsNonQuery (statement);
 440
 441                                 BindParameters (statement);
 442
 443                                 if (isNonQuery)
 444                                         ExecuteNonQueryInternal (statement, false);
 445                                 else {
 446                                         if ((behavior & CommandBehavior.SchemaOnly) != 0)
 447                                                 statement.ExecuteQuery (true);
 448                                         else
 449                                                 hasRows = statement.ExecuteQuery (false);
 450                                 }
 451
 452                                 rd = new OracleDataReader (this, statement, hasRows, behavior);
 453                         }
 454                         finally {
 455                                 if (statement != null && rd == null)
 456                                         statement.Dispose();
 457                         }
 458
 459                         return rd;
 460                 }
 461
 462                 public object ExecuteScalar ()
 463                 {
 464                         object output = DBNull.Value;
 465
 466                         AssertConnectionIsOpen ();
 467                         AssertTransactionMatch ();
 468                         AssertCommandTextIsSet ();
 469
 470                         if (Transaction != null)
 471                                 Transaction.AttachToServiceContext ();
 472
 473                         OciStatementHandle statement = GetStatementHandle ();
 474                         try {
 475                                 if (preparedStatement == null)
 476                                         PrepareStatement (statement);
 477
 478                                 bool isNonQuery = IsNonQuery (statement);
 479
 480                                 BindParameters (statement);
 481
 482                                 if (isNonQuery == true)
 483                                         ExecuteNonQueryInternal (statement, false);
 484                                 else {
 485                                         statement.ExecuteQuery (false);
 486
 487                                         if (statement.Fetch ()) {
 488                                                 OciDefineHandle defineHandle = (OciDefineHandle) statement.Values [0];
 489                                                 if (defineHandle.IsNull)
 490                                                         output = DBNull.Value;
 491                                                 else {
 492                                                         switch (defineHandle.DataType) {
 493                                                         case OciDataType.Blob:
 494                                                         case OciDataType.Clob:
 495                                                                 OracleLob lob = (OracleLob) defineHandle.GetValue (
 496                                                                         Connection.SessionFormatProvider);
 497                                                                 lob.connection = Connection;
 498                                                                 output = lob.Value;
 499                                                                 lob.Close ();
 500                                                                 break;
 501                                                         default:
 502                                                                 output = defineHandle.GetValue (
 503                                                                         Connection.SessionFormatProvider);
 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)
 559                                         foreach (OracleParameter parm in Parameters) {
 560                                                 if (sb.Length > 0)
 561                                                         sb.Append (",");
 562                                                 sb.Append (":" + parm.ParameterName);
 563                                         }
 564
 565                                 string sql = "call " + commandText + "(" + sb.ToString() + ")";
 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                 protected override void Dispose (bool disposing)
 581                 {
 582                         if (disposing)
 583                                 if (Parameters.Count > 0)
 584                                         foreach (OracleParameter parm in Parameters)
 585                                                 parm.FreeHandle ();
 586                         base.Dispose (disposing);
 587                 }
 588
 589                 #endregion // Methods
 590         }
 591 }