using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;


namespace CSharpToMySQL
{
	public partial class frmMain : Form
	{
		private System.Data.Odbc.OdbcConnection OdbcCon;
		private System.Data.Odbc.OdbcCommand OdbcCom;
        private System.Data.Odbc.OdbcCommand OdbcComAlt;
		private System.Data.Odbc.OdbcDataReader OdbcDR;
        private Microsoft.Office.Interop.Excel.Workbook wb;
        private Microsoft.Office.Interop.Excel.Worksheet sh;
        private Microsoft.Office.Interop.Excel.Range rng;
		private string ConStr;
		private Form frmAbout;

		public frmMain()
		{
			InitializeComponent();
		}

		private void btnConnect_Click(object sender, EventArgs e)
		{
			ConStr = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" + txtIP.Text + ";PORT=" + txtPort.Text + ";DATABASE=" + txtDatabase.Text + ";UID=" + txtUsername.Text + ";PWD=" + txtPassword.Text + ";OPTION=3";
			OdbcCon = new System.Data.Odbc.OdbcConnection(ConStr);
			btnListTables.Enabled = true;
            btnShowTableColumns.Enabled = true;
            btnShowProductPrice.Enabled = true;
            btnPriceUpdate.Enabled = true;

			try
			{
				txtLog.AppendText("Openning connection...\r\n");
				if (OdbcCon.State == ConnectionState.Closed)
				{
					OdbcCon.Open();
				}
				txtLog.AppendText("Connection opened\r\n");
			}
			catch (System.Data.Odbc.OdbcException Ex)
			{
				txtLog.AppendText(Ex.Message + "\r\n");
				MessageBox.Show("Could not access the database.\r\nPlease make sure you completed the fields with the correct information and try again.\r\n\r\nMore details:\r\n" + Ex.Message, "Database connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
		}

		private void btnListTables_Click(object sender, EventArgs e)
		{
			if (OdbcCon.State == ConnectionState.Open)
			{
				OdbcCom = new System.Data.Odbc.OdbcCommand("SHOW TABLES", OdbcCon);
				OdbcDR = OdbcCom.ExecuteReader();
				txtLog.AppendText("Tables inside " + txtDatabase.Text + ":\r\n");
				while (OdbcDR.Read())
				{
					txtLog.AppendText(">> " + OdbcDR[0] + "\r\n");
				}
			}
		}
        private void btnShowTablesColumns_Click(object sender, EventArgs e)
        {
            
            if (OdbcCon.State == ConnectionState.Open)
            {
                OdbcCom = new System.Data.Odbc.OdbcCommand("SHOW COLUMNS FROM " + txtTable.Text, OdbcCon);
                OdbcDR = OdbcCom.ExecuteReader();
                txtLog.AppendText("Columns inside " + txtTable.Text + ":\r\n");
                while (OdbcDR.Read())
                {
                    txtLog.AppendText(">> " + OdbcDR[0] + "\r\n");
                }

            }
        }

        private void btnShowProductPrice_Click(object sender, EventArgs e)
        {

            if (OdbcCon.State == ConnectionState.Open)
            {
                OdbcCom = new System.Data.Odbc.OdbcCommand("SELECT `products_price` FROM `products` WHERE `products_model`='" + txtProduct.Text + "'", OdbcCon);
                OdbcDR = OdbcCom.ExecuteReader();
                txtLog.AppendText("The Price for " + txtProduct.Text + " is:\r\n");
                while (OdbcDR.Read())
                {
                    txtLog.AppendText(">> " + OdbcDR[0] + "\r\n");
                }

            }
        }

        private void btnPriceUpdate_Click(object sender, EventArgs e)
        {

            if (OdbcCon.State == ConnectionState.Open)
            {
                OdbcCom = new System.Data.Odbc.OdbcCommand("UPDATE `products` SET `products_price`='" + txtPrice.Text + "' WHERE `products_model`='" + txtProduct.Text + "'", OdbcCon);
                OdbcDR = OdbcCom.ExecuteReader();
                OdbcComAlt = new System.Data.Odbc.OdbcCommand("SELECT `products_price` FROM `products` WHERE `products_model`='" + txtProduct.Text + "'", OdbcCon);
                OdbcDR = OdbcComAlt.ExecuteReader();
                txtLog.AppendText("The New Price for " + txtProduct.Text + " is:\r\n");
                while (OdbcDR.Read())
                {
                    txtLog.AppendText(">> " + OdbcDR[0] + "\r\n");
                }

            }
        }
        
        private void btnExcelValue_Click(object sender, EventArgs e)
        {

            if (OdbcCon.State == ConnectionState.Open)
            {
                wb = Microsoft.Office.Interop.Excel.Workbooks.Open(@"c:\test.xls",1);                      
                sh = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets(1);
                rng = sh.get_Range("A1","A1");
                rng.Value2.ToString();
                txtLog.AppendText("The Value is " + rng.Value2.ToString());
            }
        }
        
        private void btnDisconnect_Click(object sender, EventArgs e)
        {
            try
            {
                txtLog.AppendText("Closing connection...\r\n");
                if (OdbcCon.State == ConnectionState.Open)
                {
                    OdbcCon.Close();
                }
                txtLog.AppendText("Connection Closed\r\n");
            }
            catch (System.Data.Odbc.OdbcException Ex)
            {
                txtLog.AppendText(Ex.Message + "\r\n");
                MessageBox.Show("Could not close the database.\r\n" + Ex.Message, "Database connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void mnuExit_Click(object sender, EventArgs e)
        {
            System.Windows.Forms.Application.Exit();
        }

        private void mnuAbout_Click(object sender, EventArgs e)
        {
            frmAbout = new frmAbout();
            frmAbout.ShowDialog(this);
            frmAbout.Dispose();
        }

        private void txtLog_TextChanged(object sender, EventArgs e)
        {

        }

        private void txtDatabase_TextChanged(object sender, EventArgs e)
        {

        }

        private void txtTable_TextChanged(object sender, EventArgs e)
        {

        }

        private void txtProduct_TextChanged(object sender, EventArgs e)
        {

        }

        private void txtPrice_TextChanged(object sender, EventArgs e)
        {

        }

        private void lblDatabase_Click(object sender, EventArgs e)
        {

        }

        private void lblTable_Click(object sender, EventArgs e)
        {

        }

        private void lblProduct_Click(object sender, EventArgs e)
        {

        }

        private void lblPrice_Click(object sender, EventArgs e)
        {

        }
    }
}