I understand there were some changes to the MsSqlFeatureLayer class in the latest release. I am hoping to open a map based on an ad-hoc query rather than a Table or View saved to the database. Can you provide any information of this feature?
MsSqlFeatureLayer
Charles,
I notice you have posted a similar question in previous post
gis.thinkgeo.com/Support/Dis...fault.aspx
string connectString = "Data Source=192.168.0.191;Initial Catalog=InternalDB;Persist Security Info=True;User ID=**;Password=*****";
MsSql2008FeatureLayer sql2008Layer = new MsSql2008FeatureLayer(connectString, "cntry02", "ID");
sql2008Layer.Srid = 4326;
sql2008Layer.Open();
sql2008Layer.ExecuteNonQuery("create view cntry02view as select id, SIZE, geom from cntry02 where POP_CNTRY > '10000000' ;");
sql2008Layer.Close();
sql2008Layer = new MsSql2008FeatureLayer(connectString, "cntry02view", "ID");
sql2008Layer.Srid = 4326;
sql2008Layer.Open();
sql2008Layer.ZoomLevelSet.ZoomLevel01.DefaultAreaStyle = AreaStyles.Country1;
sql2008Layer.ZoomLevelSet.ZoomLevel01.ApplyUntilZoomLevel = ApplyUntilZoomLevel.Level20;
winformsMap1.StaticOverlay.Layers.Add("Sql2008Layer", sql2008Layer);
winformsMap1.Refresh();
Let me know if you have any more questions.
Thanks.
Yale
Yale,
Yes I posted a similar question earlier. It was for a previous version of MapSuite available in April. I was told by sales and development that the latest release included the ability to include an ad-hoc query (i.e. SELECT * FROM RoadsGeometry) without creating a view or table. My project requires that I use custom queries all of the time, and creating and destroying hundreds of views daily is not an option.
Charles
This feature is in the last release. I am on my phone browser so can’t say much now. It is added as an event. The event passes you the SQL it will execute and a enum to tell you the kind of query. You can in the event modify or completly replace the SQL. This gives you the maximum flexability. You can modify all the SQL statements from get record count to get columns etc.
David
The way it works in more detail is that there is a new event that looks something like'ExecutingSqlStatement' and it gets raised before we execute any type of SQL statement in the feature source. On the event arguments it passes you an enumeration that tells you what kind of SQL statement it is and also the SQL statement itself that you can replace, edit or just take a look at. As you can see from the enum list below it will tell you what kind of SQL statement it is. The intended use is that if you want the feature source to reflect some special join or subset of data then you need to massage the SQL statements to reflect that. SQL is very flexible and this is why we thought it best just to expose the entire statement to you. We considered just some 'where' clause options but this doesn't work if you wanted to do joins etc. Once you get past anything very basic this is the only solution we could come up with that gives the developer maximum control. When using this you need to consider quite a bit becasue if you want a subset of a table you need to make sure you apply your flters everywhere like in getting the count etc. I suggest if this is something you are considering that you hook up the event and put a break point in to see what SQL statements are being called in your scenerio. The enumeration below are all the possible SQL statement but in reality for most users you will only need to modify a small subset.
The enumeration is as below.
public enum ExecutingSqlStatementType
{
Unknow = 0,
GetFeaturesByIds = 1,
CommitTransaction = 2,
GetFeaturesInsideBoundingBox = 2,
GetFeaturesOutsideBoundingBox = 3,
GetSpatialDataType = 4,
GetBouNdingBox = 5,
GetAllFeatures = 6,
GetCount = 7,
GetColumns = 8,
BuildIndex = 9,
Open = 10,
ExecuteScalar = 11,
ExecuteQuery = 12,
ExecuteNonQuery = 13,
GetFirstGeometryType = 14,
MakeAllGeometriesValid = 15,
Validate = 16
}
David
Here is an example of the new event:
MsSql2008FeatureLayer layer = new MsSql2008FeatureLayer();
((MsSql2008FeatureSource)layer.FeatureSource).ExecutingSqlStatement +=new EventHandler<ExecutingSqlStatementMsSql2008FeatureSourceEventArgs>(Form1_ExecutingSqlStatement);
private void Form1_ExecutingSqlStatement(object sender, ExecutingSqlStatementMsSql2008FeatureSourceEventArgs e)
{
if (e.ExecutingSqlStatementType == ExecutingSqlStatementType.GetFeaturesInsideBoundingBox)
{
string sqlStatement = e.SqlStatement;
e.SqlStatement = sqlStatement.TrimEnd(';') + " and Road_class='1';";
}
}
Ryan,
While this is a good way to show the basics of the event one should remember that you also need to apply this to other queries such as the count etc otherwise things are going to be off.
David