using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

/* 
 * 
 * 
 * System.Data.ODBC Namespace
 * http://msdn.microsoft.com/en-us/library/system.data.odbc(VS.80).aspx
 * 
 * Connecting to MySQL with C# and ODBC
 * http://www.geekpedia.com/tutorial139_Connecting-to-MySQL-with-Csharp-and-ODBC.html
 *
 * MyODBC .Net2.0 Library Plugins
 * http://www.mysql.com/products/connector/odbc/
 * 
 * 
 * Putty
 * http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html

 * NetCat (could prob use instead of Putty)
 * http://www.datastronghold.com/articles/3.html    
 * 


ATTENTION: SETUP INSTRUCTIONS

 * Connect to Your MySQL Database from Third-Party Programs via a Secure SSH Tunnel Using Putty v0.60 
   http://wiki.dreamhost.com/Mysql


Using PuTTy, it is possible to access your MySQL database via a secure SSH tunnel. 
This method is peferred over insecure methods as it provides point-to-point encryption and does not expose your MySQL account to potential hacks from allowed networks. 

To set up a tunnel in PuTTy, your account must first have SSH enabled. To do this... 

go to your Dreamhost Account Control Panel 
edit the user to whom you wish to grant SSH access 
check the Enable ssh/telnet checkbox 
Make sure /bin/bash is your shell type 
click Save Changes 
Next, download and launch PuTTy. 

In the category tree on the left, click Tunnels near the bottom 
Enter 3306 in the source port 
Enter your mysql database address and port in the destination field (e.g. yourdbdomain.yourdomain.com:3306) 
Use all other defaults (local, auto) and click the Add button 
Click Session in the category tree 
Enter your shell account address in the Host Name field (e.g. foo.dreamhost.com 
Ensure that port 22 and SSH are specified under prototcol 
Give your session a name and click the Save button. 
Now log into your shell account using your newly created session 
Minimize the window 
Next, open the third-party tool. We'll use the MySQL Adminstrator tool as an example. 

enter localhost under Server Host (note: entering your actual address (e.g. yourdbdomain.yourdomain.com) will not work here. You must use localhost) 
enter 3306 as the port 
enter your db username and password 
click OK 
You should be in! 

Notes: 

Your PuTTY session must be active and you must be logged in for the tunnel to be active 
If you are running a local MySQL db, you can specify a different port for your tunnel. 
Otherwise, you will not be able to access your local mysql connection while the tunnel is active. 

 * To do this: 
 * 1) specify a different number (e.g. 3307) in the source port field under Tunnels in PuTTY. 
 * 2) Use the same number as your port in the MySQL Adminstrator Port field. Note that your destination port must remain 3306. 

The idea is that you're sending data to port 3307 on your end, the data is sent through the tunnel, and then is shunted to port 3306 on Dreamhost's end. 


*/

namespace CSharpToMySQL
{
	public partial class frmMain : Form
	{
		private System.Data.Odbc.OdbcConnection OdbcCon;
		private System.Data.Odbc.OdbcCommand OdbcCom;
		private System.Data.Odbc.OdbcDataReader OdbcDR;
		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;

			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 btnDisconnect_Click(object sender, EventArgs e)
		{
			if (OdbcCon.State == ConnectionState.Open)
			{
				OdbcCon.Close();
			}
		}

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

	}
}