/// <summary> /// Constructor for a query /// </summary> /// <param name="queryText">The text of the query to execute</param> /// <param name="connection">The information of the connection to use to execute the query</param> /// <param name="settings">Settings for how to execute the query, from the user</param> /// <param name="outputFactory">Factory for creating output files</param> public Query(string queryText, ConnectionInfo connection, QueryExecutionSettings settings, IFileStreamFactory outputFactory) { // Sanity check for input Validate.IsNotNullOrEmptyString(nameof(queryText), queryText); Validate.IsNotNull(nameof(connection), connection); Validate.IsNotNull(nameof(settings), settings); Validate.IsNotNull(nameof(outputFactory), outputFactory); // Initialize the internal state QueryText = queryText; editorConnection = connection; cancellationSource = new CancellationTokenSource(); // Process the query into batches ParseResult parseResult = Parser.Parse(queryText, new ParseOptions { BatchSeparator = settings.BatchSeparator }); // NOTE: We only want to process batches that have statements (ie, ignore comments and empty lines) var batchSelection = parseResult.Script.Batches .Where(batch => batch.Statements.Count > 0) .Select((batch, index) => new Batch(batch.Sql, new SelectionData( batch.StartLocation.LineNumber - 1, batch.StartLocation.ColumnNumber - 1, batch.EndLocation.LineNumber - 1, batch.EndLocation.ColumnNumber - 1), index, outputFactory)); Batches = batchSelection.ToArray(); }
private static string VerifyReadWrite <T>(int valueLength, T value, Func <ServiceBufferFileStreamWriter, T, int> writeFunc, Func <ServiceBufferFileStreamReader, FileStreamReadResult> readFunc, QueryExecutionSettings overrideSettings = null) { // Setup: Create a mock file stream byte[] storage = new byte[8192]; overrideSettings = overrideSettings ?? new QueryExecutionSettings(); // If: // ... I write a type T to the writer using (ServiceBufferFileStreamWriter writer = new ServiceBufferFileStreamWriter(new MemoryStream(storage), overrideSettings)) { int writtenBytes = writeFunc(writer, value); Assert.Equal(valueLength, writtenBytes); } // ... And read the type T back FileStreamReadResult outValue; using (ServiceBufferFileStreamReader reader = new ServiceBufferFileStreamReader(new MemoryStream(storage), overrideSettings)) { outValue = readFunc(reader); } // Then: Assert.Equal(value, outValue.Value.RawObject); Assert.Equal(valueLength, outValue.TotalLength); Assert.NotNull(outValue.Value); return(outValue.Value.DisplayValue); }
public async Task CancelQueryBeforeExecutionStartedTest() { // Setup query settings QueryExecutionSettings querySettings = new QueryExecutionSettings { ExecutionPlanOptions = new ExecutionPlanOptions { IncludeActualExecutionPlanXml = false, IncludeEstimatedExecutionPlanXml = true } }; // Create query with a failure callback function ConnectionInfo ci = Common.CreateTestConnectionInfo(null, false, false); ConnectionService.Instance.OwnerToConnectionMap[ci.OwnerUri] = ci; Query query = new Query(Constants.StandardQuery, ci, querySettings, MemoryFileSystem.GetFileStreamFactory()); string errorMessage = null; Query.QueryAsyncErrorEventHandler failureCallback = async(q, e) => { errorMessage = "Error Occured"; }; query.QueryFailed += failureCallback; query.Cancel(); query.Execute(); await query.ExecutionTask; // Validate that query has not been executed but cancelled and query failed called function was called Assert.Equal(true, query.HasCancelled); Assert.Equal(false, query.HasExecuted); Assert.Equal("Error Occured", errorMessage); }
/// <summary> /// Constructs a new writer /// </summary> /// <param name="stream">The file wrapper to use as the underlying file stream</param> /// <param name="settings">The query execution settings</param> public ServiceBufferFileStreamWriter(Stream stream, QueryExecutionSettings settings) { Validate.IsNotNull(nameof(stream), stream); Validate.IsNotNull(nameof(settings), settings); // open file for reading/writing if (!stream.CanWrite || !stream.CanSeek) { throw new InvalidOperationException("Stream must be writable and seekable."); } fileStream = stream; executionSettings = settings; // create internal buffer byteBuffer = new byte[DefaultBufferLength]; // Create internal buffers for blockcopy of contents to byte array // Note: We create them now to avoid the overhead of creating a new array for every write call shortBuffer = new short[1]; intBuffer = new int[1]; longBuffer = new long[1]; charBuffer = new char[1]; doubleBuffer = new double[1]; floatBuffer = new float[1]; // Define what methods to use to write a type to the file writeMethods = new Dictionary <Type, Func <object, int> > { { typeof(string), val => WriteString((string)val) }, { typeof(short), val => WriteInt16((short)val) }, { typeof(int), val => WriteInt32((int)val) }, { typeof(long), val => WriteInt64((long)val) }, { typeof(byte), val => WriteByte((byte)val) }, { typeof(char), val => WriteChar((char)val) }, { typeof(bool), val => WriteBoolean((bool)val) }, { typeof(double), val => WriteDouble((double)val) }, { typeof(float), val => WriteSingle((float)val) }, { typeof(decimal), val => WriteDecimal((decimal)val) }, { typeof(DateTime), val => WriteDateTime((DateTime)val) }, { typeof(DateTimeOffset), val => WriteDateTimeOffset((DateTimeOffset)val) }, { typeof(TimeSpan), val => WriteTimeSpan((TimeSpan)val) }, { typeof(byte[]), val => WriteBytes((byte[])val) }, { typeof(Guid), val => WriteGuid((Guid)val) }, { typeof(SqlString), val => WriteNullable((SqlString)val, obj => WriteString((string)obj)) }, { typeof(SqlInt16), val => WriteNullable((SqlInt16)val, obj => WriteInt16((short)obj)) }, { typeof(SqlInt32), val => WriteNullable((SqlInt32)val, obj => WriteInt32((int)obj)) }, { typeof(SqlInt64), val => WriteNullable((SqlInt64)val, obj => WriteInt64((long)obj)) }, { typeof(SqlByte), val => WriteNullable((SqlByte)val, obj => WriteByte((byte)obj)) }, { typeof(SqlBoolean), val => WriteNullable((SqlBoolean)val, obj => WriteBoolean((bool)obj)) }, { typeof(SqlDouble), val => WriteNullable((SqlDouble)val, obj => WriteDouble((double)obj)) }, { typeof(SqlSingle), val => WriteNullable((SqlSingle)val, obj => WriteSingle((float)obj)) }, { typeof(SqlDecimal), val => WriteNullable((SqlDecimal)val, obj => WriteSqlDecimal((SqlDecimal)obj)) }, { typeof(SqlDateTime), val => WriteNullable((SqlDateTime)val, obj => WriteDateTime((DateTime)obj)) }, { typeof(SqlBytes), val => WriteNullable((SqlBytes)val, obj => WriteBytes((byte[])obj)) }, { typeof(SqlBinary), val => WriteNullable((SqlBinary)val, obj => WriteBytes((byte[])obj)) }, { typeof(SqlGuid), val => WriteNullable((SqlGuid)val, obj => WriteGuid((Guid)obj)) }, { typeof(SqlMoney), val => WriteNullable((SqlMoney)val, obj => WriteMoney((SqlMoney)obj)) } }; }
/// <summary> /// Constructor for a query /// </summary> /// <param name="queryText">The text of the query to execute</param> /// <param name="connection">The information of the connection to use to execute the query</param> /// <param name="settings">Settings for how to execute the query, from the user</param> /// <param name="outputFactory">Factory for creating output files</param> public Query( string queryText, ConnectionInfo connection, QueryExecutionSettings settings, IFileStreamFactory outputFactory, bool getFullColumnSchema = false, bool applyExecutionSettings = false) { // Sanity check for input Validate.IsNotNull(nameof(queryText), queryText); Validate.IsNotNull(nameof(connection), connection); Validate.IsNotNull(nameof(settings), settings); Validate.IsNotNull(nameof(outputFactory), outputFactory); // Initialize the internal state QueryText = queryText; editorConnection = connection; cancellationSource = new CancellationTokenSource(); // Process the query into batches BatchParserWrapper parser = new BatchParserWrapper(); ExecutionEngineConditions conditions = null; if (settings.IsSqlCmdMode) { conditions = new ExecutionEngineConditions() { IsSqlCmd = settings.IsSqlCmdMode }; } List <BatchDefinition> parserResult = parser.GetBatches(queryText, conditions); var batchSelection = parserResult .Select((batchDefinition, index) => new Batch(batchDefinition.BatchText, new SelectionData( batchDefinition.StartLine - 1, batchDefinition.StartColumn - 1, batchDefinition.EndLine - 1, batchDefinition.EndColumn - 1), index, outputFactory, batchDefinition.SqlCmdCommand, batchDefinition.BatchExecutionCount, getFullColumnSchema)); Batches = batchSelection.ToArray(); // Create our batch lists BeforeBatches = new List <Batch>(); AfterBatches = new List <Batch>(); if (applyExecutionSettings) { ApplyExecutionSettings(connection, settings, outputFactory); } }
public static Query CreateAndExecuteQuery(string queryText, ConnectionInfo connectionInfo, IFileStreamFactory fileStreamFactory, bool IsSqlCmd = false) { var settings = new QueryExecutionSettings() { IsSqlCmdMode = IsSqlCmd }; Query query = new Query(queryText, connectionInfo, settings, fileStreamFactory); query.Execute(); query.ExecutionTask.Wait(); return(query); }
/// <summary> /// Constructs a new ServiceBufferFileStreamReader and initializes its state /// </summary> /// <param name="stream">The filestream to read from</param> /// <param name="settings">The query execution settings</param> public ServiceBufferFileStreamReader(Stream stream, QueryExecutionSettings settings) { Validate.IsNotNull(nameof(stream), stream); Validate.IsNotNull(nameof(settings), settings); // Open file for reading/writing if (!stream.CanRead || !stream.CanSeek) { throw new InvalidOperationException("Stream must be readable and seekable"); } fileStream = stream; executionSettings = settings; // Create internal buffer buffer = new byte[DefaultBufferSize]; // Create the methods that will be used to read back readMethods = new Dictionary <Type, ReadMethod> { { typeof(string), (o, id, col) => ReadString(o, id) }, { typeof(short), (o, id, col) => ReadInt16(o, id) }, { typeof(int), (o, id, col) => ReadInt32(o, id) }, { typeof(long), (o, id, col) => ReadInt64(o, id) }, { typeof(byte), (o, id, col) => ReadByte(o, id) }, { typeof(char), (o, id, col) => ReadChar(o, id) }, { typeof(bool), (o, id, col) => ReadBoolean(o, id) }, { typeof(double), (o, id, col) => ReadDouble(o, id) }, { typeof(float), (o, id, col) => ReadSingle(o, id) }, { typeof(decimal), (o, id, col) => ReadDecimal(o, id) }, { typeof(DateTime), ReadDateTime }, { typeof(DateTimeOffset), (o, id, col) => ReadDateTimeOffset(o, id) }, { typeof(TimeSpan), (o, id, col) => ReadTimeSpan(o, id) }, { typeof(byte[]), (o, id, col) => ReadBytes(o, id) }, { typeof(Guid), (o, id, col) => ReadGuid(o, id) }, { typeof(SqlString), (o, id, col) => ReadString(o, id) }, { typeof(SqlInt16), (o, id, col) => ReadInt16(o, id) }, { typeof(SqlInt32), (o, id, col) => ReadInt32(o, id) }, { typeof(SqlInt64), (o, id, col) => ReadInt64(o, id) }, { typeof(SqlByte), (o, id, col) => ReadByte(o, id) }, { typeof(SqlBoolean), (o, id, col) => ReadBoolean(o, id) }, { typeof(SqlDouble), (o, id, col) => ReadDouble(o, id) }, { typeof(SqlSingle), (o, id, col) => ReadSingle(o, id) }, { typeof(SqlDecimal), (o, id, col) => ReadSqlDecimal(o, id) }, { typeof(SqlDateTime), ReadDateTime }, { typeof(SqlBytes), (o, id, col) => ReadBytes(o, id) }, { typeof(SqlBinary), (o, id, col) => ReadBytes(o, id) }, { typeof(SqlGuid), (o, id, col) => ReadGuid(o, id) }, { typeof(SqlMoney), (o, id, col) => ReadMoney(o, id) }, }; }
/// <summary> /// Constructor for a query /// </summary> /// <param name="queryText">The text of the query to execute</param> /// <param name="connection">The information of the connection to use to execute the query</param> /// <param name="settings">Settings for how to execute the query, from the user</param> /// <param name="outputFactory">Factory for creating output files</param> public Query(string queryText, ConnectionInfo connection, QueryExecutionSettings settings, IFileStreamFactory outputFactory, bool getFullColumnSchema = false) { // Sanity check for input Validate.IsNotNull(nameof(queryText), queryText); Validate.IsNotNull(nameof(connection), connection); Validate.IsNotNull(nameof(settings), settings); Validate.IsNotNull(nameof(outputFactory), outputFactory); // Initialize the internal state QueryText = queryText; editorConnection = connection; cancellationSource = new CancellationTokenSource(); // Process the query into batches BatchParserWrapper parser = new BatchParserWrapper(); List <BatchDefinition> parserResult = parser.GetBatches(queryText); var batchSelection = parserResult .Select((batchDefinition, index) => new Batch(batchDefinition.BatchText, new SelectionData( batchDefinition.StartLine - 1, batchDefinition.StartColumn - 1, batchDefinition.EndLine - 1, batchDefinition.EndColumn - 1), index, outputFactory, batchDefinition.BatchExecutionCount, getFullColumnSchema)); Batches = batchSelection.ToArray(); // Create our batch lists BeforeBatches = new List <Batch>(); AfterBatches = new List <Batch>(); if (DoesSupportExecutionPlan(connection)) { // Checking settings for execution plan options if (settings.ExecutionPlanOptions.IncludeEstimatedExecutionPlanXml) { // Enable set showplan xml AddBatch(string.Format(SetShowPlanXml, On), BeforeBatches, outputFactory); AddBatch(string.Format(SetShowPlanXml, Off), AfterBatches, outputFactory); } else if (settings.ExecutionPlanOptions.IncludeActualExecutionPlanXml) { AddBatch(string.Format(SetStatisticsXml, On), BeforeBatches, outputFactory); AddBatch(string.Format(SetStatisticsXml, Off), AfterBatches, outputFactory); } } }
public void When_query_endpoint_is_not_found_then_should_throw() { using (var client = _fixture.CreateHttpClient()) { var settings = new QueryExecutionSettings( _fixture.MessageExecutionSettings.Vendor, _fixture.MessageExecutionSettings.ModelToExceptionConverter, path: "notfoundpath"); Func <Task <TestQueryResponse> > act = () => client.ExecuteQuery <TestQuery, TestQueryResponse>(new TestQuery(), Guid.NewGuid(), settings); act.ShouldThrow <InvalidOperationException>(); } }
public static Query GetBasicExecutedQuery(QueryExecutionSettings querySettings) { ConnectionInfo ci = CreateTestConnectionInfo(StandardTestDataSet, false); // Query won't be able to request a new query DbConnection unless the ConnectionService has a // ConnectionInfo with the same URI as the query, so we will manually set it ConnectionService.Instance.OwnerToConnectionMap[ci.OwnerUri] = ci; Query query = new Query(StandardQuery, ci, querySettings, GetFileStreamFactory(new Dictionary <string, byte[]>())); query.Execute(); query.ExecutionTask.Wait(); return(query); }
private async Task <Query> CreateAndActivateNewQuery(ExecuteRequestParamsBase executeParams, Func <Task> successAction, Func <string, Task> failureAction) { try { // Attempt to get the connection for the editor ConnectionInfo connectionInfo; if (!ConnectionService.TryFindConnection(executeParams.OwnerUri, out connectionInfo)) { await failureAction(SR.QueryServiceQueryInvalidOwnerUri); return(null); } // Attempt to clean out any old query on the owner URI Query oldQuery; if (ActiveQueries.TryGetValue(executeParams.OwnerUri, out oldQuery) && oldQuery.HasExecuted) { oldQuery.Dispose(); ActiveQueries.TryRemove(executeParams.OwnerUri, out oldQuery); } // Retrieve the current settings for executing the query with QueryExecutionSettings querySettings = Settings.QueryExecutionSettings; // Apply execution parameter settings querySettings.ExecutionPlanOptions = executeParams.ExecutionPlanOptions; // If we can't add the query now, it's assumed the query is in progress Query newQuery = new Query(GetSqlText(executeParams), connectionInfo, querySettings, BufferFileFactory); if (!ActiveQueries.TryAdd(executeParams.OwnerUri, newQuery)) { await failureAction(SR.QueryServiceQueryInProgress); newQuery.Dispose(); return(null); } // Successfully created query await successAction(); return(newQuery); } catch (Exception e) { await failureAction(e.Message); return(null); } }
private Query CreateQuery(ExecuteRequestParamsBase executeParams, ConnectionInfo connInfo) { // Attempt to get the connection for the editor ConnectionInfo connectionInfo; if (connInfo != null) { connectionInfo = connInfo; } else if (!ConnectionService.TryFindConnection(executeParams.OwnerUri, out connectionInfo)) { throw new ArgumentOutOfRangeException(nameof(executeParams.OwnerUri), SR.QueryServiceQueryInvalidOwnerUri); } // Attempt to clean out any old query on the owner URI Query oldQuery; // DevNote: // if any oldQuery exists on the executeParams.OwnerUri but it has not yet executed, // then shouldn't we cancel and clean out that query since we are about to create a new query object on the current OwnerUri. // if (ActiveQueries.TryGetValue(executeParams.OwnerUri, out oldQuery) && (oldQuery.HasExecuted || oldQuery.HasCancelled)) { oldQuery.Dispose(); ActiveQueries.TryRemove(executeParams.OwnerUri, out oldQuery); } // Retrieve the current settings for executing the query with QueryExecutionSettings settings = Settings.QueryExecutionSettings; // Apply execution parameter settings settings.ExecutionPlanOptions = executeParams.ExecutionPlanOptions; // If we can't add the query now, it's assumed the query is in progress Query newQuery = new Query(GetSqlText(executeParams), connectionInfo, settings, BufferFileFactory, executeParams.GetFullColumnSchema); if (!ActiveQueries.TryAdd(executeParams.OwnerUri, newQuery)) { newQuery.Dispose(); throw new InvalidOperationException(SR.QueryServiceQueryInProgress); } Logger.Write(TraceEventType.Information, $"Query object for URI:'{executeParams.OwnerUri}' created"); return(newQuery); }
[InlineData(2)] // Invalid batch, too high public async Task QueryExecutionPlanInvalidParamsTest(int batchIndex) { // Setup query settings QueryExecutionSettings querySettings = new QueryExecutionSettings { ExecutionPlanOptions = new ExecutionPlanOptions { IncludeActualExecutionPlanXml = false, IncludeEstimatedExecutionPlanXml = true } }; // If I have an executed query Query q = Common.GetBasicExecutedQuery(querySettings); // ... And I ask for a subset with an invalid result set index // Then: // ... It should throw an exception await Assert.ThrowsAsync <ArgumentOutOfRangeException>(() => q.GetExecutionPlan(batchIndex, 0)); }
private Query CreateQuery(ExecuteRequestParamsBase executeParams, ConnectionInfo connInfo) { // Attempt to get the connection for the editor ConnectionInfo connectionInfo; if (connInfo != null) { connectionInfo = connInfo; } else if (!ConnectionService.TryFindConnection(executeParams.OwnerUri, out connectionInfo)) { throw new ArgumentOutOfRangeException(nameof(executeParams.OwnerUri), SR.QueryServiceQueryInvalidOwnerUri); } // Attempt to clean out any old query on the owner URI Query oldQuery; if (ActiveQueries.TryGetValue(executeParams.OwnerUri, out oldQuery) && oldQuery.HasExecuted) { oldQuery.Dispose(); ActiveQueries.TryRemove(executeParams.OwnerUri, out oldQuery); } // Retrieve the current settings for executing the query with QueryExecutionSettings settings = Settings.QueryExecutionSettings; // Apply execution parameter settings settings.ExecutionPlanOptions = executeParams.ExecutionPlanOptions; // If we can't add the query now, it's assumed the query is in progress Query newQuery = new Query(GetSqlText(executeParams), connectionInfo, settings, BufferFileFactory, executeParams.GetFullColumnSchema); if (!ActiveQueries.TryAdd(executeParams.OwnerUri, newQuery)) { newQuery.Dispose(); throw new InvalidOperationException(SR.QueryServiceQueryInProgress); } return(newQuery); }
public ContrivedMiddleware() { _queryExecutionSettings = new QueryExecutionSettings("vendor"); }
public QuerySettingsHelper(QueryExecutionSettings settings) { this.settings = settings; }
private async Task <Query> CreateAndActivateNewQuery(QueryExecuteParams executeParams, RequestContext <QueryExecuteResult> requestContext) { try { // Attempt to get the connection for the editor ConnectionInfo connectionInfo; if (!ConnectionService.TryFindConnection(executeParams.OwnerUri, out connectionInfo)) { await requestContext.SendError(SR.QueryServiceQueryInvalidOwnerUri); return(null); } // Attempt to clean out any old query on the owner URI Query oldQuery; if (ActiveQueries.TryGetValue(executeParams.OwnerUri, out oldQuery) && oldQuery.HasExecuted) { oldQuery.Dispose(); ActiveQueries.TryRemove(executeParams.OwnerUri, out oldQuery); } // Retrieve the current settings for executing the query with QueryExecutionSettings settings = WorkspaceService.CurrentSettings.QueryExecutionSettings; // Get query text from the workspace. ScriptFile queryFile = WorkspaceService.Workspace.GetFile(executeParams.OwnerUri); string queryText; if (executeParams.QuerySelection != null) { string[] queryTextArray = queryFile.GetLinesInRange( new BufferRange( new BufferPosition( executeParams.QuerySelection.StartLine + 1, executeParams.QuerySelection.StartColumn + 1 ), new BufferPosition( executeParams.QuerySelection.EndLine + 1, executeParams.QuerySelection.EndColumn + 1 ) ) ); queryText = queryTextArray.Aggregate((a, b) => a + '\r' + '\n' + b); } else { queryText = queryFile.Contents; } // If we can't add the query now, it's assumed the query is in progress Query newQuery = new Query(queryText, connectionInfo, settings, BufferFileFactory); if (!ActiveQueries.TryAdd(executeParams.OwnerUri, newQuery)) { await requestContext.SendError(SR.QueryServiceQueryInProgress); newQuery.Dispose(); return(null); } return(newQuery); } catch (Exception e) { await requestContext.SendError(e.Message); return(null); } // Any other exceptions will fall through here and be collected at the end }
/// <summary> /// Update the current settings with the new settings /// </summary> /// <param name="newSettings">The new settings</param> public void Update(QueryExecutionSettings newSettings) { BatchSeparator = newSettings.BatchSeparator; }
private void ApplyExecutionSettings( ConnectionInfo connection, QueryExecutionSettings settings, IFileStreamFactory outputFactory) { outputFactory.QueryExecutionSettings = settings; QuerySettingsHelper helper = new QuerySettingsHelper(settings); // set query execution plan options if (DoesSupportExecutionPlan(connection)) { // Checking settings for execution plan options if (settings.ExecutionPlanOptions.IncludeEstimatedExecutionPlanXml) { // Enable set showplan xml AddBatch(string.Format(SetShowPlanXml, On), BeforeBatches, outputFactory); AddBatch(string.Format(SetShowPlanXml, Off), AfterBatches, outputFactory); } else if (settings.ExecutionPlanOptions.IncludeActualExecutionPlanXml) { AddBatch(string.Format(SetStatisticsXml, On), BeforeBatches, outputFactory); AddBatch(string.Format(SetStatisticsXml, Off), AfterBatches, outputFactory); } } StringBuilder builderBefore = new StringBuilder(512); StringBuilder builderAfter = new StringBuilder(512); if (!connection.IsSqlDW) { // "set noexec off" should be the very first command, cause everything after // corresponding "set noexec on" is not executed until "set noexec off" // is encounted if (!settings.NoExec) { builderBefore.AppendFormat("{0} ", helper.SetNoExecString); } if (settings.StatisticsIO) { builderBefore.AppendFormat("{0} ", helper.GetSetStatisticsIOString(true)); builderAfter.AppendFormat("{0} ", helper.GetSetStatisticsIOString(false)); } if (settings.StatisticsTime) { builderBefore.AppendFormat("{0} ", helper.GetSetStatisticsTimeString(true)); builderAfter.AppendFormat("{0} ", helper.GetSetStatisticsTimeString(false)); } } if (settings.ParseOnly) { builderBefore.AppendFormat("{0} ", helper.GetSetParseOnlyString(true)); builderAfter.AppendFormat("{0} ", helper.GetSetParseOnlyString(false)); } // append first part of exec options builderBefore.AppendFormat("{0} {1} {2}", helper.SetRowCountString, helper.SetTextSizeString, helper.SetNoCountString); if (!connection.IsSqlDW) { // append second part of exec options builderBefore.AppendFormat(" {0} {1} {2} {3} {4} {5} {6}", helper.SetConcatenationNullString, helper.SetArithAbortString, helper.SetLockTimeoutString, helper.SetQueryGovernorCostString, helper.SetDeadlockPriorityString, helper.SetTransactionIsolationLevelString, // We treat XACT_ABORT special in that we don't add anything if the option // isn't checked. This is because we don't want to be overwriting the server // if it has a default of ON since that's something people would specifically // set and having a client change it could be dangerous (the reverse is much // less risky) // The full fix would probably be to make the options tri-state instead of // just on/off, where the default is to use the servers default. Until that // happens though this is the best solution we came up with. See TFS#7937925 // Note that users can always specifically add SET XACT_ABORT OFF to their // queries if they do truly want to set it off. We just don't want to // do it silently (since the default is going to be off) settings.XactAbortOn ? helper.SetXactAbortString : string.Empty); // append Ansi options builderBefore.AppendFormat(" {0} {1} {2} {3} {4} {5} {6}", helper.SetAnsiNullsString, helper.SetAnsiNullDefaultString, helper.SetAnsiPaddingString, helper.SetAnsiWarningsString, helper.SetCursorCloseOnCommitString, helper.SetImplicitTransactionString, helper.SetQuotedIdentifierString); // "set noexec on" should be the very last command, cause everything after it is not // being executed unitl "set noexec off" is encounered if (settings.NoExec) { builderBefore.AppendFormat("{0} ", helper.SetNoExecString); } } // add connection option statements before query execution if (builderBefore.Length > 0) { AddBatch(builderBefore.ToString(), BeforeBatches, outputFactory); } // add connection option statements after query execution if (builderAfter.Length > 0) { AddBatch(builderAfter.ToString(), AfterBatches, outputFactory); } }