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