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(); } } }