public void SpTestRunner_RunProcQueryFail(string testPath, string testCase)
{
// ARRANGE
string basePath = "TestCases\\SpRunner\\RunProcQuery\\Fail";
// Save the Connection String and Stored Procedure name in case they are set at the class level
string savedConn = SpRunner.ConnectionString;
string savedSPName = SpRunner.SpName;
// Temporarily clear the connection string and spName from the class property so the input files can simulate missing values
SpRunner.ConnectionString = "";
SpRunner.SpName = "";
// Prepare input and expected
var input = JsonConvert.DeserializeObject <SpExecInput>(File.ReadAllText($"{basePath}\\{testPath}\\input{testCase}.json"));
var expected = JsonConvert.DeserializeObject <SpExecResult>(File.ReadAllText($"{basePath}\\{testPath}\\expected{testCase}.json"));
////ACT
SpExecResult actual = SpRunner.RunProcQuery(input);
//Capture json output if needed to create test case "expected" records.
string jsonString = JsonConvert.SerializeObject(actual, Formatting.Indented);
// restore class level settings for subsequent tests in this same run. Do this before Assert in case an error is thrown during Assert.
SpRunner.ConnectionString = savedConn;
SpRunner.SpName = savedSPName;
// ASSERT
Assert.True(actual.ResultText == expected.ResultText);
}
// Main method to execute stored procedure
// The Execute method looks for input properties that are already set at the SqlSpClient level, then applies the values from the input model.
// The values in the input model have precedence.
// Returns SpExecResultObject that contains the run details and the rows returned
// Use the SpExecResult.IsEquivalent method to Assert that the actual SpExecResult is equal to the expected result.
public static SpExecResult ExecProcQuery(SpExecInput input)
{
Stopwatch stopWatch = new Stopwatch();
SpExecResult result = new SpExecResult();
try
{
// set local variables to prepare
// PrepRun(input);
// use local variables to execute proc
using (SqlConnection con = new SqlConnection(input.ConnectionString))
{
SqlCommand cmd = new SqlCommand(input.SpName, con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = input.CommandTimeout ?? 0;
// The list of simple SqlParamInput models is set from the class Property or input model.
// If parameters are present, the SqlDataCommand.AddSqlParameters extension method will convert them to real Sql parameters and add them to the SqlDataCommand.
if (input.SqlParams != null && input.SqlParams.Count() > 0)
{
cmd.AddSqlParameters(input.SqlParams);
}
//Open the connection
con.Open();
//Start run timer to record elapsed time
stopWatch.Start();
SqlDataReader rdr = cmd.ExecuteReader();
result.DbRows = rdr.GetDbRows();
// TODO CHECK IF RET PARAM EXISTS
// RETURN VALUE
result.ReturnValue = (int)cmd.Parameters["@ReturnValue"].Value;
result.ResultText = "Success";
}
}
catch (Exception e)
{
string errMsg = DebugLogger.CreateErrorDetail("SqlSpClient.Execute", e.Message, e.InnerException?.Message);
result.ResultText = errMsg;
DebugLogger.LogError(errMsg);
}
//Add duration
if (stopWatch != null)
{
stopWatch.Stop();
result.Duration = (stopWatch.ElapsedMilliseconds);
}
//Capture json output if needed to create test case "expected" records.
string jsonString = JsonConvert.SerializeObject(result, Formatting.Indented);
return(result);
}
public void SpTestRunner_RunProcQueryGood(string procedure, string testCase)
{
//ARRANGE
string basePath = "TestCases\\SpRunner\\RunProcQuery\\Good";
// Create the input model
var input = JsonConvert.DeserializeObject <SpExecInput>(File.ReadAllText($"{basePath}\\{procedure}\\input{testCase}.json"));
// Create the expected model
var expected = JsonConvert.DeserializeObject <SpExecResult>(File.ReadAllText($"{basePath}\\{procedure}\\expected{testCase}.json"));
////ACT
SpExecResult actual = SpRunner.RunProcQuery(input);
////ASSERT
/// SpExecOutput.IsEquivalent method performs deep compare and generates detailed error messages to ResultText property and the Console Log
/// NOTE!!!
/// Comparison for SpExecResult.Duration passes when expected Duration is greater than 0 and actual.Duration IS LESS THAN OR EQUAL TO expected.Duration
/// To disable this check set expected.Duration to 0
///
Assert.True(actual.IsEquivalent(expected));
}
