} // Insert()
/// <summary>
///
/// </summary>
/// <param name="o"></param>
/// <param name="fullUpdate"></param>
/// <returns>number of rows affected</returns>
public Int32 Update(DataObject o, Boolean fullUpdate = false)
{
if (!o.AuthorizeUpdate())
{
throw new Exception("Not authorized for Update.");
}
DataDefinition d = o.GetDefinition();
var dv = o.ToDictionary(fullUpdate);
var PK_key = d.PrimaryKey;
var PK_value = dv[d.PrimaryKey];
dv.Remove(PK_key);
if (dv.Count == 0)
{
return(0);
}
using (IDbCommand query = CreateCommand())
{
query.CommandText = "update [" + d.DataEntity + "]";
AppendSet(query, dv);
AppendWhere(query, PK_key, "=", PK_value);
return(ExecuteNonQuery(query));
}
} // Update();
public JoinPath(DataDefinition a, DataDefinition b, Int32 maxNodes = 512)
{
if (a == b)
{
return;
}
var q = new Queue <TreeNode <DataRelationship> >();
EnqueueRelations(q, a);
while (q.Count > 0 && maxNodes > 0)
{
TreeNode <DataRelationship> path = q.Dequeue();
DataRelationship d = path.Value;
// we found it!
if (d.RemoteEntity.GetDefinition() == b)
{
RecordPath(path);
break;
}
else
{
EnqueueRelations(q, d.RemoteEntity.GetDefinition(), path);
}
maxNodes--;
}
if (Relationships.Count == 0)
{
throw new Exception("No path exists between " + a.DataEntity + " and " + b.DataEntity);
}
}
private void EnqueueRelations(Queue <TreeNode <DataRelationship> > q, DataDefinition target, TreeNode <DataRelationship> parentPath = null)
{
foreach (DataRelationship r in target.Parents)
{
q.Enqueue(new TreeNode <DataRelationship>(r, parentPath));
}
foreach (DataRelationship r in target.Children)
{
q.Enqueue(new TreeNode <DataRelationship>(r, parentPath));
}
}
} // Find() ... (all)
/// <summary>
/// UPDATEs the provided DataObject if a PK is present, INSERTs if a PK is not present.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="o">The object to save</param>
/// <param name="fullUpdate">Whether to set nulls, 0's, and empty strings values in the update, which are otherwise ignored.</param>
/// <returns>number of rows affected</returns>
public Int32 Save(DataObject o, Boolean fullUpdate = false)
{
if (!o.Validate())
{
throw new Exception("Invalid object state for saving.");
}
DataDefinition d = o.GetDefinition();
Dictionary <String, String> v = o.ToDictionary();
if (d.PrimaryKey != null && v.ContainsKey(d.PrimaryKey) && v[d.PrimaryKey] != null)
{
return(Update(o, fullUpdate));
}
else
{
return(Insert(o, fullUpdate));
}
}
} // RelationshipPath()
public List <String> RelationshipPath(DataDefinition a, DataDefinition b, Int32 maxNodes)
{
return((new JoinPath(a, b, maxNodes)).SqlInnerJoins);
} // RelationshipPath()
public List <String> RelationshipPath(DataDefinition a, DataDefinition b)
{
return(RelationshipPath(a, b, 512));
} // RelationshipPath()
/// <summary>
///
/// </summary>
/// <param name="o"></param>
/// <param name="fullUpdate"></param>
/// <returns>number of rows affected</returns>
public Int32 Insert(DataObject o, Boolean fullUpdate = false)
{
if (!o.AuthorizeInsert())
{
throw new Exception("Not authorized for Insert.");
}
var dv = o.ToDictionary(fullUpdate);
DataDefinition d = o.GetDefinition();
Boolean GuidPK = true;
if (d.PrimaryKey != null)
{
Guid newGuid = Guid.NewGuid();
GuidPK = d.Maps[d.PrimaryKey].PropertyType.Equals(typeof(Guid));
if (GuidPK && (!dv.ContainsKey(d.PrimaryKey) || dv[d.PrimaryKey] == null || dv[d.PrimaryKey].Equals(Guid.Empty.ToString())))
{
dv[d.PrimaryKey] = newGuid.ToString();
}
}
using (IDbCommand query = CreateCommand())
{
query.CommandText = "insert into [" + o.GetDefinition().DataEntity + "]";
query.CommandText += " (" + String.Join(",", dv.Keys.ToArray()) + ")";
query.CommandText += " values (@" + String.Join(",@", dv.Keys.ToArray()) + ")";
IDbDataParameter insertid = null;
if (!GuidPK)
{
query.CommandText += " SET @insert_id = SCOPE_IDENTITY()";
insertid = query.CreateParameter();
insertid.Direction = ParameterDirection.Output;
insertid.ParameterName = "insert_id";
insertid.DbType = DbType.Int32;
insertid.Size = sizeof(Int32); // Convert.ToInt32(Math.Pow(2, 16));
query.Parameters.Add(insertid);
}
foreach (String k in dv.Keys)
{
IDbDataParameter parameter = query.CreateParameter();
parameter.ParameterName = k;
parameter.Value = dv[k] == null ? (object)DBNull.Value : (object)dv[k];
query.Parameters.Add(parameter);
}
Int32 rv = ExecuteNonQuery(query);
//
// NOTE : No idea whether the following insertid handling is correct.
// It works in my single test-case. Much more testing is required ...
//
if (rv > 0)
{
if (d.PrimaryKey != null)
{
if (GuidPK)
{
o.Populate(d.PrimaryKey, dv[d.PrimaryKey]);
}
else
{
o.Populate(d.PrimaryKey, insertid.Value.ToString());
}
}
}
return(rv);
}
} // Insert()
public List <T> Find <T>(
List <DataObject> conditions,
Boolean fuzzy = false,
String order = null,
Int32 limit = Int32.MaxValue,
T start = null) where T : DataObject, new()
{
// for grabbing the DataDefinition of T
T _t = new T();
// query components to be join()'d and append()'d to the query
DataDefinition d = _t.GetDefinition();
String distinctString = d.Distinctable ? "distinct" : "";
//List<String> tables = new List<String>() { "[" + d.DataEntity + "] [" + d.Name + "]" };
List <String> tables = new List <String>()
{
string.Format("[{0}] [{1}] ", d.DataEntity, d.Name)
};
List <DataDefinition> condition_types = new List <DataDefinition>();
Dictionary <String, List <Dictionary <String, String> > > where = new Dictionary <String, List <Dictionary <String, String> > >();
if (conditions != null && conditions.Count > 0)
{
foreach (DataObject o in conditions)
{
DataDefinition od = o.GetDefinition();
if (!where.ContainsKey(od.Name))
{
where.Add(od.Name, new List <Dictionary <String, String> >());
}
Dictionary <String, String> this_clause = new Dictionary <String, String>();
if (!condition_types.Contains(od))
{
if (od == d)
{
condition_types.Add(od);
}
else
{
List <String> path = RelationshipPath(_t.GetDefinition(), od);
if (path.Count > 0)
{
foreach (var t in path)
{
if (!tables.Contains(t))
{
tables.Add(t);
}
}
condition_types.Add(od);
}
}
}
if (condition_types.Contains(od))
{
Dictionary <String, String> c = o.ToDictionary();
if (c.Count > 0)
{
if (!String.IsNullOrEmpty(od.PrimaryKey) &&
c.ContainsKey(od.PrimaryKey) && c[od.PrimaryKey] != null)
{
this_clause.Add(od.Name + "." + od.PrimaryKey, c[od.PrimaryKey]);
}
else
{
foreach (KeyValuePair <String, String> p in c)
{
if (p.Value != null)
{
this_clause.Add(od.Name + "." + p.Key, p.Value);
}
}
}
where[od.Name].Add(this_clause);
}
}
else
{
throw new Exception("A path does not exist from " + _t.GetDefinition().DataEntity + " to " + od.DataEntity + ". Aborting query.");
}
}
}
String orderClause = "";
String fullOrderField = "";
String effectiveOrderColumn = order;
if (!string.IsNullOrEmpty(order))
{
if (order.Contains("."))
{
fullOrderField = "[" + order.Replace(".", "].[") + "]";
}
else
{
fullOrderField = "[" + d.Name + "].[" + order + "]";
}
}
else if (!String.IsNullOrEmpty(d.DefaultOrder))
{
fullOrderField = "[" + d.Name + "].[" + d.DefaultOrder + "]";
effectiveOrderColumn = d.DefaultOrder;
}
else if (!String.IsNullOrEmpty(d.PrimaryKey))
{
fullOrderField = "[" + d.Name + "].[" + d.PrimaryKey + "]";
effectiveOrderColumn = d.PrimaryKey;
}
if (fullOrderField != "")
{
orderClause = " order by " + fullOrderField;
}
String topOrderByFieldAlias = "";
if (!String.IsNullOrEmpty(fullOrderField))
{
topOrderByFieldAlias = " ___orderField, ";
}
using (IDbCommand query = CreateCommand())
{
if (limit == Int32.MaxValue) //Edit: Remove the "top" structure if no limit is being set. Anecdotally, "top" can cause query slowdowns
{
query.CommandText = "select " + distinctString + fullOrderField.Replace(" DESC", "") + " " + topOrderByFieldAlias + "[" + d.Name + "].* from " + String.Join("\r\n", tables.ToArray());
}
else
{
query.CommandText = "select " + distinctString + " top " + limit + " " + fullOrderField.Replace(" DESC", "") + " " + topOrderByFieldAlias + "[" + d.Name + "].* from " + String.Join("\r\n", tables.ToArray());
}
AppendWhere(query, where, fuzzy);
if (start != null && !String.IsNullOrEmpty(effectiveOrderColumn))
{
Dictionary <String, String> startD = start.ToDictionary();
if (startD.Keys.Contains(effectiveOrderColumn))
{
AppendWhere(query, effectiveOrderColumn, ">=", startD[effectiveOrderColumn], where.Count > 0);
}
}
query.CommandText += " " + orderClause;
return(Select <T>(query));
}
} // Find()
public virtual Dictionary <String, String> ToDictionary(Boolean includeAllFields = false)
{
DataDefinition d = GetDefinition();
Dictionary <String, String> rv = new Dictionary <String, String>();
foreach (KeyValuePair <String, IReference> m in d.Maps)
{
Object Value = m.Value.Get();
Type t = m.Value.PropertyType;
if (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable <>) && Value == null)
{
if (includeAllFields)
{
rv.Add(m.Key, null);
}
else
{
// omit the value.
}
}
else if (Value is String)
{
rv.Add(m.Key, (String)Value);
}
else if (t.Equals(typeof(Int16?)) || t.Equals(typeof(UInt16?)) || t.Equals(typeof(Int32?)) || t.Equals(typeof(UInt32?)) || t.Equals(typeof(Int64?)))
{
String v = Value.ToString();
if (includeAllFields || !String.IsNullOrEmpty(v))
{
rv.Add(m.Key, v);
}
}
else if (Value is Int16 || Value is UInt16 || Value is Int32 || Value is UInt32 || Value is Int64)
{
Int64 v = Convert.ToInt64(Value);
if (includeAllFields || v != 0)
{
rv.Add(m.Key, v.ToString());
}
}
else if (Value is Single || Value is Double || Value is Decimal)
{
Decimal v = Convert.ToDecimal(Value);
if (includeAllFields || !v.Equals(new Decimal(0)))
{
rv.Add(m.Key, v.ToString());
}
}
else if (Value is DateTime)
{
DateTime v = (DateTime)Value;
if (includeAllFields || (v != null && v != DateTime.MinValue))
{
rv.Add(m.Key, v.ToString());
}
}
else if (t.Equals(typeof(Guid?)))
{
Guid?v = (Guid?)Value;
if (includeAllFields || v.HasValue)
{
rv.Add(m.Key, (v == null || v == Guid.Empty) ? Guid.Empty.ToString() : v.ToString());
}
}
else if (Value is Guid)
{
Guid v = (Guid)Value;
if (includeAllFields || (v != null && v != Guid.Empty))
{
if (v == Guid.Empty)
{
rv.Add(m.Key, null);
}
else
{
rv.Add(m.Key, v.ToString());
}
}
}
else if (t.Equals(typeof(Boolean?)))
{
Boolean?v = (Boolean?)Value;
if (includeAllFields || v.HasValue)
{
rv.Add(m.Key, v == true ? "1" : "0");
}
}
else if (Value is Boolean)
{
Boolean v = (Boolean)Value;
if (includeAllFields || !OmitBooleansInSearch)
{
rv.Add(m.Key, v ? "1" : "0");
}
}
else if (Value is Byte[])
{
Byte[] v = (Byte[])Value;
if (includeAllFields || (v != null && v.Length > 0))
{
rv.Add(m.Key, System.Text.Encoding.UTF8.GetString(v));
}
}
}
return(rv);
} // ToDictionary()
} // ToDictionary()
public virtual void Populate(Dictionary <String, String> row)
{
DataDefinition d = GetDefinition();
foreach (KeyValuePair <String, String> pair in row)
{
if (d.Maps.ContainsKey(pair.Key))
{
IReference property = d.Maps[pair.Key];
var thisType = GetType();
Type t = property.PropertyType;
if (t.IsGenericType && t.GetGenericArguments().Length > 0)
{
t = t.GetGenericArguments()[0];
}
if (t == null)
{
var propertyType = thisType.GetProperty(pair.Key);
var fieldType = thisType.GetField(pair.Key);
if (propertyType != null)
{
t = propertyType.PropertyType;
}
else if (fieldType != null)
{
t = fieldType.FieldType;
}
else
{
t = typeof(Object);
}
}
if (!String.IsNullOrEmpty(pair.Value))
{
if (t == typeof(Int16))
{
property.Set(Convert.ToInt16(pair.Value));
}
else if (t == typeof(UInt16))
{
property.Set(Convert.ToUInt16(pair.Value));
}
else if (t == typeof(Int32))
{
property.Set(Convert.ToInt32(pair.Value));
}
else if (t == typeof(UInt32))
{
property.Set(Convert.ToUInt32(pair.Value));
}
else if (t == typeof(Int64))
{
property.Set(Convert.ToInt64(pair.Value));
}
else if (t == typeof(Single))
{
property.Set(Convert.ToSingle(pair.Value));
}
else if (t == typeof(Double))
{
property.Set(Convert.ToDouble(pair.Value));
}
else if (t == typeof(Decimal))
{
property.Set(Convert.ToDecimal(pair.Value));
}
else if (t == typeof(DateTime))
{
property.Set(Convert.ToDateTime(pair.Value));
}
else if (t == typeof(Guid))
{
Guid g = Guid.Empty;
try
{
g = new Guid(pair.Value);
}
catch
{
// do nothing.
}
property.Set(g);
}
else if (t == typeof(Nullable <Boolean>) || t == typeof(Boolean))
{
if (pair.Value != null)
{
Boolean v = false;
switch (pair.Value.ToLower())
{
case "1":
case "true":
v = true;
break;
default:
break;
}
property.Set(v);
}
}
else if (t == typeof(String))
{
property.Set(Convert.ToString(pair.Value));
}
else if (t == typeof(Byte[]))
{
property.Set(System.Text.Encoding.UTF8.GetBytes(pair.Value));
}
else
{
property.Set(pair.Value);
}
}
}
}
} // Populate(Dictionary<String,String>)