public IEnumerable <Survey_Question> GetSurvey_Questions(Survey s)
{
var survey_questions = new List <Survey_Question>();
using (SqlConnection sqlConnection1 = new SqlConnection(_connectionString)) // using permet de refermer la connection après ouverture
{
//ouvre la connection à la base de donnée.
var cmd = new SqlCommand // objet cmd me permet d'exécuter des requêtes SQL
{
CommandType = CommandType.Text, // methode permettant de definir le type de commande (text = une commande sql; Storeprocedure= le nom de la procedure stockée; TableDirect= le nom d'une table.
CommandText = "SELECT QuestionId FROM Survey_Question WHERE SurveyId = @surveyId ;", // stock la requete sql dans commandText. SCOPE_IDENTITY renvoie l'Id de la question inseré.
Connection = sqlConnection1, // etablie la connection.
};
// permet de definir les variables values dans CommandText.
cmd.Parameters.AddWithValue("@SurveyId", s.Id);
sqlConnection1.Open();
var result = cmd.ExecuteReader(); // execute la requete et return l'element de la première ligne à la première colonne
while (result.Read())
{
var survey_question = new Survey_Question()
{
SurveyId = Convert.ToInt32(result["SurveyId"]),
QuestionId = Convert.ToInt32(result["QuestionId"])
};
survey_questions.Add(survey_question);
}
;
}
return(survey_questions);
}
public Answer GetById(int answerId)
{
// Use ADO.Net to DB access
//objet de connection
using (SqlConnection sqlConnection1 = new SqlConnection(_connectionString))
{
// Do work here
SqlCommand cmd = new SqlCommand // objet cmd me permet d'exécuter des requêtes SQL
{
CommandType = CommandType.Text, // methode permettant de definir le type de commande (text = une commande sql; Storeprocedure= le nom de la procedure stockée; TableDirect= le nom d'une table.
CommandText = "SELECT QuestionId,Content FROM Answer WHERE Id = @answerId;", // stock la requete sql dans commandText. SCOPE_IDENTITY renvoie l'Id de la question inseré.
Connection = sqlConnection1, // etablie la connection.
};
cmd.Parameters.AddWithValue("@answerId", answerId);
sqlConnection1.Open();
var result = cmd.ExecuteReader();
result.Read();
var answer = new Answer
{
Id = Convert.ToInt32(result["Id"]),//recupère l'id de question dans la database et le converti
QuestionId = Convert.ToInt32(result["QuestonId"]),
Content = result["Content"].ToString()
};
return(answer);
}
}
public Survey SelectSurveyInfo(int surveyId)
{
using (SqlConnection sqlConnection1 = new SqlConnection(_connectionString)) // using permet de refermer la connection après ouverture
{
SqlCommand cmd = new SqlCommand // objet cmd me permet d'exécuter des requêtes SQL
{
CommandType = CommandType.Text, // methode permettant de definir le type de commande (text = une commande sql; Storeprocedure= le nom de la procedure stockée; TableDirect= le nom d'une table.
CommandText = "SELECT Name, SurveyEvaluation, CreationDate FROM Survey Where Id = @Id",
Connection = sqlConnection1, // etablie la connection.
};
sqlConnection1.Open();
cmd.Parameters.AddWithValue("@Id", surveyId);
var resultReader = cmd.ExecuteReader();
resultReader.Read();
var s = new Survey()
{
Name = resultReader["Name"].ToString(),
SurveyEvaluation = resultReader["SurveyEvaluation"]?.ToString(),
};
if (DateTime.TryParse(resultReader["CreationDate"]?.ToString(), out var creationDate))
{
s.CreationDate = creationDate;
}
return(s);
}
}
public Question GetBydId(int questionId)
{
// Use ADO.Net to DB access
//objet de connection
using (SqlConnection sqlConnection1 = new SqlConnection(_connectionString))
{
// Do work here
SqlCommand cmd = new SqlCommand // objet cmd me permet d'exécuter des requêtes SQL
{
CommandType = CommandType.Text, // methode permettant de definir le type de commande (text = une commande sql; Storeprocedure= le nom de la procedure stockée; TableDirect= le nom d'une table.
CommandText = @"
SELECT
q.Id,
q.QuestionCategoryId,
q.QuestionDifficultyId,
q.QuestionTypeId,
q.Content,
c.Name AS QuestionCategoryName,
d.QuestionDifficultyLevel ,
qt.Name AS QuestionTypeName,
a.content AS AnwserContent
FROM Question q
INNER JOIN QuestionDifficulty d ON q.QuestionDifficultyId = d.Id
INNER JOIN QuestionType qt ON q.QuestionTypeId = qt.Id
INNER JOIN QuestionCategory c ON q.QuestionCategoryId = c.Id
INNER JOIN Answer a ON q.Id = a.QuestionId
WHERE q.Id = @questionId",
Connection = sqlConnection1, // etablie la connection.
};
cmd.Parameters.AddWithValue("@questionId", questionId);
sqlConnection1.Open();
var result = cmd.ExecuteReader();
result.Read();
var question = new Question()
{
Id = Convert.ToInt32(result["Id"]),//recupère l'id de question dans la database et le converti
Category = new QuestionCategory()
{
Id = Convert.ToInt32(result["QuestionCategoryId"]),
Name = result["QuestionCategoryName"].ToString()
},
Content = result["Content"].ToString(),
Difficulty = new QuestionDifficulty()
{
Id = Convert.ToInt32(result["QuestionDifficultyId"]),
DifficultyLevel = result["QuestionDifficultyLevel"].ToString()
},
Type = new QuestionType()
{
Id = Convert.ToInt32(result["QuestionTypeId"]),
Name = result["QuestionTypeName"].ToString()
}
};
//question.Answers = _answerDao.GetByQuestionId(question.Id);
return(question);
}
}
public IEnumerable <Question> GetSurvey_Questions(int surveyId)
{
IEnumerable <Question> questions = new List <Question>();
IEnumerable <Answer> answers = new List <Answer>();
using (SqlConnection sqlConnection1 = new SqlConnection(_connectionString)) // using permet de refermer la connection après ouverture
{
//ouvre la connection à la base de donnée.
var cmd = new SqlCommand // objet cmd me permet d'exécuter des requêtes SQL
{
CommandType = CommandType.Text, // methode permettant de definir le type de commande (text = une commande sql; Storeprocedure= le nom de la procedure stockée; TableDirect= le nom d'une table.
CommandText = "SELECT QuestionId FROM Survey_Question WHERE SurveyId = @surveyId;", // stock la requete sql dans commandText. SCOPE_IDENTITY renvoie l'Id de la question inseré.
Connection = sqlConnection1, // etablie la connection.
};
// permet de definir les variables values dans CommandText.
cmd.Parameters.AddWithValue("@SurveyId", surveyId);
sqlConnection1.Open();
var result = cmd.ExecuteReader(); // execute la requete et return l'element de la première ligne à la première colonne
var survey_questions = new List <Survey_Question>();
while (result.Read())
{
var survey_question = new Survey_Question()
{
SurveyId = surveyId,
QuestionId = Convert.ToInt32(result["QuestionId"])
};
survey_questions.Add(survey_question);
}
if (survey_questions.Any())
{
questions = _questionDao.GetAll()?.Where(q => survey_questions.Any(sq => sq.QuestionId == q.Id));
}
//une autre manière de recupération de question.
//foreach (var element in survey_questions)
//{
// var getQuestionById = _questionDao.GetBydId(element.QuestionId);
// var
// foreach (var question in questions)
// {
// var q = new Question()
// {
// Id = getQuestionById.Id,
// Content = getQuestionById.Content,
// Category = getQuestionById.Category,
// Difficulty = getQuestionById.Difficulty,
// Type = getQuestionById.Type
// };
// questions.Add(q);
// }
//}
//};
}
return(questions);
}