using System;
using System.Text;
using System.Windows.Forms;
using java.sql;
using chemaxon.util;
using chemaxon.jchem.db;

namespace InsertDeleteUpdateMolecules
{

    public partial class MainForm : Form
    {
        public MainForm()
            : base()
        {
            InitializeComponent();
        }

        private void insertBtn_Click(object sender, EventArgs e)
        {
            InsertUpdate(UpdateHandler.INSERT);
        }

        private void updateBtn_Click(object sender, EventArgs e)
        {
            InsertUpdate(UpdateHandler.UPDATE);
        }

        private void deleteBtn_Click(object sender, EventArgs e)
        {
            DeleteStructure();
        }

        private static void InsertUpdate(int operationType)
        {
            int id = 497;          // cd_id value of compound
            string structureTableName = "ScreenColl";

            // Additional columns:
            //string name = "some name";
            //float stock = 1;
            //string comments = "some comment";
            
            ConnectionHandler conh = GetConnectionHandler();

            UpdateHandler uh = new UpdateHandler(conh, operationType, structureTableName, null); // "name, stock, comments");

            try
            {
                uh.setStructure(Encoding.Default.GetBytes("CC"));//hardcoded molecule CC
                if (operationType == UpdateHandler.UPDATE)
                    uh.setID(id);

                //uh.setValueForAdditionalColumn(1, name);
                //uh.setValueForAdditionalColumn(2, stock);
                //uh.setValueForAdditionalColumn(3, comments);

                uh.execute();
                MessageBox.Show("Done!");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                uh.close();
            }
        }

        private static void DeleteStructure()
        {
            int id = 497;          // cd_id value of compound

            // Additional columns:
            string structureTableName = "Molecules";

            ConnectionHandler conh = GetConnectionHandler();

            try
            {
                UpdateHandler.deleteRow(conh, structureTableName, id);
                MessageBox.Show("Done!");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private static ConnectionHandler GetConnectionHandler()
        {
            ConnectionHandler conh = new ConnectionHandler();
            Connection conn = GetMSSql2005Connection(); //use this for MSSql2005 databases 
            //Connection conn = GetMySqlConnection(); //use this for MySql databases
            //Connection conn = GetOracleThinConnection(); //use this for Oracle databases
            //Connection conn = GetODBCConnection(); //use this for any ODBC connections you have configured on your system

            conh.setConnection(conn);
            return conh;
        }

        private static Connection GetMSSql2005Connection()
        {
            string server = "server"; //server hostname or IP address
            string port = "1111";
            string databaseName = "databaseName";
            string user = "user";
            string password = "password";
            //NOTE: Integrated windows authentication does not work with jTDS unless you set in system environment variables the additional dll 
            //provided with jTDS...

            string jdbcUrl = "jdbc:jtds:sqlserver://{0}:{1}/{2};SelectMethod=cursor;";
            jdbcUrl = string.Format(jdbcUrl, server, port, databaseName);
            net.sourceforge.jtds.jdbc.Driver drv = new net.sourceforge.jtds.jdbc.Driver(); //do not delete this otherwise it will try 
            //to locate the driver in CLASSPATH environment variable  (Java style).
            Connection conn = java.sql.DriverManager.getConnection(jdbcUrl, user, password);
            return conn;
        }

        private static Connection GetMySqlConnection()
        {
            string server = "server"; //server hostname or IP address
            string databaseName = "databaseName";
            string user = "user";
            string password = "password";

            string jdbcUrl = "jdbc:mysql://{0}/{1}";
            jdbcUrl = string.Format(jdbcUrl, server, databaseName);
            com.mysql.jdbc.Driver drv = new com.mysql.jdbc.Driver(); //do not delete this otherwise it will try 
            //to locate the driver in CLASSPATH environment variable  (Java style).
            Connection conn = java.sql.DriverManager.getConnection(jdbcUrl, user, password);
            return conn;
        }

        private static Connection GetOracleThinConnection()
        {
            string server = "server"; //server hostname or IP address
            string port = "1111";
            string databaseName = "databaseName";
            string user = "user";
            string password = "password";

            string jdbcUrl = "jdbc:oracle:thin:@{0}:{1}:{2}";
            jdbcUrl = string.Format(jdbcUrl, server, port, databaseName);
            oracle.jdbc.driver.OracleDriver drv = new oracle.jdbc.driver.OracleDriver(); //do not delete this otherwise it will try 
            //to locate the driver in CLASSPATH environment variable  (Java style).
            Connection conn = java.sql.DriverManager.getConnection(jdbcUrl, user, password);
            return conn;
        }

        private static Connection GetODBCConnection()
        {
            string odbcConnectionName = "jchemdb";
            string jdbcUrl = "jdbc:odbc:{0}";
            jdbcUrl = string.Format(jdbcUrl, odbcConnectionName);
            sun.jdbc.odbc.JdbcOdbcDriver drv = new sun.jdbc.odbc.JdbcOdbcDriver(); //do not delete this otherwise it will try 
            //to locate the driver in CLASSPATH environment variable  (Java style).
            Connection conn = java.sql.DriverManager.getConnection(jdbcUrl);
            return conn;
        }
    }

}