public void UpdateQuestion(Question q)
{
try {
using (var con = DBUtils.GetConnection()) {
con.Open();
using (var cmd = new SqlCommand("UpdateQuestion", con)) {
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", q.ID);
cmd.Parameters.AddWithValue("@answer", q.Answer);
cmd.Parameters.AddWithValue("@answerA", q.AnsA);
cmd.Parameters.AddWithValue("@answerB", q.AnsB);
cmd.Parameters.AddWithValue("@answerC", q.AnsC);
cmd.Parameters.AddWithValue("@answerD", q.AnsD);
cmd.Parameters.AddWithValue("@queContent", q.Content);
cmd.Parameters.AddWithValue("@qTime", q.Time);
if (string.IsNullOrEmpty(q.Image))
{
q.Image = "default.png";
}
cmd.Parameters.AddWithValue("@qImage", q.Image);
cmd.ExecuteNonQuery();
}
}
} catch (Exception e) {
throw e;
}
}
public void AddNewQuiz(Quiz q)
{
try {
using (var con = DBUtils.GetConnection()) {
con.Open();
using (var cmd = new SqlCommand("AddNewQuiz", con)) {
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", q.ID);
cmd.Parameters.AddWithValue("@creator", q.Creator);
cmd.Parameters.AddWithValue("@title", q.Title);
cmd.Parameters.AddWithValue("@image", q.Image);
cmd.Parameters.AddWithValue("@desp", q.Desp);
cmd.ExecuteNonQuery();
}
}
QuestionDM dM = new QuestionDM();
foreach (var item in q.QuestionList)
{
dM.AddQuestion(item);
}
} catch (Exception e) {
throw e;
}
}
public void DeleteQuestionsOfAQuiz(int quizID)
{
try {
using (var con = DBUtils.GetConnection()) {
con.Open();
using (var cmd = new SqlCommand("DeleteQuestionsOfAQuiz", con)) {
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", quizID);
cmd.ExecuteNonQuery();
}
}
} catch (Exception e) {
throw e;
}
}
public List <Quiz> LoadQuizzesPerPage(int quizPerPage, int pageNo)
{
List <Quiz> list = null;
try {
using (var con = DBUtils.GetConnection()) {
con.Open();
using (var cmd = new SqlCommand("LoadQuizzesPerPage", con)) {
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@offset", (pageNo - 1) * quizPerPage);
cmd.Parameters.AddWithValue("@quizPerPage", quizPerPage);
using (var reader = cmd.ExecuteReader()) {
if (reader.HasRows)
{
QuestionDM questionDM = new QuestionDM();
while (reader.Read())
{
int quizID = (int)reader["id"];
Quiz q = new Quiz {
Creator = (string)reader["creator"],
ID = quizID,
Title = (string)reader["title"],
Image = reader["image"] == DBNull.Value
? "default.png" : (string)reader["image"],
Desp = reader["description"] == DBNull.Value
? "No Caterogy" : (string)reader["description"],
Plays = reader["plays"] == DBNull.Value
? 0 : (int)reader["plays"],
QuestionList = questionDM.FindQuestionByQuizID(quizID)
};
if (list == null)
{
list = new List <Quiz>();
}
list.Add(q);
}
}
}
}
}
} catch (Exception e) {
throw e;
}
return(list);
}
public void UpdatePlays(Quiz q)
{
try {
using (var con = DBUtils.GetConnection()) {
con.Open();
string sql = "UPDATE Quiz SET plays = @plays WHERE ID = @id";
using (var cmd = new SqlCommand(sql, con)) {
cmd.Parameters.AddWithValue("@id", q.ID);
cmd.Parameters.AddWithValue("@plays", q.Plays);
cmd.ExecuteNonQuery();
}
}
} catch (Exception e) {
throw e;
}
}
public List <Question> FindQuestionByQuizID(int id)
{
List <Question> list = null;
try {
using (var con = DBUtils.GetConnection()) {
con.Open();
using (var cmd = new SqlCommand("FindQuestionsOfAQuiz", con)) {
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@quizID", id);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Question q = new Question {
ID = (int)reader["id"],
QuizID = (int)reader["quizID"],
Content = (string)reader["queContent"],
AnsA = (string)reader["answerA"],
AnsB = (string)reader["answerB"],
AnsC = (string)reader["answerC"],
AnsD = (string)reader["answerD"],
Answer = (string)reader["answer"],
Image = reader["qimage"] == DBNull.Value ?
"default.png" : (string)reader["qimage"],
Time = reader["qtime"] == DBNull.Value ? 0 : (int)reader["qtime"],
Type = (int)reader["qtype"]
};
if (list == null)
{
list = new List <Question>();
}
list.Add(q);
}
}
}
}
} catch (Exception e) {
throw e;
}
return(list);
}
public int GetMaxID()
{
try {
using (var con = DBUtils.GetConnection()) {
con.Open();
using (var cmd = new SqlCommand("GetMaxQuestionID", con)) {
var reader = cmd.ExecuteReader();
if (reader.Read())
{
return((int)reader[0]);
}
return(-1);
}
}
} catch (Exception e) {
throw e;
}
}
public void UpdateDescription(Quiz q)
{
try {
using (var con = DBUtils.GetConnection()) {
con.Open();
using (var cmd = new SqlCommand("UpdateDescription", con)) {
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", q.ID);
cmd.Parameters.AddWithValue("@title", q.Title);
cmd.Parameters.AddWithValue("@image", q.Image);
cmd.Parameters.AddWithValue("@desp", q.Desp);
cmd.ExecuteNonQuery();
}
}
} catch (Exception e) {
throw e;
}
}
public List <Quiz> FindQuizzesByName(string searchName)
{
List <Quiz> list = null;
try {
using (var con = DBUtils.GetConnection()) {
using (var cmd = new SqlCommand("FindQuizzesByName", con)) {
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@name", searchName);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (list == null)
{
list = new List <Quiz>();
}
Quiz q = new Quiz {
ID = (int)reader["id"],
Title = (string)reader["title"],
Image = (string)reader["image"],
Desp = reader["description"] == DBNull.Value
? "No Caterogy" : (string)reader["description"],
Plays = reader["plays"] == DBNull.Value
? 0 : (int)reader["plays"],
Creator = (string)reader["creator"]
};
list.Add(q);
}
}
}
} catch (Exception e) {
throw e;
}
return(list);
}
public Quiz FindQuizByID(int id)
{
try {
using (var con = DBUtils.GetConnection()) {
con.Open();
using (var cmd = new SqlCommand("FindQuizByID", con)) {
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", id);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
QuestionDM questionDM = new QuestionDM();
if (reader.Read())
{
int quizID = (int)reader["id"];
Quiz q = new Quiz {
Creator = (string)reader["creator"],
ID = quizID,
Title = (string)reader["title"],
Image = reader["image"] == DBNull.Value
? "default.png" : (string)reader["image"],
Desp = reader["description"] == DBNull.Value
? "No Caterogy" : (string)reader["description"],
Plays = reader["plays"] == DBNull.Value
? 0 : (int)reader["plays"],
QuestionList = questionDM.FindQuestionByQuizID(quizID)
};
return(q);
}
}
}
}
} catch (Exception e) {
throw e;
}
return(null);
}
public bool checkDuplicateAccount(string username)
{
bool Successful = false;
SqlConnection cnn = DBUtils.GetConnection();
try {
string SQL = "select * from Account where username = @username";
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
SqlCommand cmd = new SqlCommand(SQL, cnn);
cmd.Parameters.AddWithValue("@username", username);
Console.Write(cmd);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
Successful = true;
}
} catch (Exception e) {
Console.WriteLine(e);
throw;
} finally {
cnn.Close();
}
return(Successful);
}
