2 // System.Data.SqlClient.SqlCommand.cs
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)
10 // (C) Ximian, Inc 2002 http://www.ximian.com/
11 // (C) Daniel Morgan, 2002
12 // Copyright (C) Tim Coleman, 2002
16 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
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:
26 // The above copyright notice and this permission notice shall be
27 // included in all copies or substantial portions of the Software.
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.
39 using Mono.Data.Tds.Protocol;
41 using System.Collections;
42 using System.Collections.Specialized;
43 using System.ComponentModel;
45 using System.Data.Common;
46 using System.Runtime.InteropServices;
50 namespace System.Data.SqlClient {
51 [DesignerAttribute ("Microsoft.VSDesigner.Data.VS.SqlCommandDesigner, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.ComponentModel.Design.IDesigner")]
52 [ToolboxItemAttribute ("System.Drawing.Design.ToolboxItem, "+ Consts.AssemblySystem_Drawing)]
54 public sealed class SqlCommand : DbCommand, IDbCommand, ICloneable
56 public sealed class SqlCommand : Component, IDbCommand, ICloneable
61 bool disposed = false;
63 bool designTimeVisible;
65 CommandType commandType;
66 SqlConnection connection;
67 SqlTransaction transaction;
68 UpdateRowSource updatedRowSource;
69 CommandBehavior behavior = CommandBehavior.Default;
70 SqlParameterCollection parameters;
71 string preparedStatement = null;
78 : this (String.Empty, null, null)
82 public SqlCommand (string commandText)
83 : this (commandText, null, null)
87 public SqlCommand (string commandText, SqlConnection connection)
88 : this (commandText, connection, null)
92 public SqlCommand (string commandText, SqlConnection connection, SqlTransaction transaction)
94 this.commandText = commandText;
95 this.connection = connection;
96 this.transaction = transaction;
97 this.commandType = CommandType.Text;
98 this.updatedRowSource = UpdateRowSource.Both;
100 this.designTimeVisible = false;
101 this.commandTimeout = 30;
102 parameters = new SqlParameterCollection (this);
105 private SqlCommand(string commandText, SqlConnection connection, SqlTransaction transaction, CommandType commandType, UpdateRowSource updatedRowSource, bool designTimeVisible, int commandTimeout, SqlParameterCollection parameters)
107 this.commandText = commandText;
108 this.connection = connection;
109 this.transaction = transaction;
110 this.commandType = commandType;
111 this.updatedRowSource = updatedRowSource;
112 this.designTimeVisible = designTimeVisible;
113 this.commandTimeout = commandTimeout;
114 this.parameters = new SqlParameterCollection(this);
115 for (int i = 0;i < parameters.Count;i++)
116 this.parameters.Add(((ICloneable)parameters[i]).Clone());
118 #endregion // Constructors
122 internal CommandBehavior CommandBehavior {
123 get { return behavior; }
126 [DataCategory ("Data")]
128 [DataSysDescription ("Command text to execute.")]
131 [EditorAttribute ("Microsoft.VSDesigner.Data.SQL.Design.SqlCommandTextEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
132 [RefreshProperties (RefreshProperties.All)]
138 get { return commandText; }
140 if (value != commandText && preparedStatement != null)
147 [DataSysDescription ("Time to wait for command to execute.")]
155 get { return commandTimeout; }
158 throw new ArgumentException ("The property value assigned is less than 0.");
159 commandTimeout = value;
163 [DataCategory ("Data")]
165 [DataSysDescription ("How to interpret the CommandText.")]
167 [DefaultValue (CommandType.Text)]
168 [RefreshProperties (RefreshProperties.All)]
173 CommandType CommandType {
174 get { return commandType; }
176 if (value == CommandType.TableDirect)
177 throw new ArgumentException ("CommandType.TableDirect is not supported by the Mono SqlClient Data Provider.");
179 if (!Enum.IsDefined (typeof (CommandType), value))
180 throw ExceptionHelper.InvalidEnumValueException ("CommandType", value);
185 [DataCategory ("Behavior")]
186 [DefaultValue (null)]
188 [DataSysDescription ("Connection used by the command.")]
190 [EditorAttribute ("Microsoft.VSDesigner.Data.Design.DbConnectionEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
195 SqlConnection Connection {
196 get { return connection; }
198 if (transaction != null && connection.Transaction != null && connection.Transaction.IsOpen)
199 throw new InvalidOperationException ("The Connection property was changed while a transaction was in progress.");
206 [DefaultValue (true)]
212 bool DesignTimeVisible {
213 get { return designTimeVisible; }
214 set { designTimeVisible = value; }
217 [DataCategory ("Data")]
219 [DataSysDescription ("The parameters collection.")]
221 [DesignerSerializationVisibility (DesignerSerializationVisibility.Content)]
226 SqlParameterCollection Parameters {
227 get { return parameters; }
231 get { return Connection.Tds; }
234 IDbConnection IDbCommand.Connection {
235 get { return Connection; }
237 if (!(value is SqlConnection))
238 throw new InvalidCastException ("The value was not a valid SqlConnection.");
239 Connection = (SqlConnection) value;
243 IDataParameterCollection IDbCommand.Parameters {
244 get { return Parameters; }
247 IDbTransaction IDbCommand.Transaction {
248 get { return Transaction; }
250 if (!(value is SqlTransaction))
251 throw new ArgumentException ();
252 Transaction = (SqlTransaction) value;
258 [DataSysDescription ("The transaction used by the command.")]
260 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
261 public new SqlTransaction Transaction {
262 get { return transaction; }
263 set { transaction = value; }
266 [DataCategory ("Behavior")]
268 [DataSysDescription ("When used by a DataAdapter.Update, how command results are applied to the current DataRow.")]
270 [DefaultValue (UpdateRowSource.Both)]
275 UpdateRowSource UpdatedRowSource {
276 get { return updatedRowSource; }
278 if (!Enum.IsDefined (typeof (UpdateRowSource), value))
279 throw ExceptionHelper.InvalidEnumValueException ("UpdateRowSource", value);
280 updatedRowSource = value;
294 if (Connection == null || Connection.Tds == null)
296 Connection.Tds.Cancel ();
299 internal void CloseDataReader (bool moreResults)
301 Connection.DataReader = null;
303 if ((behavior & CommandBehavior.CloseConnection) != 0)
306 // Reset the behavior
307 behavior = CommandBehavior.Default;
308 Tds.SequentialAccess = false;
311 public new SqlParameter CreateParameter ()
313 return new SqlParameter ();
316 internal void DeriveParameters ()
318 if (commandType != CommandType.StoredProcedure)
319 throw new InvalidOperationException (String.Format ("SqlCommand DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
320 ValidateCommand ("DeriveParameters");
322 SqlParameterCollection localParameters = new SqlParameterCollection (this);
323 localParameters.Add ("@procedure_name", SqlDbType.NVarChar, commandText.Length).Value = commandText;
325 string sql = "sp_procedure_params_rowset";
327 Connection.Tds.ExecProc (sql, localParameters.MetaParameters, 0, true);
329 SqlDataReader reader = new SqlDataReader (this);
331 object[] dbValues = new object[reader.FieldCount];
333 while (reader.Read ()) {
334 reader.GetValues (dbValues);
335 parameters.Add (new SqlParameter (dbValues));
341 private void Execute (CommandBehavior behavior, bool wantResults)
343 Connection.Tds.RecordsAffected = -1;
344 TdsMetaParameterCollection parms = Parameters.MetaParameters;
345 if (preparedStatement == null) {
346 bool schemaOnly = ((behavior & CommandBehavior.SchemaOnly) > 0);
347 bool keyInfo = ((behavior & CommandBehavior.KeyInfo) > 0);
349 StringBuilder sql1 = new StringBuilder ();
350 StringBuilder sql2 = new StringBuilder ();
352 if (schemaOnly || keyInfo)
353 sql1.Append ("SET FMTONLY OFF;");
355 sql1.Append ("SET NO_BROWSETABLE ON;");
356 sql2.Append ("SET NO_BROWSETABLE OFF;");
359 sql1.Append ("SET FMTONLY ON;");
360 sql2.Append ("SET FMTONLY OFF;");
363 switch (CommandType) {
364 case CommandType.StoredProcedure:
365 if (keyInfo || schemaOnly)
366 Connection.Tds.Execute (sql1.ToString ());
367 Connection.Tds.ExecProc (CommandText, parms, CommandTimeout, wantResults);
368 if (keyInfo || schemaOnly)
369 Connection.Tds.Execute (sql2.ToString ());
371 case CommandType.Text:
372 string sql = String.Format ("{0}{1};{2}", sql1.ToString (), CommandText, sql2.ToString ());
373 Connection.Tds.Execute (sql, parms, CommandTimeout, wantResults);
378 Connection.Tds.ExecPrepared (preparedStatement, parms, CommandTimeout, wantResults);
385 int ExecuteNonQuery ()
387 ValidateCommand ("ExecuteNonQuery");
389 behavior = CommandBehavior.Default;
392 Execute (CommandBehavior.Default, false);
393 result = Connection.Tds.RecordsAffected;
395 catch (TdsTimeoutException e) {
396 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
399 GetOutputParameters ();
403 public new SqlDataReader ExecuteReader ()
405 return ExecuteReader (CommandBehavior.Default);
408 public new SqlDataReader ExecuteReader (CommandBehavior behavior)
410 ValidateCommand ("ExecuteReader");
412 this.behavior = behavior;
413 if ((behavior & CommandBehavior.SequentialAccess) != 0)
414 Tds.SequentialAccess = true;
415 Execute (behavior, true);
416 Connection.DataReader = new SqlDataReader (this);
418 catch (TdsTimeoutException e) {
419 // if behavior is closeconnection, even if it throws exception
420 // the connection has to be closed.
421 if ((behavior & CommandBehavior.CloseConnection) != 0)
423 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
424 } catch (SqlException) {
425 // if behavior is closeconnection, even if it throws exception
426 // the connection has to be closed.
427 if ((behavior & CommandBehavior.CloseConnection) != 0)
433 return Connection.DataReader;
440 object ExecuteScalar ()
442 ValidateCommand ("ExecuteScalar");
443 behavior = CommandBehavior.Default;
445 Execute (CommandBehavior.Default, true);
447 catch (TdsTimeoutException e) {
448 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
451 if (!Connection.Tds.NextResult () || !Connection.Tds.NextRow ())
454 object result = Connection.Tds.ColumnValues [0];
455 CloseDataReader (true);
459 public XmlReader ExecuteXmlReader ()
461 ValidateCommand ("ExecuteXmlReader");
462 behavior = CommandBehavior.Default;
464 Execute (CommandBehavior.Default, true);
466 catch (TdsTimeoutException e) {
467 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
470 SqlDataReader dataReader = new SqlDataReader (this);
471 SqlXmlTextReader textReader = new SqlXmlTextReader (dataReader);
472 XmlReader xmlReader = new XmlTextReader (textReader);
476 internal void GetOutputParameters ()
478 IList list = Connection.Tds.OutputParameters;
480 if (list != null && list.Count > 0) {
483 foreach (SqlParameter parameter in parameters) {
484 if (parameter.Direction != ParameterDirection.Input) {
485 parameter.Value = list [index];
488 if (index >= list.Count)
494 object ICloneable.Clone ()
496 return new SqlCommand (commandText, connection, transaction, commandType, updatedRowSource, designTimeVisible, commandTimeout, parameters);
500 IDbDataParameter IDbCommand.CreateParameter ()
502 return CreateParameter ();
505 IDataReader IDbCommand.ExecuteReader ()
507 return ExecuteReader ();
510 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
512 return ExecuteReader (behavior);
521 ValidateCommand ("Prepare");
523 if (CommandType == CommandType.StoredProcedure)
527 foreach (SqlParameter param in Parameters)
528 param.CheckIfInitialized ();
529 }catch (Exception e) {
530 throw new InvalidOperationException ("SqlCommand.Prepare requires " + e.Message);
533 preparedStatement = Connection.Tds.Prepare (CommandText, Parameters.MetaParameters);
536 public void ResetCommandTimeout ()
541 private void Unprepare ()
543 Connection.Tds.Unprepare (preparedStatement);
544 preparedStatement = null;
547 private void ValidateCommand (string method)
549 if (Connection == null)
550 throw new InvalidOperationException (String.Format ("{0} requires a Connection object to continue.", method));
551 if (Connection.Transaction != null && transaction != Connection.Transaction)
552 throw new InvalidOperationException ("The Connection object does not have the same transaction as the command object.");
553 if (Connection.State != ConnectionState.Open)
554 throw new InvalidOperationException (String.Format ("ExecuteNonQuery requires an open Connection object to continue. This connection is closed.", method));
555 if (commandText == String.Empty || commandText == null)
556 throw new InvalidOperationException ("The command text for this Command has not been set.");
557 if (Connection.DataReader != null)
558 throw new InvalidOperationException ("There is already an open DataReader associated with this Connection which must be closed first.");
559 if (Connection.XmlReader != null)
560 throw new InvalidOperationException ("There is already an open XmlReader associated with this Connection which must be closed first.");
562 if (method.StartsWith ("Begin") && !Connection.AsyncProcessing)
563 throw new InvalidOperationException ("This Connection object is not " +
564 "in Asynchronous mode. Use 'Asynchronous" +
565 " Processing = true' to set it.");
571 protected override DbParameter CreateDbParameter ()
573 return (DbParameter) CreateParameter ();
577 protected override DbDataReader ExecuteDbDataReader (CommandBehavior behavior)
579 return (DbDataReader) ExecuteReader (behavior);
583 protected override DbConnection DbConnection
585 get { return (DbConnection) Connection; }
586 set { Connection = (SqlConnection) value; }
590 protected override DbParameterCollection DbParameterCollection
592 get { return (DbParameterCollection) Parameters; }
596 protected override DbTransaction DbTransaction
598 get { return (DbTransaction) Transaction; }
599 set { Transaction = (SqlTransaction) value; }
603 #endregion // Methods
606 #region Asynchronous Methods
608 internal IAsyncResult BeginExecuteInternal (CommandBehavior behavior,
610 AsyncCallback callback,
613 IAsyncResult ar = null;
614 Connection.Tds.RecordsAffected = -1;
615 TdsMetaParameterCollection parms = Parameters.MetaParameters;
616 if (preparedStatement == null) {
617 bool schemaOnly = ((behavior & CommandBehavior.SchemaOnly) > 0);
618 bool keyInfo = ((behavior & CommandBehavior.KeyInfo) > 0);
620 StringBuilder sql1 = new StringBuilder ();
621 StringBuilder sql2 = new StringBuilder ();
623 if (schemaOnly || keyInfo)
624 sql1.Append ("SET FMTONLY OFF;");
626 sql1.Append ("SET NO_BROWSETABLE ON;");
627 sql2.Append ("SET NO_BROWSETABLE OFF;");
630 sql1.Append ("SET FMTONLY ON;");
631 sql2.Append ("SET FMTONLY OFF;");
634 switch (CommandType) {
635 case CommandType.StoredProcedure:
638 if (keyInfo || schemaOnly)
639 prolog = sql1.ToString ();
640 if (keyInfo || schemaOnly)
641 epilog = sql2.ToString ();
642 Connection.Tds.BeginExecuteProcedure (prolog,
651 case CommandType.Text:
652 string sql = String.Format ("{0}{1};{2}", sql1.ToString (), CommandText, sql2.ToString ());
654 ar = Connection.Tds.BeginExecuteQuery (sql, parms,
657 ar = Connection.Tds.BeginExecuteNonQuery (sql, parms, callback, state);
662 Connection.Tds.ExecPrepared (preparedStatement, parms, CommandTimeout, wantResults);
668 internal void EndExecuteInternal (IAsyncResult ar)
670 SqlAsyncResult sqlResult = ( (SqlAsyncResult) ar);
671 Connection.Tds.WaitFor (sqlResult.InternalResult);
672 Connection.Tds.CheckAndThrowException (sqlResult.InternalResult);
675 public IAsyncResult BeginExecuteNonQuery ()
677 return BeginExecuteNonQuery (null, null);
680 public IAsyncResult BeginExecuteNonQuery (AsyncCallback callback, object state)
682 ValidateCommand ("BeginExecuteNonQuery");
683 SqlAsyncResult ar = new SqlAsyncResult (callback, state);
684 ar.EndMethod = "EndExecuteNonQuery";
685 ar.InternalResult = BeginExecuteInternal (CommandBehavior.Default, false, ar.BubbleCallback, ar);
689 public int EndExecuteNonQuery (IAsyncResult ar)
691 ValidateAsyncResult (ar, "EndExecuteNonQuery");
692 EndExecuteInternal (ar);
694 int ret = Connection.Tds.RecordsAffected;
696 GetOutputParameters ();
697 ( (SqlAsyncResult) ar).Ended = true;
701 public IAsyncResult BeginExecuteReader ()
703 return BeginExecuteReader (null, null, CommandBehavior.Default);
706 public IAsyncResult BeginExecuteReader (CommandBehavior behavior)
708 return BeginExecuteReader (null, null, behavior);
711 public IAsyncResult BeginExecuteReader (AsyncCallback callback, object state)
713 return BeginExecuteReader (callback, state, CommandBehavior.Default);
716 public IAsyncResult BeginExecuteReader (AsyncCallback callback, object state, CommandBehavior behavior)
718 ValidateCommand ("BeginExecuteReader");
719 this.behavior = behavior;
720 SqlAsyncResult ar = new SqlAsyncResult (callback, state);
721 ar.EndMethod = "EndExecuteReader";
722 IAsyncResult tdsResult = BeginExecuteInternal (behavior, true,
723 ar.BubbleCallback, state);
724 ar.InternalResult = tdsResult;
728 public SqlDataReader EndExecuteReader (IAsyncResult ar)
730 ValidateAsyncResult (ar, "EndExecuteReader");
731 EndExecuteInternal (ar);
732 SqlDataReader reader = null;
734 reader = new SqlDataReader (this);
736 catch (TdsTimeoutException e) {
737 // if behavior is closeconnection, even if it throws exception
738 // the connection has to be closed.
739 if ((behavior & CommandBehavior.CloseConnection) != 0)
741 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
742 } catch (SqlException) {
743 // if behavior is closeconnection, even if it throws exception
744 // the connection has to be closed.
745 if ((behavior & CommandBehavior.CloseConnection) != 0)
751 ( (SqlAsyncResult) ar).Ended = true;
755 public IAsyncResult BeginExecuteXmlReader (AsyncCallback callback, object state)
757 ValidateCommand ("BeginExecuteXmlReader");
758 SqlAsyncResult ar = new SqlAsyncResult (callback, state);
759 ar.EndMethod = "EndExecuteXmlReader";
760 ar.InternalResult = BeginExecuteInternal (behavior, true,
761 ar.BubbleCallback, state);
765 public XmlReader EndExecuteXmlReader (IAsyncResult ar)
767 ValidateAsyncResult (ar, "EndExecuteXmlReader");
768 EndExecuteInternal (ar);
769 SqlDataReader reader = new SqlDataReader (this);
770 SqlXmlTextReader textReader = new SqlXmlTextReader (reader);
771 XmlReader xmlReader = new XmlTextReader (textReader);
772 ( (SqlAsyncResult) ar).Ended = true;
777 internal void ValidateAsyncResult (IAsyncResult ar, string endMethod)
780 throw new ArgumentException ("result passed is null!");
781 if (! (ar is SqlAsyncResult))
782 throw new ArgumentException (String.Format ("cannot test validity of types {0}",
785 SqlAsyncResult result = (SqlAsyncResult) ar;
787 if (result.EndMethod != endMethod)
788 throw new InvalidOperationException (String.Format ("Mismatched {0} called for AsyncResult. " +
789 "Expected call to {1} but {0} is called instead.",
794 throw new InvalidOperationException (String.Format ("The method {0} cannot be called " +
795 "more than once for the same AsyncResult.",
800 #endregion // Asynchronous Methods