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.IO;
  42 using System.Collections;
  43 using System.Collections.Specialized;
  44 using System.ComponentModel;
  45 using System.Data;
  46 using System.Data.Common;
  47 #if NET_2_0
  48 using System.Data.Sql;
  49 #endif
  50 using System.Runtime.InteropServices;
  51 using System.Text;
  52 using System.Xml;
  53
  54 namespace System.Data.SqlClient {
  55         [DesignerAttribute ("Microsoft.VSDesigner.Data.VS.SqlCommandDesigner, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.ComponentModel.Design.IDesigner")]
  56         [ToolboxItemAttribute ("System.Drawing.Design.ToolboxItem, "+ Consts.AssemblySystem_Drawing)]
  57 #if NET_2_0
  58         [DefaultEventAttribute ("RecordsAffected")]
  59         public sealed class SqlCommand : DbCommand, IDbCommand, ICloneable
  60 #else
  61         public sealed class SqlCommand : Component, IDbCommand, ICloneable
  62 #endif // NET_2_0
  63         {
  64                 #region Fields
  65
  66                 const int DEFAULT_COMMAND_TIMEOUT = 30;
  67
  68                 int commandTimeout;
  69                 bool designTimeVisible;
  70                 string commandText;
  71                 CommandType commandType;
  72                 SqlConnection connection;
  73                 SqlTransaction transaction;
  74                 UpdateRowSource updatedRowSource;
  75                 CommandBehavior behavior = CommandBehavior.Default;
  76                 SqlParameterCollection parameters;
  77                 string preparedStatement;
  78 #if NET_2_0
  79                 bool disposed;
  80                 SqlNotificationRequest notification;
  81                 bool notificationAutoEnlist;
  82 #endif
  83
  84                 #endregion // Fields
  85
  86                 #region Constructors
  87
  88                 public SqlCommand()
  89                         : this (String.Empty, null, null)
  90                 {
  91                 }
  92
  93                 public SqlCommand (string cmdText)
  94                         : this (cmdText, null, null)
  95                 {
  96                 }
  97
  98                 public SqlCommand (string cmdText, SqlConnection connection)
  99                         : this (cmdText, connection, null)
 100                 {
 101                 }
 102
 103                 public SqlCommand (string cmdText, SqlConnection connection, SqlTransaction transaction)
 104                 {
 105                         this.commandText = cmdText;
 106                         this.connection = connection;
 107                         this.transaction = transaction;
 108                         this.commandType = CommandType.Text;
 109                         this.updatedRowSource = UpdateRowSource.Both;
 110
 111                         this.commandTimeout = DEFAULT_COMMAND_TIMEOUT;
 112 #if NET_2_0
 113                         notificationAutoEnlist = true;
 114 #endif
 115                         designTimeVisible = true;
 116                         parameters = new SqlParameterCollection (this);
 117                 }
 118
 119                 private SqlCommand(string commandText, SqlConnection connection, SqlTransaction transaction, CommandType commandType, UpdateRowSource updatedRowSource, bool designTimeVisible, int commandTimeout, SqlParameterCollection parameters)
 120                 {
 121                         this.commandText = commandText;
 122                         this.connection = connection;
 123                         this.transaction = transaction;
 124                         this.commandType = commandType;
 125                         this.updatedRowSource = updatedRowSource;
 126                         this.designTimeVisible = designTimeVisible;
 127                         this.commandTimeout = commandTimeout;
 128                         this.parameters = new SqlParameterCollection(this);
 129                         for (int i = 0;i < parameters.Count;i++)
 130                                 this.parameters.Add(((ICloneable)parameters[i]).Clone());
 131                 }
 132
 133                 #endregion // Constructors
 134
 135                 #region Properties
 136
 137                 internal CommandBehavior CommandBehavior {
 138                         get { return behavior; }
 139                 }
 140
 141 #if !NET_2_0
 142                 [DataSysDescription ("Command text to execute.")]
 143 #endif
 144                 [DefaultValue ("")]
 145                 [EditorAttribute ("Microsoft.VSDesigner.Data.SQL.Design.SqlCommandTextEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
 146                 [RefreshProperties (RefreshProperties.All)]
 147                 public
 148 #if NET_2_0
 149                 override
 150 #endif //NET_2_0
 151                 string CommandText {
 152                         get {
 153                                 if (commandText == null)
 154                                         return string.Empty;
 155                                 return commandText;
 156                         }
 157                         set {
 158                                 if (value != commandText && preparedStatement != null)
 159                                         Unprepare ();
 160                                 commandText = value;
 161                         }
 162                 }
 163
 164 #if !NET_2_0
 165                 [DataSysDescription ("Time to wait for command to execute.")]
 166                 [DefaultValue (DEFAULT_COMMAND_TIMEOUT)]
 167 #endif
 168                 public
 169 #if NET_2_0
 170                 override
 171 #endif //NET_2_0
 172                 int CommandTimeout {
 173                         get { return commandTimeout; }
 174                         set {
 175                                 if (value < 0)
 176 #if NET_2_0
 177                                         throw new ArgumentException ("The property value assigned is less than 0.",
 178                                                 "CommandTimeout");
 179 #else
 180                                         throw new ArgumentException ("The property value assigned is less than 0.");
 181 #endif
 182                                 commandTimeout = value;
 183                         }
 184                 }
 185
 186 #if !NET_2_0
 187                 [DataSysDescription ("How to interpret the CommandText.")]
 188 #endif
 189                 [DefaultValue (CommandType.Text)]
 190                 [RefreshProperties (RefreshProperties.All)]
 191                 public
 192 #if NET_2_0
 193                 override
 194 #endif //NET_2_0
 195                 CommandType CommandType {
 196                         get { return commandType; }
 197                         set {
 198                                 if (value == CommandType.TableDirect)
 199 #if NET_2_0
 200                                         throw new ArgumentOutOfRangeException ("CommandType.TableDirect is not supported " +
 201                                                 "by the Mono SqlClient Data Provider.");
 202 #else
 203                                         throw new ArgumentException ("CommandType.TableDirect is not supported by the Mono SqlClient Data Provider.");
 204 #endif
 205
 206                                 ExceptionHelper.CheckEnumValue (typeof (CommandType), value);
 207                                 commandType = value;
 208                         }
 209                 }
 210
 211                 [DefaultValue (null)]
 212 #if !NET_2_0
 213                 [DataSysDescription ("Connection used by the command.")]
 214 #endif
 215                 [EditorAttribute ("Microsoft.VSDesigner.Data.Design.DbConnectionEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
 216                 public
 217 #if NET_2_0
 218                 new
 219 #endif //NET_2_0
 220                 SqlConnection Connection {
 221                         get { return connection; }
 222                         set
 223                         {
 224 #if ONLY_1_1
 225                                 if (connection != null && connection.DataReader != null)
 226                                         throw new InvalidOperationException ("The connection is busy fetching data.");
 227 #endif
 228                                 connection = value;
 229                         }
 230                 }
 231
 232                 [Browsable (false)]
 233                 [DefaultValue (true)]
 234                 [DesignOnly (true)]
 235 #if NET_2_0
 236                 [EditorBrowsable (EditorBrowsableState.Never)]
 237 #endif
 238                 public
 239 #if NET_2_0
 240                 override
 241 #endif //NET_2_0
 242                 bool DesignTimeVisible {
 243                         get { return designTimeVisible; }
 244                         set { designTimeVisible = value; }
 245                 }
 246
 247 #if !NET_2_0
 248                 [DataSysDescription ("The parameters collection.")]
 249 #endif
 250                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Content)]
 251                 public
 252 #if NET_2_0
 253                 new
 254 #endif //NET_2_0
 255                 SqlParameterCollection Parameters {
 256                         get { return parameters; }
 257                 }
 258
 259                 internal Tds Tds {
 260                         get { return Connection.Tds; }
 261                 }
 262
 263 #if !NET_2_0
 264                 IDbConnection IDbCommand.Connection {
 265                         get { return Connection; }
 266                         set {
 267                                 if (!(value == null || value is SqlConnection))
 268                                         throw new InvalidCastException ("The value was not a valid SqlConnection.");
 269                                 Connection = (SqlConnection) value;
 270                         }
 271                 }
 272
 273                 IDataParameterCollection IDbCommand.Parameters {
 274                         get { return Parameters; }
 275                 }
 276
 277                 IDbTransaction IDbCommand.Transaction {
 278                         get { return Transaction; }
 279                         set {
 280                                 if (!(value == null || value is SqlTransaction))
 281                                         throw new ArgumentException ();
 282                                 Transaction = (SqlTransaction) value;
 283                         }
 284                 }
 285 #endif
 286
 287                 [Browsable (false)]
 288 #if !NET_2_0
 289                 [DataSysDescription ("The transaction used by the command.")]
 290 #endif
 291                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 292                 public new SqlTransaction Transaction {
 293                         get {
 294                                 if (transaction != null && !transaction.IsOpen)
 295                                         transaction = null;
 296                                 return transaction;
 297                         }
 298                         set
 299                         {
 300 #if ONLY_1_1
 301                                 if (connection != null && connection.DataReader != null)
 302                                         throw new InvalidOperationException ("The connection is busy fetching data.");
 303 #endif
 304                                 transaction = value;
 305                         }
 306                 }
 307
 308 #if !NET_2_0
 309                 [DataSysDescription ("When used by a DataAdapter.Update, how command results are applied to the current DataRow.")]
 310 #endif
 311                 [DefaultValue (UpdateRowSource.Both)]
 312                 public
 313 #if NET_2_0
 314                 override
 315 #endif // NET_2_0
 316                 UpdateRowSource UpdatedRowSource {
 317                         get { return updatedRowSource; }
 318                         set {
 319                                 ExceptionHelper.CheckEnumValue (typeof (UpdateRowSource), value);
 320                                 updatedRowSource = value;
 321                         }
 322                 }
 323
 324 #if NET_2_0
 325                 [Browsable (false)]
 326                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 327                 public SqlNotificationRequest Notification {
 328                         get { return notification; }
 329                         set { notification = value; }
 330                 }
 331
 332                 [DefaultValue (true)]
 333                 public bool NotificationAutoEnlist {
 334                         get { return notificationAutoEnlist; }
 335                         set { notificationAutoEnlist = value; }
 336                 }
 337 #endif
 338                 #endregion // Fields
 339
 340                 #region Methods
 341
 342                 public
 343 #if NET_2_0
 344                 override
 345 #endif // NET_2_0
 346                 void Cancel ()
 347                 {
 348                         if (Connection == null || Connection.Tds == null)
 349                                 return;
 350                         Connection.Tds.Cancel ();
 351                 }
 352
 353 #if NET_2_0
 354                 public SqlCommand Clone ()
 355                 {
 356                         return new SqlCommand (commandText, connection, transaction, commandType, updatedRowSource, designTimeVisible, commandTimeout, parameters);
 357                 }
 358 #endif // NET_2_0
 359
 360                 internal void CloseDataReader ()
 361                 {
 362                         if (Connection != null) {
 363                                 Connection.DataReader = null;
 364
 365                                 if ((behavior & CommandBehavior.CloseConnection) != 0)
 366                                         Connection.Close ();
 367
 368                                 if (Tds != null)
 369                                         Tds.SequentialAccess = false;
 370                         }
 371
 372                         // Reset the behavior
 373                         behavior = CommandBehavior.Default;
 374                 }
 375
 376                 public new SqlParameter CreateParameter ()
 377                 {
 378                         return new SqlParameter ();
 379                 }
 380
 381                 private string EscapeProcName (string name, bool schema)
 382                 {
 383                         string procName;
 384                         string tmpProcName = name.Trim ();
 385                         int procNameLen = tmpProcName.Length;
 386                         char[] brkts = new char [] {'[', ']'};
 387                         bool foundMatching = false;
 388                         int start = 0, count = procNameLen;
 389                         int sindex = -1, eindex = -1;
 390
 391                         // We try to match most of the "brackets" combination here, however
 392                         // there could be other combinations that may generate a different type
 393                         // of exception in MS.NET
 394
 395                         if (procNameLen > 1) {
 396                                 if ((sindex = tmpProcName.IndexOf ('[')) <= 0)
 397                                         foundMatching = true;
 398                                 else
 399                                         foundMatching = false;
 400
 401                                 if (foundMatching == true && sindex > -1) {
 402                                         eindex = tmpProcName.IndexOf (']');
 403                                         if (sindex > eindex && eindex != -1) {
 404                                                 foundMatching = false;
 405                                         } else if (eindex == procNameLen-1) {
 406                                                 if (tmpProcName.IndexOfAny (brkts, 1, procNameLen-2) != -1) {
 407                                                         foundMatching = false;
 408                                                 } else {
 409                                                         start = 1;
 410                                                         count = procNameLen - 2;
 411                                                 }
 412                                         } else if (eindex == -1 && schema) {
 413                                                 foundMatching = true;
 414                                         } else {
 415                                                 foundMatching = false;
 416                                         }
 417                                 }
 418
 419                                 if (foundMatching)
 420                                         procName = tmpProcName.Substring (start, count);
 421                                 else
 422                                         throw new ArgumentException (String.Format ("SqlCommand.CommandText property value is an invalid multipart name {0}, incorrect usage of quotes", CommandText));
 423                         } else {
 424                                 procName = tmpProcName;
 425                         }
 426
 427                         return procName;
 428                 }
 429                 internal void DeriveParameters ()
 430                 {
 431                         if (commandType != CommandType.StoredProcedure)
 432                                 throw new InvalidOperationException (String.Format ("SqlCommand DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
 433                         ValidateCommand ("DeriveParameters", false);
 434
 435                         string procName = CommandText;
 436                         string schemaName = String.Empty;
 437                         int dotPosition = procName.LastIndexOf ('.');
 438
 439                         // Procedure name can be: [database].[user].[procname]
 440                         if (dotPosition >= 0) {
 441                                 schemaName = procName.Substring (0, dotPosition);
 442                                 procName = procName.Substring (dotPosition + 1);
 443                                 if ((dotPosition = schemaName.LastIndexOf ('.')) >= 0)
 444                                         schemaName = schemaName.Substring (dotPosition + 1);
 445                         }
 446
 447                         procName = EscapeProcName (procName, false);
 448                         schemaName = EscapeProcName (schemaName, true);
 449
 450                         SqlParameterCollection localParameters = new SqlParameterCollection (this);
 451                         localParameters.Add ("@procedure_name", SqlDbType.NVarChar, procName.Length).Value = procName;
 452                         if (schemaName.Length > 0)
 453                                 localParameters.Add ("@procedure_schema", SqlDbType.NVarChar, schemaName.Length).Value = schemaName;
 454
 455                         string sql = "sp_procedure_params_rowset";
 456
 457                         try {
 458                                 Connection.Tds.ExecProc (sql, localParameters.MetaParameters, 0, true);
 459                         } catch (TdsTimeoutException ex) {
 460                                 Connection.Tds.Reset ();
 461                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 462                         } catch (TdsInternalException ex) {
 463                                 Connection.Close ();
 464                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 465                         }
 466
 467                         SqlDataReader reader = new SqlDataReader (this);
 468                         parameters.Clear ();
 469                         object[] dbValues = new object[reader.FieldCount];
 470
 471                         while (reader.Read ()) {
 472                                 reader.GetValues (dbValues);
 473                                 parameters.Add (new SqlParameter (dbValues));
 474                         }
 475                         reader.Close ();
 476
 477                         if (parameters.Count == 0)
 478                                 throw new InvalidOperationException ("Stored procedure '" + procName + "' does not exist.");
 479                 }
 480
 481                 private void Execute (bool wantResults)
 482                 {
 483                         int index = 0;
 484                         Connection.Tds.RecordsAffected = -1;
 485                         TdsMetaParameterCollection parms = Parameters.MetaParameters;
 486                         foreach (TdsMetaParameter param in parms) {
 487                                 param.Validate (index++);
 488                         }
 489
 490                         if (preparedStatement == null) {
 491                                 bool schemaOnly = ((behavior & CommandBehavior.SchemaOnly) > 0);
 492                                 bool keyInfo = ((behavior & CommandBehavior.KeyInfo) > 0);
 493
 494                                 StringBuilder sql1 = new StringBuilder ();
 495                                 StringBuilder sql2 = new StringBuilder ();
 496
 497                                 if (schemaOnly || keyInfo)
 498                                         sql1.Append ("SET FMTONLY OFF;");
 499                                 if (keyInfo) {
 500                                         sql1.Append ("SET NO_BROWSETABLE ON;");
 501                                         sql2.Append ("SET NO_BROWSETABLE OFF;");
 502                                 }
 503                                 if (schemaOnly) {
 504                                         sql1.Append ("SET FMTONLY ON;");
 505                                         sql2.Append ("SET FMTONLY OFF;");
 506                                 }
 507
 508                                 switch (CommandType) {
 509                                 case CommandType.StoredProcedure:
 510                                         try {
 511                                                 if (keyInfo || schemaOnly)
 512                                                         Connection.Tds.Execute (sql1.ToString ());
 513                                                 Connection.Tds.ExecProc (CommandText, parms, CommandTimeout, wantResults);
 514                                                 if (keyInfo || schemaOnly)
 515                                                         Connection.Tds.Execute (sql2.ToString ());
 516                                         } catch (TdsTimeoutException ex) {
 517                                                 // If it is a timeout exception there can be many reasons:
 518                                                 // 1) Network is down/server is down/not reachable
 519                                                 // 2) Somebody has an exclusive lock on Table/DB
 520                                                 // In any of these cases, don't close the connection. Let the user do it
 521                                                 Connection.Tds.Reset ();
 522                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 523                                         } catch (TdsInternalException ex) {
 524                                                 Connection.Close ();
 525                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 526                                         }
 527                                         break;
 528                                 case CommandType.Text:
 529                                         string sql;
 530                                         if (sql2.Length > 0) {
 531                                                 sql = String.Format ("{0}{1};{2}", sql1.ToString (), CommandText, sql2.ToString ());
 532                                         } else {
 533                                                 sql = String.Format ("{0}{1}", sql1.ToString (), CommandText);
 534                                         }
 535                                         try {
 536                                                 Connection.Tds.Execute (sql, parms, CommandTimeout, wantResults);
 537                                         } catch (TdsTimeoutException ex) {
 538                                                 Connection.Tds.Reset ();
 539                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 540                                         } catch (TdsInternalException ex) {
 541                                                 Connection.Close ();
 542                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 543                                         }
 544                                         break;
 545                                 }
 546                         }
 547                         else {
 548                                 try {
 549                                         Connection.Tds.ExecPrepared (preparedStatement, parms, CommandTimeout, wantResults);
 550                                 } catch (TdsTimeoutException ex) {
 551                                         Connection.Tds.Reset ();
 552                                         throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 553                                 } catch (TdsInternalException ex) {
 554                                         Connection.Close ();
 555                                         throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 556                                 }
 557                         }
 558                 }
 559
 560                 public
 561 #if NET_2_0
 562                 override
 563 #endif // NET_2_0
 564                 int ExecuteNonQuery ()
 565                 {
 566                         ValidateCommand ("ExecuteNonQuery", false);
 567                         int result = 0;
 568                         behavior = CommandBehavior.Default;
 569
 570                         try {
 571                                 Execute (false);
 572                                 result = Connection.Tds.RecordsAffected;
 573                         } catch (TdsTimeoutException e) {
 574                                 Connection.Tds.Reset ();
 575                                 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
 576                         }
 577
 578                         GetOutputParameters ();
 579                         return result;
 580                 }
 581
 582                 public new SqlDataReader ExecuteReader ()
 583                 {
 584                         return ExecuteReader (CommandBehavior.Default);
 585                 }
 586
 587                 public new SqlDataReader ExecuteReader (CommandBehavior behavior)
 588                 {
 589                         ValidateCommand ("ExecuteReader", false);
 590                         if ((behavior & CommandBehavior.SingleRow) != 0)
 591                                 behavior |= CommandBehavior.SingleResult;
 592                         this.behavior = behavior;
 593                         if ((behavior & CommandBehavior.SequentialAccess) != 0)
 594                                 Tds.SequentialAccess = true;
 595                         try {
 596                                 Execute (true);
 597                                 Connection.DataReader = new SqlDataReader (this);
 598                                 return Connection.DataReader;
 599                         } catch {
 600                                 if ((behavior & CommandBehavior.CloseConnection) != 0)
 601                                         Connection.Close ();
 602                                 throw;
 603                         }
 604                 }
 605
 606                 public
 607 #if NET_2_0
 608                 override
 609 #endif // NET_2_0
 610                 object ExecuteScalar ()
 611                 {
 612                         try {
 613                                 object result = null;
 614 #if NET_2_0
 615                                 ValidateCommand ("ExecuteScalar", false);
 616 #else
 617                                 ValidateCommand ("ExecuteReader", false);
 618 #endif
 619                                 behavior = CommandBehavior.Default;
 620                                 Execute (true);
 621
 622                                 try {
 623                                         if (Connection.Tds.NextResult () && Connection.Tds.NextRow ())
 624                                                 result = Connection.Tds.ColumnValues[0];
 625
 626                                         if (commandType == CommandType.StoredProcedure) {
 627                                                 Connection.Tds.SkipToEnd ();
 628                                                 GetOutputParameters ();
 629                                         }
 630                                 } catch (TdsTimeoutException ex) {
 631                                         Connection.Tds.Reset ();
 632                                         throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 633                                 } catch (TdsInternalException ex) {
 634                                         Connection.Close ();
 635                                         throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 636                                 }
 637
 638                                 return result;
 639                         } finally {
 640                                 CloseDataReader ();
 641                         }
 642                 }
 643
 644                 public XmlReader ExecuteXmlReader ()
 645                 {
 646                         ValidateCommand ("ExecuteXmlReader", false);
 647                         behavior = CommandBehavior.Default;
 648                         try {
 649                                 Execute (true);
 650                         } catch (TdsTimeoutException e) {
 651                                 Connection.Tds.Reset ();
 652                                 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
 653                         }
 654
 655                         SqlDataReader dataReader = new SqlDataReader (this);
 656                         SqlXmlTextReader textReader = new SqlXmlTextReader (dataReader);
 657                         XmlReader xmlReader = new XmlTextReader (textReader);
 658                         return xmlReader;
 659                 }
 660
 661                 internal void GetOutputParameters ()
 662                 {
 663                         IList list = Connection.Tds.OutputParameters;
 664
 665                         if (list != null && list.Count > 0) {
 666
 667                                 int index = 0;
 668                                 foreach (SqlParameter parameter in parameters) {
 669                                         if (parameter.Direction != ParameterDirection.Input &&
 670                                             parameter.Direction != ParameterDirection.ReturnValue) {
 671                                                 parameter.Value = list [index];
 672                                                 index += 1;
 673                                         }
 674                                         if (index >= list.Count)
 675                                                 break;
 676                                 }
 677                         }
 678                 }
 679
 680                 object ICloneable.Clone ()
 681                 {
 682                         return new SqlCommand (commandText, connection, transaction, commandType, updatedRowSource, designTimeVisible, commandTimeout, parameters);
 683
 684                 }
 685
 686 #if !NET_2_0
 687                 IDbDataParameter IDbCommand.CreateParameter ()
 688                 {
 689                         return CreateParameter ();
 690                 }
 691
 692                 IDataReader IDbCommand.ExecuteReader ()
 693                 {
 694                         return ExecuteReader ();
 695                 }
 696
 697                 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
 698                 {
 699                         return ExecuteReader (behavior);
 700                 }
 701 #endif
 702
 703 #if NET_2_0
 704                 protected override void Dispose (bool disposing)
 705                 {
 706                         if (disposed) return;
 707                         if (disposing) {
 708                                 parameters.Clear();
 709                                 if (Connection != null)
 710                                         Connection.DataReader = null;
 711                         }
 712                         base.Dispose (disposing);
 713                         disposed = true;
 714                 }
 715 #endif
 716
 717                 public
 718 #if NET_2_0
 719                 override
 720 #endif // NET_2_0
 721                 void Prepare ()
 722                 {
 723 #if NET_2_0
 724                         if (Connection == null)
 725                                 throw new NullReferenceException ();
 726 #endif
 727
 728                         if (CommandType == CommandType.StoredProcedure || CommandType == CommandType.Text && Parameters.Count == 0)
 729                                 return;
 730
 731                         ValidateCommand ("Prepare", false);
 732
 733                         try {
 734                                 foreach (SqlParameter param in Parameters)
 735                                         param.CheckIfInitialized ();
 736                         } catch (Exception e) {
 737                                 throw new InvalidOperationException ("SqlCommand.Prepare requires " + e.Message);
 738                         }
 739
 740                         preparedStatement = Connection.Tds.Prepare (CommandText, Parameters.MetaParameters);
 741                 }
 742
 743                 public void ResetCommandTimeout ()
 744                 {
 745                         commandTimeout = DEFAULT_COMMAND_TIMEOUT;
 746                 }
 747
 748                 private void Unprepare ()
 749                 {
 750                         Connection.Tds.Unprepare (preparedStatement);
 751                         preparedStatement = null;
 752                 }
 753
 754                 private void ValidateCommand (string method, bool async)
 755                 {
 756                         if (Connection == null)
 757                                 throw new InvalidOperationException (String.Format ("{0}: A Connection object is required to continue.", method));
 758                         if (Transaction == null && Connection.Transaction != null)
 759                                 throw new InvalidOperationException (String.Format (
 760                                         "{0} requires a transaction if the command's connection is in a pending transaction.",
 761 #if NET_2_0
 762                                         method));
 763 #else
 764                                         "Execute"));
 765 #endif
 766                         if (Transaction != null && Transaction.Connection != Connection)
 767                                 throw new InvalidOperationException ("The connection does not have the same transaction as the command.");
 768                         if (Connection.State != ConnectionState.Open)
 769                                 throw new InvalidOperationException (String.Format ("{0} requires an open connection to continue. This connection is closed.", method));
 770                         if (CommandText.Length == 0)
 771                                 throw new InvalidOperationException (String.Format ("{0}: CommandText has not been set for this Command.", method));
 772                         if (Connection.DataReader != null)
 773                                 throw new InvalidOperationException ("There is already an open DataReader associated with this Connection which must be closed first.");
 774                         if (Connection.XmlReader != null)
 775                                 throw new InvalidOperationException ("There is already an open XmlReader associated with this Connection which must be closed first.");
 776 #if NET_2_0
 777                         if (async && !Connection.AsyncProcessing)
 778                                 throw new InvalidOperationException ("This Connection object is not " +
 779                                         "in Asynchronous mode. Use 'Asynchronous" +
 780                                         " Processing = true' to set it.");
 781 #endif // NET_2_0
 782                 }
 783
 784 #if NET_2_0
 785                 protected override DbParameter CreateDbParameter ()
 786                 {
 787                         return CreateParameter ();
 788                 }
 789
 790                 protected override DbDataReader ExecuteDbDataReader (CommandBehavior behavior)
 791                 {
 792                         return ExecuteReader (behavior);
 793                 }
 794
 795                 protected override DbConnection DbConnection {
 796                         get { return Connection; }
 797                         set { Connection = (SqlConnection) value; }
 798                 }
 799
 800                 protected override DbParameterCollection DbParameterCollection {
 801                         get { return Parameters; }
 802                 }
 803
 804                 protected override DbTransaction DbTransaction {
 805                         get { return Transaction; }
 806                         set { Transaction = (SqlTransaction) value; }
 807                 }
 808 #endif // NET_2_0
 809
 810                 #endregion // Methods
 811
 812 #if NET_2_0
 813                 #region Asynchronous Methods
 814
 815                 internal IAsyncResult BeginExecuteInternal (CommandBehavior behavior,
 816                                                                                                         bool wantResults,
 817                                                                                                         AsyncCallback callback,
 818                                                                                                         object state)
 819                 {
 820                         IAsyncResult ar = null;
 821                         Connection.Tds.RecordsAffected = -1;
 822                         TdsMetaParameterCollection parms = Parameters.MetaParameters;
 823                         if (preparedStatement == null) {
 824                                 bool schemaOnly = ((behavior & CommandBehavior.SchemaOnly) > 0);
 825                                 bool keyInfo = ((behavior & CommandBehavior.KeyInfo) > 0);
 826
 827                                 StringBuilder sql1 = new StringBuilder ();
 828                                 StringBuilder sql2 = new StringBuilder ();
 829
 830                                 if (schemaOnly || keyInfo)
 831                                         sql1.Append ("SET FMTONLY OFF;");
 832                                 if (keyInfo) {
 833                                         sql1.Append ("SET NO_BROWSETABLE ON;");
 834                                         sql2.Append ("SET NO_BROWSETABLE OFF;");
 835                                 }
 836                                 if (schemaOnly) {
 837                                         sql1.Append ("SET FMTONLY ON;");
 838                                         sql2.Append ("SET FMTONLY OFF;");
 839                                 }
 840                                 switch (CommandType) {
 841                                 case CommandType.StoredProcedure:
 842                                         string prolog = "";
 843                                         string epilog = "";
 844                                         if (keyInfo || schemaOnly)
 845                                                 prolog = sql1.ToString ();
 846                                         if (keyInfo || schemaOnly)
 847                                                 epilog = sql2.ToString ();
 848                                         try {
 849                                                 ar = Connection.Tds.BeginExecuteProcedure (prolog,
 850                                                                                       epilog,
 851                                                                                       CommandText,
 852                                                                                       !wantResults,
 853                                                                                       parms,
 854                                                                                       callback,
 855                                                                                       state);
 856                                         } catch (TdsTimeoutException ex) {
 857                                                 Connection.Tds.Reset ();
 858                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 859                                         } catch (TdsInternalException ex) {
 860                                                 Connection.Close ();
 861                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 862                                         }
 863                                         break;
 864                                 case CommandType.Text:
 865                                         string sql = String.Format ("{0}{1};{2}", sql1.ToString (), CommandText, sql2.ToString ());
 866                                         try {
 867                                                 if (wantResults)
 868                                                         ar = Connection.Tds.BeginExecuteQuery (sql, parms, callback, state);
 869                                                 else
 870                                                         ar = Connection.Tds.BeginExecuteNonQuery (sql, parms, callback, state);
 871                                         } catch (TdsTimeoutException ex) {
 872                                                 Connection.Tds.Reset ();
 873                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 874                                         } catch (TdsInternalException ex) {
 875                                                 Connection.Close ();
 876                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 877                                         }
 878                                         break;
 879                                 }
 880                         }
 881                         else {
 882                                 try {
 883                                         Connection.Tds.ExecPrepared (preparedStatement, parms, CommandTimeout, wantResults);
 884                                 } catch (TdsTimeoutException ex) {
 885                                         Connection.Tds.Reset ();
 886                                         throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 887                                 } catch (TdsInternalException ex) {
 888                                         Connection.Close ();
 889                                         throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 890                                 }
 891                         }
 892                         return ar;
 893                 }
 894
 895                 internal void EndExecuteInternal (IAsyncResult ar)
 896                 {
 897                         SqlAsyncResult sqlResult = ( (SqlAsyncResult) ar);
 898                         Connection.Tds.WaitFor (sqlResult.InternalResult);
 899                         Connection.Tds.CheckAndThrowException (sqlResult.InternalResult);
 900                 }
 901
 902                 public IAsyncResult BeginExecuteNonQuery ()
 903                 {
 904                         return BeginExecuteNonQuery (null, null);
 905                 }
 906
 907                 public IAsyncResult BeginExecuteNonQuery (AsyncCallback callback, object stateObject)
 908                 {
 909                         ValidateCommand ("BeginExecuteNonQuery", true);
 910                         SqlAsyncResult ar = new SqlAsyncResult (callback, stateObject);
 911                         ar.EndMethod = "EndExecuteNonQuery";
 912                         ar.InternalResult = BeginExecuteInternal (CommandBehavior.Default, false, ar.BubbleCallback, ar);
 913                         return ar;
 914                 }
 915
 916                 public int EndExecuteNonQuery (IAsyncResult asyncResult)
 917                 {
 918                         ValidateAsyncResult (asyncResult, "EndExecuteNonQuery");
 919                         EndExecuteInternal (asyncResult);
 920
 921                         int ret = Connection.Tds.RecordsAffected;
 922
 923                         GetOutputParameters ();
 924                         ((SqlAsyncResult) asyncResult).Ended = true;
 925                         return ret;
 926                 }
 927
 928                 public IAsyncResult BeginExecuteReader ()
 929                 {
 930                         return BeginExecuteReader (null, null, CommandBehavior.Default);
 931                 }
 932
 933                 public IAsyncResult BeginExecuteReader (CommandBehavior behavior)
 934                 {
 935                         return BeginExecuteReader (null, null, behavior);
 936                 }
 937
 938                 public IAsyncResult BeginExecuteReader (AsyncCallback callback, object stateObject)
 939                 {
 940                         return BeginExecuteReader (callback, stateObject, CommandBehavior.Default);
 941                 }
 942
 943                 public IAsyncResult BeginExecuteReader (AsyncCallback callback, object stateObject, CommandBehavior behavior)
 944                 {
 945                         ValidateCommand ("BeginExecuteReader", true);
 946                         this.behavior = behavior;
 947                         SqlAsyncResult ar = new SqlAsyncResult (callback, stateObject);
 948                         ar.EndMethod = "EndExecuteReader";
 949                         IAsyncResult tdsResult = BeginExecuteInternal (behavior, true,
 950                                 ar.BubbleCallback, stateObject);
 951                         ar.InternalResult = tdsResult;
 952                         return ar;
 953                 }
 954
 955                 public SqlDataReader EndExecuteReader (IAsyncResult asyncResult)
 956                 {
 957                         ValidateAsyncResult (asyncResult, "EndExecuteReader");
 958                         EndExecuteInternal (asyncResult);
 959                         SqlDataReader reader = null;
 960                         try {
 961                                 reader = new SqlDataReader (this);
 962                         } catch (TdsTimeoutException e) {
 963                                 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
 964                         } catch (TdsInternalException e) {
 965                                 // if behavior is closeconnection, even if it throws exception
 966                                 // the connection has to be closed.
 967                                 if ((behavior & CommandBehavior.CloseConnection) != 0)
 968                                         Connection.Close ();
 969                                 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
 970                         }
 971
 972                         ((SqlAsyncResult) asyncResult).Ended = true;
 973                         return reader;
 974                 }
 975
 976                 public IAsyncResult BeginExecuteXmlReader (AsyncCallback callback, object stateObject)
 977                 {
 978                         ValidateCommand ("BeginExecuteXmlReader", true);
 979                         SqlAsyncResult ar = new SqlAsyncResult (callback, stateObject);
 980                         ar.EndMethod = "EndExecuteXmlReader";
 981                         ar.InternalResult = BeginExecuteInternal (behavior, true,
 982                                 ar.BubbleCallback, stateObject);
 983                         return ar;
 984                 }
 985
 986                 public IAsyncResult BeginExecuteXmlReader ()
 987                 {
 988                         return BeginExecuteXmlReader (null, null);
 989                 }
 990
 991
 992                 public XmlReader EndExecuteXmlReader (IAsyncResult asyncResult)
 993                 {
 994                         ValidateAsyncResult (asyncResult, "EndExecuteXmlReader");
 995                         EndExecuteInternal (asyncResult);
 996                         SqlDataReader reader = new SqlDataReader (this);
 997                         SqlXmlTextReader textReader = new SqlXmlTextReader (reader);
 998                         XmlReader xmlReader = new XmlTextReader (textReader);
 999                         ((SqlAsyncResult) asyncResult).Ended = true;
1000                         return xmlReader;
1001                 }
1002
1003                 internal void ValidateAsyncResult (IAsyncResult ar, string endMethod)
1004                 {
1005                         if (ar == null)
1006                                 throw new ArgumentException ("result passed is null!");
1007                         if (! (ar is SqlAsyncResult))
1008                                 throw new ArgumentException (String.Format ("cannot test validity of types {0}",
1009                                         ar.GetType ()));
1010                         SqlAsyncResult result = (SqlAsyncResult) ar;
1011                         if (result.EndMethod != endMethod)
1012                                 throw new InvalidOperationException (String.Format ("Mismatched {0} called for AsyncResult. " +
1013                                         "Expected call to {1} but {0} is called instead.",
1014                                         endMethod, result.EndMethod));
1015                         if (result.Ended)
1016                                 throw new InvalidOperationException (String.Format ("The method {0} cannot be called " +
1017                                         "more than once for the same AsyncResult.", endMethod));
1018                 }
1019
1020                 #endregion // Asynchronous Methods
1021
1022                 public event StatementCompletedEventHandler StatementCompleted;
1023 #endif // NET_2_0
1024         }
1025 }