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)); }