DBObject with Self Updating.

I’ve added self updating and Insert to the DBObject. I’ve also changed the naming of the columns it pulls from the database. Each column property now starts with “column_”.

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

namespace ZenSoftware
{
    /// <summary>
    /// Base object for ORM Objects
    /// </summary>
    public class DBObject
    {
        protected Hashtable _properties;

        protected DBObject()
        {
            _properties = new Hashtable();
        }

        /// <summary>
        /// Get a property in the object if it exits. If not use the default_value instead.
        /// </summary>
        /// <param name="name">Name of the Property</param>
        /// <param name="default_value">Value to use if the property doesn't exist.</param>
        /// <returns></returns>
        public object GetProperty(string name, object default_value)
        {
            //Check if it exists yet
            if (!_properties.ContainsKey(name))
            {
                _properties[name] = default_value;
            }
            return _properties[name];
        }
        /// <summary>
        /// Get a property in the object if it exits. If not use the default_method to get one.
        /// </summary>
        /// <param name="name">Name of the Property</param>
        /// <param name="default_method">Method to call if the property doesn't exist.</param>
        /// <returns></returns>
        public object GetProperty(string name, Func<object> default_method)
        {
            //Check if it exists yet
            if (!_properties.ContainsKey(name))
            {
                _properties[name] = default_method();
            }
            return _properties[name];
        }
        /// <summary>
        /// Get a property from the object.
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        public void SetProperty(string name, object value)
        {
            _properties[name] = value;
        }
        /// <summary>
        /// Will return true if the value is Different from the stored property
        /// </summary>
        /// <param name="name">Name of Property</param>
        /// <param name="value">Value check the Property</param>
        protected bool isPropertyDifferent(string name, object value)
        {
            //Check if it exists yet
            if (_properties.ContainsKey(name))
            {
                //Check if it's different
                if (_properties[name] == value)
                {
                    return false;
                }
            }
            //It's different or it doesn't exist.
            return true;
        }

        /// <summary>
        /// Fill out our properties from the command.
        /// </summary>
        /// <param name="command"></param>
        /// <param name="connection"></param>
        protected void Fill(MySqlCommand command, string connection)
        {
            using (MySqlConnection conn = new MySqlConnection(connection))
            {
                //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++)
                        {
                            this.SetProperty("column_" + reader.GetName(i), reader[i]);
                        }
                    }

                }
                catch (Exception ex)
                {
                    Console.WriteLine("\nError:\n" + ex.Message);
                }

            }
        }

        /// <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(string table, string index_column, string connection)
        {
            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)
                    {
                        Console.WriteLine("\nError:\n" + ex.Message);
                    }
                }
            }
        }

        /// <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>
        public void Insert(string table, string index_column, string connection)
        {
            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)
                    {
                        Console.WriteLine("\nError:\n" + ex.Message);
                    }
                }

                //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, connection);
            }
        }

        /// <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, string connection)
        {
            //Use the list, just return a single item though
            List<T> list = DBObject.BySelect<T>(command, connection);
            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"></typeparam>
        /// <param name="command"></param>
        /// <param name="connection"></param>
        /// <returns></returns>
        static protected List<T> BySelect<T>(MySqlCommand command, string connection)
        {
            List<T> list = new List<T>();
            using (MySqlConnection conn = new MySqlConnection(connection))
            {
                //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("SetProperty");
                            method.Invoke(obj, parms);
                        }
                        //Add it to the list
                        list.Add(obj);
                    }

                }
                catch (Exception ex)
                {
                    Console.WriteLine("\nError:\n" + ex.Message);
                }

            }
            return list;
        }

    }
}

Update:

Ran into an unexpected problem. I used a bigint for my id column and was getting a conversion error when I ran the code. I thought bigint could be cast into int. I was wrong. bigint is cast to Int64. With this changed everything worked as expected.

It would also be nice if there was a way to check for duplicate records before inserting. Sometimes I might want to allow duplications, but the most of the time I don’t want them.