public DataDifferencesModel GetDifferences(DataTable userData, DataTable answerData)
{
DataDifferencesModel differences = new DataDifferencesModel();
differences.UserDataRowCount = userData.Rows.Count;
differences.AnswerDataRowCount = answerData.Rows.Count;
differences.UserDataColumnCount = userData.Columns.Count;
differences.AnswerDataColumnCount = answerData.Columns.Count;
// Check that they have the same number of rows
if (userData.Rows.Count != answerData.Rows.Count)
{
DataDifferenceModel difference = new DataDifferenceModel();
difference.DifferenceType = DataDifferenceType.RowCountDifferent;
differences.Differences.Add(difference);
}
// Check that they have the same number of columns
// If the number of columns don't match, don't do any further comparisons
if (userData.Columns.Count != answerData.Columns.Count)
{
DataDifferenceModel difference = new DataDifferenceModel();
difference.DifferenceType = DataDifferenceType.ColumnCountDifferent;
differences.Differences.Add(difference);
return(differences);
}
// Check that each of the columns is the same
Boolean foundDifferentColumnNames = false;
for (int i = 0; i < userData.Columns.Count; i++)
{
if (userData.Columns[i].ColumnName != answerData.Columns[i].ColumnName)
{
DataDifferenceModel difference = new DataDifferenceModel();
difference.DifferenceType = DataDifferenceType.ColumnMismatch;
difference.ColumnDifferencePosition = i;
difference.AnswerQueryColumn = answerData.Columns[i].ColumnName;
difference.UserQueryColumn = userData.Columns[i].ColumnName;
differences.Differences.Add(difference);
foundDifferentColumnNames = true;
}
}
if (foundDifferentColumnNames)
{
return(differences);
}
var answerDataLookup = new Dictionary <string, DataRow>();
foreach (DataRow row in answerData.Rows)
{
var hash = GetHash(row);
if (!answerDataLookup.ContainsKey(hash))
{
answerDataLookup.Add(hash, row);
}
}
// If the columns match, we can do a more detailed row comparison
for (int i = 0; i < userData.Rows.Count; i++)
{
Boolean foundInSamePosition = false;
Boolean foundAtAll = false;
Boolean similarMatchFound = false;
int answerQueryPosition = 0;
// First check if there was a match in the same position or not
if (answerData.Rows.Count > i && DataRowsSame(userData.Rows[i], answerData.Rows[i]))
{
foundInSamePosition = true;
foundAtAll = true;
}
// If it wasn't found there, check if it was in the other result set at all
if (!foundInSamePosition && !StopAtFirstDifference)
{
var dataHash = GetHash(userData.Rows[i]);
var identicalRowFound = answerDataLookup.ContainsKey(dataHash);
if (identicalRowFound)
{
var identicalRow = answerDataLookup[dataHash];
foundAtAll = true;
}
if (LookForSimilarRows)
{
for (int j = 0; j < answerData.Rows.Count; j++)
{
if (DataRowsSimilar(userData.Rows[i], answerData.Rows[j]))
{
similarMatchFound = true;
answerQueryPosition = j;
}
}
}
}
// Log a difference if it wasn't found in the same position
if (!foundInSamePosition)
{
DataDifferenceModel difference = new DataDifferenceModel();
difference.UserQueryRow = userData.Rows[i];
difference.UserQueryPosition = i;
if (foundAtAll)
{
difference.DifferenceType = DataDifferenceType.WrongOrder;
difference.AnswerQueryPosition = answerQueryPosition;
}
else if (similarMatchFound)
{
difference.DifferenceType = DataDifferenceType.SimilarMatchFound;
difference.AnswerQueryPosition = answerQueryPosition;
difference.AnswerQueryRow = answerData.Rows[answerQueryPosition];
}
else
{
difference.DifferenceType = DataDifferenceType.NotFound;
}
differences.Differences.Add(difference);
if (StopAtFirstDifference)
{
return(differences);
}
}
}
answerDataLookup = null;
return(differences);
}
public Boolean CheckAnswer(int QuestionId, string Query, int UserId, out DataDifferencesModel Differences, string AnswerQuery = "", string SqlToRunFirst = "")
{
ValidateUserQuery(Query);
QuestionModel question = _da.GetQuestion(UserId, QuestionId);
ExerciseModel exercise = _da.GetExercise(UserId, question.ExerciseId);
IQueryBuilder queryBuilder = QueryBuilderFactory.CreateQueryBuilder(SourceDatabaseType.TSQL);
if (String.IsNullOrEmpty(AnswerQuery))
{
AnswerQuery = question.AnswerTemplate;
}
String userQuery = queryBuilder.BuildQuery(Query, exercise, question);
String answerQuery = queryBuilder.BuildQuery(AnswerQuery, exercise, question);
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.DatabaseDataSourcesConnectionString))
{
try
{
conn.Open();
_sourceDA.BeginUserTransaction(conn);
// Do any commands that the user has already ran on this data source (Execute Query type only, not submitted answers)
if (UserId != -1)
{
List <UserActionModel> queries = _da.ListQuestionQueries(question.QuestionId, UserId);
foreach (UserActionModel query in queries)
{
try
{
String previouslyRunUserQuery = queryBuilder.BuildQuery(query.Description, exercise, question);
_sourceDA.ExecuteUserQuery(previouslyRunUserQuery, conn);
}
catch (Exception)
{
}
}
}
DataTable userQueryData = _sourceDA.GetDataTable(userQuery, conn);
_sourceDA.RollBackUserTransaction(conn);
_sourceDA.BeginUserTransaction(conn);
if (!string.IsNullOrEmpty(SqlToRunFirst))
{
String queryToRun = queryBuilder.BuildQuery(SqlToRunFirst, exercise, question);
_sourceDA.ExecuteUserQuery(queryToRun, conn);
}
DataTable answerQueryData = _sourceDA.GetDataTable(answerQuery, conn);
_sourceDA.RollBackUserTransaction(conn);
// Stop at the first difference if we are being called from the unit test runner
var stopAtFirstDifference = !String.IsNullOrEmpty(SqlToRunFirst);
Differences = new DifferenceEngine(@StopAtFirstDifference: stopAtFirstDifference).GetDifferences(userQueryData, answerQueryData);
}
catch (Exception ex)
{
throw new Exception("Error checking answer - " + ex.Message);
}
finally
{
_sourceDA.RollBackUserTransaction(conn);
}
}
// The answer is correct if there were no differences
return(Differences.Differences.Count == 0);
}
