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 //    Marek Safar <marek.safar@gmail.com>
  14 //
  15 // Copyright (C) Daniel Morgan, 2002, 2004-2005
  16 // Copyright (C) Tim Coleman , 2003
  17 //
  18 // Licensed under the MIT/X11 License.
  19 //
  20
  21 using System;
  22 using System.ComponentModel;
  23 using System.Data;
  24 using System.Data.Common;
  25 using System.Data.OracleClient.Oci;
  26 using System.Drawing.Design;
  27 using System.Text;
  28 using System.Threading;
  29
  30 namespace System.Data.OracleClient
  31 {
  32         [DefaultEvent ("RecordsAffected")]
  33         [Designer ("Microsoft.VSDesigner.Data.VS.OracleCommandDesigner, " + Consts.AssemblyMicrosoft_VSDesigner)]
  34         [ToolboxItem (true)]
  35         public sealed class OracleCommand :
  36                 DbCommand, ICloneable
  37         {
  38                 #region Fields
  39
  40                 CommandBehavior behavior;
  41                 string commandText;
  42                 CommandType commandType;
  43                 OracleConnection connection;
  44                 bool designTimeVisible;
  45                 OracleParameterCollection parameters;
  46                 OracleTransaction transaction;
  47                 UpdateRowSource updatedRowSource;
  48                 OciStatementHandle preparedStatement;
  49
  50                 int moreResults;
  51
  52                 #endregion // Fields
  53
  54                 #region Constructors
  55
  56                 public OracleCommand ()
  57                         : this (String.Empty, null, null)
  58                 {
  59                 }
  60
  61                 public OracleCommand (string commandText)
  62                         : this (commandText, null, null)
  63                 {
  64                 }
  65
  66                 public OracleCommand (string commandText, OracleConnection connection)
  67                         : this (commandText, connection, null)
  68                 {
  69                 }
  70
  71                 public OracleCommand (string commandText, OracleConnection connection, OracleTransaction tx)
  72                 {
  73                         moreResults = -1;
  74                         preparedStatement = null;
  75                         CommandText = commandText;
  76                         Connection = connection;
  77                         Transaction = tx;
  78                         CommandType = CommandType.Text;
  79                         UpdatedRowSource = UpdateRowSource.Both;
  80                         DesignTimeVisible = true;
  81                         parameters = new OracleParameterCollection ();
  82                 }
  83
  84                 #endregion // Constructors
  85
  86                 #region Properties
  87
  88                 [DefaultValue ("")]
  89                 [RefreshProperties (RefreshProperties.All)]
  90                 [Editor ("Microsoft.VSDesigner.Data.Oracle.Design.OracleCommandTextEditor, " + Consts.AssemblyMicrosoft_VSDesigner, typeof(UITypeEditor))]
  91                 public
  92                 override
  93                 string CommandText {
  94                         get {
  95                                 if (commandText == null)
  96                                         return string.Empty;
  97
  98                                 return commandText;
  99                         }
 100                         set { commandText = value; }
 101                 }
 102
 103                 [RefreshProperties (RefreshProperties.All)]
 104                 [DefaultValue (CommandType.Text)]
 105                 public
 106                 override
 107                 CommandType CommandType {
 108                         get { return commandType; }
 109                         set {
 110                                 if (value == CommandType.TableDirect)
 111                                         throw new ArgumentException ("OracleClient provider does not support TableDirect CommandType.");
 112                                 commandType = value;
 113                         }
 114                 }
 115
 116                 [DefaultValue (null)]
 117                 [Editor ("Microsoft.VSDesigner.Data.Design.DbConnectionEditor, " + Consts.AssemblyMicrosoft_VSDesigner, typeof(UITypeEditor))]
 118                 public
 119                 new
 120                 OracleConnection Connection {
 121                         get { return connection; }
 122                         set { connection = value; }
 123                 }
 124
 125                 [Browsable (false)]
 126                 [EditorBrowsable (EditorBrowsableState.Never)]
 127                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 128                 public override int CommandTimeout {
 129                         get { return 0; }
 130                         set { }
 131                 }
 132
 133                 [MonoTODO]
 134                 protected override DbConnection DbConnection {
 135                         get { return Connection; }
 136                         set { Connection = (OracleConnection) value; }
 137                 }
 138
 139                 [MonoTODO]
 140                 protected override DbParameterCollection DbParameterCollection {
 141                         get { return Parameters; }
 142                 }
 143
 144                 [MonoTODO]
 145                 protected override DbTransaction DbTransaction {
 146                         get { return Transaction; }
 147                         set { Transaction = (OracleTransaction) value; }
 148                 }
 149
 150                 [DefaultValue (true)]
 151                 [Browsable (false)]
 152                 [DesignOnly (true)]
 153                 [EditorBrowsable (EditorBrowsableState.Never)]
 154                 public
 155                 override
 156                 bool DesignTimeVisible {
 157                         get { return designTimeVisible; }
 158                         set { designTimeVisible = value; }
 159                 }
 160
 161                 internal OciEnvironmentHandle Environment {
 162                         get { return Connection.Environment; }
 163                 }
 164
 165                 internal OciErrorHandle ErrorHandle {
 166                         get { return Connection.ErrorHandle; }
 167                 }
 168
 169
 170                 [DesignerSerializationVisibility(DesignerSerializationVisibility.Content)]
 171                 public
 172                 new
 173                 OracleParameterCollection Parameters {
 174                         get { return parameters; }
 175                 }
 176
 177                 [Browsable (false)]
 178                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 179                 public
 180                 new
 181                 OracleTransaction Transaction {
 182                         get { return transaction; }
 183                         set { transaction = value; }
 184                 }
 185
 186                 [DefaultValue (UpdateRowSource.Both)]
 187                 public
 188                 override
 189                 UpdateRowSource UpdatedRowSource {
 190                         get { return updatedRowSource; }
 191                         set { updatedRowSource = value; }
 192                 }
 193
 194                 #endregion
 195
 196                 #region Methods
 197
 198                 private void AssertCommandTextIsSet ()
 199                 {
 200                         if (CommandText.Length == 0)
 201                                 throw new InvalidOperationException ("The command text for this Command has not been set.");
 202                 }
 203
 204                 private void AssertConnectionIsOpen ()
 205                 {
 206                         if (Connection == null || Connection.State == ConnectionState.Closed)
 207                                 throw new InvalidOperationException ("An open Connection object is required to continue.");
 208                 }
 209
 210                 private void AssertTransactionMatch ()
 211                 {
 212                         if (Connection.Transaction != null && Transaction != Connection.Transaction)
 213                                 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.");
 214                 }
 215
 216                 private void BindParameters (OciStatementHandle statement)
 217                 {
 218                         for (int p = 0; p < Parameters.Count; p++)
 219                                 Parameters[p].Bind (statement, Connection, (uint) p);
 220                 }
 221
 222                 [MonoTODO]
 223                 public
 224                 override
 225                 void Cancel ()
 226                 {
 227                         throw new NotImplementedException ();
 228                 }
 229
 230                 [MonoTODO]
 231                 public object Clone ()
 232                 {
 233                         // create a new OracleCommand object with the same properties
 234
 235                         OracleCommand cmd = new OracleCommand ();
 236
 237                         cmd.CommandText = this.CommandText;
 238                         cmd.CommandType = this.CommandType;
 239
 240                         // FIXME: not sure if I should set the same object here
 241                         // or get a clone of these too
 242                         cmd.Connection = this.Connection;
 243                         cmd.Transaction = this.Transaction;
 244
 245                         foreach (OracleParameter parm in this.Parameters) {
 246
 247                                 OracleParameter newParm = cmd.CreateParameter ();
 248
 249                                 newParm.DbType = parm.DbType;
 250                                 newParm.Direction = parm.Direction;
 251                                 newParm.IsNullable = parm.IsNullable;
 252                                 newParm.Offset = parm.Offset;
 253                                 newParm.OracleType = parm.OracleType;
 254                                 newParm.ParameterName = parm.ParameterName;
 255                                 //newParm.Precision = parm.Precision;
 256                                 //newParm.Scale = parm.Scale;
 257                                 newParm.SourceColumn = parm.SourceColumn;
 258                                 newParm.SourceVersion = parm.SourceVersion;
 259                                 newParm.Value = parm.Value;
 260
 261                                 cmd.Parameters.Add (newParm);
 262                         }
 263
 264                         //cmd.Container = this.Container;
 265                         cmd.DesignTimeVisible = this.DesignTimeVisible;
 266                         //cmd.DesignMode = this.DesignMode;
 267                         cmd.Site = this.Site;
 268                         //cmd.UpdateRowSource = this.UpdateRowSource;
 269
 270                         return cmd;
 271                 }
 272
 273                 protected override DbParameter CreateDbParameter ()
 274                 {
 275                         return CreateParameter ();
 276                 }
 277
 278                 protected override DbDataReader ExecuteDbDataReader (CommandBehavior behavior)
 279                 {
 280                         return ExecuteReader (behavior);
 281                 }
 282
 283                 internal void UpdateParameterValues ()
 284                 {
 285                         moreResults = -1;
 286                         if (Parameters.Count > 0) {
 287                                 bool foundCursor = false;
 288                                 for (int p = 0; p < Parameters.Count; p++) {
 289                                         OracleParameter parm = Parameters [p];
 290                                         if (parm.OracleType.Equals (OracleType.Cursor)) {
 291                                                 if (!foundCursor && parm.Direction != ParameterDirection.Input) {
 292                                                         // if there are multiple REF CURSORs,
 293                                                         // you only can get the first cursor for now
 294                                                         // because user of OracleDataReader
 295                                                         // will do a NextResult to get the next
 296                                                         // REF CURSOR (if it exists)
 297                                                         foundCursor = true;
 298                                                         parm.Update (this);
 299                                                         if (p + 1 == Parameters.Count)
 300                                                                 moreResults = -1;
 301                                                         else
 302                                                                 moreResults = p;
 303                                                 }
 304                                         } else
 305                                                 parm.Update (this);
 306                                 }
 307                         }
 308                 }
 309
 310                 internal void CloseDataReader ()
 311                 {
 312                         Connection.DataReader = null;
 313                         if ((behavior & CommandBehavior.CloseConnection) != 0)
 314                                 Connection.Close ();
 315                 }
 316
 317                 public
 318                 new
 319                 OracleParameter CreateParameter ()
 320                 {
 321                         return new OracleParameter ();
 322                 }
 323
 324                 internal void DeriveParameters ()
 325                 {
 326                         if (commandType != CommandType.StoredProcedure)
 327                                 throw new InvalidOperationException (String.Format ("OracleCommandBuilder DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
 328
 329                         //OracleParameterCollection localParameters = new OracleParameterCollection (this);
 330
 331                         throw new NotImplementedException ();
 332                 }
 333
 334                 private int ExecuteNonQueryInternal (OciStatementHandle statement, bool useAutoCommit)
 335                 {
 336                         moreResults = -1;
 337
 338                         if (preparedStatement == null)
 339                                 PrepareStatement (statement);
 340
 341                         bool isNonQuery = IsNonQuery (statement);
 342
 343                         BindParameters (statement);
 344                         if (isNonQuery == true)
 345                                 statement.ExecuteNonQuery (useAutoCommit);
 346                         else
 347                                 statement.ExecuteQuery (false);
 348
 349                         UpdateParameterValues ();
 350
 351                         int rowsAffected = statement.GetAttributeInt32 (OciAttributeType.RowCount, ErrorHandle);
 352
 353                         return rowsAffected;
 354                 }
 355
 356                 public
 357                 override
 358                 int ExecuteNonQuery ()
 359                 {
 360                         moreResults = -1;
 361
 362                         AssertConnectionIsOpen ();
 363                         AssertTransactionMatch ();
 364                         AssertCommandTextIsSet ();
 365                         bool useAutoCommit = false;
 366
 367                         if (Transaction != null)
 368                                 Transaction.AttachToServiceContext ();
 369                         else
 370                                 useAutoCommit = true;
 371
 372                         OciStatementHandle statement = GetStatementHandle ();
 373                         try {
 374                                 return ExecuteNonQueryInternal (statement, useAutoCommit);
 375                         } finally {
 376                                 SafeDisposeHandle (statement);
 377                         }
 378                 }
 379
 380                 public int ExecuteOracleNonQuery (out OracleString rowid)
 381                 {
 382                         moreResults = -1;
 383
 384                         AssertConnectionIsOpen ();
 385                         AssertTransactionMatch ();
 386                         AssertCommandTextIsSet ();
 387                         bool useAutoCommit = false;
 388
 389                         if (Transaction != null)
 390                                 Transaction.AttachToServiceContext ();
 391                         else
 392                                 useAutoCommit = true;
 393
 394                         OciStatementHandle statement = GetStatementHandle ();
 395
 396                         try {
 397                                 int retval = ExecuteNonQueryInternal (statement, useAutoCommit);
 398                                 OciRowIdDescriptor rowIdDescriptor = statement.GetAttributeRowIdDescriptor (ErrorHandle, Environment);
 399                                 string srowid = rowIdDescriptor.GetRowIdToString (ErrorHandle);
 400                                 rowid = new OracleString (srowid);
 401                                 rowIdDescriptor = null;
 402                                 return retval;
 403                         } finally {
 404                                 SafeDisposeHandle (statement);
 405                         }
 406                 }
 407
 408                 public object ExecuteOracleScalar ()
 409                 {
 410                         moreResults = -1;
 411
 412                         object output = DBNull.Value;
 413
 414                         AssertConnectionIsOpen ();
 415                         AssertTransactionMatch ();
 416                         AssertCommandTextIsSet ();
 417
 418                         if (Transaction != null)
 419                                 Transaction.AttachToServiceContext ();
 420
 421                         OciStatementHandle statement = GetStatementHandle ();
 422                         try {
 423                                 if (preparedStatement == null)
 424                                         PrepareStatement (statement);
 425
 426                                 bool isNonQuery = IsNonQuery (statement);
 427
 428                                 BindParameters (statement);
 429
 430                                 if (isNonQuery == true)
 431                                         ExecuteNonQueryInternal (statement, false);
 432                                 else {
 433                                         statement.ExecuteQuery (false);
 434
 435                                         if (statement.Fetch ()) {
 436                                                 OciDefineHandle defineHandle = (OciDefineHandle) statement.Values [0];
 437                                                 if (!defineHandle.IsNull)
 438                                                         output = defineHandle.GetOracleValue (Connection.SessionFormatProvider, Connection);
 439                                                 switch (defineHandle.DataType) {
 440                                                 case OciDataType.Blob:
 441                                                 case OciDataType.Clob:
 442                                                         ((OracleLob) output).connection = Connection;
 443                                                         break;
 444                                                 }
 445                                         }
 446                                         UpdateParameterValues ();
 447                                 }
 448
 449                                 return output;
 450                         } finally {
 451                                 SafeDisposeHandle (statement);
 452                         }
 453                 }
 454
 455                 private bool IsNonQuery (OciStatementHandle statementHandle)
 456                 {
 457                         // assumes Prepare() has been called prior to calling this function
 458
 459                         OciStatementType statementType = statementHandle.GetStatementType ();
 460                         if (statementType.Equals (OciStatementType.Select))
 461                                 return false;
 462
 463                         return true;
 464                 }
 465
 466                 public
 467                 new
 468                 OracleDataReader ExecuteReader ()
 469                 {
 470                         return ExecuteReader (CommandBehavior.Default);
 471                 }
 472
 473                 public
 474                 new
 475                 OracleDataReader ExecuteReader (CommandBehavior behavior)
 476                 {
 477                         AssertConnectionIsOpen ();
 478                         AssertTransactionMatch ();
 479                         AssertCommandTextIsSet ();
 480
 481                         moreResults = -1;
 482
 483                         bool hasRows = false;
 484
 485                         this.behavior = behavior;
 486
 487                         if (Transaction != null)
 488                                 Transaction.AttachToServiceContext ();
 489
 490                         OciStatementHandle statement = GetStatementHandle ();
 491                         OracleDataReader rd = null;
 492
 493                         try {
 494                                 if (preparedStatement == null)
 495                                         PrepareStatement (statement);
 496                                 else
 497                                         preparedStatement = null;       // OracleDataReader releases the statement handle
 498
 499                                 bool isNonQuery = IsNonQuery (statement);
 500
 501                                 BindParameters (statement);
 502
 503                                 if (isNonQuery)
 504                                         ExecuteNonQueryInternal (statement, false);
 505                                 else {
 506                                         if ((behavior & CommandBehavior.SchemaOnly) != 0)
 507                                                 statement.ExecuteQuery (true);
 508                                         else
 509                                                 hasRows = statement.ExecuteQuery (false);
 510
 511                                         UpdateParameterValues ();
 512                                 }
 513
 514                                 if (Parameters.Count > 0) {
 515                                         for (int p = 0; p < Parameters.Count; p++) {
 516                                                 OracleParameter parm = Parameters [p];
 517                                                 if (parm.OracleType.Equals (OracleType.Cursor)) {
 518                                                         if (parm.Direction != ParameterDirection.Input) {
 519                                                                 rd = (OracleDataReader) parm.Value;
 520                                                                 break;
 521                                                         }
 522                                                 }
 523                                         }
 524                                 }
 525
 526                                 if (rd == null)
 527                                         rd = new OracleDataReader (this, statement, hasRows, behavior);
 528
 529                         } finally {
 530                                 if (statement != null && rd == null)
 531                                         statement.Dispose();
 532                         }
 533
 534                         return rd;
 535                 }
 536
 537                 public
 538                 override
 539                 object ExecuteScalar ()
 540                 {
 541                         moreResults = -1;
 542                         object output = null;//if we find nothing we return this
 543
 544                         AssertConnectionIsOpen ();
 545                         AssertTransactionMatch ();
 546                         AssertCommandTextIsSet ();
 547
 548                         if (Transaction != null)
 549                                 Transaction.AttachToServiceContext ();
 550
 551                         OciStatementHandle statement = GetStatementHandle ();
 552                         try {
 553                                 if (preparedStatement == null)
 554                                         PrepareStatement (statement);
 555
 556                                 bool isNonQuery = IsNonQuery (statement);
 557
 558                                 BindParameters (statement);
 559
 560                                 if (isNonQuery == true)
 561                                         ExecuteNonQueryInternal (statement, false);
 562                                 else {
 563                                         statement.ExecuteQuery (false);
 564
 565                                         if (statement.Fetch ()) {
 566                                                 OciDefineHandle defineHandle = (OciDefineHandle) statement.Values [0];
 567                                                 if (!defineHandle.IsNull)
 568                                                 {
 569                                                         switch (defineHandle.DataType) {
 570                                                         case OciDataType.Blob:
 571                                                         case OciDataType.Clob:
 572                                                                 OracleLob lob = (OracleLob) defineHandle.GetValue (
 573                                                                         Connection.SessionFormatProvider, Connection);
 574                                                                 lob.connection = Connection;
 575                                                                 output = lob.Value;
 576                                                                 lob.Close ();
 577                                                                 break;
 578                                                         default:
 579                                                                 output = defineHandle.GetValue (
 580                                                                         Connection.SessionFormatProvider, Connection);
 581                                                                 break;
 582                                                         }
 583                                                 }
 584                                         }
 585                                         UpdateParameterValues ();
 586                                 }
 587                         } finally {
 588                                 SafeDisposeHandle (statement);
 589                         }
 590
 591                         return output;
 592                 }
 593
 594                 internal OciStatementHandle GetNextResult ()
 595                 {
 596                         if (moreResults == -1)
 597                                 return null;
 598
 599                         if (Parameters.Count > 0) {
 600                                 int p = moreResults + 1;
 601
 602                                 if (p >= Parameters.Count) {
 603                                         moreResults = -1;
 604                                         return null;
 605                                 }
 606
 607                                 for (; p < Parameters.Count; p++) {
 608                                         OracleParameter parm = Parameters [p];
 609                                         if (parm.OracleType.Equals (OracleType.Cursor)) {
 610                                                 if (parm.Direction != ParameterDirection.Input) {
 611                                                         if (p + 1 == Parameters.Count)
 612                                                                 moreResults = -1;
 613                                                         else
 614                                                                 moreResults = p;
 615                                                         return parm.GetOutRefCursor (this);
 616
 617                                                 }
 618                                         }
 619                                 }
 620                         }
 621
 622                         moreResults = -1;
 623                         return null;
 624                 }
 625
 626                 private OciStatementHandle GetStatementHandle ()
 627                 {
 628                         AssertConnectionIsOpen ();
 629                         if (preparedStatement != null)
 630                                 return preparedStatement;
 631
 632                         OciStatementHandle h = (OciStatementHandle) Connection.Environment.Allocate (OciHandleType.Statement);
 633                         h.ErrorHandle = Connection.ErrorHandle;
 634                         h.Service = Connection.ServiceContext;
 635                         h.Command = this;
 636                         return h;
 637                 }
 638
 639                 private void SafeDisposeHandle (OciStatementHandle h)
 640                 {
 641                         if (h != null && h != preparedStatement)
 642                                 h.Dispose();
 643                 }
 644
 645
 646                 void PrepareStatement (OciStatementHandle statement)
 647                 {
 648                         if (commandType == CommandType.StoredProcedure) {
 649                                 StringBuilder sb = new StringBuilder ();
 650                                 if (Parameters.Count > 0)
 651                                         foreach (OracleParameter parm in Parameters) {
 652                                                 if (sb.Length > 0)
 653                                                         sb.Append (",");
 654                                                 sb.Append (parm.ParameterName + "=>:" + parm.ParameterName);
 655                                         }
 656
 657                                 string sql = "begin " + commandText + "(" + sb.ToString() + "); end;";
 658                                 statement.Prepare (sql);
 659                         } else  // Text
 660                                 statement.Prepare (commandText);
 661                 }
 662
 663                 public
 664                 override
 665                 void Prepare ()
 666                 {
 667                         AssertConnectionIsOpen ();
 668                         OciStatementHandle statement = GetStatementHandle ();
 669                         PrepareStatement (statement);
 670                         preparedStatement = statement;
 671                 }
 672
 673                 protected override void Dispose (bool disposing)
 674                 {
 675                         if (preparedStatement != null)
 676                                 OciCalls.OCIHandleFree(preparedStatement,
 677                                                        OciHandleType.Statement);
 678                         if (disposing)
 679                                 if (Parameters.Count > 0)
 680                                         foreach (OracleParameter parm in Parameters)
 681                                                 parm.FreeHandle ();
 682                         base.Dispose (disposing);
 683                 }
 684
 685                 #endregion // Methods
 686         }
 687 }