It actually simplifies things a lot, to the point I'm starting to double think making everything static.
As you can see, you can simplly call something like:
DBUser chris = (DBUser)DBObject.By("firstname", "chris", typeof(DBUser), Utility.connMy);
DBUser bob = (DBUser)DBObject.By("id", "1327", typeof(DBUser), Utility.connMy);
And get back a valid user.
This isn't prefect, for example, what if there are two people named Chris? this would only return an object for the first record returned. Which leads into my next step, returning a list of DBObjects.
///
/// This is the baseclass for DB Object created by Chris Richards
///
[Serializable]
public class DBObject
{
//Holds all the properties in the object
protected Hashtable _properties = new Hashtable();
protected DBObject() { }
///
/// Get the Object By a single property
///
/// The Property you want to get the Object By
/// Value of the Property
/// Type of the object to get (Must inherent from DBOBject)
/// Connection String to use
///
static protected DBObject By(string property, object value, Type who_type, string connection)
{
DBObject obj = null; //Either it will be set to a valid object, or it will remain null
//Find the Column Name for the property requested
string column_name = DBObject.GetColumn(property, who_type);
if (column_name != string.Empty)
{
//Append the WHERE clause
string query = DBObject.SelectQuery(who_type) + " WHERE " + column_name + "=?value";
using (MySqlConnection conn = new MySqlConnection(connection))
{
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("?value", value);
try
{
conn.Open();
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
obj = (DBObject)DBObject.FromReader(reader, who_type);
}
conn.Close();
}
catch (Exception ex)
{
Console.WriteLine("\n\nERROR:\n" + ex.Message);
}
}
}
//Return what we have
return obj;
}
///
/// Gets the Column name of a Property
///
/// Name of the Property
/// Type of the object to get (Must inherent from DBOBject)
///
static protected string GetColumn(string property, Type who_type)
{
foreach (PropertyInfo who_properity in who_type.GetProperties())
{
//Find the matching property
if (who_properity.Name.ToLower() == property.ToLower())
{
foreach (object attribute in who_properity.GetCustomAttributes(false))
{
//Get the column
if (attribute is DBColumn)
{
return ((DBColumn)attribute).Column;
}
}
}
}
//Couldn't find it
return string.Empty;
}
///
/// Gets the SELECT ... FROM query for this object
///
/// Type of the object to get (Must inherent from DBOBject)
///
static protected string SelectQuery(Type who_type)
{
//Generate the Select Query
string _select = "SELECT";
//Now Set all the select columns
foreach (PropertyInfo properity in who_type.GetProperties())
{
foreach (object attribute in properity.GetCustomAttributes(false))
{
if (attribute is DBColumn)
{
_select += " " + ((DBColumn)attribute).Column + ",";
}
}
}
//Remove the last Comma
_select = _select.Substring(0, _select.Length - 1);
//Get the Table Name for this Object
foreach (object attr in who_type.GetCustomAttributes(false))
{
if (attr is DBTable)
{
//Add the Table to our List
_select += " FROM " + ((DBTable)attr).Table;
}
}
//Now Return it
return _select;
}
///
/// This will populate the object from the reader.
/// It will not advance the reader.
///
///
/// Type of the object to get (Must inherent from DBOBject)
///
static protected DBObject FromReader(MySqlDataReader reader, Type who_type)
{
DBObject who = (DBObject)Activator.CreateInstance(who_type, true);
//Look for all the properities
foreach (PropertyInfo properity in who_type.GetProperties())
{
if (properity.CanWrite)
{
object[] attributes = properity.GetCustomAttributes(false);
foreach (object attribute in attributes)
{
if (attribute is DBColumn)
{
try
{
properity.SetValue(who, reader[((DBColumn)attribute).Column], null);
}
catch (IndexOutOfRangeException)
{
//Just Skip it
}
catch (Exception ex)
{
string junk = ex.Message;
}
}
}
}
}
return who;
}
}
Here is my test object.
[Serializable]
[DB.MetaData.DBTable("users")]
class DBUser : DBObject
{
[DBColumn("first_name")]
public string FirstName
{
get
{
if (!_properties.ContainsKey("first_name"))
{
_properties["first_name"] = string.Empty;
}
return (string)_properties["first_name"];
}
set
{
_properties["first_name"] = value;
}
}
[DBColumn("last_name")]
public string LastName
{
get
{
if (!_properties.ContainsKey("last_name"))
{
_properties["last_name"] = string.Empty;
}
return (string)_properties["last_name"];
}
set
{
_properties["last_name"] = value;
}
}
public string FullName
{
get { return this.FirstName + " " + this.LastName; }
}
[DBColumn("id", true)]
public int ID
{
get
{
if (!_properties.ContainsKey("id"))
{
_properties["id"] = -1;
}
return (int)_properties["id"];
}
set
{
_properties["id"] = value;
}
}
protected DBUser() { }
static public void DEBUG()
{
string suery = DBObject.SelectQuery(typeof(DBUser)) + " WHERE id=?id";
Console.Write("Going to run: " + suery);
using (MySqlConnection conn = new MySqlConnection(Utility.connMy))
{
MySqlCommand cmd = new MySqlCommand(suery, conn);
cmd.Parameters.AddWithValue("?id", 1327);
try
{
conn.Open();
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
DBUser user = (DBUser)DBObject.FromReader(reader, typeof(DBUser));
Console.WriteLine("\nGot: " + user.FullName);
}
conn.Close();
}
catch (Exception ex)
{
Console.WriteLine("\n\nERROR:\n" + ex.Message);
}
}
//Now try the properity
Console.WriteLine("\n\nTrying the By Methiod (ID)");
DBUser u2 = (DBUser)DBObject.By("id", "1327", typeof(DBUser), Utility.connMy);
if (u2 != null)
{
Console.WriteLine("Got: " + u2.FullName);
}
else
{
Console.WriteLine("Oops, got a null back");
}
//Try another property
Console.WriteLine("\n\nTrying the By Methiod (FirstName)");
DBUser u3 = (DBUser)DBObject.By("firstname", "chris", typeof(DBUser), Utility.connMy);
if (u3 != null)
{
Console.WriteLine("Got: " + u3.FullName);
}
else
{
Console.WriteLine("Oops, got a null back");
}
}
}
