mcs/class/Mono.Data.SybaseClient/Mono.Data.SybaseClient/SybaseCommand.cs

   1 //
   2 // Mono.Data.SybaseClient.SybaseCommand.cs
   3 //
   4 // Author:
   5 //   Rodrigo Moya (rodrigo@ximian.com)
   6 //   Daniel Morgan (danmorg@sc.rr.com)
   7 //   Tim Coleman (tim@timcoleman.com)
   8 //
   9 // (C) Ximian, Inc 2002 http://www.ximian.com/
  10 // (C) Daniel Morgan, 2002
  11 // Copyright (C) Tim Coleman, 2002
  12 //
  13
  14 //
  15 // Permission is hereby granted, free of charge, to any person obtaining
  16 // a copy of this software and associated documentation files (the
  17 // "Software"), to deal in the Software without restriction, including
  18 // without limitation the rights to use, copy, modify, merge, publish,
  19 // distribute, sublicense, and/or sell copies of the Software, and to
  20 // permit persons to whom the Software is furnished to do so, subject to
  21 // the following conditions:
  22 //
  23 // The above copyright notice and this permission notice shall be
  24 // included in all copies or substantial portions of the Software.
  25 //
  26 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  27 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  28 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  29 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  30 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  31 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  32 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  33 //
  34
  35 using Mono.Data.Tds;
  36 using Mono.Data.Tds.Protocol;
  37 using System;
  38 using System.Collections;
  39 using System.Collections.Specialized;
  40 using System.ComponentModel;
  41 using System.Data;
  42 using System.Data.Common;
  43 using System.Runtime.InteropServices;
  44 using System.Text;
  45
  46 namespace Mono.Data.SybaseClient {
  47         public sealed class SybaseCommand : Component, IDbCommand, ICloneable
  48         {
  49                 #region Fields
  50
  51                 bool disposed = false;
  52                 int commandTimeout;
  53                 bool designTimeVisible;
  54                 string commandText;
  55                 CommandType commandType;
  56                 SybaseConnection connection;
  57                 SybaseTransaction transaction;
  58                 UpdateRowSource updatedRowSource;
  59                 CommandBehavior behavior = CommandBehavior.Default;
  60                 SybaseParameterCollection parameters;
  61                 string preparedStatement = null;
  62
  63                 #endregion // Fields
  64
  65                 #region Constructors
  66
  67                 public SybaseCommand()
  68                         : this (String.Empty, null, null)
  69                 {
  70                 }
  71
  72                 public SybaseCommand (string commandText)
  73                         : this (commandText, null, null)
  74                 {
  75                         commandText = commandText;
  76                 }
  77
  78                 public SybaseCommand (string commandText, SybaseConnection connection)
  79                         : this (commandText, connection, null)
  80                 {
  81                         Connection = connection;
  82                 }
  83
  84                 public SybaseCommand (string commandText, SybaseConnection connection, SybaseTransaction transaction)
  85                 {
  86                         this.commandText = commandText;
  87                         this.connection = connection;
  88                         this.transaction = transaction;
  89                         this.commandType = CommandType.Text;
  90                         this.updatedRowSource = UpdateRowSource.Both;
  91
  92                         this.designTimeVisible = false;
  93                         this.commandTimeout = 30;
  94                         parameters = new SybaseParameterCollection (this);
  95                 }
  96
  97                 #endregion // Constructors
  98
  99                 #region Properties
 100
 101                 internal CommandBehavior CommandBehavior {
 102                         get { return behavior; }
 103                 }
 104
 105                 public string CommandText {
 106                         get { return commandText; }
 107                         set {
 108                                 if (value != commandText && preparedStatement != null)
 109                                         Unprepare ();
 110                                 commandText = value;
 111                         }
 112                 }
 113
 114                 public int CommandTimeout {
 115                         get { return commandTimeout;  }
 116                         set {
 117                                 if (commandTimeout < 0)
 118                                         throw new ArgumentException ("The property value assigned is less than 0.");
 119                                 commandTimeout = value;
 120                         }
 121                 }
 122
 123                 public CommandType CommandType  {
 124                         get { return commandType; }
 125                         set {
 126                                 if (value == CommandType.TableDirect)
 127                                         throw new ArgumentException ("CommandType.TableDirect is not supported by the Mono SybaseClient Data Provider.");
 128                                 commandType = value;
 129                         }
 130                 }
 131
 132                 public SybaseConnection Connection {
 133                         get { return connection; }
 134                         set {
 135                                 if (transaction != null && connection.Transaction != null && connection.Transaction.IsOpen)
 136                                         throw new InvalidOperationException ("The Connection property was changed while a transaction was in progress.");
 137                                 transaction = null;
 138                                 connection = value;
 139                         }
 140                 }
 141
 142                 public bool DesignTimeVisible {
 143                         get { return designTimeVisible; }
 144                         set { designTimeVisible = value; }
 145                 }
 146
 147                 public SybaseParameterCollection Parameters {
 148                         get { return parameters; }
 149                 }
 150
 151                 internal ITds Tds {
 152                         get { return Connection.Tds; }
 153                 }
 154
 155                 IDbConnection IDbCommand.Connection {
 156                         get { return Connection; }
 157                         set {
 158                                 if (!(value is SybaseConnection))
 159                                         throw new InvalidCastException ("The value was not a valid SybaseConnection.");
 160                                 Connection = (SybaseConnection) value;
 161                         }
 162                 }
 163
 164                 IDataParameterCollection IDbCommand.Parameters  {
 165                         get { return Parameters; }
 166                 }
 167
 168                 IDbTransaction IDbCommand.Transaction {
 169                         get { return Transaction; }
 170                         set {
 171                                 if (!(value is SybaseTransaction))
 172                                         throw new ArgumentException ();
 173                                 Transaction = (SybaseTransaction) value;
 174                         }
 175                 }
 176
 177                 public SybaseTransaction Transaction {
 178                         get { return transaction; }
 179                         set { transaction = value; }
 180                 }
 181
 182                 public UpdateRowSource UpdatedRowSource {
 183                         get { return updatedRowSource; }
 184                         set { updatedRowSource = value; }
 185                 }
 186
 187                 #endregion // Fields
 188
 189                 #region Methods
 190
 191                 public void Cancel ()
 192                 {
 193                         if (Connection == null || Connection.Tds == null)
 194                                 return;
 195                         Connection.Tds.Cancel ();
 196                 }
 197
 198                 internal void CloseDataReader (bool moreResults)
 199                 {
 200                         GetOutputParameters ();
 201                         Connection.DataReader = null;
 202
 203                         if ((behavior & CommandBehavior.CloseConnection) != 0)
 204                                 Connection.Close ();
 205                 }
 206
 207                 public SybaseParameter CreateParameter ()
 208                 {
 209                         return new SybaseParameter ();
 210                 }
 211
 212                 internal void DeriveParameters ()
 213                 {
 214                         if (commandType != CommandType.StoredProcedure)
 215                                 throw new InvalidOperationException (String.Format ("SybaseCommand DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
 216                         ValidateCommand ("DeriveParameters");
 217
 218                         SybaseParameterCollection localParameters = new SybaseParameterCollection (this);
 219                         localParameters.Add ("@P1", SybaseType.NVarChar, commandText.Length).Value = commandText;
 220
 221                         string sql = "sp_procedure_params_rowset";
 222
 223                         Connection.Tds.ExecProc (sql, localParameters.MetaParameters, 0, true);
 224
 225                         SybaseDataReader reader = new SybaseDataReader (this);
 226                         parameters.Clear ();
 227                         object[] dbValues = new object[reader.FieldCount];
 228
 229                         while (reader.Read ()) {
 230                                 reader.GetValues (dbValues);
 231                                 parameters.Add (new SybaseParameter (dbValues));
 232                         }
 233                         reader.Close ();
 234                 }
 235
 236                 private void Execute (CommandBehavior behavior, bool wantResults)
 237                 {
 238                         Tds.RecordsAffected = -1;
 239                         TdsMetaParameterCollection parms = Parameters.MetaParameters;
 240                         if (preparedStatement == null) {
 241                                 bool schemaOnly = ((CommandBehavior & CommandBehavior.SchemaOnly) > 0);
 242                                 bool keyInfo = ((CommandBehavior & CommandBehavior.SchemaOnly) > 0);
 243
 244                                 StringBuilder sql1 = new StringBuilder ();
 245                                 StringBuilder sql2 = new StringBuilder ();
 246
 247                                 if (schemaOnly || keyInfo)
 248                                         sql1.Append ("SET FMTONLY OFF;");
 249                                 if (keyInfo) {
 250                                         sql1.Append ("SET NO_BROWSETABLE ON;");
 251                                         sql2.Append ("SET NO_BROWSETABLE OFF;");
 252                                 }
 253                                 if (schemaOnly) {
 254                                         sql1.Append ("SET FMTONLY ON;");
 255                                         sql2.Append ("SET FMTONLY OFF;");
 256                                 }
 257
 258                                 switch (CommandType) {
 259                                 case CommandType.StoredProcedure:
 260                                         if (keyInfo || schemaOnly)
 261                                                 Connection.Tds.Execute (sql1.ToString ());
 262                                         Connection.Tds.ExecProc (CommandText, parms, CommandTimeout, wantResults);
 263                                         if (keyInfo || schemaOnly)
 264                                                 Connection.Tds.Execute (sql2.ToString ());
 265                                         break;
 266                                 case CommandType.Text:
 267                                         string sql = String.Format ("{0}{1}{2}", sql1.ToString (), CommandText, sql2.ToString ());
 268                                         Connection.Tds.Execute (sql, parms, CommandTimeout, wantResults);
 269                                         break;
 270                                 }
 271                         }
 272                         else
 273                                 Connection.Tds.ExecPrepared (preparedStatement, parms, CommandTimeout, wantResults);
 274                 }
 275
 276                 public int ExecuteNonQuery ()
 277                 {
 278                         ValidateCommand ("ExecuteNonQuery");
 279                         int result = 0;
 280
 281                         try {
 282                                 Execute (CommandBehavior.Default, false);
 283                                 result = Tds.RecordsAffected;
 284                         }
 285                         catch (TdsTimeoutException e) {
 286                                 throw SybaseException.FromTdsInternalException ((TdsInternalException) e);
 287                         }
 288
 289                         GetOutputParameters ();
 290                         return result;
 291                 }
 292
 293                 public SybaseDataReader ExecuteReader ()
 294                 {
 295                         return ExecuteReader (CommandBehavior.Default);
 296                 }
 297
 298                 public SybaseDataReader ExecuteReader (CommandBehavior behavior)
 299                 {
 300                         ValidateCommand ("ExecuteReader");
 301                         try {
 302                                 Execute (behavior, true);
 303                         }
 304                         catch (TdsTimeoutException e) {
 305                                 throw SybaseException.FromTdsInternalException ((TdsInternalException) e);
 306                         }
 307                         Connection.DataReader = new SybaseDataReader (this);
 308                         return Connection.DataReader;
 309                 }
 310
 311                 public object ExecuteScalar ()
 312                 {
 313                         ValidateCommand ("ExecuteScalar");
 314                         try {
 315                                 Execute (CommandBehavior.Default, true);
 316                         }
 317                         catch (TdsTimeoutException e) {
 318                                 throw SybaseException.FromTdsInternalException ((TdsInternalException) e);
 319                         }
 320
 321                         if (!Connection.Tds.NextResult () || !Connection.Tds.NextRow ())
 322                                 return null;
 323
 324                         object result = Connection.Tds.ColumnValues [0];
 325                         CloseDataReader (true);
 326                         return result;
 327                 }
 328
 329                 private void GetOutputParameters ()
 330                 {
 331                         Connection.Tds.SkipToEnd ();
 332
 333                         IList list = Connection.Tds.ColumnValues;
 334
 335                         if (list != null && list.Count > 0) {
 336                                 int index = 0;
 337                                 foreach (SybaseParameter parameter in parameters) {
 338                                         if (parameter.Direction != ParameterDirection.Input) {
 339                                                 parameter.Value = list [index];
 340                                                 index += 1;
 341                                         }
 342                                         if (index >= list.Count)
 343                                                 break;
 344                                 }
 345                         }
 346                 }
 347
 348                 object ICloneable.Clone ()
 349                 {
 350                         return new SybaseCommand (commandText, Connection);
 351                 }
 352
 353                 IDbDataParameter IDbCommand.CreateParameter ()
 354                 {
 355                         return CreateParameter ();
 356                 }
 357
 358                 IDataReader IDbCommand.ExecuteReader ()
 359                 {
 360                         return ExecuteReader ();
 361                 }
 362
 363                 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
 364                 {
 365                         return ExecuteReader (behavior);
 366                 }
 367
 368                 public void Prepare ()
 369                 {
 370                         ValidateCommand ("Prepare");
 371                         if (CommandType == CommandType.Text)
 372                                 preparedStatement = Connection.Tds.Prepare (CommandText, Parameters.MetaParameters);
 373                 }
 374
 375                 public void ResetCommandTimeout ()
 376                 {
 377                         commandTimeout = 30;
 378                 }
 379
 380                 private void Unprepare ()
 381                 {
 382                         Connection.Tds.Unprepare (preparedStatement);
 383                         preparedStatement = null;
 384                 }
 385
 386                 private void ValidateCommand (string method)
 387                 {
 388                         if (Connection == null)
 389                                 throw new InvalidOperationException (String.Format ("{0} requires a Connection object to continue.", method));
 390                         if (Connection.Transaction != null && transaction != Connection.Transaction)
 391                                 throw new InvalidOperationException ("The Connection object does not have the same transaction as the command object.");
 392                         if (Connection.State != ConnectionState.Open)
 393                                 throw new InvalidOperationException (String.Format ("ExecuteNonQuery requires an open Connection object to continue. This connection is closed.", method));
 394                         if (commandText == String.Empty || commandText == null)
 395                                 throw new InvalidOperationException ("The command text for this Command has not been set.");
 396                         if (Connection.DataReader != null)
 397                                 throw new InvalidOperationException ("There is already an open DataReader associated with this Connection which must be closed first.");
 398                 }
 399
 400                 #endregion // Methods
 401         }
 402 }