package MySqlTest;

import System.Data.*;
import MySql.Data.MySqlClient.*;

/**
 * Summary description for Class1.
 */
public class Class1
{
	private static MySqlConnection getConnection( String server, int port, String database, String user, String pass )
	{
		String connectString = "Server=" + server + ";port=" + port + ";database=" + database + ";user id=" + user + ";password=" + pass + ";Pooling=false";

		MySqlConnection connection = new MySqlConnection( connectString );

		connection.Open();

		return connection;
	}

	private static int ExecuteNonQuery( MySqlConnection connection, String sql )
	{
		MySqlCommand command = new MySqlCommand();

		command.set_Connection( connection );
		command.set_CommandType( CommandType.Text );
		command.set_CommandText( sql );

		int result = command.ExecuteNonQuery();

		command.Dispose();

		return result;
	}

	private static void ExecuteQuery( MySqlConnection connection, String sql )
	{
		MySqlCommand command = new MySqlCommand();

		command.set_Connection( connection );
		command.set_CommandType( CommandType.Text );
		command.set_CommandText( sql );

		MySqlDataReader reader = command.ExecuteReader();
		int rowNum = 1;
		while ( reader.Read() )
		{
			System.Console.WriteLine( "ROW " + (rowNum++) );
			System.Console.WriteLine( "=====" );
			for ( int i = 0; i < reader.get_FieldCount(); i++ )
				System.Console.WriteLine( "Column " +  (i+1)  + " = " + reader.GetValue( i ) );
			System.Console.WriteLine( " " );
		}
		reader.Close();
		command.Dispose();
	}

	public static void main(String[] args)
	{

		try
		{
			String SERVER	= "127.0.0.1";
			int    PORT		= 3306;
			String DATABASE = "test";
			String USER		= "root";
			String PASS		= "root";

			// Get connection
			MySqlConnection connection = getConnection( SERVER, PORT, DATABASE, USER, PASS );
			
			// Drop table
			try 
			{
				ExecuteNonQuery( connection, "DROP TABLE bug_tbl" );
			}
			catch ( System.Exception ignore )
			{
			}

			// Create table
			ExecuteNonQuery( connection, "CREATE TABLE bug_tbl (EMail varchar ( 50) NULL )" );

			String insertText = "Eureka! A fórmula";

			ExecuteNonQuery( connection, "INSERT INTO bug_tbl (Email) values( '" + insertText + "' )" );

			// Insert a row
			MySqlCommand command = new MySqlCommand();
			command.set_Connection( connection );
			command.set_CommandType( CommandType.Text );
			command.set_CommandText( "INSERT INTO bug_tbl (Email) values( ?1 )" );
			
			MySqlParameter parm = command.get_Parameters().Add( "?1", MySqlDbType.VarChar );
			parm.set_Direction( ParameterDirection.Input );
			parm.set_Size( insertText.length() );
			parm.set_Value( insertText );
			
			command.Prepare();
			
			int result = command.ExecuteNonQuery();	// work around for bug #12163
			
			// BUG #12163 OCCURS HERE.  THIS CLOSE CALL CAUSES ANOTHER ROW TO BE INSERTED
			//MySqlDataReader rdr = command.ExecuteReader();			
			//rdr.Close();

			command.Dispose();

			// Display the rows
			ExecuteQuery( connection, "SELECT * FROM bug_tbl" );

			connection.Close();
		}
		catch ( System.Exception e )
		{
			System.Console.WriteLine( e.get_Message() );
			System.Console.WriteLine( e.ToString() );
		}

		System.Console.WriteLine( ".NET VERSION = " + System.Environment.get_Version() );
		System.Console.WriteLine( "<Press Enter To Continue>" );
		System.Console.ReadLine();
	}
}