2

I am trying to retrieve a SqlHierarchyID data-type in C#, from a DataRow that has been fetched from a SQL Server database. After the database call, if I write the following:

var id = datarow["TreeID"];

And then inspect the value of id using a watch in visual studio, it is holding the correct hierarchy value (and is non-null).

However, any and all of the following throw an InvalidCastException:

SqlHierarchyId id2 = (SqlHierarchyId) id;
SqlHierarchyId id3 = datarow.Field<SqlHierarchyId>("TreeID");
SqlHierarchyId id4 = (SqlHierarchyId)datarow["TreeID"];

Moreover, asking reflection for the assign-ability, of the two objects:

//returns false:
SqlHierarchyId id5 = new SqlHierarchyId();
return id5.GetType().IsAssignableFrom(id.GetType());

Moreover, trying a "soft cast" returns null:

SqlHierarchyId id6 = id as SqlHierarchyId;
  • Trying everything as a SqlHierarchyId? data-type is fruitless.
  • I am referencing the 32 bit assembly, and have tried compiling my solution in both 64 and 32 bit modes.
  • Initializing an empty SqlHierarchyId instance and then assigning it does not help.

Thanks for any help!

-JT

EDIT: The assembly comes from SQL Server Express 2012 (x86) SDK directory.

J T
  • 4,946
  • 5
  • 28
  • 38
  • What does `datarow["TreeID"].GetType()` return? – Tim M. Mar 12 '13 at 01:25
  • @Tim It returns"{Name = "SqlHierarchyId" FullName = "Microsoft.SqlServer.Types.SqlHierarchyId"}" – J T Mar 12 '13 at 02:45
  • Almost sounds like there are two different versions of the type. If you compare the Type from the DataRow with `typeof( SqlHierarchyId )` are they identical? I'm not sure what properties of the Type to check (maybe `GUID`?) – Tim M. Mar 12 '13 at 03:18
  • 1
    Also, are you retrieving data using a new SQL 2012 assembly, or just classes from the System.Data.Sql namespace? – Tim M. Mar 12 '13 at 03:22
  • Can you mark the post below as an answer if it address your needs? Thanks – Patrick D'Souza Apr 16 '13 at 02:09

2 Answers2

2

I had the same problem. It turned out that I added a link to the wrong Microsoft.SqlServer.Types.dll. For MS SQL 2008 it is located in C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies for MS SQL 2012 in C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies

Michael Matveev
  • 51
  • 6
  • 1
    Basically, Microsoft.SqlServer.Types version 10 should be used for SQL Server 2008. However Microsoft,SqlServer.Types edge should be used for SQL Server 2008 R2 and later (i.e 2012, 2014 etc). – Phil May 28 '15 at 22:41
1

How about using

//convert the string back into a hierarchyid
oRow["NodeKey"] = SqlHierarchyId.Parse((string)oRow["NodeString"]);

I also see that you are working with a tree, so may this code project article will help you

Patrick D'Souza
  • 3,491
  • 2
  • 22
  • 39
  • This will work if he has the "NodeString" but does not solve the original question. In his example "TreeID" is infact a SqlHierarchyId but it looks as though the SQL server has a different version of the Types DLL then the one he has referenced in his project. I am having the same issue currently. – crunchy May 08 '14 at 19:04