The Old Mysql Database

using MySql.Data.MySqlClient;
using System;
using LiteNetLib;

namespace Server
{
    public class DbAccount
    {


        private static MySqlConnection conn;

        private static string connString = "Server=" + Const.LOGIN_HOSTNAME + ";Port=" + Const.LOGIN_DBPORT + ";Database=" + Const.LOGIN_DATABASE + ";User=" + Const.LOGIN_USERNAME + ";Password=" + Const.LOGIN_PASSWORD + ";";

        public static String SetupConnection()
        {
            string status;
            try
            {
                conn = new MySqlConnection(connString);
                conn.Open();
                MySqlCommand command = conn.CreateCommand();
                command.CommandText = "TRUNCATE TABLE online_players; ALTER TABLE online_players AUTO_INCREMENT = 1;";
                command.ExecuteNonQuery();
                status = "OK";
            }
            catch (MySqlException e)
            {
                status = e.Message.ToString();
            }
            return status;
        }

        public static bool Login(int index, string username, string password)
        {
            conn = new MySqlConnection(connString);
            conn.Open();
            MySqlCommand command = conn.CreateCommand();
            command.CommandText = "Select * FROM accounts WHERE username='" + username + "' AND password='" + password + "'";
            MySqlDataReader rdr = command.ExecuteReader();
            if (rdr.Read())
            {
                Globals.account[index].AccountID = rdr.GetInt32("ID");
                rdr.Close();
                conn.Close();
                return true;
            }
            else
            {
                rdr.Close();
                conn.Close();
                return false;
            }
        }

        public static bool AccountExist(string username)
        {
            conn = new MySqlConnection(connString);
            conn.Open();
            MySqlCommand command = conn.CreateCommand();
            command.CommandText = "Select * FROM accounts WHERE username='" + username + "'";
            MySqlDataReader rdr = command.ExecuteReader();
            if (rdr.Read())
            {
                rdr.Close();
                conn.Close();
                return true;
            }
            else
            {
                rdr.Close();
                conn.Close();
                return false;
            }
        }

        public static Character[] tempCharacter = new Character[6];

        public static Character[] GetPlayerCharacters(int OwnerID)
        {

            for (int Slot = 0; Slot < 6; Slot++)
            {
                tempCharacter[Slot] = new Character();
                tempCharacter[Slot].ID = 0;
            }
            int Nr = 0;
            try
            {
                MySqlCommand command = conn.CreateCommand();
                command.CommandText = "SELECT CharID, Name, Race, Level FROM characters WHERE OwnerID = '" + OwnerID + "'";
                command.Parameters.AddWithValue("@OwnerID", OwnerID);
                conn.Open();
                MySqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    //[Personal Note] TRY USE THIS NOOB:   Character.CharacterArray[Nr].ID = Convert.ToInt32(reader["CharID"]);
                    tempCharacter[Nr].ID = Convert.ToInt32(reader["CharID"]);
                    tempCharacter[Nr].Name = Convert.ToString(reader["Name"]);
                    tempCharacter[Nr].Race = Convert.ToString(reader["Race"]);
                    tempCharacter[Nr].Level = Convert.ToInt32(reader["Level"]);
                    Nr++;
                }
                Debug.Log(TAG.DEBUG, "Characters loaded from DB [" + Nr + "]");
                reader.Close();
                conn.Close();
                return tempCharacter;
            }
            catch (Exception ex)
            {
                Console.WriteLine("DATA ERROR: " + ex.Message);
                return tempCharacter;
            }
        }

        public static bool Create(int index, string characterName, int characterRace)
        {
            conn = new MySqlConnection(connString);
            conn.Open();
            MySqlCommand command = conn.CreateCommand();
            command.CommandText = "Select * FROM characters WHERE Name='" + characterName + "'";
            MySqlDataReader rdr = command.ExecuteReader();
            if (rdr.Read())
            {
                rdr.Close();
                conn.Close();
                Globals.Client[index].Slot.Send(GameServer._netSerializer.Serialize(new Notification() { Message = "Character name already used. Please try another!" }), SendOptions.ReliableOrdered);
                return true;
            }
            else
            {
                rdr.Close();
                command.CommandText = "INSERT INTO `characters` (`OwnerID`, `Name`, `X`, `Y`, `Z`, `Race`, `Level`) VALUES(" + Globals.account[index].AccountID + ",'" + characterName + "','" + Const.DEFAULT_X_POS + "','" + Const.DEFAULT_Y_POS + "','" + Const.DEFAULT_Z_POS + "','" + characterRace + "','" + Const.DEFAULT_LEVEL + "')";
                command.ExecuteNonQuery();
                conn.Close();



                if (characterRace == 1)
                {
                    Globals.Client[index].Slot.Send(GameServer._netSerializer.Serialize(new Notification() { Message = "Character: " + characterName + " Race: " + Const.Races.Human.ToString() + " created successfully!" }), SendOptions.ReliableOrdered);
                }
                else if(characterRace == 2)
                {
                    Globals.Client[index].Slot.Send(GameServer._netSerializer.Serialize(new Notification() { Message = "Character: " + characterName + " Race: " + Const.Races.Elf.ToString() + " created successfully!" }), SendOptions.ReliableOrdered);
                }
                return false;
            }
        }

        public static void Load(int index, int CharID)
        {
            try
            {
                MySqlCommand command = conn.CreateCommand();
                command.CommandText = "SELECT CharID, OwnerID, Name, Race, Level, X, Y, Z FROM characters WHERE OwnerID = '" + Globals.account[index].AccountID + "' AND CharID = '" + CharID + "'";
                conn.Open();
                MySqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Globals.character[index].ID = Convert.ToInt32(reader["CharID"]);
                    Globals.character[index].OwnerID = Convert.ToInt32(reader["OwnerID"]);
                    Globals.character[index].Name = Convert.ToString(reader["Name"]);
                    Globals.character[index].Race = Convert.ToString(reader["Race"]);
                    Globals.character[index].Level = Convert.ToInt32(reader["Level"]);
                    Globals.character[index].playerPosition.X = Convert.ToInt32(reader["X"]);
                    Globals.character[index].playerPosition.Y = Convert.ToInt32(reader["Y"]);
                    Globals.character[index].playerPosition.Z = Convert.ToInt32(reader["Z"]);
                }
                Debug.Log(TAG.DEBUG, "Character loaded from DB [" + Globals.character[index].ID + "] [" + Globals.character[index].Name + "]");
                reader.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine("DATA ERROR: " + ex.Message);
            }
        }

        public bool DeleteCharacter(string charName)
        {
            try
            {
                conn.Open();
                MySqlCommand command = conn.CreateCommand();
                command.CommandText = "SELECT * FROM  characters where Name ='" + charName + "'";
                MySqlDataReader rdr = command.ExecuteReader();
                rdr.Close();
                conn.Close();
                return true;
            }
            catch (MySqlException e)
            {
                Console.WriteLine("SQL exception when trying to delete character");
                Console.WriteLine(e);
                return false;
            }
        }



        /// TO SEE WHAT THEY DO.

        //public static int GetSessionId(int characterId)
        //{
        //    MySqlCommand command = conn.CreateCommand();
        //    command.CommandText = "SELECT sessionId from online_players where charId=@charID";
        //    command.Parameters.AddWithValue("@characterId", characterId);
        //    MySqlDataReader rdr = command.ExecuteReader();
        //    rdr.Read();
        //    if (rdr.HasRows)
        //    {
        //        var sessionId = rdr.GetInt32(0);
        //        rdr.Close();

        //        return sessionId;
        //    }
        //    else
        //    {
        //        throw new Exception("Could not find session id for character id: " + characterId);
        //    }
        //}

        //public static void AddToOnlinePlayerList(int characterId, string clientAddress)
        //{
        //    MySqlCommand command = conn.CreateCommand();
        //    command.CommandText = "INSERT INTO online_players(`charId`, `accountId`, `name`, `ipAddress`) SELECT id,accountId,name,@ipAddress from login.characters where id=@characterId;";
        //    command.Parameters.AddWithValue("@characterId", characterId);
        //    command.Parameters.AddWithValue("@ipAddress", clientAddress);
        //    command.ExecuteNonQuery();
        //}

        //public static void UpdateCharacterPosition(int characterId, float xPos, float yPos, string zone)
        //{
        //    MySqlCommand command = conn.CreateCommand();
        //    command.CommandText = "UPDATE `character_positions` set`xPos`=@xPos,`yPos`=@yPos,`zone`=@zone WHERE `characterId`=@characterId";
        //    command.Parameters.AddWithValue("@characterId", characterId);
        //    command.Parameters.AddWithValue("@xPos", xPos);
        //    command.Parameters.AddWithValue("@yPos", yPos);
        //    command.Parameters.AddWithValue("@zone", zone);
        //    command.ExecuteNonQuery();
        //}


    }
}