ThinkGeo.com    |     Documentation    |     Premium Support

SqlServerFeatureLayer issues

#1

Hello,

I’ve been having issues with SqlServerFeatureLayer issues for a long time in my code, but recently things start to break down. I hope it’s my novice programming skills and that you are able to help me.

This is my peace of code which has been working for a long time, but now I updated to the latest version of Thinkgeo:
Thinkgeo.UI.WPF 14.2.1
Thinkgeo.Core 14.2.1
Thinkgeo.Dependency.SqlClient 12.4.0
Thinkgeo.SqlServer 12.2.22

	Dim MSSQLLayer As New SqlServerFeatureLayer(_SQLstringAsfalt, "vwHVHCalc_Asfalt_Nietgereed", "id", 31370)
	MSSQLLayer.FeatureSource.ProjectionConverter = New ProjectionConverter(proj28992, 3857)

	'Add an overlay to the map
	OVL = mapView2.AddContentOverlay(sOverlayname)

	Dim LayerFeatures As New Collection(Of Feature)
	If Not MSSQLLayer.IsOpen Then MSSQLLayer.Open()

	LayerFeatures = MSSQLLayer.FeatureSource.GetAllFeatures(ReturningColumnsType.AllColumns)
    Dim LayerColumns As Collection(Of FeatureSourceColumn) = MSSQLLayer.FeatureSource.GetColumns()

This now results in exception ‘Datareader.GetFieldType(2) has returned null-value’ when executing GetAllFeatures and GetColumns().

  1. When I change ReturningColumnsType to .NoColumns I get all the features but without other data off course.
  2. I checked the query… there are NO null values in the result and even if there were that should not crash the code.

Any idea what might be causing this? I’m a novice programmer and have no clues (after days of searching).

Other problem is the SqlServer version. I still use 12.2.22 because that is for me the latest working version. I’ve skipped updating this, because this versions kept working, but I don’t see what’s the problem.

After updating to the newest version I get this error when the MSSQLLayer.Open() function is executing:
‘The command already has an open DataReader associated with it that must be closed first.’ (translated from Dutch).

When the code continues up-to GetAllFeatures this is the message I get:
‘The FeatureSource is not open. Please call the Open method before calling this method.’

When I do that by adding mssqllayer.featuresource.open before the getallfeatures I get the same message about Datareader as earlier.

Can you help me out?

Kind regards,
Guido van den Boom

#2

Hi Guido,

Just want to make sure:

  1. The same code works with v12.2.22, but it doesn’t work with v14.2.1.
  2. After updating to v14.2.1, the following exception is thrown in MSSQLLayer.Open()
    ‘The command already has an open DataReader associated with it that must be closed first.’
  3. “When the code continues up-to GetAllFeature”, I’m wondering how you continue running the code if it throws an exception in MSSQLLayer.Open()?

Thanks,
Ben

#3

Hi Ben,

I solved the first problem… in my effort to update many nuget packages I also updated Microsoft Sqlservetypes to 160. That was causing the Datareadery.GetFieldType error messages.
Changing this back to version 14 did the trick for that issue.

To answer your questions:

  1. Correct.
    I use the following versions and this works:
    Thinkgeo.UI.WPF 14.2.1
    Thinkgeo.Core 14.2.1
    Thinkgeo.Dependency.SqlClient 12.4.0
    Thinkgeo.SqlServer 12.2.22

  2. Correct
    Changing Thinkgeo.SqlServer to 14.2.1 as well results in the errors.

  3. In the original code I Try/catch the exceptions. I deleted that part of the code in this post.

Kind regards,
Guido van den Boom

#4

Hi Guido,

Thanks for the explanation. We found an issue in the latest version and just fixed it in the latest beta for you, can you have a try? NuGet Gallery | ThinkGeo.SqlServer 14.3.0-beta002

And you mentioned updating Microsoft.SqlServer.Types from v14 to v16 caused “Datareader.GetFieldType(2) has returned null-value” error. So just want to make sure you are using Microsoft.SqlServer.Types outside ThinkGeo component, right? as I don’t think this package is referenced by ThinkGeo v14.

Thanks,
Ben

#5

Hi Ben,

This update solved my first problem with opening layers. I can upgrade to the latest version.
It has been arround since version 12.3.0, that’s why I have been using 12.2.22 up until now.

I’m indeed using microsoft.sqlserver.types outside thinkgeo component to directly work in the database with geometries.
But when I update this to version 160 I get the error “Datareader.GetFieldType(2) has returned null-value”.

Although you don’t reference this package it still infects Thinkgeo because the error occures when excecuting this line of code:
LayerFeatures = MSSQLLayer.FeatureSource.GetAllFeatures(ReturningColumnsType.AllColumns)

*I tried to use v160 again after update to ThinkGeo.SqlServer 14.3.0-beta002, but still the same issue.

Kind regards,
Guido van den Boom

#6

Hi Guido,

Just want to make sure. So

  1. With the latest version, you don’t see any exceptions when doing MSSQLLayer.Open().
  2. With Microsoft SqlServer.Types v160, you still see the exception:

Datareader.GetFieldType(2) has returned null-value

when running the following code:
LayerFeatures = MSSQLLayer.FeatureSource.GetAllFeatures(ReturningColumnsType.AllColumns)
3. You don’t see the exception in #2 if downgrade Microsoft SqlServer.Types to v140

If above is true, can you let me know more about how you are using Microsoft SqlServer.Types in your code?

Thanks,
Ben

#7

Hi Ben,

That’s correct for all 3 points.

I have my own SQLcontrol for performing all sorts of actions on the database.
Although I’m transferring as much stuff to an API as possible.

For instance I let a user draw geometry (point, linestring, polygon) and with my query i store this
information in a datatable with a geometry column.
Or I get a subset of a table with limited columns and more filters to fill an InMemoryLayer.
Many times 1 filter is not enough for me.
Is this what you mean?

Kind regards,
Guido van den Boom

#8

Hi Guido,

With the help of ChatGPT, I found

  1. The breaking changes between SqlServer.Types v14 and v16 caused this issue.
  2. System.Data.SqlClient, the package we were using, has been largely superseded by Microsoft.Data.SqlClient, which is newer and should be consistent with the newer version of SqlServer.Types.

Solutions:

  1. The ultimate solution is we upgrade ThinkGeo, use Microsoft.Data.SqlClient instead of System.Data.SqlClient.
  2. The workaround is to use SqlServer.Types v14 for now until the upgrade is finished.

I’ve added it to our list, do you think that works for you?

Thanks,
Ben

#9

Hi Ben,

For me no problem at all.
I’ll just keep using v14 untill you upgrade thinkgeo.

For now, thanks for the bugfix!

Best regards,
Guido van den Boom

#10

No problem! Guido, Thanks for reporting this issue!