/// <summary> /// used when the CommandType is TableDirect. /// </summary> private DbTestSqlResult ExecuteSqlTableDirectRequest(SqlRequest request, DbTestRunnerResult result, string phaseName) { var sqlResult = new DbTestSqlResult(); try { // Create a new command using the same connection as the data context using (var cmd = this.GetDbConnection().CreateCommand()) { // Set the command parameters foreach (var p in request.Parameters.Select(i => i.Value)) { result.Logs.Add($"{phaseName} Sql Parameter:{p.ParameterName} = {p.Value}"); cmd.Parameters.Add(p); } if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); } // build up the sql command object cmd.CommandText = $"Select * From {request.SqlToExecute}"; result.Logs.Add($"{phaseName} Table Direct Sql: {cmd.CommandText}"); cmd.CommandType = CommandType.Text; // Execute the command via a data reader and use it to load data tables; one for each result set returned from the sql. using (var rdr = cmd.ExecuteReader()) { do { var dt = new DataTable(); dt.Load(rdr); sqlResult.Data.Add(dt); result.Logs.Add($"{phaseName} Phase: DataTable[{sqlResult.Data.Count - 1}] read {dt.Rows.Count} rows(s)."); } while (!rdr.IsClosed && rdr.NextResult()); } result.Logs.Add($"{phaseName} Phase: Read {sqlResult.Data.Count} result set(s)."); } sqlResult.IsSuccessful = true; } catch (Exception e) { sqlResult.Exception = e; sqlResult.IsSuccessful = false; } return(sqlResult); }
/// <summary> /// Used when CommandType is StoredProcedure /// </summary> private DbTestSqlResult ExecuteSqlStoredProcedureRequest(SqlRequest request, DbTestRunnerResult result, string phaseName) { var sqlResult = new DbTestSqlResult(); try { // var dataTable = new DataTable(); var dataset = new DataSet(); // Create a new command using the same connection as the data context using (var cmd = GetDbConnection().CreateCommand()) { if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); } // build up the sql command object cmd.CommandText = request.SqlToExecute; cmd.CommandType = CommandType.StoredProcedure; // Set the command parameters foreach (var p in request.Parameters.Select(i => i.Value)) { cmd.Parameters.Add(p); } result.Logs.Add($"{phaseName} Stored Procedure: {cmd.CommandText}"); // Execute the command via a data adapter and use it to load a data set using (var dataAdapter = new SqlDataAdapter(cmd)) { //dataAdapter.Fill(dataTable); dataAdapter.Fill(dataset); } } // populate the sqlResult data tables with the tables filled by the adapter sqlResult.Data.AddRange(dataset.Tables.Cast <DataTable>()); //sqlResult.Data.Add(dataTable); sqlResult.IsSuccessful = true; } catch (Exception e) { sqlResult.Exception = e; sqlResult.IsSuccessful = false; } return(sqlResult); }
/// <summary> /// Executes the sql requests /// </summary> protected DbTestSqlResult ExecuteTestPhase(DbTestRunnerResult result, string phaseName, SqlRequest request) { var phaseResult = new DbTestSqlResult(); try { if (request == null) { return(DbTestSqlResult.Success); } // if we're supposed to commit the data context changes before running the sql, do so now. if (request.DataContextCommitPoint == DataContextCommitPoint.BeforeRunningSql) { var contextRowsAffected = this.CommitChanges(); result.Logs.Add($"{phaseName} Phase: Commited Data Context prior to executing sql. Rows Affected: {contextRowsAffected}"); } if (request.IsEmpty) { return(DbTestSqlResult.Success); } // Run the sql, we have different execution strategies based on the command types switch (request.CommandType) { case CommandType.Text: phaseResult = this.ExecuteSqlTextRequest(request, result, phaseName); break; case CommandType.StoredProcedure: phaseResult = this.ExecuteSqlStoredProcedureRequest(request, result, phaseName); break; case CommandType.TableDirect: phaseResult = this.ExecuteSqlTableDirectRequest(request, result, phaseName); break; default: throw new Exception("Unsupported Command Type"); } // if there was a problem running the sql command, stop executing now if (!phaseResult.IsSuccessful) { return(phaseResult); } // if we're supposed to commit the data context changes after running the sql, do so now. if (request.DataContextCommitPoint == DataContextCommitPoint.AfterRunningSql) { var contextRowsAffected = this.CommitChanges(); result.Logs.Add($"{phaseName} Phase: Commited Data Context prior to executing sql. Rows Affected: {contextRowsAffected}"); } // if we got this far, the test executed successfully, set the return value phaseResult.IsSuccessful = true; } catch (Exception e) { // runtime exception executing the test. result.Exception = e; result.Logs.Add($"Error attempting to execute {phaseName} sql. {e.GetType().Name} - {e.Message}"); phaseResult.IsSuccessful = false; throw; } // set the overall result success status based on how this command was executed result.IsSuccessful = phaseResult.IsSuccessful; result.TestPhase = phaseName; return(phaseResult); }