2

I've written an ASP.Net 4 Application which has been working perfectly. However, I decided to make use of the new Geography Data Type in Sql Sever 2012. This worked perfectly on my local machine but fails when I upload.

I get there error "DataReader.GetFieldType(9) returned null." when doing a usual "select * FROM Table" query on a table that contains one of these datatypes.

I have searched the various threads regarding adding a reference to the Microsoft.SqlServer.Types.dll into my project and changing Copy Local to true. However I am getting the same error.

I run the database and the web server on two separate servers so I don't think there are any assemblies on the web server unless I upload them with my project.

Am I missing any other assemblies that are needed? or any other settings?

I've taken days trying to solve this and uploading variouse libraries. Any help would be appreciated.

dontbesorry80
  • 567
  • 1
  • 6
  • 12

4 Answers4

0

From what I can tell you will at least need SQL Server installed locally when you initially add the reference. If you can I would try and install SQL Express locally first (you do not need to use it just install it) and then try your code again to see if that is the problem.

References:

DataReader.GetFieldType returned null

http://blogs.bing.com/maps/2013/08/05/advance-spatial-queries-using-entity-framework-5/

Edit:

I am more familiar with using spatial types via the entity framework. It seems if you want to use them directly you need to use a nuget package in order to get the required DLLs into the project. From the Microsoft ADO.NET blog page

SqlServerSpatial110.dll – This is a native assembly so it cannot be added as a project reference.

References:

Microsoft ADO.NET Blog

http://blogs.msdn.com/b/adonet/archive/2013/12/09/microsoft-sqlserver-types-nuget-package-spatial-on-azure.aspx

Relevant SO Questions

'Microsoft.SqlServer.Types' version 10 or higher could not be found on Azure https://gis.stackexchange.com/questions/382/how-can-i-use-sql-servers-spatial-types-from-a-net-application

Community
  • 1
  • 1
KHeaney
  • 785
  • 8
  • 18
  • Thanks for this. However, this is exactly what I have done. I have SQL Server 2012 installed locally and added the reference using Assemblies -> Extensions -> Microsoft Sql Server Types. But still no joy – dontbesorry80 Aug 26 '14 at 13:58
  • What version of SQL are you running on the other server. Just want to make sure that the local SQL 2012 DLLs are not conflicting with your server geography datatype. As Martin Smellworse stated in his answer there are a lot of changes in the types between versions and if they are different it may not work. – KHeaney Aug 26 '14 at 14:15
  • The server is running 11.0.3000.0 (Microsoft SQL Server Express (64-bit)). The local version is 11.0.3128.0 (Microsoft SQL Server Express (64-bit)).. – dontbesorry80 Aug 26 '14 at 14:22
  • So, I tried pulling the Microsoft.SqlServer.Types.dll from the database server and adding that to my bin folder. Then referencing it. However still no joy. It works locally but not on the server. – dontbesorry80 Aug 26 '14 at 14:26
  • Are you using the same Entity Framework version on both local and server as well Entity Framework 5 does not support spatial types on .NET 4 only .NET 4.5 Source: http://msdn.microsoft.com/en-us/data/dn194325.aspx – KHeaney Aug 26 '14 at 14:35
  • I'm not using entity framework for this application. So this shouldn't have any effect right? – dontbesorry80 Aug 26 '14 at 17:34
  • Thanks for the additional information I've tried installing the NuGet package and it didn't help. Although it did give me another problem where I couldn't build the project as those DLL stay running. I would have to stop the application pool to build/publish the project. I've also tried installing the Types from the SQL Server SP1 Feature Pack with no luck... There must be something I'm missing. The web server is 2012 if that has any bearing on it. – dontbesorry80 Aug 27 '14 at 10:22
0

msdn

When the compatibility level is 100 or below in SQL Server 2012 then the geography data type has the following restrictions:

•Each geography instance must fit inside a single hemisphere. No spatial objects larger than a hemisphere can be stored.

•Any geography instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) or Well-Known Binary (WKB) representation that produces an object larger than a hemisphere throws an ArgumentException.

•The geography data type methods that require the input of two geography instances, such as STIntersection(), STUnion(), STDifference(), and STSymDifference(), will return null if the results from the methods do not fit inside a single hemisphere. STBuffer() will also return null if the output exceeds a single hemisphere.

Martin Smellworse
  • 1,702
  • 3
  • 28
  • 46
  • I don't actually have any data in the column yet so the value is null anyway. However it's returning an ASP.Net error as it can't seem to fill the DataSet. However I elevated the compatibility level of the database to 110 and still had the same error?? – dontbesorry80 Aug 26 '14 at 14:13
0

Spent heaps of time on this, adding references as well, but I found a workaround i want to share. Should work fine on asp.net as well.

I use the following query to parse me the string representation of the object:

DECLARE @result geography;
SELECT @result = area FROM news WHERE id LIKE @id ;
SELECT ...,  @result.STAsText() as area FROM news WHERE id LIKE @id;

And in the asmx (c#) webservice i retrieved my SQLGeography with a parser method:

row["area"].ToString()

After that you can use a parser method to retrieve the SQLGeography object.

/// <summary>
/// Converts a String into an sql geography object.</summary>
/// <param name="pText">The string representation of the sql geography object. </param>
public static SqlGeography GetGeographyFromText(String pText)
{
    SqlString ss = new SqlString(pText);
    SqlChars sc = new SqlChars(ss);
    try
    {
        if (pText.Contains("POINT"))
        {
            return SqlGeography.STPointFromText(sc, 4326);
        }
        return SqlGeography.STPolyFromText(sc, 4326);
    }
    catch (Exception ex)
    {
        return SqlGeography.STMPolyFromText(sc, 4326);
        throw ex;
    }
}

Hope it helps anyone!

Falco Winkler
  • 1,082
  • 1
  • 18
  • 24
-1

In the end I couldn't get it to work as it does on my development machine and I don't want to install Visual Studio or SQL Server Express on the web server so instead I just removed all the queries where I did "SELECT * FROM Table" and the error went away. It still let me do my calculations using the geography field, but doesn't like you trying to show it on the screen. Thanks for all your help!

dontbesorry80
  • 567
  • 1
  • 6
  • 12