예제 #1
0
        public List <GameModel> GetAll()
        {
            var db = new DB_Connection();
            List <GameModel> result = new List <GameModel>();

            try
            {
                db.OpenConnection();
                string          query      = "SELECT * FROM games";
                MySqlCommand    cmd        = new MySqlCommand(query, db.Connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list

                while (dataReader.Read())
                {
                    var game = new GameModel(dataReader);
                    result.Add(game);
                }

                //close Data Reader
                dataReader.Close();
                db.CloseConnection();
                return(result);
            }
            catch (MySqlException)
            {
                return(null);
            }
        }
예제 #2
0
        public string Delete(int id)
        {
            var db = new DB_Connection();

            //open connection

            try
            {
                db.OpenConnection();
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(null, db.Connection);

                cmd.CommandText = "DELETE FROM games WHERE gameid = @id";


                var idParam = new MySqlParameter("@id", MySqlDbType.Int32, 11);
                idParam.Value = id;
                cmd.Parameters.Add(idParam);



                // Call Prepare after setting the Commandtext and Parameters.
                cmd.Prepare();
                cmd.ExecuteNonQuery();

                //close connection
                db.CloseConnection();
                return("true");
            }
            catch (MySqlException ex)
            {
                return(ex.ToString());
            }
        }
예제 #3
0
        public int Add(string userid, string text)
        {
            var db = new DB_Connection();

            try
            {
                db.OpenConnection();
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(null, db.Connection);
                cmd.CommandText = "INSERT INTO games (userid, drawText)" + "VALUES(@userid, @drawText)";

                var userIdParam = new MySqlParameter("@userid", MySqlDbType.Int16, 45);
                userIdParam.Value = userid;
                var textParam = new MySqlParameter("@drawText", MySqlDbType.VarChar, 45);
                textParam.Value = text;



                cmd.Parameters.Add(userIdParam);
                cmd.Parameters.Add(textParam);

                // Call Prepare after setting the Commandtext and Parameters.
                cmd.Prepare();
                cmd.ExecuteNonQuery();
                int gameId = (int)cmd.LastInsertedId;

                //close connection
                db.CloseConnection();
                return(gameId);
            }
            catch (MySqlException)
            {
                return(-1);
            }
        }
예제 #4
0
        public Data indexInfo()
        {
            int users = 0;
            int games = 0;
            var db    = new DB_Connection();

            try
            {
                db.OpenConnection();
                string       query = "SELECT COUNT(*) AS count FROM users";
                MySqlCommand cmd   = new MySqlCommand(query, db.Connection);
                users = int.Parse(cmd.ExecuteScalar().ToString());

                query = "SELECT COUNT(*) AS countGames FROM games";
                cmd   = new MySqlCommand(query, db.Connection);
                games = int.Parse(cmd.ExecuteScalar().ToString());

                //close Data Reader
                db.CloseConnection();
                return(new Data(users, games));
            }
            catch (MySqlException)
            {
                return(new Data(users, games));
            }
        }
예제 #5
0
        public bool Edit(int id, string nickName, string email, string password, string avatar)
        {
            var db = new DB_Connection();

            //open connection

            try
            {
                db.OpenConnection();
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(null, db.Connection);

                cmd.CommandText = "UPDATE users  SET username= @userName, email= @email, password= @password, avatar= @avatar WHERE userId= @id";

                var userNameParam = new MySqlParameter("@userName", MySqlDbType.VarChar, 45);
                userNameParam.Value = nickName;
                var emailParam = new MySqlParameter("@email", MySqlDbType.VarChar, 45);
                emailParam.Value = email;
                var passwordParam = new MySqlParameter("@password", MySqlDbType.VarChar, 45);
                passwordParam.Value = password;
                var avatarParam = new MySqlParameter("@avatar", MySqlDbType.VarChar, 30);
                avatarParam.Value = avatar;
                var idParam = new MySqlParameter("@id", MySqlDbType.Int16, 30);
                idParam.Value = id;


                cmd.Parameters.Add(userNameParam);
                cmd.Parameters.Add(emailParam);
                cmd.Parameters.Add(passwordParam);
                cmd.Parameters.Add(avatarParam);
                cmd.Parameters.Add(idParam);

                // Call Prepare after setting the Commandtext and Parameters.
                cmd.Prepare();
                cmd.ExecuteNonQuery();

                //close connection
                db.CloseConnection();
                return(true);
            }catch (MySqlException)
            {
                return(false);
            }
        }
예제 #6
0
        public string UpdateCorrectGuess(string id, string guess)
        {
            var game = GetGame(int.Parse(id));

            if (game.DrawText == guess)
            {
                var db = new DB_Connection();
                //open connection

                try
                {
                    db.OpenConnection();
                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(null, db.Connection);

                    cmd.CommandText = "UPDATE games SET correctGuess= correctGuess + 1 WHERE gameid = @id";


                    var idParam = new MySqlParameter("@id", MySqlDbType.Int32, 11);
                    idParam.Value = id;

                    cmd.Parameters.Add(idParam);



                    // Call Prepare after setting the Commandtext and Parameters.
                    cmd.Prepare();
                    cmd.ExecuteNonQuery();

                    //close connection
                    db.CloseConnection();
                    return("true");
                }
                catch (MySqlException ex)
                {
                    return(ex.ToString());
                }
            }
            return("");
        }
예제 #7
0
        public bool Add(string nickName, string email, string password, string avatar)
        {
            var db = new DB_Connection();

            //open connection

            try
            {
                db.OpenConnection();
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(null, db.Connection);
                cmd.CommandText = "INSERT INTO users (username, email, password, avatar)" + "VALUES(@userName, @email, @password, @avatar)";

                var userNameParam = new MySqlParameter("@userName", MySqlDbType.VarChar, 45);
                userNameParam.Value = nickName;
                var emailParam = new MySqlParameter("@email", MySqlDbType.VarChar, 45);
                emailParam.Value = email;
                var passwordParam = new MySqlParameter("@password", MySqlDbType.VarChar, 45);
                passwordParam.Value = password;
                var avatarParam = new MySqlParameter("@avatar", MySqlDbType.VarChar, 30);
                avatarParam.Value = avatar;


                cmd.Parameters.Add(userNameParam);
                cmd.Parameters.Add(emailParam);
                cmd.Parameters.Add(passwordParam);
                cmd.Parameters.Add(avatarParam);

                // Call Prepare after setting the Commandtext and Parameters.
                cmd.Prepare();
                cmd.ExecuteNonQuery();

                //close connection
                db.CloseConnection();
                return(true);
            }catch (MySqlException)
            {
                return(false);
            }
        }
예제 #8
0
        public string Update(string id, string img)
        {
            var db = new DB_Connection();

            //open connection

            try
            {
                db.OpenConnection();
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(null, db.Connection);

                cmd.CommandText = "UPDATE games SET active= 0, img= @img WHERE gameid = @id";


                var imgParam = new MySqlParameter("@img", MySqlDbType.LongText, img.Length);
                imgParam.Value = img;

                var idParam = new MySqlParameter("@id", MySqlDbType.Int32, 11);
                idParam.Value = id;

                cmd.Parameters.Add(imgParam);
                cmd.Parameters.Add(idParam);



                // Call Prepare after setting the Commandtext and Parameters.
                cmd.Prepare();
                cmd.ExecuteNonQuery();

                //close connection
                db.CloseConnection();
                return("true");
            }
            catch (MySqlException ex)
            {
                return(ex.ToString());
            }
        }
예제 #9
0
        public Data indexInfo()
        {
            int activeGames = 0;
            var db          = new DB_Connection();

            try
            {
                db.OpenConnection();
                string       query = "SELECT COUNT(*) AS count FROM games WHERE active = 1";
                MySqlCommand cmd   = new MySqlCommand(query, db.Connection);
                activeGames = int.Parse(cmd.ExecuteScalar().ToString());



                //close Data Reader
                db.CloseConnection();
                return(new Data(activeGames, 0));
            }
            catch (MySqlException)
            {
                return(new Data(0, 0));
            }
        }