using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using MySql;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using System.Threading;

namespace WindowsFormsApplication3.Class
{
    class MySQLConnection
    {
        string MySQLConnectorString = "";
        string[][] results;
        string user, pass, host, database;
        int resultsCount;
        int totalResults;

        int connection_count = 0;

        public int num_rows(string Query)
        {
            int totalResultsi = 0;
            MySqlConnection connection = new MySqlConnection(MySQLConnectorString);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = Query;
            connection.Open();
            connection_count++;

            MySqlDataReader Reader = command.ExecuteReader();

            while (Reader.Read())
            {
                totalResultsi++;
            }
            connection.Close();
            connection_count--;
            return totalResultsi;
        }

        public bool connect(string username = "root", string password = "", string host = "localhost")
        {
            // set the connection string
            this.host = host;
            this.pass = password;
            this.user = username;
            string MySQLConnectorString = "SERVER=" + host + ";";
            MySQLConnectorString += "UID=" + username + ";";
            MySQLConnectorString += "PASSWORD=" + password;

            // test the connection details
            MySqlConnection connection = new MySqlConnection(MySQLConnectorString);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SHOW DATABASES";
            bool ret = false;
            try
            {
                connection.Open();
                connection_count++;
                MySqlDataReader Reader = command.ExecuteReader();
                connection.Close();
                connection_count--;
                ret = true;
            }
            catch (Exception e)
            {
                ret = false;
            }
            this.MySQLConnectorString = MySQLConnectorString;
            return ret;
        }

        public bool connect(string username = "root", string password = "", string host = "localhost", string database = "")
        {
            // set the connection string
            this.host = host;
            this.pass = password;
            this.user = username;
            this.database = database;
            string MySQLConnectorString = "SERVER=" + this.host + ";";
            MySQLConnectorString += "DATABASE=" + this.database + ";";
            MySQLConnectorString += "UID=" + this.user + ";";
            MySQLConnectorString += "PASSWORD=" + pass;

            MessageBox.Show(MySQLConnectorString);

            // test the connection details
            MySqlConnection connection = new MySqlConnection(MySQLConnectorString);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SHOW TABLES";
            bool ret = false;
            try
            {
                connection.Open();
                connection_count++;
                MySqlDataReader Reader = command.ExecuteReader();
                connection.Close();
                connection_count--;
                ret = true;
            }
            catch (Exception e)
            {
                ret = false;
            }
            this.MySQLConnectorString = MySQLConnectorString;
            return ret;
        }

        public bool select_db(string database = "")
        {
            // set the connection string
            this.database = database;
            string MySQLConnectorString = "SERVER=" + host + ";";
            MySQLConnectorString += "DATABASE=" + database + ";";
            MySQLConnectorString += "UID=" + user + ";";
            MySQLConnectorString += "PASSWORD=" + pass;

            // test the connection details
            MySqlConnection connection = new MySqlConnection(MySQLConnectorString);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SHOW TABLES";
            bool ret = false;
            try
            {
                connection.Open();
                connection_count++;
                MySqlDataReader Reader = command.ExecuteReader();
                connection.Close();
                connection_count--;
                ret = true;
            }
            catch (Exception e)
            {
                ret = false;
            }
            this.MySQLConnectorString = MySQLConnectorString;
            return ret;
        }

        private void callback(string[] values)
        {
            results[resultsCount] = values;
            resultsCount++;
        }

        public string[][] getResults()
        {
            return results;
        }

        public void Query(string Query)
        {
            if (Query.IndexOf("INSERT") != -1)
            {
                totalResults = 0;
                resultsCount = 0;
                MySqlConnection connection = new MySqlConnection(MySQLConnectorString);
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = Query;
                connection.Open();
                connection_count++;
                MySqlDataReader Reader;
                try
                {
                    Reader = command.ExecuteReader();
                }
                catch (Exception Ex)
                {
                    //MessageBox.Show(Ex.Message);
                    return;
                }
                connection.Close();
                connection_count--;
            }
            else if (Query.IndexOf("SELECT") != -1)
            {
                totalResults = 0;
                resultsCount = 0;

                MySqlConnection connection = new MySqlConnection(MySQLConnectorString);
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = Query;
                connection.Open();
                connection_count++;
                MySqlDataReader Reader;
                try
                {
                    Reader = command.ExecuteReader();
                }catch(Exception Ex){
                    //MessageBox.Show(Ex.Message);
                    return;
                }
                while (Reader.Read())
                {
                    totalResults++;
                }

                results = new string[totalResults][];
                connection.Close();
                connection_count--;

                connection.Open();
                connection_count++;
                Reader = command.ExecuteReader();
                while (Reader.Read())
                {
                    string thisrow = "";
                    for (int i = 0; i < Reader.FieldCount; i++)
                    {
                        thisrow += Reader.GetValue(i).ToString() + ",";
                    }
                    this.callback(thisrow.Split(','));

                }
                connection.Close();
                connection_count--;
            }
            else if (Query.IndexOf("UPDATE") != -1)
            {
                totalResults = 0;
                resultsCount = 0;
                MySqlConnection connection = new MySqlConnection(MySQLConnectorString);
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = Query;
                connection.Open();
                connection_count++;
                MySqlDataReader Reader;
                try
                {
                    Reader = command.ExecuteReader();
                }
                catch (Exception Ex)
                {
                    //MessageBox.Show(Ex.Message);
                    return;
                }
                connection.Close();
                connection_count--;
            }
            else if (Query.IndexOf("DELETE") != -1)
            {
                totalResults = 0;
                resultsCount = 0;
                MySqlConnection connection = new MySqlConnection(MySQLConnectorString);
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = Query;
                connection.Open();
                connection_count++;
                MySqlDataReader Reader;
                try
                {
                    Reader = command.ExecuteReader();
                }
                catch (Exception Ex)
                {
                    //MessageBox.Show(Ex.Message);
                    return;
                }
                connection.Close();
                connection_count--;
            }
        }
    }
}