SQL Server & GetStyle

Topics: Data Access, SharpMap v0.9 / v1.x, Web Controls
Dec 28, 2011 at 11:05 PM
Edited Dec 28, 2011 at 11:50 PM

Hi,

I have a SQL 2008 DB with shapefile data in it. I want to fill each polygon with a color based on some conditions. Basically I want to use custom themes.

Problem: I am not able to access the dataset featurerow.
Error Msg: -  $exception {"Object reference not set to an instance of an object."} System.Exception {System.NullReferenceException}

Also since the conditions are dynamic - depends on the user who is currently logged in, I am writing into fdRow["Owner"]

Here's my code

string con = GetSQLConnectionString();
SharpMap.Data.Providers.SqlServer2008 sqlDS = new SharpMap.Data.Providers.SqlServer2008(con, tablename, geomColName, oidColName);

//Create a layer
VectorLayer layer = new VectorLayer(layername);
layer.DataSource = sqlDS;

//Setup a shapefile vectorlayer with some default styles
layer.Style.Line = new Pen(Color.White);
layer.Style.Fill = new SolidBrush(Color.LightCoral);
layer.Style.Outline = Pens.Black;
layer.Style.EnableOutline = true;
layer.SRID = 4326;

//************NEW CODE**********************
int iCount = sqlDS.GetFeatureCount(); //RETURNS 21
FeatureDataRow fdRow;

for (uint i = 0; i < iCount - 1; i++)
{
	fdRow = sqlDS.GetFeature(i); //RETRUNING NULL
	if (fdRow["ID1"] == _ID1.ToString())
		fdRow["Owner"] = 1;
	else if (fdRow["ID2"] == _ID2.ToString())
		fdRow["Owner"] = 2;
	else
		fdRow["Owner"] = 3;

	layer.Theme = new SharpMap.Rendering.Thematics.CustomTheme(GetStyleForShape);
}
//**************************************
//Add the current layer to the map
map.Layers.Add(layer);
}
private static VectorStyle GetStyleForShape(SharpMap.Data.FeatureDataRow fdRow)
{
	
	SharpMap.Styles.VectorStyle style = new SharpMap.Styles.VectorStyle();
	style.Outline = Pens.Black;
	style.EnableOutline = true;
	switch ((int)fdRow["Owner"])
	{
		case 1:
			style.Line = new Pen(Color.Green, 5);
			style.Fill = new SolidBrush(Color.LightCoral);
			return style;
	         case 2:
        		style.Line = new Pen(Color.Blue, 10);
 			style.Fill = new SolidBrush(Color.Beige);
           		return style;
		case 3:
		style.Line = new Pen(Color.Yellow, 10);
		style.Fill = new SolidBrush(Color.Beige);
		return style;

		default:
			style.Line= new Pen(Color.Black, 10);
			style.Fill = new SolidBrush(Color.LightSalmon);
          		return style;
	}

}
I think the problem is with the SQL statement in the GetFeature()

 string strSQL = "select g.* , g." + GeometryColumn + ".STAsBinary() As sharpmap_tempgeometry from " + Table + " g WHERE " + ObjectIdColumn + "=" + rowId + "";

"select g.* , g.Geom.STAsBinary() As sharpmap_tempgeometry from t_table g WHERE MyID=0" 
In my case, MyID happens to be a 8 digit unique no. I think the function is looking for a no. from 0 to 20 (as I have 21 features)
Developer
Dec 29, 2011 at 9:15 AM

try with for (uint i = 1; i < iCount; i++)

Dec 29, 2011 at 8:18 PM
D_Guidi wrote:

try with for (uint i = 1; i < iCount; i++)


Won't work. SQL condition  WHERE ObjectIdColumn = rowId will never match. ObjectIdColumn can be any no. While rowId is a number 1 to Count after applying the WHERE condition.

Coordinator
Dec 29, 2011 at 9:19 PM

You need to change your for(uint i = ...) loop to a foreach construct, somehow like this:

var fds = new FeatureDataSet();
sqlDS.ExecuteIntersectionQuery(sqlDS.GetExtents(), fds)
foreach (FeatureDataRow fdr in fds.Tables[0].Rows)
{
...
}

The rest of your code can stay as it is, I think. NOTE: this code has not seen a compiler at all, there may be typos

Hth FObermaier

Jan 12, 2012 at 4:40 AM

Thanks FObermaier. I will use the work around.

D_Guidi , for the GetFeature() to work as intended, the SQL statement should be changed to:

string strSQL = "WITH Temp AS SELECT g.* , g." + GeometryColumn + ".STAsBinary() As sharpmap_tempgeometry, ROW_NUMBER() OVER (ORDER BY "
  + ObjectIdColumn + ") AS 'RowNumber' " + "FROM " + Table + " ) SELECT * FROM Temp WHERE RowNumber = " + rowId + "";