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 #if NET_1_0 || ONLY_1_1
 137                 [DefaultValue (DEFAULT_COMMAND_TIMEOUT)]
 138 #endif
 139                 public
 140 #if NET_2_0
 141                 override
 142 #endif
 143                 int CommandTimeout {
 144                         get { return timeout; }
 145                         set {
 146                                 if (value < 0)
 147 #if NET_2_0
 148                                         throw new ArgumentException ("The property value assigned is less than 0.",
 149                                                 "CommandTimeout");
 150 #else
 151                                         throw new ArgumentException ("The property value assigned is less than 0.");
 152 #endif
 153                                 timeout = value;
 154                         }
 155                 }
 156
 157                 [OdbcCategory ("Data")]
 158                 [DefaultValue ("Text")]
 159                 [OdbcDescriptionAttribute ("How to interpret the CommandText")]
 160                 [RefreshPropertiesAttribute (RefreshProperties.All)]
 161                 public
 162 #if NET_2_0
 163                 override
 164 #endif
 165                 CommandType CommandType {
 166                         get { return commandType; }
 167                         set {
 168                                 ExceptionHelper.CheckEnumValue (typeof (CommandType), value);
 169                                 commandType = value;
 170                         }
 171                 }
 172
 173 #if ONLY_1_1
 174                 [OdbcCategory ("Behavior")]
 175                 [OdbcDescriptionAttribute ("Connection used by the command")]
 176                 [DefaultValue (null)]
 177                 [EditorAttribute ("Microsoft.VSDesigner.Data.Design.DbConnectionEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
 178                 public OdbcConnection Connection {
 179                         get { return connection; }
 180                         set { connection = value; }
 181                 }
 182 #endif // ONLY_1_1
 183
 184 #if NET_2_0
 185                 [DefaultValue (null)]
 186                 [EditorAttribute ("Microsoft.VSDesigner.Data.Design.DbConnectionEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
 187                 public new OdbcConnection Connection {
 188                         get { return DbConnection as OdbcConnection; }
 189                         set { DbConnection = value; }
 190                 }
 191 #endif // NET_2_0
 192
 193                 [BrowsableAttribute (false)]
 194                 [DesignOnlyAttribute (true)]
 195                 [DefaultValue (true)]
 196 #if NET_2_0
 197                 [EditorBrowsable (EditorBrowsableState.Never)]
 198 #endif
 199                 public
 200 #if NET_2_0
 201                 override
 202 #endif
 203                 bool DesignTimeVisible {
 204                         get { return designTimeVisible; }
 205                         set { designTimeVisible = value; }
 206                 }
 207
 208                 [OdbcCategory ("Data")]
 209                 [OdbcDescriptionAttribute ("The parameters collection")]
 210                 [DesignerSerializationVisibilityAttribute (DesignerSerializationVisibility.Content)]
 211                 public
 212 #if NET_2_0
 213                 new
 214 #endif // NET_2_0
 215                 OdbcParameterCollection Parameters {
 216                         get {
 217 #if ONLY_1_1
 218                                 return _parameters;
 219 #else
 220                                 return base.Parameters as OdbcParameterCollection;
 221 #endif // ONLY_1_1
 222                         }
 223                 }
 224
 225                 [BrowsableAttribute (false)]
 226                 [OdbcDescriptionAttribute ("The transaction used by the command")]
 227                 [DesignerSerializationVisibilityAttribute (DesignerSerializationVisibility.Hidden)]
 228                 public
 229 #if NET_2_0
 230                 new
 231 #endif // NET_2_0
 232                 OdbcTransaction Transaction {
 233                         get { return transaction; }
 234                         set { transaction = value; }
 235                 }
 236
 237                 [OdbcCategory ("Behavior")]
 238                 [DefaultValue (UpdateRowSource.Both)]
 239                 [OdbcDescriptionAttribute ("When used by a DataAdapter.Update, how command results are applied to the current DataRow")]
 240                 public
 241 #if NET_2_0
 242                 override
 243 #endif
 244                 UpdateRowSource UpdatedRowSource {
 245                         get { return updateRowSource; }
 246                         set {
 247                                 ExceptionHelper.CheckEnumValue (typeof (UpdateRowSource), value);
 248                                 updateRowSource = value;
 249                         }
 250                 }
 251
 252 #if NET_2_0
 253                 protected override DbConnection DbConnection {
 254                         get { return connection; }
 255                         set { connection = (OdbcConnection) value;}
 256                 }
 257
 258 #endif // NET_2_0
 259
 260 #if ONLY_1_1
 261                 IDbConnection IDbCommand.Connection {
 262                         get { return Connection; }
 263                         set { Connection = (OdbcConnection) value; }
 264                 }
 265
 266                 IDataParameterCollection IDbCommand.Parameters {
 267                         get { return Parameters; }
 268                 }
 269 #else
 270                 protected override DbParameterCollection DbParameterCollection {
 271                         get { return _parameters as DbParameterCollection;}
 272                 }
 273 #endif // NET_2_0
 274
 275 #if ONLY_1_1
 276                 IDbTransaction IDbCommand.Transaction {
 277                         get { return (IDbTransaction) Transaction; }
 278                         set {
 279                                 if (value is OdbcTransaction) {
 280                                         Transaction = (OdbcTransaction) value;
 281                                 } else {
 282                                         throw new ArgumentException ();
 283                                 }
 284                         }
 285                 }
 286 #else
 287                 protected override DbTransaction DbTransaction {
 288                         get { return transaction; }
 289                         set { transaction = (OdbcTransaction) value; }
 290                 }
 291 #endif // ONLY_1_1
 292
 293                 #endregion // Properties
 294
 295                 #region Methods
 296
 297                 public
 298 #if NET_2_0
 299                 override
 300 #endif // NET_2_0
 301                 void Cancel ()
 302                 {
 303                         if (hstmt != IntPtr.Zero) {
 304                                 OdbcReturn Ret = libodbc.SQLCancel (hstmt);
 305                                 if (Ret != OdbcReturn.Success && Ret != OdbcReturn.SuccessWithInfo)
 306                                         throw connection.CreateOdbcException (OdbcHandleType.Stmt, hstmt);
 307                         } else
 308                                 throw new InvalidOperationException ();
 309                 }
 310
 311 #if ONLY_1_1
 312                 IDbDataParameter IDbCommand.CreateParameter ()
 313                 {
 314                         return CreateParameter ();
 315                 }
 316
 317 #else
 318                 protected override DbParameter CreateDbParameter ()
 319                 {
 320                         return CreateParameter ();
 321                 }
 322 #endif // ONLY_1_1
 323
 324                 public new OdbcParameter CreateParameter ()
 325                 {
 326                         return new OdbcParameter ();
 327                 }
 328
 329                 internal void Unlink ()
 330                 {
 331                         if (disposed)
 332                                 return;
 333
 334                         FreeStatement (false);
 335                 }
 336
 337                 protected override void Dispose (bool disposing)
 338                 {
 339                         if (disposed)
 340                                 return;
 341
 342                         FreeStatement (); // free handles
 343 #if NET_2_0
 344                         CommandText = null;
 345 #endif
 346                         Connection = null;
 347                         Transaction = null;
 348                         Parameters.Clear ();
 349                         disposed = true;
 350                 }
 351
 352                 private IntPtr ReAllocStatment ()
 353                 {
 354                         OdbcReturn ret;
 355
 356                         if (hstmt != IntPtr.Zero)
 357                                 // Free the existing hstmt.  Also unlinks from the connection.
 358                                 FreeStatement ();
 359                         // Link this command to the connection.  The hstmt created below
 360                         // only remains valid while generation == Connection.generation.
 361                         generation = Connection.Link (this);
 362                         ret = libodbc.SQLAllocHandle (OdbcHandleType.Stmt, Connection.hDbc, ref hstmt);
 363                         if (ret != OdbcReturn.Success && ret != OdbcReturn.SuccessWithInfo)
 364                                 throw connection.CreateOdbcException (OdbcHandleType.Dbc, Connection.hDbc);
 365                         disposed = false;
 366                         return hstmt;
 367                 }
 368
 369                 void FreeStatement ()
 370                 {
 371                         FreeStatement (true);
 372                 }
 373
 374                 private void FreeStatement (bool unlink)
 375                 {
 376                         prepared = false;
 377
 378                         if (hstmt == IntPtr.Zero)
 379                                 return;
 380
 381                         // Normally the command is unlinked from the connection, but during
 382                         // OdbcConnection.Close() this would be pointless and (quadratically)
 383                         // slow.
 384                         if (unlink)
 385                                 Connection.Unlink (this);
 386
 387                         // Serialize with respect to the connection's own destruction
 388                         lock(Connection) {
 389                                 // If the connection has already called SQLDisconnect then hstmt
 390                                 // may have already been freed, in which case it is not safe to
 391                                 // use.  Thus the generation check.
 392                                 if(Connection.Generation == generation) {
 393                                         // free previously allocated handle.
 394                                         OdbcReturn ret = libodbc.SQLFreeStmt (hstmt, libodbc.SQLFreeStmtOptions.Close);
 395                                         if ((ret!=OdbcReturn.Success) && (ret!=OdbcReturn.SuccessWithInfo))
 396                                                 throw connection.CreateOdbcException (OdbcHandleType.Stmt, hstmt);
 397
 398                                         ret = libodbc.SQLFreeHandle ((ushort) OdbcHandleType.Stmt, hstmt);
 399                                         if (ret != OdbcReturn.Success && ret != OdbcReturn.SuccessWithInfo)
 400                                                 throw connection.CreateOdbcException (OdbcHandleType.Stmt, hstmt);
 401                                 }
 402                                 hstmt = IntPtr.Zero;
 403                         }
 404                 }
 405
 406                 private void ExecSQL (CommandBehavior behavior, bool createReader, string sql)
 407                 {
 408                         OdbcReturn ret;
 409
 410                         if (!prepared && Parameters.Count == 0) {
 411                                 ReAllocStatment ();
 412
 413                                 ret = libodbc.SQLExecDirect (hstmt, sql, libodbc.SQL_NTS);
 414                                 if (ret != OdbcReturn.Success && ret != OdbcReturn.SuccessWithInfo && ret != OdbcReturn.NoData)
 415                                         throw connection.CreateOdbcException (OdbcHandleType.Stmt, hstmt);
 416                                 return;
 417                         }
 418
 419                         if (!prepared)
 420                                 Prepare();
 421
 422                         BindParameters ();
 423                         ret = libodbc.SQLExecute (hstmt);
 424                         if (ret != OdbcReturn.Success && ret != OdbcReturn.SuccessWithInfo)
 425                                 throw connection.CreateOdbcException (OdbcHandleType.Stmt, hstmt);
 426                 }
 427
 428                 internal void FreeIfNotPrepared ()
 429                 {
 430                         if (! prepared)
 431                                 FreeStatement ();
 432                 }
 433
 434                 public
 435 #if NET_2_0
 436                 override
 437 #endif // NET_2_0
 438                 int ExecuteNonQuery ()
 439                 {
 440                         return ExecuteNonQuery ("ExecuteNonQuery", CommandBehavior.Default, false);
 441                 }
 442
 443                 private int ExecuteNonQuery (string method, CommandBehavior behavior, bool createReader)
 444                 {
 445                         int records = 0;
 446                         if (Connection == null)
 447                                 throw new InvalidOperationException (string.Format (
 448                                         "{0}: Connection is not set.", method));
 449                         if (Connection.State == ConnectionState.Closed)
 450                                 throw new InvalidOperationException (string.Format (
 451                                         "{0}: Connection state is closed", method));
 452                         if (CommandText.Length == 0)
 453                                 throw new InvalidOperationException (string.Format (
 454                                         "{0}: CommandText is not set.", method));
 455
 456                         ExecSQL (behavior, createReader, CommandText);
 457
 458                         // .NET documentation says that except for INSERT, UPDATE and
 459                         // DELETE  where the return value is the number of rows affected
 460                         // for the rest of the commands the return value is -1.
 461                         if ((CommandText.ToUpper().IndexOf("UPDATE")!=-1) ||
 462                             (CommandText.ToUpper().IndexOf("INSERT")!=-1) ||
 463                             (CommandText.ToUpper().IndexOf("DELETE")!=-1)) {
 464                                 int numrows = 0;
 465                                 libodbc.SQLRowCount (hstmt, ref numrows);
 466                                 records = numrows;
 467                         } else
 468                                 records = -1;
 469
 470                         if (!createReader && !prepared)
 471                                 FreeStatement ();
 472
 473                         return records;
 474                 }
 475
 476                 public
 477 #if NET_2_0
 478                 override
 479 #endif // NET_2_0
 480                 void Prepare()
 481                 {
 482                         ReAllocStatment ();
 483
 484                         OdbcReturn ret;
 485                         ret = libodbc.SQLPrepare(hstmt, CommandText, CommandText.Length);
 486                         if ((ret!=OdbcReturn.Success) && (ret!=OdbcReturn.SuccessWithInfo))
 487                                 throw connection.CreateOdbcException (OdbcHandleType.Stmt, hstmt);
 488                         prepared = true;
 489                 }
 490
 491                 private void BindParameters ()
 492                 {
 493                         int i = 1;
 494                         foreach (OdbcParameter p in Parameters) {
 495                                 p.Bind (this, hstmt, i);
 496                                 p.CopyValue ();
 497                                 i++;
 498                         }
 499                 }
 500
 501                 public
 502 #if NET_2_0
 503                 new
 504 #endif // NET_2_0
 505                 OdbcDataReader ExecuteReader ()
 506                 {
 507                         return ExecuteReader (CommandBehavior.Default);
 508                 }
 509
 510 #if ONLY_1_1
 511                 IDataReader IDbCommand.ExecuteReader ()
 512                 {
 513                         return ExecuteReader ();
 514                 }
 515 #else
 516                 protected override DbDataReader ExecuteDbDataReader (CommandBehavior behavior)
 517                 {
 518                         return ExecuteReader (behavior);
 519                 }
 520 #endif // ONLY_1_1
 521
 522                 public
 523 #if NET_2_0
 524                 new
 525 #endif // NET_2_0
 526                 OdbcDataReader ExecuteReader (CommandBehavior behavior)
 527                 {
 528                         return ExecuteReader ("ExecuteReader", behavior);
 529                 }
 530
 531                 OdbcDataReader ExecuteReader (string method, CommandBehavior behavior)
 532                 {
 533                         int recordsAffected = ExecuteNonQuery (method, behavior, true);
 534                         OdbcDataReader dataReader = new OdbcDataReader (this, behavior, recordsAffected);
 535                         return dataReader;
 536                 }
 537
 538 #if ONLY_1_1
 539                 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
 540                 {
 541                         return ExecuteReader (behavior);
 542                 }
 543 #endif // ONLY_1_1
 544
 545                 public
 546 #if NET_2_0
 547                 override
 548 #endif
 549                 object ExecuteScalar ()
 550                 {
 551                         object val = null;
 552                         OdbcDataReader reader = ExecuteReader ("ExecuteScalar",
 553                                 CommandBehavior.Default);
 554                         try {
 555                                 if (reader.Read ())
 556                                         val = reader [0];
 557                         } finally {
 558                                 reader.Close ();
 559                         }
 560                         return val;
 561                 }
 562
 563                 object ICloneable.Clone ()
 564                 {
 565                         OdbcCommand command = new OdbcCommand ();
 566                         command.CommandText = this.CommandText;
 567                         command.CommandTimeout = this.CommandTimeout;
 568                         command.CommandType = this.CommandType;
 569                         command.Connection = this.Connection;
 570                         command.DesignTimeVisible = this.DesignTimeVisible;
 571                         foreach (OdbcParameter parameter in this.Parameters)
 572                                 command.Parameters.Add (parameter);
 573                         command.Transaction = this.Transaction;
 574                         return command;
 575                 }
 576
 577                 public void ResetCommandTimeout ()
 578                 {
 579                         CommandTimeout = DEFAULT_COMMAND_TIMEOUT;
 580                 }
 581
 582                 #endregion
 583         }
 584 }