xTRUMANx

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
using System.Reflection;

namespace hopeDBConsole
{
    public class DataLayer
    {
        private SQLiteConnection conn;
        private SQLiteDataAdapter adapter;
        private SQLiteCommand command;
        private static DataSet set = new DataSet();

        // How do I test if connString is valid?
        // i.e. Test if connection to db is working?
        public DataLayer(string connString, params string[] tableNames)
        {
            conn = new SQLiteConnection();
            conn.ConnectionString = connString;

            DataTable table = new DataTable();

            command = new SQLiteCommand();
            command.Connection = conn;

            adapter = new SQLiteDataAdapter();

            #region Filling the DataSet with the tables in the Database
            foreach (string tableName in tableNames)
            {
                command.CommandText = "SELECT * FROM "+ tableName +";";
                adapter.SelectCommand = command;
                adapter.Fill(set, tableName);
                table = set.Tables[tableName];
                SetPrimaryKey(table);
            }
            #endregion Finished filling the DataSet
        }

        // All my tables have a column named id which is the PK.
        public void SetPrimaryKey(DataTable table)
        {
            DataColumn[] key = new DataColumn[1];
            DataColumn idCol = new DataColumn();
            idCol = table.Columns["id"];
            key[0] = idCol;
            table.PrimaryKey = key;
            idCol.AutoIncrement = true;
        }

        public static DataTable GetTable(string tableName)
        {
            return set.Tables[tableName];
        }

        public static DataTable GetTable(ISchoolObject thing)
        {
            string tableName = GetTableName(thing);
            return GetTable(tableName);
        }

        // My table names are lowercase versions of the class name.
        // It would have been nice to have pluralization as in RoR.
        public static string GetTableName(ISchoolObject thing)
        {
            return thing.GetType().ToString().ToLower();
        }

        // TODO: Find out how Create could fail and make it return
        // false to the caller.
        public bool Create(ISchoolObject thing)
        {
            //string tableName = thing.GetType().ToString().ToLower(); // This string is used at end of this method
            DataTable table = GetTable(thing);
            string tableName = table.TableName;
            // Get all the properties to be stored for the object
            PropertyInfo[] properties = thing.GetType().GetProperties();

            #region Building InsertCommand
            string columns = "";
            string values = "";

            foreach (PropertyInfo property in properties)
            {
                // Don't need the Id property when creating a *new* record
                if (property.Name != "Id")
                {
                    string prop = property.Name.ToLower();
                    columns += prop + ", ";
                    values += "@" + prop + ", ";
                    command.Parameters.Add("@" + prop, DbType.String, 0, prop);
                }
            }
            // Next two if statements remove the extra ", "
            if(columns != "")
                columns = columns.Remove(columns.Length-2);
            if(values != "")
                values = values.Remove(values.Length-2);
            // Read up on your sql if the below makes no sense :-P
            command.CommandText = "INSERT INTO " + tableName + "(" + columns + ") VALUES(" + values + ")";
            adapter.InsertCommand = command;
            #endregion InsertCommand is ready

            DataRow row = table.Rows.Add();
            Save(thing, properties, row);

            int rowCount = table.Rows.Count;
            long id = (long) table.Rows[rowCount - 1]["id"];
            thing.Id = id;

            return true;
        }

        // Excessive commenting below perhaps. May come in handy when
        // I don't work with DataAdapters, DataSets and PropertyInfo
        // objects for a while and read this method again.
        public void Save(ISchoolObject thing, PropertyInfo[] properties, DataRow row)
        {
            foreach (PropertyInfo property in properties)
            {
                // Assuming the property is called Age, this
                // statement is executed: row["age"] = thing.Age;
                if (property.Name != "Id")
                    row[property.Name.ToLower()] = property.GetGetMethod().Invoke(thing, null);
            }

            // This updates the local data set
            adapter.Update(set, GetTableName(thing));

            // This actually saves changes to the
            // dataset to the database
            set.AcceptChanges();

            // Refilling the dataset so the generated
            // id shows up in the dataset
            RefillDataSet(GetTableName(thing));
        }

        public void Update(ISchoolObject thing)
        {
            DataTable table = GetTable(thing);
            string tableName = table.TableName;
            PropertyInfo[] properties = thing.GetType().GetProperties();

            DataRow row = table.Rows.Find(thing.Id);

            CheckNullRow(row); // Throws an exception if row is null

            #region Building update commantText
            // Can't get SQLiteCommandBuilder to work
            command.CommandText = "UPDATE " + tableName + " SET ";
            foreach (PropertyInfo property in properties)
            {
                if (property.Name != "Id")
                {
                    string prop = property.Name.ToLower();
                    command.CommandText += prop + " = @" + prop + ", ";
                    command.Parameters.Add("@" + prop, DbType.String, 0, prop);
                }
            }
            command.CommandText = command.CommandText.Remove(command.CommandText.Length - 2);
            command.CommandText += " WHERE id = @id";
            command.Parameters.Add("@id", DbType.Int64, 0, "id");
            adapter.UpdateCommand = command;
            #endregion Update command ready

            Save(thing, properties, row);
        }

        public void Delete(ISchoolObject thing)
        {
            DataTable table = GetTable(thing);

            DataRow row = table.Rows.Find(thing.Id);
            CheckNullRow(row); // Throws an exception if row is null

            row.Delete();

            command.CommandText = "DELETE FROM " + table.TableName + " WHERE id = " + thing.Id;
            adapter.DeleteCommand = command;

            adapter.Update(set, table.TableName);
            set.AcceptChanges();

            // Refilling dataset so that deleted entry
            // is removed from the dataset
            RefillDataSet(table.TableName);
        }

        // Built this method since I don't know of a command
        // that accomplishes the same task
        public void RefillDataSet(string tableName)
        {
            adapter.SelectCommand.CommandText = "SELECT * FROM " + tableName;
            set.Tables[tableName].Clear();
            adapter.Fill(set, tableName);
        }

        public void CheckNullRow(DataRow row)
        {
            if (row == null)
                throw new RowNotInTableException("The row you are updating/deleting is not in the DataTable.");
        }
    }
}