public int CountChildren()
{
var sql = @"
WITH q AS
(
SELECT Entity.*, [{0}].*
FROM Entity
INNER JOIN [{0}] ON [{0}].EntityGuid = Entity.Guid
WHERE Entity.ParentGuid = @Guid AND
(@ShowHidden = 1 OR Entity.Hidden = 0) AND
(@ShowDeleted = 1 OR Entity.Deleted = 0)
)
SELECT @RowCount = COUNT(*) FROM q
";
sql = String.Format(sql, childrenType.ToString());
using (var cmd = Context.CreateTextCommand(sql))
{
cmd.Parameters.Add("@UserGuid", SqlDbType.UniqueIdentifier).Value = Context.UserGuid;
cmd.Parameters.Add("@ShowHidden", SqlDbType.Bit).Value = Context.ShowHidden;
cmd.Parameters.Add("@ShowDeleted", SqlDbType.Bit).Value = Context.ShowDeleted;
cmd.Parameters.Add("@From", SqlDbType.Int).Value = DBNull.Value;
cmd.Parameters.Add("@Max", SqlDbType.Int).Value = DBNull.Value;
cmd.Parameters.Add("@RowCount", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = parentEntity.Guid;
cmd.ExecuteNonQuery();
return((int)cmd.Parameters["@RowCount"].Value);
}
}
public IEnumerable <Entity> SelectChildren(int from, int max)
{
var sql = @"
WITH q AS
(
SELECT Entity.*, [{0}].*, ROW_NUMBER () OVER ( ORDER BY Entity.Number ) AS rn
FROM Entity
INNER JOIN [{0}] ON [{0}].EntityGuid = Entity.Guid
WHERE Entity.ParentGuid = @Guid AND
(@ShowHidden = 1 OR Entity.Hidden = 0) AND
(@ShowDeleted = 1 OR Entity.Deleted = 0)
)
SELECT q.* FROM q
WHERE rn BETWEEN @From + 1 AND @From + @Max OR @From IS NULL OR @Max IS NULL
ORDER BY rn
SET @RowCount = @@ROWCOUNT
";
sql = String.Format(sql, childrenType.ToString());
using (SqlCommand cmd = Context.CreateTextCommand(sql))
{
cmd.Parameters.Add("@UserGuid", SqlDbType.UniqueIdentifier).Value = Context.UserGuid;
cmd.Parameters.Add("@ShowHidden", SqlDbType.Bit).Value = Context.ShowHidden;
cmd.Parameters.Add("@ShowDeleted", SqlDbType.Bit).Value = Context.ShowDeleted;
cmd.Parameters.Add("@From", SqlDbType.Int).Value = from;
cmd.Parameters.Add("@Max", SqlDbType.Int).Value = max;
cmd.Parameters.Add("@RowCount", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = parentEntity.Guid;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Entity item = new Entity(Context);
item.LoadFromDataReader(dr);
string classname = "Jhu.Graywulf.Registry." + item.EntityType.ToString();
Type classtype = global::System.Reflection.Assembly.GetExecutingAssembly().GetType(classname);
item = (Entity)classtype.GetConstructor(new Type[] { typeof(Context) }).Invoke(new object[] { Context });
item.LoadFromDataReader(dr);
yield return(item);
}
dr.Close();
}
}
}
/// <summary>
/// Loads the entity from the database.
/// </summary>
/// <remarks>
/// The value of the <see cref="Guid" /> property and the object context must be set.
/// The <see cref="Context" /> property must have a value of a valid object context with open database connection.
/// </remarks>
public void Load()
{
var sql = DBHelpers[this.GetType()].SelectQuery;
using (var cmd = Context.CreateTextCommand(sql))
{
AppendBasicParameters(cmd);
using (var dr = cmd.ExecuteReader())
{
dr.Read();
LoadFromDataReader(dr);
}
}
}
public IEnumerable <ItemType> FindConnection <ItemType>(Entity parent, Entity to, int?referenceType)
where ItemType : Entity, new()
{
var sql = @"
WITH q AS
(
SELECT Entity.*, [{0}].*
FROM Entity
INNER JOIN [{0}] ON [{0}].EntityGuid = Entity.Guid
INNER JOIN EntityReference ON EntityReference.EntityGuid = Entity.Guid
WHERE
Entity.ParentGuid = @ParentGuid AND
EntityReference.ReferencedEntityGuid = @EntityGuidTo AND
(@ReferenceType IS NULL OR EntityReference.ReferenceType = @ReferenceType) AND
(@ShowHidden = 1 OR Entity.Hidden = 0) AND
(@ShowDeleted = 1 OR Entity.Deleted = 0)
)
SELECT q.* FROM q
ORDER BY Number";
var childrentype = new ItemType().EntityType;
sql = String.Format(sql, childrentype);
using (var cmd = Context.CreateTextCommand(sql))
{
cmd.Parameters.Add("@UserGuid", SqlDbType.UniqueIdentifier).Value = Context.UserGuid;
cmd.Parameters.Add("@ShowHidden", SqlDbType.Bit).Value = Context.ShowHidden;
cmd.Parameters.Add("@ShowDeleted", SqlDbType.Bit).Value = Context.ShowDeleted;
cmd.Parameters.Add("@ParentGuid", SqlDbType.UniqueIdentifier).Value = parent.Guid;
cmd.Parameters.Add("@EntityGuidTo", SqlDbType.UniqueIdentifier).Value = to.Guid;
cmd.Parameters.Add("@ReferenceType", SqlDbType.Int).Value = referenceType.HasValue ? (object)referenceType.Value : DBNull.Value;
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
ItemType item = new ItemType();
item.Context = Context;
item.Parent = parent;
item.LoadFromDataReader(dr);
yield return(item);
}
dr.Close();
}
}
}
/// <summary>
/// Loads all entities of a given type.
/// </summary>
/// <typeparam name="ItemType">Type of the entities to load.</typeparam>
/// <returns>An IEnumerable interface to the loaded objects.</returns>
public IEnumerable <ItemType> FindAll <ItemType>()
where ItemType : Entity, new()
{
var type = new ItemType().EntityType;
var sql = @"
WITH q AS
(
SELECT Entity.*, [{0}].*, ROW_NUMBER () OVER ( ORDER BY Entity.Number ) AS rn
FROM Entity
INNER JOIN [{0}] ON [{0}].EntityGuid = Entity.Guid
WHERE
(@ShowHidden = 1 OR Entity.Hidden = 0) AND
(@ShowDeleted = 1 OR Entity.Deleted = 0)
)
SELECT q.* FROM q
WHERE rn BETWEEN @From + 1 AND @From + @Max OR @From IS NULL OR @Max IS NULL
ORDER BY rn
";
sql = String.Format(sql, type);
using (var cmd = Context.CreateTextCommand(sql))
{
cmd.Parameters.Add("@UserGuid", SqlDbType.UniqueIdentifier).Value = Context.UserGuid;
cmd.Parameters.Add("@ShowHidden", SqlDbType.Bit).Value = Context.ShowHidden;
cmd.Parameters.Add("@ShowDeleted", SqlDbType.Bit).Value = Context.ShowDeleted;
cmd.Parameters.Add("@From", SqlDbType.Int).Value = DBNull.Value;
cmd.Parameters.Add("@Max", SqlDbType.Int).Value = DBNull.Value;
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
ItemType item = new ItemType();
item.Context = Context;
item.LoadFromDataReader(dr);
yield return(item);
}
dr.Close();
}
}
}
public IEnumerable <T> FindChildren <T>(Entity parent)
where T : Entity, new()
{
var childrentype = Entity.EntityTypeMap[typeof(T)];
var sql = @"
WITH q AS
(
SELECT Entity.*, [{0}].*
FROM Entity
INNER JOIN [{0}] ON [{0}].EntityGuid = Entity.Guid
WHERE Entity.ParentGuid = @Guid AND
(@ShowHidden = 1 OR Entity.Hidden = 0) AND
(@ShowDeleted = 1 OR Entity.Deleted = 0)
)
SELECT q.* FROM q
ORDER BY Number
";
sql = String.Format(sql, childrentype);
using (var cmd = Context.CreateTextCommand(sql))
{
cmd.Parameters.Add("@UserGuid", SqlDbType.UniqueIdentifier).Value = Context.UserGuid;
cmd.Parameters.Add("@ShowHidden", SqlDbType.Bit).Value = Context.ShowHidden;
cmd.Parameters.Add("@ShowDeleted", SqlDbType.Bit).Value = Context.ShowDeleted;
cmd.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier).Value = parent.Guid;
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
T item = new T();
item.Context = Context;
item.Parent = parent;
item.LoadFromDataReader(dr);
yield return(item);
}
dr.Close();
}
}
}
