Convert a string to a hierarchyid in C#

Question

Hello guys/girls,

I need to be able to convert a string to a hierarchyid in c#.net - I cannot use stored procedures.

When I pass in the path (string) the query fails as the path is stored like this '/' instead of /

Can I convert it to another type?

SqlCommand command = new SqlCommand("INSERT Structure (Path,Description,ParentID) " +
    "VALUES(" + path + ".GetDescendant(" + lastChildPath +
    ", NULL) " +
    ",@description, @parentId", _connection);

-- BitKFu

I have added the and this is the sql query it produces:

CommandText = "INSERT Structure (Path,Description,ParentID) VALUES(CAST(/ AS hierarchyid).GetDescendant(NULL, NULL) ,@description, @parentId"

I get the following error: ex = {"Incorrect syntax near '/'."}

-- ck

This is what I am expecting

"INSERT Structure (Path,Description,ParentID) VALUES(/.GetDescendant(NULL, NULL) ,'Test', 1"

-- Paul Ruane

I have looked at this page already but it didnt really help, unless I overlooked something?

Thanks

Clare

Answer 1

I would try to cast it, if only the '' is wrong.

SqlCommand command = new SqlCommand("INSERT Structure (Path,Description,ParentID) " +
    "VALUES(CAST('"+path+"' AS hierarchyid).GetDescendant(" + lastChildPath +
    ", NULL) " +
    ",@description, @parentId", _connection);