public DataTable ExecuteUserQuery(int QuestionId, string Query, User User)
{
ValidateUserQuery(Query);
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.DatabaseDataSourcesConnectionString))
{
try
{
conn.Open();
var exerciseService = IocContainer.Container.GetInstance <IExerciseService>();
MultipleChoiceExercise exercise = (MultipleChoiceExercise)exerciseService.GetByQuestion(QuestionId, User);
var question = exercise.Questions.Single(x => x.ExerciseQuestionId == QuestionId);
// Start a transaction first
_sourceDA.BeginUserTransaction(conn);
IQueryBuilder queryBuilder = QueryBuilderFactory.CreateQueryBuilder(SourceDatabaseType.TSQL);
// Do any commands that the user has already ran on this data source (Execute Query type only, not submitted answers)
if (User.UserId != -1)
{
List <UserActionModel> queries = _da.ListQuestionQueries(question.ExerciseQuestionId, User.UserId);
foreach (UserActionModel query in queries)
{
try
{
String userQuery = queryBuilder.BuildQuery(query.Description, exercise, question);
_sourceDA.ExecuteUserQuery(userQuery, conn);
}
catch (Exception)
{
}
}
}
// Do the commands
String builtQuery = queryBuilder.BuildQuery(Query, exercise, question);
DataTable results = _sourceDA.GetDataTable(builtQuery, conn, 100);
return(results);
}
catch (Exception)
{
throw;
}
finally
{
// Roll back the transaction
_sourceDA.RollBackUserTransaction(conn);
}
}
}
public void QueryBuilderFactoryTests_should_return_tsql_query_builder_for_tsql()
{
IQueryBuilder result = QueryBuilderFactory.CreateQueryBuilder(SourceDatabaseType.TSQL);
Assert.IsTrue(result.GetType() == typeof(TSQLQueryBuilder));
}
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);
}