mcs/class/Mono.Data.TdsClient/Mono.Data.TdsClient/TdsCommand.cs

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