mcs/class/System.Data/System.Data.Odbc/OdbcCommand.cs

   1 //
   2 // System.Data.Odbc.OdbcCommand
   3 //
   4 // Authors:
   5 //   Brian Ritchie (brianlritchie@hotmail.com)
   6 //
   7 // Copyright (C) Brian Ritchie, 2002
   8 //
   9
  10 //
  11 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
  12 //
  13 // Permission is hereby granted, free of charge, to any person obtaining
  14 // a copy of this software and associated documentation files (the
  15 // "Software"), to deal in the Software without restriction, including
  16 // without limitation the rights to use, copy, modify, merge, publish,
  17 // distribute, sublicense, and/or sell copies of the Software, and to
  18 // permit persons to whom the Software is furnished to do so, subject to
  19 // the following conditions:
  20 //
  21 // The above copyright notice and this permission notice shall be
  22 // included in all copies or substantial portions of the Software.
  23 //
  24 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  25 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  26 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  27 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  28 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  29 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  30 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  31 //
  32
  33 using System;
  34 using System.ComponentModel;
  35 using System.Data;
  36 using System.Data.Common;
  37 using System.Collections;
  38 using System.Runtime.InteropServices;
  39
  40 namespace System.Data.Odbc
  41 {
  42         /// <summary>
  43         /// Represents an SQL statement or stored procedure to execute against a data source.
  44         /// </summary>
  45         [DesignerAttribute ("Microsoft.VSDesigner.Data.VS.OdbcCommandDesigner, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.ComponentModel.Design.IDesigner")]
  46         [ToolboxItemAttribute ("System.Drawing.Design.ToolboxItem, "+ Consts.AssemblySystem_Drawing)]
  47 #if NET_2_0
  48         [DefaultEvent ("RecordsAffected")]
  49         public sealed class OdbcCommand : DbCommand, ICloneable
  50 #else
  51         public sealed class OdbcCommand : Component, ICloneable, IDbCommand
  52 #endif //NET_2_0
  53         {
  54                 #region Fields
  55
  56                 const int DEFAULT_COMMAND_TIMEOUT = 30;
  57
  58                 string commandText;
  59                 int timeout;
  60                 CommandType commandType;
  61                 UpdateRowSource updateRowSource;
  62
  63                 OdbcConnection connection;
  64                 OdbcTransaction transaction;
  65                 OdbcParameterCollection _parameters;
  66
  67                 bool designTimeVisible;
  68                 bool prepared;
  69                 IntPtr hstmt = IntPtr.Zero;
  70                 object generation = null; // validity of hstmt
  71
  72                 bool disposed;
  73
  74                 #endregion // Fields
  75
  76                 #region Constructors
  77
  78                 public OdbcCommand ()
  79                 {
  80                         timeout = DEFAULT_COMMAND_TIMEOUT;
  81                         commandType = CommandType.Text;
  82                         _parameters = new OdbcParameterCollection ();
  83                         designTimeVisible = true;
  84                         updateRowSource = UpdateRowSource.Both;
  85                 }
  86
  87                 public OdbcCommand (string cmdText) : this ()
  88                 {
  89                         commandText = cmdText;
  90                 }
  91
  92                 public OdbcCommand (string cmdText, OdbcConnection connection)
  93                         : this (cmdText)
  94                 {
  95                         Connection = connection;
  96                 }
  97
  98                 public OdbcCommand (string cmdText, OdbcConnection connection,
  99                                     OdbcTransaction transaction) : this (cmdText, connection)
 100                 {
 101                         this.Transaction = transaction;
 102                 }
 103
 104                 #endregion // Constructors
 105
 106                 #region Properties
 107
 108                 internal IntPtr hStmt {
 109                         get { return hstmt; }
 110                 }
 111
 112                 [OdbcCategory ("Data")]
 113                 [DefaultValue ("")]
 114                 [OdbcDescriptionAttribute ("Command text to execute")]
 115                 [EditorAttribute ("Microsoft.VSDesigner.Data.Odbc.Design.OdbcCommandTextEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
 116                 [RefreshPropertiesAttribute (RefreshProperties.All)]
 117                 public
 118 #if NET_2_0
 119                 override
 120 #endif
 121                 string CommandText {
 122                         get {
 123                                 if (commandText == null)
 124                                         return string.Empty;
 125                                 return commandText;
 126                         }
 127                         set {
 128 #if NET_2_0
 129                                 prepared = false;
 130 #endif
 131                                 commandText = value;
 132                         }
 133                 }
 134
 135                 [OdbcDescriptionAttribute ("Time to wait for command to execute")]
 136                 public override
 137                 int CommandTimeout {
 138                         get { return timeout; }
 139                         set {
 140                                 if (value < 0)
 141                                         throw new ArgumentException ("The property value assigned is less than 0.",
 142                                                 "CommandTimeout");
 143                                 timeout = value;
 144                         }
 145                 }
 146
 147                 [OdbcCategory ("Data")]
 148                 [DefaultValue ("Text")]
 149                 [OdbcDescriptionAttribute ("How to interpret the CommandText")]
 150                 [RefreshPropertiesAttribute (RefreshProperties.All)]
 151                 public
 152 #if NET_2_0
 153                 override
 154 #endif
 155                 CommandType CommandType {
 156                         get { return commandType; }
 157                         set {
 158                                 ExceptionHelper.CheckEnumValue (typeof (CommandType), value);
 159                                 commandType = value;
 160                         }
 161                 }
 162
 163 #if ONLY_1_1
 164                 [OdbcCategory ("Behavior")]
 165                 [OdbcDescriptionAttribute ("Connection used by the command")]
 166                 [DefaultValue (null)]
 167                 [EditorAttribute ("Microsoft.VSDesigner.Data.Design.DbConnectionEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
 168                 public OdbcConnection Connection {
 169                         get { return connection; }
 170                         set { connection = value; }
 171                 }
 172 #endif // ONLY_1_1
 173
 174 #if NET_2_0
 175                 [DefaultValue (null)]
 176                 [EditorAttribute ("Microsoft.VSDesigner.Data.Design.DbConnectionEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
 177                 public new OdbcConnection Connection {
 178                         get { return DbConnection as OdbcConnection; }
 179                         set { DbConnection = value; }
 180                 }
 181 #endif // NET_2_0
 182
 183                 [BrowsableAttribute (false)]
 184                 [DesignOnlyAttribute (true)]
 185                 [DefaultValue (true)]
 186 #if NET_2_0
 187                 [EditorBrowsable (EditorBrowsableState.Never)]
 188 #endif
 189                 public
 190 #if NET_2_0
 191                 override
 192 #endif
 193                 bool DesignTimeVisible {
 194                         get { return designTimeVisible; }
 195                         set { designTimeVisible = value; }
 196                 }
 197
 198                 [OdbcCategory ("Data")]
 199                 [OdbcDescriptionAttribute ("The parameters collection")]
 200                 [DesignerSerializationVisibilityAttribute (DesignerSerializationVisibility.Content)]
 201                 public
 202 #if NET_2_0
 203                 new
 204 #endif // NET_2_0
 205                 OdbcParameterCollection Parameters {
 206                         get {
 207 #if ONLY_1_1
 208                                 return _parameters;
 209 #else
 210                                 return base.Parameters as OdbcParameterCollection;
 211 #endif // ONLY_1_1
 212                         }
 213                 }
 214
 215                 [BrowsableAttribute (false)]
 216                 [OdbcDescriptionAttribute ("The transaction used by the command")]
 217                 [DesignerSerializationVisibilityAttribute (DesignerSerializationVisibility.Hidden)]
 218                 public
 219 #if NET_2_0
 220                 new
 221 #endif // NET_2_0
 222                 OdbcTransaction Transaction {
 223                         get { return transaction; }
 224                         set { transaction = value; }
 225                 }
 226
 227                 [OdbcCategory ("Behavior")]
 228                 [DefaultValue (UpdateRowSource.Both)]
 229                 [OdbcDescriptionAttribute ("When used by a DataAdapter.Update, how command results are applied to the current DataRow")]
 230                 public
 231 #if NET_2_0
 232                 override
 233 #endif
 234                 UpdateRowSource UpdatedRowSource {
 235                         get { return updateRowSource; }
 236                         set {
 237                                 ExceptionHelper.CheckEnumValue (typeof (UpdateRowSource), value);
 238                                 updateRowSource = value;
 239                         }
 240                 }
 241
 242 #if NET_2_0
 243                 protected override DbConnection DbConnection {
 244                         get { return connection; }
 245                         set { connection = (OdbcConnection) value;}
 246                 }
 247
 248 #endif // NET_2_0
 249
 250 #if ONLY_1_1
 251                 IDbConnection IDbCommand.Connection {
 252                         get { return Connection; }
 253                         set { Connection = (OdbcConnection) value; }
 254                 }
 255
 256                 IDataParameterCollection IDbCommand.Parameters {
 257                         get { return Parameters; }
 258                 }
 259 #else
 260                 protected override DbParameterCollection DbParameterCollection {
 261                         get { return _parameters as DbParameterCollection;}
 262                 }
 263 #endif // NET_2_0
 264
 265 #if ONLY_1_1
 266                 IDbTransaction IDbCommand.Transaction {
 267                         get { return (IDbTransaction) Transaction; }
 268                         set {
 269                                 if (value is OdbcTransaction) {
 270                                         Transaction = (OdbcTransaction) value;
 271                                 } else {
 272                                         throw new ArgumentException ();
 273                                 }
 274                         }
 275                 }
 276 #else
 277                 protected override DbTransaction DbTransaction {
 278                         get { return transaction; }
 279                         set { transaction = (OdbcTransaction) value; }
 280                 }
 281 #endif // ONLY_1_1
 282
 283                 #endregion // Properties
 284
 285                 #region Methods
 286
 287                 public
 288 #if NET_2_0
 289                 override
 290 #endif // NET_2_0
 291                 void Cancel ()
 292                 {
 293                         if (hstmt != IntPtr.Zero) {
 294                                 OdbcReturn Ret = libodbc.SQLCancel (hstmt);
 295                                 if (Ret != OdbcReturn.Success && Ret != OdbcReturn.SuccessWithInfo)
 296                                         throw connection.CreateOdbcException (OdbcHandleType.Stmt, hstmt);
 297                         } else
 298                                 throw new InvalidOperationException ();
 299                 }
 300
 301 #if ONLY_1_1
 302                 IDbDataParameter IDbCommand.CreateParameter ()
 303                 {
 304                         return CreateParameter ();
 305                 }
 306
 307 #else
 308                 protected override DbParameter CreateDbParameter ()
 309                 {
 310                         return CreateParameter ();
 311                 }
 312 #endif // ONLY_1_1
 313
 314                 public new OdbcParameter CreateParameter ()
 315                 {
 316                         return new OdbcParameter ();
 317                 }
 318
 319                 internal void Unlink ()
 320                 {
 321                         if (disposed)
 322                                 return;
 323
 324                         FreeStatement (false);
 325                 }
 326
 327                 protected override void Dispose (bool disposing)
 328                 {
 329                         if (disposed)
 330                                 return;
 331
 332                         FreeStatement (); // free handles
 333 #if NET_2_0
 334                         CommandText = null;
 335 #endif
 336                         Connection = null;
 337                         Transaction = null;
 338                         Parameters.Clear ();
 339                         disposed = true;
 340                 }
 341
 342                 private IntPtr ReAllocStatment ()
 343                 {
 344                         OdbcReturn ret;
 345
 346                         if (hstmt != IntPtr.Zero)
 347                                 // Free the existing hstmt.  Also unlinks from the connection.
 348                                 FreeStatement ();
 349                         // Link this command to the connection.  The hstmt created below
 350                         // only remains valid while generation == Connection.generation.
 351                         generation = Connection.Link (this);
 352                         ret = libodbc.SQLAllocHandle (OdbcHandleType.Stmt, Connection.hDbc, ref hstmt);
 353                         if (ret != OdbcReturn.Success && ret != OdbcReturn.SuccessWithInfo)
 354                                 throw connection.CreateOdbcException (OdbcHandleType.Dbc, Connection.hDbc);
 355                         disposed = false;
 356                         return hstmt;
 357                 }
 358
 359                 void FreeStatement ()
 360                 {
 361                         FreeStatement (true);
 362                 }
 363
 364                 private void FreeStatement (bool unlink)
 365                 {
 366                         prepared = false;
 367
 368                         if (hstmt == IntPtr.Zero)
 369                                 return;
 370
 371                         // Normally the command is unlinked from the connection, but during
 372                         // OdbcConnection.Close() this would be pointless and (quadratically)
 373                         // slow.
 374                         if (unlink)
 375                                 Connection.Unlink (this);
 376
 377                         // Serialize with respect to the connection's own destruction
 378                         lock(Connection) {
 379                                 // If the connection has already called SQLDisconnect then hstmt
 380                                 // may have already been freed, in which case it is not safe to
 381                                 // use.  Thus the generation check.
 382                                 if(Connection.Generation == generation) {
 383                                         // free previously allocated handle.
 384                                         OdbcReturn ret = libodbc.SQLFreeStmt (hstmt, libodbc.SQLFreeStmtOptions.Close);
 385                                         if ((ret!=OdbcReturn.Success) && (ret!=OdbcReturn.SuccessWithInfo))
 386                                                 throw connection.CreateOdbcException (OdbcHandleType.Stmt, hstmt);
 387
 388                                         ret = libodbc.SQLFreeHandle ((ushort) OdbcHandleType.Stmt, hstmt);
 389                                         if (ret != OdbcReturn.Success && ret != OdbcReturn.SuccessWithInfo)
 390                                                 throw connection.CreateOdbcException (OdbcHandleType.Stmt, hstmt);
 391                                 }
 392                                 hstmt = IntPtr.Zero;
 393                         }
 394                 }
 395
 396                 private void ExecSQL (CommandBehavior behavior, bool createReader, string sql)
 397                 {
 398                         OdbcReturn ret;
 399
 400                         if (!prepared && Parameters.Count == 0) {
 401                                 ReAllocStatment ();
 402
 403                                 ret = libodbc.SQLExecDirect (hstmt, sql, libodbc.SQL_NTS);
 404                                 if (ret != OdbcReturn.Success && ret != OdbcReturn.SuccessWithInfo && ret != OdbcReturn.NoData)
 405                                         throw connection.CreateOdbcException (OdbcHandleType.Stmt, hstmt);
 406                                 return;
 407                         }
 408
 409                         if (!prepared)
 410                                 Prepare();
 411
 412                         BindParameters ();
 413                         ret = libodbc.SQLExecute (hstmt);
 414                         if (ret != OdbcReturn.Success && ret != OdbcReturn.SuccessWithInfo)
 415                                 throw connection.CreateOdbcException (OdbcHandleType.Stmt, hstmt);
 416                 }
 417
 418                 internal void FreeIfNotPrepared ()
 419                 {
 420                         if (! prepared)
 421                                 FreeStatement ();
 422                 }
 423
 424                 public
 425 #if NET_2_0
 426                 override
 427 #endif // NET_2_0
 428                 int ExecuteNonQuery ()
 429                 {
 430                         return ExecuteNonQuery ("ExecuteNonQuery", CommandBehavior.Default, false);
 431                 }
 432
 433                 private int ExecuteNonQuery (string method, CommandBehavior behavior, bool createReader)
 434                 {
 435                         int records = 0;
 436                         if (Connection == null)
 437                                 throw new InvalidOperationException (string.Format (
 438                                         "{0}: Connection is not set.", method));
 439                         if (Connection.State == ConnectionState.Closed)
 440                                 throw new InvalidOperationException (string.Format (
 441                                         "{0}: Connection state is closed", method));
 442                         if (CommandText.Length == 0)
 443                                 throw new InvalidOperationException (string.Format (
 444                                         "{0}: CommandText is not set.", method));
 445
 446                         ExecSQL (behavior, createReader, CommandText);
 447
 448                         // .NET documentation says that except for INSERT, UPDATE and
 449                         // DELETE  where the return value is the number of rows affected
 450                         // for the rest of the commands the return value is -1.
 451                         if ((CommandText.ToUpper().IndexOf("UPDATE")!=-1) ||
 452                             (CommandText.ToUpper().IndexOf("INSERT")!=-1) ||
 453                             (CommandText.ToUpper().IndexOf("DELETE")!=-1)) {
 454                                 int numrows = 0;
 455                                 libodbc.SQLRowCount (hstmt, ref numrows);
 456                                 records = numrows;
 457                         } else
 458                                 records = -1;
 459
 460                         if (!createReader && !prepared)
 461                                 FreeStatement ();
 462
 463                         return records;
 464                 }
 465
 466                 public
 467 #if NET_2_0
 468                 override
 469 #endif // NET_2_0
 470                 void Prepare()
 471                 {
 472                         ReAllocStatment ();
 473
 474                         OdbcReturn ret;
 475                         ret = libodbc.SQLPrepare(hstmt, CommandText, CommandText.Length);
 476                         if ((ret!=OdbcReturn.Success) && (ret!=OdbcReturn.SuccessWithInfo))
 477                                 throw connection.CreateOdbcException (OdbcHandleType.Stmt, hstmt);
 478                         prepared = true;
 479                 }
 480
 481                 private void BindParameters ()
 482                 {
 483                         int i = 1;
 484                         foreach (OdbcParameter p in Parameters) {
 485                                 p.Bind (this, hstmt, i);
 486                                 p.CopyValue ();
 487                                 i++;
 488                         }
 489                 }
 490
 491                 public
 492 #if NET_2_0
 493                 new
 494 #endif // NET_2_0
 495                 OdbcDataReader ExecuteReader ()
 496                 {
 497                         return ExecuteReader (CommandBehavior.Default);
 498                 }
 499
 500 #if ONLY_1_1
 501                 IDataReader IDbCommand.ExecuteReader ()
 502                 {
 503                         return ExecuteReader ();
 504                 }
 505 #else
 506                 protected override DbDataReader ExecuteDbDataReader (CommandBehavior behavior)
 507                 {
 508                         return ExecuteReader (behavior);
 509                 }
 510 #endif // ONLY_1_1
 511
 512                 public
 513 #if NET_2_0
 514                 new
 515 #endif // NET_2_0
 516                 OdbcDataReader ExecuteReader (CommandBehavior behavior)
 517                 {
 518                         return ExecuteReader ("ExecuteReader", behavior);
 519                 }
 520
 521                 OdbcDataReader ExecuteReader (string method, CommandBehavior behavior)
 522                 {
 523                         int recordsAffected = ExecuteNonQuery (method, behavior, true);
 524                         OdbcDataReader dataReader = new OdbcDataReader (this, behavior, recordsAffected);
 525                         return dataReader;
 526                 }
 527
 528 #if ONLY_1_1
 529                 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
 530                 {
 531                         return ExecuteReader (behavior);
 532                 }
 533 #endif // ONLY_1_1
 534
 535                 public
 536 #if NET_2_0
 537                 override
 538 #endif
 539                 object ExecuteScalar ()
 540                 {
 541                         object val = null;
 542                         OdbcDataReader reader = ExecuteReader ("ExecuteScalar",
 543                                 CommandBehavior.Default);
 544                         try {
 545                                 if (reader.Read ())
 546                                         val = reader [0];
 547                         } finally {
 548                                 reader.Close ();
 549                         }
 550                         return val;
 551                 }
 552
 553                 object ICloneable.Clone ()
 554                 {
 555                         OdbcCommand command = new OdbcCommand ();
 556                         command.CommandText = this.CommandText;
 557                         command.CommandTimeout = this.CommandTimeout;
 558                         command.CommandType = this.CommandType;
 559                         command.Connection = this.Connection;
 560                         command.DesignTimeVisible = this.DesignTimeVisible;
 561                         foreach (OdbcParameter parameter in this.Parameters)
 562                                 command.Parameters.Add (parameter);
 563                         command.Transaction = this.Transaction;
 564                         return command;
 565                 }
 566
 567                 public void ResetCommandTimeout ()
 568                 {
 569                         CommandTimeout = DEFAULT_COMMAND_TIMEOUT;
 570                 }
 571
 572                 #endregion
 573         }
 574 }