Esempio n. 1
0
        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();
            }
        }
Esempio n. 2
0
        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();
            }
        }
Esempio n. 3
0
        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();
            }
        }
Esempio n. 4
0
        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();
            }
        }
Esempio n. 5
0
        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();
            }
        }
Esempio n. 6
0
        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();
            }
        }
Esempio n. 7
0
        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);
        }
Esempio n. 8
0
        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);
        }
Esempio n. 9
0
        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);
        }
Esempio n. 10
0
        /// <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();
            }
        }
Esempio n. 11
0
        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);
            }
        }
Esempio n. 12
0
        //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);
            }
        }