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