public static StoredProcedureTest AddCustomParameterToBoth(this StoredProcedureTest test, string parameterName, string value)
{
test.UnoptimisedStoredProcedureParameters.Add(new TestParameter(parameterName, value, TestParameterDataType.Custom));
test.OptimisedStoredProcedureParameters.Add(new TestParameter(parameterName, value, TestParameterDataType.Custom));
return(test);
}
public void BuildSqlQueryText(StoredProcedureTest test)
{
if (!hasBeenBuilt)
{
Build(test);
}
}
public static StoredProcedureTest AddBoolParameterToBoth(this StoredProcedureTest test, string parameterName, bool value)
{
test.UnoptimisedStoredProcedureParameters.Add(new TestParameter(parameterName, value.ToString(), TestParameterDataType.Bool));
test.OptimisedStoredProcedureParameters.Add(new TestParameter(parameterName, value.ToString(), TestParameterDataType.Bool));
return(test);
}
public static StoredProcedureTest DefaultTest()
{
StoredProcedureTest storedProcedureTest = new StoredProcedureTest();
storedProcedureTest.DatabaseName = StoredProcedureTesterTestsConsts.DatabaseName;
storedProcedureTest.UnoptimisedStoredProcedureName = $"[StoredProcedureTester].{StoredProcedureTesterTestsConsts.TestUnoptimisedStoredProcedureName}";
storedProcedureTest.OptimisedStoredProcedureName = $"[StoredProcedureTester].{StoredProcedureTesterTestsConsts.TestOptimisedStoredProcedureName}";
return(storedProcedureTest);
}
public async Task <TestSummary> Run(StoredProcedureTest currentTest, bool isRetry)
{
if (!isRetry)
{
_sqlBuilder.ResetSql();
}
_sqlBuilder.BuildSqlQueryText(currentTest);
TestSummary testSummary = await _sqlRunner.RunSqlAsync(currentTest);
return(testSummary);
}
private static async Task <TestSummary> RunStoredProcedureTester(StoredProcedureTest storedProcedureTest = null)
{
if (storedProcedureTest == null)
{
storedProcedureTest = StoredProcedureTestFactory.DefaultTest();
}
SqlTestRunner sqlTestRunner = new SqlTestRunner(new NullLogHelper());
TestSummary testSummary = await sqlTestRunner.Run(storedProcedureTest);
return(testSummary);
}
private void SetupDefaults()
{
currentTest = new StoredProcedureTest();
logHelper = new LogHelper();
logHelper.GetLogs().ListChanged += LogsOnListChanged;
logHelper.Log("Ready");
lbUnoptimisedParameters.MouseDoubleClick += LbUnoptimisedParametersOnMouseDoubleClick;
lbOptimisedParameters.MouseDoubleClick += LbOptimisedParametersOnMouseDoubleClick;
cbType.SelectedIndexChanged += CbTypeOnSelectedIndexChanged;
this.KeyPreview = true;
dtpValue.Hide();
chkBoxValue.Hide();
btnGenerateGuid.Hide();
HideResults();
}
public async Task TestTwoDuplicateStoredProceduresThatUseCustomParameter()
{
try
{
var storedProcedureHelper = CreateStoredProcedureHelper();
await storedProcedureHelper.CreateDuplicateStoredProceduresThatUseCustomParameterAsync();
StoredProcedureTest storedProcedureTest = StoredProcedureTestFactory.DefaultTest()
.AddCustomParameterToBoth("TestCustom", "Custom");
var testSummary = await RunStoredProcedureTester(storedProcedureTest);
CheckResult(testSummary, ResultEnum.AllPass).ShouldBeTrue();
}
finally
{
await Cleanup();
}
}
private void FillVariables(StoredProcedureTest test)
{
stringBuilder.Replace("{UnoptimisedStoredProcedureName}", $"{test.DatabaseName}.{test.UnoptimisedStoredProcedureName}");
stringBuilder.Replace("{OptimisedStoredProcedureName}", $"{test.DatabaseName}.{test.OptimisedStoredProcedureName}");
StringBuilder unOptimisedParameters = new StringBuilder();
for (int i = 0; i < test.UnoptimisedStoredProcedureParameters.Count; i++)
{
TestParameter testParameter = test.UnoptimisedStoredProcedureParameters[i];
if (i == 0)
{
unOptimisedParameters.AppendLine($"+N' {testParameter.GetSqlString()}'");
}
else
{
unOptimisedParameters.AppendLine($"+N', {testParameter.GetSqlString()}'");
}
}
StringBuilder optimisedParameters = new StringBuilder();
for (int i = 0; i < test.OptimisedStoredProcedureParameters.Count; i++)
{
TestParameter testParameter = test.OptimisedStoredProcedureParameters[i];
if (i == 0)
{
optimisedParameters.AppendLine($"+N' {testParameter.GetSqlString()}'");
}
else
{
optimisedParameters.AppendLine($"+N', {testParameter.GetSqlString()}'");
}
}
stringBuilder.Replace("{UsingUnoptimisedParameters}", unOptimisedParameters.ToString());
stringBuilder.Replace("{UsingOptimisedParameters}", optimisedParameters.ToString());
test.SetGeneratedSql(stringBuilder.ToString());
}
public async Task <TestSummary> RunSqlAsync(StoredProcedureTest test)
{
try
{
string connectionString = $"SERVER=(local);DATABASE={test.DatabaseName};Integrated Security=true";
foreach (string startupScript in StoredProcedureTesterConsts.StartupScripts)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(startupScript, connection);
await connection.OpenAsync();
SqlDataReader reader = await command.ExecuteReaderAsync();
}
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(test.GeneratedSql, connection);
//command.Parameters.AddWithValue("@tPatSName", "Your-Parm-Value");
await connection.OpenAsync();
SqlDataReader reader = command.ExecuteReader();
try
{
TestSummary testSummary = new TestSummary(test);
while (await reader.ReadAsync())
{
testSummary.FirstTestResult = new TestResult((bool)reader["First Test Result"],
reader["First Test Message"] == DBNull.Value
? string.Empty
: (string)reader["First Test Message"]);
testSummary.SecondTestResult = new TestResult((bool)reader["Second Test Result"],
reader["Second Test Message"] == DBNull.Value
? string.Empty
: (string)reader["Second Test Message"]);
testSummary.ThirdTestResult = new TestResult((bool)reader["Third Test Result"],
reader["Third Test Message"] == DBNull.Value
? string.Empty
: (string)reader["Third Test Message"]);
testSummary.FourthTestResult = new TestResult((bool)reader["Fourth Test Result"],
reader["Fourth Test Message"] == DBNull.Value
? string.Empty
: (string)reader["Fourth Test Message"]);
testSummary.OverallResult = (bool)reader["Overall Result"];
testSummary.UnoptimisedDateTimeStart = (DateTime)reader["Unoptimised Start"];
testSummary.UnoptimisedDateTimeEnd = (DateTime)reader["Unoptimised End"];
testSummary.OptimisedDateTimeStart = (DateTime)reader["Optimised Start"];
testSummary.OptimisedDateTimeEnd = (DateTime)reader["Optimised End"];
//Save to excel
SaveToFile(testSummary);
_logHelper.Log("Ran SQL");
if (testSummary.OverallResult)
{
_logHelper.Log("Test PASSED");
}
else
{
_logHelper.Log("Test FAILED");
}
}
return(testSummary);
}
catch (Exception ex)
{
_logHelper.Log(ex.Message);
throw;
}
finally
{
// Always call Close when done reading.
reader.Close();
}
}
}
catch (Exception ex)
{
_logHelper.Log(ex.Message);
throw;
}
}
public void Build(StoredProcedureTest test)
{
FillVariables(test);
_logHelper.Log("Built SQL");
hasBeenBuilt = true;
}
public async Task <TestSummary> Run(StoredProcedureTest currentTest)
{
return(await Run(currentTest, false));
}
