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                         foreach (OracleParameter p in Parameters)
 202                                 p.Bind (statement, Connection);
 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 GetOutParameters ()
 255                 {
 256                         if (Parameters.Count > 0) {
 257                                 foreach (OracleParameter parm in Parameters) {
 258                                         if (parm.Direction != ParameterDirection.Input) {
 259                                                 parm.Update (this);
 260                                         }
 261                                 }
 262                         }
 263                 }
 264
 265                 internal void CloseDataReader ()
 266                 {
 267                         GetOutParameters ();
 268
 269                         Connection.DataReader = null;
 270                         if ((behavior & CommandBehavior.CloseConnection) != 0)
 271                                 Connection.Close ();
 272                 }
 273
 274                 public OracleParameter CreateParameter ()
 275                 {
 276                         return new OracleParameter ();
 277                 }
 278
 279                 internal void DeriveParameters ()
 280                 {
 281                         if (commandType != CommandType.StoredProcedure)
 282                                 throw new InvalidOperationException (String.Format ("OracleCommandBuilder DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
 283
 284                         //OracleParameterCollection localParameters = new OracleParameterCollection (this);
 285
 286                         throw new NotImplementedException ();
 287                 }
 288
 289                 private int ExecuteNonQueryInternal (OciStatementHandle statement, bool useAutoCommit)
 290                 {
 291                         if (preparedStatement == null)
 292                                 PrepareStatement (statement);
 293
 294                         bool isNonQuery = IsNonQuery (statement);
 295
 296                         BindParameters (statement);
 297                         if (isNonQuery == true)
 298                                 statement.ExecuteNonQuery (useAutoCommit);
 299                         else
 300                                 statement.ExecuteQuery (false);
 301
 302                         GetOutParameters ();
 303
 304                         int rowsAffected = statement.GetAttributeInt32 (OciAttributeType.RowCount, ErrorHandle);
 305
 306                         return rowsAffected;
 307                 }
 308
 309                 public int ExecuteNonQuery ()
 310                 {
 311                         AssertConnectionIsOpen ();
 312                         AssertTransactionMatch ();
 313                         AssertCommandTextIsSet ();
 314                         bool useAutoCommit = false;
 315
 316                         if (Transaction != null)
 317                                 Transaction.AttachToServiceContext ();
 318                         else
 319                                 useAutoCommit = true;
 320
 321                         OciStatementHandle statement = GetStatementHandle ();
 322                         try {
 323                                 return ExecuteNonQueryInternal (statement, useAutoCommit);
 324                         }
 325                         finally {
 326                                 SafeDisposeHandle (statement);
 327                         }
 328                 }
 329
 330                 public int ExecuteOracleNonQuery (out OracleString rowid)
 331                 {
 332                         AssertConnectionIsOpen ();
 333                         AssertTransactionMatch ();
 334                         AssertCommandTextIsSet ();
 335                         bool useAutoCommit = false;
 336
 337                         if (Transaction != null)
 338                                 Transaction.AttachToServiceContext ();
 339                         else
 340                                 useAutoCommit = true;
 341
 342                         OciStatementHandle statement = GetStatementHandle ();
 343
 344                         try {
 345                                 int retval = ExecuteNonQueryInternal (statement, useAutoCommit);
 346
 347                                 OciRowIdDescriptor descriptor = (OciRowIdDescriptor) Environment.Allocate (OciHandleType.RowId);
 348                                 descriptor.SetHandle (statement.GetAttributeIntPtr (OciAttributeType.RowId, ErrorHandle));
 349
 350                                 rowid = new OracleString (descriptor.GetRowId (ErrorHandle));
 351
 352                                 return retval;
 353                         }
 354                         finally {
 355                                 SafeDisposeHandle (statement);
 356                         }
 357                 }
 358
 359                 [MonoTODO]
 360                 public object ExecuteOracleScalar ()
 361                 {
 362                         object output = DBNull.Value;
 363
 364                         AssertConnectionIsOpen ();
 365                         AssertTransactionMatch ();
 366                         AssertCommandTextIsSet ();
 367
 368                         if (Transaction != null)
 369                                 Transaction.AttachToServiceContext ();
 370
 371                         OciStatementHandle statement = GetStatementHandle ();
 372                         try {
 373                                 if (preparedStatement == null)
 374                                         PrepareStatement (statement);
 375
 376                                 bool isNonQuery = IsNonQuery (statement);
 377
 378                                 BindParameters (statement);
 379
 380                                 if (isNonQuery == true)
 381                                         ExecuteNonQueryInternal (statement, false);
 382                                 else {
 383                                         statement.ExecuteQuery (false);
 384
 385                                         if (statement.Fetch ()) {
 386                                                 OciDefineHandle defineHandle = (OciDefineHandle) statement.Values [0];
 387                                                 if (!defineHandle.IsNull)
 388                                                         output = defineHandle.GetOracleValue ();
 389                                                 switch (defineHandle.DataType) {
 390                                                 case OciDataType.Blob:
 391                                                 case OciDataType.Clob:
 392                                                         ((OracleLob) output).connection = Connection;
 393                                                         break;
 394                                                 }
 395                                         }
 396                                         GetOutParameters ();
 397                                 }
 398
 399                                 return output;
 400                         }
 401                         finally {
 402                                 SafeDisposeHandle (statement);
 403                         }
 404                 }
 405
 406                 private bool IsNonQuery (OciStatementHandle statementHandle) \r
 407                 {
 408                         // assumes Prepare() has been called prior to calling this function
 409
 410                         OciStatementType statementType = statementHandle.GetStatementType ();
 411                         if (statementType.Equals (OciStatementType.Select))
 412                                 return false;
 413
 414                         return true;
 415                 }
 416
 417                 public OracleDataReader ExecuteReader ()
 418                 {
 419                         return ExecuteReader (CommandBehavior.Default);
 420                 }
 421
 422                 public OracleDataReader ExecuteReader (CommandBehavior behavior)
 423                 {
 424                         AssertConnectionIsOpen ();
 425                         AssertTransactionMatch ();
 426                         AssertCommandTextIsSet ();
 427                         AssertNoDataReader ();
 428                         bool hasRows = false;
 429
 430                         this.behavior = behavior;
 431
 432                         if (Transaction != null)
 433                                 Transaction.AttachToServiceContext ();
 434
 435                         OciStatementHandle statement = GetStatementHandle ();
 436                         OracleDataReader rd = null;
 437
 438                         try     {
 439                                 if (preparedStatement == null)
 440                                         PrepareStatement (statement);
 441                                 else
 442                                         preparedStatement = null;       // OracleDataReader releases the statement handle
 443
 444                                 bool isNonQuery = IsNonQuery (statement);
 445
 446                                 BindParameters (statement);
 447
 448                                 if (isNonQuery)
 449                                         ExecuteNonQueryInternal (statement, false);
 450                                 else {
 451                                         if ((behavior & CommandBehavior.SchemaOnly) != 0)
 452                                                 statement.ExecuteQuery (true);
 453                                         else
 454                                                 hasRows = statement.ExecuteQuery (false);
 455                                 }
 456
 457                                 rd = new OracleDataReader (this, statement, hasRows, behavior);
 458                         }
 459                         finally {
 460                                 if (statement != null && rd == null)
 461                                         statement.Dispose();
 462                         }
 463
 464                         return rd;
 465                 }
 466
 467                 public object ExecuteScalar ()
 468                 {
 469                         object output = DBNull.Value;
 470
 471                         AssertConnectionIsOpen ();
 472                         AssertTransactionMatch ();
 473                         AssertCommandTextIsSet ();
 474
 475                         if (Transaction != null)
 476                                 Transaction.AttachToServiceContext ();
 477
 478                         OciStatementHandle statement = GetStatementHandle ();
 479                         try {
 480                                 if (preparedStatement == null)
 481                                         PrepareStatement (statement);
 482
 483                                 bool isNonQuery = IsNonQuery (statement);
 484
 485                                 BindParameters (statement);
 486
 487                                 if (isNonQuery == true)
 488                                         ExecuteNonQueryInternal (statement, false);
 489                                 else {
 490                                         statement.ExecuteQuery (false);
 491
 492                                         if (statement.Fetch ()) {
 493                                                 OciDefineHandle defineHandle = (OciDefineHandle) statement.Values [0];
 494                                                 if (defineHandle.IsNull)
 495                                                         output = DBNull.Value;
 496                                                 else {
 497                                                         switch (defineHandle.DataType) {
 498                                                         case OciDataType.Blob:
 499                                                         case OciDataType.Clob:
 500                                                                 OracleLob lob = (OracleLob) defineHandle.GetValue ();
 501                                                                 lob.connection = Connection;
 502                                                                 output = lob.Value;
 503                                                                 lob.Close ();
 504                                                                 break;
 505                                                         default:
 506                                                                 output = defineHandle.GetValue ();
 507                                                                 break;
 508                                                         }
 509                                                 }
 510                                         }
 511                                         else
 512                                                 output = DBNull.Value;
 513                                         GetOutParameters ();
 514                                 }
 515                         }
 516                         finally {
 517                                 SafeDisposeHandle (statement);
 518                         }
 519
 520                         return output;
 521                 }
 522
 523                 private OciStatementHandle GetStatementHandle ()
 524                 {
 525                         AssertConnectionIsOpen ();
 526                         if (preparedStatement != null)
 527                                 return preparedStatement;
 528
 529                         OciStatementHandle h = (OciStatementHandle) Connection.Environment.Allocate (OciHandleType.Statement);
 530                         h.ErrorHandle = Connection.ErrorHandle;
 531                         h.Service = Connection.ServiceContext;
 532                         h.Command = this;
 533                         return h;
 534                 }
 535
 536                 private void SafeDisposeHandle (OciStatementHandle h)
 537                 {
 538                         if (h != null && h != preparedStatement)
 539                                 h.Dispose();
 540                 }
 541
 542                 IDbDataParameter IDbCommand.CreateParameter ()
 543                 {
 544                         return CreateParameter ();
 545                 }
 546
 547                 IDataReader IDbCommand.ExecuteReader ()
 548                 {
 549                         return ExecuteReader ();
 550                 }
 551
 552                 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
 553                 {
 554                         return ExecuteReader (behavior);
 555                 }
 556
 557                 void PrepareStatement (OciStatementHandle statement)
 558                 {
 559                         if (commandType == CommandType.StoredProcedure) {
 560                                 StringBuilder sb = new StringBuilder ();
 561                                 if (Parameters.Count > 0)\r
 562                                         foreach (OracleParameter parm in Parameters) {\r
 563                                                 if (sb.Length > 0)\r
 564                                                         sb.Append (",");\r
 565                                                 sb.Append (":" + parm.ParameterName);\r
 566                                         }\r
 567 \r
 568                                 string sql = "call " + commandText + "(" + sb.ToString() + ")";\r
 569                                 statement.Prepare (sql);
 570                         }
 571                         else    // Text
 572                                 statement.Prepare (commandText);
 573                 }
 574
 575                 public void Prepare ()
 576                 {
 577                         AssertConnectionIsOpen ();
 578                         OciStatementHandle statement = GetStatementHandle ();
 579                         PrepareStatement (statement);
 580                         preparedStatement = statement;
 581                 }
 582
 583                 #endregion // Methods
 584         }
 585 }