Refactoring DBObject, Part 4
The keyPath I talked about last time is just to useful to wait on. So I added it. This lets you easly get values from DBObjects within other DBObjects. So on the death date example from last time.
return (DateTime)((DBObject)valueForKey("user_info")).valueForKey("column_death_date");
is replaced with
return (DateTime)valueForKeyPath("user_info.column_death_date");
Much nicer, and easier to understand. The KeyPath checks type and all that. The valueForKey still exists because the KeyPath adds extra overhead. Unless you need it, don’t use it. But if you do need it, it’s great to have.
The DBObject is filled with all this KVC stuff, and it’s not really related to the Database stuff. So I think we need another class that DBObject can inherit from. At first I wanted to call this new class NSObject because, well that what I’ve modeled it after. But the only things it does is the KVC so I’m calling it KVCObject instead.
Here is the new KVCObject and DBObject
using System;
using System.Collections;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using MySql.Data.MySqlClient;
namespace org.ZenSoftware
{
/// <summary>
/// Delegate to observe changes in keys
/// </summary>
/// <param name="forKey">The key that was observed.</param>
/// <param name="forObject">The object the key belongs to.</param>
/// <param name="oldValue">The key's old value.</param>
/// <param name="newValue">The key's new value.</param>
public delegate void observeValueForKey(string forKey, KVCObject forObject, object oldValue, object newValue);
/// <summary>
/// An implimentation of Objective-C's Key Value coding.
/// By Chris Richards 2009.
/// </summary>
public class KVCObject
{
private Hashtable _properties;
private Dictionary<string, observeValueForKey> _kvo;
public KVCObject()
{
_properties = new Hashtable();
_kvo = new Dictionary<string, observeValueForKey>();
}
/// <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>
/// Get the value for a specified key in the keyPath.
/// All objects in the path *MUST* inherit from KVCObject for the path to be followed.
/// </summary>
/// <param name="keyPath">A Period seprated list of Key values.</param>
/// <returns></returns>
public object valueForKeyPath(string keyPath)
{
//Get our Key and the rest of the keyPath
string[] keys = keyPath.Split(".".ToCharArray(), 2);
//Did we get 2 keys?
if (keys.Length == 2)
{
//Get the Key value, then call it's valueForKeyPath
object val = valueForKey(keys[0]);
if (val is KVCObject) //Verifiy it's the right type
{
return ((KVCObject)val).valueForKeyPath(keys[1]);
}
return val;
}
//It's just a normal key
return valueForKey(keys[0]);
}
/// <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>
/// Set the value for a keyPath.
/// All objects in the path *MUST* inherit from KVCObject for the path to be followed.
/// </summary>
/// <param name="keyPath">A Period seprated list of Key values</param>
/// <param name="value"></param>
public void setValueForKeyPath(string keyPath, object value)
{
//Get our Key and the rest of the keyPath
string[] keys = keyPath.Split(".".ToCharArray(), 2);
//Did we get 2 keys?
if (keys.Length == 2)
{
//Get the Key value, then call it's setValueForKeyPath
object val = valueForKey(keys[0]);
if (val is KVCObject) //Verifiy it's the right type
{
((KVCObject)val).setValueForKeyPath(keys[1], value);
}
else
{
//Not a DBObject, just do a normal set
setValueForKey(keys[0], value);
}
}
else
{
//It's just a normal key
setValueForKey(keys[0], 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>
/// Add an observer when the key changes on the key path.
/// All objects in the path *MUST* inherit from KVCObject for the path to be followed.
/// </summary>
/// <param name="keyPath">A Period seprated list of Key values</param>
/// <param name="observer"></param>
public void addObserverForKeyPath(string keyPath, observeValueForKey observer)
{
//Get our Key and the rest of the keyPath
string[] keys = keyPath.Split(".".ToCharArray(), 2);
//Did we get 2 keys?
if (keys.Length == 2)
{
//Get the Key value, then call it's addObserverForKeyPath
object val = valueForKey(keys[0]);
if (val is KVCObject) //Verifiy it's the right type
{
((KVCObject)val).addObserverForKeyPath(keys[1], observer);
}
else
{
//Not a DBObject, just do a normal add
addObserverForKey(keys[0], observer);
}
}
else
{
//It's just a normal key
addObserverForKey(keys[0], 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>
/// Remove an observer from the Key changes.
/// All objects in the path *MUST* inherit from KVCObject for the path to be followed.
/// </summary>
/// <param name="keyPath"></param>
/// <param name="observer"></param>
public void removeObserverForKeyPath(string keyPath, observeValueForKey observer)
{
//Get our Key and the rest of the keyPath
string[] keys = keyPath.Split(".".ToCharArray(), 2);
//Did we get 2 keys?
if (keys.Length == 2)
{
//Get the Key value, then call it's addObserverForKeyPath
object val = valueForKey(keys[0]);
if (val is KVCObject) //Verifiy it's the right type
{
((KVCObject)val).removeObserverForKeyPath(keys[1], observer);
}
else
{
//Not a DBObject, just do a normal add
removeObserverForKey(keys[0], observer);
}
}
else
{
//It's just a normal key
removeObserverForKey(keys[0], observer);
}
}
}
/// <summary>
/// Base object for ORM Objects
/// By Chris Richards 2008
/// </summary>
public class DBObject : KVCObject
{
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)
{
this.TableName = table_name;
this.IndexColumn = index_column;
this.ConnectionString = connection_string;
}
/// <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 also download the new KVCObject to use in your own projects Download KVCObject
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
