using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;
using System.Data.SqlClient;
using System.Data.Common;

namespace digioz
{
    /// <summary>
    /// MSSQL Database Communication Class
    /// </summary>
    public class MYSQL : IDataRelational
    {
        #region "Variable Declaration"
        private string csHost;
        private string csName;
        private string csUser;
        private string csPass;

        public DataSet coDS;
        public MySqlDataAdapter coDA;
        public DataTable coDT = new DataTable();
        public MySqlCommandBuilder coCB;
        public MySqlConnection coCN;
        public MySqlDataReader coReader;
        public string cnString;
        public string csDLLVersion = "1.1.0";
        public string csErr;
        public int csErrNumber;
        public PortalSettings coPortalSettings;
        #endregion

        public MYSQL(PortalSettings poPortalSettings)
        {
            coPortalSettings = poPortalSettings;
            if (coPortalSettings.ConnectionString != String.Empty)
            {
                cnString = coPortalSettings.ConnectionString;
            }
            else
            {
                csHost = coPortalSettings.DBHost;
                csName = coPortalSettings.DBName;
                csUser = coPortalSettings.DBUser;
                csPass = coPortalSettings.DBPass;

                cnString = "Server=" + csHost + ";Database=" + csName + ";User ID=" + csUser + ";Password=" + csPass + ";Trusted_Connection=False;";
            }
        }

        public void openConnection()
        {
            coCN = new MySqlConnection(cnString);
            try
            {
                coCN.Open();
                csErr = "";
            }
            catch (MySqlException ex)
            {
                csErr = ex.Message.ToString();
            }
        }

        public void closeConnection()
        {
            coCN.Close();
        }

        public DataTable QueryDBDataset(string sql)
        {
            _QueryDBDataset(sql, null);
            return coDS.Tables[0];
        }

        public DataTable QueryDBDataset(string sql, DbParameter[] _par)
        {
            _QueryDBDataset(sql, _par);
            return coDS.Tables[0];
        }

        private DataTable _QueryDBDataset(string sql, DbParameter[] _par)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand(sql, coCN);
                if (_par != null)
                {
                    foreach (DbParameter p in _par)
                    {
                        cmd.Parameters.Add((SqlParameter)p);
                    }
                }
                coDA = new MySqlDataAdapter(cmd);
                coDS = new DataSet();
                coDA.SelectCommand = cmd;
                coCB = new MySqlCommandBuilder(coDA);
                coDA.Fill(coDS);
            }
            catch (MySqlException ex)
            {
                csErr = ex.Message.ToString();
            }

            return coDS.Tables[0];
        }

        public DataTable QueryDBDatasetProc(string sql, out int count, DbParameter[] _par)
        {
            count = 0;
            try
            {
                MySqlCommand cmd = new MySqlCommand(sql, coCN);  
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (DbParameter p in _par)
                {
                    cmd.Parameters.Add((SqlParameter)p);
                }
                coDA = new MySqlDataAdapter(cmd);
                coDS = new DataSet();
                coDA.SelectCommand = cmd;
                coCB = new MySqlCommandBuilder(coDA);
                coDA.Fill(coDS);
                count = Convert.ToInt32(cmd.Parameters["@Count"].Value);
            }
            catch (MySqlException ex)
            {
                csErr = ex.Message.ToString();
            }

            return coDS.Tables[0];
        }

        public DataTable GetDBDataset(string sql, string _host, string _name, string _user, string _pass)
        {
            csHost = _host;
            csName = _name;
            csUser = _user;
            csPass = _pass;

            openConnection();

            try
            {
                MySqlCommand cmd = new MySqlCommand(sql, coCN);
                coDA.SelectCommand = cmd;
                coCB = new MySqlCommandBuilder(coDA);
                coDA.Fill(coDS);
            }
            catch (MySqlException ex)
            {
                csErr += "Error: " + ex.Message.ToString();
                csErrNumber = ex.Number;
            }
            closeConnection();

            return coDS.Tables[0];
        }

        //public QueryDBReader(string sql)
        //{
        //    coReader = null;

        //    MySqlCommand cmd = new MySqlCommand(sql, coCN);
        //    try
        //    {
        //        coReader = cmd.ExecuteReader();
        //    }
        //    catch (MySqlException ex)
        //    {
        //        csErr = ex.Message.ToString();
        //    }
        //    finally
        //    {
        //        if ((coReader != null))
        //            coReader.Close();
        //    }

        //    return coReader;
        //}

        public object ExecDBScalar(string sql)
        {
            return _ExecDBScalar(sql, null);
        }

        public object ExecDBScalar(string sql, DbParameter[] _par)
        {
            return _ExecDBScalar(sql, _par);
        }

        private object _ExecDBScalar(string sql, DbParameter[] _par)
        {
            MySqlCommand cmd = new MySqlCommand(sql, coCN);
            object oReturn;

            try
            {
                if (_par != null)
                {
                    foreach (DbParameter p in _par)
                    {
                        cmd.Parameters.Add((SqlParameter)p);
                    }
                }

                // This creates an exception when return value is null.
                //oReturn = cmd.ExecuteScalar().ToString();
                oReturn = cmd.ExecuteScalar();
                if (oReturn != null)
                {
                    oReturn = oReturn.ToString();
                }
            }
            catch (MySqlException ex)
            {
                csErr = ex.Message.ToString();
                csErrNumber = ex.Number;
                oReturn = null;
            }

            return oReturn;
        }

        public void ExecDB(string sql)
        {
            int cnt;
            _ExecDB(sql, out cnt, null);
        }

        public void ExecDB(string sql, out int count, DbParameter[] _par)
        {
            _ExecDB(sql, out count, _par);
        }

        private void _ExecDB(string sql, out int count, DbParameter[] _par)
        {
            count = 0;
            try
            {
                MySqlCommand cmd = new MySqlCommand(sql, coCN);
                if (_par != null)
                {
                    foreach (DbParameter p in _par)
                    {
                        cmd.Parameters.Add((SqlParameter)p);
                    }
                }
                count = cmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                csErr = ex.Message.ToString();
            }
        }

        public void UpdateDBDataset()
        {
            try
            {
                coDT = coDS.Tables[0];
                DataTable changes = coDT.GetChanges();

                coDA.Update(changes);
                coDT.AcceptChanges();
            }
            catch (MySqlException ex)
            {
                csErr = ex.Message.ToString();
            }
        }

        public void DeleteDBDataset(int primarykey)
        {
            try
            {
                coDS.Tables[0].Rows[primarykey].Delete();
            }
            catch (MySqlException ex)
            {
                csErr += "Error: " + ex.Message.ToString();
                csErrNumber = ex.Number;
            }
        }

        public void CreateDatabase(string DatabaseName)
        {
            try
            {
                this.ExecDB("CREATE DATABASE " + DatabaseName + ";");
            }
            catch (MySqlException ex)
            {
                csErr = ex.Message.ToString();
            }
        }

        public string GetDLLVersion()
        {
            return "DigiOz MSSQL DLL Version " + csDLLVersion;
        }

        public void ExecDBProc(string sql, out int count, DbParameter[] _par)
        {
            MySqlCommand cmd = new MySqlCommand(sql, coCN);
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (DbParameter p in _par)
            {
                cmd.Parameters.Add((SqlParameter)p);
            }
            count = cmd.ExecuteNonQuery();
        }

        #region "Public Properties"
        public string dbhost
        {
            get { return csHost; }
            set { csHost = value; }
        }

        public string dbname
        {
            get { return csName; }
            set { csName = value; }
        }

        public string dbuser
        {
            get { return csUser; }
            set { csUser = value; }
        }

        public string dbpass
        {
            get { return csPass; }
            set { csPass = value; }
        }

        public string sErr
        {
            get
            {
                return csErr;
            }
        }
        #endregion
    }
}
