Refactoring DBObject, Part 3

I think it’s time for a demonstration. In this example I show two ways you can use joined tables within the object. I also so why the KeyPath would be a useful addition to the object.

In User.BirthDate I’m joining two columns from the table users_info directly into the User object. There are problems with this. For example I have to change the table and index column. I’d also have to change the column names if I did and update (because birth_date and death_date don’t exist in the users table.) Over all the BirthDate method is sloppy and error prone.
The DeathDate creates a new DBObject and works off that. This is a cleaner apporach, I just have to remember to update when I update the User object. This also shows why KeyPath would be a nice addition. (DateTime)((DBObject)valueForKey("user_info")).valueForKey("column_death_date") isn’t nearly as nice as something like (DateTime)valueForKey("user_info.column_death_date")

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using ZenSoftware;

namespace DBObject_Test
{
    class Program
    {
        static public string connMy = "Server=192.168.1.1;User Id=web;Password=web;Persist Security Info=True;Database=playground;pooling=true";

        static void Main(string[] args)
        {
            //Get a user
            User user = User.ByID(1313);

            //Display some info
            Console.WriteLine("Full Name: " + user.FullName);
            Console.WriteLine("\tBirthDate: " + user.BirthDate.ToShortDateString());
            Console.WriteLine("\tDeathDate: " + user.DeathDate.ToShortDateString());

            Console.WriteLine("\n\nPress any key to exit.");
            Console.ReadKey();
        }
    }

    public class User : DBObject
    {
        public int ID
        {
            get { return (int)this.valueForKey("column_id"); }
            set { this.setValueForKey("column_id", value); }
        }
        public string FirstName
        {
            get
            {
                return (string)valueForKey("column_first_name");
            }
            set
            {
                setValueForKey("column_first_name", value);
            }
        }
        public string LastName
        {
            get
            {
                return (string)valueForKey("column_last_name");
            }
            set { setValueForKey("column_last_name", value); }
        }
        public string FullName
        {
            get
            {
                return this.FirstName + " " + this.LastName;
            }
        }
        public string Email
        {
            get
            {
                return (string)valueForKey("column_email");
            }
            set
            {
                setValueForKey("column_email", value);
            }
        }

        public DateTime BirthDate
        {
            get
            {
                if (null == valueForKey("column_birth_date"))
                {
                    //Save our current information
                    string oldTable = this.TableName;
                    string oldIndex = this.IndexColumn;

                    //Set the information for the other table
                    this.TableName = "users_info";
                    this.IndexColumn = "user_id";
                    //Create the Query
                    MySqlCommand cmd = new MySqlCommand("SELECT birth_date, death_date FROM users_info WHERE user_id=?id");
                    cmd.Parameters.AddWithValue("?id", this.ID);
                    this.Fill(cmd);

                    //Now restore the correct table/index
                    this.TableName = oldTable;
                    this.IndexColumn = oldIndex;
                }
                return (DateTime)valueForKey("column_birth_date");
            }
            set
            {
                setValueForKey("column_birth_date", value);
            }
        }
        public DateTime DeathDate
        {
            get
            {
                if (null == valueForKey("user_info"))
                {
                    //Store the user_info table inside of this object
                    DBObject user_info = new DBObject("users_info", "user_id", this.ConnectionString);
                    //Create the query
                    MySqlCommand cmd = new MySqlCommand("SELECT birth_date, death_date FROM users_info WHERE user_id=?id");
                    cmd.Parameters.AddWithValue("?id", this.ID);
                    //Fill the object
                    user_info.Fill(cmd);
                    //Save the user_info
                    setValueForKey("user_info", user_info);
                }
                return (DateTime)((DBObject)valueForKey("user_info")).valueForKey("column_death_date");
            }
            set
            {
                ((DBObject)valueForKey("user_info")).setValueForKey("column_death_date", value);
            }
        }

        public User()
            :base("users", "id", Program.connMy)
        {
        }

        public User(string first_name, string last_name)
            :base("users", "id", Program.connMy)
        {
            //Setup the connection
            this.TableName = "users";
            this.IndexColumn = "id";
            this.ConnectionString = Program.connMy;

            //Add the columnds
            this.FirstName = first_name;
            this.LastName = last_name;
            this.Email = "DELETEME@junk.com";

            this.Insert();
        }

        static public User ByID(int id)
        {
            //Let's load up a user object
            MySqlCommand cmd = new MySqlCommand("SELECT * FROM users WHERE id = ?id");
            cmd.Parameters.AddWithValue("?id", id);

            User obj = DBObject.SingleBySelect$lt;User$gt;(cmd);

            return obj;
        }
    }
}

And here is the up to date DBObject

using System;
using System.Collections;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using MySql.Data.MySqlClient;

namespace ZenSoftware
{
    //Delegate for KVO
    public delegate void observeValueForKey(string forKey, DBObject forObject, object oldValue, object newValue);

    /// <summary>
    /// Base object for ORM Objects
    /// </summary>
    public class DBObject
    {
        protected Hashtable _properties;
        protected Dictionary<string, observeValueForKey> _kvo;

        protected string TableName
        {
            get { return (string)valueForKey("_table_name_"); }
            set { setValueForKey("_table_name_", value); }
        }
        protected string IndexColumn
        {
            get { return (string)valueForKey("_index_column_"); }
            set { setValueForKey("_index_column_", value); }
        }
        protected string ConnectionString
        {
            get { return (string)valueForKey("_connection_string_"); }
            set { setValueForKey("_connection_string_", value); }
        }

        /// <summary>
        /// Create a DBObject
        /// </summary>
        /// <param name="table_name">table used for this object</param>
        /// <param name="index_column">index column used in the table. Expects something like "id"</param>
        /// <param name="connection_string">connection string to use for this object</param>
        public DBObject(string table_name, string index_column, string connection_string)
        {
            _properties = new Hashtable();
            _kvo = new Dictionary<string, observeValueForKey>();
            this.TableName = table_name;
            this.IndexColumn = index_column;
            this.ConnectionString = connection_string;
        }

        /// <summary>
        /// Get the value for a specified key.
        /// If key doesn't exist, valueForUndefinedKey(string key) will be called instead.
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public object valueForKey(string key)
        {
            if (_properties.ContainsKey(key))
            {
                return _properties[key];
            }
            return valueForUndefinedKey(key);
        }
        /// <summary>
        /// Called when a key doesn't exist.
        /// Override to impliment your own error handling.
        /// </summary>
        /// <param name="key"></param>
        /// <returns>null</returns>
        public object valueForUndefinedKey(string key)
        {
            return null;
        }
        /// <summary>
        /// Set a value for the key
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        public void setValueForKey(string key, object value)
        {
            //Save the old value
            object oldValue = _properties.ContainsKey(key) ? _properties[key] : null;
            //Change it
            _properties[key] = value;
            //Inform everyone of the change.
            if (_kvo.ContainsKey(key) && _kvo[key] != null) { ((observeValueForKey)_kvo[key])(key, this, oldValue, value); }
        }
        /// <summary>
        /// Add an observer for when the Key changes.
        /// </summary>
        /// <param name="key">The key you want to watch</param>
        /// <param name="observer">delegate observeValueForKey</param>
        public void addObserverForKey(string key, observeValueForKey observer)
        {
            if (!_kvo.ContainsKey(key))
            {
                _kvo.Add(key, observer);
            }
            else
            {
                //Add the observer
                _kvo[key] += observer;
            }
        }
        /// <summary>
        /// Remove an observer from the Key changes.
        /// </summary>
        /// <param name="key"></param>
        /// <param name="observer"></param>
        public void removeObserverForKey(string key, observeValueForKey observer)
        {
            if (_kvo.ContainsKey(key))
            {
                _kvo[key] -= observer;
            }
        }

        /// <summary>
        /// Fill out our properties from the command.
        /// </summary>
        /// <param name="command"></param>
        /// <param name="connection"></param>
        public void Fill(MySqlCommand command)
        {
            if (!canCallTable()) { throw new Exception("TableName, IndexColumn, or ConnectionString are not defined."); }

            using (MySqlConnection conn = new MySqlConnection(this.ConnectionString))
            {
                //Connect the command to the connectiong
                command.Connection = conn;

                try
                {
                    //Open the connection and get the reader
                    conn.Open();
                    MySqlDataReader reader = command.ExecuteReader();

                    //I only want to get the frist record because I'm returning just one object.
                    while (reader.Read())
                    {
                        //Get all the columns
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            setValueForKey("column_" + reader.GetName(i), reader[i]);
                        }
                    }

                }
                catch (Exception ex)
                {
                    LogError("DBObject.Fill("+ command.CommandText +")", ex);
                }

            }
        }

        /// <summary>
        /// index_column is the column to use in the WHERE clause. The object expects something like "id"
        /// All columns to updated *must* start with "column_" in the Properties (this is done automatically if you used the object.)
        /// </summary>
        /// <param name="connection"></param>
        public void Update()
        {
            if (!canCallTable()) { throw new Exception("TableName, IndexColumn, or ConnectionString are not defined."); }
            string table = this.TableName;
            string index_column = this.IndexColumn;
            string connection = this.ConnectionString;

            if (this._properties.Count > 0 && this._properties.ContainsKey("column_" + index_column))
            {
                //Build our Command
                MySqlCommand cmd = new MySqlCommand();
                //Build the query
                StringBuilder sb_query = new StringBuilder();
                sb_query.Append("UPDATE ");
                sb_query.Append(table);
                sb_query.Append(" SET ");

                //Add all of the properties
                foreach (string key in this._properties.Keys)
                {
                    //If it's a column and it's not the id
                    if (key.StartsWith("column_") && key != "column_" + index_column)
                    {
                        //Cool, lets add the key
                        sb_query.Append(key.Substring(key.IndexOf("_") + 1));
                        sb_query.Append("=");
                        sb_query.Append("?" + key);
                        sb_query.Append(", ");
                        //Now add the value
                        cmd.Parameters.AddWithValue("?" + key, this._properties[key]);
                    }
                }
                //Now remove the  trailing comma
                sb_query.Remove(sb_query.Length - 2, 2);

                //Add the WHERE
                sb_query.Append(" WHERE ");
                sb_query.Append(index_column);
                sb_query.Append("=?column_");
                sb_query.Append(index_column);
                cmd.Parameters.AddWithValue("?column_" + index_column, this._properties["column_" + index_column]);

                //Start the Database stuff
                using (MySqlConnection conn = new MySqlConnection(connection))
                {
                    //Put the Command together
                    cmd.CommandText = sb_query.ToString();
                    cmd.Connection = conn;

                    //Now run it!
                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        LogError("DBObject.Update()", ex);
                    }
                }
            }
        }

        /// <summary>
        /// Inserts all properties who's name starts with "column_" into the database.
        /// It will then set it's own index_column property with the new index value.
        /// </summary>
        /// <param name="table">Table to insert.</param>
        /// <param name="index_column">expects something like "id".</param>
        /// <param name="connection">Database Connection String.</param>
        protected void Insert()
        {
            if (!canCallTable()) { throw new Exception("TableName, IndexColumn, or ConnectionString are not defined."); }
            string table = this.TableName;
            string index_column = this.IndexColumn;
            string connection = this.ConnectionString;

            if (this._properties.Count > 0)
            {
                //Build the command
                MySqlCommand cmd = new MySqlCommand();

                //Build the Query
                StringBuilder sb_query = new StringBuilder();   //First part of the query
                sb_query.Append("INSERT INTO ");
                sb_query.Append(table);
                StringBuilder sb_values = new StringBuilder(); //Second part of the query
                sb_values.Append("VALUES ");

                //Add the Columns to insert and their values
                sb_query.Append("(");
                sb_values.Append("(");
                foreach (string key in this._properties.Keys)
                {
                    //If it's a column and it's not the id
                    if (key.StartsWith("column_") && key != "column_" + index_column)
                    {
                        //Add the Column name
                        sb_query.Append(key.Substring(key.IndexOf("_") + 1));
                        sb_query.Append(",");

                        //Add the Value
                        sb_values.Append("?");
                        sb_values.Append(key);
                        sb_values.Append(",");
                        //Add the value to the command
                        cmd.Parameters.AddWithValue("?" + key, this._properties[key]);
                    }
                }
                //Now remove the  trailing comma
                sb_query.Remove(sb_query.Length - 1, 1);
                sb_query.Append(")");
                sb_values.Remove(sb_values.Length - 1, 1);
                sb_values.Append(")");

                //Start the Database stuff
                using (MySqlConnection conn = new MySqlConnection(connection))
                {
                    //Put the Command together
                    cmd.CommandText = sb_query.ToString() + " " + sb_values.ToString();
                    cmd.Connection = conn;

                    //Now run it!
                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        LogError("DBObject.Insert()", ex);
                    }
                }

                //Now lets get the ID back
                // TODO: This could return the wrong id. We need to verify that the columns are the ones we just inserted.
                MySqlCommand cmdID = new MySqlCommand("SELECT " + index_column + " FROM " + table + " ORDER BY id DESC LIMIT 1");
                this.Fill(cmdID);
            }
        }

        /// <summary>
        /// Checks if all the data except the index already exists as a record in the database.
        /// </summary>
        /// <param name="table"></param>
        /// <param name="index_column"></param>
        /// <param name="connection"></param>
        /// <returns></returns>
        protected bool doesExist()
        {
            if (!canCallTable()) { throw new Exception("TableName, IndexColumn, or ConnectionString are not defined."); }
            string table = this.TableName;
            string index_column = this.IndexColumn;
            string connection = this.ConnectionString;

            //If there are no properties then it can't be exist
            if (this._properties.Count <= 0) { return false; }
            //Our Command
            MySqlCommand cmd = new MySqlCommand();
            //Build the Query
            StringBuilder sb_query = new StringBuilder();
            sb_query.Append("SELECT COUNT(*) FROM ");
            sb_query.Append(table);
            sb_query.Append(" WHERE ");

            //Check that all the columns match (ignore the index_column if it exists)
            foreach (string key in this._properties.Keys)
            {
                //If it's a column and it's not the index
                if (key.StartsWith("column_") && key != "column_" + index_column)
                {
                    //Add the Key
                    sb_query.Append(key.Substring(key.IndexOf("_") + 1));

                    //Check for null entries
                    if (DBNull.Value != this._properties[key])
                    {
                        //Cool, normal check
                        sb_query.Append("=");
                        sb_query.Append("?" + key);
                        cmd.Parameters.AddWithValue("?" + key, this._properties[key]);
                    }
                    else
                    {
                        //Null is special
                        sb_query.Append(" IS NULL ");
                    }
                    sb_query.Append(" AND ");
                    //Now add the value

                }
            }
            //Now remove the  trailing comma
            sb_query.Remove(sb_query.Length - 5, 5);

            //Start the Database stuff
            using (MySqlConnection conn = new MySqlConnection(connection))
            {
                //Put the Command together
                cmd.CommandText = sb_query.ToString();
                cmd.Connection = conn;

                //Now run it!
                try
                {
                    conn.Open();
                    int count = Convert.ToInt32(cmd.ExecuteScalar());
                    if (count > 0)
                    {
                        //it exists
                        return true;
                    }
                }
                catch (Exception ex)
                {
                    LogError("DBObject.doesExist()", ex);
                }
            }

            //Cool, it passes
            return false;
        }
        /// <summary>
        /// If the record exists {doesExist()} then this will set the object's index column to the index of the existing record.
        /// </summary>
        /// <param name="table"></param>
        /// <param name="index_column"></param>
        /// <param name="connection"></param>
        protected void GetExistingIndex()
        {
            if (!canCallTable()) { throw new Exception("TableName, IndexColumn, or ConnectionString are not defined."); }
            string table = this.TableName;
            string index_column = this.IndexColumn;
            string connection = this.ConnectionString;

            //If there are no properties then there is nothing to do
            if (this._properties.Count <= 0) { return; }
            //Our Command
            MySqlCommand cmd = new MySqlCommand();
            //Build the Query
            StringBuilder sb_query = new StringBuilder();
            sb_query.Append("SELECT ");
            sb_query.Append(index_column);
            sb_query.Append(" FROM ");
            sb_query.Append(table);
            sb_query.Append(" WHERE ");

            //Check that all the columns match (ignore the index_column if it exists)
            foreach (string key in this._properties.Keys)
            {
                //If it's a column and it's not the index
                if (key.StartsWith("column_") && key != "column_" + index_column)
                {
                    //Add the Key
                    sb_query.Append(key.Substring(key.IndexOf("_") + 1));

                    //Check for null entries
                    if (DBNull.Value != this._properties[key])
                    {
                        //Cool, normal check
                        sb_query.Append("=");
                        sb_query.Append("?" + key);
                        cmd.Parameters.AddWithValue("?" + key, this._properties[key]);
                    }
                    else
                    {
                        //Null is special
                        sb_query.Append(" IS NULL ");
                    }
                    sb_query.Append(" AND ");
                }
            }
            //Now remove the  trailing AND
            sb_query.Remove(sb_query.Length - 5, 5);
            //Make sure we only get one
            sb_query.Append(" LIMIT 1");

            //Start the Database stuff
            using (MySqlConnection conn = new MySqlConnection(connection))
            {
                //Put the Command together
                cmd.CommandText = sb_query.ToString();
                cmd.Connection = conn;

                //Now run it!
                try
                {
                    conn.Open();
                    setValueForKey("column_" + index_column, cmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    LogError("DBObject.GetExistingIndex()", ex);
                }
            }
        }

        /// <summary>
        /// Get just a single object by a select command.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="command"></param>
        /// <param name="connection"></param>
        /// <returns></returns>
        static protected T SingleBySelect<T>(MySqlCommand command) where T : DBObject
        {
            //Use the list, just return a single item though
            List<T> list = DBObject.BySelect<T>(command);
            if (list.Count > 0)
            {
                return list[0];
            }
            else
            {
                return default(T);
            }
        }

        /// <summary>
        /// Get a list of T Objects from a query.
        /// The objects will have properties for every column specified by the query.
        /// The Property names will be "column_" + column_name.
        /// </summary>
        /// <typeparam name="T">Must Inherit from DBObject</typeparam>
        /// <param name="command"></param>
        /// <returns></returns>
        static protected List<T> BySelect<T>(MySqlCommand command) where T : DBObject
        {
            //Create a dummy T so we can use it's values
            DBObject dummy = (DBObject)System.Activator.CreateInstance<T>();

            //Create our list of T
            List<T> list = new List<T>();
            using (MySqlConnection conn = new MySqlConnection(dummy.ConnectionString))
            {
                //Connect the command to the connectiong
                command.Connection = conn;

                try
                {
                    //Open the connection and get the reader
                    conn.Open();
                    MySqlDataReader reader = command.ExecuteReader();

                    //I only want to get the frist record because I'm returning just one object.
                    while (reader.Read())
                    {
                        T obj = System.Activator.CreateInstance<T>();
                        //Get all the columns
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            //Get the name and the value
                            object[] parms = { "column_" + reader.GetName(i), reader[i] };
                            //Get the SetProperty Method and Call it
                            MethodInfo method = obj.GetType().GetMethod("setValueForKey");
                            method.Invoke(obj, parms);
                        }
                        //Add it to the list
                        list.Add(obj);
                    }

                }
                catch (Exception ex)
                {
                    dummy.LogError("DBObject.BySelect<" + typeof(T) + ">('" + command.CommandText + "')", ex);
                }

            }
            return list;
        }

        /// <summary>
        /// Returns true if table, index, and connection string are defined.
        /// </summary>
        /// <returns></returns>
        protected bool canCallTable()
        {
            if (string.IsNullOrEmpty(this.TableName) || string.IsNullOrEmpty(this.IndexColumn) || string.IsNullOrEmpty(this.ConnectionString))
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// Log an Error. Override this for your own Error Logging
        /// </summary>
        /// <param name="location"></param>
        /// <param name="ex"></param>
        protected void LogError(string location, Exception ex)
        {
            //Just write it to the Console
            Console.WriteLine("\n\nError:" + location + "\n" + ex.Message);
        }
    }
}

You can Go back to Part 2
or Start from the beginning