BattleSQL

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using BattleCore;
using BattleCore.Events;
using BattleCorePsyOps;

using MySql.Data.MySqlClient;
using System.Diagnostics;

namespace BZControl
{
    public class DBManager
    {
        public DBManager()
        {
            server = "server.com";
            database = "DBName";
            uid = "UIDName";
            password = "MyPassWord";
            string connectionString;
            connectionString = "SERVER=" + server + ";" + "DATABASE=" +
            database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

            connection = new MySqlConnection(connectionString);
        }

        private MySqlConnection connection;
        private string server;
        private string database;
        private string uid;
        private string password;

        ShortChat msg = new ShortChat();

        public void SQLTest(Queue<EventArgs> q)
        {
                        //open connection
            if (this.OpenConnection(q) == true)
            {
                //string query = "UPDATE subspace_bz_arenalist SET playername='PsyOps', status='0' WHERE status='0'";
                //string query = "INSERT INTO subspace_bz_arenalist (playername,status,frequency) VALUES('Hallowed.', '1', '1')";
                string query = "UPDATE subspace_bz_arenalist SET status='5', frequency='9' WHERE playername='PsyOps'";
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //Execute command
                cmd.ExecuteNonQuery();

                //close connection
                this.connection.Close();

                q.Enqueue(msg.arena("Database update success."));
            }

        }

        public void AddPLayerArenaList(Queue<EventArgs> q, MainPlayer Player)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand("INSERT INTO subspace_bz_arenalist (playername,status,frequency) VALUES('" + Player.PlayerName + "', '" + (int)Player.CurrentShip + "', '" + (int)Player.Freq + "')", connection);
                //Execute command
                cmd.ExecuteNonQuery();

                q.Enqueue(msg.arena("Player [ "+ Player.PlayerName +" ] Updated."));
            }
            catch (MySqlException ex)
            {
                q.Enqueue(msg.arena("Error: " + ex));
                if (this.OpenConnection(q) == true)
                {
                    MySqlCommand cmd = new MySqlCommand("INSERT INTO subspace_bz_arenalist (playername,status,frequency) VALUES('" + Player.PlayerName + "', '" + (int)Player.CurrentShip + "', '" + (int)Player.Freq + "')", connection);
                    //Execute command
                    cmd.ExecuteNonQuery();

                    q.Enqueue(msg.arena("Connection had to reconnect. Player [ " + Player.PlayerName + " ] Updated."));
                }
                else
                    q.Enqueue(msg.arena("Update Failed."));
            }
        }

        public void InitiateDatabase(Queue<EventArgs> q, List<MainPlayer> MasterList)
        {
            if (this.OpenConnection(q) == true)
            {
                string query = "TRUNCATE table subspace_bz_arenalist";
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Execute command
                cmd.ExecuteNonQuery();

                for (int i = 0; i < MasterList.Count; i += 1)
                {
                    cmd = new MySqlCommand("INSERT INTO subspace_bz_arenalist (playername,status,frequency) VALUES('" + MasterList[i].PlayerName + "', '" + (int)MasterList[i].CurrentShip + "', '" + (int)MasterList[i].Freq + "')", connection);
                    cmd.ExecuteNonQuery();
                }

                q.Enqueue(msg.arena("Database Initiate success."));
            }
        }

        //open connection to database
        public bool OpenConnection(Queue<EventArgs> q)
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                //When handling errors, you can your application's response based
                //on the error number.
                //The two most common error numbers when connecting are as follows:
                //0: Cannot connect to server.
                //1045: Invalid user name and/or password.
                switch (ex.Number)
                {
                    case 0:
                        q.Enqueue(msg.arena("Cannot connect to server.  Contact administrator"));
                        break;

                    case 1045:
                        q.Enqueue(msg.arena("Invalid username/password, please try again"));
                        break;
                    default:
                        q.Enqueue(msg.arena("Error: Num[" + ex.Number + "]"));
                        break;
                }
                return false;
            }
        }
        //Close connection
        public bool CloseConnection(Queue<EventArgs> q)
        {
            try
            {
                connection.Close();
                q.Enqueue(msg.arena("Connection Closed."));
                return true;
            }
            catch (MySqlException ex)
            {
                q.Enqueue(msg.arena(ex.Message));
                return false;
            }
        }
    }
}