public void VerifyRunSqlCmd()
{
using (ExecutionEngine executionEngine = new ExecutionEngine())
{
const string sqlCmdQuery = @"
:setvar __var1 1
:setvar __var2 2
:setvar __IsSqlCmdEnabled " + "\"True\"" + @"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
BEGIN
PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
SET NOEXEC ON;
END
GO
select $(__var1) + $(__var2) as col
GO";
var liveConnection = LiveConnectionHelper.InitLiveConnectionInfo("master");
var condition = new ExecutionEngineConditions()
{
IsSqlCmd = true
};
using (SqlConnection sqlConn = ConnectionService.OpenSqlConnection(liveConnection.ConnectionInfo))
using (TestExecutor testExecutor = new TestExecutor(sqlCmdQuery, sqlConn, condition))
{
testExecutor.Run();
Assert.True(testExecutor.ResultCountQueue.Count >= 1, $"Unexpected number of ResultCount items - expected 0 but got {testExecutor.ResultCountQueue.Count}");
Assert.True(testExecutor.ErrorMessageQueue.Count == 0, $"Unexpected error messages from test executor : {string.Join(Environment.NewLine, testExecutor.ErrorMessageQueue)}");
}
}
}
public void VerifyRunSqlCmd()
{
using (ExecutionEngine executionEngine = new ExecutionEngine())
{
const string sqlCmdQuery = @"
:setvar __var1 1
:setvar __var2 2
:setvar __IsSqlCmdEnabled " + "\"True\"" + @"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
BEGIN
PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
SET NOEXEC ON;
END
GO
select $(__var1) + $(__var2) as col
GO";
using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
{
con.Open();
var condition = new ExecutionEngineConditions()
{
IsSqlCmd = true
};
TestExecutor testExecutor = new TestExecutor(sqlCmdQuery, con, condition);
testExecutor.Run();
Assert.True(testExecutor.ResultCountQueue.Count >= 1);
Assert.True(testExecutor.ErrorMessageQueue.Count == 0);
}
}
}
public void ExecutionEngineTest_SQLCmds()
{
string[] sqlStatements =
{
"select $(INVALIDVAR) from sysobjects",
":help",
"exit",
"quit",
"!! dir",
"ed",
"reset",
":list",
":listvar",
":serverlist",
":on error ignore",
":connect hypothermia -t 300 -U foo -P bar",
":out $(SystemDrive)\\test.txt",
":r $(SystemDrive)\\test.txt",
":error STDOUT",
":perftrace STDOUT",
"exit (Select count(*) from sysobjects)"
};
ExecutionEngineConditions conditions = new ExecutionEngineConditions();
foreach (string stmt in sqlStatements)
{
TestExecutor executor = new TestExecutor(stmt, connection, conditions);
executor.Run();
//Assert.AreEqual(ScriptExecutionResult.Failure, executor.ExecutionResult);
//Assert.IsTrue(executor.ResultCountQueue.Count == 0);
}
}
public void VerifyOnErrorSqlCmd()
{
using (ExecutionEngine executionEngine = new ExecutionEngine())
{
var liveConnection = LiveConnectionHelper.InitLiveConnectionInfo("master");
string serverName = liveConnection.ConnectionInfo.ConnectionDetails.ServerName;
string sqlCmdQuery = $@"
:on error ignore
GO
select * from sys.databases_wrong where name = 'master'
GO
select* from sys.databases where name = 'master'
GO
:on error exit
GO
select* from sys.databases_wrong where name = 'master'
GO
select* from sys.databases where name = 'master'
GO";
var condition = new ExecutionEngineConditions()
{
IsSqlCmd = true
};
using (SqlConnection sqlConn = ConnectionService.OpenSqlConnection(liveConnection.ConnectionInfo))
using (TestExecutor testExecutor = new TestExecutor(sqlCmdQuery, sqlConn, condition))
{
testExecutor.Run();
Assert.True(testExecutor.ResultCountQueue.Count == 1, $"Unexpected number of ResultCount items - expected only 1 since the later should not be executed but got {testExecutor.ResultCountQueue.Count}");
Assert.True(testExecutor.ErrorMessageQueue.Count == 2, $"Unexpected number error messages from test executor expected 2, actual : {string.Join(Environment.NewLine, testExecutor.ErrorMessageQueue)}");
}
}
}
public async Task ExecutionEngineTest_MultiThreading_WithCancel()
{
string[] sqlStatement = { "waitfor delay '0:0:10'",
"waitfor delay '0:0:10'",
"waitfor delay '0:0:10'" };
ExecutionEngineConditions conditions = new ExecutionEngineConditions();
conditions.IsTransactionWrapped = true;
conditions.IsParseOnly = false;
conditions.IsHaltOnError = false;
SqlConnection connection2 = SetUpConnection("test4");
SqlConnection connection3 = SetUpConnection("test5");
TestExecutor executor1 = new TestExecutor(sqlStatement[0], connection, conditions, true);
executor1.CancelTimeOut = 2000;
TestExecutor executor2 = new TestExecutor(sqlStatement[1], connection2, conditions, true);
executor1.CancelTimeOut = 2500;
TestExecutor executor3 = new TestExecutor(sqlStatement[2], connection3, conditions, true);
executor1.CancelTimeOut = 3000;
Thread t1 = new Thread(new ThreadStart(executor1.Run));
Thread t2 = new Thread(new ThreadStart(executor2.Run));
Thread t3 = new Thread(new ThreadStart(executor3.Run));
t1.Name = "Executor1";
t1.Start();
t2.Name = "Executor2";
t2.Start();
t3.Name = "Executor3";
t3.Start();
while ((t1.ThreadState != ThreadState.Stopped) &&
(t2.ThreadState != ThreadState.Stopped) &&
(t3.ThreadState != ThreadState.Stopped))
{
Thread.Sleep(1000);
}
Assert.True(!executor1.ScriptExecuteThread.IsAlive);
Assert.True(!executor2.ScriptExecuteThread.IsAlive);
Assert.True(!executor3.ScriptExecuteThread.IsAlive);
Assert.True(executor1.CancelEventFired);
Assert.True(executor2.CancelEventFired);
Assert.True(executor3.CancelEventFired);
CloseConnection(connection2);
CloseConnection(connection3);
await SqlTestDb.DropDatabase(connection2.Database);
await SqlTestDb.DropDatabase(connection3.Database);
}
/// <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);
}
}
private void RunDeploymentScriptFromReferences(FileInfo dacpacPackage, string targetDatabaseName, bool isPreDeploy)
{
using var model = new TSqlModel(dacpacPackage.FullName, DacSchemaModelStorageType.Memory);
var references = model.GetReferencedDacPackages();
if (!references.Any())
{
return;
}
var builder = new SqlConnectionStringBuilder(ConnectionStringBuilder.ConnectionString);
if (!isPreDeploy)
{
// Only set initial catalog for post-deployment script since database might not exist yet for pre-deployment
builder.InitialCatalog = targetDatabaseName;
}
var executionEngineConditions = new ExecutionEngineConditions {
IsSqlCmd = true
};
using var engine = new ExecutionEngine();
using var connection = new SqlConnection(builder.ConnectionString);
connection.Open();
foreach (var reference in references)
{
if (!File.Exists(reference))
{
// TODO Format output to allow warning to show up in Error List
_console.WriteLine($"warning: Unable to find referenced .dacpac at '{reference}'");
continue;
}
using var referencedPackage = DacPackage.Load(reference);
var script = isPreDeploy ? referencedPackage.GetPreDeploymentScript() : referencedPackage.GetPostDeploymentScript();
if (string.IsNullOrEmpty(script))
{
continue;
}
string scriptPrefix = isPreDeploy ? "pre" : "post";
_console.WriteLine($"Running {scriptPrefix}-deployment script for referenced package '{referencedPackage.Name}' version '{referencedPackage.Version}'");
_currentSource = $"{referencedPackage.Name}/{scriptPrefix}deploy.sql";
var scriptExecutionArgs = new ScriptExecutionArgs(script, connection, 0, executionEngineConditions, this);
AddSqlCmdVariables(scriptExecutionArgs, targetDatabaseName);
engine.BatchParserExecutionError += (sender, args) => _console.WriteLine(args.Format(_currentSource));
engine.ScriptExecutionFinished += (sender, args) => _console.WriteLine($"Executed {scriptPrefix}-deployment script for referenced package " +
$"'{referencedPackage.Name}' version '{referencedPackage.Version}' with result: {args.ExecutionResult}");
engine.ExecuteScript(scriptExecutionArgs);
}
}
/// <summary>
/// Takes in a query string and returns a list of BatchDefinitions
/// </summary>
public List <BatchDefinition> GetBatches(string sqlScript, ExecutionEngineConditions conditions = null)
{
batchInfos = new List <BatchInfo>();
// execute the script - all communication / integration after here happen via event handlers
executionEngine.ParseScript(sqlScript, notificationHandler, conditions);
// retrieve a list of BatchDefinitions
List <BatchDefinition> batchDefinitionList = ConvertToBatchDefinitionList(sqlScript, batchInfos);
return(batchDefinitionList);
}
/// <summary>
/// Test with execution timeout value
/// </summary>
//TEST_DOESNOTWORK[TestMethod()]
public void ExecutionEngineTest_TimeOut()
{
string sqlStatement = "select * from sysobjects\n go 10\n";
ExecutionEngineConditions conditions = new ExecutionEngineConditions();
conditions.IsTransactionWrapped = true;
conditions.IsParseOnly = false;
conditions.IsHaltOnError = false;
TestExecutor executor = new TestExecutor(sqlStatement, connection, conditions, -1);
executor.Run();
Assert.Equal(executor.ExecutionResult, ScriptExecutionResult.Success);
}
public void TestShowStatements()
{
Assert.NotNull(ExecutionEngineConditions.ShowPlanXmlStatement(true));
Assert.NotNull(ExecutionEngineConditions.ShowPlanAllStatement(true));
Assert.NotNull(ExecutionEngineConditions.ShowPlanTextStatement(true));
Assert.NotNull(ExecutionEngineConditions.StatisticsXmlStatement(true));
Assert.NotNull(ExecutionEngineConditions.StatisticsProfileStatement(true));
Assert.NotNull(ExecutionEngineConditions.ParseOnlyStatement(true));
Assert.NotNull(ExecutionEngineConditions.NoExecStatement(true));
Assert.NotNull(ExecutionEngineConditions.StatisticsIOStatement(true));
Assert.NotNull(ExecutionEngineConditions.StatisticsTimeStatement(true));
Assert.NotNull(ExecutionEngineConditions.ResetStatement);
}
public void ExecutionEngineTest_ParseOnly_ValidScriptWithoutTransaction()
{
string sqlStatement = "select * from sysobjects";
ExecutionEngineConditions conditions = new ExecutionEngineConditions();
conditions.IsTransactionWrapped = false;
conditions.IsParseOnly = true;
conditions.IsHaltOnError = false;
TestExecutor executor = new TestExecutor(sqlStatement, connection, conditions);
executor.Run();
Assert.Equal(ScriptExecutionResult.Success, executor.ExecutionResult);
Assert.True(executor.ResultCountQueue.Count == 0);
Assert.Equal(0, executor.BatchFinshedEventCounter);
}
public void ExecutionEngineTest_DefaultCondition_ValidScript()
{
string sqlStatement = "select * from sysobjects\nGo\n";
//Use default execution condition
ExecutionEngineConditions conditions = new ExecutionEngineConditions();
TestExecutor executor = new TestExecutor(sqlStatement, connection, conditions, false);
executor.Run();
//Get the expected values
List<string> batchScripts = executor.BatchScripts;
ExecuteSqlBatch(batchScripts, connection);
Assert.Equal(ScriptExecutionResult.Success, executor.ExecutionResult);
Assert.True(CompareTwoIntLists(executor.ResultCountQueue, expResultCounts));
}
public TestExecutor(string batch, SqlConnection conn, ExecutionEngineConditions exeCondition, bool cancelExecution)
{
sqlStatement = batch;
conditions.IsHaltOnError = exeCondition.IsHaltOnError;
conditions.IsParseOnly = exeCondition.IsParseOnly;
conditions.IsTransactionWrapped = exeCondition.IsTransactionWrapped;
_cancel = cancelExecution;
connection = conn;
//Initialize the static variables
execResult = ScriptExecutionResult.All;
batchScripts = new List <string>();
exeThread = null;
parserExecutionError = false;
}
public void ExecutionEngineTest_InvalidConnection()
{
string sqlStatement = "select * from sysobjects\n go 100\n";
ExecutionEngineConditions conditions = new ExecutionEngineConditions();
conditions.IsTransactionWrapped = true;
conditions.IsParseOnly = false;
conditions.IsHaltOnError = false;
connection.Close();
TestExecutor executor = new TestExecutor(sqlStatement, connection, conditions);
executor.Run();
// Note: this used to also return Halted at some point in the distant past.
// However since that gets mapped to Failure anyhow, consider "Failure" as acceptable here
Assert.True(executor.ExecutionResult.HasFlag(ScriptExecutionResult.Failure), "Expected failure when invalid connection is present");
}
public void ExecutionEngineTest_SyncCancel()
{
string sqlStatement = "waitfor delay '0:0:10'";
ExecutionEngineConditions conditions = new ExecutionEngineConditions();
conditions.IsTransactionWrapped = true;
conditions.IsParseOnly = false;
conditions.IsHaltOnError = false;
TestExecutor executor = new TestExecutor(sqlStatement, connection, conditions, true);
executor.CancelTimeOut = 3000;
executor.Run();
Assert.NotNull(executor.ScriptExecuteThread);
Assert.Equal(ScriptExecutionResult.Cancel, executor.ExecutionResult);
Assert.True(executor.CancelEventFired);
}
/// <summary>
/// Test sync cancel when the execution is done
/// </summary>
///
/// Disabled test, has race condition where Sql statement will finish
/// before harness has an opportunity to cancel.
//TEST_DOESNOTWORK[TestMethod()]
public void ExecutionEngineTest_SyncCancelAfterExecutionDone()
{
string sqlStatement = "select 1";
ExecutionEngineConditions conditions = new ExecutionEngineConditions();
conditions.IsTransactionWrapped = true;
conditions.IsParseOnly = false;
conditions.IsHaltOnError = false;
TestExecutor executor = new TestExecutor(sqlStatement, connection, conditions, true);
executor.Run();
Assert.True(!executor.CancelEventFired);
Assert.NotNull(executor.ScriptExecuteThread);
if (executor.ScriptExecuteThread != null)
Assert.True(!executor.ScriptExecuteThread.IsAlive);
Assert.Equal(ScriptExecutionResult.Success | ScriptExecutionResult.Cancel, executor.ExecutionResult);
}
public void TestExecutionEngineConditions()
{
string sqlStatement = "select * from sys.databases\ngo\nselect name from sys.databases\ngo\nprint 'test'\ngo";
ExecutionEngineConditions conditions = new ExecutionEngineConditions();
conditions.IsNoExec = true;
conditions.IsStatisticsIO = true;
conditions.IsStatisticsTime = true;
conditions.IsEstimatedShowPlan = true;
TestExecutor executor = new TestExecutor(sqlStatement, connection, conditions, false);
executor.Run();
//Get the expected values
List<string> batchScripts = executor.BatchScripts;
ExecuteSqlBatch(batchScripts, connection);
Assert.Equal(ScriptExecutionResult.Success, executor.ExecutionResult);
Assert.True(CompareTwoIntLists(executor.ResultCountQueue, expResultCounts));
}
public void VerifyIncludeSqlCmd()
{
string file = "VerifyIncludeSqlCmd_test.sql";
try
{
using (ExecutionEngine executionEngine = new ExecutionEngine())
{
var liveConnection = LiveConnectionHelper.InitLiveConnectionInfo("master");
string serverName = liveConnection.ConnectionInfo.ConnectionDetails.ServerName;
string sqlCmdFile = $@"
select * from sys.databases where name = 'master'
GO";
File.WriteAllText("VerifyIncludeSqlCmd_test.sql", sqlCmdFile);
string sqlCmdQuery = $@"
:r {file}
GO
select * from sys.databases where name = 'master'
GO";
var condition = new ExecutionEngineConditions()
{
IsSqlCmd = true
};
using (SqlConnection sqlConn = ConnectionService.OpenSqlConnection(liveConnection.ConnectionInfo))
using (TestExecutor testExecutor = new TestExecutor(sqlCmdQuery, sqlConn, condition))
{
testExecutor.Run();
Assert.True(testExecutor.ResultCountQueue.Count == 2, $"Unexpected number of ResultCount items - expected 1 but got {testExecutor.ResultCountQueue.Count}");
Assert.True(testExecutor.ErrorMessageQueue.Count == 0, $"Unexpected error messages from test executor : {string.Join(Environment.NewLine, testExecutor.ErrorMessageQueue)}");
}
File.Delete(file);
}
}
catch
{
if (File.Exists(file))
{
File.Delete(file);
}
}
}
public void TestExecutionEngineConditionsSetMethods()
{
ExecutionEngineConditions conditions = new ExecutionEngineConditions();
bool getValue = conditions.IsScriptExecutionTracked;
conditions.IsScriptExecutionTracked = !getValue;
Assert.Equal(conditions.IsScriptExecutionTracked, !getValue);
getValue = conditions.IsEstimatedShowPlan;
conditions.IsEstimatedShowPlan = !getValue;
Assert.Equal(conditions.IsEstimatedShowPlan, !getValue);
getValue = conditions.IsActualShowPlan;
conditions.IsActualShowPlan = !getValue;
Assert.Equal(conditions.IsActualShowPlan, !getValue);
getValue = conditions.IsSuppressProviderMessageHeaders;
conditions.IsSuppressProviderMessageHeaders = !getValue;
Assert.Equal(conditions.IsSuppressProviderMessageHeaders, !getValue);
getValue = conditions.IsNoExec;
conditions.IsNoExec = !getValue;
Assert.Equal(conditions.IsNoExec, !getValue);
getValue = conditions.IsStatisticsIO;
conditions.IsStatisticsIO = !getValue;
Assert.Equal(conditions.IsStatisticsIO, !getValue);
getValue = conditions.IsShowPlanText;
conditions.IsShowPlanText = !getValue;
Assert.Equal(conditions.IsShowPlanText, !getValue);
getValue = conditions.IsStatisticsTime;
conditions.IsStatisticsTime = !getValue;
Assert.Equal(conditions.IsStatisticsTime, !getValue);
getValue = conditions.IsSqlCmd;
conditions.IsSqlCmd = !getValue;
Assert.Equal(conditions.IsSqlCmd, !getValue);
conditions.BatchSeparator = "GO";
Assert.Equal(conditions.BatchSeparator, "GO");
}
public void ExecutionEngineTest_MultiValidScripts()
{
string sqlStatement = "select * from sys.databases\ngo\nselect name from sys.databases\ngo\nprint 'test'\ngo";
ExecutionEngineConditions conditions = new ExecutionEngineConditions();
conditions.IsTransactionWrapped = true;
conditions.IsParseOnly = false;
conditions.IsHaltOnError = false;
TestExecutor executor = new TestExecutor(sqlStatement, connection, conditions, false);
executor.Run();
//Get the expected values
List<string> batchScripts = executor.BatchScripts;
ExecuteSqlBatch(batchScripts, connection);
Assert.Equal(ScriptExecutionResult.Success, executor.ExecutionResult);
Assert.True(CompareTwoIntLists(executor.ResultCountQueue, expResultCounts));
}
public void ExecutionEngineTest_TestComment()
{
string sqlStatement = "/*test comments*/";
ExecutionEngineConditions conditions = new ExecutionEngineConditions();
conditions.IsTransactionWrapped = true;
conditions.IsParseOnly = false;
conditions.IsHaltOnError = false;
TestExecutor executor = new TestExecutor(sqlStatement, connection, conditions);
executor.Run();
//Get the expected values
List<string> batchScripts = executor.BatchScripts;
ExecuteSqlBatch(batchScripts, connection);
Assert.Equal(ScriptExecutionResult.Success, executor.ExecutionResult);
Assert.True(CompareTwoIntLists(executor.ResultCountQueue, expResultCounts));
}
public void ExecutionEngineTest_DefaultCondition_InvalidScript()
{
string sqlStatement = "select ** from sysobjects";
//Use default execution condition
ExecutionEngineConditions conditions = new ExecutionEngineConditions();
TestExecutor executor = new TestExecutor(sqlStatement, connection, conditions, false);
executor.Run();
//Get the expected values
List<string> batchScripts = executor.BatchScripts;
ExecuteSqlBatch(batchScripts, connection);
Assert.Equal(ScriptExecutionResult.Success | ScriptExecutionResult.Failure, executor.ExecutionResult);
Assert.True(!executor.ParserExecutionError);
Assert.True(CompareTwoStringLists(executor.ErrorMessageQueue, expErrorMessage));
Assert.True(CompareTwoIntLists(executor.ResultCountQueue, expResultCounts));
Assert.Equal(0, executor.BatchFinshedEventCounter);
}
public void ExecutionEngineTest_ParseOnly_InvalidScript()
{
string sqlStatement = "select ** from authors";
ExecutionEngineConditions conditions = new ExecutionEngineConditions();
conditions.IsTransactionWrapped = true;
conditions.IsParseOnly = true;
conditions.IsHaltOnError = false;
TestExecutor executor = new TestExecutor(sqlStatement, connection, conditions);
executor.Run();
//Get the expected values
List<string> batchScripts = executor.BatchScripts;
ExecuteSqlBatch(batchScripts, connection);
Assert.Equal(ScriptExecutionResult.Success | ScriptExecutionResult.Failure, executor.ExecutionResult);
Assert.True(!executor.ParserExecutionError);
Assert.True(executor.ResultCountQueue.Count == 0);
Assert.True(CompareTwoStringLists(executor.ErrorMessageQueue, expErrorMessage));
}
public void ExecutionEngineTest_ASyncCancel()
{
//string sqlStatement = "--This is a test\nSELECT * FROM sysobjects as t\nGO 50\n use pubsplus \n select * from titles\n go" ;
string sqlStatement = "waitfor delay '0:0:10'";
ExecutionEngineConditions conditions = new ExecutionEngineConditions();
conditions.IsTransactionWrapped = true;
conditions.IsParseOnly = false;
conditions.IsHaltOnError = false;
TestExecutor executor = new TestExecutor(sqlStatement, connection, conditions, true);
executor.SyncCancel = false;
executor.Run();
Assert.True(executor.CancelEventFired);
Assert.NotNull(executor.ScriptExecuteThread);
if (executor.ScriptExecuteThread != null)
Assert.True(!executor.ScriptExecuteThread.IsAlive);
Assert.Equal(ScriptExecutionResult.Cancel, executor.ExecutionResult);
}
public void VerifyConnectSqlCmd()
{
using (ExecutionEngine executionEngine = new ExecutionEngine())
{
var liveConnection = LiveConnectionHelper.InitLiveConnectionInfo("master");
string serverName = liveConnection.ConnectionInfo.ConnectionDetails.ServerName;
string userName = liveConnection.ConnectionInfo.ConnectionDetails.UserName;
string password = liveConnection.ConnectionInfo.ConnectionDetails.Password;
string sqlCmdQuery = $@"
:Connect {serverName} -U {userName} -P {password}
GO
select * from sys.databases where name = 'master'
GO";
string sqlCmdQueryIncorrect = $@"
:Connect {serverName} -u {userName} -p {password}
GO
select * from sys.databases where name = 'master'
GO";
var condition = new ExecutionEngineConditions()
{
IsSqlCmd = true
};
using (SqlConnection sqlConn = ConnectionService.OpenSqlConnection(liveConnection.ConnectionInfo))
using (TestExecutor testExecutor = new TestExecutor(sqlCmdQuery, sqlConn, condition))
{
testExecutor.Run();
Assert.True(testExecutor.ParserExecutionError == false, "Parse Execution error should be false");
Assert.True(testExecutor.ResultCountQueue.Count == 1, $"Unexpected number of ResultCount items - expected 1 but got {testExecutor.ResultCountQueue.Count}");
Assert.True(testExecutor.ErrorMessageQueue.Count == 0, $"Unexpected error messages from test executor : {string.Join(Environment.NewLine, testExecutor.ErrorMessageQueue)}");
}
using (SqlConnection sqlConn = ConnectionService.OpenSqlConnection(liveConnection.ConnectionInfo))
using (TestExecutor testExecutor = new TestExecutor(sqlCmdQueryIncorrect, sqlConn, condition))
{
testExecutor.Run();
Assert.True(testExecutor.ParserExecutionError == true, "Parse Execution error should be true");
}
}
}
public void ExecutionEngineTest_MixedValidandInvalidScript()
{
string sqlStatement = "SELECT * FROM Authors \n Go\n select * from sysobjects \n go\nif exists (select * from sysobjects where id = object_id('MyTab')) DROP TABLE MyTab2";
ExecutionEngineConditions conditions = new ExecutionEngineConditions();
conditions.IsTransactionWrapped = true;
conditions.IsParseOnly = false;
conditions.IsHaltOnError = false;
TestExecutor executor = new TestExecutor(sqlStatement, connection, conditions);
executor.Run();
//Get the expected values
List<string> batchScripts = executor.BatchScripts;
ExecuteSqlBatch(batchScripts, connection);
Assert.Equal(executor.ExecutionResult, ScriptExecutionResult.Success | ScriptExecutionResult.Failure);
Assert.True(!executor.ParserExecutionError);
Assert.True(CompareTwoStringLists(executor.ErrorMessageQueue, expErrorMessage));
Assert.True(CompareTwoIntLists(executor.ResultCountQueue, expResultCounts));
}
public void ExecutionEngineTest_HaltOnError_OneBatch()
{
string sqlStatement = "select * from authors\n go 30\n";
ExecutionEngineConditions conditions = new ExecutionEngineConditions();
conditions.IsTransactionWrapped = true;
conditions.IsParseOnly = false;
conditions.IsHaltOnError = true;
TestExecutor executor = new TestExecutor(sqlStatement, connection, conditions);
executor.Run();
//Get the expected values
List<string> batchScripts = executor.BatchScripts;
ExecuteSqlBatch(batchScripts, connection);
Assert.Equal(ScriptExecutionResult.Halted | ScriptExecutionResult.Failure, executor.ExecutionResult);
Assert.True(CompareTwoStringLists(executor.ErrorMessageQueue, expErrorMessage));
Assert.True(CompareTwoIntLists(executor.ResultCountQueue, expResultCounts));
Assert.True(executor.ResultCountQueue.Count == 0);
Assert.Equal(0, executor.BatchFinshedEventCounter);
}
public void ExecutionEngineTest_MultipleInvalidScript_SingleBatch()
{
string sqlStatement = "select ** from products \n insert into products values (1,'abc')\n go \n";
ExecutionEngineConditions conditions = new ExecutionEngineConditions();
conditions.IsTransactionWrapped = true;
conditions.IsParseOnly = false;
conditions.IsHaltOnError = false;
TestExecutor executor = new TestExecutor(sqlStatement, connection, conditions);
executor.Run();
//Get the expected values
List<string> batchScripts = executor.BatchScripts;
ExecuteSqlBatch(batchScripts, connection);
Assert.Equal(ScriptExecutionResult.Success | ScriptExecutionResult.Failure, executor.ExecutionResult);
Assert.True(!executor.ParserExecutionError);
Assert.True(CompareTwoStringLists(executor.ErrorMessageQueue, expErrorMessage));
Assert.True(CompareTwoIntLists(executor.ResultCountQueue, expResultCounts));
}
/// <summary>
/// Execut the script
/// </summary>
/// <param name="exec">Execution Engine</param>
/// <param name="connection">SQL connection</param>
/// <param name="script">script text</param>
/// <param name="conditions">Execution condition</param>
/// <param name="batchHandler">Batch event handler</param>
/// <param name="timeout">time out value</param>
static void ExecuteScript(ExecutionEngine exec, SqlConnection connection, string script, ExecutionEngineConditions conditions, IBatchEventsHandler batchHandler, int timeout)
{
Validate.IsNotNull(nameof(exec), exec);
Validate.IsNotNull(nameof(connection), connection);
Validate.IsNotNullOrEmptyString(nameof(script), script);
Validate.IsNotNull(nameof(conditions), conditions);
Console.WriteLine("------------------------ Executing Script ----------------------");
//exec.BeginScriptExecution(script, connection, timeout, conditions, batchConsumer);
ScriptExecutionArgs args = new ScriptExecutionArgs(script, connection, timeout, conditions, batchHandler);
//exec.ExecuteScript(args);
_executionThread = new Thread(new ParameterizedThreadStart(exec.ExecuteScript));
_executionThread.Start(args);
}
public TestExecutor(string batch, SqlConnection conn, ExecutionEngineConditions exeCondition, int timeOut)
: this(batch, conn, exeCondition, false)
{
exeTimeOut = timeOut;
}
