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                 [MonoTODO]
 514                 public new Task<SqlDataReader> ExecuteReaderAsync ()
 515                 {
 516                         throw new NotImplementedException ();
 517                 }
 518
 519                 [MonoTODO]
 520                 public new Task<SqlDataReader> ExecuteReaderAsync (CancellationToken cancellationToken)
 521                 {
 522                         throw new NotImplementedException ();
 523                 }
 524
 525                 [MonoTODO]
 526                 public new Task<SqlDataReader> ExecuteReaderAsync (CommandBehavior behavior)
 527                 {
 528                         throw new NotImplementedException ();
 529                 }
 530
 531                 [MonoTODO]
 532                 public new Task<SqlDataReader> ExecuteReaderAsync (CommandBehavior behavior, CancellationToken cancellationToken)
 533                 {
 534                         throw new NotImplementedException ();
 535                 }
 536
 537                 [MonoTODO]
 538                 public Task<XmlReader> ExecuteXmlReaderAsync ()
 539                 {
 540                         throw new NotImplementedException ();
 541                 }
 542
 543                 [MonoTODO]
 544                 public Task<XmlReader> ExecuteXmlReaderAsync (CancellationToken cancellationToken)
 545                 {
 546                         throw new NotImplementedException ();
 547                 }
 548
 549                 public
 550                 override
 551                 object ExecuteScalar ()
 552                 {
 553                         try {
 554                                 object result = null;
 555                                 ValidateCommand ("ExecuteScalar", false);
 556                                 behavior = CommandBehavior.Default;
 557                                 Execute (true);
 558
 559                                 try {
 560                                         if (Connection.Tds.NextResult () && Connection.Tds.NextRow ())
 561                                                 result = Connection.Tds.ColumnValues[0];
 562
 563                                         if (commandType == CommandType.StoredProcedure) {
 564                                                 Connection.Tds.SkipToEnd ();
 565                                                 GetOutputParameters ();
 566                                         }
 567                                 } catch (TdsTimeoutException ex) {
 568                                         Connection.Tds.Reset ();
 569                                         throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 570                                 } catch (TdsInternalException ex) {
 571                                         Connection.Close ();
 572                                         throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 573                                 }
 574
 575                                 return result;
 576                         } finally {
 577                                 CloseDataReader ();
 578                         }
 579                 }
 580
 581                 public XmlReader ExecuteXmlReader ()
 582                 {
 583                         ValidateCommand ("ExecuteXmlReader", false);
 584                         behavior = CommandBehavior.Default;
 585                         try {
 586                                 Execute (true);
 587                         } catch (TdsTimeoutException e) {
 588                                 Connection.Tds.Reset ();
 589                                 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
 590                         }
 591
 592                         SqlDataReader dataReader = new SqlDataReader (this);
 593                         SqlXmlTextReader textReader = new SqlXmlTextReader (dataReader);
 594                         XmlReader xmlReader = new XmlTextReader (textReader);
 595                         return xmlReader;
 596                 }
 597
 598                 internal void GetOutputParameters ()
 599                 {
 600                         IList list = Connection.Tds.OutputParameters;
 601
 602                         if (list != null && list.Count > 0) {
 603
 604                                 int index = 0;
 605                                 foreach (SqlParameter parameter in parameters) {
 606                                         if (parameter.Direction != ParameterDirection.Input &&
 607                                             parameter.Direction != ParameterDirection.ReturnValue) {
 608                                                 parameter.Value = list [index];
 609                                                 index += 1;
 610                                         }
 611                                         if (index >= list.Count)
 612                                                 break;
 613                                 }
 614                         }
 615                 }
 616
 617                 object ICloneable.Clone ()
 618                 {
 619                         return new SqlCommand (commandText, connection, transaction, commandType, updatedRowSource, designTimeVisible, commandTimeout, parameters);
 620
 621                 }
 622
 623
 624                 protected override void Dispose (bool disposing)
 625                 {
 626                         if (disposed) return;
 627                         if (disposing) {
 628                                 parameters.Clear();
 629                                 if (Connection != null)
 630                                         Connection.DataReader = null;
 631                         }
 632                         base.Dispose (disposing);
 633                         disposed = true;
 634                 }
 635
 636                 public
 637                 override
 638                 void Prepare ()
 639                 {
 640                         if (Connection == null)
 641                                 throw new NullReferenceException ();
 642
 643                         if (CommandType == CommandType.StoredProcedure || CommandType == CommandType.Text && Parameters.Count == 0)
 644                                 return;
 645
 646                         ValidateCommand ("Prepare", false);
 647
 648                         try {
 649                                 foreach (SqlParameter param in Parameters)
 650                                         param.CheckIfInitialized ();
 651                         } catch (Exception e) {
 652                                 throw new InvalidOperationException ("SqlCommand.Prepare requires " + e.Message);
 653                         }
 654
 655                         preparedStatement = Connection.Tds.Prepare (CommandText, Parameters.MetaParameters);
 656                 }
 657
 658                 public void ResetCommandTimeout ()
 659                 {
 660                         commandTimeout = DEFAULT_COMMAND_TIMEOUT;
 661                 }
 662
 663                 private void Unprepare ()
 664                 {
 665                         Connection.Tds.Unprepare (preparedStatement);
 666                         preparedStatement = null;
 667                 }
 668
 669                 private void ValidateCommand (string method, bool async)
 670                 {
 671                         if (Connection == null)
 672                                 throw new InvalidOperationException (String.Format ("{0}: A Connection object is required to continue.", method));
 673                         if (Transaction == null && Connection.Transaction != null)
 674                                 throw new InvalidOperationException (String.Format (
 675                                         "{0} requires a transaction if the command's connection is in a pending transaction.",
 676                                         method));
 677                         if (Transaction != null && Transaction.Connection != Connection)
 678                                 throw new InvalidOperationException ("The connection does not have the same transaction as the command.");
 679                         if (Connection.State != ConnectionState.Open)
 680                                 throw new InvalidOperationException (String.Format ("{0} requires an open connection to continue. This connection is closed.", method));
 681                         if (CommandText.Length == 0)
 682                                 throw new InvalidOperationException (String.Format ("{0}: CommandText has not been set for this Command.", method));
 683                         if (Connection.DataReader != null)
 684                                 throw new InvalidOperationException ("There is already an open DataReader associated with this Connection which must be closed first.");
 685                         if (Connection.XmlReader != null)
 686                                 throw new InvalidOperationException ("There is already an open XmlReader associated with this Connection which must be closed first.");
 687                         if (async && !Connection.AsyncProcessing)
 688                                 throw new InvalidOperationException ("This Connection object is not " +
 689                                         "in Asynchronous mode. Use 'Asynchronous" +
 690                                         " Processing = true' to set it.");
 691                 }
 692
 693                 protected override DbParameter CreateDbParameter ()
 694                 {
 695                         return CreateParameter ();
 696                 }
 697
 698                 protected override DbDataReader ExecuteDbDataReader (CommandBehavior behavior)
 699                 {
 700                         return ExecuteReader (behavior);
 701                 }
 702
 703                 protected override DbConnection DbConnection {
 704                         get { return Connection; }
 705                         set { Connection = (SqlConnection) value; }
 706                 }
 707
 708                 protected override DbParameterCollection DbParameterCollection {
 709                         get { return Parameters; }
 710                 }
 711
 712                 protected override DbTransaction DbTransaction {
 713                         get { return Transaction; }
 714                         set { Transaction = (SqlTransaction) value; }
 715                 }
 716
 717                 #endregion // Methods
 718
 719                 #region Asynchronous Methods
 720
 721                 internal IAsyncResult BeginExecuteInternal (CommandBehavior behavior,
 722                                                                                                         bool wantResults,
 723                                                                                                         AsyncCallback callback,
 724                                                                                                         object state)
 725                 {
 726                         IAsyncResult ar = null;
 727                         Connection.Tds.RecordsAffected = -1;
 728                         TdsMetaParameterCollection parms = Parameters.MetaParameters;
 729                         if (preparedStatement == null) {
 730                                 bool schemaOnly = ((behavior & CommandBehavior.SchemaOnly) > 0);
 731                                 bool keyInfo = ((behavior & CommandBehavior.KeyInfo) > 0);
 732
 733                                 StringBuilder sql1 = new StringBuilder ();
 734                                 StringBuilder sql2 = new StringBuilder ();
 735
 736                                 if (schemaOnly || keyInfo)
 737                                         sql1.Append ("SET FMTONLY OFF;");
 738                                 if (keyInfo) {
 739                                         sql1.Append ("SET NO_BROWSETABLE ON;");
 740                                         sql2.Append ("SET NO_BROWSETABLE OFF;");
 741                                 }
 742                                 if (schemaOnly) {
 743                                         sql1.Append ("SET FMTONLY ON;");
 744                                         sql2.Append ("SET FMTONLY OFF;");
 745                                 }
 746                                 switch (CommandType) {
 747                                 case CommandType.StoredProcedure:
 748                                         string prolog = "";
 749                                         string epilog = "";
 750                                         if (keyInfo || schemaOnly)
 751                                                 prolog = sql1.ToString ();
 752                                         if (keyInfo || schemaOnly)
 753                                                 epilog = sql2.ToString ();
 754                                         try {
 755                                                 ar = Connection.Tds.BeginExecuteProcedure (prolog,
 756                                                                                       epilog,
 757                                                                                       CommandText,
 758                                                                                       !wantResults,
 759                                                                                       parms,
 760                                                                                       callback,
 761                                                                                       state);
 762                                         } catch (TdsTimeoutException ex) {
 763                                                 Connection.Tds.Reset ();
 764                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 765                                         } catch (TdsInternalException ex) {
 766                                                 Connection.Close ();
 767                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 768                                         }
 769                                         break;
 770                                 case CommandType.Text:
 771                                         string sql = String.Format ("{0}{1};{2}", sql1.ToString (), CommandText, sql2.ToString ());
 772                                         try {
 773                                                 if (wantResults)
 774                                                         ar = Connection.Tds.BeginExecuteQuery (sql, parms, callback, state);
 775                                                 else
 776                                                         ar = Connection.Tds.BeginExecuteNonQuery (sql, parms, callback, state);
 777                                         } catch (TdsTimeoutException ex) {
 778                                                 Connection.Tds.Reset ();
 779                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 780                                         } catch (TdsInternalException ex) {
 781                                                 Connection.Close ();
 782                                                 throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 783                                         }
 784                                         break;
 785                                 }
 786                         }
 787                         else {
 788                                 try {
 789                                         Connection.Tds.ExecPrepared (preparedStatement, parms, CommandTimeout, wantResults);
 790                                 } catch (TdsTimeoutException ex) {
 791                                         Connection.Tds.Reset ();
 792                                         throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 793                                 } catch (TdsInternalException ex) {
 794                                         Connection.Close ();
 795                                         throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
 796                                 }
 797                         }
 798                         return ar;
 799                 }
 800
 801                 internal void EndExecuteInternal (IAsyncResult ar)
 802                 {
 803                         SqlAsyncResult sqlResult = ( (SqlAsyncResult) ar);
 804                         Connection.Tds.WaitFor (sqlResult.InternalResult);
 805                         Connection.Tds.CheckAndThrowException (sqlResult.InternalResult);
 806                 }
 807
 808                 public IAsyncResult BeginExecuteNonQuery ()
 809                 {
 810                         return BeginExecuteNonQuery (null, null);
 811                 }
 812
 813                 public IAsyncResult BeginExecuteNonQuery (AsyncCallback callback, object stateObject)
 814                 {
 815                         ValidateCommand ("BeginExecuteNonQuery", true);
 816                         SqlAsyncResult ar = new SqlAsyncResult (callback, stateObject);
 817                         ar.EndMethod = "EndExecuteNonQuery";
 818                         ar.InternalResult = BeginExecuteInternal (CommandBehavior.Default, false, ar.BubbleCallback, ar);
 819                         return ar;
 820                 }
 821
 822                 public int EndExecuteNonQuery (IAsyncResult asyncResult)
 823                 {
 824                         ValidateAsyncResult (asyncResult, "EndExecuteNonQuery");
 825                         EndExecuteInternal (asyncResult);
 826
 827                         int ret = Connection.Tds.RecordsAffected;
 828
 829                         GetOutputParameters ();
 830                         ((SqlAsyncResult) asyncResult).Ended = true;
 831                         return ret;
 832                 }
 833
 834                 public IAsyncResult BeginExecuteReader ()
 835                 {
 836                         return BeginExecuteReader (null, null, CommandBehavior.Default);
 837                 }
 838
 839                 public IAsyncResult BeginExecuteReader (CommandBehavior behavior)
 840                 {
 841                         return BeginExecuteReader (null, null, behavior);
 842                 }
 843
 844                 public IAsyncResult BeginExecuteReader (AsyncCallback callback, object stateObject)
 845                 {
 846                         return BeginExecuteReader (callback, stateObject, CommandBehavior.Default);
 847                 }
 848
 849                 public IAsyncResult BeginExecuteReader (AsyncCallback callback, object stateObject, CommandBehavior behavior)
 850                 {
 851                         ValidateCommand ("BeginExecuteReader", true);
 852                         this.behavior = behavior;
 853                         SqlAsyncResult ar = new SqlAsyncResult (callback, stateObject);
 854                         ar.EndMethod = "EndExecuteReader";
 855                         IAsyncResult tdsResult = BeginExecuteInternal (behavior, true,
 856                                 ar.BubbleCallback, stateObject);
 857                         ar.InternalResult = tdsResult;
 858                         return ar;
 859                 }
 860
 861                 public SqlDataReader EndExecuteReader (IAsyncResult asyncResult)
 862                 {
 863                         ValidateAsyncResult (asyncResult, "EndExecuteReader");
 864                         EndExecuteInternal (asyncResult);
 865                         SqlDataReader reader = null;
 866                         try {
 867                                 reader = new SqlDataReader (this);
 868                         } catch (TdsTimeoutException e) {
 869                                 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
 870                         } catch (TdsInternalException e) {
 871                                 // if behavior is closeconnection, even if it throws exception
 872                                 // the connection has to be closed.
 873                                 if ((behavior & CommandBehavior.CloseConnection) != 0)
 874                                         Connection.Close ();
 875                                 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
 876                         }
 877
 878                         ((SqlAsyncResult) asyncResult).Ended = true;
 879                         return reader;
 880                 }
 881
 882                 public IAsyncResult BeginExecuteXmlReader (AsyncCallback callback, object stateObject)
 883                 {
 884                         ValidateCommand ("BeginExecuteXmlReader", true);
 885                         SqlAsyncResult ar = new SqlAsyncResult (callback, stateObject);
 886                         ar.EndMethod = "EndExecuteXmlReader";
 887                         ar.InternalResult = BeginExecuteInternal (behavior, true,
 888                                 ar.BubbleCallback, stateObject);
 889                         return ar;
 890                 }
 891
 892                 public IAsyncResult BeginExecuteXmlReader ()
 893                 {
 894                         return BeginExecuteXmlReader (null, null);
 895                 }
 896
 897
 898                 public XmlReader EndExecuteXmlReader (IAsyncResult asyncResult)
 899                 {
 900                         ValidateAsyncResult (asyncResult, "EndExecuteXmlReader");
 901                         EndExecuteInternal (asyncResult);
 902                         SqlDataReader reader = new SqlDataReader (this);
 903                         SqlXmlTextReader textReader = new SqlXmlTextReader (reader);
 904                         XmlReader xmlReader = new XmlTextReader (textReader);
 905                         ((SqlAsyncResult) asyncResult).Ended = true;
 906                         return xmlReader;
 907                 }
 908
 909                 internal void ValidateAsyncResult (IAsyncResult ar, string endMethod)
 910                 {
 911                         if (ar == null)
 912                                 throw new ArgumentException ("result passed is null!");
 913                         if (! (ar is SqlAsyncResult))
 914                                 throw new ArgumentException (String.Format ("cannot test validity of types {0}",
 915                                         ar.GetType ()));
 916                         SqlAsyncResult result = (SqlAsyncResult) ar;
 917                         if (result.EndMethod != endMethod)
 918                                 throw new InvalidOperationException (String.Format ("Mismatched {0} called for AsyncResult. " +
 919                                         "Expected call to {1} but {0} is called instead.",
 920                                         endMethod, result.EndMethod));
 921                         if (result.Ended)
 922                                 throw new InvalidOperationException (String.Format ("The method {0} cannot be called " +
 923                                         "more than once for the same AsyncResult.", endMethod));
 924                 }
 925
 926                 #endregion // Asynchronous Methods
 927
 928                 public event StatementCompletedEventHandler StatementCompleted;
 929         }
 930 }