protected virtual IDbCommand findallcmd(Catalogable schema, CommandType cmdType, params IDbDataParameter[] parameters)
{
IDbCommand cmd = CommandNew();
cmd.CommandType = cmdType;
switch (cmdType)
{
case CommandType.Text:
cmd = schema.Cached ? CommandNew() : whereClause(schema, parameters);
cmd.CommandType = cmdType;
cmd.CommandText = string.Format("SELECT {0} FROM [{1}] {2}", string.Join(", ", schema.Columns.Select(c => "[" + c.Name + "]")), schema.TableName, cmd.CommandText); //cmd.CommandText will be empty for schema.Cached
break;
case CommandType.StoredProcedure:
if (parameters != null && parameters.Length > 0)
{
for (int i = 0; i < parameters.Length; i++)
{
cmd.Parameters.Add(ParameterNew(parameters[i].ParameterName, parameters[i].Value));
}
}
break;
}
return(cmd);
}
protected virtual IDbCommand whereClause(Catalogable schema, params IDbDataParameter[] parameters)
{
IDbCommand cmd = CommandNew();
if (parameters == null || parameters.Length == 0)
{
return(cmd);
}
string paramqry = " WHERE ";
for (int i = 0; i < parameters.Length; i++)
{
if (schema.Columns.Any(p => p.Name == parameters[i].ParameterName))
{
paramqry += string.Format("{0} = @{1} {2}", parameters[i].ParameterName, "p" + i.ToString(), i < parameters.Length - 1 ? " AND " : "");
cmd.Parameters.Add(ParameterNew("p" + i.ToString(), parameters[i].Value));
}
else
{
throw new Exception("Parameter not found : " + parameters[i].ParameterName);
}
}
cmd.CommandText = paramqry;
return(cmd);
}
public ResultSet <T[]> LoadFromSP <T>(string sp, Type[] types, bool recursiveLoad, params object[] parameters) where T : class
{
Catalogable p = Catalogable.For(typeof(T));
IDbCommand cmd = findallcmd(p, CommandType.StoredProcedure, ParameterList(parameters));
cmd.CommandText = sp;
return(Load <T>(types, cmd, recursiveLoad));
}
public void Save(object target)
{
Catalogable schema = Catalogable.For(target);
object idval = Convert.ChangeType(schema.ID(target), schema.IDType);
string idvalStr = idval.ToString();
bool isNew = idvalStr == "" || idvalStr == "0";
Save(target, isNew);
}
protected virtual IDbCommand deletecmd(object item)
{
Catalogable schema = Catalogable.For(item);
IDbCommand cmd = CommandNew();
string qry = string.Format("DELETE FROM {0} WHERE {1}=@{2}", schema.TableName, schema.IDProperty.Name, schema.IDProperty.Name);
cmd.Parameters.Add(ParameterNew(schema.IDProperty.Name, schema.ID(item)));
cmd.CommandText = qry;
return(cmd);
}
public virtual T FindByID <T>(bool initialLoad, bool recursiveLoad, object id) where T : class
{
Catalogable p = Catalogable.For(typeof(T));
T[] results = Find <T>(initialLoad, recursiveLoad, p.IDProperty.Name, id);
if (results == null)
{
return(null);
}
return(results.Length == 1 ? results[0] : results.FirstOrDefault(obj => p.ID(obj).Equals(id)));
}
/// <summary>
/// Runs a DBCommand and adds the various results to a ResultSet object.
/// It processes the ResultSet queue after the command is complete to wire up all the "associated" properties between results.
/// Potentially more DBCommands could be generated and executed during this processing of the queue for any missing results.
/// </summary>
/// <typeparam name="T">The Type of the first (and primary) result for this call to Load</typeparam>
/// <param name="types">The full list of types that will be returned by the DBCommand</param>
/// <param name="cmd">The DBCommand to run</param>
/// <param name="recursiveLoad">Dictates whether to manually attempt to load missing results expected during wiring up the "associated properties</param>
/// <returns></returns>
protected ResultSet <T[]> Load <T>(Type[] types, IDbCommand cmd, bool recursiveLoad) where T : class
{
if (typeof(T) != types[0])
{
throw new Exception("First Type does not match Generic Type");
}
ResultSet <T[]> rs = null;
//try
{
using (IDbConnection connection = ConnectionNew()) {
cmd.Connection = connection;
connection.Open();
DbDataReader reader = (DbDataReader)cmd.ExecuteReader();
int i = 0;
while (reader.HasRows && i < types.Length)
{
try {
Catalogable pt = Catalogable.For(types[i]);
ArrayList results = new ArrayList();
while (reader.Read())
{
object obj = Activator.CreateInstance(pt.Type);
InstantiateProperties(pt, obj, s => reader[s]);
results.Add(obj);
}
if (pt.Cached && pt.Cache == null)
{
pt.Cache = results.ToArray(); //check if it's cacheable and add if not already cached
}
if (rs == null)
{
rs = new ResultSet <T[]>((T[])results.ToArray(pt.Type));
}
else
{
rs.Add(results.ToArray(pt.Type));
}
reader.NextResult();
} catch (Exception ex) { } //no error handling for now
i++;
}
connection.Close();
}
if (rs != null) //ie results found
{
rs.ProcessQueue(this, recursiveLoad);
}
} //catch (Exception ex) { }
return(rs);
}
protected static void InstantiateProperties(Catalogable t, object item, Func <string, object> valueExtractor)
{
foreach (PropertyInfo p in t.Columns)
{
object fieldValue = valueExtractor(p.Name);
if (Convert.IsDBNull(fieldValue)) // data in database is null, so do not set the value of the property
{
continue;
}
bool rightType = (p.PropertyType == fieldValue.GetType()) || (Nullable.GetUnderlyingType(p.PropertyType) == fieldValue.GetType());
p.SetValue(item, rightType ? fieldValue : Convert.ChangeType(fieldValue, p.PropertyType), null);
}
}
public override void Save(object target, bool isNew)
{
Catalogable schema = Catalogable.For(target);
if (isNew)
{
object id = ExecuteScalar(insertcmd(schema, target));
schema.IDProperty.SetValue(target, Convert.ChangeType(id, schema.IDProperty.PropertyType), null);
}
else
{
ExecuteNonQuery(savecmd(schema, target));
}
}
protected virtual IDbCommand savecmd(Catalogable schema, object item)
{
IDbCommand cmd = CommandNew();
string fields = "";
foreach (var col in schema.Columns.Where(col => col.Name != schema.IDProperty.Name))
{
fields += string.Format("{0}=@{1}, ", col.Name, col.Name);
cmd.Parameters.Add(ParameterNew(col.Name, GetParameterValue(col, item)));
}
cmd.Parameters.Add(ParameterNew(schema.IDProperty.Name, schema.ID(item)));
cmd.CommandText = string.Format("UPDATE [{0}] SET {1} WHERE {2}=@{3}", schema.TableName, fields.TrimEnd(',', ' '), schema.IDProperty.Name, schema.IDProperty.Name);
return(cmd);
}
protected virtual IDbCommand insertcmd(Catalogable schema, object item)
{
IDbCommand cmd = CommandNew();
string fieldnames = string.Empty, paramnames = string.Empty;
foreach (var col in schema.Columns.Where(col => col.Name != schema.IDProperty.Name))
{
fieldnames += string.Format("{0}, ", col.Name);
paramnames += string.Format("@{0}, ", col.Name);
cmd.Parameters.Add(ParameterNew(col.Name, GetParameterValue(col, item)));
}
cmd.CommandText = string.Format("INSERT INTO [{0}]({1}) OUTPUT Inserted.[{2}] VALUES ({3}); ", schema.TableName, fieldnames.TrimEnd(',', ' '), schema.IDProperty.Name, paramnames.TrimEnd(',', ' '));
return(cmd);
}
protected Type[] Fullify(IDbCommand cmd, Catalogable p, params IDbDataParameter[] parameters)
{
IDbCommand where = whereClause(p, parameters);
List <Type> types = new List <Type>()
{
p.Type
};
foreach (PropertyInfo property in p.Associated)
{
TypeEx tEx = property.TypeEx();
Catalogable pt = Catalogable.For(tEx.ElementType);
if (!tEx.IsListOrArray && pt.IDProperty == null)
{
continue;
}
if (pt.Cached)
{
if (pt.Cache == null && !types.Contains(tEx.ElementType))
{
cmd.CommandText += string.Format(";\r\nSELECT {0} FROM [{1}]", string.Join(", ", pt.Columns.Select(c => "[" + c.Name + "]")), pt.TableName);
types.Add(tEx.ElementType);
}
}
else
{
/* either single property select ... from assoctable where id in (select assoctableid from parenttable where ...)
* or array property select ... from assoctable where parenttableid in (select id from parenttable where ...)
*/
string assocTableKeyID = tEx.IsListOrArray ? p.Reference : pt.IDProperty.Name; //array/list issue if FK isn't same as ID Name
string parentTableKeyID = tEx.IsListOrArray ? p.IDProperty.Name : (p.HasPropertyAttribute(property) ? p.PropertyAttribute(property).KeyID : pt.Reference);
string qry = string.Format(";\r\nSELECT {0} FROM [{1}] WHERE {2} IN (SELECT {3} FROM [{4}] {5})"
, string.Join(", ", pt.Columns.Select(c => "[" + c.Name + "]"))
, pt.TableName
, assocTableKeyID
, parentTableKeyID
, p.TableName
, where.CommandText);
cmd.CommandText += qry;
types.Add(tEx.ElementType);
}
}
return(types.ToArray());
}
public override T[] Find <T>(bool initialLoad, bool recursiveLoad, params object[] parameters)
{
Catalogable p = Catalogable.For(typeof(T));
IDbDataParameter[] pl = ParameterList(parameters);
IDbCommand cmd = findallcmd(p, CommandType.Text, pl);
ResultSet <T[]> result;
if (initialLoad)
{
Type[] types = Fullify(cmd, p, pl);
result = Load <T>(types, cmd, recursiveLoad);
}
else
{
result = Load <T>(new Type[] { typeof(T) }, cmd, recursiveLoad);
}
return(result != null ? result.Result : null);
}
public void Add(object value)
{
if (value == null)
{
return;
}
TypeEx tEx = TypeEx.For(value);
Catalogable pt = Catalogable.For(tEx.ElementType);
if (tEx.IsListOrArray)
{
foreach (object obj in (IList)value)
{
Add(tEx.ElementType.FullName, pt.ID(obj), obj);
}
}
else
{
Add(tEx.Type.FullName, pt.ID(value), value);
}
}
/// <summary>
/// Go through an item's "associated" properties and attempt to find in this ResultSet. Potentially load from the MereCataloger if missing
/// </summary>
/// <param name="p">The MereCataloger to attempt to load from if required</param>
/// <param name="item">the item that is being parsed and "associated"</param>
/// <param name="recursiveLoad">Dictates whether to manually attempt to load missing results expected during wiring up the "associated properties</param>
private void InstantiateAssociated(MereCataloger p, object item, bool recursiveLoad)
{
Catalogable t = Catalogable.For(item);
object itemID = t.ID(item);
foreach (PropertyInfo property in t.Associated)
{
TypeEx tEx = property.TypeEx();
if (property.GetValue(item, null) != null)
{
continue;
}
Catalogable pt = Catalogable.For(tEx.ElementType);
if (tEx.IsListOrArray)
{
object[] result = null;
string KeyID = t.HasPropertyAttribute(property) ? t.PropertyAttribute(property).KeyID : t.Reference;
if (pt.Cached && pt.Cache != null)
{
result = pt.Cache.Where(obj => pt.ColumnValue(obj, KeyID).Equals(itemID)).ToArray();
}
else
{
//if in the results then use, else load in using Find method on the relatedBusinessObject
if (result == null && results.ContainsKey(tEx.ElementType.FullName))
{
result = results[tEx.ElementType.FullName]
.Select(obj => Convert.ChangeType(obj.Value, tEx.ElementType)) //is this needed? at the very least, can it be moved to after the where
.Where(obj => pt.ColumnValue(obj, KeyID).Equals(itemID)).ToArray();
}
//if none found but recursiveLoad is allowed, manually get the results
if ((result == null || ((IList)result).Count == 0) && recursiveLoad)
{
result = (object[])p._FindMethod(tEx.ElementType).Invoke(p, new object[] { false, false, new object[] { KeyID, itemID } });
Add(result);
}
}
if (result != null)
{
if (tEx.IsArray)
{
Array array = Array.CreateInstance(tEx.ElementType, result.Count());
Array.Copy(result, array, result.Length);
property.SetValue(item, array, null);
}
else
{
IList list = (IList)Activator.CreateInstance(property.PropertyType);
foreach (object o in (object[])result)
{
list.Add(o);
}
property.SetValue(item, list, null);
}
}
}
else //singular objects
{
object result = null;
if (pt.IDProperty == null) //possibly because it's not been "excluded"
{
continue;
}
string KeyID = t.HasPropertyAttribute(property) ? t.PropertyAttribute(property).KeyID : pt.Reference;
object id = t.ColumnValue(item, KeyID);
//if in the results then use, else load in using Find method on the relatedBusinessObject
if (pt.Cached && pt.Cache != null)
{
result = pt.Cache.FirstOrDefault(o => pt.ID(o).Equals(id));
}
else //if (result == null) {
{
if (results.ContainsKey(property.PropertyType.FullName) && results[property.PropertyType.FullName].ContainsKey(id))
{
result = results[property.PropertyType.FullName][id];
}
else if (recursiveLoad) //if none found but recursiveLoad is allowed, manually get the results
//if ((long)id != 0) {
{
if (id != null)
{
result = p._FindByIDMethod(property.PropertyType).Invoke(p, new object[] { false, false, id });
Add(result);
}
}
}
if (result != null)
{
property.SetValue(item, result, null);
}
}
}
}