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

namespace luento10
{
    public partial class Form1 : Form
    {

        public Form1()
        {
            InitializeComponent();
        }

        OdbcDataAdapter dAdapter;
        DataTable dTable;
        OdbcCommandBuilder cBuilder;
        OdbcConnection connection;
        
        private void Form1_Load(object sender, EventArgs e)
        {
            // edellyttää Reseptit2-nimistä ODBC-yhteyttä
            string connString = "dsn=Reseptit2";
            connection = new OdbcConnection(connString);
            connection.Open();

            string query = "SELECT * FROM Resepti";
//            dAdapter = new OdbcDataAdapter(query, connString);
            dAdapter = new OdbcDataAdapter(query, connection);
            cBuilder = new OdbcCommandBuilder(dAdapter);
            dTable = new DataTable();

            dAdapter.Fill(dTable);

            //the DataGridView
            DataGridView dgView = new DataGridView();

            //BindingSource to sync DataTable and DataGridView
            BindingSource bSource = new BindingSource();

            //set the BindingSource DataSource
            bSource.DataSource = dTable;

            //set the DataGridView DataSource
            dgView.DataSource = bSource;

            this.Controls.Add(dgView);
// yhteyden jatkuvaa aukomista ja sulkemista pitää välttää. Raskasta
            connection.Close();

// tämä tallentaisi gridviewin kautta tehdyt muutokset tietokantaan
//            dAdapter.Update(dTable);

        }

        private void button1_Click(object sender, EventArgs e)
        {
            dAdapter.Update(dTable);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            // näin ajettaisiin yksittäinen SQL-rivi
/*            string queryString = "INSERT INTO foo...";
			OdbcCommand command = new OdbcCommand(queryString);

            using (OdbcConnection connection = new OdbcConnection(connectionString))
            {
                command.Connection = connection;
                connection.Open();
                command.ExecuteNonQuery();
            }*/

            connection.Open();
            OdbcCommand command = new OdbcCommand("SELECT * FROM Resepti", connection);


                // Execute the DataReader and access the data.
                OdbcDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    listBox1.Items.Add(reader[0]);
                }

                // Call Close when done reading.
                reader.Close();
                connection.Close();
 
        }
    }
}