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