version 1.0.2005.1 final (self-servicing) VS2005 asp.net 2.0

Hiya,

I have a bit of sql that creates a temp table.All the fields are from the same table.

SQL

    @categoryID int
AS
    DECLARE @parentID int
    DECLARE @category varchar(50)
    DECLARE @categoryGUID nvarchar(50)
    DECLARE @output varchar(500)
    DECLARE @loopCount int
    SET @parentID=9999
    
    CREATE TABLE #cats(
        categoryID int,
        category varchar(50),
        categoryGUID nvarchar(50),
        listOrder int
    )
    
    SET @loopCount=1
    
    while(@parentID <>0 AND @loopCount<10)
    BEGIN
        SELECT @parentID=parentID, 
                @category=categoryName, 
                @categoryGUID=categoryGUID  
        FROM CSK_Store_Category WHERE categoryID=@categoryID
        
        IF(@category<>'')
            SET @output=@output+';'+@category
        
        INSERT INTO #cats(categoryID, category, categoryGUID, listOrder) VALUES(@categoryID, @category, @categoryGUID, @loopCount)
        SET @categoryID=@parentID
        SET @loopCount=@loopCount+1
    END
    SELECT * FROM #cats ORDER BY listOrder DESC
    DROP Table #cats
    RETURN

I’m looking to replicate it into a typedList. I’ve no idea where to start.I assume that I have to use a predicate to create the temp table? Or maybe llblGenPro uses a different way of working?

Any help appreciated.

Many thanks,

yogi

Hi yogi,

Aparently you need to retrieve a superChild StoreCategoryEntity and all its parents until the root, its that true?

Do you have already a DB relation or Custom Relation (LLBLGen) between _StoreCategoryEntity.CategoryID _and StoreCategoryEntity.ParentID?

Why do you need a _TypedList _instead of StoreCategoryEntityCollection?

buenas David,

Do you have already a DB relation or Custom Relation (LLBLGen) between StoreCategoryEntity.CategoryID and StoreCategoryEntity.ParentID?

No, I don't have any llblGen relation / other relation The schema is:

CSK_Store_Category categoryID PK parentID

So, it's a single table with a single primary key.All the schema stuff has to stay the same.

I don't NEED it to be a typedList, but it will be read-only, so I thought it should be a typedList.

As I say the sql i have works fine.But I need an llblGenPro solution to this problem.

how should i proceed?

cheers,

yogi

One approach would be:

• Add custom relation in LLBLGen designer (Ref: LLBLGenPro Help - Using the designer - Adding custom relations): StoreCategoryEntity.CategoryID - StoreCategoryEntity.ParentID. (This do not require changes in your DB schema )

• Use some code like:

/// <summary>
/// Fill the passed collection (categoryResults) with the descent of category
/// </summary>
/// <param name="category">category</param>
/// <param name="categoryResults">returns the descent filled</param>
public void GetCategoryDescent(MyCategoryEntity category, ref MyCategoryCollection categoryResults)
{
    // add the category current traversed
    categoryResults.Add(category);

    // if has a parent
    if (!category.TestOriginalFieldValueForNull(MyCategoryFieldIndex.ParentCategoryId))
    {
        // add the grandfathers of category father
        GetCategoryDescent(category.MyParentCategory, ref categoryResults);
    }

    // at this point all the descent was added
}

public void TestRetrieveRecursiveFromSameTable()
{
    // category which we want to retrieve the descent
    MyCategoryEntity myCategory = new MyCategoryEntity(10);

    // get the descent
    MyCategoryCollection categoryDescent = new MyCategoryCollection();
    GetCategoryDescent(myCategory, ref categoryDescent);

    // show results
    foreach (MyCategoryEntity category in categoryDescent)
    {
        Console.WriteLine(category.Name);
    }
}

This approach have the same number of SELECT's that your SP, however doesn't have INSERT's and other loops.

Other approach: if at some place you have the DEEP of the category, you could add _deep _times the subPrefetchPath.

This thread should be very helpful if you want other approaches more efficient: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3208

Suerte

I solved this problem via the following approach which only uses 1 select .

Actual code comments:

First grab all folders for the module. Then create a hashtable, and store <id:entity node> combinations. Then traverse the set of entities from front to back and for each entity create a new entity node and add it to the hashtable. Then use the hashtable to select the parent for the entity and add the node to the parent's node, which is obtained from the hashtable's nodelist. [Thanks Otis]

Here is generic real world code that this turns in to (note: there is unrelated code here, but hopefully you can pick out the parts based on the above desc):

private static void BuildTreeCollectionGeneric(IEntity ModuleEntity, EntityField FilterField, EntityType BranchType, string PrimaryKey, string ParentKey, string ModuleKey, string RootKey, string ChildObjects, string RootObject, string AlreadyFetchedProperty)
// Yes yes, I know I should have used EntityFields, but this was before I really understood LLBL
        {
            DateTime ActionStartTime = DateTime.Now;
            IEntityCollection col = GeneralEntityFactory.CreateCollection(BranchType);
            IPredicate filter = (FilterField == (int)ModuleEntity.Fields[ModuleKey].CurrentValue);
            col.GetMulti(filter);

            if (col.Count == 0) return;

            Hashtable ht = new Hashtable(col.Count + 20);
            for (int f = 0; f < col.Count; f++)
            {
                ht.Add((int)col[f].Fields[PrimaryKey].CurrentValue, col[f]);
                SetObjectProperty(col[f], AlreadyFetchedProperty, true);
            }

            for (int f = 0; f < col.Count; f++)
            {
                if ((int?)col[f].Fields[ParentKey].CurrentValue != null)
                {
                    object o = GetObjectProperty((IEntity)ht[(int)col[f].Fields[ParentKey].CurrentValue], ChildObjects);
                    if (o != null)
                        ((IEntityCollection)o).Add(col[f]);
                }
                else if ((int?)ModuleEntity.Fields[RootKey].CurrentValue == (int)col[f].Fields[PrimaryKey].CurrentValue)
                    ModuleEntity.SetRelatedEntity(col[f], RootObject);
            }
        }

And that wouldn't be complete without a sample call:

BuildTreeCollectionGeneric(dlmc[f], DocumentLibraryFolderFields.DocumentLibraryModuleId, EntityType.DocumentLibraryFolderEntity, "DocumentLibraryFolderId", "ParentDocumentFolderId", "DocumentLibraryModuleId", "RootDocumentFolderId", "ChildFolders", "RootFolder", "alreadyFetchedChildFolders"); 

hiya,

ok, i have looked at both examples.At the moment, I simply want it to want work in the same way as the sql I supplied earlier:

@categoryID int
AS
    DECLARE @parentID int
    DECLARE @category varchar(50)
    DECLARE @categoryGUID nvarchar(50)
    DECLARE @output varchar(500)
    DECLARE @loopCount int
    SET @parentID=9999
    
    CREATE TABLE #cats(
        categoryID int,
        category varchar(50),
        categoryGUID nvarchar(50),
        listOrder int
    )
    
    SET @loopCount=1
    
    while(@parentID <>0 AND @loopCount<10)
    BEGIN
        SELECT @parentID=parentID,
                @category=categoryName,
                @categoryGUID=categoryGUID
        FROM CSK_Store_Category WHERE categoryID=@categoryID
        
        IF(@category<>'')
            SET @output=@output+';'+@category
        
        INSERT INTO #cats(categoryID, category, categoryGUID, listOrder) VALUES(@categoryID, @category, @categoryGUID, @loopCount)
        SET @categoryID=@parentID
        SET @loopCount=@loopCount+1
    END
    SELECT * FROM #cats ORDER BY listOrder DESC
    DROP Table #cats
    RETURN

Now, all I need to display is typedList (preferably) or collection that contains 2 fields from Csk_Store_Category table: categoryGUID categoryName

So, question #1, can I use a typedList / does it have to be an entityCollection to store the end results?

(obvioulsy I need to use other fields to create the recursive list, but I only need the 2 fields as an output)

Taking Matt's as the starting example, I can obtain the categoryGuid filter:

IPredicateExpression filtProductCategory = new PredicateExpression();           
Guid g = new Guid(categoryGUID);
        filtProductCategory.Add(dalHamilton.HelperClasses.CskStoreCategoryFields.CategoryGuid == g);

I will only ever supply a SINGLE categoryGUID..So, question #2 I think that this will work for me?

Question #3 As far as the collection / typedList that will hold the data, I assume I can use a concrete collection type, becasue I know what it will be:

dalHamilton.CollectionClasses.CskStoreCategoryCollection = new CskStoreCategoryCollection();

This is as far as I have managed.If anyone could confirm any of the above it'd be a great help.As I said before, I want to get it working as simply as possible.Then, I iprove performance later if necessary.

many thanks,

yogi

yogiberr wrote:

So, question #1, can I use a typedList / does it have to be an entityCollection to store the end results?

Yes, you can.

yogiberr wrote:

I will only ever supply a SINGLE categoryGUID..So, question #2 I think that this will work for me?

It should be.

yogiberr wrote:

Question #3 As far as the collection / typedList that will hold the data, I assume I can use a concrete collection type, becasue I know what it will work:

dalHamilton.CollectionClasses.CskStoreCategoryCollection = new CskStoreCategoryCollection();

Yes you can use a collection, but in relation to question#1. Is this (question#3) the same as question#1?

hiya,

Ok, I can confirm that I'll be (trying to) use a typedList.

IPredicateExpression filtProductCategory = new PredicateExpression();
Guid g = new Guid(categoryGUID);
        filtProductCategory.Add(dalHamilton.HelperClasses.CskStoreCategoryFields.CategoryGuid == g);

dalHamilton.TypedListClasses.TListCategoryCrumbsTypedList tListCategoryCrumbs = new TListCategoryCrumbsTypedList());

tListCategoryCrumbs.Fill(0, null, false, filtProductCategory, null);

Now, the above code retreives a typedList that is populated by a single row, that of the categoryGUID that was supplied.

What I want it to do, based on the categoryGUID, arg, is:

IF this particular category has a parentID == 0 then simply return this category

ELSEIF this particular category has a parentID <> 0 then, (as above) return the category

PLUS the category that is referred to in the parentID

eg, if: 1) the categoryGUID refers to the "Shelves" category, 2) and the "Shelves" category parentID = 0, then simply return the shelves category.

IF: 1) the categoryGUID refers to the "SLATWALL Shelves" category, 2) and the " SLATWALL Shelves" category parentID = 1,

then return the: a) SLATWALL shelves category. b) category whose categoryId = 1

I hope this makes sense.Please let me know if it doesn't

cheers,

yogi

Hi,

If you want a different structure of result depending of the content of your typedlist, you need to make the switch in .net. You can also retreave the 2 value each time and show the parent category only if the parentid<>0. Did you directly bind the result to a grid/dropdown or did you use it directly in your code?

hiya,

I haven't done anything yet, only the code that you see.I won't be binding to a grid, all I wil be doing is creating a breadcrumb navigation.With that in mind, how do you think I shoul proceed? I suppose it might even be better to bring back the entire contents into the typedList (ie, all the rows in the table) then filter them?

many thanks,

yogi

Since you want some logic in your query, you should either use a storedProcedure or do this logic in code.

For the second option, if you use a TypedList, it would be better to fetch all the fields that you want at one go, then you can define a custom property/column in the TypedList to return based on some logic the data that you want.

hiya,

ok, I will bring back entire typedList then grab the data that I really need. My previous code filtered the typedList via a predicate:

IPredicateExpression filtProductCategory = new PredicateExpression();
Guid g = new Guid(categoryGUID);
        filtProductCategory.Add(dalHamilton.HelperClasses.CskStoreCategoryFields.CategoryGuid == g);

dalHamilton.TypedListClasses.TListCategoryCrumbsTypedList tListCategoryCrumbs = new TListCategoryCrumbsTypedList());

tListCategoryCrumbs.Fill(0, null, false, filtProductCategory, null);

Obviously, I now shouldn't use that predicate (categoryGUID) when I populate the typedList, but I should use categoryGUID to "find" the correct row within the typedList, AFTER it has been populated.

So, I try this:

dalHamilton.TypedListClasses.TListCategoryCrumbsTypedList tListCategoryCrumbs = new TListCategoryCrumbsTypedList();
tListCategoryCrumbs.Fill(0, null, false, null, null);

object currRow;
currRow = tListCategoryCrumbs.Select("categoryGUID =" + categoryGUID);

error: <<missing operand after f1 operator

Any ideas, All I want to do is find the row that has this catagoryGUID then see if it has a parent row?

many thanks,

yogi

Hello,

if categoryGUID is a nvarchar, did you try do defined your expression like this (using a quote):

currRow = tListCategoryCrumbs.Select("categoryGUID ='" + categoryGUID+ "'" );

hiya,

yes, that worked, thanks.

Now, instead of using an object, I want to store currRow into the most suitable datatype.

I have tried:

System.Data.DataRow currRow;
currRow = tListCategoryCrumbs.Select("categoryGUID ='" + categoryGUID+ "'");

error: <<Cannot implicitly convert type 'System.Data.DataRow[]' to 'System.Data.DataRow>>

I think I am nearly there.Can anyone tell me the best datatype to use?

many thanks,

yogi

Use a 'System.Data.DataRow[]' instead.

thanks Walaa.

I now have other issues, bu that's for a differnt post :-)

yogi