/// <summary>
/// Executes this batch and captures any server messages that are returned.
/// </summary>
/// <param name="conn">The connection to use to execute the batch</param>
/// <param name="cancellationToken">Token for cancelling the execution</param>
public async Task Execute(DbConnection conn, CancellationToken cancellationToken)
{
// Sanity check to make sure we haven't already run this batch
if (HasExecuted)
{
throw new InvalidOperationException("Batch has already executed.");
}
// Notify that we've started execution
if (BatchStart != null)
{
await BatchStart(this);
}
try
{
// Register the message listener to *this instance* of the batch
// Note: This is being done to associate messages with batches
ReliableSqlConnection sqlConn = conn as ReliableSqlConnection;
DbCommand command;
if (sqlConn != null)
{
// Register the message listener to *this instance* of the batch
// Note: This is being done to associate messages with batches
sqlConn.GetUnderlyingConnection().InfoMessage += StoreDbMessage;
command = sqlConn.GetUnderlyingConnection().CreateCommand();
// Add a handler for when the command completes
SqlCommand sqlCommand = (SqlCommand)command;
sqlCommand.StatementCompleted += StatementCompletedHandler;
}
else
{
command = conn.CreateCommand();
}
// Make sure we aren't using a ReliableCommad since we do not want automatic retry
Debug.Assert(!(command is ReliableSqlConnection.ReliableSqlCommand),
"ReliableSqlCommand command should not be used to execute queries");
// Create a command that we'll use for executing the query
using (command)
{
command.CommandText = BatchText;
command.CommandType = CommandType.Text;
command.CommandTimeout = 0;
executionStartTime = DateTime.Now;
// Execute the command to get back a reader
using (DbDataReader reader = await command.ExecuteReaderAsync(cancellationToken))
{
int resultSetOrdinal = 0;
do
{
// Skip this result set if there aren't any rows (ie, UPDATE/DELETE/etc queries)
if (!reader.HasRows && reader.FieldCount == 0)
{
continue;
}
// This resultset has results (ie, SELECT/etc queries)
ResultSet resultSet = new ResultSet(reader, resultSetOrdinal, Id, outputFileFactory);
resultSet.ResultCompletion += ResultSetCompletion;
// Add the result set to the results of the query
lock (resultSets)
{
resultSets.Add(resultSet);
resultSetOrdinal++;
}
// Read until we hit the end of the result set
await resultSet.ReadResultToEnd(cancellationToken).ConfigureAwait(false);
} while (await reader.NextResultAsync(cancellationToken));
// If there were no messages, for whatever reason (NO COUNT set, messages
// were emitted, records returned), output a "successful" message
if (resultMessages.Count == 0)
{
resultMessages.Add(new ResultMessage(SR.QueryServiceCompletedSuccessfully));
}
}
}
}
catch (DbException dbe)
{
HasError = true;
UnwrapDbException(dbe);
}
catch (TaskCanceledException)
{
resultMessages.Add(new ResultMessage(SR.QueryServiceQueryCancelled));
throw;
}
catch (Exception e)
{
HasError = true;
resultMessages.Add(new ResultMessage(SR.QueryServiceQueryFailed(e.Message)));
throw;
}
finally
{
// Remove the message event handler from the connection
ReliableSqlConnection sqlConn = conn as ReliableSqlConnection;
if (sqlConn != null)
{
sqlConn.GetUnderlyingConnection().InfoMessage -= StoreDbMessage;
}
// Mark that we have executed
HasExecuted = true;
executionEndTime = DateTime.Now;
// Fire an event to signify that the batch has completed
if (BatchCompletion != null)
{
await BatchCompletion(this);
}
}
}
private async Task ExecuteOnce(DbConnection conn, CancellationToken cancellationToken)
{
// Make sure we haven't cancelled yet
cancellationToken.ThrowIfCancellationRequested();
// Create a command that we'll use for executing the query
using (DbCommand dbCommand = CreateCommand(conn))
{
// Make sure that we cancel the command if the cancellation token is cancelled
cancellationToken.Register(() => dbCommand?.Cancel());
// Setup the command for executing the batch
dbCommand.CommandText = BatchText;
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandTimeout = 0;
executionStartTime = DateTime.Now;
List <DbColumn[]> columnSchemas = null;
if (getFullColumnSchema)
{
// Executing the same query twice with different command behavior causes the second
// execution to return no rows if there's a trailing comment with no newline after,
// so add a newline to the end of the query. See https://github.com/Microsoft/sqlopsstudio/issues/1424
dbCommand.CommandText += Environment.NewLine;
// Fetch schema info separately, since CommandBehavior.KeyInfo will include primary
// key columns in the result set, even if they weren't part of the select statement.
// Extra key columns get added to the end, so just correlate via Column Ordinal.
columnSchemas = new List <DbColumn[]>();
using (DbDataReader reader = await dbCommand.ExecuteReaderAsync(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly, cancellationToken))
{
if (reader != null && reader.CanGetColumnSchema())
{
do
{
columnSchemas.Add(reader.GetColumnSchema().ToArray());
} while (await reader.NextResultAsync(cancellationToken));
}
}
}
// Execute the command to get back a reader
using (DbDataReader reader = await dbCommand.ExecuteReaderAsync(cancellationToken))
{
do
{
// Verify that the cancellation token hasn't been canceled
cancellationToken.ThrowIfCancellationRequested();
// Skip this result set if there aren't any rows (i.e. UPDATE/DELETE/etc queries)
if (!reader.HasRows && reader.FieldCount == 0)
{
continue;
}
// This resultset has results (i.e. SELECT/etc queries)
ResultSet resultSet = new ResultSet(resultSets.Count, Id, outputFileFactory);
resultSet.ResultCompletion += ResultSetCompletion;
// Add the result set to the results of the query
lock (resultSets)
{
resultSets.Add(resultSet);
}
// Read until we hit the end of the result set
await resultSet.ReadResultToEnd(reader, cancellationToken);
} while (await reader.NextResultAsync(cancellationToken));
// If there were no messages, for whatever reason (NO COUNT set, messages
// were emitted, records returned), output a "successful" message
if (!messagesSent)
{
await SendMessage(SR.QueryServiceCompletedSuccessfully, false);
}
}
if (columnSchemas != null)
{
ExtendResultMetadata(columnSchemas, resultSets);
}
}
}
private async Task ExecuteOnce(DbConnection conn, CancellationToken cancellationToken)
{
// Make sure we haven't cancelled yet
cancellationToken.ThrowIfCancellationRequested();
// Register the message listener to *this instance* of the batch
// Note: This is being done to associate messages with batches
ReliableSqlConnection sqlConn = conn as ReliableSqlConnection;
DbCommand dbCommand;
if (sqlConn != null)
{
// Register the message listener to *this instance* of the batch
// Note: This is being done to associate messages with batches
sqlConn.GetUnderlyingConnection().InfoMessage += ServerMessageHandler;
dbCommand = sqlConn.GetUnderlyingConnection().CreateCommand();
// Add a handler for when the command completes
SqlCommand sqlCommand = (SqlCommand)dbCommand;
sqlCommand.StatementCompleted += StatementCompletedHandler;
}
else
{
dbCommand = conn.CreateCommand();
}
// Make sure we aren't using a ReliableCommad since we do not want automatic retry
Debug.Assert(!(dbCommand is ReliableSqlConnection.ReliableSqlCommand),
"ReliableSqlCommand command should not be used to execute queries");
// Create a command that we'll use for executing the query
using (dbCommand)
{
// Make sure that we cancel the command if the cancellation token is cancelled
cancellationToken.Register(() => dbCommand?.Cancel());
// Setup the command for executing the batch
dbCommand.CommandText = BatchText;
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandTimeout = 0;
executionStartTime = DateTime.Now;
// Execute the command to get back a reader
using (DbDataReader reader = await dbCommand.ExecuteReaderAsync(cancellationToken))
{
int resultSetOrdinal = 0;
do
{
// Verify that the cancellation token hasn't benn cancelled
cancellationToken.ThrowIfCancellationRequested();
// Skip this result set if there aren't any rows (ie, UPDATE/DELETE/etc queries)
if (!reader.HasRows && reader.FieldCount == 0)
{
continue;
}
// This resultset has results (ie, SELECT/etc queries)
ResultSet resultSet = new ResultSet(resultSetOrdinal, Id, outputFileFactory);
resultSet.ResultCompletion += ResultSetCompletion;
// Add the result set to the results of the query
lock (resultSets)
{
resultSets.Add(resultSet);
resultSetOrdinal++;
}
// Read until we hit the end of the result set
await resultSet.ReadResultToEnd(reader, cancellationToken);
} while (await reader.NextResultAsync(cancellationToken));
// If there were no messages, for whatever reason (NO COUNT set, messages
// were emitted, records returned), output a "successful" message
if (!messagesSent)
{
await SendMessage(SR.QueryServiceCompletedSuccessfully, false);
}
}
}
}
