ThinkGeo.com    |     Documentation    |     Premium Support

Rendering with join query

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! 

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

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

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