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                 bool disposed;
  69                 int commandTimeout;
  70                 bool designTimeVisible;
  71                 string commandText;
  72                 CommandType commandType;
  73                 SqlConnection connection;
  74                 SqlTransaction transaction;
  75                 UpdateRowSource updatedRowSource;
  76                 CommandBehavior behavior = CommandBehavior.Default;
  77                 SqlParameterCollection parameters;
  78                 string preparedStatement;
  79 #if NET_2_0
  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                                         throw new ArgumentException ("The property value assigned is less than 0.");
 177                                 commandTimeout = value;
 178                         }
 179                 }
 180
 181 #if !NET_2_0
 182                 [DataSysDescription ("How to interpret the CommandText.")]
 183 #endif
 184                 [DefaultValue (CommandType.Text)]
 185                 [RefreshProperties (RefreshProperties.All)]
 186                 public
 187 #if NET_2_0
 188                 override
 189 #endif //NET_2_0
 190                 CommandType CommandType {
 191                         get { return commandType; }
 192                         set {
 193                                 if (value == CommandType.TableDirect)
 194 #if NET_2_0
 195                                         throw new ArgumentOutOfRangeException ("CommandType.TableDirect is not supported " +
 196                                                 "by the Mono SqlClient Data Provider.");
 197 #else
 198                                         throw new ArgumentException ("CommandType.TableDirect is not supported by the Mono SqlClient Data Provider.");
 199 #endif
 200
 201                                 ExceptionHelper.CheckEnumValue (typeof (CommandType), value);
 202                                 commandType = value;
 203                         }
 204                 }
 205
 206                 [DefaultValue (null)]
 207 #if !NET_2_0
 208                 [DataSysDescription ("Connection used by the command.")]
 209 #endif
 210                 [EditorAttribute ("Microsoft.VSDesigner.Data.Design.DbConnectionEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
 211                 public
 212 #if NET_2_0
 213                 new
 214 #endif //NET_2_0
 215                 SqlConnection Connection {
 216                         get { return connection; }
 217                         set
 218                         {
 219 #if ONLY_1_1
 220                                 if (connection != null && connection.DataReader != null)
 221                                         throw new InvalidOperationException ("The connection is busy fetching data.");
 222 #endif
 223                                 connection = value;
 224                         }
 225                 }
 226
 227                 [Browsable (false)]
 228                 [DefaultValue (true)]
 229                 [DesignOnly (true)]
 230 #if NET_2_0
 231                 [EditorBrowsable (EditorBrowsableState.Never)]
 232 #endif
 233                 public
 234 #if NET_2_0
 235                 override
 236 #endif //NET_2_0
 237                 bool DesignTimeVisible {
 238                         get { return designTimeVisible; }
 239                         set { designTimeVisible = value; }
 240                 }
 241
 242 #if !NET_2_0
 243                 [DataSysDescription ("The parameters collection.")]
 244 #endif
 245                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Content)]
 246                 public
 247 #if NET_2_0
 248                 new
 249 #endif //NET_2_0
 250                 SqlParameterCollection Parameters {
 251                         get { return parameters; }
 252                 }
 253
 254                 internal Tds Tds {
 255                         get { return Connection.Tds; }
 256                 }
 257
 258 #if !NET_2_0
 259                 IDbConnection IDbCommand.Connection {
 260                         get { return Connection; }
 261                         set {
 262                                 if (!(value == null || value is SqlConnection))
 263                                         throw new InvalidCastException ("The value was not a valid SqlConnection.");
 264                                 Connection = (SqlConnection) value;
 265                         }
 266                 }
 267
 268                 IDataParameterCollection IDbCommand.Parameters {
 269                         get { return Parameters; }
 270                 }
 271
 272                 IDbTransaction IDbCommand.Transaction {
 273                         get { return Transaction; }
 274                         set {
 275                                 if (!(value == null || value is SqlTransaction))
 276                                         throw new ArgumentException ();
 277                                 Transaction = (SqlTransaction) value;
 278                         }
 279                 }
 280 #endif
 281
 282                 [Browsable (false)]
 283 #if !NET_2_0
 284                 [DataSysDescription ("The transaction used by the command.")]
 285 #endif
 286                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 287                 public new SqlTransaction Transaction {
 288                         get {
 289                                 if (transaction != null && !transaction.IsOpen)
 290                                         transaction = null;
 291                                 return transaction;
 292                         }
 293                         set
 294                         {
 295 #if ONLY_1_1
 296                                 if (connection != null && connection.DataReader != null)
 297                                         throw new InvalidOperationException ("The connection is busy fetching data.");
 298 #endif
 299                                 transaction = value;
 300                         }
 301                 }
 302
 303 #if !NET_2_0
 304                 [DataSysDescription ("When used by a DataAdapter.Update, how command results are applied to the current DataRow.")]
 305 #endif
 306                 [DefaultValue (UpdateRowSource.Both)]
 307                 public
 308 #if NET_2_0
 309                 override
 310 #endif // NET_2_0
 311                 UpdateRowSource UpdatedRowSource {
 312                         get { return updatedRowSource; }
 313                         set {
 314                                 ExceptionHelper.CheckEnumValue (typeof (UpdateRowSource), value);
 315                                 updatedRowSource = value;
 316                         }
 317                 }
 318
 319 #if NET_2_0
 320                 [Browsable (false)]
 321                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 322                 public SqlNotificationRequest Notification {
 323                         get { return notification; }
 324                         set { notification = value; }
 325                 }
 326
 327                 [DefaultValue (true)]
 328                 public bool NotificationAutoEnlist {
 329                         get { return notificationAutoEnlist; }
 330                         set { notificationAutoEnlist = value; }
 331                 }
 332 #endif
 333                 #endregion // Fields
 334
 335                 #region Methods
 336
 337                 public
 338 #if NET_2_0
 339                 override
 340 #endif // NET_2_0
 341                 void Cancel ()
 342                 {
 343                         if (Connection == null || Connection.Tds == null)
 344                                 return;
 345                         Connection.Tds.Cancel ();
 346                 }
 347
 348 #if NET_2_0
 349                 public SqlCommand Clone ()
 350                 {
 351                         return new SqlCommand (commandText, connection, transaction, commandType, updatedRowSource, designTimeVisible, commandTimeout, parameters);
 352                 }
 353 #endif // NET_2_0
 354
 355                 internal void CloseDataReader (bool moreResults)
 356                 {
 357                         Connection.DataReader = null;
 358
 359                         if ((behavior & CommandBehavior.CloseConnection) != 0)
 360                                 Connection.Close ();
 361
 362                         // Reset the behavior
 363                         behavior = CommandBehavior.Default;
 364                         if (Tds != null)
 365                                 Tds.SequentialAccess = false;
 366                 }
 367
 368                 public new SqlParameter CreateParameter ()
 369                 {
 370                         return new SqlParameter ();
 371                 }
 372
 373                 internal void DeriveParameters ()
 374                 {
 375                         if (commandType != CommandType.StoredProcedure)
 376                                 throw new InvalidOperationException (String.Format ("SqlCommand DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
 377                         ValidateCommand ("DeriveParameters");
 378
 379                         string procName = CommandText;
 380                         string schemaName = String.Empty;
 381                         int dotPosition = procName.IndexOf ('.');
 382                         if (dotPosition >= 0) {
 383                                 schemaName = procName.Substring (0, dotPosition);
 384                                 procName = procName.Substring (dotPosition + 1);
 385                         }
 386
 387                         SqlParameterCollection localParameters = new SqlParameterCollection (this);
 388                         localParameters.Add ("@procedure_name", SqlDbType.NVarChar, procName.Length).Value = procName;
 389                         if (schemaName.Length > 0)
 390                                 localParameters.Add ("@procedure_schema", SqlDbType.NVarChar, schemaName.Length).Value = schemaName;
 391
 392                         string sql = "sp_procedure_params_rowset";
 393
 394                         try {
 395                                 Connection.Tds.ExecProc (sql, localParameters.MetaParameters, 0, true);
 396                         } catch (TdsTimeoutException ex) {
 397                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 398                         } catch (TdsInternalException ex) {
 399                                 Connection.Close ();
 400                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 401                         }
 402
 403                         SqlDataReader reader = new SqlDataReader (this);
 404                         parameters.Clear ();
 405                         object[] dbValues = new object[reader.FieldCount];
 406
 407                         while (reader.Read ()) {
 408                                 reader.GetValues (dbValues);
 409                                 parameters.Add (new SqlParameter (dbValues));
 410                         }
 411                         reader.Close ();
 412
 413                         if (parameters.Count == 0)
 414                                 throw new InvalidOperationException ("Stored procedure '" + procName + "' does not exist.");
 415                 }
 416
 417                 private void Execute (CommandBehavior behavior, bool wantResults)
 418                 {
 419                         int index = 0;
 420                         Connection.Tds.RecordsAffected = -1;
 421                         TdsMetaParameterCollection parms = Parameters.MetaParameters;
 422                         foreach (TdsMetaParameter param in parms) {
 423                                 param.Validate (index++);
 424                         }
 425
 426                         if (preparedStatement == null) {
 427                                 bool schemaOnly = ((behavior & CommandBehavior.SchemaOnly) > 0);
 428                                 bool keyInfo = ((behavior & CommandBehavior.KeyInfo) > 0);
 429
 430                                 StringBuilder sql1 = new StringBuilder ();
 431                                 StringBuilder sql2 = new StringBuilder ();
 432
 433                                 if (schemaOnly || keyInfo)
 434                                         sql1.Append ("SET FMTONLY OFF;");
 435                                 if (keyInfo) {
 436                                         sql1.Append ("SET NO_BROWSETABLE ON;");
 437                                         sql2.Append ("SET NO_BROWSETABLE OFF;");
 438                                 }
 439                                 if (schemaOnly) {
 440                                         sql1.Append ("SET FMTONLY ON;");
 441                                         sql2.Append ("SET FMTONLY OFF;");
 442                                 }
 443
 444                                 switch (CommandType) {
 445                                 case CommandType.StoredProcedure:
 446                                         try {
 447                                                 if (keyInfo || schemaOnly)
 448                                                         Connection.Tds.Execute (sql1.ToString ());
 449                                                 Connection.Tds.ExecProc (CommandText, parms, CommandTimeout, wantResults);
 450                                                 if (keyInfo || schemaOnly)
 451                                                         Connection.Tds.Execute (sql2.ToString ());
 452                                         } catch (TdsTimeoutException ex) {
 453                                                 // If it is a timeout exception there can be many reasons:
 454                                                 // 1) Network is down/server is down/not reachable
 455                                                 // 2) Somebody has an exclusive lock on Table/DB
 456                                                 // In any of these cases, don't close the connection. Let the user do it
 457                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 458                                         } catch (TdsInternalException ex) {
 459                                                 Connection.Close ();
 460                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 461                                         }
 462                                         break;
 463                                 case CommandType.Text:
 464                                         string sql;
 465                                         if (sql2.Length > 0) {
 466                                                 sql = String.Format ("{0}{1};{2}", sql1.ToString (), CommandText, sql2.ToString ());
 467                                         } else {
 468                                                 sql = String.Format ("{0}{1}", sql1.ToString (), CommandText);
 469                                         }
 470                                         try {
 471                                                 Connection.Tds.Execute (sql, parms, CommandTimeout, wantResults);
 472                                         } catch (TdsTimeoutException ex) {
 473                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 474                                         } catch (TdsInternalException ex) {
 475                                                 Connection.Close ();
 476                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 477                                         }
 478                                         break;
 479                                 }
 480                         }
 481                         else {
 482                                 try {
 483                                         Connection.Tds.ExecPrepared (preparedStatement, parms, CommandTimeout, wantResults);
 484                                 } catch (TdsTimeoutException ex) {
 485                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 486                                 } catch (TdsInternalException ex) {
 487                                         Connection.Close ();
 488                                         throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 489                                 }
 490                         }
 491                 }
 492
 493                 public
 494 #if NET_2_0
 495                 override
 496 #endif // NET_2_0
 497                 int ExecuteNonQuery ()
 498                 {
 499                         ValidateCommand ("ExecuteNonQuery");
 500                         int result = 0;
 501                         behavior = CommandBehavior.Default;
 502
 503                         try {
 504                                 Execute (CommandBehavior.Default, false);
 505                                 result = Connection.Tds.RecordsAffected;
 506                         }
 507                         catch (TdsTimeoutException e) {
 508                                 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
 509                         }
 510
 511                         GetOutputParameters ();
 512                         return result;
 513                 }
 514
 515                 public new SqlDataReader ExecuteReader ()
 516                 {
 517                         return ExecuteReader (CommandBehavior.Default);
 518                 }
 519
 520                 public new SqlDataReader ExecuteReader (CommandBehavior behavior)
 521                 {
 522                         ValidateCommand ("ExecuteReader");
 523                         this.behavior = behavior;
 524                         if ((behavior & CommandBehavior.SequentialAccess) != 0)
 525                                 Tds.SequentialAccess = true;
 526                         Execute (behavior, true);
 527                         Connection.DataReader = new SqlDataReader (this);
 528
 529                         return Connection.DataReader;
 530                 }
 531
 532                 public
 533 #if NET_2_0
 534                 override
 535 #endif // NET_2_0
 536                 object ExecuteScalar ()
 537                 {
 538                         try {
 539                                 object result = null;
 540                                 ValidateCommand ("ExecuteScalar");
 541                                 behavior = CommandBehavior.Default;
 542                                 Execute (CommandBehavior.Default, true);
 543
 544                                 try {
 545                                         if (Connection.Tds.NextResult () && Connection.Tds.NextRow ())
 546                                                 result = Connection.Tds.ColumnValues[0];
 547
 548                                         if (commandType == CommandType.StoredProcedure) {
 549                                                 Connection.Tds.SkipToEnd ();
 550                                                 GetOutputParameters ();
 551                                         }
 552                                 } catch (TdsTimeoutException ex) {
 553                                         throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 554                                 } catch (TdsInternalException ex) {
 555                                         Connection.Close ();
 556                                         throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 557                                 }
 558
 559                                 return result;
 560                         }
 561                         finally {
 562                                 CloseDataReader (true);
 563                         }
 564                 }
 565
 566                 public XmlReader ExecuteXmlReader ()
 567                 {
 568                         ValidateCommand ("ExecuteXmlReader");
 569                         behavior = CommandBehavior.Default;
 570                         try {
 571                                 Execute (CommandBehavior.Default, true);
 572                         } catch (TdsTimeoutException e) {
 573                                 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
 574                         }
 575
 576                         SqlDataReader dataReader = new SqlDataReader (this);
 577                         SqlXmlTextReader textReader = new SqlXmlTextReader (dataReader);
 578                         XmlReader xmlReader = new XmlTextReader (textReader);
 579                         return xmlReader;
 580                 }
 581
 582                 internal void GetOutputParameters ()
 583                 {
 584                         IList list = Connection.Tds.OutputParameters;
 585
 586                         if (list != null && list.Count > 0) {
 587
 588                                 int index = 0;
 589                                 foreach (SqlParameter parameter in parameters) {
 590                                         if (parameter.Direction != ParameterDirection.Input &&
 591                                             parameter.Direction != ParameterDirection.ReturnValue) {
 592                                                 parameter.Value = list [index];
 593                                                 index += 1;
 594                                         }
 595                                         if (index >= list.Count)
 596                                                 break;
 597                                 }
 598                         }
 599                 }
 600
 601                 object ICloneable.Clone ()
 602                 {
 603                         return new SqlCommand (commandText, connection, transaction, commandType, updatedRowSource, designTimeVisible, commandTimeout, parameters);
 604
 605                 }
 606
 607 #if !NET_2_0
 608                 IDbDataParameter IDbCommand.CreateParameter ()
 609                 {
 610                         return CreateParameter ();
 611                 }
 612
 613                 IDataReader IDbCommand.ExecuteReader ()
 614                 {
 615                         return ExecuteReader ();
 616                 }
 617
 618                 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
 619                 {
 620                         return ExecuteReader (behavior);
 621                 }
 622 #endif
 623
 624 #if NET_2_0
 625                 protected override void Dispose (bool disposing)
 626                 {
 627                         if (disposed) return;
 628                         if (disposing) {
 629                                 parameters.Clear();
 630                         }
 631                         base.Dispose (disposing);
 632                         disposed = true;
 633                 }
 634 #endif
 635
 636                 public
 637 #if NET_2_0
 638                 override
 639 #endif // NET_2_0
 640                 void Prepare ()
 641                 {
 642                         ValidateCommand ("Prepare");
 643
 644                         if (CommandType == CommandType.StoredProcedure)
 645                                 return;
 646
 647                         try {
 648                                 foreach (SqlParameter param in Parameters)
 649                                         param.CheckIfInitialized ();
 650                         } catch (Exception e) {
 651                                 throw new InvalidOperationException ("SqlCommand.Prepare requires " + e.Message);
 652                         }
 653
 654                         preparedStatement = Connection.Tds.Prepare (CommandText, Parameters.MetaParameters);
 655                 }
 656
 657                 public void ResetCommandTimeout ()
 658                 {
 659                         commandTimeout = DEFAULT_COMMAND_TIMEOUT;
 660                 }
 661
 662                 private void Unprepare ()
 663                 {
 664                         Connection.Tds.Unprepare (preparedStatement);
 665                         preparedStatement = null;
 666                 }
 667
 668                 private void ValidateCommand (string method)
 669                 {
 670                         if (Connection == null)
 671 #if NET_2_0
 672                                 throw new NullReferenceException (String.Format ("{0} requires a Connection object to continue.", method));
 673 #else
 674                                 throw new InvalidOperationException (String.Format ("{0} requires a Connection object to continue.", method));
 675 #endif
 676                         if (Connection.Transaction != null && transaction != Connection.Transaction)
 677                                 throw new InvalidOperationException ("The Connection object does not have the same transaction as the command object.");
 678                         if (Connection.State != ConnectionState.Open)
 679 #if NET_2_0
 680                                 throw new NullReferenceException (String.Format ("ExecuteNonQuery requires an open Connection object to continue. This connection is closed.", method));
 681 #else
 682                                 throw new InvalidOperationException (String.Format ("ExecuteNonQuery requires an open Connection object to continue. This connection is closed.", method));
 683 #endif
 684                         if (CommandText.Length == 0)
 685                                 throw new InvalidOperationException ("The command text for this Command has not been set.");
 686                         if (Connection.DataReader != null)
 687                                 throw new InvalidOperationException ("There is already an open DataReader associated with this Connection which must be closed first.");
 688                         if (Connection.XmlReader != null)
 689                                 throw new InvalidOperationException ("There is already an open XmlReader associated with this Connection which must be closed first.");
 690 #if NET_2_0
 691                         if (method.StartsWith ("Begin") && !Connection.AsyncProcessing)
 692                                 throw new InvalidOperationException ("This Connection object is not " +
 693                                         "in Asynchronous mode. Use 'Asynchronous" +
 694                                         " Processing = true' to set it.");
 695 #endif // NET_2_0
 696                 }
 697
 698 #if NET_2_0
 699                 protected override DbParameter CreateDbParameter ()
 700                 {
 701                         return CreateParameter ();
 702                 }
 703
 704                 protected override DbDataReader ExecuteDbDataReader (CommandBehavior behavior)
 705                 {
 706                         return ExecuteReader (behavior);
 707                 }
 708
 709                 protected override DbConnection DbConnection {
 710                         get { return Connection; }
 711                         set { Connection = (SqlConnection) value; }
 712                 }
 713
 714                 protected override DbParameterCollection DbParameterCollection {
 715                         get { return Parameters; }
 716                 }
 717
 718                 protected override DbTransaction DbTransaction {
 719                         get { return Transaction; }
 720                         set { Transaction = (SqlTransaction) value; }
 721                 }
 722 #endif // NET_2_0
 723
 724                 #endregion // Methods
 725
 726 #if NET_2_0
 727                 #region Asynchronous Methods
 728
 729                 internal IAsyncResult BeginExecuteInternal (CommandBehavior behavior,
 730                                                                                                         bool wantResults,
 731                                                                                                         AsyncCallback callback,
 732                                                                                                         object state)
 733                 {
 734                         IAsyncResult ar = null;
 735                         Connection.Tds.RecordsAffected = -1;
 736                         TdsMetaParameterCollection parms = Parameters.MetaParameters;
 737                         if (preparedStatement == null) {
 738                                 bool schemaOnly = ((behavior & CommandBehavior.SchemaOnly) > 0);
 739                                 bool keyInfo = ((behavior & CommandBehavior.KeyInfo) > 0);
 740
 741                                 StringBuilder sql1 = new StringBuilder ();
 742                                 StringBuilder sql2 = new StringBuilder ();
 743
 744                                 if (schemaOnly || keyInfo)
 745                                         sql1.Append ("SET FMTONLY OFF;");
 746                                 if (keyInfo) {
 747                                         sql1.Append ("SET NO_BROWSETABLE ON;");
 748                                         sql2.Append ("SET NO_BROWSETABLE OFF;");
 749                                 }
 750                                 if (schemaOnly) {
 751                                         sql1.Append ("SET FMTONLY ON;");
 752                                         sql2.Append ("SET FMTONLY OFF;");
 753                                 }
 754                                 switch (CommandType) {
 755                                 case CommandType.StoredProcedure:
 756                                         string prolog = "";
 757                                         string epilog = "";
 758                                         if (keyInfo || schemaOnly)
 759                                                 prolog = sql1.ToString ();
 760                                         if (keyInfo || schemaOnly)
 761                                                 epilog = sql2.ToString ();
 762                                         try {
 763                                                 Connection.Tds.BeginExecuteProcedure (prolog,
 764                                                                                       epilog,
 765                                                                                       CommandText,
 766                                                                                       !wantResults,
 767                                                                                       parms,
 768                                                                                       callback,
 769                                                                                       state);
 770                                         } catch (TdsTimeoutException ex) {
 771                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 772                                         } catch (TdsInternalException ex) {
 773                                                 Connection.Close ();
 774                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 775                                         }
 776                                         break;
 777                                 case CommandType.Text:
 778                                         string sql = String.Format ("{0}{1};{2}", sql1.ToString (), CommandText, sql2.ToString ());
 779                                         try {
 780                                                 if (wantResults)
 781                                                         ar = Connection.Tds.BeginExecuteQuery (sql, parms, callback, state);
 782                                                 else
 783                                                         ar = Connection.Tds.BeginExecuteNonQuery (sql, parms, callback, state);
 784                                         } catch (TdsTimeoutException ex) {
 785                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 786                                         } catch (TdsInternalException ex) {
 787                                                 Connection.Close ();
 788                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 789                                         }
 790                                         break;
 791                                 }
 792                         }
 793                         else {
 794                                 try {
 795                                         Connection.Tds.ExecPrepared (preparedStatement, parms, CommandTimeout, wantResults);
 796                                 } catch (TdsTimeoutException ex) {
 797                                         throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 798                                 } catch (TdsInternalException ex) {
 799                                         Connection.Close ();
 800                                         throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 801                                 }
 802                         }
 803                         return ar;
 804                 }
 805
 806                 internal void EndExecuteInternal (IAsyncResult ar)
 807                 {
 808                         SqlAsyncResult sqlResult = ( (SqlAsyncResult) ar);
 809                         Connection.Tds.WaitFor (sqlResult.InternalResult);
 810                         Connection.Tds.CheckAndThrowException (sqlResult.InternalResult);
 811                 }
 812
 813                 public IAsyncResult BeginExecuteNonQuery ()
 814                 {
 815                         return BeginExecuteNonQuery (null, null);
 816                 }
 817
 818                 public IAsyncResult BeginExecuteNonQuery (AsyncCallback callback, object stateObject)
 819                 {
 820                         ValidateCommand ("BeginExecuteNonQuery");
 821                         SqlAsyncResult ar = new SqlAsyncResult (callback, stateObject);
 822                         ar.EndMethod = "EndExecuteNonQuery";
 823                         ar.InternalResult = BeginExecuteInternal (CommandBehavior.Default, false, ar.BubbleCallback, ar);
 824                         return ar;
 825                 }
 826
 827                 public int EndExecuteNonQuery (IAsyncResult asyncResult)
 828                 {
 829                         ValidateAsyncResult (asyncResult, "EndExecuteNonQuery");
 830                         EndExecuteInternal (asyncResult);
 831
 832                         int ret = Connection.Tds.RecordsAffected;
 833
 834                         GetOutputParameters ();
 835                         ((SqlAsyncResult) asyncResult).Ended = true;
 836                         return ret;
 837                 }
 838
 839                 public IAsyncResult BeginExecuteReader ()
 840                 {
 841                         return BeginExecuteReader (null, null, CommandBehavior.Default);
 842                 }
 843
 844                 public IAsyncResult BeginExecuteReader (CommandBehavior behavior)
 845                 {
 846                         return BeginExecuteReader (null, null, behavior);
 847                 }
 848
 849                 public IAsyncResult BeginExecuteReader (AsyncCallback callback, object stateObject)
 850                 {
 851                         return BeginExecuteReader (callback, stateObject, CommandBehavior.Default);
 852                 }
 853
 854                 public IAsyncResult BeginExecuteReader (AsyncCallback callback, object stateObject, CommandBehavior behavior)
 855                 {
 856                         ValidateCommand ("BeginExecuteReader");
 857                         this.behavior = behavior;
 858                         SqlAsyncResult ar = new SqlAsyncResult (callback, stateObject);
 859                         ar.EndMethod = "EndExecuteReader";
 860                         IAsyncResult tdsResult = BeginExecuteInternal (behavior, true,
 861                                 ar.BubbleCallback, stateObject);
 862                         ar.InternalResult = tdsResult;
 863                         return ar;
 864                 }
 865
 866                 public SqlDataReader EndExecuteReader (IAsyncResult asyncResult)
 867                 {
 868                         ValidateAsyncResult (asyncResult, "EndExecuteReader");
 869                         EndExecuteInternal (asyncResult);
 870                         SqlDataReader reader = null;
 871                         try {
 872                                 reader = new SqlDataReader (this);
 873                         } catch (TdsTimeoutException e) {
 874                                 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
 875                         } catch (TdsInternalException e) {
 876                                 // if behavior is closeconnection, even if it throws exception
 877                                 // the connection has to be closed.
 878                                 if ((behavior & CommandBehavior.CloseConnection) != 0)
 879                                         Connection.Close ();
 880                                 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
 881                         }
 882
 883                         ((SqlAsyncResult) asyncResult).Ended = true;
 884                         return reader;
 885                 }
 886
 887                 public IAsyncResult BeginExecuteXmlReader (AsyncCallback callback, object stateObject)
 888                 {
 889                         ValidateCommand ("BeginExecuteXmlReader");
 890                         SqlAsyncResult ar = new SqlAsyncResult (callback, stateObject);
 891                         ar.EndMethod = "EndExecuteXmlReader";
 892                         ar.InternalResult = BeginExecuteInternal (behavior, true,
 893                                 ar.BubbleCallback, stateObject);
 894                         return ar;
 895                 }
 896
 897                 public IAsyncResult BeginExecuteXmlReader ()
 898                 {
 899                         return BeginExecuteXmlReader (null, null);
 900                 }
 901
 902
 903                 public XmlReader EndExecuteXmlReader (IAsyncResult asyncResult)
 904                 {
 905                         ValidateAsyncResult (asyncResult, "EndExecuteXmlReader");
 906                         EndExecuteInternal (asyncResult);
 907                         SqlDataReader reader = new SqlDataReader (this);
 908                         SqlXmlTextReader textReader = new SqlXmlTextReader (reader);
 909                         XmlReader xmlReader = new XmlTextReader (textReader);
 910                         ((SqlAsyncResult) asyncResult).Ended = true;
 911                         return xmlReader;
 912                 }
 913
 914                 internal void ValidateAsyncResult (IAsyncResult ar, string endMethod)
 915                 {
 916                         if (ar == null)
 917                                 throw new ArgumentException ("result passed is null!");
 918                         if (! (ar is SqlAsyncResult))
 919                                 throw new ArgumentException (String.Format ("cannot test validity of types {0}",
 920                                         ar.GetType ()));
 921                         SqlAsyncResult result = (SqlAsyncResult) ar;
 922                         if (result.EndMethod != endMethod)
 923                                 throw new InvalidOperationException (String.Format ("Mismatched {0} called for AsyncResult. " +
 924                                         "Expected call to {1} but {0} is called instead.",
 925                                         endMethod, result.EndMethod));
 926                         if (result.Ended)
 927                                 throw new InvalidOperationException (String.Format ("The method {0} cannot be called " +
 928                                         "more than once for the same AsyncResult.", endMethod));
 929                 }
 930
 931                 #endregion // Asynchronous Methods
 932
 933                 public event StatementCompletedEventHandler StatementCompleted;
 934 #endif // NET_2_0
 935         }
 936 }