ThinkGeo.com    |     Documentation    |     Premium Support

Rendering with join query

#1

Hi

       I could not find a way apply a query to a class breaker using MsSql2008FeatureLayer.  So I have Layer1 and I try to join tableB and apply class breaker to tableB.field1 then generate tiles by adding the layer to an Overlay.  Can you suggest a way of doing that?  FeatureSource.ExecuteQuery is no different to the function in ADO.Net, the result does not apply to the layer, it also requires the layer to be open.  Should I write a class of overwrite FeatureSource to do that?  What methods should I overwrite?  I also found similar post in other forum.  Is using database view the only alternative?   



Thanks! 

#2

Hi Myron,



Welcome to map suite forums!

I think we can try the ExecutingSqlStatement event to change the request t-sql statement by pending a JOIN statement behind the original one, please try the below codes:




private void LoadMsSQLLayer()
        {
            string connectString = @"Data Source=localhost;Initial Catalog=shapefiles;Persist Security Info=True;User ID=sa;Password=*******";
            MsSql2008FeatureLayer sql2008Layer = new MsSql2008FeatureLayer(connectString, "Countries02", "ID");
            ((MsSql2008FeatureSource)sql2008Layer.FeatureSource).ExecutingSqlStatement += DisplayASimpleMap_ExecutingSqlStatement;
 
            ClassBreakStyle classBreakStyle = new ClassBreakStyle("POP_CNTRY");
            classBreakStyle.ClassBreaks.Add(new ClassBreak(0, AreaStyles.CreateSimpleAreaStyle(GeoColor.SimpleColors.LightBlue)));
           classBreakStyle.ClassBreaks.Add(new ClassBreak(50000000, AreaStyles.CreateSimpleAreaStyle(GeoColor.SimpleColors.Blue)));
            classBreakStyle.ClassBreaks.Add(new ClassBreak(100000000, AreaStyles.CreateSimpleAreaStyle(GeoColor.SimpleColors.LightRed)));
            classBreakStyle.ClassBreaks.Add(new ClassBreak(200000000, AreaStyles.CreateSimpleAreaStyle(GeoColor.SimpleColors.Red)));
 
            sql2008Layer.ZoomLevelSet.ZoomLevel01.CustomStyles.Add(classBreakStyle);
            sql2008Layer.ZoomLevelSet.ZoomLevel01.ApplyUntilZoomLevel = ApplyUntilZoomLevel.Level20;
            Map1.StaticOverlay.Layers.Add("Sql2008Layer", sql2008Layer);
        }
 
        private void DisplayASimpleMap_ExecutingSqlStatement(object sender, ExecutingSqlStatementMsSql2008FeatureSourceEventArgs e)
        {
            string sqlStatement = e.SqlStatement;
 
            if (e.ExecutingSqlStatementType == ExecutingSqlStatementType.GetFeaturesInsideBoundingBoxEx)
            {
                sqlStatement = sqlStatement.Insert(sqlStatement.ToLowerInvariant().IndexOf("from"), " ,[POP_CNTRY] ");
                if (sqlStatement.ToLowerInvariant().Contains("[id]"))
                {
                    sqlStatement = sqlStatement.Replace("[ID]", "Countries02.ID");
                }
 
                if (sqlStatement.ToLowerInvariant().Contains("where"))
                {
                    string joinString = " join linkedtable on Countries02.RECID=linkedtable.RECID ";
                    sqlStatement = sqlStatement.Insert(sqlStatement.ToLowerInvariant().IndexOf("where"), joinString);
                }
 
                e.SqlStatement = sqlStatement;
            }
        }

Of course, creating a database view is also another way.



Please feel free to let us know if any questions.

Thanks,

Troy

#3

Hi Troy, 
   It works. Thank you for your detail example and timely reply.

#4

Hi Myron, 
  
 I am glad to hear that works for you. 
  
 Any question please let us know. 
  
 Regards, 
  
 Don