mcs/class/IBM.Data.DB2/IBM.Data.DB2/Db2Command.cs

   1 using System;
   2 using System.Data;
   3 using System.Runtime.InteropServices;
   4
   5
   6 namespace IBM.Data.DB2
   7 {
   8
   9         public class DB2Command : System.ComponentModel.Component,IDbCommand
  10         {
  11                 #region Private data members
  12
  13
  14                 private WeakReference refDataReader;
  15                 private string commandText;
  16                 private CommandType commandType = CommandType.Text;
  17                 private DB2Connection db2Conn;
  18                 private DB2Transaction db2Trans;
  19                 private int commandTimeout;
  20                 private bool prepared = false;
  21                 private IntPtr hwndStmt = IntPtr.Zero;  //Our statement handle
  22                 private DB2ParameterCollection parameters = new DB2ParameterCollection();
  23                 private bool disposed = false;
  24                 private bool statementOpen;
  25
  26                 #endregion
  27
  28                 #region Constructors
  29
  30                 public DB2Command()
  31                 {
  32                         commandTimeout = 30;
  33                         hwndStmt = IntPtr.Zero;
  34                 }
  35                 public DB2Command(string commandStr):this()
  36                 {
  37                         commandText = commandStr;
  38
  39                 }
  40                 public DB2Command(string commandStr, DB2Connection con) : this(commandStr)
  41                 {
  42                         db2Conn = con;
  43                         AllocateStatement("Constructor 3");
  44                 }
  45                 public DB2Command (string commandStr, DB2Connection con, DB2Transaction trans):this(commandStr, con)
  46                 {
  47                         db2Trans = trans;
  48                         if(null != con)
  49                         {
  50                                 con.AddCommand(this);
  51                         }
  52                         AllocateStatement("Constructor 4");
  53                 }
  54                 #endregion
  55
  56                 #region Dispose
  57                 public new void  Dispose()
  58                 {
  59                         Dispose(true);
  60                         GC.SuppressFinalize(this);
  61                 }
  62
  63                 protected override void Dispose(bool disposing)
  64                 {
  65                         if(!disposed)
  66                         {
  67                                 if(disposing)
  68                                 {
  69                                         ConnectionClosed();
  70                                         if(db2Conn != null)
  71                                         {
  72                                                 db2Conn.RemoveCommand(this);
  73                                                 db2Conn = null;
  74                                         }
  75                                 }
  76                         }
  77                         base.Dispose(disposing);
  78                         disposed = true;
  79                 }
  80
  81
  82                 ~DB2Command()
  83                 {
  84                         Dispose(false);
  85                 }
  86
  87                 internal void DataReaderClosed()
  88                 {
  89                         CloseStatementHandle(false);
  90                         refDataReader = null;
  91                 }
  92
  93                 private void CloseStatementHandle(bool dispose)
  94                 {
  95                         if(hwndStmt != IntPtr.Zero)
  96                         {
  97                                 if(statementOpen)
  98                                 {
  99                                         short sqlRet = DB2CLIWrapper.SQLFreeStmt(hwndStmt, DB2Constants.SQL_CLOSE);
 100                                         Db2Environment.Log("Close stm {0,-4}  {1}", hwndStmt, sqlRet);
 101                                 }
 102                                 if((!prepared && statementOpen) ||
 103                                         dispose)
 104                                 {
 105                                         short sqlRet = DB2CLIWrapper.SQLFreeHandle(DB2Constants.SQL_HANDLE_STMT, hwndStmt);
 106
 107                                         hwndStmt = IntPtr.Zero;
 108                                         prepared = false;
 109                                 }
 110                                 statementOpen = false;
 111                         }
 112                 }
 113
 114                 internal void ConnectionClosed()
 115                 {
 116                         DB2DataReader reader = null;
 117                         if((refDataReader != null) && refDataReader.IsAlive)
 118                         {
 119                                 reader = (DB2DataReader)refDataReader.Target;
 120                         }
 121                         if((reader != null) && refDataReader.IsAlive)
 122                         {
 123                                 reader.Dispose();
 124                                 refDataReader = null;
 125                         }
 126                         CloseStatementHandle(true);
 127
 128                         db2Trans = null;
 129                 }
 130
 131                 #endregion
 132
 133                 #region SelfDescribe property
 134                 ///
 135                 /// Property dictates whether or not any paramter markers will get their describe info
 136                 /// from the database, or if the user will supply the information
 137                 ///
 138                 bool selfDescribe = false;
 139                 public bool SelfDescribe
 140                 {
 141                         get
 142                         {
 143                                 return selfDescribe;
 144                         }
 145                         set
 146                         {
 147                                 selfDescribe = value;
 148                         }
 149                 }
 150                 #endregion
 151
 152                 #region CommandText property
 153                 ///
 154                 ///The query;  If it gets set, reset the prepared property
 155                 ///
 156                 public string CommandText
 157                 {
 158                         get
 159                         {
 160                                 return commandText;
 161                         }
 162                         set
 163                         {
 164                                 prepared = false;
 165                                 commandText = value;
 166                         }
 167                 }
 168                 #endregion
 169
 170                 #region CommandTimeout property
 171                 ///
 172                 /// The Timeout property states how long we wait for results to return
 173                 ///
 174                 public int CommandTimeout
 175                 {
 176                         get
 177                         {
 178                                 return commandTimeout;
 179                         }
 180                         set
 181                         {
 182                                 commandTimeout = value;
 183                         }
 184                 }
 185                 #endregion
 186
 187                 #region CommandType property
 188
 189                 public CommandType CommandType
 190                 {
 191                         get
 192                         {
 193                                 return commandType;
 194                         }
 195                         set
 196                         {
 197                                 commandType = value;
 198                         }
 199                 }
 200                 #endregion
 201
 202                 #region Connection property
 203                 ///
 204                 ///  The connection we'll be executing on.
 205                 ///
 206                 IDbConnection IDbCommand.Connection
 207                 {
 208                         get
 209                         {
 210                                 return db2Conn;
 211                         }
 212                         set
 213                         {
 214                                 db2Conn = (DB2Connection)value;
 215                                 //AllocateStatement("Connection property set");
 216                         }
 217                 }
 218
 219                 public DB2Connection Connection
 220                 {
 221                         get
 222                         {
 223                                 return db2Conn;
 224                         }
 225                         set
 226                         {
 227                                 if(db2Conn != null)
 228                                 {
 229                                         db2Conn.RemoveCommand(this);
 230                                 }
 231                                 db2Conn = value;
 232                                 if(db2Conn != null)
 233                                 {
 234                                         db2Conn.AddCommand(this);
 235                                 }
 236                         }
 237                 }
 238                 #endregion
 239
 240                 #region Parameters property
 241                 ///
 242                 /// Parameter list, Not yet implemented
 243                 ///
 244                 public DB2ParameterCollection Parameters
 245                 {
 246                         get
 247                         {
 248                                 return parameters;
 249                         }
 250                 }
 251                 IDataParameterCollection IDbCommand.Parameters
 252                 {
 253                         get
 254                         {
 255                                 return parameters;
 256                         }
 257                 }
 258                 #endregion
 259
 260                 #region Transaction property
 261                         ///
 262                         /// The transaction this command is associated with
 263                         ///
 264                         public IDbTransaction Transaction
 265                 {
 266                         get
 267                         {
 268                                 return db2Trans;
 269                         }
 270                         set
 271                         {
 272                                 db2Trans = (DB2Transaction)value;
 273                         }
 274                 }
 275                 #endregion
 276
 277                 #region UpdatedRowSource property
 278
 279                 public UpdateRowSource UpdatedRowSource
 280                 {
 281                         get
 282                         {
 283                                 throw new DB2Exception ("TBD");
 284                         }
 285                         set
 286                         {
 287                                 throw new DB2Exception ("TBD");
 288                         }
 289                 }
 290                 #endregion
 291
 292                 #region Statement Handle
 293                 ///
 294                 /// returns the DB2Client statement handle
 295                 ///
 296                 public IntPtr statementHandle
 297                 {
 298                         get
 299                         {
 300                                 return hwndStmt;
 301                         }
 302                 }
 303                 #endregion
 304
 305                 #region AllocateStatement function
 306
 307                 internal void AllocateStatement(string location)
 308                 {
 309                         if (db2Conn.DBHandle.ToInt32() == 0) return;
 310                         short sqlRet;
 311                         sqlRet = DB2CLIWrapper.SQLAllocHandle(DB2Constants.SQL_HANDLE_STMT, db2Conn.DBHandle , ref hwndStmt);
 312                         Console.WriteLine("Connection handle : {0}", db2Conn.DBHandle.ToInt32().ToString());
 313                         Db2Environment.Log("Alloc stm {0,-4}  {1}", hwndStmt, sqlRet);
 314                         if ((sqlRet != DB2Constants.SQL_SUCCESS) && (sqlRet != DB2Constants.SQL_SUCCESS_WITH_INFO))
 315                                 throw new DB2Exception(DB2Constants.SQL_HANDLE_DBC, db2Conn.DBHandle, location +": Unable to allocate statement handle.");
 316                         parameters.HwndStmt = hwndStmt;
 317                 }
 318                 #endregion
 319
 320                 #region Cancel
 321                 /// <summary>
 322                 /// Attempt to cancel an executing command
 323                 /// </summary>
 324                 public void Cancel()
 325                 {
 326                         if(hwndStmt == IntPtr.Zero)
 327                         {
 328                                 throw new InvalidOperationException("Nothing to Cancel.");
 329                         }
 330                         DB2CLIWrapper.SQLCancel(hwndStmt);
 331                 }
 332                 #endregion
 333
 334                 #region CreateParameter
 335                 ///
 336                 ///Returns a parameter
 337                 ///
 338                 public IDbDataParameter CreateParameter()
 339                 {
 340                         throw new DB2Exception("TBD");
 341                 }
 342                 #endregion
 343
 344                 #region ExecuteNonQuery
 345
 346                 public int ExecuteNonQuery()
 347                 {
 348                         int result = ExecuteNonQueryInternal();
 349                         CloseStatementHandle(false);
 350
 351                         return result;
 352                 }
 353
 354                 public int ExecuteNonQueryInternal()
 355                 {
 356                         if((db2Conn == null) || (db2Conn.State != ConnectionState.Open))
 357                                 throw new InvalidOperationException("Prepare needs an open connection");
 358
 359                         if (hwndStmt == IntPtr.Zero)
 360                         {
 361                                 AllocateStatement("InternalExecuteNonQuery");
 362                         }
 363                         if ((commandText == null) ||(commandText.Length == 0))
 364                                 throw new InvalidOperationException("Command string is empty");
 365
 366                         if(CommandType.StoredProcedure == commandType && !commandText.StartsWith("CALL "))
 367                                 commandText = "CALL " + commandText;
 368
 369                         short sqlRet;
 370                         if (prepared)
 371                         {
 372                                 sqlRet = DB2CLIWrapper.SQLExecute(hwndStmt);
 373                                 DB2ClientUtils.DB2CheckReturn(sqlRet, DB2Constants.SQL_HANDLE_STMT, hwndStmt, "SQLExecute error.");
 374                         }
 375                         else
 376                         {
 377                                 sqlRet = DB2CLIWrapper.SQLExecDirect(hwndStmt, commandText, commandText.Length);
 378                                 DB2ClientUtils.DB2CheckReturn(sqlRet, DB2Constants.SQL_HANDLE_STMT, hwndStmt, "SQLExecDirect error.");
 379                         }
 380                         statementOpen = true;
 381
 382                         int numRows = 0;
 383                         sqlRet = DB2CLIWrapper.SQLRowCount(hwndStmt, ref numRows);   //How many rows affected.  numRows will be -1 if we aren't dealing with an Insert, Delete or Update, or if the statement did not execute successfully
 384                         DB2ClientUtils.DB2CheckReturn(sqlRet, DB2Constants.SQL_HANDLE_STMT, hwndStmt, "SQLExecDirect error.");
 385
 386
 387                         parameters.GetOutValues();
 388                         ///At this point, I think we need to save any results, but not return them
 389                         ///For now, we will go execute and return the number of rows affected
 390                         return numRows;
 391                 }
 392                 #endregion
 393
 394                 #region ExecuteReader calls
 395                 ///
 396                 ///ExecuteReader
 397                 ///
 398                 IDataReader IDbCommand.ExecuteReader()
 399                 {
 400                         return ExecuteReader(0);
 401                 }
 402
 403                 IDataReader IDbCommand.ExecuteReader(CommandBehavior behavior)
 404                 {
 405                         return ExecuteReader(behavior);
 406                 }
 407
 408                 public DB2DataReader ExecuteReader()
 409                 {
 410                         return ExecuteReader(0);
 411                 }
 412
 413                 public DB2DataReader ExecuteReader(CommandBehavior behavior)
 414                 {
 415                         if((db2Conn == null) || (db2Conn.State != ConnectionState.Open))
 416                                 throw new InvalidOperationException("Prepare needs an open connection");
 417
 418                         DB2DataReader reader;
 419
 420                         int nrOfRows = ExecuteNonQueryInternal();
 421
 422                         reader = new DB2DataReader(db2Conn, this);
 423                         reader.recordsAffected = nrOfRows;
 424
 425                         refDataReader = new WeakReference(reader);
 426
 427                         return reader;
 428                 }
 429                 #endregion
 430
 431                 #region ExecuteScalar
 432                 ///
 433                 /// ExecuteScalar
 434                 ///
 435                 public object ExecuteScalar()
 436                 {
 437                         if((db2Conn == null) || (db2Conn.State != ConnectionState.Open))
 438                                 throw new InvalidOperationException("Prepare needs an open connection");
 439
 440                         using(DB2DataReader reader = ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.SingleRow))
 441                         {
 442                                 if(reader.Read() && (reader.FieldCount > 0))
 443                                 {
 444                                         return reader[0];
 445                                 }
 446                         }
 447                         return null;
 448                 }
 449                 #endregion
 450
 451                 #region Prepare ()
 452
 453                 public void Prepare ()
 454                 {
 455                         if((db2Conn == null) || (db2Conn.State != ConnectionState.Open))
 456                                 throw new InvalidOperationException("Prepare needs an open connection");
 457
 458                         CloseStatementHandle(false);
 459                         if (hwndStmt == IntPtr.Zero)
 460                         {
 461                                 AllocateStatement("InternalExecuteNonQuery");
 462                         }
 463
 464                         short sqlRet = 0;
 465
 466                         IntPtr numParams = IntPtr.Zero;
 467                         sqlRet = DB2CLIWrapper.SQLPrepare(hwndStmt, commandText, commandText.Length);
 468                         DB2ClientUtils.DB2CheckReturn(sqlRet, DB2Constants.SQL_HANDLE_STMT, hwndStmt, "SQLPrepare error.");
 469
 470                         statementOpen = true;
 471
 472                         short i=1;
 473                         foreach ( DB2Parameter param in parameters)
 474                         {
 475
 476                                 if (selfDescribe)
 477                                 {
 478                                         sqlRet = param.Describe(this.hwndStmt, i);
 479                                         DB2ClientUtils.DB2CheckReturn(sqlRet, DB2Constants.SQL_HANDLE_STMT, hwndStmt, "Error binding parameter in Db2Command: ");
 480                                 }
 481                                 sqlRet = param.Bind(this.hwndStmt, i);
 482                                 DB2ClientUtils.DB2CheckReturn(sqlRet, DB2Constants.SQL_HANDLE_STMT, hwndStmt, "Error binding parameter in Db2Command: ");
 483                                 i++;
 484                         }
 485                         prepared=true;
 486                 }
 487                 #endregion
 488         }
 489 }