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         public sealed class OdbcCommand : DbCommand, ICloneable
  49 #else
  50         public sealed class OdbcCommand : Component, ICloneable, IDbCommand
  51 #endif //NET_2_0
  52         {
  53                 #region Fields
  54
  55                 string commandText;
  56                 int timeout;
  57                 CommandType commandType;
  58                 UpdateRowSource updateRowSource = UpdateRowSource.Both;
  59
  60                 OdbcConnection connection;
  61                 OdbcTransaction transaction;
  62                 OdbcParameterCollection _parameters;
  63
  64                 bool designTimeVisible;
  65                 bool prepared=false;
  66                 IntPtr hstmt = IntPtr.Zero;
  67
  68                 bool disposed = false;
  69
  70                 #endregion // Fields
  71
  72                 #region Constructors
  73
  74                 public OdbcCommand ()
  75                 {
  76                         this.CommandText = String.Empty;
  77                         this.CommandTimeout = 30; // default timeout
  78                         this.CommandType = CommandType.Text;
  79                         Connection = null;
  80                         _parameters = new OdbcParameterCollection ();
  81                         Transaction = null;
  82                         designTimeVisible = false;
  83 #if ONLY_1_1
  84                         updateRowSource = UpdateRowSource.Both;
  85 #endif // ONLY_1_1
  86                 }
  87
  88                 public OdbcCommand (string cmdText) : this ()
  89                 {
  90                         CommandText = cmdText;
  91                 }
  92
  93                 public OdbcCommand (string cmdText, OdbcConnection connection)
  94                         : this (cmdText)
  95                 {
  96                         Connection = connection;
  97                 }
  98
  99                 public OdbcCommand (string cmdText,
 100                                     OdbcConnection connection,
 101                                     OdbcTransaction transaction) : this (cmdText, connection)
 102                 {
 103                         this.Transaction = transaction;
 104                 }
 105
 106                 #endregion // Constructors
 107
 108                 #region Properties
 109
 110                 internal IntPtr hStmt
 111                 {
 112                         get { return hstmt; }
 113                 }
 114
 115
 116                 [OdbcCategory ("Data")]
 117                 [DefaultValue ("")]
 118                 [OdbcDescriptionAttribute ("Command text to execute")]
 119                 [EditorAttribute ("Microsoft.VSDesigner.Data.Odbc.Design.OdbcCommandTextEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
 120                 [RefreshPropertiesAttribute (RefreshProperties.All)]
 121                 public
 122 #if NET_2_0
 123                 override
 124 #endif
 125                 string CommandText
 126                 {
 127                         get { return commandText; }
 128                         set {
 129                                 prepared=false;
 130                                 commandText = value;
 131                         }
 132                 }
 133
 134                 [OdbcDescriptionAttribute ("Time to wait for command to execute")]
 135 #if NET_1_0 || ONLY_1_1
 136                 [DefaultValue (30)]
 137 #endif
 138                 public
 139 #if NET_2_0
 140                 override
 141 #endif
 142                 int CommandTimeout {
 143                         get { return timeout; }
 144                         set { timeout = value; }
 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 { commandType = value; }
 158                 }
 159
 160 #if ONLY_1_1
 161                 [OdbcCategory ("Behavior")]
 162                 [OdbcDescriptionAttribute ("Connection used by the command")]
 163                 [DefaultValue (null)]
 164                 [EditorAttribute ("Microsoft.VSDesigner.Data.Design.DbConnectionEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
 165                 public OdbcConnection Connection {
 166                         get {
 167                                 return connection;
 168                         }
 169                         set {
 170                                 connection = value;
 171                         }
 172                 }
 173 #endif // ONLY_1_1
 174
 175 #if NET_2_0
 176                 [DefaultValue (null)]
 177                 [EditorAttribute ("Microsoft.VSDesigner.Data.Design.DbConnectionEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
 178                 public new OdbcConnection Connection
 179                 {
 180                         get { return DbConnection as OdbcConnection; }
 181                         set { DbConnection = value; }
 182                 }
 183
 184 #endif // NET_2_0
 185
 186                 [BrowsableAttribute (false)]
 187                 [DesignOnlyAttribute (true)]
 188                 [DefaultValue (true)]
 189 #if NET_2_0
 190                 [EditorBrowsable (EditorBrowsableState.Never)]
 191 #else
 192                 [Editor ("Microsoft.VSDesigner.Data.Design.DbConnectionEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
 193 #endif
 194                 public
 195 #if NET_2_0
 196                 override
 197 #endif
 198                 bool DesignTimeVisible {
 199                         get {
 200                                 return designTimeVisible;
 201                         }
 202                         set {
 203                                 designTimeVisible = value;
 204                         }
 205                 }
 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
 226                 [BrowsableAttribute (false)]
 227                 [OdbcDescriptionAttribute ("The transaction used by the command")]
 228                 [DesignerSerializationVisibilityAttribute (DesignerSerializationVisibility.Hidden)]
 229                 public
 230 #if NET_2_0
 231                 new
 232 #endif // NET_2_0
 233                 OdbcTransaction Transaction {
 234                         get {
 235                                 return transaction;
 236                         }
 237                         set {
 238                                 transaction = value;
 239                         }
 240                 }
 241
 242                 [OdbcCategory ("Behavior")]
 243                 [DefaultValue (UpdateRowSource.Both)]
 244                 [OdbcDescriptionAttribute ("When used by a DataAdapter.Update, how command results are applied to the current DataRow")]
 245                 public
 246 #if NET_2_0
 247                 override
 248 #endif
 249                 UpdateRowSource UpdatedRowSource {
 250                                 get {
 251                                         return updateRowSource;
 252                                 }
 253                                 set {
 254                                         updateRowSource = value;
 255                                 }
 256                 }
 257
 258 #if NET_2_0
 259                 protected override DbConnection DbConnection
 260                 {
 261                         get { return connection; }
 262                         set {
 263                                 connection = (OdbcConnection) value;
 264                         }
 265                 }
 266
 267 #endif // NET_2_0
 268
 269 #if ONLY_1_1
 270
 271                 IDbConnection IDbCommand.Connection {
 272                         get {
 273                                 return Connection;
 274                         }
 275                         set {
 276                                 Connection = (OdbcConnection) value;
 277                         }
 278                 }
 279
 280                 IDataParameterCollection IDbCommand.Parameters  {
 281                         get {
 282                                 return Parameters;
 283                         }
 284                 }
 285                 #else
 286                 protected override DbParameterCollection DbParameterCollection
 287                 {
 288                         get { return _parameters as DbParameterCollection;}
 289                 }
 290
 291 #endif // NET_2_0
 292
 293 #if ONLY_1_1
 294                 IDbTransaction IDbCommand.Transaction  {
 295                         get {
 296                                 return (IDbTransaction) Transaction;
 297                         }
 298                         set {
 299                                 if (value is OdbcTransaction)
 300                                 {
 301                                         Transaction = (OdbcTransaction)value;
 302                                 }
 303                                 else
 304                                 {
 305                                         throw new ArgumentException ();
 306                                 }
 307                         }
 308                 }
 309                 #else
 310                 protected override DbTransaction DbTransaction
 311                 {
 312                         get { return transaction; }
 313                         set {
 314                                 transaction = (OdbcTransaction)value;
 315                         }
 316                 }
 317 #endif // ONLY_1_1
 318
 319
 320
 321                 #endregion // Properties
 322
 323                 #region Methods
 324
 325                 public
 326 #if NET_2_0
 327                 override
 328 #endif // NET_2_0
 329                 void Cancel ()
 330                 {
 331                         if (hstmt!=IntPtr.Zero)
 332                         {
 333                                 OdbcReturn Ret=libodbc.SQLCancel(hstmt);
 334                                 if ((Ret!=OdbcReturn.Success) && (Ret!=OdbcReturn.SuccessWithInfo))
 335                                         throw new OdbcException(new OdbcError("SQLCancel",OdbcHandleType.Stmt,hstmt));
 336                         }
 337                         else
 338                                 throw new InvalidOperationException();
 339                 }
 340
 341 #if ONLY_1_1
 342                 IDbDataParameter IDbCommand.CreateParameter ()
 343                 {
 344                         return CreateParameter ();
 345                 }
 346
 347 #else
 348                 protected override DbParameter CreateDbParameter ()
 349                 {
 350                         return CreateParameter ();
 351                 }
 352
 353 #endif // ONLY_1_1
 354
 355                 public new OdbcParameter CreateParameter ()
 356                 {
 357                         return new OdbcParameter ();
 358                 }
 359
 360                 protected override void Dispose (bool disposing)
 361                 {
 362                         if (disposed)
 363                                 return;
 364
 365                         FreeStatement (); // free handles
 366                         Connection = null;
 367                         Transaction = null;
 368                         disposed = true;
 369                 }
 370
 371                 private IntPtr ReAllocStatment ()
 372                 {
 373                         OdbcReturn ret;
 374
 375                         if (hstmt != IntPtr.Zero)
 376                                 FreeStatement ();
 377
 378                         ret=libodbc.SQLAllocHandle(OdbcHandleType.Stmt, Connection.hDbc, ref hstmt);
 379                         if ((ret!=OdbcReturn.Success) && (ret!=OdbcReturn.SuccessWithInfo))
 380                                 throw new OdbcException(new OdbcError("SQLAllocHandle",OdbcHandleType.Dbc,Connection.hDbc));
 381                         disposed = false;
 382                         return hstmt;
 383                 }
 384
 385                 private void FreeStatement ()
 386                 {
 387                         if (hstmt == IntPtr.Zero)
 388                                 return;
 389
 390                         // free previously allocated handle.
 391                         OdbcReturn ret = libodbc.SQLFreeStmt (hstmt, libodbc.SQLFreeStmtOptions.Close);
 392                         if ((ret!=OdbcReturn.Success) && (ret!=OdbcReturn.SuccessWithInfo))
 393                                 throw new OdbcException(new OdbcError("SQLCloseCursor",OdbcHandleType.Stmt,hstmt));
 394
 395                         ret = libodbc.SQLFreeHandle( (ushort) OdbcHandleType.Stmt, hstmt);
 396                         if ((ret!=OdbcReturn.Success) && (ret!=OdbcReturn.SuccessWithInfo))
 397                                 throw new OdbcException(new OdbcError("SQLFreeHandle",OdbcHandleType.Stmt,hstmt));
 398                         hstmt = IntPtr.Zero;
 399                 }
 400
 401                 private void ExecSQL(string sql)
 402                 {
 403                         OdbcReturn ret;
 404                         if (! prepared && Parameters.Count <= 0) {
 405
 406                                 ReAllocStatment ();
 407
 408                                 ret = libodbc.SQLExecDirect (hstmt, sql, libodbc.SQL_NTS);
 409                                 if ((ret != OdbcReturn.Success) && (ret != OdbcReturn.SuccessWithInfo) &&
 410                                     (ret != OdbcReturn.NoData))
 411                                         throw new OdbcException(new OdbcError("SQLExecDirect",OdbcHandleType.Stmt,hstmt));
 412                                 return;
 413                         }
 414
 415                         if (!prepared)
 416                                 Prepare();
 417
 418                         BindParameters ();
 419                         ret=libodbc.SQLExecute(hstmt);
 420                         if ((ret!=OdbcReturn.Success) && (ret!=OdbcReturn.SuccessWithInfo))
 421                                 throw new OdbcException(new OdbcError("SQLExecute",OdbcHandleType.Stmt,hstmt));
 422                 }
 423
 424                 internal void FreeIfNotPrepared ()
 425                 {
 426                         if (! prepared)
 427                                 FreeStatement ();
 428                 }
 429
 430                 public
 431 #if NET_2_0
 432                 override
 433 #endif // NET_2_0
 434                 int ExecuteNonQuery ()
 435                 {
 436                         return ExecuteNonQuery (true);
 437                 }
 438
 439                 private int ExecuteNonQuery (bool freeHandle)
 440                 {
 441                         int records = 0;
 442                         if (Connection == null)
 443                                 throw new InvalidOperationException ("No open connection");
 444                         if (Connection.State == ConnectionState.Closed)
 445                                 throw new InvalidOperationException ("Connection state is closed");
 446                         // FIXME: a third check is mentioned in .NET docs
 447
 448                         ExecSQL(CommandText);
 449
 450                         // .NET documentation says that except for INSERT, UPDATE and
 451                         // DELETE  where the return value is the number of rows affected
 452                         // for the rest of the commands the return value is -1.
 453                         if ((CommandText.ToUpper().IndexOf("UPDATE")!=-1) ||
 454                             (CommandText.ToUpper().IndexOf("INSERT")!=-1) ||
 455                             (CommandText.ToUpper().IndexOf("DELETE")!=-1)) {
 456
 457                                 int numrows = 0;
 458                                 OdbcReturn ret = libodbc.SQLRowCount(hstmt,ref numrows);
 459                                 records = numrows;
 460                         }
 461                         else
 462                                 records = -1;
 463
 464                         if (freeHandle && !prepared)
 465                                 FreeStatement ();
 466
 467                         return records;
 468                 }
 469
 470                 public
 471 #if NET_2_0
 472                 override
 473 #endif // NET_2_0
 474                 void Prepare()
 475                 {
 476                         ReAllocStatment ();
 477
 478                         OdbcReturn ret;
 479                         ret=libodbc.SQLPrepare(hstmt, CommandText, CommandText.Length);
 480                         if ((ret!=OdbcReturn.Success) && (ret!=OdbcReturn.SuccessWithInfo))
 481                                 throw new OdbcException(new OdbcError("SQLPrepare",OdbcHandleType.Stmt,hstmt));
 482                         prepared=true;
 483                 }
 484
 485                 private void BindParameters ()
 486                 {
 487                         int i=1;
 488                         foreach (OdbcParameter p in Parameters)
 489                         {
 490                                 p.Bind(hstmt, i);
 491                                 p.CopyValue ();
 492                                 i++;
 493                         }
 494                 }
 495
 496
 497                 public
 498 #if NET_2_0
 499                 new
 500 #endif // NET_2_0
 501                 OdbcDataReader ExecuteReader ()
 502                 {
 503                         return ExecuteReader (CommandBehavior.Default);
 504                 }
 505
 506 #if ONLY_1_1
 507                 IDataReader IDbCommand.ExecuteReader ()
 508                 {
 509                         return ExecuteReader ();
 510                 }
 511                 #else
 512                 protected override DbDataReader ExecuteDbDataReader (CommandBehavior behavior)
 513                 {
 514                         return ExecuteReader (behavior);
 515                 }
 516
 517 #endif // ONLY_1_1
 518
 519                 public
 520 #if NET_2_0
 521                 new
 522 #endif // NET_2_0
 523                 OdbcDataReader ExecuteReader (CommandBehavior behavior)
 524                 {
 525                         int recordsAffected = ExecuteNonQuery(false);
 526                         OdbcDataReader dataReader=new OdbcDataReader(this, behavior, recordsAffected);
 527                         return dataReader;
 528                 }
 529
 530 #if ONLY_1_1
 531                 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
 532                 {
 533                         return ExecuteReader (behavior);
 534                 }
 535 #endif // ONLY_1_1
 536
 537                 public
 538 #if NET_2_0
 539                 override
 540 #endif
 541                 object ExecuteScalar ()
 542                 {
 543                         object val = null;
 544                         OdbcDataReader reader=ExecuteReader();
 545                         try
 546                         {
 547                                 if (reader.Read ())
 548                                         val=reader[0];
 549                         }
 550                         finally
 551                         {
 552                                 reader.Close();
 553                         }
 554                         return val;
 555                 }
 556
 557                 [MonoTODO]
 558                 object ICloneable.Clone ()
 559                 {
 560                         throw new NotImplementedException ();
 561                 }
 562
 563                 public void ResetCommandTimeout ()
 564                 {
 565                         CommandTimeout = 30;
 566                 }
 567
 568                 #endregion
 569         }
 570 }