I had just installed version 10 from the NuGet packages right before running into this problem, and none of them show as us having any updates available. Also, I’m not sure I get how my app isn’t sending the correct sql statement. I never had to specify a sql statement before when using the MsSql2008FeatureLayer, are you saying you have to in the new one?
I can’t really “build you a really simple application” because you wouldn’t have access to the database tables or any of that.
Here is how I am loading the layer:
// Define a SQL layer
// To speed up access times and to better utilize indexing, all MsSqlFeatureLayers are loaded via a view.
// These views use a table marked via a _Display keyword that contains the PKs for the spatial data we will be viewing.
var sqlLayer =
new MsSqlFeatureLayer(ConnectionHelper.GetClientMappingDBConnection(), "v_Mapping_Counties", "Artificial_Key", 4326)
{
WhereClause = $"WHERE Is_Adjacent = {Convert.ToByte(adjacent)}",
SpatialIndexName = "IX_dbo_Mapping_GIS_County_Info_Spatial"
};
// DEBUGGING
((MsSqlFeatureSource) sqlLayer.FeatureSource).ExecutingSqlStatement += (o, e) => { Console.WriteLine(e.SqlStatement); };
// Setup our projection
// All county entries are stored using a SRID of 4326, this needs to be re-projected to the UTM SRID the map is using
var projection = new Proj4Projection()
{
InternalProjectionParametersString = Proj4Projection.GetEpsgParametersString(4326),
ExternalProjectionParametersString = Proj4Projection.GetEpsgParametersString(Srid)
};
projection.Open();
// Set the Projection for the MsSqlFeatureLayer, this will automatically convert the features
// contained within this layer to the UTM SRID for us
sqlLayer.FeatureSource.Projection = projection;
sqlLayer.FeatureSource.Open();
// To further boost performance, we convert the MsSqlFeatureLayer to an InMemoryFeatureLayer
// This prevents database hits each time the extent or zoom of the map is changed
// To accomplish this, we first pull all the features from the MsSqlFeatureLayer, this does perform a database hit
var features = sqlLayer.FeatureSource.GetAllFeatures(ReturningColumnsType.AllColumns);
sqlLayer.FeatureSource.Close();
My view is just a joined from the Counties table to a Counties display table based on the PK to FK relationship, and includes all the columns from the Counties table and the Is_Adjacent column from the display table.
For you guys to reproduce, it should be pretty easy to set up.
Take your existing table, then create another table called Countries02_Display. Add a column which is a FK to the Countries02 table’s PK, then another column called Is_Adjacent as a BIT which defaults to 0. Insert records from your Countries02 table into the Countries02_Display with a few of them being flagged as Is_Adjacent = 1 whereas the others are 0.
Now, create a view for these two, we can call it vCounties02. All this view needs to do is select from Countires02 joined to Countires02_Display on the keys, return all the columns from Countires02 and then the Is_Adjacent column from Countires02_Display.
Now, you can use the same code I have above to load it:
// Define a SQL layer
// To speed up access times and to better utilize indexing, all MsSqlFeatureLayers are loaded via a view.
// These views use a table marked via a _Display keyword that contains the PKs for the spatial data we will be viewing.
var sqlLayer =
new MsSqlFeatureLayer(ConnectionHelper.GetClientMappingDBConnection(), "v_Counties02", "Artificial_Key", 4326)
{
WhereClause = $"WHERE Is_Adjacent = {Convert.ToByte(adjacent)}",
SpatialIndexName = "IX_dbo_Mapping_GIS_County_Info_Spatial"
};
I think it has to do with the View and not using straight up table. The select from the sys.columns appears to be totally different for what I am executing.
I can’t actually provide you with a working example b/c our database is in no state in which it can be exported or shared.
