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 using Mono.Data.Tds;
  15 using Mono.Data.Tds.Protocol;
  16 using System;
  17 using System.Collections;
  18 using System.Collections.Specialized;
  19 using System.ComponentModel;
  20 using System.Data;
  21 using System.Data.Common;
  22 using System.Runtime.InteropServices;
  23 using System.Text;
  24
  25 namespace Mono.Data.SybaseClient {
  26         public sealed class SybaseCommand : Component, IDbCommand, ICloneable
  27         {
  28                 #region Fields
  29
  30                 bool disposed = false;
  31                 int commandTimeout;
  32                 bool designTimeVisible;
  33                 string commandText;
  34                 CommandType commandType;
  35                 SybaseConnection connection;
  36                 SybaseTransaction transaction;
  37                 UpdateRowSource updatedRowSource;
  38                 CommandBehavior behavior = CommandBehavior.Default;
  39                 SybaseParameterCollection parameters;
  40                 string preparedStatement = null;
  41
  42                 #endregion // Fields
  43
  44                 #region Constructors
  45
  46                 public SybaseCommand()
  47                         : this (String.Empty, null, null)
  48                 {
  49                 }
  50
  51                 public SybaseCommand (string commandText)
  52                         : this (commandText, null, null)
  53                 {
  54                         commandText = commandText;
  55                 }
  56
  57                 public SybaseCommand (string commandText, SybaseConnection connection)
  58                         : this (commandText, connection, null)
  59                 {
  60                         Connection = connection;
  61                 }
  62
  63                 public SybaseCommand (string commandText, SybaseConnection connection, SybaseTransaction transaction)
  64                 {
  65                         this.commandText = commandText;
  66                         this.connection = connection;
  67                         this.transaction = transaction;
  68                         this.commandType = CommandType.Text;
  69                         this.updatedRowSource = UpdateRowSource.Both;
  70
  71                         this.designTimeVisible = false;
  72                         this.commandTimeout = 30;
  73                         parameters = new SybaseParameterCollection (this);
  74                 }
  75
  76                 #endregion // Constructors
  77
  78                 #region Properties
  79
  80                 internal CommandBehavior CommandBehavior {
  81                         get { return behavior; }
  82                 }
  83
  84                 public string CommandText {
  85                         get { return commandText; }
  86                         set {
  87                                 if (value != commandText && preparedStatement != null)
  88                                         Unprepare ();
  89                                 commandText = value;
  90                         }
  91                 }
  92
  93                 public int CommandTimeout {
  94                         get { return commandTimeout;  }
  95                         set {
  96                                 if (commandTimeout < 0)
  97                                         throw new ArgumentException ("The property value assigned is less than 0.");
  98                                 commandTimeout = value;
  99                         }
 100                 }
 101
 102                 public CommandType CommandType  {
 103                         get { return commandType; }
 104                         set {
 105                                 if (value == CommandType.TableDirect)
 106                                         throw new ArgumentException ("CommandType.TableDirect is not supported by the Mono SybaseClient Data Provider.");
 107                                 commandType = value;
 108                         }
 109                 }
 110
 111                 public SybaseConnection Connection {
 112                         get { return connection; }
 113                         set {
 114                                 if (transaction != null && connection.Transaction != null && connection.Transaction.IsOpen)
 115                                         throw new InvalidOperationException ("The Connection property was changed while a transaction was in progress.");
 116                                 transaction = null;
 117                                 connection = value;
 118                         }
 119                 }
 120
 121                 public bool DesignTimeVisible {
 122                         get { return designTimeVisible; }
 123                         set { designTimeVisible = value; }
 124                 }
 125
 126                 public SybaseParameterCollection Parameters {
 127                         get { return parameters; }
 128                 }
 129
 130                 internal ITds Tds {
 131                         get { return Connection.Tds; }
 132                 }
 133
 134                 IDbConnection IDbCommand.Connection {
 135                         get { return Connection; }
 136                         set {
 137                                 if (!(value is SybaseConnection))
 138                                         throw new InvalidCastException ("The value was not a valid SybaseConnection.");
 139                                 Connection = (SybaseConnection) value;
 140                         }
 141                 }
 142
 143                 IDataParameterCollection IDbCommand.Parameters  {
 144                         get { return Parameters; }
 145                 }
 146
 147                 IDbTransaction IDbCommand.Transaction {
 148                         get { return Transaction; }
 149                         set {
 150                                 if (!(value is SybaseTransaction))
 151                                         throw new ArgumentException ();
 152                                 Transaction = (SybaseTransaction) value;
 153                         }
 154                 }
 155
 156                 public SybaseTransaction Transaction {
 157                         get { return transaction; }
 158                         set { transaction = value; }
 159                 }
 160
 161                 public UpdateRowSource UpdatedRowSource {
 162                         get { return updatedRowSource; }
 163                         set { updatedRowSource = value; }
 164                 }
 165
 166                 #endregion // Fields
 167
 168                 #region Methods
 169
 170                 public void Cancel ()
 171                 {
 172                         if (Connection == null || Connection.Tds == null)
 173                                 return;
 174                         Connection.Tds.Cancel ();
 175                 }
 176
 177                 internal void CloseDataReader (bool moreResults)
 178                 {
 179                         GetOutputParameters ();
 180                         Connection.DataReader = null;
 181
 182                         if ((behavior & CommandBehavior.CloseConnection) != 0)
 183                                 Connection.Close ();
 184                 }
 185
 186                 public SybaseParameter CreateParameter ()
 187                 {
 188                         return new SybaseParameter ();
 189                 }
 190
 191                 internal void DeriveParameters ()
 192                 {
 193                         if (commandType != CommandType.StoredProcedure)
 194                                 throw new InvalidOperationException (String.Format ("SybaseCommand DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
 195                         ValidateCommand ("DeriveParameters");
 196
 197                         SybaseParameterCollection localParameters = new SybaseParameterCollection (this);
 198                         localParameters.Add ("@P1", SybaseType.NVarChar, commandText.Length).Value = commandText;
 199
 200                         string sql = "sp_procedure_params_rowset";
 201
 202                         Connection.Tds.ExecProc (sql, localParameters.MetaParameters, 0, true);
 203
 204                         SybaseDataReader reader = new SybaseDataReader (this);
 205                         parameters.Clear ();
 206                         object[] dbValues = new object[reader.FieldCount];
 207
 208                         while (reader.Read ()) {
 209                                 reader.GetValues (dbValues);
 210                                 parameters.Add (new SybaseParameter (dbValues));
 211                         }
 212                         reader.Close ();
 213                 }
 214
 215                 private void Execute (CommandBehavior behavior, bool wantResults)
 216                 {
 217                         TdsMetaParameterCollection parms = Parameters.MetaParameters;
 218                         if (preparedStatement == null) {
 219                                 bool schemaOnly = ((CommandBehavior & CommandBehavior.SchemaOnly) > 0);
 220                                 bool keyInfo = ((CommandBehavior & CommandBehavior.SchemaOnly) > 0);
 221
 222                                 StringBuilder sql1 = new StringBuilder ();
 223                                 StringBuilder sql2 = new StringBuilder ();
 224
 225                                 if (schemaOnly || keyInfo)
 226                                         sql1.Append ("SET FMTONLY OFF;");
 227                                 if (keyInfo) {
 228                                         sql1.Append ("SET NO_BROWSETABLE ON;");
 229                                         sql2.Append ("SET NO_BROWSETABLE OFF;");
 230                                 }
 231                                 if (schemaOnly) {
 232                                         sql1.Append ("SET FMTONLY ON;");
 233                                         sql2.Append ("SET FMTONLY OFF;");
 234                                 }
 235
 236                                 switch (CommandType) {
 237                                 case CommandType.StoredProcedure:
 238                                         if (keyInfo || schemaOnly)
 239                                                 Connection.Tds.Execute (sql1.ToString ());
 240                                         Connection.Tds.ExecProc (CommandText, parms, CommandTimeout, wantResults);
 241                                         if (keyInfo || schemaOnly)
 242                                                 Connection.Tds.Execute (sql2.ToString ());
 243                                         break;
 244                                 case CommandType.Text:
 245                                         string sql = String.Format ("{0}{1}{2}", sql1.ToString (), CommandText, sql2.ToString ());
 246                                         Connection.Tds.Execute (sql, parms, CommandTimeout, wantResults);
 247                                         break;
 248                                 }
 249                         }
 250                         else
 251                                 Connection.Tds.ExecPrepared (preparedStatement, parms, CommandTimeout, wantResults);
 252                 }
 253
 254                 public int ExecuteNonQuery ()
 255                 {
 256                         ValidateCommand ("ExecuteNonQuery");
 257                         int result = 0;
 258
 259                         try {
 260                                 Execute (CommandBehavior.Default, false);
 261                         }
 262                         catch (TdsTimeoutException e) {
 263                                 throw SybaseException.FromTdsInternalException ((TdsInternalException) e);
 264                         }
 265
 266                         GetOutputParameters ();
 267                         return result;
 268                 }
 269
 270                 public SybaseDataReader ExecuteReader ()
 271                 {
 272                         return ExecuteReader (CommandBehavior.Default);
 273                 }
 274
 275                 public SybaseDataReader ExecuteReader (CommandBehavior behavior)
 276                 {
 277                         ValidateCommand ("ExecuteReader");
 278                         try {
 279                                 Execute (behavior, true);
 280                         }
 281                         catch (TdsTimeoutException e) {
 282                                 throw SybaseException.FromTdsInternalException ((TdsInternalException) e);
 283                         }
 284                         Connection.DataReader = new SybaseDataReader (this);
 285                         return Connection.DataReader;
 286                 }
 287
 288                 public object ExecuteScalar ()
 289                 {
 290                         ValidateCommand ("ExecuteScalar");
 291                         try {
 292                                 Execute (CommandBehavior.Default, true);
 293                         }
 294                         catch (TdsTimeoutException e) {
 295                                 throw SybaseException.FromTdsInternalException ((TdsInternalException) e);
 296                         }
 297
 298                         if (!Connection.Tds.NextResult () || !Connection.Tds.NextRow ())
 299                                 return null;
 300
 301                         object result = Connection.Tds.ColumnValues [0];
 302                         CloseDataReader (true);
 303                         return result;
 304                 }
 305
 306                 private void GetOutputParameters ()
 307                 {
 308                         Connection.Tds.SkipToEnd ();
 309
 310                         IList list = Connection.Tds.ColumnValues;
 311
 312                         if (list != null && list.Count > 0) {
 313                                 int index = 0;
 314                                 foreach (SybaseParameter parameter in parameters) {
 315                                         if (parameter.Direction != ParameterDirection.Input) {
 316                                                 parameter.Value = list [index];
 317                                                 index += 1;
 318                                         }
 319                                         if (index >= list.Count)
 320                                                 break;
 321                                 }
 322                         }
 323                 }
 324
 325                 object ICloneable.Clone ()
 326                 {
 327                         return new SybaseCommand (commandText, Connection);
 328                 }
 329
 330                 IDbDataParameter IDbCommand.CreateParameter ()
 331                 {
 332                         return CreateParameter ();
 333                 }
 334
 335                 IDataReader IDbCommand.ExecuteReader ()
 336                 {
 337                         return ExecuteReader ();
 338                 }
 339
 340                 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
 341                 {
 342                         return ExecuteReader (behavior);
 343                 }
 344
 345                 public void Prepare ()
 346                 {
 347                         ValidateCommand ("Prepare");
 348                         if (CommandType == CommandType.Text)
 349                                 preparedStatement = Connection.Tds.Prepare (CommandText, Parameters.MetaParameters);
 350                 }
 351
 352                 public void ResetCommandTimeout ()
 353                 {
 354                         commandTimeout = 30;
 355                 }
 356
 357                 private void Unprepare ()
 358                 {
 359                         Connection.Tds.Unprepare (preparedStatement);
 360                         preparedStatement = null;
 361                 }
 362
 363                 private void ValidateCommand (string method)
 364                 {
 365                         if (Connection == null)
 366                                 throw new InvalidOperationException (String.Format ("{0} requires a Connection object to continue.", method));
 367                         if (Connection.Transaction != null && transaction != Connection.Transaction)
 368                                 throw new InvalidOperationException ("The Connection object does not have the same transaction as the command object.");
 369                         if (Connection.State != ConnectionState.Open)
 370                                 throw new InvalidOperationException (String.Format ("ExecuteNonQuery requires an open Connection object to continue. This connection is closed.", method));
 371                         if (commandText == String.Empty || commandText == null)
 372                                 throw new InvalidOperationException ("The command text for this Command has not been set.");
 373                         if (Connection.DataReader != null)
 374                                 throw new InvalidOperationException ("There is already an open DataReader associated with this Connection which must be closed first.");
 375                 }
 376
 377                 #endregion // Methods
 378         }
 379 }