mcs/class/System.Data/System.Data.SqlClient/SqlCommand.cs

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