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