DataReader.GetFieldType returned null

Question

In my db table Layout, there's one column whose type is hierarchyid (column index=4). When trying to set-up new environment (a virtual web-server, created from XEN server), then running the site, I've met with this issue:

Exception message: DataReader.GetFieldType(4) returned null. Exception data: System.Collections.ListDictionaryInternal

I've made some search and found out there are already some topic on it (such as on MSDN).

But even when I added the C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Types.dll library, it seems like db type in structure SqlHierarchyId doesn't get recognized.

"Exception at DataReader.GetFieldType(4) returned null" is still thrown out.

Note: The issue will be solved if I made installation of C# package in VS2010 onto the environment (Windows Server 2008 RC2), but my boss didn't accept that, because this is purely a simple web-server.

Answer 1

Reference the Microsoft.SQLServer.Types dll from the project and for the reference set it as "Copy Local" in the properties of the reference. This will package that DLL up with the website when you deploy it. Then you don't need all of SQL Server installed on your web box in order to use the SQL Server data types. I did this for my website because it was using the geography data type columns and I was getting the same error.

Answer 2

I tried to resolve this issue by adding the Microsoft.SqlServer.Types NuGet package to my project, but that alone did not help. I also had to add the following to the <assemblyBinding> element of my project's App.config:

<runtime>
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
  .
  .
  .
    <dependentAssembly>
      <assemblyIdentity name="Microsoft.SqlServer.Types" publicKeyToken="89845dcd8080cc91" culture="neutral" />
      <bindingRedirect oldVersion="0.0.0.0-14.0.0.0" newVersion="14.0.0.0" />
    </dependentAssembly>
  .
  .
  .
  </assemblyBinding>
</runtime>