using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SQLite;



namespace RMtrix.Class
{
    class dBHelper
    // Adapted from "C# & SQLite 1007000" By kribo | 3 May 2011, http://www.codeproject.com/Articles/190223/C-SQLite-1007000
    // by Tom Holden, Feb 2012: Dutch comments translated using Google Translate.
    {
        // Declaration internal variables
        private SQLiteConnection m_connection = null;
        private string m_connectionString = "";
        private SQLiteDataAdapter m_dataAdapter = null;
        private DataSet m_dataSet = null;
        private string m_fieldNameID = "";

        // The DataSet is filled with the methode LoadDataSet
        public DataSet DataSet
        {
            get { return m_dataSet; }
        }

        // Constructor -> ConnectionString is required
        public dBHelper(string connectionString)
        {
            m_connectionString = connectionString;
        }

        // Load the DataSet 
        public bool Load(string commandText, string fieldNameID)
        {
            // Save the variables
            m_fieldNameID = fieldNameID;

            try
            {
                // Open de connectie - Open the connection
                m_connection = new SQLiteConnection(m_connectionString);
                m_connection.Open();

                // Maak een DataAdapter - Create a DataAdapter
                m_dataAdapter = new SQLiteDataAdapter(commandText, m_connection);

                // Koppel een eventhandler aan het RowUpdated-event van de DataAdapter
                // Associate an event handler to the RowUpdated event of the DataAdapter
                //m_dataAdapter.RowUpdated += new SqlRowUpdatedEventHandler(m_dataAdapter_RowUpdated);
                m_dataAdapter.RowUpdated += m_dataAdapter_RowUpdated;
                m_dataSet = new DataSet();

                // Voor eventueel opslaan --> Commands maken
                // For any store -> create Commands
                if (!string.IsNullOrEmpty(fieldNameID))
                {
                    SQLiteCommandBuilder commandBuilder = new SQLiteCommandBuilder(m_dataAdapter);
                    m_dataAdapter.InsertCommand = commandBuilder.GetInsertCommand();
                    m_dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand();
                    m_dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
                }

                // Vul de DataSet - Fill the DataSet
                m_dataAdapter.Fill(m_dataSet);

                // We zijn hier, alles okay! - We are here, everything is okay!
                return true;
            }

            catch (Exception e)
            {
                //System.Windows.Forms.MessageBox.Show("Error:" + e);
                //Console.WriteLine("Error:", e);
                throw new Exception("Error: "+ e) ;
            }
            finally
            {
                // Altijd netjes sluiten - Always neatly close
                m_connection.Close();
            }
        }


        // Laad de DataSet - Load the DataSet
        public bool Load(string commandText)
        {
            return Load(commandText, "");
        }

        // Sla de DataSet op - Save to the DataSet
        public bool Save()
        {
            // Hij kan alleen gegevens opslaan in ID bekend is
            // It can only store data in known ID
            if (m_fieldNameID.Trim().Length == 0)
            {
                return false;
            }

            try
            {
                // Open the connection
                m_connection.Open();

                // Sla de DataRow op. Dit vuurt het event OnRowUpdated af
                // Save to the DataRow. This fires off the event OnRowUpdated
                m_dataAdapter.Update(m_dataSet);

                // We zijn hier, alles okay! - We are here, everything is okay!
                return true;
            }

            catch (Exception)
            {
                throw;
            }
            finally
            {
                // Altijd netjes sluiten - Always neatly close
                m_connection.Close();
            }
        }

        // Rij is opgeslagen, bepaal eventueel de nieuwe ID
        // Row is stored, determine if the new ID
        void m_dataAdapter_RowUpdated(object sender, System.Data.Common.RowUpdatedEventArgs e)
        {
            // Het (zojuist verkregen?) ID is alleen interessant bij een nieuwe record
            // The (newly acquired?) ID is only interesting to a new record

            if (e.StatementType == StatementType.Insert)
            {
                // Bepaal het zojuist verkregen ID - Determine the newly obtained ID
                //SQLiteCommand command = new SQLiteCommand("SELECT @@IDENTITY", m_connection);
                SQLiteCommand command = new SQLiteCommand("SELECT last_insert_rowid() AS ID", m_connection);
                

                // Bepaal de nieuwe ID en sla deze op in het juiste veld
                // Determine the new ID and save it in the appropriate field
                object nieuweID = command.ExecuteScalar();

                // Bij evt. fouten geen ID --> Daarom testen - If in any. no ID errors -> why test
                if (nieuweID == System.DBNull.Value == false)
                {
                    // Zet de ID in de juiste kolom in de DataRow
                    // Put the ID in the appropriate column in the DataRow
                    e.Row[m_fieldNameID] = Convert.ToInt32(nieuweID);
                }
            }
        }








    }
}
