public XmlDocument DbToXml(string username) { XmlDocument doc = new XmlDocument(); Postgre conn = new Postgre(); string sql = "SELECT * FROM examresult WHERE username=:username"; try { conn._cmd = new NpgsqlCommand(sql, conn._conn); conn._cmd.Parameters.AddWithValue("username", username); conn._dr = conn._cmd.ExecuteReader(); while (conn._dr.Read()) { doc.LoadXml(conn._dr["xml"].ToString()); } return(doc); } catch (NpgsqlException e) { Debug.Write(e); return(null); } finally { conn.CloseConnection(); } }
public string GetQuestionFromDB(int question_id) { Postgre conn = new Postgre(); string sql = "SELECT * FROM exam WHERE question_id=:question_id"; try { conn._cmd = new NpgsqlCommand(sql, conn._conn); conn._cmd.Parameters.AddWithValue("question_id", question_id); conn._dr = conn._cmd.ExecuteReader(); while (conn._dr.Read()) { question = conn._dr["question"] as string ?? ""; } return(question); } catch (NpgsqlException e) { Debug.Write(e); throw; } finally { conn.CloseConnection(); } }
public List <string> GetAnswersFromDB(int question) { Postgre conn = new Postgre(); string sql = "SELECT * FROM answers WHERE question=:question"; List <string> answers = new List <string>(); string answer = null; try { conn._cmd = new NpgsqlCommand(sql, conn._conn); conn._cmd.Parameters.AddWithValue("question", question); conn._dr = conn._cmd.ExecuteReader(); while (conn._dr.Read()) { answer = conn._dr["answer"] as string ?? ""; answers.Add(answer); } return(answers); } catch (Exception e) { Debug.Write(e); throw; } finally { conn.CloseConnection(); } }
public string GetCorrectAnswerTemp(int question) { Postgre conn = new Postgre(); string sql = "select * from answers a where a.question = @question and a.correct = true"; try { conn._cmd = new NpgsqlCommand(sql, conn._conn); conn._cmd.Parameters.AddWithValue("question", question); conn._dr = conn._cmd.ExecuteReader(); while (conn._dr.Read()) { answer = conn._dr["answer"] as string ?? ""; } return(answer); } catch (Exception e) { Debug.Write(e); throw; } finally { conn.CloseConnection(); } }
public string GetCategory(int question_id) { Postgre conn = new Postgre(); string sql = "select * from exam where question_id = @question_id"; try { conn._cmd = new NpgsqlCommand(sql, conn._conn); conn._cmd.Parameters.AddWithValue("question_id", question_id); conn._dr = conn._cmd.ExecuteReader(); while (conn._dr.Read()) { category = conn._dr["category"] as string ?? ""; } return(category); } catch (Exception e) { Debug.Write(e); throw; } finally { conn.CloseConnection(); } }
public bool GetLicenseApproved(string username) { Postgre conn = new Postgre(); string sql = "select * from person where username = @username"; bool approved = false; try { conn._cmd = new NpgsqlCommand(sql, conn._conn); conn._cmd.Parameters.AddWithValue("username", username); conn._dr = conn._cmd.ExecuteReader(); while (conn._dr.Read()) { approved = conn._dr["licensed"] as bool? ?? default(bool); } return(approved); } catch (Exception e) { Debug.Write(e); return(false); } finally { conn.CloseConnection(); } }
public DataTable GetUserList() { Postgre db = new Postgre(); DataTable dt = new DataTable(); string sql = "SELECT * FROM person WHERE isadmin = false"; dt = db.Select(sql); return(dt); }
public DataTable GetExam(string user) { Postgre db = new Postgre(); DataTable dt = new DataTable(); // Dictionary to send parameters in format key-value Dictionary <string, string> myParams = new Dictionary <string, string>(); myParams.Add("@uname", user); string sql = "select distinct on (\"username\") * from examresult WHERE username = @uname ORDER BY username, date DESC"; dt = db.Select(sql, myParams); return(dt); }
public DataTable AdmLogin() { Postgre db = new Postgre(); DataTable dt = new DataTable(); Dictionary <string, string> myParams = new Dictionary <string, string>(); myParams.Add("@username", Username); myParams.Add("@password", Password); // Sql query with parameters string sql = "SELECT * FROM person " + "WHERE username = @username AND " + "password = @password AND " + "isadmin = true"; dt = db.Select(sql, myParams); return(dt); }
/// <summary> /// Method to get all question IDs. /// </summary> /// <returns>List of all Question IDs</returns> public List <int> GetQuestionIDs(int examType) { Postgre conn = new Postgre(); string sql = ""; if (examType == 1 || examType == null) { sql = "select question_id from exam order by random() limit 25"; } else if (examType == 2) { sql = "select question_id from exam order by random() limit 15"; } List <int> ids = new List <int>(); int id = 0; try { conn._cmd = new NpgsqlCommand(sql, conn._conn); conn._dr = conn._cmd.ExecuteReader(); while (conn._dr.Read()) { id = conn._dr["question_id"] as int? ?? default(int); ids.Add(id); } return(ids); } catch (Exception e) { Debug.Write(e); throw; } finally { conn.CloseConnection(); } }
public bool updateLicense(string username, bool licensed) { Postgre conn = new Postgre(); string sql = "UPDATE person SET licensed = @licensed WHERE username = @username"; try { conn._cmd = new NpgsqlCommand(sql, conn._conn); conn._cmd.Parameters.Add(new NpgsqlParameter("licensed", licensed)); conn._cmd.Parameters.Add(new NpgsqlParameter("username", username)); if (conn._cmd.ExecuteNonQuery() == 1) { return(true); } return(false); } catch (NpgsqlException e) { Debug.Write(e); return(false); } }
//public DataTable GetCorrectAnswer(string question) //{ // //Postgre db = new Postgre(); // //DataTable dt = new DataTable(); // //// Dictionary to send parameters in format key-value // //Dictionary<string, string> myParams = new Dictionary<string, string>(); // //myParams.Add("@question_id", question); // //// Sql query with parameters // //string sql = "select * from exam e, answers a where e.correct_answer = a.answer_id and question_id = '@question'"; // //dt = db.Select(sql, myParams); // //return dt; //} public bool xmlToDb(string username, string xml, DateTime today) { Postgre conn = new Postgre(); string sql = "INSERT INTO examresult(username, xml, date) VALUES(@username, @xml, @today)"; try { conn._cmd = new NpgsqlCommand(sql, conn._conn); conn._cmd.Parameters.Add(new NpgsqlParameter("xml", xml)); conn._cmd.Parameters.Add(new NpgsqlParameter("username", username)); conn._cmd.Parameters.Add(new NpgsqlParameter("today", today)); if (conn._cmd.ExecuteNonQuery() == 1) { return(true); } return(false); } catch (NpgsqlException e) { Debug.Write(e); return(false); } }