locked
Referencing Microsoft.SQLServer.Types.dll in .Net application RRS feed

  • Question

  • Hi

    I've created an application that references the Microsoft.SQLServer.Types assembly for access to the spatial types. However, when I package this up in a setup project and install it on a client machine without SQL Server installed I get the following error: 

    DataReader.GetFieldType(2) returned null

    This seems to be because it can't find the Microsoft.SQLServer.Types.dll, despite it being packaged and installed in the applications install directory. 

    In my visual studio solution I have tried copying the microsoft.sqlserver.types.dll file into a specific folder (i.e. not directly using the one from the microsoft install dir) and then referencing that in the project and choosing copy local.

    When I install the application using the msi generated by the setup project, the dll gets copied to the correct folder as expected, but then when I run the application I still get the above error. 

    After doing some searches it seems like I might have to install the SQLSysCLRTypes.msi redistributable package, it this really necessary just for one dll? Ideally I want the installer to install this as a pre-requisite, but that doesn't look straightforward. Has anyone successfully referenced the microsoft.sqlserver.types.dll in a .net application and got it working on a machine without any SQL Server installation without installing the SQLSysCLRTypes.msi package? 

    This all seems like a lot of work just to use the sql server spatial types. Surely these should be part of the .Net Framework as per the other sql server types? Not needing to install additional client software has always been a plus over using Oracle. 

    Anyway, any help/advice on this would be most appreciated.

    Cheers

    John

    Sunday, February 26, 2012 5:08 PM

Answers

  • If all you want to do is retrieve the numeric coordinates of a table of points, you'd be even better off doing SELECT SHAPE.STX, SHAPE.STY in your query... that way you get straight to the coordinates and don't even need to parse them out of the WKT string...

    twitter: @alastaira blog: http://alastaira.wordpress.com/

    • Marked as answer by gisgeezer Monday, February 27, 2012 11:55 AM
    Monday, February 27, 2012 11:37 AM
    Answerer

All replies

  • Hi,

    SQL Server spatial functionality relies on two libraries - the managed library Microsoft.SqlServer.Types.dll, and the unmanaged library SQLServerSpatial.dll - make sure you include both in your package. I wrote an article about deploying a simple .NET application to Windows Azure here, but the process is largely the same for a deployment to any other client:

    http://alastaira.wordpress.com/2011/08/19/spatial-applications-in-windows-azure-redux-including-denali/

    Also, you don't mention what version of the library you're trying to use, but there is some interesting behaviour that differs between SQL 2008/R2/2012 when you try to use certain syntax such as GetValue() on a datareader, and could well apply to GetFieldType() too. See here:

    http://alastaira.wordpress.com/2011/09/11/cant-cast-from-sqlgeometry-to-sqlgeometry-and-other-strange-version-conflicts-with-sql-server-denali-microsoft-sqlserver-types/


    twitter: @alastaira blog: http://alastaira.wordpress.com/

    Sunday, February 26, 2012 5:29 PM
    Answerer
  • Hi

    Thanks for the reply. I'm using SQL Server 2008 (not R2) and the assembly version is 10.0.0.0. 

    After wasting several hours on this already, I've actually decided to re-write the small amount of code that uses the SQL Server types to return geometry as OGC WKT (eg SHAPE.STAsText()) instead. As the geometries are only points, it's then fairly simple to get at the coordinates with some string processing. I can then get rid of my reference to the Microsoft.SqlServer.Types assembly altogether. 

    John

    Monday, February 27, 2012 11:21 AM
  • If all you want to do is retrieve the numeric coordinates of a table of points, you'd be even better off doing SELECT SHAPE.STX, SHAPE.STY in your query... that way you get straight to the coordinates and don't even need to parse them out of the WKT string...

    twitter: @alastaira blog: http://alastaira.wordpress.com/

    • Marked as answer by gisgeezer Monday, February 27, 2012 11:55 AM
    Monday, February 27, 2012 11:37 AM
    Answerer
  • Ok - great thanks. Didn't see that in the documentation for geometry functions - I was looking for something like that. 
    Monday, February 27, 2012 11:53 AM