1 //------------------------------------------------------------------------------
2 // <copyright file="SqlCommand.cs" company="Microsoft">
3 // Copyright (c) Microsoft Corporation. All rights reserved.
5 // <owner current="true" primary="true">[....]</owner>
6 // <owner current="true" primary="false">[....]</owner>
7 //------------------------------------------------------------------------------
9 namespace System.Data.SqlClient {
11 using System.Collections;
12 using System.Collections.Generic;
13 using System.Collections.ObjectModel;
14 using System.ComponentModel;
15 using System.Configuration.Assemblies;
17 using System.Data.Common;
18 using System.Data.ProviderBase;
19 using System.Data.Sql;
20 using System.Data.SqlTypes;
21 using System.Diagnostics;
22 using System.Diagnostics.Tracing;
23 using System.Globalization;
26 using System.Reflection;
27 using System.Runtime.CompilerServices;
28 using System.Runtime.ConstrainedExecution;
29 using System.Runtime.Serialization.Formatters;
30 using System.Security.Permissions;
32 using System.Threading;
33 using System.Threading.Tasks;
34 using SysTx = System.Transactions;
37 using Microsoft.SqlServer.Server;
40 DefaultEvent("RecordsAffected"),
42 Designer("Microsoft.VSDesigner.Data.VS.SqlCommandDesigner, " + AssemblyRef.MicrosoftVSDesigner)
44 public sealed class SqlCommand : DbCommand, ICloneable {
46 private static int _objectTypeCount; // Bid counter
47 internal readonly int ObjectID = System.Threading.Interlocked.Increment(ref _objectTypeCount);
49 private string _commandText;
50 private CommandType _commandType;
51 private int _commandTimeout = ADP.DefaultCommandTimeout;
52 private UpdateRowSource _updatedRowSource = UpdateRowSource.Both;
53 private bool _designTimeInvisible;
56 /// Indicates if the column encryption setting was set at-least once in the batch rpc mode, when using AddBatchCommand.
58 private bool _wasBatchModeColumnEncryptionSettingSetOnce;
61 /// Column Encryption Override. Defaults to SqlConnectionSetting, in which case
62 /// it will be Enabled if SqlConnectionOptions.IsColumnEncryptionSettingEnabled = true, Disabled if false.
63 /// This may also be used to set other behavior which overrides connection level setting.
65 private SqlCommandColumnEncryptionSetting _columnEncryptionSetting = SqlCommandColumnEncryptionSetting.UseConnectionSetting;
67 internal SqlDependency _sqlDep;
71 /// Force the client to sleep during sp_describe_parameter_encryption in the function TryFetchInputParameterEncryptionInfo.
73 private static bool _sleepDuringTryFetchInputParameterEncryptionInfo = false;
76 /// Force the client to sleep during sp_describe_parameter_encryption in the function RunExecuteReaderTds.
78 private static bool _sleepDuringRunExecuteReaderTdsForSpDescribeParameterEncryption = false;
81 /// Force the client to sleep during sp_describe_parameter_encryption after ReadDescribeEncryptionParameterResults.
83 private static bool _sleepAfterReadDescribeEncryptionParameterResults = false;
87 // Against 7.0 Server (Sphinx) a prepare/unprepare requires an extra roundtrip to the server.
89 // From 8.0 (Shiloh) and above (Yukon) the preparation can be done as part of the command execution.
91 private enum EXECTYPE {
92 UNPREPARED, // execute unprepared commands, all server versions (results in sp_execsql call)
93 PREPAREPENDING, // prepare and execute command, 8.0 and above only (results in sp_prepexec call)
94 PREPARED, // execute prepared commands, all server versions (results in sp_exec call)
100 // On 8.0 and above the Prepared state cannot be left. Once a command is prepared it will always be prepared.
101 // A change in parameters, commandtext etc (IsDirty) automatically causes a hidden prepare
103 // _inPrepare will be set immediately before the actual prepare is done.
104 // The OnReturnValue function will test this flag to determine whether the returned value is a _prepareHandle or something else.
106 // _prepareHandle - the handle of a prepared command. Apparently there can be multiple prepared commands at a time - a feature that we do not support yet.
108 private bool _inPrepare = false;
109 private int _prepareHandle = -1;
110 private bool _hiddenPrepare = false;
111 private int _preparedConnectionCloseCount = -1;
112 private int _preparedConnectionReconnectCount = -1;
114 private SqlParameterCollection _parameters;
115 private SqlConnection _activeConnection;
116 private bool _dirty = false; // true if the user changes the commandtext or number of parameters after the command is already prepared
117 private EXECTYPE _execType = EXECTYPE.UNPREPARED; // by default, assume the user is not sharing a connection so the command has not been prepared
118 private _SqlRPC[] _rpcArrayOf1 = null; // Used for RPC executes
119 private _SqlRPC _rpcForEncryption = null; // Used for sp_describe_parameter_encryption RPC executes
121 // cut down on object creation and cache all these
123 private _SqlMetaDataSet _cachedMetaData;
125 // Last TaskCompletionSource for reconnect task - use for cancellation only
126 TaskCompletionSource<object> _reconnectionCompletionSource = null;
129 static internal int DebugForceAsyncWriteDelay { get; set; }
131 internal bool InPrepare {
138 /// Return if column encryption setting is enabled.
139 /// The order in the below if is important since _activeConnection.Parser can throw if the
140 /// underlying tds connection is closed and we don't want to change the behavior for folks
141 /// not trying to use transparent parameter encryption i.e. who don't use (SqlCommandColumnEncryptionSetting.Enabled or _activeConnection.IsColumnEncryptionSettingEnabled) here.
143 internal bool IsColumnEncryptionEnabled {
145 return (_columnEncryptionSetting == SqlCommandColumnEncryptionSetting.Enabled
146 || (_columnEncryptionSetting == SqlCommandColumnEncryptionSetting.UseConnectionSetting && _activeConnection.IsColumnEncryptionSettingEnabled))
147 && _activeConnection.Parser != null
148 && _activeConnection.Parser.IsColumnEncryptionSupported;
152 // Cached info for async executions
153 private class CachedAsyncState {
154 private int _cachedAsyncCloseCount = -1; // value of the connection's CloseCount property when the asyncResult was set; tracks when connections are closed after an async operation
155 private TaskCompletionSource<object> _cachedAsyncResult = null;
156 private SqlConnection _cachedAsyncConnection = null; // Used to validate that the connection hasn't changed when end the connection;
157 private SqlDataReader _cachedAsyncReader = null;
158 private RunBehavior _cachedRunBehavior = RunBehavior.ReturnImmediately;
159 private string _cachedSetOptions = null;
160 private string _cachedEndMethod = null;
162 internal CachedAsyncState () {
165 internal SqlDataReader CachedAsyncReader {
166 get {return _cachedAsyncReader;}
168 internal RunBehavior CachedRunBehavior {
169 get {return _cachedRunBehavior;}
171 internal string CachedSetOptions {
172 get {return _cachedSetOptions;}
174 internal bool PendingAsyncOperation {
175 get {return (null != _cachedAsyncResult);}
177 internal string EndMethodName {
178 get { return _cachedEndMethod; }
181 internal bool IsActiveConnectionValid(SqlConnection activeConnection) {
182 return (_cachedAsyncConnection == activeConnection && _cachedAsyncCloseCount == activeConnection.CloseCount);
185 [ReliabilityContract(Consistency.WillNotCorruptState, Cer.Success)]
186 internal void ResetAsyncState() {
187 _cachedAsyncCloseCount = -1;
188 _cachedAsyncResult = null;
189 if (_cachedAsyncConnection != null) {
190 _cachedAsyncConnection.AsyncCommandInProgress = false;
191 _cachedAsyncConnection = null;
193 _cachedAsyncReader = null;
194 _cachedRunBehavior = RunBehavior.ReturnImmediately;
195 _cachedSetOptions = null;
196 _cachedEndMethod = null;
199 internal void SetActiveConnectionAndResult(TaskCompletionSource<object> completion, string endMethod, SqlConnection activeConnection) {
200 Debug.Assert(activeConnection != null, "Unexpected null connection argument on SetActiveConnectionAndResult!");
201 TdsParser parser = activeConnection.Parser;
202 if ((parser == null) || (parser.State == TdsParserState.Closed) || (parser.State == TdsParserState.Broken)) {
203 throw ADP.ClosedConnectionError();
206 _cachedAsyncCloseCount = activeConnection.CloseCount;
207 _cachedAsyncResult = completion;
208 if (null != activeConnection && !parser.MARSOn) {
209 if (activeConnection.AsyncCommandInProgress)
210 throw SQL.MARSUnspportedOnConnection();
212 _cachedAsyncConnection = activeConnection;
214 // Should only be needed for non-MARS, but set anyways.
215 _cachedAsyncConnection.AsyncCommandInProgress = true;
216 _cachedEndMethod = endMethod;
219 internal void SetAsyncReaderState (SqlDataReader ds, RunBehavior runBehavior, string optionSettings) {
220 _cachedAsyncReader = ds;
221 _cachedRunBehavior = runBehavior;
222 _cachedSetOptions = optionSettings;
226 CachedAsyncState _cachedAsyncState = null;
228 private CachedAsyncState cachedAsyncState {
230 if (_cachedAsyncState == null) {
231 _cachedAsyncState = new CachedAsyncState ();
233 return _cachedAsyncState;
237 // sql reader will pull this value out for each NextResult call. It is not cumulative
238 // _rowsAffected is cumulative for ExecuteNonQuery across all rpc batches
239 internal int _rowsAffected = -1; // rows affected by the command
241 // number of rows affected by sp_describe_parameter_encryption.
242 // The below line is used only for debug asserts and not exposed publicly or impacts functionality otherwise.
243 private int _rowsAffectedBySpDescribeParameterEncryption = -1;
245 private SqlNotificationRequest _notification;
246 private bool _notificationAutoEnlist = true; // Notifications auto enlistment is turned on by default
248 // transaction support
249 private SqlTransaction _transaction;
251 private StatementCompletedEventHandler _statementCompletedEventHandler;
253 private TdsParserStateObject _stateObj; // this is the TDS session we're using.
255 // Volatile bool used to synchronize with cancel thread the state change of an executing
256 // command going from pre-processing to obtaining a stateObject. The cancel synchronization
257 // we require in the command is only from entering an Execute* API to obtaining a
258 // stateObj. Once a stateObj is successfully obtained, cancel synchronization is handled
259 // by the stateObject.
260 private volatile bool _pendingCancel;
262 private bool _batchRPCMode;
263 private List<_SqlRPC> _RPCList;
264 private _SqlRPC[] _SqlRPCBatchArray;
265 private _SqlRPC[] _sqlRPCParameterEncryptionReqArray;
266 private List<SqlParameterCollection> _parameterCollectionList;
267 private int _currentlyExecutingBatch;
270 /// This variable is used to keep track of which RPC batch's results are being read when reading the results of
271 /// describe parameter encryption RPC requests in BatchRPCMode.
273 private int _currentlyExecutingDescribeParameterEncryptionRPC;
276 /// A flag to indicate if we have in-progress describe parameter encryption RPC requests.
277 /// Reset to false when completed.
279 private bool _isDescribeParameterEncryptionRPCCurrentlyInProgress;
282 /// Return the flag that indicates if describe parameter encryption RPC requests are in-progress.
284 internal bool IsDescribeParameterEncryptionRPCCurrentlyInProgress {
286 return _isDescribeParameterEncryptionRPCCurrentlyInProgress;
291 // Smi execution-specific stuff
293 sealed private class CommandEventSink : SmiEventSink_Default {
294 private SqlCommand _command;
296 internal CommandEventSink( SqlCommand command ) : base( ) {
300 internal override void StatementCompleted( int rowsAffected ) {
301 if (Bid.AdvancedOn) {
302 Bid.Trace("<sc.SqlCommand.CommandEventSink.StatementCompleted|ADV> %d#, rowsAffected=%d.\n", _command.ObjectID, rowsAffected);
304 _command.InternalRecordsAffected = rowsAffected;
313 internal override void BatchCompleted() {
314 if (Bid.AdvancedOn) {
315 Bid.Trace("<sc.SqlCommand.CommandEventSink.BatchCompleted|ADV> %d#.\n", _command.ObjectID);
319 internal override void ParametersAvailable( SmiParameterMetaData[] metaData, ITypedGettersV3 parameterValues ) {
320 if (Bid.AdvancedOn) {
321 Bid.Trace("<sc.SqlCommand.CommandEventSink.ParametersAvailable|ADV> %d# metaData.Length=%d.\n", _command.ObjectID, (null!=metaData)?metaData.Length:-1);
323 if (null != metaData) {
324 for (int i=0; i < metaData.Length; i++) {
325 Bid.Trace("<sc.SqlCommand.CommandEventSink.ParametersAvailable|ADV> %d#, metaData[%d] is %ls%ls\n",
326 _command.ObjectID, i, metaData[i].GetType().ToString(), metaData[i].TraceString());
330 Debug.Assert(SmiContextFactory.Instance.NegotiatedSmiVersion >= SmiContextFactory.YukonVersion);
331 _command.OnParametersAvailableSmi( metaData, parameterValues );
334 internal override void ParameterAvailable(SmiParameterMetaData metaData, SmiTypedGetterSetter parameterValues, int ordinal)
336 if (Bid.AdvancedOn) {
337 if (null != metaData) {
338 Bid.Trace("<sc.SqlCommand.CommandEventSink.ParameterAvailable|ADV> %d#, metaData[%d] is %ls%ls\n",
339 _command.ObjectID, ordinal, metaData.GetType().ToString(), metaData.TraceString());
342 Debug.Assert(SmiContextFactory.Instance.NegotiatedSmiVersion >= SmiContextFactory.KatmaiVersion);
343 _command.OnParameterAvailableSmi(metaData, parameterValues, ordinal);
347 private SmiContext _smiRequestContext; // context that _smiRequest came from
348 private CommandEventSink _smiEventSink;
349 private SmiEventSink_DeferedProcessing _outParamEventSink;
351 private CommandEventSink EventSink {
353 if ( null == _smiEventSink ) {
354 _smiEventSink = new CommandEventSink( this );
357 _smiEventSink.Parent = InternalSmiConnection.CurrentEventSink;
358 return _smiEventSink;
362 private SmiEventSink_DeferedProcessing OutParamEventSink {
364 if (null == _outParamEventSink) {
365 _outParamEventSink = new SmiEventSink_DeferedProcessing(EventSink);
368 _outParamEventSink.Parent = EventSink;
371 return _outParamEventSink;
376 public SqlCommand() : base() {
377 GC.SuppressFinalize(this);
380 public SqlCommand(string cmdText) : this() {
381 CommandText = cmdText;
384 public SqlCommand(string cmdText, SqlConnection connection) : this() {
385 CommandText = cmdText;
386 Connection = connection;
389 public SqlCommand(string cmdText, SqlConnection connection, SqlTransaction transaction) : this() {
390 CommandText = cmdText;
391 Connection = connection;
392 Transaction = transaction;
395 public SqlCommand(string cmdText, SqlConnection connection, SqlTransaction transaction, SqlCommandColumnEncryptionSetting columnEncryptionSetting) : this() {
396 CommandText = cmdText;
397 Connection = connection;
398 Transaction = transaction;
399 _columnEncryptionSetting = columnEncryptionSetting;
402 private SqlCommand(SqlCommand from) : this() { // Clone
403 CommandText = from.CommandText;
404 CommandTimeout = from.CommandTimeout;
405 CommandType = from.CommandType;
406 Connection = from.Connection;
407 DesignTimeVisible = from.DesignTimeVisible;
408 Transaction = from.Transaction;
409 UpdatedRowSource = from.UpdatedRowSource;
410 _columnEncryptionSetting = from.ColumnEncryptionSetting;
412 SqlParameterCollection parameters = Parameters;
413 foreach(object parameter in from.Parameters) {
414 parameters.Add((parameter is ICloneable) ? (parameter as ICloneable).Clone() : parameter);
420 Editor("Microsoft.VSDesigner.Data.Design.DbConnectionEditor, " + AssemblyRef.MicrosoftVSDesigner, "System.Drawing.Design.UITypeEditor, " + AssemblyRef.SystemDrawing),
421 ResCategoryAttribute(Res.DataCategory_Data),
422 ResDescriptionAttribute(Res.DbCommand_Connection),
424 new public SqlConnection Connection {
426 return _activeConnection;
429 // Don't allow the connection to be changed while in a async opperation.
430 if (_activeConnection != value && _activeConnection != null) { // If new value...
431 if (cachedAsyncState.PendingAsyncOperation) { // If in pending async state, throw.
432 throw SQL.CannotModifyPropertyAsyncOperationInProgress(SQL.Connection);
436 // Check to see if the currently set transaction has completed. If so,
437 // null out our local reference.
438 if (null != _transaction && _transaction.Connection == null) {
442 // If the connection has changes, then the request context may have changed as well
443 _smiRequestContext = null;
445 // Command is no longer prepared on new connection, cleanup prepare status
447 if (_activeConnection != value && _activeConnection != null) {
448 RuntimeHelpers.PrepareConstrainedRegions();
451 TdsParser.ReliabilitySection tdsReliabilitySection = new TdsParser.ReliabilitySection();
452 RuntimeHelpers.PrepareConstrainedRegions();
454 tdsReliabilitySection.Start();
461 tdsReliabilitySection.Stop();
465 catch (System.OutOfMemoryException) {
466 _activeConnection.InnerConnection.DoomThisConnection();
469 catch (System.StackOverflowException) {
470 _activeConnection.InnerConnection.DoomThisConnection();
473 catch (System.Threading.ThreadAbortException) {
474 _activeConnection.InnerConnection.DoomThisConnection();
478 // we do not really care about errors in unprepare (may be the old connection went bad)
481 // clean prepare status (even successfull Unprepare does not do that)
483 _execType = EXECTYPE.UNPREPARED;
488 _activeConnection = value; // UNDONE: Designers need this setter. Should we block other scenarios?
490 Bid.Trace("<sc.SqlCommand.set_Connection|API> %d#, %d#\n", ObjectID, ((null != value) ? value.ObjectID : -1));
494 override protected DbConnection DbConnection { // V1.2.3300
499 Connection = (SqlConnection)value;
503 private SqlInternalConnectionSmi InternalSmiConnection {
505 return (SqlInternalConnectionSmi)_activeConnection.InnerConnection;
509 private SqlInternalConnectionTds InternalTdsConnection {
511 return (SqlInternalConnectionTds)_activeConnection.InnerConnection;
515 private bool IsShiloh {
517 Debug.Assert(_activeConnection != null, "The active connection is null!");
518 if (_activeConnection == null)
520 return _activeConnection.IsShiloh;
526 ResCategoryAttribute(Res.DataCategory_Notification),
527 ResDescriptionAttribute(Res.SqlCommand_NotificationAutoEnlist),
529 public bool NotificationAutoEnlist {
531 return _notificationAutoEnlist;
534 _notificationAutoEnlist = value;
540 DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden), // MDAC 90471
541 ResCategoryAttribute(Res.DataCategory_Notification),
542 ResDescriptionAttribute(Res.SqlCommand_Notification),
544 public SqlNotificationRequest Notification {
546 return _notification;
549 Bid.Trace("<sc.SqlCommand.set_Notification|API> %d#\n", ObjectID);
551 _notification = value;
556 internal SqlStatistics Statistics {
558 if (null != _activeConnection) {
559 if (_activeConnection.StatisticsEnabled) {
560 return _activeConnection.Statistics;
569 DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden),
570 ResDescriptionAttribute(Res.DbCommand_Transaction),
572 new public SqlTransaction Transaction {
574 // if the transaction object has been zombied, just return null
575 if ((null != _transaction) && (null == _transaction.Connection)) { // MDAC 72720
581 // Don't allow the transaction to be changed while in a async opperation.
582 if (_transaction != value && _activeConnection != null) { // If new value...
583 if (cachedAsyncState.PendingAsyncOperation) { // If in pending async state, throw
584 throw SQL.CannotModifyPropertyAsyncOperationInProgress(SQL.Transaction);
589 Bid.Trace("<sc.SqlCommand.set_Transaction|API> %d#\n", ObjectID);
590 _transaction = value;
594 override protected DbTransaction DbTransaction { // V1.2.3300
599 Transaction = (SqlTransaction)value;
605 Editor("Microsoft.VSDesigner.Data.SQL.Design.SqlCommandTextEditor, " + AssemblyRef.MicrosoftVSDesigner, "System.Drawing.Design.UITypeEditor, " + AssemblyRef.SystemDrawing),
606 RefreshProperties(RefreshProperties.All), // MDAC 67707
607 ResCategoryAttribute(Res.DataCategory_Data),
608 ResDescriptionAttribute(Res.DbCommand_CommandText),
610 override public string CommandText { // V1.2.3300, XXXCommand V1.0.5000
612 string value = _commandText;
613 return ((null != value) ? value : ADP.StrEmpty);
617 Bid.Trace("<sc.SqlCommand.set_CommandText|API> %d#, '", ObjectID);
618 Bid.PutStr(value); // Use PutStr to write out entire string
621 if (0 != ADP.SrcCompare(_commandText, value)) {
623 _commandText = value;
630 DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden),
631 ResCategoryAttribute(Res.DataCategory_Data),
632 ResDescriptionAttribute(Res.TCE_SqlCommand_ColumnEncryptionSetting),
634 public SqlCommandColumnEncryptionSetting ColumnEncryptionSetting {
636 return _columnEncryptionSetting;
641 ResCategoryAttribute(Res.DataCategory_Data),
642 ResDescriptionAttribute(Res.DbCommand_CommandTimeout),
644 override public int CommandTimeout { // V1.2.3300, XXXCommand V1.0.5000
646 return _commandTimeout;
649 Bid.Trace("<sc.SqlCommand.set_CommandTimeout|API> %d#, %d\n", ObjectID, value);
651 throw ADP.InvalidCommandTimeout(value);
653 if (value != _commandTimeout) {
655 _commandTimeout = value;
660 public void ResetCommandTimeout() { // V1.2.3300
661 if (ADP.DefaultCommandTimeout != _commandTimeout) {
663 _commandTimeout = ADP.DefaultCommandTimeout;
667 private bool ShouldSerializeCommandTimeout() { // V1.2.3300
668 return (ADP.DefaultCommandTimeout != _commandTimeout);
672 DefaultValue(System.Data.CommandType.Text),
673 RefreshProperties(RefreshProperties.All),
674 ResCategoryAttribute(Res.DataCategory_Data),
675 ResDescriptionAttribute(Res.DbCommand_CommandType),
677 override public CommandType CommandType { // V1.2.3300, XXXCommand V1.0.5000
679 CommandType cmdType = _commandType;
680 return ((0 != cmdType) ? cmdType : CommandType.Text);
683 Bid.Trace("<sc.SqlCommand.set_CommandType|API> %d#, %d{ds.CommandType}\n", ObjectID, (int)value);
684 if (_commandType != value) {
685 switch(value) { // @perfnote: Enum.IsDefined
686 case CommandType.Text:
687 case CommandType.StoredProcedure:
689 _commandType = value;
691 case System.Data.CommandType.TableDirect:
692 throw SQL.NotSupportedCommandType(value);
694 throw ADP.InvalidCommandType(value);
700 // @devnote: By default, the cmd object is visible on the design surface (i.e. VS7 Server Tray)
701 // to limit the number of components that clutter the design surface,
702 // when the DataAdapter design wizard generates the insert/update/delete commands it will
703 // set the DesignTimeVisible property to false so that cmds won't appear as individual objects
708 EditorBrowsableAttribute(EditorBrowsableState.Never),
710 public override bool DesignTimeVisible { // V1.2.3300, XXXCommand V1.0.5000
712 return !_designTimeInvisible;
715 _designTimeInvisible = !value;
716 TypeDescriptor.Refresh(this); // VS7 208845
721 DesignerSerializationVisibility(DesignerSerializationVisibility.Content),
722 ResCategoryAttribute(Res.DataCategory_Data),
723 ResDescriptionAttribute(Res.DbCommand_Parameters),
725 new public SqlParameterCollection Parameters {
727 if (null == this._parameters) {
728 // delay the creation of the SqlParameterCollection
729 // until user actually uses the Parameters property
730 this._parameters = new SqlParameterCollection();
732 return this._parameters;
736 override protected DbParameterCollection DbParameterCollection { // V1.2.3300
743 DefaultValue(System.Data.UpdateRowSource.Both),
744 ResCategoryAttribute(Res.DataCategory_Update),
745 ResDescriptionAttribute(Res.DbCommand_UpdatedRowSource),
747 override public UpdateRowSource UpdatedRowSource { // V1.2.3300, XXXCommand V1.0.5000
749 return _updatedRowSource;
752 switch(value) { // @perfnote: Enum.IsDefined
753 case UpdateRowSource.None:
754 case UpdateRowSource.OutputParameters:
755 case UpdateRowSource.FirstReturnedRecord:
756 case UpdateRowSource.Both:
757 _updatedRowSource = value;
760 throw ADP.InvalidUpdateRowSource(value);
766 ResCategoryAttribute(Res.DataCategory_StatementCompleted),
767 ResDescriptionAttribute(Res.DbCommand_StatementCompleted),
769 public event StatementCompletedEventHandler StatementCompleted {
771 _statementCompletedEventHandler += value;
774 _statementCompletedEventHandler -= value;
778 internal void OnStatementCompleted(int recordCount) { // V1.2.3300
779 if (0 <= recordCount) {
780 StatementCompletedEventHandler handler = _statementCompletedEventHandler;
781 if (null != handler) {
783 Bid.Trace("<sc.SqlCommand.OnStatementCompleted|INFO> %d#, recordCount=%d\n", ObjectID, recordCount);
784 handler(this, new StatementCompletedEventArgs(recordCount));
788 if (!ADP.IsCatchableOrSecurityExceptionType(e)) {
792 ADP.TraceExceptionWithoutRethrow(e);
798 private void PropertyChanging() { // also called from SqlParameterCollection
802 override public void Prepare() {
803 SqlConnection.ExecutePermission.Demand();
805 // Reset _pendingCancel upon entry into any Execute - used to synchronize state
806 // between entry into Execute* API and the thread obtaining the stateObject.
807 _pendingCancel = false;
809 // Context connection's prepare is a no-op
810 if (null != _activeConnection && _activeConnection.IsContextConnection) {
814 SqlStatistics statistics = null;
816 Bid.ScopeEnter(out hscp, "<sc.SqlCommand.Prepare|API> %d#", ObjectID);
817 Bid.CorrelationTrace("<sc.SqlCommand.Prepare|API|Correlation> ObjectID%d#, ActivityID %ls\n", ObjectID);
818 statistics = SqlStatistics.StartTimer(Statistics);
820 // only prepare if batch with parameters
823 this.IsPrepared && !this.IsDirty
824 || (this.CommandType == CommandType.StoredProcedure)
826 (System.Data.CommandType.Text == this.CommandType)
827 && (0 == GetParameterCount (_parameters))
831 if (null != Statistics) {
832 Statistics.SafeIncrement (ref Statistics._prepares);
834 _hiddenPrepare = false;
837 // Validate the command outside of the try\catch to avoid putting the _stateObj on error
838 ValidateCommand(ADP.Prepare, false /*not async*/);
840 bool processFinallyBlock = true;
841 TdsParser bestEffortCleanupTarget = null;
842 RuntimeHelpers.PrepareConstrainedRegions();
844 bestEffortCleanupTarget = SqlInternalConnection.GetBestEffortCleanupTarget(_activeConnection);
846 // NOTE: The state object isn't actually needed for this, but it is still here for back-compat (since it does a bunch of checks)
849 // Loop through parameters ensuring that we do not have unspecified types, sizes, scales, or precisions
850 if (null != _parameters) {
851 int count = _parameters.Count;
852 for (int i = 0; i < count; ++i) {
853 _parameters[i].Prepare(this); // MDAC 67063
858 TdsParser.ReliabilitySection tdsReliabilitySection = new TdsParser.ReliabilitySection();
859 RuntimeHelpers.PrepareConstrainedRegions();
861 tdsReliabilitySection.Start();
869 tdsReliabilitySection.Stop();
873 catch (System.OutOfMemoryException e) {
874 processFinallyBlock = false;
875 _activeConnection.Abort(e);
878 catch (System.StackOverflowException e) {
879 processFinallyBlock = false;
880 _activeConnection.Abort(e);
883 catch (System.Threading.ThreadAbortException e) {
884 processFinallyBlock = false;
885 _activeConnection.Abort(e);
887 SqlInternalConnection.BestEffortCleanup(bestEffortCleanupTarget);
890 catch (Exception e) {
891 processFinallyBlock = ADP.IsCatchableExceptionType(e);
895 if (processFinallyBlock) {
896 _hiddenPrepare = false; // The command is now officially prepared
898 ReliablePutStateObject();
903 SqlStatistics.StopTimer(statistics);
904 Bid.ScopeLeave(ref hscp);
907 private void InternalPrepare() {
909 Debug.Assert(_cachedMetaData == null || !_dirty, "dirty query should not have cached metadata!"); // can have cached metadata if dirty because of parameters
911 // someone changed the command text or the parameter schema so we must unprepare the command
914 this.IsDirty = false;
916 Debug.Assert(_execType != EXECTYPE.PREPARED, "Invalid attempt to Prepare already Prepared command!");
917 Debug.Assert(_activeConnection != null, "must have an open connection to Prepare");
918 Debug.Assert(null != _stateObj, "TdsParserStateObject should not be null");
919 Debug.Assert(null != _stateObj.Parser, "TdsParser class should not be null in Command.Execute!");
920 Debug.Assert(_stateObj.Parser == _activeConnection.Parser, "stateobject parser not same as connection parser");
921 Debug.Assert(false == _inPrepare, "Already in Prepare cycle, this.inPrepare should be false!");
923 // remember that the user wants to do a prepare but don't actually do an rpc
924 _execType = EXECTYPE.PREPAREPENDING;
925 // Note the current close count of the connection - this will tell us if the connection has been closed between calls to Prepare() and Execute
926 _preparedConnectionCloseCount = _activeConnection.CloseCount;
927 _preparedConnectionReconnectCount = _activeConnection.ReconnectCount;
929 if (null != Statistics) {
930 Statistics.SafeIncrement(ref Statistics._prepares);
934 // SqlInternalConnectionTds needs to be able to unprepare a statement
935 internal void Unprepare() {
936 // Context connection's prepare is a no-op
937 if (_activeConnection.IsContextConnection) {
941 Debug.Assert(true == IsPrepared, "Invalid attempt to Unprepare a non-prepared command!");
942 Debug.Assert(_activeConnection != null, "must have an open connection to UnPrepare");
943 Debug.Assert(false == _inPrepare, "_inPrepare should be false!");
945 // @devnote: we're always falling back to Prepare pending
946 // @devnote: This seems broken because once the command is prepared it will - always - be a
947 // @devnote: prepared execution.
948 // @devnote: Even replacing the parameterlist with something completely different or
949 // @devnote: changing the commandtext to a non-parameterized query will result in prepared execution
951 // @devnote: We need to keep the behavior for backward compatibility though (non-breaking change)
953 _execType = EXECTYPE.PREPAREPENDING;
954 // Don't zero out the handle because we'll pass it in to sp_prepexec on the next prepare
955 // Unless the close count isn't the same as when we last prepared
956 if ((_activeConnection.CloseCount != _preparedConnectionCloseCount) || (_activeConnection.ReconnectCount != _preparedConnectionReconnectCount)) {
961 _cachedMetaData = null;
962 Bid.Trace("<sc.SqlCommand.Prepare|INFO> %d#, Command unprepared.\n", ObjectID);
966 // Cancel is supposed to be multi-thread safe.
967 // It doesn't make sense to verify the connection exists or that it is open during cancel
968 // because immediately after checkin the connection can be closed or removed via another thread.
970 override public void Cancel() {
972 Bid.ScopeEnter(out hscp, "<sc.SqlCommand.Cancel|API> %d#", ObjectID);
974 Bid.CorrelationTrace("<sc.SqlCommand.Cancel|API|Correlation> ObjectID%d#, ActivityID %ls\n", ObjectID);
976 SqlStatistics statistics = null;
978 statistics = SqlStatistics.StartTimer(Statistics);
980 // If we are in reconnect phase simply cancel the waiting task
981 var reconnectCompletionSource = _reconnectionCompletionSource;
982 if (reconnectCompletionSource != null) {
983 if (reconnectCompletionSource.TrySetCanceled()) {
988 // the pending data flag means that we are awaiting a response or are in the middle of proccessing a response
989 // if we have no pending data, then there is nothing to cancel
990 // if we have pending data, but it is not a result of this command, then we don't cancel either. Note that
991 // this model is implementable because we only allow one active command at any one time. This code
992 // will have to change we allow multiple outstanding batches
995 if (null == _activeConnection) {
998 SqlInternalConnectionTds connection = (_activeConnection.InnerConnection as SqlInternalConnectionTds);
999 if (null == connection) { // Fail with out locking
1003 // The lock here is to protect against the command.cancel / connection.close race condition
1004 // The SqlInternalConnectionTds is set to OpenBusy during close, once this happens the cast below will fail and
1005 // the command will no longer be cancelable. It might be desirable to be able to cancel the close opperation, but this is
1006 // outside of the scope of Whidbey RTM. See (SqlConnection::Close) for other lock.
1008 if (connection != (_activeConnection.InnerConnection as SqlInternalConnectionTds)) { // make sure the connection held on the active connection is what we have stored in our temp connection variable, if not between getting "connection" and takeing the lock, the connection has been closed
1012 TdsParser parser = connection.Parser;
1013 if (null == parser) {
1017 TdsParser bestEffortCleanupTarget = null;
1018 RuntimeHelpers.PrepareConstrainedRegions();
1021 TdsParser.ReliabilitySection tdsReliabilitySection = new TdsParser.ReliabilitySection();
1023 RuntimeHelpers.PrepareConstrainedRegions();
1025 tdsReliabilitySection.Start();
1029 bestEffortCleanupTarget = SqlInternalConnection.GetBestEffortCleanupTarget(_activeConnection);
1031 if (!_pendingCancel) { // Do nothing if aleady pending.
1032 // Before attempting actual cancel, set the _pendingCancel flag to false.
1033 // This denotes to other thread before obtaining stateObject from the
1034 // session pool that there is another thread wishing to cancel.
1035 // The period in question is between entering the ExecuteAPI and obtaining
1037 _pendingCancel = true;
1039 TdsParserStateObject stateObj = _stateObj;
1040 if (null != stateObj) {
1041 stateObj.Cancel(ObjectID);
1044 SqlDataReader reader = connection.FindLiveReader(this);
1045 if (reader != null) {
1046 reader.Cancel(ObjectID);
1053 tdsReliabilitySection.Stop();
1057 catch (System.OutOfMemoryException e) {
1058 _activeConnection.Abort(e);
1061 catch (System.StackOverflowException e) {
1062 _activeConnection.Abort(e);
1065 catch (System.Threading.ThreadAbortException e) {
1066 _activeConnection.Abort(e);
1067 SqlInternalConnection.BestEffortCleanup(bestEffortCleanupTarget);
1073 SqlStatistics.StopTimer(statistics);
1074 Bid.ScopeLeave(ref hscp);
1078 new public SqlParameter CreateParameter() {
1079 return new SqlParameter();
1082 override protected DbParameter CreateDbParameter() {
1083 return CreateParameter();
1086 override protected void Dispose(bool disposing) {
1087 if (disposing) { // release mananged objects
1089 // V1.0, V1.1 did not reset the Connection, Parameters, CommandText, WebData 100524
1090 //_parameters = null;
1091 //_activeConnection = null;
1092 //_statistics = null;
1093 //CommandText = null;
1094 _cachedMetaData = null;
1096 // release unmanaged objects
1097 base.Dispose(disposing);
1100 override public object ExecuteScalar() {
1101 SqlConnection.ExecutePermission.Demand();
1103 // Reset _pendingCancel upon entry into any Execute - used to synchronize state
1104 // between entry into Execute* API and the thread obtaining the stateObject.
1105 _pendingCancel = false;
1107 SqlStatistics statistics = null;
1109 Bid.ScopeEnter(out hscp, "<sc.SqlCommand.ExecuteScalar|API> %d#", ObjectID);
1110 Bid.CorrelationTrace("<sc.SqlCommand.ExecuteScalar|API|Correlation> ObjectID%d#, ActivityID %ls\n", ObjectID);
1112 bool success = false;
1113 int? sqlExceptionNumber = null;
1115 statistics = SqlStatistics.StartTimer(Statistics);
1116 WriteBeginExecuteEvent();
1118 ds = RunExecuteReader(0, RunBehavior.ReturnImmediately, true, ADP.ExecuteScalar);
1119 object result = CompleteExecuteScalar(ds, false);
1123 catch (SqlException ex) {
1124 sqlExceptionNumber = ex.Number;
1128 SqlStatistics.StopTimer(statistics);
1129 Bid.ScopeLeave(ref hscp);
1130 WriteEndExecuteEvent(success, sqlExceptionNumber, synchronous: true);
1134 private object CompleteExecuteScalar(SqlDataReader ds, bool returnSqlValue) {
1135 object retResult = null;
1139 if (ds.FieldCount > 0) {
1140 if (returnSqlValue) {
1141 retResult = ds.GetSqlValue(0);
1144 retResult = ds.GetValue(0);
1150 // clean off the wire
1157 override public int ExecuteNonQuery() {
1158 SqlConnection.ExecutePermission.Demand();
1160 // Reset _pendingCancel upon entry into any Execute - used to synchronize state
1161 // between entry into Execute* API and the thread obtaining the stateObject.
1162 _pendingCancel = false;
1164 SqlStatistics statistics = null;
1166 Bid.ScopeEnter(out hscp, "<sc.SqlCommand.ExecuteNonQuery|API> %d#", ObjectID);
1167 Bid.CorrelationTrace("<sc.SqlCommand.ExecuteNonQuery|API|Correlation> ObjectID%d#, ActivityID %ls\n", ObjectID);
1168 bool success = false;
1169 int? sqlExceptionNumber = null;
1171 statistics = SqlStatistics.StartTimer(Statistics);
1172 WriteBeginExecuteEvent();
1173 InternalExecuteNonQuery(null, ADP.ExecuteNonQuery, false, CommandTimeout);
1175 return _rowsAffected;
1177 catch (SqlException ex) {
1178 sqlExceptionNumber = ex.Number;
1182 SqlStatistics.StopTimer(statistics);
1183 Bid.ScopeLeave(ref hscp);
1184 WriteEndExecuteEvent(success, sqlExceptionNumber, synchronous : true);
1188 // Handles in-proc execute-to-pipe functionality
1189 // Identical to ExecuteNonQuery
1190 internal void ExecuteToPipe( SmiContext pipeContext ) {
1191 SqlConnection.ExecutePermission.Demand();
1193 // Reset _pendingCancel upon entry into any Execute - used to synchronize state
1194 // between entry into Execute* API and the thread obtaining the stateObject.
1195 _pendingCancel = false;
1197 SqlStatistics statistics = null;
1199 Bid.ScopeEnter(out hscp, "<sc.SqlCommand.ExecuteToPipe|INFO> %d#", ObjectID);
1201 statistics = SqlStatistics.StartTimer(Statistics);
1202 InternalExecuteNonQuery(null, ADP.ExecuteNonQuery, true, CommandTimeout);
1205 SqlStatistics.StopTimer(statistics);
1206 Bid.ScopeLeave(ref hscp);
1210 [System.Security.Permissions.HostProtectionAttribute(ExternalThreading=true)]
1211 public IAsyncResult BeginExecuteNonQuery() {
1212 // BeginExecuteNonQuery will track ExecutionTime for us
1213 return BeginExecuteNonQuery(null, null);
1216 [System.Security.Permissions.HostProtectionAttribute(ExternalThreading=true)]
1217 public IAsyncResult BeginExecuteNonQuery(AsyncCallback callback, object stateObject) {
1218 Bid.CorrelationTrace("<sc.SqlCommand.BeginExecuteNonQuery|API|Correlation> ObjectID%d#, ActivityID %ls\n", ObjectID);
1219 SqlConnection.ExecutePermission.Demand();
1220 return BeginExecuteNonQueryInternal(callback, stateObject, 0);
1223 private IAsyncResult BeginExecuteNonQueryAsync(AsyncCallback callback, object stateObject) {
1224 return BeginExecuteNonQueryInternal(callback, stateObject, CommandTimeout, asyncWrite:true);
1227 private IAsyncResult BeginExecuteNonQueryInternal(AsyncCallback callback, object stateObject, int timeout, bool asyncWrite = false) {
1228 // Reset _pendingCancel upon entry into any Execute - used to synchronize state
1229 // between entry into Execute* API and the thread obtaining the stateObject.
1230 _pendingCancel = false;
1232 ValidateAsyncCommand(); // Special case - done outside of try/catches to prevent putting a stateObj
1233 // back into pool when we should not.
1235 SqlStatistics statistics = null;
1237 statistics = SqlStatistics.StartTimer(Statistics);
1238 WriteBeginExecuteEvent();
1239 TaskCompletionSource<object> completion = new TaskCompletionSource<object>(stateObject);
1241 try { // InternalExecuteNonQuery already has reliability block, but if failure will not put stateObj back into pool.
1242 Task execNQ = InternalExecuteNonQuery(completion, ADP.BeginExecuteNonQuery, false, timeout, asyncWrite);
1243 if (execNQ != null) {
1244 AsyncHelper.ContinueTask(execNQ, completion, () => BeginExecuteNonQueryInternalReadStage(completion));
1247 BeginExecuteNonQueryInternalReadStage(completion);
1250 catch (Exception e) {
1251 if (!ADP.IsCatchableOrSecurityExceptionType(e)) {
1252 // If not catchable - the connection has already been caught and doomed in RunExecuteReader.
1256 // For async, RunExecuteReader will never put the stateObj back into the pool, so do so now.
1257 ReliablePutStateObject();
1261 // Add callback after work is done to avoid overlapping Begin\End methods
1262 if (callback != null) {
1263 completion.Task.ContinueWith((t) => callback(t), TaskScheduler.Default);
1266 return completion.Task;
1269 SqlStatistics.StopTimer(statistics);
1273 private void BeginExecuteNonQueryInternalReadStage(TaskCompletionSource<object> completion) {
1274 // Read SNI does not have catches for async exceptions, handle here.
1275 TdsParser bestEffortCleanupTarget = null;
1276 RuntimeHelpers.PrepareConstrainedRegions();
1279 TdsParser.ReliabilitySection tdsReliabilitySection = new TdsParser.ReliabilitySection();
1281 RuntimeHelpers.PrepareConstrainedRegions();
1283 tdsReliabilitySection.Start();
1287 bestEffortCleanupTarget = SqlInternalConnection.GetBestEffortCleanupTarget(_activeConnection);
1288 // must finish caching information before ReadSni which can activate the callback before returning
1289 cachedAsyncState.SetActiveConnectionAndResult(completion, ADP.EndExecuteNonQuery, _activeConnection);
1290 _stateObj.ReadSni(completion);
1294 tdsReliabilitySection.Stop();
1298 catch (System.OutOfMemoryException e) {
1299 _activeConnection.Abort(e);
1302 catch (System.StackOverflowException e) {
1303 _activeConnection.Abort(e);
1306 catch (System.Threading.ThreadAbortException e) {
1307 _activeConnection.Abort(e);
1308 SqlInternalConnection.BestEffortCleanup(bestEffortCleanupTarget);
1312 // Similarly, if an exception occurs put the stateObj back into the pool.
1313 // and reset async cache information to allow a second async execute
1314 if (null != _cachedAsyncState) {
1315 _cachedAsyncState.ResetAsyncState();
1317 ReliablePutStateObject();
1322 private void VerifyEndExecuteState(Task completionTask, String endMethod) {
1323 if (null == completionTask) {
1324 throw ADP.ArgumentNull("asyncResult");
1326 if (completionTask.IsCanceled) {
1327 if (_stateObj != null) {
1328 _stateObj.Parser.State = TdsParserState.Broken; // We failed to respond to attention, we have to quit!
1329 _stateObj.Parser.Connection.BreakConnection();
1330 _stateObj.Parser.ThrowExceptionAndWarning(_stateObj);
1333 Debug.Assert(_reconnectionCompletionSource == null || _reconnectionCompletionSource.Task.IsCanceled, "ReconnectCompletionSource should be null or cancelled");
1334 throw SQL.CR_ReconnectionCancelled();
1337 else if (completionTask.IsFaulted) {
1338 throw completionTask.Exception.InnerException;
1341 // If transparent parameter encryption was attempted, then we need to skip other checks like those on EndMethodName
1342 // since we want to wait for async results before checking those fields.
1343 if (IsColumnEncryptionEnabled) {
1344 if (_activeConnection.State != ConnectionState.Open) {
1345 // If the connection is not 'valid' then it was closed while we were executing
1346 throw ADP.ClosedConnectionError();
1352 if (cachedAsyncState.EndMethodName == null) {
1353 throw ADP.MethodCalledTwice(endMethod);
1355 if (endMethod != cachedAsyncState.EndMethodName) {
1356 throw ADP.MismatchedAsyncResult(cachedAsyncState.EndMethodName, endMethod);
1358 if ((_activeConnection.State != ConnectionState.Open) || (!cachedAsyncState.IsActiveConnectionValid(_activeConnection))) {
1359 // If the connection is not 'valid' then it was closed while we were executing
1360 throw ADP.ClosedConnectionError();
1364 private void WaitForAsyncResults(IAsyncResult asyncResult) {
1365 Task completionTask = (Task) asyncResult;
1366 if (!asyncResult.IsCompleted) {
1367 asyncResult.AsyncWaitHandle.WaitOne();
1369 _stateObj._networkPacketTaskSource = null;
1370 _activeConnection.GetOpenTdsConnection().DecrementAsyncCount();
1373 public int EndExecuteNonQuery(IAsyncResult asyncResult) {
1375 return EndExecuteNonQueryInternal(asyncResult);
1378 Bid.CorrelationTrace("<sc.SqlCommand.EndExecuteNonQuery|API|Correlation> ObjectID%d#, ActivityID %ls\n", ObjectID);
1382 private void ThrowIfReconnectionHasBeenCanceled() {
1383 if (_stateObj == null) {
1384 var reconnectionCompletionSource = _reconnectionCompletionSource;
1385 if (reconnectionCompletionSource != null && reconnectionCompletionSource.Task.IsCanceled) {
1386 throw SQL.CR_ReconnectionCancelled();
1391 private int EndExecuteNonQueryAsync(IAsyncResult asyncResult) {
1392 Bid.CorrelationTrace("<sc.SqlCommand.EndExecuteNonQueryAsync|Info|Correlation> ObjectID%d#, ActivityID %ls\n", ObjectID);
1394 Exception asyncException = ((Task)asyncResult).Exception;
1395 if (asyncException != null) {
1396 // Leftover exception from the Begin...InternalReadStage
1397 ReliablePutStateObject();
1398 throw asyncException.InnerException;
1401 ThrowIfReconnectionHasBeenCanceled();
1402 // lock on _stateObj prevents ----s with close/cancel.
1404 return EndExecuteNonQueryInternal(asyncResult);
1409 private int EndExecuteNonQueryInternal(IAsyncResult asyncResult) {
1410 SqlStatistics statistics = null;
1412 TdsParser bestEffortCleanupTarget = null;
1413 RuntimeHelpers.PrepareConstrainedRegions();
1414 bool success = false;
1415 int? sqlExceptionNumber = null;
1418 TdsParser.ReliabilitySection tdsReliabilitySection = new TdsParser.ReliabilitySection();
1420 RuntimeHelpers.PrepareConstrainedRegions();
1422 tdsReliabilitySection.Start();
1426 bestEffortCleanupTarget = SqlInternalConnection.GetBestEffortCleanupTarget(_activeConnection);
1427 statistics = SqlStatistics.StartTimer(Statistics);
1428 VerifyEndExecuteState((Task)asyncResult, ADP.EndExecuteNonQuery);
1429 WaitForAsyncResults(asyncResult);
1431 // If Transparent parameter encryption was attempted, then we would have skipped the below
1432 // checks in VerifyEndExecuteState since we wanted to wait for WaitForAsyncResults to complete.
1433 if (IsColumnEncryptionEnabled) {
1434 if (cachedAsyncState.EndMethodName == null) {
1435 throw ADP.MethodCalledTwice(ADP.EndExecuteNonQuery);
1438 if (ADP.EndExecuteNonQuery != cachedAsyncState.EndMethodName) {
1439 throw ADP.MismatchedAsyncResult(cachedAsyncState.EndMethodName, ADP.EndExecuteNonQuery);
1442 if (!cachedAsyncState.IsActiveConnectionValid(_activeConnection)) {
1443 // If the connection is not 'valid' then it was closed while we were executing
1444 throw ADP.ClosedConnectionError();
1448 bool processFinallyBlock = true;
1451 CheckThrowSNIException();
1453 // only send over SQL Batch command if we are not a stored proc and have no parameters
1454 if ((System.Data.CommandType.Text == this.CommandType) && (0 == GetParameterCount(_parameters))) {
1457 Debug.Assert(_stateObj._syncOverAsync, "Should not attempt pends in a synchronous call");
1458 bool result = _stateObj.Parser.TryRun(RunBehavior.UntilDone, this, null, null, _stateObj, out dataReady);
1459 if (!result) { throw SQL.SynchronousCallMayNotPend(); }
1462 cachedAsyncState.ResetAsyncState();
1465 else { // otherwise, use a full-fledged execute that can handle params and stored procs
1466 SqlDataReader reader = CompleteAsyncExecuteReader();
1467 if (null != reader) {
1472 catch (SqlException e) {
1473 sqlExceptionNumber = e.Number;
1476 catch (Exception e) {
1477 processFinallyBlock = ADP.IsCatchableExceptionType(e);
1481 if (processFinallyBlock) {
1486 Debug.Assert(null == _stateObj, "non-null state object in EndExecuteNonQuery");
1488 return _rowsAffected;
1492 tdsReliabilitySection.Stop();
1496 catch (System.OutOfMemoryException e) {
1497 _activeConnection.Abort(e);
1500 catch (System.StackOverflowException e) {
1501 _activeConnection.Abort(e);
1504 catch (System.Threading.ThreadAbortException e) {
1505 _activeConnection.Abort(e);
1506 SqlInternalConnection.BestEffortCleanup(bestEffortCleanupTarget);
1509 catch (Exception e) {
1510 if (cachedAsyncState != null) {
1511 cachedAsyncState.ResetAsyncState();
1513 if (ADP.IsCatchableExceptionType(e)) {
1514 ReliablePutStateObject();
1519 SqlStatistics.StopTimer(statistics);
1520 WriteEndExecuteEvent(success, sqlExceptionNumber, synchronous: false);
1524 private Task InternalExecuteNonQuery(TaskCompletionSource<object> completion, string methodName, bool sendToPipe, int timeout, bool asyncWrite = false) {
1525 bool async = (null != completion);
1527 SqlStatistics statistics = Statistics;
1530 TdsParser bestEffortCleanupTarget = null;
1531 RuntimeHelpers.PrepareConstrainedRegions();
1534 TdsParser.ReliabilitySection tdsReliabilitySection = new TdsParser.ReliabilitySection();
1536 RuntimeHelpers.PrepareConstrainedRegions();
1538 tdsReliabilitySection.Start();
1542 bestEffortCleanupTarget = SqlInternalConnection.GetBestEffortCleanupTarget(_activeConnection);
1543 // @devnote: this function may throw for an invalid connection
1544 // @devnote: returns false for empty command text
1545 ValidateCommand(methodName, async);
1546 CheckNotificationStateAndAutoEnlist(); // Only call after validate - requires non null connection!
1550 // only send over SQL Batch command if we are not a stored proc and have no parameters and not in batch RPC mode
1551 if ( _activeConnection.IsContextConnection ) {
1552 if (null != statistics) {
1553 statistics.SafeIncrement(ref statistics._unpreparedExecs);
1556 RunExecuteNonQuerySmi( sendToPipe );
1558 else if (!BatchRPCMode && (System.Data.CommandType.Text == this.CommandType) && (0 == GetParameterCount(_parameters))) {
1559 Debug.Assert( !sendToPipe, "trying to send non-context command to pipe" );
1560 if (null != statistics) {
1561 if (!this.IsDirty && this.IsPrepared) {
1562 statistics.SafeIncrement(ref statistics._preparedExecs);
1565 statistics.SafeIncrement(ref statistics._unpreparedExecs);
1569 task = RunExecuteNonQueryTds(methodName, async, timeout, asyncWrite);
1571 else { // otherwise, use a full-fledged execute that can handle params and stored procs
1572 Debug.Assert( !sendToPipe, "trying to send non-context command to pipe" );
1573 Bid.Trace("<sc.SqlCommand.ExecuteNonQuery|INFO> %d#, Command executed as RPC.\n", ObjectID);
1574 SqlDataReader reader = RunExecuteReader(0, RunBehavior.UntilDone, false, methodName, completion, timeout, out task, asyncWrite);
1577 task = AsyncHelper.CreateContinuationTask(task, () => reader.Close());
1584 Debug.Assert(async || null == _stateObj, "non-null state object in InternalExecuteNonQuery");
1589 tdsReliabilitySection.Stop();
1593 catch (System.OutOfMemoryException e) {
1594 _activeConnection.Abort(e);
1597 catch (System.StackOverflowException e) {
1598 _activeConnection.Abort(e);
1601 catch (System.Threading.ThreadAbortException e) {
1602 _activeConnection.Abort(e);
1603 SqlInternalConnection.BestEffortCleanup(bestEffortCleanupTarget);
1608 public XmlReader ExecuteXmlReader() {
1609 SqlConnection.ExecutePermission.Demand();
1611 // Reset _pendingCancel upon entry into any Execute - used to synchronize state
1612 // between entry into Execute* API and the thread obtaining the stateObject.
1613 _pendingCancel = false;
1615 SqlStatistics statistics = null;
1617 Bid.ScopeEnter(out hscp, "<sc.SqlCommand.ExecuteXmlReader|API> %d#", ObjectID);
1618 Bid.CorrelationTrace("<sc.SqlCommand.ExecuteXmlReader|API|Correlation> ObjectID%d#, ActivityID %ls\n", ObjectID);
1619 bool success = false;
1620 int? sqlExceptionNumber = null;
1622 statistics = SqlStatistics.StartTimer(Statistics);
1623 WriteBeginExecuteEvent();
1625 // use the reader to consume metadata
1627 ds = RunExecuteReader(CommandBehavior.SequentialAccess, RunBehavior.ReturnImmediately, true, ADP.ExecuteXmlReader);
1628 XmlReader result = CompleteXmlReader(ds);
1632 catch (SqlException ex) {
1633 sqlExceptionNumber = ex.Number;
1637 SqlStatistics.StopTimer(statistics);
1638 Bid.ScopeLeave(ref hscp);
1639 WriteEndExecuteEvent(success, sqlExceptionNumber, synchronous : true);
1643 [System.Security.Permissions.HostProtectionAttribute(ExternalThreading=true)]
1644 public IAsyncResult BeginExecuteXmlReader() {
1645 // BeginExecuteXmlReader will track executiontime
1646 return BeginExecuteXmlReader(null, null);
1649 [System.Security.Permissions.HostProtectionAttribute(ExternalThreading=true)]
1650 public IAsyncResult BeginExecuteXmlReader(AsyncCallback callback, object stateObject) {
1651 Bid.CorrelationTrace("<sc.SqlCommand.BeginExecuteXmlReader|API|Correlation> ObjectID%d#, ActivityID %ls\n", ObjectID);
1652 SqlConnection.ExecutePermission.Demand();
1653 return BeginExecuteXmlReaderInternal(callback, stateObject, 0);
1656 private IAsyncResult BeginExecuteXmlReaderAsync(AsyncCallback callback, object stateObject) {
1657 return BeginExecuteXmlReaderInternal(callback, stateObject, CommandTimeout, asyncWrite:true);
1660 private IAsyncResult BeginExecuteXmlReaderInternal(AsyncCallback callback, object stateObject, int timeout, bool asyncWrite = false) {
1661 // Reset _pendingCancel upon entry into any Execute - used to synchronize state
1662 // between entry into Execute* API and the thread obtaining the stateObject.
1663 _pendingCancel = false;
1665 ValidateAsyncCommand(); // Special case - done outside of try/catches to prevent putting a stateObj
1666 // back into pool when we should not.
1668 SqlStatistics statistics = null;
1670 statistics = SqlStatistics.StartTimer(Statistics);
1671 WriteBeginExecuteEvent();
1672 TaskCompletionSource<object> completion = new TaskCompletionSource<object>(stateObject);
1675 try { // InternalExecuteNonQuery already has reliability block, but if failure will not put stateObj back into pool.
1676 RunExecuteReader(CommandBehavior.SequentialAccess, RunBehavior.ReturnImmediately, true, ADP.BeginExecuteXmlReader, completion, timeout, out writeTask, asyncWrite);
1678 catch (Exception e) {
1679 if (!ADP.IsCatchableOrSecurityExceptionType(e)) {
1680 // If not catchable - the connection has already been caught and doomed in RunExecuteReader.
1684 // For async, RunExecuteReader will never put the stateObj back into the pool, so do so now.
1685 ReliablePutStateObject();
1689 if (writeTask != null) {
1690 AsyncHelper.ContinueTask(writeTask, completion, () => BeginExecuteXmlReaderInternalReadStage(completion));
1693 BeginExecuteXmlReaderInternalReadStage(completion);
1696 // Add callback after work is done to avoid overlapping Begin\End methods
1697 if (callback != null) {
1698 completion.Task.ContinueWith((t) => callback(t), TaskScheduler.Default);
1700 return completion.Task;
1703 SqlStatistics.StopTimer(statistics);
1707 private void BeginExecuteXmlReaderInternalReadStage(TaskCompletionSource<object> completion) {
1708 Debug.Assert(completion != null,"Completion source should not be null");
1709 // Read SNI does not have catches for async exceptions, handle here.
1710 TdsParser bestEffortCleanupTarget = null;
1711 RuntimeHelpers.PrepareConstrainedRegions();
1714 TdsParser.ReliabilitySection tdsReliabilitySection = new TdsParser.ReliabilitySection();
1716 RuntimeHelpers.PrepareConstrainedRegions();
1718 tdsReliabilitySection.Start();
1722 bestEffortCleanupTarget = SqlInternalConnection.GetBestEffortCleanupTarget(_activeConnection);
1723 // must finish caching information before ReadSni which can activate the callback before returning
1724 cachedAsyncState.SetActiveConnectionAndResult(completion, ADP.EndExecuteXmlReader, _activeConnection);
1725 _stateObj.ReadSni(completion);
1729 tdsReliabilitySection.Stop();
1733 catch (System.OutOfMemoryException e) {
1734 _activeConnection.Abort(e);
1735 completion.TrySetException(e);
1738 catch (System.StackOverflowException e) {
1739 _activeConnection.Abort(e);
1740 completion.TrySetException(e);
1743 catch (System.Threading.ThreadAbortException e) {
1744 _activeConnection.Abort(e);
1745 SqlInternalConnection.BestEffortCleanup(bestEffortCleanupTarget);
1746 completion.TrySetException(e);
1749 catch (Exception e) {
1750 // Similarly, if an exception occurs put the stateObj back into the pool.
1751 // and reset async cache information to allow a second async execute
1752 if (null != _cachedAsyncState) {
1753 _cachedAsyncState.ResetAsyncState();
1755 ReliablePutStateObject();
1756 completion.TrySetException(e);
1760 public XmlReader EndExecuteXmlReader(IAsyncResult asyncResult) {
1762 return EndExecuteXmlReaderInternal(asyncResult);
1765 Bid.CorrelationTrace("<sc.SqlCommand.EndExecuteXmlReader|API|Correlation> ObjectID%d#, ActivityID %ls\n", ObjectID);
1769 private XmlReader EndExecuteXmlReaderAsync(IAsyncResult asyncResult) {
1770 Bid.CorrelationTrace("<sc.SqlCommand.EndExecuteXmlReaderAsync|Info|Correlation> ObjectID%d#, ActivityID %ls\n", ObjectID);
1772 Exception asyncException = ((Task)asyncResult).Exception;
1773 if (asyncException != null) {
1774 // Leftover exception from the Begin...InternalReadStage
1775 ReliablePutStateObject();
1776 throw asyncException.InnerException;
1779 ThrowIfReconnectionHasBeenCanceled();
1780 // lock on _stateObj prevents ----s with close/cancel.
1782 return EndExecuteXmlReaderInternal(asyncResult);
1787 private XmlReader EndExecuteXmlReaderInternal(IAsyncResult asyncResult) {
1788 bool success = false;
1789 int? sqlExceptionNumber = null;
1791 XmlReader result = CompleteXmlReader(InternalEndExecuteReader(asyncResult, ADP.EndExecuteXmlReader));
1795 catch (SqlException e){
1796 sqlExceptionNumber = e.Number;
1797 if (cachedAsyncState != null) {
1798 cachedAsyncState.ResetAsyncState();
1801 // SqlException is always catchable
1802 ReliablePutStateObject();
1805 catch (Exception e) {
1806 if (cachedAsyncState != null) {
1807 cachedAsyncState.ResetAsyncState();
1809 if (ADP.IsCatchableExceptionType(e)) {
1810 ReliablePutStateObject();
1815 WriteEndExecuteEvent(success, sqlExceptionNumber, synchronous : false);
1819 private XmlReader CompleteXmlReader(SqlDataReader ds) {
1820 XmlReader xr = null;
1822 SmiExtendedMetaData[] md = ds.GetInternalSmiMetaData();
1823 bool isXmlCapable = (null != md && md.Length == 1 && (md[0].SqlDbType == SqlDbType.NText
1824 || md[0].SqlDbType == SqlDbType.NVarChar
1825 || md[0].SqlDbType == SqlDbType.Xml));
1829 SqlStream sqlBuf = new SqlStream(ds, true /*addByteOrderMark*/, (md[0].SqlDbType == SqlDbType.Xml) ? false : true /*process all rows*/);
1830 xr = sqlBuf.ToXmlReader();
1832 catch (Exception e) {
1833 if (ADP.IsCatchableExceptionType(e)) {
1841 throw SQL.NonXmlResult();
1846 [System.Security.Permissions.HostProtectionAttribute(ExternalThreading=true)]
1847 public IAsyncResult BeginExecuteReader() {
1848 return BeginExecuteReader(null, null, CommandBehavior.Default);
1851 [System.Security.Permissions.HostProtectionAttribute(ExternalThreading=true)]
1852 public IAsyncResult BeginExecuteReader(AsyncCallback callback, object stateObject) {
1853 return BeginExecuteReader(callback, stateObject, CommandBehavior.Default);
1856 override protected DbDataReader ExecuteDbDataReader(CommandBehavior behavior) {
1857 Bid.CorrelationTrace("<sc.SqlCommand.ExecuteDbDataReader|API|Correlation> ObjectID%d#, ActivityID %ls\n", ObjectID);
1858 return ExecuteReader(behavior, ADP.ExecuteReader);
1861 new public SqlDataReader ExecuteReader() {
1862 SqlStatistics statistics = null;
1864 Bid.ScopeEnter(out hscp, "<sc.SqlCommand.ExecuteReader|API> %d#", ObjectID);
1865 Bid.CorrelationTrace("<sc.SqlCommand.ExecuteReader|API|Correlation> ObjectID%d#, ActivityID %ls\n", ObjectID);
1867 statistics = SqlStatistics.StartTimer(Statistics);
1868 return ExecuteReader(CommandBehavior.Default, ADP.ExecuteReader);
1871 SqlStatistics.StopTimer(statistics);
1872 Bid.ScopeLeave(ref hscp);
1876 new public SqlDataReader ExecuteReader(CommandBehavior behavior) {
1878 Bid.ScopeEnter(out hscp, "<sc.SqlCommand.ExecuteReader|API> %d#, behavior=%d{ds.CommandBehavior}", ObjectID, (int)behavior);
1879 Bid.CorrelationTrace("<sc.SqlCommand.ExecuteReader|API|Correlation> ObjectID%d#, behavior=%d{ds.CommandBehavior}, ActivityID %ls\n", ObjectID, (int)behavior);
1881 return ExecuteReader(behavior, ADP.ExecuteReader);
1884 Bid.ScopeLeave(ref hscp);
1888 [System.Security.Permissions.HostProtectionAttribute(ExternalThreading=true)]
1889 public IAsyncResult BeginExecuteReader(CommandBehavior behavior) {
1890 return BeginExecuteReader(null, null, behavior);
1893 [System.Security.Permissions.HostProtectionAttribute(ExternalThreading=true)]
1894 public IAsyncResult BeginExecuteReader(AsyncCallback callback, object stateObject, CommandBehavior behavior) {
1895 Bid.CorrelationTrace("<sc.SqlCommand.BeginExecuteReader|API|Correlation> ObjectID%d#, behavior=%d{ds.CommandBehavior}, ActivityID %ls\n", ObjectID, (int)behavior);
1896 SqlConnection.ExecutePermission.Demand();
1897 return BeginExecuteReaderInternal(behavior, callback, stateObject, 0);
1900 internal SqlDataReader ExecuteReader(CommandBehavior behavior, string method) {
1901 SqlConnection.ExecutePermission.Demand(); // TODO: Need to move this to public methods...
1903 // Reset _pendingCancel upon entry into any Execute - used to synchronize state
1904 // between entry into Execute* API and the thread obtaining the stateObject.
1905 _pendingCancel = false;
1907 SqlStatistics statistics = null;
1909 TdsParser bestEffortCleanupTarget = null;
1910 RuntimeHelpers.PrepareConstrainedRegions();
1911 bool success = false;
1912 int? sqlExceptionNumber = null;
1914 WriteBeginExecuteEvent();
1916 TdsParser.ReliabilitySection tdsReliabilitySection = new TdsParser.ReliabilitySection();
1918 RuntimeHelpers.PrepareConstrainedRegions();
1920 tdsReliabilitySection.Start();
1924 bestEffortCleanupTarget = SqlInternalConnection.GetBestEffortCleanupTarget(_activeConnection);
1925 statistics = SqlStatistics.StartTimer(Statistics);
1926 SqlDataReader result = RunExecuteReader(behavior, RunBehavior.ReturnImmediately, true, method);
1932 tdsReliabilitySection.Stop();
1936 catch (SqlException e) {
1937 sqlExceptionNumber = e.Number;
1940 catch (System.OutOfMemoryException e) {
1941 _activeConnection.Abort(e);
1944 catch (System.StackOverflowException e) {
1945 _activeConnection.Abort(e);
1948 catch (System.Threading.ThreadAbortException e) {
1949 _activeConnection.Abort(e);
1950 SqlInternalConnection.BestEffortCleanup(bestEffortCleanupTarget);
1954 SqlStatistics.StopTimer(statistics);
1955 WriteEndExecuteEvent(success, sqlExceptionNumber, synchronous : true);
1959 public SqlDataReader EndExecuteReader(IAsyncResult asyncResult) {
1961 return EndExecuteReaderInternal(asyncResult);
1964 Bid.CorrelationTrace("<sc.SqlCommand.EndExecuteReader|API|Correlation> ObjectID%d#, ActivityID %ls\n", ObjectID);
1968 private SqlDataReader EndExecuteReaderAsync(IAsyncResult asyncResult) {
1969 Bid.CorrelationTrace("<sc.SqlCommand.EndExecuteReaderAsync|Info|Correlation> ObjectID%d#, ActivityID %ls\n", ObjectID);
1971 Exception asyncException = ((Task)asyncResult).Exception;
1972 if (asyncException != null) {
1973 // Leftover exception from the Begin...InternalReadStage
1974 ReliablePutStateObject();
1975 throw asyncException.InnerException;
1978 ThrowIfReconnectionHasBeenCanceled();
1979 // lock on _stateObj prevents ----s with close/cancel.
1981 return EndExecuteReaderInternal(asyncResult);
1986 private SqlDataReader EndExecuteReaderInternal(IAsyncResult asyncResult) {
1987 SqlStatistics statistics = null;
1988 bool success = false;
1989 int? sqlExceptionNumber = null;
1991 statistics = SqlStatistics.StartTimer(Statistics);
1992 SqlDataReader result = InternalEndExecuteReader(asyncResult, ADP.EndExecuteReader);
1996 catch (SqlException e) {
1997 sqlExceptionNumber = e.Number;
1998 if (cachedAsyncState != null)
2000 cachedAsyncState.ResetAsyncState();
2003 // SqlException is always catchable
2004 ReliablePutStateObject();
2007 catch (Exception e) {
2008 if (cachedAsyncState != null) {
2009 cachedAsyncState.ResetAsyncState();
2011 if (ADP.IsCatchableExceptionType(e)) {
2012 ReliablePutStateObject();
2017 SqlStatistics.StopTimer(statistics);
2018 WriteEndExecuteEvent(success, sqlExceptionNumber, synchronous : false);
2022 private IAsyncResult BeginExecuteReaderAsync(CommandBehavior behavior, AsyncCallback callback, object stateObject) {
2023 return BeginExecuteReaderInternal(behavior, callback, stateObject, CommandTimeout, asyncWrite:true);
2026 private IAsyncResult BeginExecuteReaderInternal(CommandBehavior behavior, AsyncCallback callback, object stateObject, int timeout, bool asyncWrite = false) {
2027 // Reset _pendingCancel upon entry into any Execute - used to synchronize state
2028 // between entry into Execute* API and the thread obtaining the stateObject.
2029 _pendingCancel = false;
2031 SqlStatistics statistics = null;
2033 statistics = SqlStatistics.StartTimer(Statistics);
2034 WriteBeginExecuteEvent();
2035 TaskCompletionSource<object> completion = new TaskCompletionSource<object>(stateObject);
2037 ValidateAsyncCommand(); // Special case - done outside of try/catches to prevent putting a stateObj
2038 // back into pool when we should not.
2040 Task writeTask = null;
2041 try { // InternalExecuteNonQuery already has reliability block, but if failure will not put stateObj back into pool.
2042 RunExecuteReader(behavior, RunBehavior.ReturnImmediately, true, ADP.BeginExecuteReader, completion, timeout, out writeTask, asyncWrite);
2044 catch (Exception e) {
2045 if (!ADP.IsCatchableOrSecurityExceptionType(e)) {
2046 // If not catchable - the connection has already been caught and doomed in RunExecuteReader.
2050 // For async, RunExecuteReader will never put the stateObj back into the pool, so do so now.
2051 ReliablePutStateObject();
2055 if (writeTask != null ) {
2056 AsyncHelper.ContinueTask(writeTask,completion,()=> BeginExecuteReaderInternalReadStage(completion));
2059 BeginExecuteReaderInternalReadStage(completion);
2062 // Add callback after work is done to avoid overlapping Begin\End methods
2063 if (callback != null) {
2064 completion.Task.ContinueWith((t) => callback(t), TaskScheduler.Default);
2066 return completion.Task;
2069 SqlStatistics.StopTimer(statistics);
2073 private void BeginExecuteReaderInternalReadStage(TaskCompletionSource<object> completion) {
2074 Debug.Assert(completion != null,"CompletionSource should not be null");
2075 // Read SNI does not have catches for async exceptions, handle here.
2076 TdsParser bestEffortCleanupTarget = null;
2077 RuntimeHelpers.PrepareConstrainedRegions();
2080 TdsParser.ReliabilitySection tdsReliabilitySection = new TdsParser.ReliabilitySection();
2082 RuntimeHelpers.PrepareConstrainedRegions();
2084 tdsReliabilitySection.Start();
2088 bestEffortCleanupTarget = SqlInternalConnection.GetBestEffortCleanupTarget(_activeConnection);
2089 // must finish caching information before ReadSni which can activate the callback before returning
2090 cachedAsyncState.SetActiveConnectionAndResult(completion, ADP.EndExecuteReader, _activeConnection);
2091 _stateObj.ReadSni(completion);
2095 tdsReliabilitySection.Stop();
2099 catch (System.OutOfMemoryException e) {
2100 _activeConnection.Abort(e);
2101 completion.TrySetException(e);
2104 catch (System.StackOverflowException e) {
2105 _activeConnection.Abort(e);
2106 completion.TrySetException(e);
2109 catch (System.Threading.ThreadAbortException e) {
2110 _activeConnection.Abort(e);
2111 SqlInternalConnection.BestEffortCleanup(bestEffortCleanupTarget);
2112 completion.TrySetException(e);
2115 catch (Exception e) {
2116 // Similarly, if an exception occurs put the stateObj back into the pool.
2117 // and reset async cache information to allow a second async execute
2118 if (null != _cachedAsyncState) {
2119 _cachedAsyncState.ResetAsyncState();
2121 ReliablePutStateObject();
2122 completion.TrySetException(e);
2126 private SqlDataReader InternalEndExecuteReader(IAsyncResult asyncResult, string endMethod) {
2128 VerifyEndExecuteState((Task) asyncResult, endMethod);
2129 WaitForAsyncResults(asyncResult);
2131 // If Transparent parameter encryption was attempted, then we would have skipped the below
2132 // checks in VerifyEndExecuteState since we wanted to wait for WaitForAsyncResults to complete.
2133 if (IsColumnEncryptionEnabled) {
2134 if (cachedAsyncState.EndMethodName == null) {
2135 throw ADP.MethodCalledTwice(endMethod);
2138 if (endMethod != cachedAsyncState.EndMethodName) {
2139 throw ADP.MismatchedAsyncResult(cachedAsyncState.EndMethodName, endMethod);
2142 if (!cachedAsyncState.IsActiveConnectionValid(_activeConnection)) {
2143 // If the connection is not 'valid' then it was closed while we were executing
2144 throw ADP.ClosedConnectionError();
2148 CheckThrowSNIException();
2150 TdsParser bestEffortCleanupTarget = null;
2151 RuntimeHelpers.PrepareConstrainedRegions();
2154 TdsParser.ReliabilitySection tdsReliabilitySection = new TdsParser.ReliabilitySection();
2156 RuntimeHelpers.PrepareConstrainedRegions();
2158 tdsReliabilitySection.Start();
2162 bestEffortCleanupTarget = SqlInternalConnection.GetBestEffortCleanupTarget(_activeConnection);
2163 SqlDataReader reader = CompleteAsyncExecuteReader();
2164 Debug.Assert(null == _stateObj, "non-null state object in InternalEndExecuteReader");
2169 tdsReliabilitySection.Stop();
2173 catch (System.OutOfMemoryException e) {
2174 _activeConnection.Abort(e);
2177 catch (System.StackOverflowException e) {
2178 _activeConnection.Abort(e);
2181 catch (System.Threading.ThreadAbortException e) {
2182 _activeConnection.Abort(e);
2183 SqlInternalConnection.BestEffortCleanup(bestEffortCleanupTarget);
2188 public override Task<int> ExecuteNonQueryAsync(CancellationToken cancellationToken) {
2190 Bid.CorrelationTrace("<sc.SqlCommand.ExecuteNonQueryAsync|API|Correlation> ObjectID%d#, ActivityID %ls\n", ObjectID);
2191 SqlConnection.ExecutePermission.Demand();
2193 TaskCompletionSource<int> source = new TaskCompletionSource<int>();
2195 CancellationTokenRegistration registration = new CancellationTokenRegistration();
2196 if (cancellationToken.CanBeCanceled) {
2197 if (cancellationToken.IsCancellationRequested) {
2198 source.SetCanceled();
2201 registration = cancellationToken.Register(CancelIgnoreFailure);
2204 Task<int> returnedTask = source.Task;
2206 RegisterForConnectionCloseNotification(ref returnedTask);
2208 Task<int>.Factory.FromAsync(BeginExecuteNonQueryAsync, EndExecuteNonQueryAsync, null).ContinueWith((t) => {
2209 registration.Dispose();
2211 Exception e = t.Exception.InnerException;
2212 source.SetException(e);
2216 source.SetCanceled();
2219 source.SetResult(t.Result);
2222 }, TaskScheduler.Default);
2224 catch (Exception e) {
2225 source.SetException(e);
2228 return returnedTask;
2231 protected override Task<DbDataReader> ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) {
2232 return ExecuteReaderAsync(behavior, cancellationToken).ContinueWith<DbDataReader>((result) => {
2233 if (result.IsFaulted) {
2234 throw result.Exception.InnerException;
2236 return result.Result;
2237 }, CancellationToken.None, TaskContinuationOptions.ExecuteSynchronously | TaskContinuationOptions.NotOnCanceled, TaskScheduler.Default);
2240 new public Task<SqlDataReader> ExecuteReaderAsync() {
2241 return ExecuteReaderAsync(CommandBehavior.Default, CancellationToken.None);
2244 new public Task<SqlDataReader> ExecuteReaderAsync(CommandBehavior behavior) {
2245 return ExecuteReaderAsync(behavior, CancellationToken.None);
2248 new public Task<SqlDataReader> ExecuteReaderAsync(CancellationToken cancellationToken) {
2249 return ExecuteReaderAsync(CommandBehavior.Default, cancellationToken);
2252 new public Task<SqlDataReader> ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) {
2254 Bid.CorrelationTrace("<sc.SqlCommand.ExecuteReaderAsync|API|Correlation> ObjectID%d#, behavior=%d{ds.CommandBehavior}, ActivityID %ls\n", ObjectID, (int)behavior);
2255 SqlConnection.ExecutePermission.Demand();
2257 TaskCompletionSource<SqlDataReader> source = new TaskCompletionSource<SqlDataReader>();
2259 CancellationTokenRegistration registration = new CancellationTokenRegistration();
2260 if (cancellationToken.CanBeCanceled) {
2261 if (cancellationToken.IsCancellationRequested) {
2262 source.SetCanceled();
2265 registration = cancellationToken.Register(CancelIgnoreFailure);
2268 Task<SqlDataReader> returnedTask = source.Task;
2270 RegisterForConnectionCloseNotification(ref returnedTask);
2272 Task<SqlDataReader>.Factory.FromAsync(BeginExecuteReaderAsync, EndExecuteReaderAsync, behavior, null).ContinueWith((t) => {
2273 registration.Dispose();
2275 Exception e = t.Exception.InnerException;
2276 source.SetException(e);
2280 source.SetCanceled();
2283 source.SetResult(t.Result);
2286 }, TaskScheduler.Default);
2288 catch (Exception e) {
2289 source.SetException(e);
2292 return returnedTask;
2295 public override Task<object> ExecuteScalarAsync(CancellationToken cancellationToken)
2297 return ExecuteReaderAsync(cancellationToken).ContinueWith((executeTask) => {
2298 TaskCompletionSource<object> source = new TaskCompletionSource<object>();
2299 if (executeTask.IsCanceled) {
2300 source.SetCanceled();
2302 else if (executeTask.IsFaulted) {
2303 source.SetException(executeTask.Exception.InnerException);
2306 SqlDataReader reader = executeTask.Result;
2307 reader.ReadAsync(cancellationToken).ContinueWith((readTask) => {
2309 if (readTask.IsCanceled) {
2311 source.SetCanceled();
2313 else if (readTask.IsFaulted) {
2315 source.SetException(readTask.Exception.InnerException);
2318 Exception exception = null;
2319 object result = null;
2321 bool more = readTask.Result;
2322 if (more && reader.FieldCount > 0) {
2324 result = reader.GetValue(0);
2326 catch (Exception e) {
2334 if (exception != null) {
2335 source.SetException(exception);
2338 source.SetResult(result);
2342 catch (Exception e) {
2343 // exception thrown by Dispose...
2344 source.SetException(e);
2346 }, TaskScheduler.Default);
2349 }, TaskScheduler.Default).Unwrap();
2352 public Task<XmlReader> ExecuteXmlReaderAsync() {
2353 return ExecuteXmlReaderAsync(CancellationToken.None);
2356 public Task<XmlReader> ExecuteXmlReaderAsync(CancellationToken cancellationToken) {
2358 Bid.CorrelationTrace("<sc.SqlCommand.ExecuteXmlReaderAsync|API|Correlation> ObjectID%d#, ActivityID %ls\n", ObjectID);
2359 SqlConnection.ExecutePermission.Demand();
2361 TaskCompletionSource<XmlReader> source = new TaskCompletionSource<XmlReader>();
2363 CancellationTokenRegistration registration = new CancellationTokenRegistration();
2364 if (cancellationToken.CanBeCanceled) {
2365 if (cancellationToken.IsCancellationRequested) {
2366 source.SetCanceled();
2369 registration = cancellationToken.Register(CancelIgnoreFailure);
2372 Task<XmlReader> returnedTask = source.Task;
2374 RegisterForConnectionCloseNotification(ref returnedTask);
2376 Task<XmlReader>.Factory.FromAsync(BeginExecuteXmlReaderAsync, EndExecuteXmlReaderAsync, null).ContinueWith((t) => {
2377 registration.Dispose();
2379 Exception e = t.Exception.InnerException;
2380 source.SetException(e);
2384 source.SetCanceled();
2387 source.SetResult(t.Result);
2390 }, TaskScheduler.Default);
2392 catch (Exception e) {
2393 source.SetException(e);
2396 return returnedTask;
2399 // If the user part is quoted, remove first and last brackets and then unquote any right square
2400 // brackets in the procedure. This is a very simple parser that performs no validation. As
2401 // with the function below, ideally we should have support from the server for this.
2402 private static string UnquoteProcedurePart(string part) {
2403 if ((null != part) && (2 <= part.Length)) {
2404 if ('[' == part[0] && ']' == part[part.Length-1]) {
2405 part = part.Substring(1, part.Length-2); // strip outer '[' & ']'
2406 part = part.Replace("]]", "]"); // undo quoted "]" from "]]" to "]"
2412 // User value in this format: [server].[database].[schema].[sp_foo];1
2413 // This function should only be passed "[sp_foo];1".
2414 // This function uses a pretty simple parser that doesn't do any validation.
2415 // Ideally, we would have support from the server rather than us having to do this.
2416 private static string UnquoteProcedureName(string name, out object groupNumber) {
2417 groupNumber = null; // Out param - initialize value to no value.
2418 string sproc = name;
2420 if (null != sproc) {
2421 if (Char.IsDigit(sproc[sproc.Length-1])) { // If last char is a digit, parse.
2422 int semicolon = sproc.LastIndexOf(';');
2423 if (semicolon != -1) { // If we found a semicolon, obtain the integer.
2424 string part = sproc.Substring(semicolon+1);
2426 if (Int32.TryParse(part, out number)) { // No checking, just fail if this doesn't work.
2427 groupNumber = number;
2428 sproc = sproc.Substring(0, semicolon);
2432 sproc = UnquoteProcedurePart(sproc);
2437 //index into indirection arrays for columns of interest to DeriveParameters
2438 private enum ProcParamsColIndex {
2441 DataType, // obsolete in katmai, use ManagedDataType instead
2442 ManagedDataType, // new in katmai
2443 CharacterMaximumLength,
2449 XmlSchemaCollectionCatalogName,
2450 XmlSchemaCollectionSchemaName,
2451 XmlSchemaCollectionName,
2452 UdtTypeName, // obsolete in Katmai. Holds the actual typename if UDT, since TypeName didn't back then.
2453 DateTimeScale // new in Katmai
2456 // Yukon- column ordinals (this array indexed by ProcParamsColIndex
2457 static readonly internal string[] PreKatmaiProcParamsNames = new string[] {
2458 "PARAMETER_NAME", // ParameterName,
2459 "PARAMETER_TYPE", // ParameterType,
2460 "DATA_TYPE", // DataType
2461 null, // ManagedDataType, introduced in Katmai
2462 "CHARACTER_MAXIMUM_LENGTH", // CharacterMaximumLength,
2463 "NUMERIC_PRECISION", // NumericPrecision,
2464 "NUMERIC_SCALE", // NumericScale,
2465 "UDT_CATALOG", // TypeCatalogName,
2466 "UDT_SCHEMA", // TypeSchemaName,
2467 "TYPE_NAME", // TypeName,
2468 "XML_CATALOGNAME", // XmlSchemaCollectionCatalogName,
2469 "XML_SCHEMANAME", // XmlSchemaCollectionSchemaName,
2470 "XML_SCHEMACOLLECTIONNAME", // XmlSchemaCollectionName
2471 "UDT_NAME", // UdtTypeName
2472 null, // Scale for datetime types with scale, introduced in Katmai
2475 // Katmai+ column ordinals (this array indexed by ProcParamsColIndex
2476 static readonly internal string[] KatmaiProcParamsNames = new string[] {
2477 "PARAMETER_NAME", // ParameterName,
2478 "PARAMETER_TYPE", // ParameterType,
2479 null, // DataType, removed from Katmai+
2480 "MANAGED_DATA_TYPE", // ManagedDataType,
2481 "CHARACTER_MAXIMUM_LENGTH", // CharacterMaximumLength,
2482 "NUMERIC_PRECISION", // NumericPrecision,
2483 "NUMERIC_SCALE", // NumericScale,
2484 "TYPE_CATALOG_NAME", // TypeCatalogName,
2485 "TYPE_SCHEMA_NAME", // TypeSchemaName,
2486 "TYPE_NAME", // TypeName,
2487 "XML_CATALOGNAME", // XmlSchemaCollectionCatalogName,
2488 "XML_SCHEMANAME", // XmlSchemaCollectionSchemaName,
2489 "XML_SCHEMACOLLECTIONNAME", // XmlSchemaCollectionName
2490 null, // UdtTypeName, removed from Katmai+
2491 "SS_DATETIME_PRECISION", // Scale for datetime types with scale
2495 internal void DeriveParameters() {
2496 switch (this.CommandType) {
2497 case System.Data.CommandType.Text:
2498 throw ADP.DeriveParametersNotSupported(this);
2499 case System.Data.CommandType.StoredProcedure:
2501 case System.Data.CommandType.TableDirect:
2502 // CommandType.TableDirect - do nothing, parameters are not supported
2503 throw ADP.DeriveParametersNotSupported(this);
2505 throw ADP.InvalidCommandType(this.CommandType);
2508 // validate that we have a valid connection
2509 ValidateCommand(ADP.DeriveParameters, false /*not async*/);
2511 // Use common parser for SqlClient and OleDb - parse into 4 parts - Server, Catalog, Schema, ProcedureName
2512 string[] parsedSProc = MultipartIdentifier.ParseMultipartIdentifier(this.CommandText, "[\"", "]\"", Res.SQL_SqlCommandCommandText, false);
2513 if (null == parsedSProc[3] || ADP.IsEmpty(parsedSProc[3]))
2515 throw ADP.NoStoredProcedureExists(this.CommandText);
2518 Debug.Assert(parsedSProc.Length == 4, "Invalid array length result from SqlCommandBuilder.ParseProcedureName");
2520 SqlCommand paramsCmd = null;
2521 StringBuilder cmdText = new StringBuilder();
2523 // Build call for sp_procedure_params_rowset built of unquoted values from user:
2524 // [user server, if provided].[user catalog, else current database].[sys if Yukon, else blank].[sp_procedure_params_rowset]
2526 // Server - pass only if user provided.
2527 if (!ADP.IsEmpty(parsedSProc[0])) {
2528 SqlCommandSet.BuildStoredProcedureName(cmdText, parsedSProc[0]);
2529 cmdText.Append(".");
2532 // Catalog - pass user provided, otherwise use current database.
2533 if (ADP.IsEmpty(parsedSProc[1])) {
2534 parsedSProc[1] = this.Connection.Database;
2536 SqlCommandSet.BuildStoredProcedureName(cmdText, parsedSProc[1]);
2537 cmdText.Append(".");
2539 // Schema - only if Yukon, and then only pass sys. Also - pass managed version of sproc
2540 // for Yukon, else older sproc.
2542 bool useManagedDataType;
2543 if (this.Connection.IsKatmaiOrNewer) {
2544 // Procedure - [sp_procedure_params_managed]
2545 cmdText.Append("[sys].[").Append(TdsEnums.SP_PARAMS_MGD10).Append("]");
2547 colNames = KatmaiProcParamsNames;
2548 useManagedDataType = true;
2551 if (this.Connection.IsYukonOrNewer) {
2552 // Procedure - [sp_procedure_params_managed]
2553 cmdText.Append("[sys].[").Append(TdsEnums.SP_PARAMS_MANAGED).Append("]");
2556 // Procedure - [sp_procedure_params_rowset]
2557 cmdText.Append(".[").Append(TdsEnums.SP_PARAMS).Append("]");
2560 colNames = PreKatmaiProcParamsNames;
2561 useManagedDataType = false;
2565 paramsCmd = new SqlCommand(cmdText.ToString(), this.Connection, this.Transaction);
2566 paramsCmd.CommandType = CommandType.StoredProcedure;
2570 // Prepare parameters for sp_procedure_params_rowset:
2571 // 1) procedure name - unquote user value
2572 // 2) group number - parsed at the time we unquoted procedure name
2573 // 3) procedure schema - unquote user value
2579 paramsCmd.Parameters.Add(new SqlParameter("@procedure_name", SqlDbType.NVarChar, 255));
2580 paramsCmd.Parameters[0].Value = UnquoteProcedureName(parsedSProc[3], out groupNumber); // ProcedureName is 4rd element in parsed array
2582 if (null != groupNumber) {
2583 SqlParameter param = paramsCmd.Parameters.Add(new SqlParameter("@group_number", SqlDbType.Int));
2584 param.Value = groupNumber;
2587 if (!ADP.IsEmpty(parsedSProc[2])) { // SchemaName is 3rd element in parsed array
2588 SqlParameter param = paramsCmd.Parameters.Add(new SqlParameter("@procedure_schema", SqlDbType.NVarChar, 255));
2589 param.Value = UnquoteProcedurePart(parsedSProc[2]);
2592 SqlDataReader r = null;
2594 List<SqlParameter> parameters = new List<SqlParameter>();
2595 bool processFinallyBlock = true;
2598 r = paramsCmd.ExecuteReader();
2600 SqlParameter p = null;
2603 // each row corresponds to a parameter of the stored proc. Fill in all the info
2605 p = new SqlParameter();
2608 p.ParameterName = (string) r[colNames[(int)ProcParamsColIndex.ParameterName]];
2611 if (useManagedDataType) {
2612 p.SqlDbType = (SqlDbType)(short)r[colNames[(int)ProcParamsColIndex.ManagedDataType]];
2614 // Yukon didn't have as accurate of information as we're getting for Katmai, so re-map a couple of
2615 // types for backward compatability.
2616 switch (p.SqlDbType) {
2617 case SqlDbType.Image:
2618 case SqlDbType.Timestamp:
2619 p.SqlDbType = SqlDbType.VarBinary;
2622 case SqlDbType.NText:
2623 p.SqlDbType = SqlDbType.NVarChar;
2626 case SqlDbType.Text:
2627 p.SqlDbType = SqlDbType.VarChar;
2635 p.SqlDbType = MetaType.GetSqlDbTypeFromOleDbType((short)r[colNames[(int)ProcParamsColIndex.DataType]],
2636 ADP.IsNull(r[colNames[(int)ProcParamsColIndex.TypeName]]) ?
2638 (string)r[colNames[(int)ProcParamsColIndex.TypeName]]);
2642 object a = r[colNames[(int)ProcParamsColIndex.CharacterMaximumLength]];
2646 // Map MAX sizes correctly. The Katmai server-side proc sends 0 for these instead of -1.
2647 // Should be fixed on the Katmai side, but would likely hold up the RI, and is safer to fix here.
2648 // If we can get the server-side fixed before shipping Katmai, we can remove this mapping.
2650 (p.SqlDbType == SqlDbType.NVarChar ||
2651 p.SqlDbType == SqlDbType.VarBinary ||
2652 p.SqlDbType == SqlDbType.VarChar)) {
2659 p.Direction = ParameterDirectionFromOleDbDirection((short)r[colNames[(int)ProcParamsColIndex.ParameterType]]);
2661 if (p.SqlDbType == SqlDbType.Decimal) {
2662 p.ScaleInternal = (byte) ((short)r[colNames[(int)ProcParamsColIndex.NumericScale]] & 0xff);
2663 p.PrecisionInternal = (byte)((short)r[colNames[(int)ProcParamsColIndex.NumericPrecision]] & 0xff);
2666 // type name for Udt
2667 if (SqlDbType.Udt == p.SqlDbType) {
2669 Debug.Assert(this._activeConnection.IsYukonOrNewer,"Invalid datatype token received from pre-yukon server");
2672 if (useManagedDataType) {
2673 udtTypeName = (string)r[colNames[(int)ProcParamsColIndex.TypeName]];
2676 udtTypeName = (string)r[colNames[(int)ProcParamsColIndex.UdtTypeName]];
2679 //read the type name
2680 p.UdtTypeName = r[colNames[(int)ProcParamsColIndex.TypeCatalogName]]+"."+
2681 r[colNames[(int)ProcParamsColIndex.TypeSchemaName]]+"."+
2685 // type name for Structured types (same as for Udt's except assign p.TypeName instead of p.UdtTypeName
2686 if (SqlDbType.Structured == p.SqlDbType) {
2688 Debug.Assert(this._activeConnection.IsKatmaiOrNewer,"Invalid datatype token received from pre-katmai server");
2690 //read the type name
2691 p.TypeName = r[colNames[(int)ProcParamsColIndex.TypeCatalogName]]+"."+
2692 r[colNames[(int)ProcParamsColIndex.TypeSchemaName]]+"."+
2693 r[colNames[(int)ProcParamsColIndex.TypeName]];
2696 // XmlSchema name for Xml types
2697 if (SqlDbType.Xml == p.SqlDbType) {
2700 value = r[colNames[(int)ProcParamsColIndex.XmlSchemaCollectionCatalogName]];
2701 p.XmlSchemaCollectionDatabase = ADP.IsNull(value) ? String.Empty : (string) value;
2703 value = r[colNames[(int)ProcParamsColIndex.XmlSchemaCollectionSchemaName]];
2704 p.XmlSchemaCollectionOwningSchema = ADP.IsNull(value) ? String.Empty : (string) value;
2706 value = r[colNames[(int)ProcParamsColIndex.XmlSchemaCollectionName]];
2707 p.XmlSchemaCollectionName = ADP.IsNull(value) ? String.Empty : (string) value;
2710 if (MetaType._IsVarTime(p.SqlDbType)) {
2711 object value = r[colNames[(int)ProcParamsColIndex.DateTimeScale]];
2713 p.ScaleInternal = (byte)(((int)value) & 0xff);
2720 catch (Exception e) {
2721 processFinallyBlock = ADP.IsCatchableExceptionType(e);
2725 TdsParser.ReliabilitySection.Assert("unreliable call to DeriveParameters"); // you need to setup for a thread abort somewhere before you call this method
2726 if (processFinallyBlock) {
2730 // always unhook the user's connection
2731 paramsCmd.Connection = null;
2735 if (parameters.Count == 0) {
2736 throw ADP.NoStoredProcedureExists(this.CommandText);
2739 this.Parameters.Clear();
2741 foreach (SqlParameter temp in parameters) {
2742 this._parameters.Add(temp);
2746 private ParameterDirection ParameterDirectionFromOleDbDirection(short oledbDirection) {
2747 Debug.Assert(oledbDirection >= 1 && oledbDirection <= 4, "invalid parameter direction from params_rowset!");
2749 switch (oledbDirection) {
2751 return ParameterDirection.InputOutput;
2753 return ParameterDirection.Output;
2755 return ParameterDirection.ReturnValue;
2757 return ParameterDirection.Input;
2762 // get cached metadata
2763 internal _SqlMetaDataSet MetaData {
2765 return _cachedMetaData;
2769 // Check to see if notificactions auto enlistment is turned on. Enlist if so.
2770 private void CheckNotificationStateAndAutoEnlist() {
2771 // First, if auto-enlist is on, check server version and then obtain context if
2772 // present. If so, auto enlist to the dependency ID given in the context data.
2773 if (NotificationAutoEnlist) {
2774 if (_activeConnection.IsYukonOrNewer) { // Only supported for Yukon...
2775 string notifyContext = SqlNotificationContext();
2776 if (!ADP.IsEmpty(notifyContext)) {
2777 // Map to dependency by ID set in context data.
2778 SqlDependency dependency = SqlDependencyPerAppDomainDispatcher.SingletonInstance.LookupDependencyEntry(notifyContext);
2780 if (null != dependency) {
2781 // Add this command to the dependency.
2782 dependency.AddCommandDependency(this);
2788 // If we have a notification with a dependency, setup the notification options at this time.
2790 // If user passes options, then we will always have option data at the time the SqlDependency
2791 // ctor is called. But, if we are using default queue, then we do not have this data until
2792 // Start(). Due to this, we always delay setting options until execute.
2794 // There is a variance in order between Start(), SqlDependency(), and Execute. This is the
2795 // best way to solve that problem.
2796 if (null != Notification) {
2797 if (_sqlDep != null) {
2798 if (null == _sqlDep.Options) {
2799 // If null, SqlDependency was not created with options, so we need to obtain default options now.
2800 // GetDefaultOptions can and will throw under certain conditions.
2802 // In order to match to the appropriate start - we need 3 pieces of info:
2803 // 1) server 2) user identity (SQL Auth or Int Sec) 3) database
2805 SqlDependency.IdentityUserNamePair identityUserName = null;
2807 // Obtain identity from connection.
2808 SqlInternalConnectionTds internalConnection = _activeConnection.InnerConnection as SqlInternalConnectionTds;
2809 if (internalConnection.Identity != null) {
2810 identityUserName = new SqlDependency.IdentityUserNamePair(internalConnection.Identity, null);
2813 identityUserName = new SqlDependency.IdentityUserNamePair(null, internalConnection.ConnectionOptions.UserID);
2816 Notification.Options = SqlDependency.GetDefaultComposedOptions(_activeConnection.DataSource,
2817 InternalTdsConnection.ServerProvidedFailOverPartner,
2818 identityUserName, _activeConnection.Database);
2821 // Set UserData on notifications, as well as adding to the appdomain dispatcher. The value is
2822 // computed by an algorithm on the dependency - fixed and will always produce the same value
2823 // given identical commandtext + parameter values.
2824 Notification.UserData = _sqlDep.ComputeHashAndAddToDispatcher(this);
2825 // Maintain server list for SqlDependency.
2826 _sqlDep.AddToServerList(_activeConnection.DataSource);
2831 [System.Security.Permissions.SecurityPermission(SecurityAction.Assert, Infrastructure=true)]
2832 static internal string SqlNotificationContext() {
2833 SqlConnection.VerifyExecutePermission();
2835 // since this information is protected, follow it so that it is not exposed to the user.
2836 // SQLBU 329633, SQLBU 329637
2837 return (System.Runtime.Remoting.Messaging.CallContext.GetData("MS.SqlDependencyCookie") as string);
2840 // Tds-specific logic for ExecuteNonQuery run handling
2841 private Task RunExecuteNonQueryTds(string methodName, bool async, int timeout, bool asyncWrite ) {
2842 Debug.Assert(!asyncWrite || async, "AsyncWrite should be always accompanied by Async");
2843 bool processFinallyBlock = true;
2846 Task reconnectTask = _activeConnection.ValidateAndReconnect(null, timeout);
2848 if (reconnectTask != null) {
2849 long reconnectionStart = ADP.TimerCurrent();
2851 TaskCompletionSource<object> completion = new TaskCompletionSource<object>();
2852 _activeConnection.RegisterWaitingForReconnect(completion.Task);
2853 _reconnectionCompletionSource = completion;
2854 CancellationTokenSource timeoutCTS = new CancellationTokenSource();
2855 AsyncHelper.SetTimeoutException(completion, timeout, SQL.CR_ReconnectTimeout, timeoutCTS.Token);
2856 AsyncHelper.ContinueTask(reconnectTask, completion,
2858 if (completion.Task.IsCompleted) {
2861 Interlocked.CompareExchange(ref _reconnectionCompletionSource, null, completion);
2862 timeoutCTS.Cancel();
2863 Task subTask = RunExecuteNonQueryTds(methodName, async, TdsParserStaticMethods.GetRemainingTimeout(timeout, reconnectionStart), asyncWrite);
2864 if (subTask == null) {
2865 completion.SetResult(null);
2868 AsyncHelper.ContinueTask(subTask, completion, () => completion.SetResult(null));
2870 }, connectionToAbort: _activeConnection);
2871 return completion.Task;
2874 AsyncHelper.WaitForCompletion(reconnectTask, timeout, () => { throw SQL.CR_ReconnectTimeout(); });
2875 timeout = TdsParserStaticMethods.GetRemainingTimeout(timeout, reconnectionStart);
2880 _activeConnection.AddWeakReference(this, SqlReferenceCollection.CommandTag);
2885 // we just send over the raw text with no annotation
2886 // no parameters are sent over
2887 // no data reader is returned
2888 // use this overload for "batch SQL" tds token type
2889 Bid.Trace("<sc.SqlCommand.ExecuteNonQuery|INFO> %d#, Command executed as SQLBATCH.\n", ObjectID);
2890 Task executeTask = _stateObj.Parser.TdsExecuteSQLBatch(this.CommandText, timeout, this.Notification, _stateObj, sync: true);
2891 Debug.Assert(executeTask == null, "Shouldn't get a task when doing sync writes");
2895 _activeConnection.GetOpenTdsConnection(methodName).IncrementAsyncCount();
2899 Debug.Assert(_stateObj._syncOverAsync, "Should not attempt pends in a synchronous call");
2900 bool result = _stateObj.Parser.TryRun(RunBehavior.UntilDone, this, null, null, _stateObj, out dataReady);
2901 if (!result) { throw SQL.SynchronousCallMayNotPend(); }
2904 catch (Exception e) {
2905 processFinallyBlock = ADP.IsCatchableExceptionType(e);
2909 TdsParser.ReliabilitySection.Assert("unreliable call to RunExecuteNonQueryTds"); // you need to setup for a thread abort somewhere before you call this method
2910 if (processFinallyBlock && !async) {
2911 // When executing Async, we need to keep the _stateObj alive...
2918 // Smi-specific logic for ExecuteNonQuery
2919 private void RunExecuteNonQuerySmi( bool sendToPipe ) {
2920 SqlInternalConnectionSmi innerConnection = InternalSmiConnection;
2922 // Set it up, process all of the events, and we're done!
2923 SmiRequestExecutor requestExecutor = null;
2925 requestExecutor = SetUpSmiRequest(innerConnection);
2926 SmiExecuteType execType;
2928 execType = SmiExecuteType.ToPipe;
2930 execType = SmiExecuteType.NonQuery;
2933 SmiEventStream eventStream = null;
2934 // Don't need a CER here because caller already has one that will doom the
2935 // connection if it's a finally-skipping type of problem.
2936 bool processFinallyBlock = true;
2939 SysTx.Transaction transaction;
2940 innerConnection.GetCurrentTransactionPair(out transactionId, out transaction);
2942 if (Bid.AdvancedOn) {
2943 Bid.Trace("<sc.SqlCommand.RunExecuteNonQuerySmi|ADV> %d#, innerConnection=%d#, transactionId=0x%I64x, cmdBehavior=%d.\n", ObjectID, innerConnection.ObjectID, transactionId, (int)CommandBehavior.Default);
2946 if (SmiContextFactory.Instance.NegotiatedSmiVersion >= SmiContextFactory.KatmaiVersion) {
2947 eventStream = requestExecutor.Execute(
2948 innerConnection.SmiConnection,
2951 CommandBehavior.Default,
2955 eventStream = requestExecutor.Execute(
2956 innerConnection.SmiConnection,
2958 CommandBehavior.Default,
2962 while ( eventStream.HasEvents ) {
2963 eventStream.ProcessEvent( EventSink );
2966 catch (Exception e) {
2967 processFinallyBlock = ADP.IsCatchableExceptionType(e);
2971 TdsParser.ReliabilitySection.Assert("unreliable call to RunExecuteNonQuerySmi"); // you need to setup for a thread abort somewhere before you call this method
2972 if (null != eventStream && processFinallyBlock) {
2973 eventStream.Close( EventSink );
2977 EventSink.ProcessMessagesAndThrow();
2980 if (requestExecutor != null) {
2981 requestExecutor.Close(EventSink);
2982 EventSink.ProcessMessagesAndThrow(ignoreNonFatalMessages: true);
2988 /// Resets the encryption related state of the command object and each of the parameters.
2989 /// BatchRPC doesn't need special handling to cleanup the state of each RPC object and its parameters since a new RPC object and
2990 /// parameters are generated on every execution.
2992 private void ResetEncryptionState() {
2993 // First reset the command level state.
2994 ClearDescribeParameterEncryptionRequests();
2996 // Reset the state of each of the parameters.
2997 if (_parameters != null) {
2998 for (int i = 0; i < _parameters.Count; i++) {
2999 _parameters[i].CipherMetadata = null;
3000 _parameters[i].HasReceivedMetadata = false;
3006 /// Steps to be executed in the Prepare Transparent Encryption finally block.
3008 private void PrepareTransparentEncryptionFinallyBlock( bool closeDataReader,
3009 bool clearDataStructures,
3010 bool decrementAsyncCount,
3011 bool wasDescribeParameterEncryptionNeeded,
3012 ReadOnlyDictionary<_SqlRPC, _SqlRPC> describeParameterEncryptionRpcOriginalRpcMap,
3013 SqlDataReader describeParameterEncryptionDataReader) {
3014 if (clearDataStructures) {
3015 // Clear some state variables in SqlCommand that reflect in-progress describe parameter encryption requests.
3016 ClearDescribeParameterEncryptionRequests();
3018 if (describeParameterEncryptionRpcOriginalRpcMap != null) {
3019 describeParameterEncryptionRpcOriginalRpcMap = null;
3023 // Decrement the async count.
3024 if (decrementAsyncCount) {
3025 SqlInternalConnectionTds internalConnectionTds = _activeConnection.GetOpenTdsConnection();
3026 if (internalConnectionTds != null) {
3027 internalConnectionTds.DecrementAsyncCount();
3031 if (closeDataReader) {
3032 // Close the data reader to reset the _stateObj
3033 if (null != describeParameterEncryptionDataReader) {
3034 describeParameterEncryptionDataReader.Close();
3040 /// Executes the reader after checking to see if we need to encrypt input parameters and then encrypting it if required.
3041 /// TryFetchInputParameterEncryptionInfo() -> ReadDescribeEncryptionParameterResults()-> EncryptInputParameters() ->RunExecuteReaderTds()
3043 /// <param name="cmdBehavior"></param>
3044 /// <param name="returnStream"></param>
3045 /// <param name="async"></param>
3046 /// <param name="timeout"></param>
3047 /// <param name="task"></param>
3048 /// <param name="asyncWrite"></param>
3049 /// <returns></returns>
3050 private void PrepareForTransparentEncryption(CommandBehavior cmdBehavior, bool returnStream, bool async, int timeout, TaskCompletionSource<object> completion, out Task returnTask, bool asyncWrite)
3052 // Fetch reader with input params
3053 Task fetchInputParameterEncryptionInfoTask = null;
3054 bool describeParameterEncryptionNeeded = false;
3055 SqlDataReader describeParameterEncryptionDataReader = null;
3058 Debug.Assert(_activeConnection != null, "_activeConnection should not be null in PrepareForTransparentEncryption.");
3059 Debug.Assert(_activeConnection.Parser != null, "_activeConnection.Parser should not be null in PrepareForTransparentEncryption.");
3060 Debug.Assert(_activeConnection.Parser.IsColumnEncryptionSupported,
3061 "_activeConnection.Parser.IsColumnEncryptionSupported should be true in PrepareForTransparentEncryption.");
3062 Debug.Assert(_columnEncryptionSetting == SqlCommandColumnEncryptionSetting.Enabled
3063 || (_columnEncryptionSetting == SqlCommandColumnEncryptionSetting.UseConnectionSetting && _activeConnection.IsColumnEncryptionSettingEnabled),
3064 "ColumnEncryption setting should be enabled for input parameter encryption.");
3065 Debug.Assert(async == (completion != null), "completion should can be null if and only if mode is async.");
3067 // A flag to indicate if finallyblock needs to execute.
3068 bool processFinallyBlock = true;
3070 // A flag to indicate if we need to decrement async count on the connection in finally block.
3071 bool decrementAsyncCountInFinallyBlock = async;
3073 // Flag to indicate if exception is caught during the execution, to govern clean up.
3074 bool exceptionCaught = false;
3076 // Used in BatchRPCMode to maintain a map of describe parameter encryption RPC requests (Keys) and their corresponding original RPC requests (Values).
3077 ReadOnlyDictionary<_SqlRPC, _SqlRPC> describeParameterEncryptionRpcOriginalRpcMap = null;
3079 TdsParser bestEffortCleanupTarget = null;
3080 RuntimeHelpers.PrepareConstrainedRegions();
3083 TdsParser.ReliabilitySection tdsReliabilitySection = new TdsParser.ReliabilitySection();
3085 RuntimeHelpers.PrepareConstrainedRegions();
3087 tdsReliabilitySection.Start();
3091 bestEffortCleanupTarget = SqlInternalConnection.GetBestEffortCleanupTarget(_activeConnection);
3093 // Fetch the encryption information that applies to any of the input parameters.
3094 describeParameterEncryptionDataReader = TryFetchInputParameterEncryptionInfo(timeout,
3097 out describeParameterEncryptionNeeded,
3098 out fetchInputParameterEncryptionInfoTask,
3099 out describeParameterEncryptionRpcOriginalRpcMap);
3101 Debug.Assert(describeParameterEncryptionNeeded || describeParameterEncryptionDataReader == null,
3102 "describeParameterEncryptionDataReader should be null if we don't need to request describe parameter encryption request.");
3104 Debug.Assert(fetchInputParameterEncryptionInfoTask == null || async,
3105 "Task returned by TryFetchInputParameterEncryptionInfo, when in sync mode, in PrepareForTransparentEncryption.");
3107 Debug.Assert((describeParameterEncryptionRpcOriginalRpcMap != null) == BatchRPCMode,
3108 "describeParameterEncryptionRpcOriginalRpcMap can be non-null if and only if it is in BatchRPCMode.");
3110 // If we didn't have parameters, we can fall back to regular code path, by simply returning.
3111 if (!describeParameterEncryptionNeeded) {
3112 Debug.Assert(null == fetchInputParameterEncryptionInfoTask,
3113 "fetchInputParameterEncryptionInfoTask should not be set if describe parameter encryption is not needed.");
3115 Debug.Assert(null == describeParameterEncryptionDataReader,
3116 "SqlDataReader created for describe parameter encryption params when it is not needed.");
3121 Debug.Assert(describeParameterEncryptionDataReader != null,
3122 "describeParameterEncryptionDataReader should not be null, as it is required to get results of describe parameter encryption.");
3124 // Fire up another task to read the results of describe parameter encryption
3125 if (fetchInputParameterEncryptionInfoTask != null) {
3126 // Mark that we should not process the finally block since we have async execution pending.
3127 // Note that this should be done outside the task's continuation delegate.
3128 processFinallyBlock = false;
3129 returnTask = AsyncHelper.CreateContinuationTask(fetchInputParameterEncryptionInfoTask, () => {
3130 bool processFinallyBlockAsync = true;
3132 RuntimeHelpers.PrepareConstrainedRegions();
3135 TdsParser.ReliabilitySection tdsReliabilitySectionAsync = new TdsParser.ReliabilitySection();
3136 RuntimeHelpers.PrepareConstrainedRegions();
3138 tdsReliabilitySectionAsync.Start();
3140 // Check for any exceptions on network write, before reading.
3141 CheckThrowSNIException();
3143 // If it is async, then TryFetchInputParameterEncryptionInfo-> RunExecuteReaderTds would have incremented the async count.
3144 // Decrement it when we are about to complete async execute reader.
3145 SqlInternalConnectionTds internalConnectionTds = _activeConnection.GetOpenTdsConnection();
3146 if (internalConnectionTds != null)
3148 internalConnectionTds.DecrementAsyncCount();
3149 decrementAsyncCountInFinallyBlock = false;
3152 // Complete executereader.
3153 describeParameterEncryptionDataReader = CompleteAsyncExecuteReader();
3154 Debug.Assert(null == _stateObj, "non-null state object in PrepareForTransparentEncryption.");
3156 // Read the results of describe parameter encryption.
3157 ReadDescribeEncryptionParameterResults(describeParameterEncryptionDataReader, describeParameterEncryptionRpcOriginalRpcMap);
3160 // Failpoint to force the thread to halt to simulate cancellation of SqlCommand.
3161 if (_sleepAfterReadDescribeEncryptionParameterResults) {
3162 Thread.Sleep(10000);
3166 tdsReliabilitySectionAsync.Stop();
3170 catch (Exception e) {
3171 processFinallyBlockAsync = ADP.IsCatchableExceptionType(e);
3175 PrepareTransparentEncryptionFinallyBlock( closeDataReader: processFinallyBlockAsync,
3176 decrementAsyncCount: decrementAsyncCountInFinallyBlock,
3177 clearDataStructures: processFinallyBlockAsync,
3178 wasDescribeParameterEncryptionNeeded: describeParameterEncryptionNeeded,
3179 describeParameterEncryptionRpcOriginalRpcMap: describeParameterEncryptionRpcOriginalRpcMap,
3180 describeParameterEncryptionDataReader: describeParameterEncryptionDataReader);
3183 onFailure: ((exception) => {
3184 if (_cachedAsyncState != null) {
3185 _cachedAsyncState.ResetAsyncState();
3187 if (exception != null) {
3192 // If it was async, ending the reader is still pending.
3194 // Mark that we should not process the finally block since we have async execution pending.
3195 // Note that this should be done outside the task's continuation delegate.
3196 processFinallyBlock = false;
3197 returnTask = Task.Run(() => {
3198 bool processFinallyBlockAsync = true;
3200 RuntimeHelpers.PrepareConstrainedRegions();
3203 TdsParser.ReliabilitySection tdsReliabilitySectionAsync = new TdsParser.ReliabilitySection();
3204 RuntimeHelpers.PrepareConstrainedRegions();
3206 tdsReliabilitySectionAsync.Start();
3209 // Check for any exceptions on network write, before reading.
3210 CheckThrowSNIException();
3212 // If it is async, then TryFetchInputParameterEncryptionInfo-> RunExecuteReaderTds would have incremented the async count.
3213 // Decrement it when we are about to complete async execute reader.
3214 SqlInternalConnectionTds internalConnectionTds = _activeConnection.GetOpenTdsConnection();
3215 if (internalConnectionTds != null) {
3216 internalConnectionTds.DecrementAsyncCount();
3217 decrementAsyncCountInFinallyBlock = false;
3220 // Complete executereader.
3221 describeParameterEncryptionDataReader = CompleteAsyncExecuteReader();
3222 Debug.Assert(null == _stateObj, "non-null state object in PrepareForTransparentEncryption.");
3224 // Read the results of describe parameter encryption.
3225 ReadDescribeEncryptionParameterResults(describeParameterEncryptionDataReader, describeParameterEncryptionRpcOriginalRpcMap);
3227 // Failpoint to force the thread to halt to simulate cancellation of SqlCommand.
3228 if (_sleepAfterReadDescribeEncryptionParameterResults) {
3229 Thread.Sleep(10000);
3235 tdsReliabilitySectionAsync.Stop();
3239 catch (Exception e) {
3240 processFinallyBlockAsync = ADP.IsCatchableExceptionType(e);
3244 PrepareTransparentEncryptionFinallyBlock( closeDataReader: processFinallyBlockAsync,
3245 decrementAsyncCount: decrementAsyncCountInFinallyBlock,
3246 clearDataStructures: processFinallyBlockAsync,
3247 wasDescribeParameterEncryptionNeeded: describeParameterEncryptionNeeded,
3248 describeParameterEncryptionRpcOriginalRpcMap: describeParameterEncryptionRpcOriginalRpcMap,
3249 describeParameterEncryptionDataReader: describeParameterEncryptionDataReader);
3254 // For synchronous execution, read the results of describe parameter encryption here.
3255 ReadDescribeEncryptionParameterResults(describeParameterEncryptionDataReader, describeParameterEncryptionRpcOriginalRpcMap);
3259 // Failpoint to force the thread to halt to simulate cancellation of SqlCommand.
3260 if (_sleepAfterReadDescribeEncryptionParameterResults) {
3261 Thread.Sleep(10000);
3266 catch (Exception e) {
3267 processFinallyBlock = ADP.IsCatchableExceptionType(e);
3268 exceptionCaught = true;
3272 // Free up the state only for synchronous execution. For asynchronous execution, free only if there was an exception.
3273 PrepareTransparentEncryptionFinallyBlock(closeDataReader: (processFinallyBlock && !async) || exceptionCaught,
3274 decrementAsyncCount: decrementAsyncCountInFinallyBlock && exceptionCaught,
3275 clearDataStructures: (processFinallyBlock && !async) || exceptionCaught,
3276 wasDescribeParameterEncryptionNeeded: describeParameterEncryptionNeeded,
3277 describeParameterEncryptionRpcOriginalRpcMap: describeParameterEncryptionRpcOriginalRpcMap,
3278 describeParameterEncryptionDataReader: describeParameterEncryptionDataReader);
3283 tdsReliabilitySection.Stop();
3287 catch (System.OutOfMemoryException e) {
3288 _activeConnection.Abort(e);
3291 catch (System.StackOverflowException e) {
3292 _activeConnection.Abort(e);
3295 catch (System.Threading.ThreadAbortException e) {
3296 _activeConnection.Abort(e);
3297 SqlInternalConnection.BestEffortCleanup(bestEffortCleanupTarget);
3300 catch (Exception e) {
3301 if (cachedAsyncState != null) {
3302 cachedAsyncState.ResetAsyncState();
3305 if (ADP.IsCatchableExceptionType(e)) {
3306 ReliablePutStateObject();
3314 /// Executes an RPC to fetch param encryption info from SQL Engine. If this method is not done writing
3315 /// the request to wire, it'll set the "task" parameter which can be used to create continuations.
3317 /// <param name="timeout"></param>
3318 /// <param name="async"></param>
3319 /// <param name="asyncWrite"></param>
3320 /// <param name="inputParameterEncryptionNeeded"></param>
3321 /// <param name="task"></param>
3322 /// <param name="describeParameterEncryptionRpcOriginalRpcMap"></param>
3323 /// <returns></returns>
3324 private SqlDataReader TryFetchInputParameterEncryptionInfo(int timeout,
3327 out bool inputParameterEncryptionNeeded,
3329 out ReadOnlyDictionary<_SqlRPC, _SqlRPC> describeParameterEncryptionRpcOriginalRpcMap) {
3330 inputParameterEncryptionNeeded = false;
3332 describeParameterEncryptionRpcOriginalRpcMap = null;
3335 // Count the rpc requests that need to be transparently encrypted
3336 // We simply look for any parameters in a request and add the request to be queried for parameter encryption
3337 Dictionary<_SqlRPC, _SqlRPC> describeParameterEncryptionRpcOriginalRpcDictionary = new Dictionary<_SqlRPC, _SqlRPC>();
3339 for (int i = 0; i < _SqlRPCBatchArray.Length; i++) {
3340 // In BatchRPCMode, the actual T-SQL query is in the first parameter and not present as the rpcName, as is the case with non-BatchRPCMode.
3341 // So input parameters start at parameters[1]. parameters[0] is the actual T-SQL Statement. rpcName is sp_executesql.
3342 if (_SqlRPCBatchArray[i].parameters.Length > 1) {
3343 _SqlRPCBatchArray[i].needsFetchParameterEncryptionMetadata = true;
3345 // Since we are going to need multiple RPC objects, allocate a new one here for each command in the batch.
3346 _SqlRPC rpcDescribeParameterEncryptionRequest = new _SqlRPC();
3348 // Prepare the describe parameter encryption request.
3349 PrepareDescribeParameterEncryptionRequest(_SqlRPCBatchArray[i], ref rpcDescribeParameterEncryptionRequest);
3350 Debug.Assert(rpcDescribeParameterEncryptionRequest != null, "rpcDescribeParameterEncryptionRequest should not be null, after call to PrepareDescribeParameterEncryptionRequest.");
3352 Debug.Assert(!describeParameterEncryptionRpcOriginalRpcDictionary.ContainsKey(rpcDescribeParameterEncryptionRequest),
3353 "There should not already be a key referring to the current rpcDescribeParameterEncryptionRequest, in the dictionary describeParameterEncryptionRpcOriginalRpcDictionary.");
3355 // Add the describe parameter encryption RPC request as the key and its corresponding original rpc request to the dictionary.
3356 describeParameterEncryptionRpcOriginalRpcDictionary.Add(rpcDescribeParameterEncryptionRequest, _SqlRPCBatchArray[i]);
3360 describeParameterEncryptionRpcOriginalRpcMap = new ReadOnlyDictionary<_SqlRPC, _SqlRPC>(describeParameterEncryptionRpcOriginalRpcDictionary);
3362 if (describeParameterEncryptionRpcOriginalRpcMap.Count == 0) {
3363 // If no parameters are present, nothing to do, simply return.
3367 inputParameterEncryptionNeeded = true;
3370 _sqlRPCParameterEncryptionReqArray = describeParameterEncryptionRpcOriginalRpcMap.Keys.ToArray();
3372 Debug.Assert(_sqlRPCParameterEncryptionReqArray.Length > 0, "There should be at-least 1 describe parameter encryption rpc request.");
3373 Debug.Assert(_sqlRPCParameterEncryptionReqArray.Length <= _SqlRPCBatchArray.Length,
3374 "The number of decribe parameter encryption RPC requests is more than the number of original RPC requests.");
3376 else if (0 != GetParameterCount(_parameters)) {
3377 // Fetch params for a single batch
3378 inputParameterEncryptionNeeded = true;
3379 _sqlRPCParameterEncryptionReqArray = new _SqlRPC[1];
3382 GetRPCObject(_parameters.Count, ref rpc);
3383 Debug.Assert(rpc != null, "GetRPCObject should not return rpc as null.");
3385 rpc.rpcName = CommandText;
3388 foreach (SqlParameter sqlParam in _parameters) {
3389 rpc.parameters[i++] = sqlParam;
3392 // Prepare the RPC request for describe parameter encryption procedure.
3393 PrepareDescribeParameterEncryptionRequest(rpc, ref _sqlRPCParameterEncryptionReqArray[0]);
3394 Debug.Assert(_sqlRPCParameterEncryptionReqArray[0] != null, "_sqlRPCParameterEncryptionReqArray[0] should not be null, after call to PrepareDescribeParameterEncryptionRequest.");
3397 if (inputParameterEncryptionNeeded) {
3398 // Set the flag that indicates that parameter encryption requests are currently in-progress.
3399 _isDescribeParameterEncryptionRPCCurrentlyInProgress = true;
3402 // Failpoint to force the thread to halt to simulate cancellation of SqlCommand.
3403 if (_sleepDuringTryFetchInputParameterEncryptionInfo) {
3404 Thread.Sleep(10000);
3409 return RunExecuteReaderTds( CommandBehavior.Default,
3410 runBehavior: RunBehavior.ReturnImmediately, // Other RunBehavior modes will skip reading rows.
3415 asyncWrite: asyncWrite,
3417 describeParameterEncryptionRequest: true);
3425 /// Constructs a SqlParameter with a given string value
3427 /// <param name="queryText"></param>
3428 /// <returns></returns>
3429 private SqlParameter GetSqlParameterWithQueryText(string queryText)
3431 SqlParameter sqlParam = new SqlParameter(null, ((queryText.Length << 1) <= TdsEnums.TYPE_SIZE_LIMIT) ? SqlDbType.NVarChar : SqlDbType.NText, queryText.Length);
3432 sqlParam.Value = queryText;
3438 /// Constructs the sp_describe_parameter_encryption request with the values from the original RPC call.
3439 /// Prototype for <sp_describe_parameter_encryption> is
3440 /// exec sp_describe_parameter_encryption @tsql=N'[SQL Statement]', @params=N'@p1 varbinary(256)'
3442 /// <param name="originalRpcRequest">Original RPC request</param>
3443 /// <param name="describeParameterEncryptionRequest">sp_describe_parameter_encryption request being built</param>
3444 private void PrepareDescribeParameterEncryptionRequest(_SqlRPC originalRpcRequest, ref _SqlRPC describeParameterEncryptionRequest) {
3445 Debug.Assert(originalRpcRequest != null);
3447 // Construct the RPC request for sp_describe_parameter_encryption
3448 // sp_describe_parameter_encryption always has 2 parameters (stmt, paramlist).
3449 GetRPCObject(2, ref describeParameterEncryptionRequest, forSpDescribeParameterEncryption:true);
3450 describeParameterEncryptionRequest.rpcName = "sp_describe_parameter_encryption";
3452 // Prepare @tsql parameter
3453 SqlParameter sqlParam;
3456 // In BatchRPCMode, The actual T-SQL query is in the first parameter and not present as the rpcName, as is the case with non-BatchRPCMode.
3458 Debug.Assert(originalRpcRequest.parameters != null && originalRpcRequest.parameters.Length > 0,
3459 "originalRpcRequest didn't have at-least 1 parameter in BatchRPCMode, in PrepareDescribeParameterEncryptionRequest.");
3460 text = (string)originalRpcRequest.parameters[0].Value;
3461 sqlParam = GetSqlParameterWithQueryText(text);
3464 text = originalRpcRequest.rpcName;
3465 if (CommandType == Data.CommandType.StoredProcedure) {
3466 // For stored procedures, we need to prepare @tsql in the following format
3467 // N'EXEC sp_name @param1=@param1, @param1=@param2, ..., @paramN=@paramN'
3468 sqlParam = BuildStoredProcedureStatementForColumnEncryption(text, originalRpcRequest.parameters);
3471 sqlParam = GetSqlParameterWithQueryText(text);
3475 Debug.Assert(text != null, "@tsql parameter is null in PrepareDescribeParameterEncryptionRequest.");
3477 describeParameterEncryptionRequest.parameters[0] = sqlParam;
3478 string parameterList = null;
3480 // In BatchRPCMode, the input parameters start at parameters[1]. parameters[0] is the T-SQL statement. rpcName is sp_executesql.
3481 // And it is already in the format expected out of BuildParamList, which is not the case with Non-BatchRPCMode.
3483 if (originalRpcRequest.parameters.Length > 1) {
3484 parameterList = (string)originalRpcRequest.parameters[1].Value;
3488 // Prepare @params parameter
3489 // Need to create new parameters as we cannot have the same parameter being part of two SqlCommand objects
3490 SqlParameter paramCopy;
3491 SqlParameterCollection tempCollection = new SqlParameterCollection();
3493 for (int i = 0; i < _parameters.Count; i++) {
3494 SqlParameter param = originalRpcRequest.parameters[i];
3495 paramCopy = new SqlParameter(param.ParameterName, param.SqlDbType, param.Size, param.Direction, param.Precision, param.Scale, param.SourceColumn, param.SourceVersion,
3496 param.SourceColumnNullMapping, param.Value, param.XmlSchemaCollectionDatabase, param.XmlSchemaCollectionOwningSchema, param.XmlSchemaCollectionName);
3497 tempCollection.Add(paramCopy);
3500 Debug.Assert(_stateObj == null, "_stateObj should be null at this time, in PrepareDescribeParameterEncryptionRequest.");
3501 Debug.Assert(_activeConnection != null, "_activeConnection should not be null at this time, in PrepareDescribeParameterEncryptionRequest.");
3502 TdsParser tdsParser = null;
3504 if (_activeConnection.Parser != null) {
3505 tdsParser = _activeConnection.Parser;
3506 if ((tdsParser == null) || (tdsParser.State == TdsParserState.Broken) || (tdsParser.State == TdsParserState.Closed)) {
3507 // Connection's parser is null as well, therefore we must be closed
3508 throw ADP.ClosedConnectionError();
3512 parameterList = BuildParamList(tdsParser, tempCollection, includeReturnValue:true);
3515 Debug.Assert(!string.IsNullOrWhiteSpace(parameterList), "parameterList should not be null or empty or whitespace.");
3517 sqlParam = new SqlParameter(null, ((parameterList.Length << 1) <= TdsEnums.TYPE_SIZE_LIMIT) ? SqlDbType.NVarChar : SqlDbType.NText, parameterList.Length);
3518 sqlParam.Value = parameterList;
3519 describeParameterEncryptionRequest.parameters[1] = sqlParam;
3523 /// Read the output of sp_describe_parameter_encryption
3525 /// <param name="ds">Resultset from calling to sp_describe_parameter_encryption</param>
3526 /// <param name="describeParameterEncryptionRpcOriginalRpcMap"> Readonly dictionary with the map of parameter encryption rpc requests with the corresponding original rpc requests.</param>
3527 private void ReadDescribeEncryptionParameterResults(SqlDataReader ds, ReadOnlyDictionary<_SqlRPC, _SqlRPC> describeParameterEncryptionRpcOriginalRpcMap) {
3529 int currentOrdinal = -1;
3530 SqlTceCipherInfoEntry cipherInfoEntry;
3531 Dictionary<int, SqlTceCipherInfoEntry> columnEncryptionKeyTable = new Dictionary<int, SqlTceCipherInfoEntry>();
3533 Debug.Assert((describeParameterEncryptionRpcOriginalRpcMap != null) == BatchRPCMode,
3534 "describeParameterEncryptionRpcOriginalRpcMap should be non-null if and only if it is BatchRPCMode.");
3536 // Indicates the current result set we are reading, used in BatchRPCMode, where we can have more than 1 result set.
3537 int resultSetSequenceNumber = 0;
3540 // Keep track of the number of rows in the result sets.
3541 int rowsAffected = 0;
3544 // A flag that used in BatchRPCMode, to assert the result of lookup in to the dictionary maintaining the map of describe parameter encryption requests
3545 // and the corresponding original rpc requests.
3546 bool lookupDictionaryResult;
3550 // If we got more RPC results from the server than what was requested.
3551 if (resultSetSequenceNumber >= _sqlRPCParameterEncryptionReqArray.Length) {
3552 Debug.Assert(false, "Server sent back more results than what was expected for describe parameter encryption requests in BatchRPCMode.");
3553 // Ignore the rest of the results from the server, if for whatever reason it sends back more than what we expect.
3558 // First read the column encryption key list
3565 // Column Encryption Key Ordinal.
3566 currentOrdinal = ds.GetInt32((int)DescribeParameterEncryptionResultSet1.KeyOrdinal);
3567 Debug.Assert(currentOrdinal >= 0, "currentOrdinal cannot be negative.");
3569 // Try to see if there was already an entry for the current ordinal.
3570 if (!columnEncryptionKeyTable.TryGetValue(currentOrdinal, out cipherInfoEntry)) {
3571 // If an entry for this ordinal was not found, create an entry in the columnEncryptionKeyTable for this ordinal.
3572 cipherInfoEntry = new SqlTceCipherInfoEntry(currentOrdinal);
3573 columnEncryptionKeyTable.Add(currentOrdinal, cipherInfoEntry);
3576 Debug.Assert(!cipherInfoEntry.Equals(default(SqlTceCipherInfoEntry)), "cipherInfoEntry should not be un-initialized.");
3579 byte[] encryptedKey = null;
3580 int encryptedKeyLength = (int)ds.GetBytes((int)DescribeParameterEncryptionResultSet1.EncryptedKey, 0, encryptedKey, 0, 0);
3581 encryptedKey = new byte[encryptedKeyLength];
3582 ds.GetBytes((int)DescribeParameterEncryptionResultSet1.EncryptedKey, 0, encryptedKey, 0, encryptedKeyLength);
3584 // Read the metadata version of the key.
3585 // It should always be 8 bytes.
3586 byte[] keyMdVersion = new byte[8];
3587 ds.GetBytes((int)DescribeParameterEncryptionResultSet1.KeyMdVersion, 0, keyMdVersion, 0, keyMdVersion.Length);
3589 // Validate the provider name
3590 string providerName = ds.GetString((int)DescribeParameterEncryptionResultSet1.ProviderName);
3591 //SqlColumnEncryptionKeyStoreProvider keyStoreProvider;
3592 //if (!SqlConnection.TryGetColumnEncryptionKeyStoreProvider (providerName, out keyStoreProvider)) {
3593 // // unknown provider, skip processing this cek.
3594 // Bid.Trace("<sc.SqlCommand.ReadDescribeEncryptionParameterResults|INFO>Unknown provider name recevied %s, skipping\n", providerName);
3598 cipherInfoEntry.Add(encryptedKey: encryptedKey,
3599 databaseId: ds.GetInt32((int)DescribeParameterEncryptionResultSet1.DbId),
3600 cekId: ds.GetInt32((int)DescribeParameterEncryptionResultSet1.KeyId),
3601 cekVersion: ds.GetInt32((int)DescribeParameterEncryptionResultSet1.KeyVersion),
3602 cekMdVersion: keyMdVersion,
3603 keyPath: ds.GetString((int)DescribeParameterEncryptionResultSet1.KeyPath),
3604 keyStoreName: providerName,
3605 algorithmName: ds.GetString((int)DescribeParameterEncryptionResultSet1.KeyEncryptionAlgorithm));
3608 if (!ds.NextResult()) {
3609 throw SQL.UnexpectedDescribeParamFormat ();
3612 // Find the RPC command that generated this tce request
3614 Debug.Assert(_sqlRPCParameterEncryptionReqArray[resultSetSequenceNumber] != null, "_sqlRPCParameterEncryptionReqArray[resultSetSequenceNumber] should not be null.");
3616 // Lookup in the dictionary to get the original rpc request corresponding to the describe parameter encryption request
3617 // pointed to by _sqlRPCParameterEncryptionReqArray[resultSetSequenceNumber]
3619 lookupDictionaryResult = describeParameterEncryptionRpcOriginalRpcMap.TryGetValue(_sqlRPCParameterEncryptionReqArray[resultSetSequenceNumber++], out rpc);
3621 Debug.Assert(lookupDictionaryResult,
3622 "Describe Parameter Encryption RPC request key must be present in the dictionary describeParameterEncryptionRpcOriginalRpcMap");
3623 Debug.Assert(rpc != null,
3624 "Describe Parameter Encryption RPC request's corresponding original rpc request must not be null in the dictionary describeParameterEncryptionRpcOriginalRpcMap");
3627 rpc = _rpcArrayOf1[0];
3630 Debug.Assert(rpc != null, "rpc should not be null here.");
3632 // This is the index in the parameters array where the actual parameters start.
3633 // In BatchRPCMode, parameters[0] has the t-sql, parameters[1] has the param list
3634 // and actual parameters of the query start at parameters[2].
3635 int parameterStartIndex = (BatchRPCMode ? 2 : 0);
3637 // Iterate over the parameter names to read the encryption type info
3643 Debug.Assert(rpc != null, "Describe Parameter Encryption requested for non-tce spec proc");
3644 string parameterName = ds.GetString((int)DescribeParameterEncryptionResultSet2.ParameterName);
3646 // When the RPC object gets reused, the parameter array has more parameters that the valid params for the command.
3647 // Null is used to indicate the end of the valid part of the array. Refer to GetRPCObject().
3648 for (paramIdx = parameterStartIndex; paramIdx < rpc.parameters.Length && rpc.parameters[paramIdx] != null; paramIdx++) {
3649 SqlParameter sqlParameter = rpc.parameters[paramIdx];
3650 Debug.Assert(sqlParameter != null, "sqlParameter should not be null.");
3652 if (sqlParameter.ParameterNameFixed.Equals(parameterName, StringComparison.Ordinal)) {
3653 Debug.Assert(sqlParameter.CipherMetadata == null, "param.CipherMetadata should be null.");
3654 sqlParameter.HasReceivedMetadata = true;
3656 // Found the param, setup the encryption info.
3657 byte columnEncryptionType = ds.GetByte((int)DescribeParameterEncryptionResultSet2.ColumnEncrytionType);
3658 if ((byte)SqlClientEncryptionType.PlainText != columnEncryptionType) {
3659 byte cipherAlgorithmId = ds.GetByte((int)DescribeParameterEncryptionResultSet2.ColumnEncryptionAlgorithm);
3660 int columnEncryptionKeyOrdinal = ds.GetInt32((int)DescribeParameterEncryptionResultSet2.ColumnEncryptionKeyOrdinal);
3661 byte columnNormalizationRuleVersion = ds.GetByte((int)DescribeParameterEncryptionResultSet2.NormalizationRuleVersion);
3663 // Lookup the key, failing which throw an exception
3664 if (!columnEncryptionKeyTable.TryGetValue(columnEncryptionKeyOrdinal, out cipherInfoEntry)) {
3665 throw SQL.InvalidEncryptionKeyOrdinal(columnEncryptionKeyOrdinal, columnEncryptionKeyTable.Count);
3668 sqlParameter.CipherMetadata = new SqlCipherMetadata(sqlTceCipherInfoEntry: cipherInfoEntry,
3669 ordinal: unchecked((ushort)-1),
3670 cipherAlgorithmId: cipherAlgorithmId,
3671 cipherAlgorithmName: null,
3672 encryptionType: columnEncryptionType,
3673 normalizationRuleVersion: columnNormalizationRuleVersion);
3675 // Decrypt the symmetric key.(This will also validate and throw if needed).
3676 Debug.Assert(_activeConnection != null, @"_activeConnection should not be null");
3677 SqlSecurityUtility.DecryptSymmetricKey(sqlParameter.CipherMetadata, this._activeConnection.DataSource);
3679 // This is effective only for BatchRPCMode even though we set it for non-BatchRPCMode also,
3680 // since for non-BatchRPCMode mode, paramoptions gets thrown away and reconstructed in BuildExecuteSql.
3681 rpc.paramoptions[paramIdx] |= TdsEnums.RPC_PARAM_ENCRYPTED;
3689 // When the RPC object gets reused, the parameter array has more parameters that the valid params for the command.
3690 // Null is used to indicate the end of the valid part of the array. Refer to GetRPCObject().
3691 for (paramIdx = parameterStartIndex; paramIdx < rpc.parameters.Length && rpc.parameters[paramIdx] != null; paramIdx++) {
3692 if (!rpc.parameters[paramIdx].HasReceivedMetadata && rpc.parameters[paramIdx].Direction != ParameterDirection.ReturnValue) {
3693 // Encryption MD wasn't sent by the server - we expect the metadata to be sent for all the parameters
3694 // that were sent in the original sp_describe_parameter_encryption but not necessarily for return values,
3695 // since there might be multiple return values but server will only send for one of them.
3696 // For parameters that don't need encryption, the encryption type is set to plaintext.
3697 throw SQL.ParamEncryptionMetadataMissing(rpc.parameters[paramIdx].ParameterName, rpc.GetCommandTextOrRpcName());
3702 Debug.Assert(rowsAffected == RowsAffectedByDescribeParameterEncryption,
3703 "number of rows received for describe parameter encryption should be equal to rows affected by describe parameter encryption.");
3706 // The server has responded with encryption related information for this rpc request. So clear the needsFetchParameterEncryptionMetadata flag.
3707 rpc.needsFetchParameterEncryptionMetadata = false;
3708 } while (ds.NextResult());
3710 // Verify that we received response for each rpc call needs tce
3712 for (int i = 0; i < _SqlRPCBatchArray.Length; i++) {
3713 if (_SqlRPCBatchArray[i].needsFetchParameterEncryptionMetadata) {
3714 throw SQL.ProcEncryptionMetadataMissing(_SqlRPCBatchArray[i].rpcName);
3720 internal SqlDataReader RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, bool returnStream, string method) {
3721 Task unused; // sync execution
3722 SqlDataReader reader = RunExecuteReader(cmdBehavior, runBehavior, returnStream, method, completion:null, timeout:CommandTimeout, task:out unused);
3723 Debug.Assert(unused == null, "returned task during synchronous execution");
3727 // task is created in case of pending asynchronous write, returned SqlDataReader should not be utilized until that task is complete
3728 internal SqlDataReader RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, bool returnStream, string method, TaskCompletionSource<object> completion, int timeout, out Task task, bool asyncWrite = false) {
3729 bool async = (null != completion);
3734 _rowsAffectedBySpDescribeParameterEncryption = -1;
3736 if (0 != (CommandBehavior.SingleRow & cmdBehavior)) {
3737 // CommandBehavior.SingleRow implies CommandBehavior.SingleResult
3738 cmdBehavior |= CommandBehavior.SingleResult;
3741 // @devnote: this function may throw for an invalid connection
3742 // @devnote: returns false for empty command text
3743 ValidateCommand(method, async);
3744 CheckNotificationStateAndAutoEnlist(); // Only call after validate - requires non null connection!
3746 TdsParser bestEffortCleanupTarget = null;
3747 // This section needs to occur AFTER ValidateCommand - otherwise it will AV without a connection.
3748 RuntimeHelpers.PrepareConstrainedRegions();
3751 TdsParser.ReliabilitySection tdsReliabilitySection = new TdsParser.ReliabilitySection();
3753 RuntimeHelpers.PrepareConstrainedRegions();
3755 tdsReliabilitySection.Start();
3759 bestEffortCleanupTarget = SqlInternalConnection.GetBestEffortCleanupTarget(_activeConnection);
3760 SqlStatistics statistics = Statistics;
3761 if (null != statistics) {
3762 if ((!this.IsDirty && this.IsPrepared && !_hiddenPrepare)
3763 || (this.IsPrepared && _execType == EXECTYPE.PREPAREPENDING))
3765 statistics.SafeIncrement(ref statistics._preparedExecs);
3768 statistics.SafeIncrement(ref statistics._unpreparedExecs);
3772 // Reset the encryption related state of the command and its parameters.
3773 ResetEncryptionState();
3775 if ( _activeConnection.IsContextConnection ) {
3776 return RunExecuteReaderSmi( cmdBehavior, runBehavior, returnStream );
3778 else if (IsColumnEncryptionEnabled) {
3779 Task returnTask = null;
3780 PrepareForTransparentEncryption(cmdBehavior, returnStream, async, timeout, completion, out returnTask, asyncWrite && async);
3781 Debug.Assert(async == (returnTask != null), @"returnTask should be null if and only if async is false.");
3783 return RunExecuteReaderTdsWithTransparentParameterEncryption( cmdBehavior, runBehavior, returnStream, async, timeout, out task, asyncWrite && async, ds: null,
3784 describeParameterEncryptionRequest: false, describeParameterEncryptionTask: returnTask);
3787 return RunExecuteReaderTds( cmdBehavior, runBehavior, returnStream, async, timeout, out task, asyncWrite && async);
3793 tdsReliabilitySection.Stop();
3797 catch (System.OutOfMemoryException e) {
3798 _activeConnection.Abort(e);
3801 catch (System.StackOverflowException e) {
3802 _activeConnection.Abort(e);
3805 catch (System.Threading.ThreadAbortException e) {
3806 _activeConnection.Abort(e);
3807 SqlInternalConnection.BestEffortCleanup(bestEffortCleanupTarget);
3813 /// RunExecuteReaderTds after Transparent Parameter Encryption is complete.
3815 /// <param name="cmdBehavior"></param>
3816 /// <param name="runBehavior"></param>
3817 /// <param name="returnStream"></param>
3818 /// <param name="async"></param>
3819 /// <param name="timeout"></param>
3820 /// <param name="task"></param>
3821 /// <param name="asyncWrite"></param>
3822 /// <param name="ds"></param>
3823 /// <param name="describeParameterEncryptionRequest"></param>
3824 /// <param name="describeParameterEncryptionTask"></param>
3825 /// <returns></returns>
3826 private SqlDataReader RunExecuteReaderTdsWithTransparentParameterEncryption(CommandBehavior cmdBehavior,
3827 RunBehavior runBehavior,
3833 SqlDataReader ds=null,
3834 bool describeParameterEncryptionRequest = false,
3835 Task describeParameterEncryptionTask = null) {
3836 Debug.Assert(!asyncWrite || async, "AsyncWrite should be always accompanied by Async");
3837 Debug.Assert((describeParameterEncryptionTask != null) == async, @"async should be true if and only if describeParameterEncryptionTask is not null.");
3839 if (ds == null && returnStream) {
3840 ds = new SqlDataReader(this, cmdBehavior);
3843 if (describeParameterEncryptionTask != null) {
3844 long parameterEncryptionStart = ADP.TimerCurrent();
3845 TaskCompletionSource<object> completion = new TaskCompletionSource<object>();
3846 AsyncHelper.ContinueTask(describeParameterEncryptionTask, completion,
3848 Task subTask = null;
3849 RunExecuteReaderTds(cmdBehavior, runBehavior, returnStream, async, TdsParserStaticMethods.GetRemainingTimeout(timeout, parameterEncryptionStart), out subTask, asyncWrite, ds);
3850 if (subTask == null) {
3851 completion.SetResult(null);
3854 AsyncHelper.ContinueTask(subTask, completion, () => completion.SetResult(null));
3856 }, connectionToDoom: null,
3857 onFailure: ((exception) => {
3858 if (_cachedAsyncState != null) {
3859 _cachedAsyncState.ResetAsyncState();
3861 if (exception != null) {
3864 onCancellation: (() => {
3865 if (_cachedAsyncState != null) {
3866 _cachedAsyncState.ResetAsyncState();
3868 connectionToAbort: _activeConnection);
3869 task = completion.Task;
3873 // Synchronous execution.
3874 return RunExecuteReaderTds(cmdBehavior, runBehavior, returnStream, async, timeout, out task, asyncWrite, ds);
3878 private SqlDataReader RunExecuteReaderTds( CommandBehavior cmdBehavior, RunBehavior runBehavior, bool returnStream, bool async, int timeout, out Task task, bool asyncWrite, SqlDataReader ds=null, bool describeParameterEncryptionRequest = false) {
3879 Debug.Assert(!asyncWrite || async, "AsyncWrite should be always accompanied by Async");
3881 if (ds == null && returnStream) {
3882 ds = new SqlDataReader(this, cmdBehavior);
3885 Task reconnectTask = _activeConnection.ValidateAndReconnect(null, timeout);
3887 if (reconnectTask != null) {
3888 long reconnectionStart = ADP.TimerCurrent();
3890 TaskCompletionSource<object> completion = new TaskCompletionSource<object>();
3891 _activeConnection.RegisterWaitingForReconnect(completion.Task);
3892 _reconnectionCompletionSource = completion;
3893 CancellationTokenSource timeoutCTS = new CancellationTokenSource();
3894 AsyncHelper.SetTimeoutException(completion, timeout, SQL.CR_ReconnectTimeout, timeoutCTS.Token);
3895 AsyncHelper.ContinueTask(reconnectTask, completion,
3897 if (completion.Task.IsCompleted) {
3900 Interlocked.CompareExchange(ref _reconnectionCompletionSource, null, completion);
3901 timeoutCTS.Cancel();
3903 RunExecuteReaderTds(cmdBehavior, runBehavior, returnStream, async, TdsParserStaticMethods.GetRemainingTimeout(timeout, reconnectionStart), out subTask, asyncWrite, ds);
3904 if (subTask == null) {
3905 completion.SetResult(null);
3908 AsyncHelper.ContinueTask(subTask, completion, () => completion.SetResult(null));
3910 }, connectionToAbort: _activeConnection);
3911 task = completion.Task;
3915 AsyncHelper.WaitForCompletion(reconnectTask, timeout, () => { throw SQL.CR_ReconnectTimeout(); });
3916 timeout = TdsParserStaticMethods.GetRemainingTimeout(timeout, reconnectionStart);
3920 // make sure we have good parameter information
3921 // prepare the command
3923 Debug.Assert(null != _activeConnection.Parser, "TdsParser class should not be null in Command.Execute!");
3925 bool inSchema = (0 != (cmdBehavior & CommandBehavior.SchemaOnly));
3932 string optionSettings = null;
3933 bool processFinallyBlock = true;
3934 bool decrementAsyncCountOnFailure = false;
3937 _activeConnection.GetOpenTdsConnection().IncrementAsyncCount();
3938 decrementAsyncCountOnFailure = true;
3944 _activeConnection.AddWeakReference(this, SqlReferenceCollection.CommandTag);
3948 Task writeTask = null;
3950 if (describeParameterEncryptionRequest) {
3952 if (_sleepDuringRunExecuteReaderTdsForSpDescribeParameterEncryption) {
3953 Thread.Sleep(10000);
3957 Debug.Assert(_sqlRPCParameterEncryptionReqArray != null, "RunExecuteReader rpc array not provided for describe parameter encryption request.");
3958 writeTask = _stateObj.Parser.TdsExecuteRPC(this, _sqlRPCParameterEncryptionReqArray, timeout, inSchema, this.Notification, _stateObj, CommandType.StoredProcedure == CommandType, sync: !asyncWrite);
3960 else if (BatchRPCMode) {
3961 Debug.Assert(inSchema == false, "Batch RPC does not support schema only command beahvior");
3962 Debug.Assert(!IsPrepared, "Batch RPC should not be prepared!");
3963 Debug.Assert(!IsDirty, "Batch RPC should not be marked as dirty!");
3964 //Currently returnStream is always false, but we may want to return a Reader later.
3965 //if (returnStream) {
3966 // Bid.Trace("<sc.SqlCommand.ExecuteReader|INFO> %d#, Command executed as batch RPC.\n", ObjectID);
3968 Debug.Assert(_SqlRPCBatchArray != null, "RunExecuteReader rpc array not provided");
3969 writeTask = _stateObj.Parser.TdsExecuteRPC(this, _SqlRPCBatchArray, timeout, inSchema, this.Notification, _stateObj, CommandType.StoredProcedure == CommandType, sync: !asyncWrite );
3971 else if ((System.Data.CommandType.Text == this.CommandType) && (0 == GetParameterCount(_parameters))) {
3972 // Send over SQL Batch command if we are not a stored proc and have no parameters
3974 Debug.Assert(!IsUserPrepared, "CommandType.Text with no params should not be prepared!");
3976 Bid.Trace("<sc.SqlCommand.ExecuteReader|INFO> %d#, Command executed as SQLBATCH.\n", ObjectID);
3978 string text = GetCommandText(cmdBehavior) + GetResetOptionsString(cmdBehavior);
3979 writeTask = _stateObj.Parser.TdsExecuteSQLBatch(text, timeout, this.Notification, _stateObj, sync: !asyncWrite);
3981 else if (System.Data.CommandType.Text == this.CommandType) {
3983 Debug.Assert(_cachedMetaData == null || !_dirty, "dirty query should not have cached metadata!"); // can have cached metadata if dirty because of parameters
3985 // someone changed the command text or the parameter schema so we must unprepare the command
3987 // remeber that IsDirty includes test for IsPrepared!
3988 if(_execType == EXECTYPE.PREPARED) {
3989 _hiddenPrepare = true;
3995 if (_execType == EXECTYPE.PREPARED) {
3996 Debug.Assert(this.IsPrepared && (_prepareHandle != -1), "invalid attempt to call sp_execute without a handle!");
3997 rpc = BuildExecute(inSchema);
3999 else if (_execType == EXECTYPE.PREPAREPENDING) {
4000 Debug.Assert(_activeConnection.IsShiloh, "Invalid attempt to call sp_prepexec on non 7.x server");
4001 rpc = BuildPrepExec(cmdBehavior);
4002 // next time through, only do an exec
4003 _execType = EXECTYPE.PREPARED;
4004 _preparedConnectionCloseCount = _activeConnection.CloseCount;
4005 _preparedConnectionReconnectCount = _activeConnection.ReconnectCount;
4006 // mark ourselves as preparing the command
4010 Debug.Assert(_execType == EXECTYPE.UNPREPARED, "Invalid execType!");
4011 BuildExecuteSql(cmdBehavior, null, _parameters, ref rpc);
4014 // if shiloh, then set NOMETADATA_UNLESSCHANGED flag
4015 if (_activeConnection.IsShiloh)
4016 rpc.options = TdsEnums.RPC_NOMETADATA;
4018 Bid.Trace("<sc.SqlCommand.ExecuteReader|INFO> %d#, Command executed as RPC.\n", ObjectID);
4022 Debug.Assert(_rpcArrayOf1[0] == rpc);
4023 writeTask = _stateObj.Parser.TdsExecuteRPC(this, _rpcArrayOf1, timeout, inSchema, this.Notification, _stateObj, CommandType.StoredProcedure == CommandType, sync:!asyncWrite);
4026 Debug.Assert(this.CommandType == System.Data.CommandType.StoredProcedure, "unknown command type!");
4027 // note: invalid asserts on Shiloh. On 8.0 (Shiloh) and above a command is ALWAYS prepared
4028 // and IsDirty is always set if there are changes and the command is marked Prepared!
4029 Debug.Assert(IsShiloh || !IsPrepared, "RPC should not be prepared!");
4030 Debug.Assert(IsShiloh || !IsDirty, "RPC should not be marked as dirty!");
4032 BuildRPC(inSchema, _parameters, ref rpc);
4034 // if we need to augment the command because a user has changed the command behavior (e.g. FillSchema)
4035 // then batch sql them over. This is inefficient (3 round trips) but the only way we can get metadata only from
4037 optionSettings = GetSetOptionsString(cmdBehavior);
4039 Bid.Trace("<sc.SqlCommand.ExecuteReader|INFO> %d#, Command executed as RPC.\n", ObjectID);
4041 // turn set options ON
4042 if (null != optionSettings) {
4043 Task executeTask = _stateObj.Parser.TdsExecuteSQLBatch(optionSettings, timeout, this.Notification, _stateObj, sync: true);
4044 Debug.Assert(executeTask == null, "Shouldn't get a task when doing sync writes");
4046 Debug.Assert(_stateObj._syncOverAsync, "Should not attempt pends in a synchronous call");
4047 bool result = _stateObj.Parser.TryRun(RunBehavior.UntilDone, this, null, null, _stateObj, out dataReady);
4048 if (!result) { throw SQL.SynchronousCallMayNotPend(); }
4049 // and turn OFF when the ds exhausts the stream on Close()
4050 optionSettings = GetResetOptionsString(cmdBehavior);
4053 // turn debugging on
4054 _activeConnection.CheckSQLDebug();
4057 Debug.Assert(_rpcArrayOf1[0] == rpc);
4058 writeTask=_stateObj.Parser.TdsExecuteRPC(this, _rpcArrayOf1, timeout, inSchema, this.Notification, _stateObj, CommandType.StoredProcedure == CommandType, sync:!asyncWrite);
4061 Debug.Assert(writeTask == null || async, "Returned task in sync mode");
4064 decrementAsyncCountOnFailure = false;
4065 if (writeTask != null) {
4066 task = AsyncHelper.CreateContinuationTask(writeTask, () => {
4067 _activeConnection.GetOpenTdsConnection(); // it will throw if connection is closed
4068 cachedAsyncState.SetAsyncReaderState(ds, runBehavior, optionSettings);
4070 onFailure: (exc) => {
4071 _activeConnection.GetOpenTdsConnection().DecrementAsyncCount();
4075 cachedAsyncState.SetAsyncReaderState(ds, runBehavior, optionSettings);
4079 // Always execute - even if no reader!
4080 FinishExecuteReader(ds, runBehavior, optionSettings);
4083 catch (Exception e) {
4084 processFinallyBlock = ADP.IsCatchableExceptionType (e);
4085 if (decrementAsyncCountOnFailure) {
4086 SqlInternalConnectionTds innerConnectionTds = (_activeConnection.InnerConnection as SqlInternalConnectionTds);
4087 if (null != innerConnectionTds) { // it may be closed
4088 innerConnectionTds.DecrementAsyncCount();
4094 TdsParser.ReliabilitySection.Assert("unreliable call to RunExecuteReaderTds"); // you need to setup for a thread abort somewhere before you call this method
4095 if (processFinallyBlock && !async) {
4096 // When executing async, we need to keep the _stateObj alive...
4101 Debug.Assert(async || null == _stateObj, "non-null state object in RunExecuteReader");
4105 private SqlDataReader RunExecuteReaderSmi( CommandBehavior cmdBehavior, RunBehavior runBehavior, bool returnStream ) {
4106 SqlInternalConnectionSmi innerConnection = InternalSmiConnection;
4108 SmiEventStream eventStream = null;
4109 SqlDataReader ds = null;
4110 SmiRequestExecutor requestExecutor = null;
4112 // Set it up, process all of the events, and we're done!
4113 requestExecutor = SetUpSmiRequest( innerConnection );
4116 SysTx.Transaction transaction;
4117 innerConnection.GetCurrentTransactionPair(out transactionId, out transaction);
4119 if (Bid.AdvancedOn) {
4120 Bid.Trace("<sc.SqlCommand.RunExecuteReaderSmi|ADV> %d#, innerConnection=%d#, transactionId=0x%I64x, commandBehavior=%d.\n", ObjectID, innerConnection.ObjectID, transactionId, (int)cmdBehavior);
4123 if (SmiContextFactory.Instance.NegotiatedSmiVersion >= SmiContextFactory.KatmaiVersion) {
4124 eventStream = requestExecutor.Execute(
4125 innerConnection.SmiConnection,
4129 SmiExecuteType.Reader
4133 eventStream = requestExecutor.Execute(
4134 innerConnection.SmiConnection,
4137 SmiExecuteType.Reader
4141 if ( ( runBehavior & RunBehavior.UntilDone ) != 0 ) {
4143 // Consume the results
4144 while( eventStream.HasEvents ) {
4145 eventStream.ProcessEvent( EventSink );
4147 eventStream.Close( EventSink );
4150 if ( returnStream ) {
4151 ds = new SqlDataReaderSmi( eventStream, this, cmdBehavior, innerConnection, EventSink, requestExecutor );
4152 ds.NextResult(); // Position on first set of results
4153 _activeConnection.AddWeakReference(ds, SqlReferenceCollection.DataReaderTag);
4156 EventSink.ProcessMessagesAndThrow();
4158 catch (Exception e) {
4159 // VSTS 159716 - we do not want to handle ThreadAbort, OutOfMemory or similar critical exceptions
4160 // because the state of used objects might remain invalid in this case
4161 if (!ADP.IsCatchableOrSecurityExceptionType(e)) {
4165 if (null != eventStream) {
4166 eventStream.Close( EventSink ); // UNDONE: should cancel instead!
4169 if (requestExecutor != null) {
4170 requestExecutor.Close(EventSink);
4171 EventSink.ProcessMessagesAndThrow(ignoreNonFatalMessages: true);
4180 private SqlDataReader CompleteAsyncExecuteReader() {
4181 SqlDataReader ds = cachedAsyncState.CachedAsyncReader; // should not be null
4182 bool processFinallyBlock = true;
4184 FinishExecuteReader(ds, cachedAsyncState.CachedRunBehavior, cachedAsyncState.CachedSetOptions);
4186 catch (Exception e) {
4187 processFinallyBlock = ADP.IsCatchableExceptionType(e);
4191 TdsParser.ReliabilitySection.Assert("unreliable call to CompleteAsyncExecuteReader"); // you need to setup for a thread abort somewhere before you call this method
4192 if (processFinallyBlock) {
4193 cachedAsyncState.ResetAsyncState();
4201 private void FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, string resetOptionsString) {
4202 // always wrap with a try { FinishExecuteReader(...) } finally { PutStateObject(); }
4205 if (runBehavior == RunBehavior.UntilDone) {
4208 Debug.Assert(_stateObj._syncOverAsync, "Should not attempt pends in a synchronous call");
4209 bool result = _stateObj.Parser.TryRun(RunBehavior.UntilDone, this, ds, null, _stateObj, out dataReady);
4210 if (!result) { throw SQL.SynchronousCallMayNotPend(); }
4212 catch (Exception e) {
4214 if (ADP.IsCatchableExceptionType(e)) {
4216 // The flag is expected to be reset by OnReturnValue. We should receive
4217 // the handle unless command execution failed. If fail, move back to pending
4219 _inPrepare = false; // reset the flag
4220 IsDirty = true; // mark command as dirty so it will be prepared next time we're comming through
4221 _execType = EXECTYPE.PREPAREPENDING; // reset execution type to pending
4232 // bind the parser to the reader if we get this far
4235 _stateObj = null; // the reader now owns this...
4236 ds.ResetOptionsString = resetOptionsString;
4242 // bind this reader to this connection now
4243 _activeConnection.AddWeakReference(ds, SqlReferenceCollection.DataReaderTag);
4245 // force this command to start reading data off the wire.
4246 // this will cause an error to be reported at Execute() time instead of Read() time
4247 // if the command is not set.
4249 _cachedMetaData = ds.MetaData;
4250 ds.IsInitialized = true; // Webdata 104560
4252 catch (Exception e) {
4254 if (ADP.IsCatchableExceptionType(e)) {
4256 // The flag is expected to be reset by OnReturnValue. We should receive
4257 // the handle unless command execution failed. If fail, move back to pending
4259 _inPrepare = false; // reset the flag
4260 IsDirty = true; // mark command as dirty so it will be prepared next time we're comming through
4261 _execType = EXECTYPE.PREPAREPENDING; // reset execution type to pending
4272 private void NotifyDependency() {
4273 if (_sqlDep != null) {
4274 _sqlDep.StartTimer(Notification);
4278 public SqlCommand Clone() {
4279 SqlCommand clone = new SqlCommand(this);
4280 Bid.Trace("<sc.SqlCommand.Clone|API> %d#, clone=%d#\n", ObjectID, clone.ObjectID);
4284 object ICloneable.Clone() {
4288 private void RegisterForConnectionCloseNotification<T>(ref Task<T> outterTask) {
4289 SqlConnection connection = _activeConnection;
4290 if (connection == null) {
4292 throw ADP.ClosedConnectionError();
4295 connection.RegisterForConnectionCloseNotification<T>(ref outterTask, this, SqlReferenceCollection.CommandTag);
4298 // validates that a command has commandText and a non-busy open connection
4299 // throws exception for error case, returns false if the commandText is empty
4300 private void ValidateCommand(string method, bool async) {
4301 if (null == _activeConnection) {
4302 throw ADP.ConnectionRequired(method);
4305 // Ensure that the connection is open and that the Parser is in the correct state
4306 SqlInternalConnectionTds tdsConnection = _activeConnection.InnerConnection as SqlInternalConnectionTds;
4308 // Ensure that if column encryption override was used then server supports its
4309 if (((SqlCommandColumnEncryptionSetting.UseConnectionSetting == ColumnEncryptionSetting && _activeConnection.IsColumnEncryptionSettingEnabled)
4310 || (ColumnEncryptionSetting == SqlCommandColumnEncryptionSetting.Enabled || ColumnEncryptionSetting == SqlCommandColumnEncryptionSetting.ResultSetOnly))
4311 && null != tdsConnection
4312 && null != tdsConnection.Parser
4313 && !tdsConnection.Parser.IsColumnEncryptionSupported) {
4314 throw SQL.TceNotSupported ();
4317 if (tdsConnection != null) {
4318 var parser = tdsConnection.Parser;
4319 if ((parser == null) || (parser.State == TdsParserState.Closed)) {
4320 throw ADP.OpenConnectionRequired(method, ConnectionState.Closed);
4322 else if (parser.State != TdsParserState.OpenLoggedIn) {
4323 throw ADP.OpenConnectionRequired(method, ConnectionState.Broken);
4326 else if (_activeConnection.State == ConnectionState.Closed) {
4327 throw ADP.OpenConnectionRequired(method, ConnectionState.Closed);
4329 else if (_activeConnection.State == ConnectionState.Broken) {
4330 throw ADP.OpenConnectionRequired(method, ConnectionState.Broken);
4333 ValidateAsyncCommand();
4335 TdsParser bestEffortCleanupTarget = null;
4336 RuntimeHelpers.PrepareConstrainedRegions();
4339 TdsParser.ReliabilitySection tdsReliabilitySection = new TdsParser.ReliabilitySection();
4341 RuntimeHelpers.PrepareConstrainedRegions();
4343 tdsReliabilitySection.Start();
4347 bestEffortCleanupTarget = SqlInternalConnection.GetBestEffortCleanupTarget(_activeConnection);
4348 // close any non MARS dead readers, if applicable, and then throw if still busy.
4349 // Throw if we have a live reader on this command
4350 _activeConnection.ValidateConnectionForExecute(method, this);
4355 tdsReliabilitySection.Stop();
4359 catch (System.OutOfMemoryException e)
4361 _activeConnection.Abort(e);
4364 catch (System.StackOverflowException e)
4366 _activeConnection.Abort(e);
4369 catch (System.Threading.ThreadAbortException e)
4371 _activeConnection.Abort(e);
4372 SqlInternalConnection.BestEffortCleanup(bestEffortCleanupTarget);
4375 // Check to see if the currently set transaction has completed. If so,
4376 // null out our local reference.
4377 if (null != _transaction && _transaction.Connection == null)
4378 _transaction = null;
4380 // throw if the connection is in a transaction but there is no
4381 // locally assigned transaction object
4382 if (_activeConnection.HasLocalTransactionFromAPI && (null == _transaction))
4383 throw ADP.TransactionRequired(method);
4385 // if we have a transaction, check to ensure that the active
4386 // connection property matches the connection associated with
4388 if (null != _transaction && _activeConnection != _transaction.Connection)
4389 throw ADP.TransactionConnectionMismatch();
4391 if (ADP.IsEmpty(this.CommandText))
4392 throw ADP.CommandTextRequired(method);
4394 // Notification property must be null for pre-Yukon connections
4395 if ((Notification != null) && !_activeConnection.IsYukonOrNewer) {
4396 throw SQL.NotificationsRequireYukon();
4399 if ((async) && (_activeConnection.IsContextConnection)) {
4400 // Async not supported on Context Connections
4401 throw SQL.NotAvailableOnContextConnection();
4405 private void ValidateAsyncCommand() {
4407 if (cachedAsyncState.PendingAsyncOperation) { // Enforce only one pending async execute at a time.
4408 if (cachedAsyncState.IsActiveConnectionValid(_activeConnection)) {
4409 throw SQL.PendingBeginXXXExists();
4412 _stateObj = null; // Session was re-claimed by session pool upon connection close.
4413 cachedAsyncState.ResetAsyncState();
4418 private void GetStateObject(TdsParser parser = null) {
4419 Debug.Assert (null == _stateObj,"StateObject not null on GetStateObject");
4420 Debug.Assert (null != _activeConnection, "no active connection?");
4422 if (_pendingCancel) {
4423 _pendingCancel = false; // Not really needed, but we'll reset anyways.
4425 // If a pendingCancel exists on the object, we must have had a Cancel() call
4426 // between the point that we entered an Execute* API and the point in Execute* that
4427 // we proceeded to call this function and obtain a stateObject. In that case,
4428 // we now throw a cancelled error.
4429 throw SQL.OperationCancelled();
4432 if (parser == null) {
4433 parser = _activeConnection.Parser;
4434 if ((parser == null) || (parser.State == TdsParserState.Broken) || (parser.State == TdsParserState.Closed)) {
4435 // Connection's parser is null as well, therefore we must be closed
4436 throw ADP.ClosedConnectionError();
4440 TdsParserStateObject stateObj = parser.GetSession(this);
4441 stateObj.StartSession(ObjectID);
4443 _stateObj = stateObj;
4445 if (_pendingCancel) {
4446 _pendingCancel = false; // Not really needed, but we'll reset anyways.
4448 // If a pendingCancel exists on the object, we must have had a Cancel() call
4449 // between the point that we entered this function and the point where we obtained
4450 // and actually assigned the stateObject to the local member. It is possible
4451 // that the flag is set as well as a call to stateObj.Cancel - though that would
4452 // be a no-op. So - throw.
4453 throw SQL.OperationCancelled();
4457 private void ReliablePutStateObject() {
4458 TdsParser bestEffortCleanupTarget = null;
4459 RuntimeHelpers.PrepareConstrainedRegions();
4462 TdsParser.ReliabilitySection tdsReliabilitySection = new TdsParser.ReliabilitySection();
4464 RuntimeHelpers.PrepareConstrainedRegions();
4466 tdsReliabilitySection.Start();
4470 bestEffortCleanupTarget = SqlInternalConnection.GetBestEffortCleanupTarget(_activeConnection);
4476 tdsReliabilitySection.Stop();
4480 catch (System.OutOfMemoryException e)
4482 _activeConnection.Abort(e);
4485 catch (System.StackOverflowException e)
4487 _activeConnection.Abort(e);
4490 catch (System.Threading.ThreadAbortException e)
4492 _activeConnection.Abort(e);
4493 SqlInternalConnection.BestEffortCleanup(bestEffortCleanupTarget);
4498 private void PutStateObject() {
4499 TdsParserStateObject stateObj = _stateObj;
4502 if (null != stateObj) {
4503 stateObj.CloseSession();
4508 /// IMPORTANT NOTE: This is created as a copy of OnDoneProc below for Transparent Column Encryption improvement
4509 /// as there is not much time, to address regressions. Will revisit removing the duplication, when we have time again.
4511 internal void OnDoneDescribeParameterEncryptionProc(TdsParserStateObject stateObj) {
4512 // called per rpc batch complete
4514 // track the records affected for the just completed rpc batch
4515 // _rowsAffected is cumulative for ExecuteNonQuery across all rpc batches
4516 _sqlRPCParameterEncryptionReqArray[_currentlyExecutingDescribeParameterEncryptionRPC].cumulativeRecordsAffected = _rowsAffected;
4518 _sqlRPCParameterEncryptionReqArray[_currentlyExecutingDescribeParameterEncryptionRPC].recordsAffected =
4519 (((0 < _currentlyExecutingDescribeParameterEncryptionRPC) && (0 <= _rowsAffected))
4520 ? (_rowsAffected - Math.Max(_sqlRPCParameterEncryptionReqArray[_currentlyExecutingDescribeParameterEncryptionRPC - 1].cumulativeRecordsAffected, 0))
4523 // track the error collection (not available from TdsParser after ExecuteNonQuery)
4524 // and the which errors are associated with the just completed rpc batch
4525 _sqlRPCParameterEncryptionReqArray[_currentlyExecutingDescribeParameterEncryptionRPC].errorsIndexStart =
4526 ((0 < _currentlyExecutingDescribeParameterEncryptionRPC)
4527 ? _sqlRPCParameterEncryptionReqArray[_currentlyExecutingDescribeParameterEncryptionRPC - 1].errorsIndexEnd
4529 _sqlRPCParameterEncryptionReqArray[_currentlyExecutingDescribeParameterEncryptionRPC].errorsIndexEnd = stateObj.ErrorCount;
4530 _sqlRPCParameterEncryptionReqArray[_currentlyExecutingDescribeParameterEncryptionRPC].errors = stateObj._errors;
4532 // track the warning collection (not available from TdsParser after ExecuteNonQuery)
4533 // and the which warnings are associated with the just completed rpc batch
4534 _sqlRPCParameterEncryptionReqArray[_currentlyExecutingDescribeParameterEncryptionRPC].warningsIndexStart =
4535 ((0 < _currentlyExecutingDescribeParameterEncryptionRPC)
4536 ? _sqlRPCParameterEncryptionReqArray[_currentlyExecutingDescribeParameterEncryptionRPC - 1].warningsIndexEnd
4538 _sqlRPCParameterEncryptionReqArray[_currentlyExecutingDescribeParameterEncryptionRPC].warningsIndexEnd = stateObj.WarningCount;
4539 _sqlRPCParameterEncryptionReqArray[_currentlyExecutingDescribeParameterEncryptionRPC].warnings = stateObj._warnings;
4541 _currentlyExecutingDescribeParameterEncryptionRPC++;
4546 /// IMPORTANT NOTE: There is a copy of this function above in OnDoneDescribeParameterEncryptionProc.
4547 /// Please consider the changes being done in this function for the above function as well.
4549 internal void OnDoneProc() { // called per rpc batch complete
4552 // track the records affected for the just completed rpc batch
4553 // _rowsAffected is cumulative for ExecuteNonQuery across all rpc batches
4554 _SqlRPCBatchArray[_currentlyExecutingBatch].cumulativeRecordsAffected = _rowsAffected;
4556 _SqlRPCBatchArray[_currentlyExecutingBatch].recordsAffected =
4557 (((0 < _currentlyExecutingBatch) && (0 <= _rowsAffected))
4558 ? (_rowsAffected - Math.Max(_SqlRPCBatchArray[_currentlyExecutingBatch-1].cumulativeRecordsAffected, 0))
4561 // track the error collection (not available from TdsParser after ExecuteNonQuery)
4562 // and the which errors are associated with the just completed rpc batch
4563 _SqlRPCBatchArray[_currentlyExecutingBatch].errorsIndexStart =
4564 ((0 < _currentlyExecutingBatch)
4565 ? _SqlRPCBatchArray[_currentlyExecutingBatch-1].errorsIndexEnd
4567 _SqlRPCBatchArray[_currentlyExecutingBatch].errorsIndexEnd = _stateObj.ErrorCount;
4568 _SqlRPCBatchArray[_currentlyExecutingBatch].errors = _stateObj._errors;
4570 // track the warning collection (not available from TdsParser after ExecuteNonQuery)
4571 // and the which warnings are associated with the just completed rpc batch
4572 _SqlRPCBatchArray[_currentlyExecutingBatch].warningsIndexStart =
4573 ((0 < _currentlyExecutingBatch)
4574 ? _SqlRPCBatchArray[_currentlyExecutingBatch-1].warningsIndexEnd
4576 _SqlRPCBatchArray[_currentlyExecutingBatch].warningsIndexEnd = _stateObj.WarningCount;
4577 _SqlRPCBatchArray[_currentlyExecutingBatch].warnings = _stateObj._warnings;
4579 _currentlyExecutingBatch++;
4580 Debug.Assert(_parameterCollectionList.Count >= _currentlyExecutingBatch, "OnDoneProc: Too many DONEPROC events");
4588 internal void OnReturnStatus(int status) {
4592 // Don't set the return status if this is the status for sp_describe_parameter_encryption.
4593 if (IsDescribeParameterEncryptionRPCCurrentlyInProgress)
4596 SqlParameterCollection parameters = _parameters;
4598 if (_parameterCollectionList.Count > _currentlyExecutingBatch) {
4599 parameters = _parameterCollectionList[_currentlyExecutingBatch];
4602 Debug.Assert(false, "OnReturnStatus: SqlCommand got too many DONEPROC events");
4606 // see if a return value is bound
4607 int count = GetParameterCount(parameters);
4608 for (int i = 0; i < count; i++) {
4609 SqlParameter parameter = parameters[i];
4610 if (parameter.Direction == ParameterDirection.ReturnValue) {
4611 object v = parameter.Value;
4613 // if the user bound a sqlint32 (the only valid one for status, use it)
4614 if ( (null != v) && (v.GetType() == typeof(SqlInt32)) ) {
4615 parameter.Value = new SqlInt32(status); // value type
4618 parameter.Value = status;
4627 // Move the return value to the corresponding output parameter.
4628 // Return parameters are sent in the order in which they were defined in the procedure.
4629 // If named, match the parameter name, otherwise fill in based on ordinal position.
4630 // If the parameter is not bound, then ignore the return value.
4632 internal void OnReturnValue(SqlReturnValue rec, TdsParserStateObject stateObj) {
4635 if (!rec.value.IsNull) {
4636 _prepareHandle = rec.value.Int32;
4642 SqlParameterCollection parameters = GetCurrentParameterCollection();
4643 int count = GetParameterCount(parameters);
4646 SqlParameter thisParam = GetParameterForOutputValueExtraction(parameters, rec.parameter, count);
4648 if (null != thisParam) {
4649 // If the parameter's direction is InputOutput, Output or ReturnValue and it needs to be transparently encrypted/decrypted
4650 // then simply decrypt, deserialize and set the value.
4651 if (rec.cipherMD != null &&
4652 thisParam.CipherMetadata != null &&
4653 (thisParam.Direction == ParameterDirection.Output ||
4654 thisParam.Direction == ParameterDirection.InputOutput ||
4655 thisParam.Direction == ParameterDirection.ReturnValue)) {
4656 if(rec.tdsType != TdsEnums.SQLBIGVARBINARY) {
4657 throw SQL.InvalidDataTypeForEncryptedParameter(thisParam.ParameterNameFixed, rec.tdsType, TdsEnums.SQLBIGVARBINARY);
4660 // Decrypt the ciphertext
4661 TdsParser parser = _activeConnection.Parser;
4662 if ((parser == null) || (parser.State == TdsParserState.Closed) || (parser.State == TdsParserState.Broken)) {
4663 throw ADP.ClosedConnectionError();
4666 if (!rec.value.IsNull) {
4668 Debug.Assert(_activeConnection != null, @"_activeConnection should not be null");
4670 // Get the key information from the parameter and decrypt the value.
4671 rec.cipherMD.EncryptionInfo = thisParam.CipherMetadata.EncryptionInfo;
4672 byte[] unencryptedBytes = SqlSecurityUtility.DecryptWithKey(rec.value.ByteArray, rec.cipherMD, _activeConnection.DataSource);
4674 if (unencryptedBytes != null) {
4675 // Denormalize the value and convert it to the parameter type.
4676 SqlBuffer buffer = new SqlBuffer();
4677 parser.DeserializeUnencryptedValue(buffer, unencryptedBytes, rec, stateObj, rec.NormalizationRuleVersion);
4678 thisParam.SetSqlBuffer(buffer);
4681 catch (Exception e) {
4682 throw SQL.ParamDecryptionFailed(thisParam.ParameterNameFixed, null, e);
4686 // Create a new SqlBuffer and set it to null
4687 // Note: We can't reuse the SqlBuffer in "rec" below since it's already been set (to varbinary)
4688 // in previous call to TryProcessReturnValue().
4689 // Note 2: We will be coming down this code path only if the Command Setting is set to use TCE.
4690 // We pass the command setting as TCE enabled in the below call for this reason.
4691 SqlBuffer buff = new SqlBuffer();
4692 TdsParser.GetNullSqlValue(buff, rec, SqlCommandColumnEncryptionSetting.Enabled, parser.Connection);
4693 thisParam.SetSqlBuffer(buff);
4699 // if the value user has supplied a SqlType class, then just copy over the SqlType, otherwise convert
4701 object val = thisParam.Value;
4703 //set the UDT value as typed object rather than bytes
4704 if (SqlDbType.Udt == thisParam.SqlDbType) {
4707 Connection.CheckGetExtendedUDTInfo(rec, true);
4709 //extract the byte array from the param value
4710 if (rec.value.IsNull)
4711 data = DBNull.Value;
4713 data = rec.value.ByteArray; //should work for both sql and non-sql values
4716 //call the connection to instantiate the UDT object
4717 thisParam.Value = Connection.GetUdtValue(data, rec, false);
4719 catch (FileNotFoundException e) {
4721 // Assign Assembly.Load failure in case where assembly not on client.
4722 // This allows execution to complete and failure on SqlParameter.Value.
4723 thisParam.SetUdtLoadError(e);
4725 catch (FileLoadException e) {
4727 // Assign Assembly.Load failure in case where assembly cannot be loaded on client.
4728 // This allows execution to complete and failure on SqlParameter.Value.
4729 thisParam.SetUdtLoadError(e);
4734 thisParam.SetSqlBuffer(rec.value);
4737 MetaType mt = MetaType.GetMetaTypeFromSqlDbType(rec.type, rec.isMultiValued);
4739 if (rec.type == SqlDbType.Decimal) {
4740 thisParam.ScaleInternal = rec.scale;
4741 thisParam.PrecisionInternal = rec.precision;
4743 else if (mt.IsVarTime) {
4744 thisParam.ScaleInternal = rec.scale;
4746 else if (rec.type == SqlDbType.Xml) {
4747 SqlCachedBuffer cachedBuffer = (thisParam.Value as SqlCachedBuffer);
4748 if (null != cachedBuffer) {
4749 thisParam.Value = cachedBuffer.ToString();
4753 if (rec.collation != null) {
4754 Debug.Assert(mt.IsCharType, "Invalid collation structure for non-char type");
4755 thisParam.Collation = rec.collation;
4763 internal void OnParametersAvailableSmi( SmiParameterMetaData[] paramMetaData, ITypedGettersV3 parameterValues ) {
4764 Debug.Assert(null != paramMetaData);
4766 for(int index=0; index < paramMetaData.Length; index++) {
4767 OnParameterAvailableSmi(paramMetaData[index], parameterValues, index);
4771 internal void OnParameterAvailableSmi(SmiParameterMetaData metaData, ITypedGettersV3 parameterValues, int ordinal) {
4772 if ( ParameterDirection.Input != metaData.Direction ) {
4774 if (ParameterDirection.ReturnValue != metaData.Direction) {
4775 name = metaData.Name;
4778 SqlParameterCollection parameters = GetCurrentParameterCollection();
4779 int count = GetParameterCount(parameters);
4780 SqlParameter param = GetParameterForOutputValueExtraction(parameters, name, count);
4782 if ( null != param ) {
4783 param.LocaleId = (int)metaData.LocaleId;
4784 param.CompareInfo = metaData.CompareOptions;
4785 SqlBuffer buffer = new SqlBuffer();
4787 if (_activeConnection.IsKatmaiOrNewer) {
4788 result = ValueUtilsSmi.GetOutputParameterV200Smi(
4789 OutParamEventSink, (SmiTypedGetterSetter)parameterValues, ordinal, metaData, _smiRequestContext, buffer );
4792 result = ValueUtilsSmi.GetOutputParameterV3Smi(
4793 OutParamEventSink, parameterValues, ordinal, metaData, _smiRequestContext, buffer );
4795 if ( null != result ) {
4796 param.Value = result;
4799 param.SetSqlBuffer( buffer );
4805 private SqlParameterCollection GetCurrentParameterCollection() {
4807 if (_parameterCollectionList.Count > _currentlyExecutingBatch) {
4808 return _parameterCollectionList[_currentlyExecutingBatch];
4811 Debug.Assert(false, "OnReturnValue: SqlCommand got too many DONEPROC events");
4820 private SqlParameter GetParameterForOutputValueExtraction( SqlParameterCollection parameters,
4821 string paramName, int paramCount ) {
4822 SqlParameter thisParam = null;
4823 bool foundParam = false;
4825 if (null == paramName) {
4826 // rec.parameter should only be null for a return value from a function
4827 for (int i = 0; i < paramCount; i++) {
4828 thisParam = parameters[i];
4829 // searching for ReturnValue
4830 if (thisParam.Direction == ParameterDirection.ReturnValue) {
4837 for (int i = 0; i < paramCount; i++) {
4838 thisParam = parameters[i];
4839 // searching for Output or InputOutput or ReturnValue with matching name
4840 if (thisParam.Direction != ParameterDirection.Input && thisParam.Direction != ParameterDirection.ReturnValue && paramName == thisParam.ParameterNameFixed) {
4852 private void GetRPCObject(int paramCount, ref _SqlRPC rpc, bool forSpDescribeParameterEncryption = false) {
4853 // Designed to minimize necessary allocations
4856 if (!forSpDescribeParameterEncryption) {
4857 if (_rpcArrayOf1 == null) {
4858 _rpcArrayOf1 = new _SqlRPC[1];
4859 _rpcArrayOf1[0] = new _SqlRPC();
4862 rpc = _rpcArrayOf1[0];
4865 if (_rpcForEncryption == null) {
4866 _rpcForEncryption = new _SqlRPC();
4869 rpc = _rpcForEncryption;
4877 rpc.recordsAffected = default(int?);
4878 rpc.cumulativeRecordsAffected = -1;
4880 rpc.errorsIndexStart = 0;
4881 rpc.errorsIndexEnd = 0;
4884 rpc.warningsIndexStart = 0;
4885 rpc.warningsIndexEnd = 0;
4886 rpc.warnings = null;
4887 rpc.needsFetchParameterEncryptionMetadata = false;
4889 // Make sure there is enough space in the parameters and paramoptions arrays
4890 if(rpc.parameters == null || rpc.parameters.Length < paramCount) {
4891 rpc.parameters = new SqlParameter[paramCount];
4893 else if (rpc.parameters.Length > paramCount) {
4894 rpc.parameters[paramCount]=null; // Terminator
4896 if(rpc.paramoptions == null || (rpc.paramoptions.Length < paramCount)) {
4897 rpc.paramoptions = new byte[paramCount];
4900 for (ii = 0 ; ii < paramCount ; ii++)
4901 rpc.paramoptions[ii] = 0;
4905 private void SetUpRPCParameters (_SqlRPC rpc, int startCount, bool inSchema, SqlParameterCollection parameters) {
4907 int paramCount = GetParameterCount(parameters) ;
4909 TdsParser parser = _activeConnection.Parser;
4910 bool yukonOrNewer = parser.IsYukonOrNewer;
4912 for (ii = 0; ii < paramCount; ii++) {
4913 SqlParameter parameter = parameters[ii];
4914 parameter.Validate(ii, CommandType.StoredProcedure == CommandType);
4916 // func will change type to that with a 4 byte length if the type has a two
4917 // byte length and a parameter length > than that expressable in 2 bytes
4918 if ((!parameter.ValidateTypeLengths(yukonOrNewer).IsPlp) && (parameter.Direction != ParameterDirection.Output)) {
4919 parameter.FixStreamDataForNonPLP();
4922 if (ShouldSendParameter(parameter)) {
4923 rpc.parameters[j] = parameter;
4926 if (parameter.Direction == ParameterDirection.InputOutput ||
4927 parameter.Direction == ParameterDirection.Output)
4928 rpc.paramoptions[j] = TdsEnums.RPC_PARAM_BYREF;
4930 // Set the encryped bit, if the parameter is to be encrypted.
4931 if (parameter.CipherMetadata != null) {
4932 rpc.paramoptions[j] |= TdsEnums.RPC_PARAM_ENCRYPTED;
4935 // set default value bit
4936 if (parameter.Direction != ParameterDirection.Output) {
4937 // remember that null == Convert.IsEmpty, DBNull.Value is a database null!
4939 // MDAC 62117, don't assume a default value exists for parameters in the case when
4940 // the user is simply requesting schema
4941 // SQLBUVSTS 179488 TVPs use DEFAULT and do not allow NULL, even for schema only.
4942 if (null == parameter.Value && (!inSchema || SqlDbType.Structured == parameter.SqlDbType)) {
4943 rpc.paramoptions[j] |= TdsEnums.RPC_PARAM_DEFAULT;
4947 // Must set parameter option bit for LOB_COOKIE if unfilled LazyMat blob
4956 // prototype for sp_prepexec is:
4957 // sp_prepexec(@handle int IN/OUT, @batch_params ntext, @batch_text ntext, param1value,param2value...)
4959 private _SqlRPC BuildPrepExec(CommandBehavior behavior) {
4960 Debug.Assert(System.Data.CommandType.Text == this.CommandType, "invalid use of sp_prepexec for stored proc invocation!");
4961 SqlParameter sqlParam;
4964 int count = CountSendableParameters(_parameters);
4967 GetRPCObject(count + j, ref rpc);
4969 rpc.ProcID = TdsEnums.RPC_PROCID_PREPEXEC;
4970 rpc.rpcName = TdsEnums.SP_PREPEXEC;
4973 sqlParam = new SqlParameter(null, SqlDbType.Int);
4974 sqlParam.Direction = ParameterDirection.InputOutput;
4975 sqlParam.Value = _prepareHandle;
4976 rpc.parameters[0] = sqlParam;
4977 rpc.paramoptions[0] = TdsEnums.RPC_PARAM_BYREF;
4980 string paramList = BuildParamList(_stateObj.Parser, _parameters);
4981 sqlParam = new SqlParameter(null, ((paramList.Length<<1)<=TdsEnums.TYPE_SIZE_LIMIT)?SqlDbType.NVarChar:SqlDbType.NText, paramList.Length);
4982 sqlParam.Value = paramList;
4983 rpc.parameters[1] = sqlParam;
4986 string text = GetCommandText(behavior);
4987 sqlParam = new SqlParameter(null, ((text.Length<<1)<=TdsEnums.TYPE_SIZE_LIMIT)?SqlDbType.NVarChar:SqlDbType.NText, text.Length);
4988 sqlParam.Value = text;
4989 rpc.parameters[2] = sqlParam;
4991 SetUpRPCParameters (rpc, j, false, _parameters);
4997 // returns true if the parameter is not a return value
4998 // and it's value is not DBNull (for a nullable parameter)
5000 private static bool ShouldSendParameter(SqlParameter p, bool includeReturnValue = false) {
5001 switch (p.Direction) {
5002 case ParameterDirection.ReturnValue:
5003 // return value parameters are not sent, except for the parameter list of sp_describe_parameter_encryption
5004 return includeReturnValue;
5005 case ParameterDirection.Output:
5006 case ParameterDirection.InputOutput:
5007 case ParameterDirection.Input:
5008 // InputOutput/Output parameters are aways sent
5011 Debug.Assert(false, "Invalid ParameterDirection!");
5016 private int CountSendableParameters(SqlParameterCollection parameters) {
5019 if (parameters != null) {
5020 int count = parameters.Count;
5021 for (int i = 0; i < count; i++) {
5022 if (ShouldSendParameter(parameters[i]))
5029 // Returns total number of parameters
5030 private int GetParameterCount(SqlParameterCollection parameters) {
5031 return ((null != parameters) ? parameters.Count : 0);
5035 // build the RPC record header for this stored proc and add parameters
5037 private void BuildRPC(bool inSchema, SqlParameterCollection parameters, ref _SqlRPC rpc) {
5038 Debug.Assert(this.CommandType == System.Data.CommandType.StoredProcedure, "Command must be a stored proc to execute an RPC");
5039 int count = CountSendableParameters(parameters);
5040 GetRPCObject(count, ref rpc);
5042 rpc.rpcName = this.CommandText; // just get the raw command text
5044 SetUpRPCParameters ( rpc, 0, inSchema, parameters);
5048 // build the RPC record header for sp_unprepare
5050 // prototype for sp_unprepare is:
5051 // sp_unprepare(@handle)
5054 private _SqlRPC BuildUnprepare() {
5055 Debug.Assert(_prepareHandle != 0, "Invalid call to sp_unprepare without a valid handle!");
5058 GetRPCObject(1, ref rpc);
5059 SqlParameter sqlParam;
5061 rpc.ProcID = TdsEnums.RPC_PROCID_UNPREPARE;
5062 rpc.rpcName = TdsEnums.SP_UNPREPARE;
5065 sqlParam = new SqlParameter(null, SqlDbType.Int);
5066 sqlParam.Value = _prepareHandle;
5067 rpc.parameters[0] = sqlParam;
5073 // build the RPC record header for sp_execute
5075 // prototype for sp_execute is:
5076 // sp_execute(@handle int,param1value,param2value...)
5078 private _SqlRPC BuildExecute(bool inSchema) {
5079 Debug.Assert(_prepareHandle != -1, "Invalid call to sp_execute without a valid handle!");
5082 int count = CountSendableParameters(_parameters);
5085 GetRPCObject(count + j, ref rpc);
5087 SqlParameter sqlParam;
5089 rpc.ProcID = TdsEnums.RPC_PROCID_EXECUTE;
5090 rpc.rpcName = TdsEnums.SP_EXECUTE;
5093 sqlParam = new SqlParameter(null, SqlDbType.Int);
5094 sqlParam.Value = _prepareHandle;
5095 rpc.parameters[0] = sqlParam;
5097 SetUpRPCParameters (rpc, j, inSchema, _parameters);
5102 // build the RPC record header for sp_executesql and add the parameters
5104 // prototype for sp_executesql is:
5105 // sp_executesql(@batch_text nvarchar(4000),@batch_params nvarchar(4000), param1,.. paramN)
5106 private void BuildExecuteSql(CommandBehavior behavior, string commandText, SqlParameterCollection parameters, ref _SqlRPC rpc) {
5108 Debug.Assert(_prepareHandle == -1, "This command has an existing handle, use sp_execute!");
5109 Debug.Assert(System.Data.CommandType.Text == this.CommandType, "invalid use of sp_executesql for stored proc invocation!");
5111 SqlParameter sqlParam;
5113 int cParams = CountSendableParameters(parameters);
5121 GetRPCObject(cParams + j, ref rpc);
5122 rpc.ProcID = TdsEnums.RPC_PROCID_EXECUTESQL;
5123 rpc.rpcName = TdsEnums.SP_EXECUTESQL;
5126 if (commandText == null) {
5127 commandText = GetCommandText(behavior);
5129 sqlParam = new SqlParameter(null, ((commandText.Length<<1)<=TdsEnums.TYPE_SIZE_LIMIT)?SqlDbType.NVarChar:SqlDbType.NText, commandText.Length);
5130 sqlParam.Value = commandText;
5131 rpc.parameters[0] = sqlParam;
5134 string paramList = BuildParamList(_stateObj.Parser, BatchRPCMode ? parameters : _parameters);
5135 sqlParam = new SqlParameter(null, ((paramList.Length<<1)<=TdsEnums.TYPE_SIZE_LIMIT)?SqlDbType.NVarChar:SqlDbType.NText, paramList.Length);
5136 sqlParam.Value = paramList;
5137 rpc.parameters[1] = sqlParam;
5139 bool inSchema = (0 != (behavior & CommandBehavior.SchemaOnly));
5140 SetUpRPCParameters (rpc, j, inSchema, parameters);
5145 /// This function constructs a string parameter containing the exec statement in the following format
5146 /// N'EXEC sp_name @param1=@param1, @param1=@param2, ..., @paramN=@paramN'
5152 private SqlParameter BuildStoredProcedureStatementForColumnEncryption(string storedProcedureName, SqlParameter[] parameters) {
5153 Debug.Assert(CommandType == CommandType.StoredProcedure, "BuildStoredProcedureStatementForColumnEncryption() should only be called for stored procedures");
5154 Debug.Assert(!string.IsNullOrWhiteSpace(storedProcedureName), "storedProcedureName cannot be null or empty in BuildStoredProcedureStatementForColumnEncryption");
5155 Debug.Assert(parameters != null, "parameters cannot be null in BuildStoredProcedureStatementForColumnEncryption");
5157 StringBuilder execStatement = new StringBuilder();
5158 execStatement.Append(@"EXEC ");
5160 // Find the return value parameter (if any).
5161 SqlParameter returnValueParameter = null;
5162 foreach (SqlParameter parameter in parameters) {
5163 if (parameter.Direction == ParameterDirection.ReturnValue) {
5164 returnValueParameter = parameter;
5169 // If there is a return value parameter we need to assign the result to it.
5170 // EXEC @returnValue = moduleName [parameters]
5171 if (returnValueParameter != null) {
5172 execStatement.AppendFormat(@"{0}=", returnValueParameter.ParameterNameFixed);
5175 execStatement.Append(ParseAndQuoteIdentifier(storedProcedureName, false));
5177 // Build parameter list in the format
5178 // @param1=@param1, @param1=@param2, ..., @paramn=@paramn
5180 // Append the first parameter
5183 if(parameters.Count() > 0) {
5184 // Skip the return value parameters.
5185 while (i < parameters.Count() && parameters[i].Direction == ParameterDirection.ReturnValue) {
5189 if (i < parameters.Count()) {
5190 // Possibility of a SQL Injection issue through parameter names and how to construct valid identifier for parameters.
5191 // Since the parameters comes from application itself, there should not be a security vulnerability.
5192 // Also since the query is not executed, but only analyzed there is no possibility for elevation of priviledge, but only for
5193 // incorrect results which would only affect the user that attempts the injection.
5194 execStatement.AppendFormat(@" {0}={0}", parameters[i].ParameterNameFixed);
5196 // InputOutput and Output parameters need to be marked as such.
5197 if (parameters[i].Direction == ParameterDirection.Output ||
5198 parameters[i].Direction == ParameterDirection.InputOutput) {
5199 execStatement.AppendFormat(@" OUTPUT");
5204 // Move to the next parameter.
5207 // Append the rest of parameters
5208 for (; i < parameters.Count(); i++) {
5209 if (parameters[i].Direction != ParameterDirection.ReturnValue) {
5210 execStatement.AppendFormat(@", {0}={0}", parameters[i].ParameterNameFixed);
5212 // InputOutput and Output parameters need to be marked as such.
5213 if (parameters[i].Direction == ParameterDirection.Output ||
5214 parameters[i].Direction == ParameterDirection.InputOutput) {
5215 execStatement.AppendFormat(@" OUTPUT");
5220 // Construct @tsql SqlParameter to be returned
5221 SqlParameter tsqlParameter = new SqlParameter(null, ((execStatement.Length << 1) <= TdsEnums.TYPE_SIZE_LIMIT) ? SqlDbType.NVarChar : SqlDbType.NText, execStatement.Length);
5222 tsqlParameter.Value = execStatement.ToString();
5224 return tsqlParameter;
5227 // paramList parameter for sp_executesql, sp_prepare, and sp_prepexec
5228 internal string BuildParamList(TdsParser parser, SqlParameterCollection parameters, bool includeReturnValue = false) {
5229 StringBuilder paramList = new StringBuilder();
5230 bool fAddSeperator = false;
5232 bool yukonOrNewer = parser.IsYukonOrNewer;
5236 count = parameters.Count;
5237 for (int i = 0; i < count; i++) {
5238 SqlParameter sqlParam = parameters[i];
5239 sqlParam.Validate(i, CommandType.StoredProcedure == CommandType);
5240 // skip ReturnValue parameters; we never send them to the server
5241 if (!ShouldSendParameter(sqlParam, includeReturnValue))
5244 // add our separator for the ith parmeter
5246 paramList.Append(',');
5248 paramList.Append(sqlParam.ParameterNameFixed);
5250 MetaType mt = sqlParam.InternalMetaType;
5252 //for UDTs, get the actual type name. Get only the typename, omitt catalog and schema names.
5253 //in TSQL you should only specify the unqualified type name
5255 // paragraph above doesn't seem to be correct. Server won't find the type
5256 // if we don't provide a fully qualified name
5257 paramList.Append(" ");
5258 if (mt.SqlDbType == SqlDbType.Udt) {
5259 string fullTypeName = sqlParam.UdtTypeName;
5260 if(ADP.IsEmpty(fullTypeName))
5261 throw SQL.MustSetUdtTypeNameForUdtParams();
5262 // DEVNOTE: do we need to escape the full type name?
5263 paramList.Append(ParseAndQuoteIdentifier(fullTypeName, true /* is UdtTypeName */));
5265 else if (mt.SqlDbType == SqlDbType.Structured) {
5266 string typeName = sqlParam.TypeName;
5267 if (ADP.IsEmpty(typeName)) {
5268 throw SQL.MustSetTypeNameForParam(mt.TypeName, sqlParam.ParameterNameFixed);
5270 paramList.Append(ParseAndQuoteIdentifier(typeName, false /* is not UdtTypeName*/));
5272 // TVPs currently are the only Structured type and must be read only, so add that keyword
5273 paramList.Append(" READONLY");
5276 // func will change type to that with a 4 byte length if the type has a two
5277 // byte length and a parameter length > than that expressable in 2 bytes
5278 mt = sqlParam.ValidateTypeLengths(yukonOrNewer);
5279 if ((!mt.IsPlp) && (sqlParam.Direction != ParameterDirection.Output)) {
5280 sqlParam.FixStreamDataForNonPLP();
5282 paramList.Append(mt.TypeName);
5285 fAddSeperator = true;
5287 if (mt.SqlDbType == SqlDbType.Decimal) {
5288 byte precision = sqlParam.GetActualPrecision();
5289 byte scale = sqlParam.GetActualScale();
5291 paramList.Append('(');
5293 if (0 == precision) {
5295 precision = TdsEnums.DEFAULT_NUMERIC_PRECISION;
5297 precision = TdsEnums.SPHINX_DEFAULT_NUMERIC_PRECISION;
5301 paramList.Append(precision);
5302 paramList.Append(',');
5303 paramList.Append(scale);
5304 paramList.Append(')');
5306 else if (mt.IsVarTime) {
5307 byte scale = sqlParam.GetActualScale();
5309 paramList.Append('(');
5310 paramList.Append(scale);
5311 paramList.Append(')');
5313 else if (false == mt.IsFixed && false == mt.IsLong && mt.SqlDbType != SqlDbType.Timestamp && mt.SqlDbType != SqlDbType.Udt && SqlDbType.Structured != mt.SqlDbType) {
5314 int size = sqlParam.Size;
5316 paramList.Append('(');
5318 // if using non unicode types, obtain the actual byte length from the parser, with it's associated code page
5319 if (mt.IsAnsiType) {
5320 object val = sqlParam.GetCoercedValue();
5323 // deal with the sql types
5324 if ((null != val) && (DBNull.Value != val)) {
5325 s = (val as string);
5327 SqlString sval = val is SqlString ? (SqlString)val : SqlString.Null;
5335 int actualBytes = parser.GetEncodingCharLength(s, sqlParam.GetActualSize(), sqlParam.Offset, null);
5336 // if actual number of bytes is greater than the user given number of chars, use actual bytes
5337 if (actualBytes > size)
5342 // bug 49497, if the user specifies a 0-sized parameter for a variable len field
5343 // pass over max size (8000 bytes or 4000 characters for wide types)
5345 size = mt.IsSizeInCharacters ? (TdsEnums.MAXSIZE >> 1) : TdsEnums.MAXSIZE;
5347 paramList.Append(size);
5348 paramList.Append(')');
5350 else if (mt.IsPlp && (mt.SqlDbType != SqlDbType.Xml) && (mt.SqlDbType != SqlDbType.Udt)) {
5351 paramList.Append("(max) ");
5354 // set the output bit for Output or InputOutput parameters
5355 if (sqlParam.Direction != ParameterDirection.Input)
5356 paramList.Append(" " + TdsEnums.PARAM_OUTPUT);
5359 return paramList.ToString();
5362 // Adds quotes to each part of a SQL identifier that may be multi-part, while leaving
5363 // the result as a single composite name.
5364 private string ParseAndQuoteIdentifier(string identifier, bool isUdtTypeName) {
5365 string[] strings = SqlParameter.ParseTypeName(identifier, isUdtTypeName);
5366 StringBuilder bld = new StringBuilder();
5368 // Stitching back together is a little tricky. Assume we want to build a full multi-part name
5369 // with all parts except trimming separators for leading empty names (null or empty strings,
5370 // but not whitespace). Separators in the middle should be added, even if the name part is
5371 // null/empty, to maintain proper location of the parts.
5372 for (int i = 0; i < strings.Length; i++ ) {
5373 if (0 < bld.Length) {
5376 if (null != strings[i] && 0 != strings[i].Length) {
5377 bld.Append(ADP.BuildQuotedString("[", "]", strings[i]));
5381 return bld.ToString();
5384 // returns set option text to turn on format only and key info on and off
5385 // @devnote: When we are executing as a text command, then we never need
5386 // to turn off the options since they command text is executed in the scope of sp_executesql.
5387 // For a stored proc command, however, we must send over batch sql and then turn off
5388 // the set options after we read the data. See the code in Command.Execute()
5389 private string GetSetOptionsString(CommandBehavior behavior) {
5392 if ((System.Data.CommandBehavior.SchemaOnly == (behavior & CommandBehavior.SchemaOnly)) ||
5393 (System.Data.CommandBehavior.KeyInfo == (behavior & CommandBehavior.KeyInfo))) {
5395 // MDAC 56898 - SET FMTONLY ON will cause the server to ignore other SET OPTIONS, so turn
5396 // it off before we ask for browse mode metadata
5397 s = TdsEnums.FMTONLY_OFF;
5399 if (System.Data.CommandBehavior.KeyInfo == (behavior & CommandBehavior.KeyInfo)) {
5400 s = s + TdsEnums.BROWSE_ON;
5403 if (System.Data.CommandBehavior.SchemaOnly == (behavior & CommandBehavior.SchemaOnly)) {
5404 s = s + TdsEnums.FMTONLY_ON;
5411 private string GetResetOptionsString(CommandBehavior behavior) {
5414 // SET FMTONLY ON OFF
5415 if (System.Data.CommandBehavior.SchemaOnly == (behavior & CommandBehavior.SchemaOnly)) {
5416 s = s + TdsEnums.FMTONLY_OFF;
5419 // SET NO_BROWSETABLE OFF
5420 if (System.Data.CommandBehavior.KeyInfo == (behavior & CommandBehavior.KeyInfo)) {
5421 s = s + TdsEnums.BROWSE_OFF;
5427 private String GetCommandText(CommandBehavior behavior) {
5428 // build the batch string we send over, since we execute within a stored proc (sp_executesql), the SET options never need to be
5429 // turned off since they are scoped to the sproc
5430 Debug.Assert(System.Data.CommandType.Text == this.CommandType, "invalid call to GetCommandText for stored proc!");
5431 return GetSetOptionsString(behavior) + this.CommandText;
5435 // build the RPC record header for sp_executesql and add the parameters
5437 // the prototype for sp_prepare is:
5438 // sp_prepare(@handle int OUTPUT, @batch_params ntext, @batch_text ntext, @options int default 0x1)
5439 private _SqlRPC BuildPrepare(CommandBehavior behavior) {
5440 Debug.Assert(System.Data.CommandType.Text == this.CommandType, "invalid use of sp_prepare for stored proc invocation!");
5443 GetRPCObject(3, ref rpc);
5444 SqlParameter sqlParam;
5446 rpc.ProcID = TdsEnums.RPC_PROCID_PREPARE;
5447 rpc.rpcName = TdsEnums.SP_PREPARE;
5450 sqlParam = new SqlParameter(null, SqlDbType.Int);
5451 sqlParam.Direction = ParameterDirection.Output;
5452 rpc.parameters[0] = sqlParam;
5453 rpc.paramoptions[0] = TdsEnums.RPC_PARAM_BYREF;
5456 string paramList = BuildParamList(_stateObj.Parser, _parameters);
5457 sqlParam = new SqlParameter(null, ((paramList.Length<<1)<=TdsEnums.TYPE_SIZE_LIMIT)?SqlDbType.NVarChar:SqlDbType.NText, paramList.Length);
5458 sqlParam.Value = paramList;
5459 rpc.parameters[1] = sqlParam;
5462 string text = GetCommandText(behavior);
5463 sqlParam = new SqlParameter(null, ((text.Length<<1)<=TdsEnums.TYPE_SIZE_LIMIT)?SqlDbType.NVarChar:SqlDbType.NText, text.Length);
5464 sqlParam.Value = text;
5465 rpc.parameters[2] = sqlParam;
5469 sqlParam = new SqlParameter(null, SqlDbType.Int);
5470 rpc.Parameters[3] = sqlParam;
5475 internal void CheckThrowSNIException() {
5476 var stateObj = _stateObj;
5477 if (stateObj != null) {
5478 stateObj.CheckThrowSNIException();
5482 // We're being notified that the underlying connection has closed
5483 internal void OnConnectionClosed() {
5485 var stateObj = _stateObj;
5486 if (stateObj != null) {
5487 stateObj.OnConnectionClosed();
5492 internal TdsParserStateObject StateObject {
5498 private bool IsPrepared {
5499 get { return(_execType != EXECTYPE.UNPREPARED);}
5502 private bool IsUserPrepared {
5503 get { return IsPrepared && !_hiddenPrepare && !IsDirty; }
5506 internal bool IsDirty {
5508 // only dirty if prepared
5509 var activeConnection = _activeConnection;
5510 return (IsPrepared &&
5512 ((_parameters != null) && (_parameters.IsDirty)) ||
5513 ((activeConnection != null) && ((activeConnection.CloseCount != _preparedConnectionCloseCount) || (activeConnection.ReconnectCount != _preparedConnectionReconnectCount)))));
5516 // only mark the command as dirty if it is already prepared
5517 // but always clear the value if it we are clearing the dirty flag
5518 _dirty = value ? IsPrepared : false;
5519 if (null != _parameters) {
5520 _parameters.IsDirty = _dirty;
5522 _cachedMetaData = null;
5527 /// Get or set the number of records affected by SpDescribeParameterEncryption.
5528 /// The below line is used only for debug asserts and not exposed publicly or impacts functionality otherwise.
5530 internal int RowsAffectedByDescribeParameterEncryption
5533 return _rowsAffectedBySpDescribeParameterEncryption;
5536 if (-1 == _rowsAffectedBySpDescribeParameterEncryption) {
5537 _rowsAffectedBySpDescribeParameterEncryption = value;
5539 else if (0 < value) {
5540 _rowsAffectedBySpDescribeParameterEncryption += value;
5545 internal int InternalRecordsAffected {
5547 return _rowsAffected;
5550 if (-1 == _rowsAffected) {
5551 _rowsAffected = value;
5553 else if (0 < value) {
5554 _rowsAffected += value;
5559 internal bool BatchRPCMode {
5561 return _batchRPCMode;
5564 _batchRPCMode = value;
5566 if (_batchRPCMode == false) {
5567 ClearBatchCommand();
5569 if (_RPCList == null) {
5570 _RPCList = new List<_SqlRPC>();
5572 if (_parameterCollectionList == null) {
5573 _parameterCollectionList = new List<SqlParameterCollection>();
5580 /// Clear the state in sqlcommand related to describe parameter encryption RPC requests.
5582 private void ClearDescribeParameterEncryptionRequests() {
5583 _sqlRPCParameterEncryptionReqArray = null;
5584 _currentlyExecutingDescribeParameterEncryptionRPC = 0;
5585 _isDescribeParameterEncryptionRPCCurrentlyInProgress = false;
5586 _rowsAffectedBySpDescribeParameterEncryption = -1;
5589 internal void ClearBatchCommand() {
5590 List<_SqlRPC> rpcList = _RPCList;
5591 if (null != rpcList) {
5594 if (null != _parameterCollectionList) {
5595 _parameterCollectionList.Clear();
5597 _SqlRPCBatchArray = null;
5598 _currentlyExecutingBatch = 0;
5602 /// Set the column encryption setting to the new one.
5603 /// Do not allow conflicting column encryption settings.
5605 private void SetColumnEncryptionSetting(SqlCommandColumnEncryptionSetting newColumnEncryptionSetting) {
5606 if (!this._wasBatchModeColumnEncryptionSettingSetOnce) {
5607 this._columnEncryptionSetting = newColumnEncryptionSetting;
5608 this._wasBatchModeColumnEncryptionSettingSetOnce = true;
5611 if (this._columnEncryptionSetting != newColumnEncryptionSetting) {
5612 throw SQL.BatchedUpdateColumnEncryptionSettingMismatch();
5617 internal void AddBatchCommand(string commandText, SqlParameterCollection parameters, CommandType cmdType, SqlCommandColumnEncryptionSetting columnEncryptionSetting) {
5618 Debug.Assert(BatchRPCMode, "Command is not in batch RPC Mode");
5619 Debug.Assert(_RPCList != null);
5620 Debug.Assert(_parameterCollectionList != null);
5622 _SqlRPC rpc = new _SqlRPC();
5624 this.CommandText = commandText;
5625 this.CommandType = cmdType;
5627 // Set the column encryption setting.
5628 SetColumnEncryptionSetting(columnEncryptionSetting);
5631 if (cmdType == CommandType.StoredProcedure) {
5632 BuildRPC(false, parameters, ref rpc);
5635 // All batch sql statements must be executed inside sp_executesql, including those without parameters
5636 BuildExecuteSql(CommandBehavior.Default, commandText, parameters, ref rpc);
5639 // Always add a parameters collection per RPC, even if there are no parameters.
5640 _parameterCollectionList.Add(parameters);
5642 ReliablePutStateObject();
5645 internal int ExecuteBatchRPCCommand() {
5647 Debug.Assert(BatchRPCMode, "Command is not in batch RPC Mode");
5648 Debug.Assert(_RPCList != null, "No batch commands specified");
5649 _SqlRPCBatchArray = _RPCList.ToArray();
5650 _currentlyExecutingBatch = 0;
5651 return ExecuteNonQuery(); // Check permissions, execute, return output params
5655 internal int? GetRecordsAffected(int commandIndex) {
5656 Debug.Assert(BatchRPCMode, "Command is not in batch RPC Mode");
5657 Debug.Assert(_SqlRPCBatchArray != null, "batch command have been cleared");
5658 return _SqlRPCBatchArray[commandIndex].recordsAffected;
5661 internal SqlException GetErrors(int commandIndex) {
5662 SqlException result = null;
5663 int length = (_SqlRPCBatchArray[commandIndex].errorsIndexEnd - _SqlRPCBatchArray[commandIndex].errorsIndexStart);
5665 SqlErrorCollection errors = new SqlErrorCollection();
5666 for(int i = _SqlRPCBatchArray[commandIndex].errorsIndexStart; i < _SqlRPCBatchArray[commandIndex].errorsIndexEnd; ++i) {
5667 errors.Add(_SqlRPCBatchArray[commandIndex].errors[i]);
5669 for(int i = _SqlRPCBatchArray[commandIndex].warningsIndexStart; i < _SqlRPCBatchArray[commandIndex].warningsIndexEnd; ++i) {
5670 errors.Add(_SqlRPCBatchArray[commandIndex].warnings[i]);
5672 result = SqlException.CreateException(errors, Connection.ServerVersion, Connection.ClientConnectionId);
5677 // Allocates and initializes a new SmiRequestExecutor based on the current command state
5678 private SmiRequestExecutor SetUpSmiRequest( SqlInternalConnectionSmi innerConnection ) {
5680 // General Approach To Ensure Security of Marshalling:
5681 // Only touch each item in the command once
5682 // (i.e. only grab a reference to each param once, only
5683 // read the type from that param once, etc.). The problem is
5684 // that if the user changes something on the command in the
5685 // middle of marshaling, it can overwrite the native buffers
5686 // set up. For example, if max length is used to allocate
5687 // buffers, but then re-read from the parameter to truncate
5688 // strings, the user could extend the length and overwrite
5691 if (null != Notification){
5692 throw SQL.NotificationsNotAvailableOnContextConnection();
5695 SmiParameterMetaData[] requestMetaData = null;
5696 ParameterPeekAheadValue[] peekAheadValues = null;
5698 // Length of rgMetadata becomes *the* official count of parameters to use,
5699 // don't rely on Parameters.Count after this point, as the user could change it.
5700 int count = GetParameterCount( Parameters );
5702 requestMetaData = new SmiParameterMetaData[count];
5703 peekAheadValues = new ParameterPeekAheadValue[count];
5705 // set up the metadata
5706 for ( int index=0; index<count; index++ ) {
5707 SqlParameter param = Parameters[index];
5708 param.Validate(index, CommandType.StoredProcedure == CommandType);
5709 requestMetaData[index] = param.MetaDataForSmi(out peekAheadValues[index]);
5711 // Check for valid type for version negotiated
5712 if (!innerConnection.IsKatmaiOrNewer) {
5713 MetaType mt = MetaType.GetMetaTypeFromSqlDbType(requestMetaData[index].SqlDbType, requestMetaData[index].IsMultiValued);
5714 if (!mt.Is90Supported) {
5715 throw ADP.VersionDoesNotSupportDataType(mt.TypeName);
5721 // Allocate the new request
5722 CommandType cmdType = CommandType;
5723 _smiRequestContext = innerConnection.InternalContext;
5724 SmiRequestExecutor requestExecutor = _smiRequestContext.CreateRequestExecutor(
5732 EventSink.ProcessMessagesAndThrow();
5734 // Now assign param values
5735 for ( int index=0; index<count; index++ ) {
5736 if ( ParameterDirection.Output != requestMetaData[index].Direction &&
5737 ParameterDirection.ReturnValue != requestMetaData[index].Direction ) {
5738 SqlParameter param = Parameters[index];
5739 // going back to command for parameter is ok, since we'll only pick up values now.
5740 object value = param.GetCoercedValue();
5741 if (value is XmlDataFeed && requestMetaData[index].SqlDbType != SqlDbType.Xml) {
5742 value = MetaType.GetStringFromXml(((XmlDataFeed)value)._source);
5744 ExtendedClrTypeCode typeCode = MetaDataUtilsSmi.DetermineExtendedTypeCodeForUseWithSqlDbType(requestMetaData[index].SqlDbType, requestMetaData[index].IsMultiValued, value, null /* parameters don't use CLR Type for UDTs */, SmiContextFactory.Instance.NegotiatedSmiVersion);
5746 // Handle null reference as special case for parameters
5747 if ( CommandType.StoredProcedure == cmdType &&
5748 ExtendedClrTypeCode.Empty == typeCode ) {
5749 requestExecutor.SetDefault( index );
5752 // SQLBU 402391 & 403631: Exception to prevent Parameter.Size data corruption cases from working.
5753 // This should be temporary until changing to correct behavior can be safely implemented.
5754 // initial size criteria is the same for all affected types
5755 // NOTE: assumes size < -1 is handled by SqlParameter.Size setter
5756 int size = param.Size;
5757 if (size != 0 && size != SmiMetaData.UnlimitedMaxLengthIndicator && !param.SizeInferred) {
5758 switch(requestMetaData[index].SqlDbType) {
5759 case SqlDbType.Image:
5760 case SqlDbType.Text:
5761 if (size != Int32.MaxValue) {
5762 throw SQL.ParameterSizeRestrictionFailure(index);
5766 case SqlDbType.NText:
5767 if (size != Int32.MaxValue/2) {
5768 throw SQL.ParameterSizeRestrictionFailure(index);
5772 case SqlDbType.VarBinary:
5773 case SqlDbType.VarChar:
5774 // Allow size==Int32.MaxValue because of DeriveParameters
5775 if (size > 0 && size != Int32.MaxValue && requestMetaData[index].MaxLength == SmiMetaData.UnlimitedMaxLengthIndicator) {
5776 throw SQL.ParameterSizeRestrictionFailure(index);
5780 case SqlDbType.NVarChar:
5781 // Allow size==Int32.MaxValue/2 because of DeriveParameters
5782 if (size > 0 && size != Int32.MaxValue/2 && requestMetaData[index].MaxLength == SmiMetaData.UnlimitedMaxLengthIndicator) {
5783 throw SQL.ParameterSizeRestrictionFailure(index);
5787 case SqlDbType.Timestamp:
5788 // Size limiting for larger values will happen due to MaxLength
5789 if (size < SmiMetaData.DefaultTimestamp.MaxLength) {
5790 throw SQL.ParameterSizeRestrictionFailure(index);
5794 case SqlDbType.Variant:
5795 // Variant problems happen when Size is less than maximums for character and binary values
5796 // Size limiting for larger values will happen due to MaxLength
5797 // NOTE: assumes xml and udt types are handled in parameter value coercion
5798 // since server does not allow these types in a variant
5799 if (null != value) {
5800 MetaType mt = MetaType.GetMetaTypeFromValue(value);
5802 if ((mt.IsNCharType && size < SmiMetaData.MaxUnicodeCharacters) ||
5803 (mt.IsBinType && size < SmiMetaData.MaxBinaryLength) ||
5804 (mt.IsAnsiType && size < SmiMetaData.MaxANSICharacters)) {
5805 throw SQL.ParameterSizeRestrictionFailure(index);
5811 // Xml is an issue for non-SqlXml types
5812 if (null != value && ExtendedClrTypeCode.SqlXml != typeCode) {
5813 throw SQL.ParameterSizeRestrictionFailure(index);
5817 // NOTE: Char, NChar, Binary and UDT do not need restricting because they are always 8k or less,
5818 // so the metadata MaxLength will match the Size setting.
5825 if (innerConnection.IsKatmaiOrNewer) {
5826 ValueUtilsSmi.SetCompatibleValueV200(EventSink, requestExecutor, index, requestMetaData[index], value, typeCode, param.Offset, param.Size, peekAheadValues[index]);
5829 ValueUtilsSmi.SetCompatibleValue( EventSink, requestExecutor, index, requestMetaData[index], value, typeCode, param.Offset );
5835 return requestExecutor;
5838 private void WriteBeginExecuteEvent()
5840 if (SqlEventSource.Log.IsEnabled() && Connection != null)
5842 string commandText = CommandType == CommandType.StoredProcedure ? CommandText : string.Empty;
5843 SqlEventSource.Log.BeginExecute(GetHashCode(), Connection.DataSource, Connection.Database, commandText);
5848 /// Writes and end execute event in Event Source.
5850 /// <param name="success">True if SQL command finished successfully, otherwise false.</param>
5851 /// <param name="sqlExceptionNumber">Gets a number that identifies the type of error.</param>
5852 /// <param name="synchronous">True if SQL command was executed synchronously, otherwise false.</param>
5853 private void WriteEndExecuteEvent(bool success, int? sqlExceptionNumber, bool synchronous)
5855 if (SqlEventSource.Log.IsEnabled())
5857 // SqlEventSource.WriteEvent(int, int, int, int) is faster than provided overload SqlEventSource.WriteEvent(int, object[]).
5858 // that's why trying to fit several booleans in one integer value
5860 // success state is stored the first bit in compositeState 0x01
5861 int successFlag = success ? 1 : 0;
5863 // isSqlException is stored in the 2nd bit in compositeState 0x100
5864 int isSqlExceptionFlag = sqlExceptionNumber.HasValue ? 2 : 0;
5866 // synchronous state is stored in the second bit in compositeState 0x10
5867 int synchronousFlag = synchronous ? 4 : 0;
5869 int compositeState = successFlag | isSqlExceptionFlag | synchronousFlag;
5871 SqlEventSource.Log.EndExecute(GetHashCode(), compositeState, sqlExceptionNumber.GetValueOrDefault());
5876 internal void CompletePendingReadWithSuccess(bool resetForcePendingReadsToWait) {
5877 var stateObj = _stateObj;
5878 if (stateObj != null) {
5879 stateObj.CompletePendingReadWithSuccess(resetForcePendingReadsToWait);
5882 var tempCachedAsyncState = cachedAsyncState;
5883 if (tempCachedAsyncState != null) {
5884 var reader = tempCachedAsyncState.CachedAsyncReader;
5885 if (reader != null) {
5886 reader.CompletePendingReadWithSuccess(resetForcePendingReadsToWait);
5892 internal void CompletePendingReadWithFailure(int errorCode, bool resetForcePendingReadsToWait) {
5893 var stateObj = _stateObj;
5894 if (stateObj != null) {
5895 stateObj.CompletePendingReadWithFailure(errorCode, resetForcePendingReadsToWait);
5898 var tempCachedAsyncState = _cachedAsyncState;
5899 if (tempCachedAsyncState != null) {
5900 var reader = tempCachedAsyncState.CachedAsyncReader;
5901 if (reader != null) {
5902 reader.CompletePendingReadWithFailure(errorCode, resetForcePendingReadsToWait);