SQL 2008: Using hierarchyid with LLBLGen?

Posts   
 
    
pat
User
Posts: 215
Joined: 02-Mar-2006
# Posted on: 10-Aug-2008 07:49:00   

Hi,

SQL Server 2008 has been released a few days ago: http://sqlblog.com/blogs/aaron_bertrand/archive/2008/08/06/sql-server-2008-has-rtm-d.aspx

I was wondering if there are any plans to support the new hierarchyid datatype and its functions: http://msdn.microsoft.com/en-us/library/bb677173(SQL.100).aspx http://blogs.msdn.com/manisblog/archive/2007/08/17/sql-server-2008-hierarchyid.aspx

Thanks, Patrick

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 10-Aug-2008 11:05:13   

No, as the fetch logic in hte O/R mapper core is generic code for all databases, and this requires a specific piece of code especially for sqlserver 2008.

If you want to store hierarchies in a database, you can do that with several different methods, e.g. the CELKO way with balanced trees or with precalculated tables with parent-child pairs.

The 4 datatypes added to sqlserver are supported btw.

Frans Bouma | Lead developer LLBLGen Pro
pat
User
Posts: 215
Joined: 02-Mar-2006
# Posted on: 21-Nov-2008 10:53:29   

Thank you... Makes sense as hierachyid is very SQL server specific

Patrick

frakevich
User
Posts: 6
Joined: 03-Sep-2009
# Posted on: 03-Mar-2010 23:00:03   

Hi, the HierarchyId column maps to the varchar datatype in llblgen. This causes an error: Unable to cast object of type 'Microsoft.SqlServer.Types.SqlHierarchyId' to type 'System.String'. In order for this cast to work the query must include [FieldName].ToString(). Is there a way to specify the ToString() function call on the Hierarchy fields?

Thank you

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 04-Mar-2010 04:32:04   

Mmmm. This is not built-in supported for the reason explained above. You can modify the templates to emit the ToString in case the type is HierarchyId. We don't warranty this would work 100% for all situations. You can give it a try though.

David Elizondo | LLBLGen Support Team
frakevich
User
Posts: 6
Joined: 03-Sep-2009
# Posted on: 04-Mar-2010 07:30:09   

Thank you for getting back to me! Could you please point me to the location of the template and what to look for in that file? I have never ventured that deep into customization, so I am not well familiar with the templates.

Thank you!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 04-Mar-2010 17:09:43   

frakevich wrote:

Thank you for getting back to me! Could you please point me to the location of the template and what to look for in that file? I have never ventured that deep into customization, so I am not well familiar with the templates.

Thank you!

types are assigned in the driver, so you should check the sqlserver driver code in the SDK.

Be aware though that he hierarchyid is not something you can use to fetch hierarchical queries using our API, so I'm not sure what your goal is with the change.

Frans Bouma | Lead developer LLBLGen Pro
frakevich
User
Posts: 6
Joined: 03-Sep-2009
# Posted on: 05-Mar-2010 15:13:38   

Hi, my initial plan was to use the field in a where clause and I wanted to have llblgen generate the correct object alias. The issue I encountered (may be because I did something wrong) was that if I setObjectAlias on a schema field then it gets converted to "LPA_c1", but when I set it in this way to the HierarchyId field (that I had to omit from the schema due to the error outlined previously) new EntityField2("FolderPath", null).SetObjectAlias("sf"), then the alias would not get substituted. So I thought it would help to keep the field in the schema, and also I could use the string representation of node's path to easily recreate the tree programmatically. But I modified the query to not have to use object aliases, so I am good for now. Another feature that might come useful with sql server adding programmatic attributes to fields would be ability to support things like [Field].FunctionName. I know this is not a generic feature, but I am sure that people living their lives in the Sql Server world would appreciate all things that make their lives easier simple_smile

Thank you for the great product and support!