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