/// <summary>
/// GetSQLDataXAML
/// returns XAML MapLayer with results of SQL Server query
/// </summary>
/// <param name="Parameters">XAMLParameters required for query</param>
public XAMLResponse GetSQLDataXAML(XAMLParameters Parameters)
{
DateTime queryStart = DateTime.Now;
DateTime queryStop;
XAMLResponse _XAMLResponse = new XAMLResponse();
_XAMLResponse.ErrorCode = 0;
_XAMLResponse.OutputMessage = "Success";
_XAMLResponse.OutputFields = new List<XAMLFields>();
StringBuilder xaml = new StringBuilder();
string layerstr = "layer"+CultureInfo.CurrentCulture.TextInfo.ToTitleCase(Parameters.table);
//XML MapLayer header string for return XAML
xaml.Append("<m:MapLayer x:Name=\""+layerstr+"\"");
xaml.Append(" xmlns:m=\"clr-namespace:Microsoft.Maps.MapControl;assembly=Microsoft.Maps.MapControl\"");
xaml.Append(" xmlns=\"http://schemas.microsoft.com/winfx/2006/xaml/presentation\"");
xaml.Append(" xmlns:x=\"http://schemas.microsoft.com/winfx/2006/xaml\"");
xaml.Append(">");
string connStr = ConfigurationManager.ConnectionStrings["DataConnectionString"].ConnectionString;
SqlConnection conn = new SqlConnection(connStr);
SqlDataReader rdr = null;
try
{
if (Parameters.querytype == null || Parameters.querytype.Length == 0) throw new ArgumentException("Invalid Parameters: querytype=\"" + Parameters.querytype + "\"");
if (Parameters.querytype.ToLower().Equals("buffer") && Parameters.radius <= 0) throw new ArgumentException("Invalid Parameters: querytype buffer requires radius > 0 - \"" + Parameters.radius + "\"");
int test = Parameters.points.Split(',').Length;
if (Parameters.points.Length == 0 && Parameters.querytype.ToLower().Equals("buffer")) throw new ArgumentException("Invalid Parameters: points must contain at least one point for querytype buffer");
if (Parameters.points.Split(',').Length != 5 && Parameters.querytype.ToLower().Equals("bbox")) throw new ArgumentException("Invalid Parameters: points must contain 5 points for a closed querytype bbox");
StringBuilder query = new StringBuilder();
if (Parameters.querytype.ToLower().Equals("bbox"))
{ //BBox
query.Append("SELECT *," + geomField + ".Reduce(@reduce) as "+geomField+"XAML FROM [dbo].[" + Parameters.table + "] WITH(INDEX(the_geo_sidx)) WHERE ");
query.Append(geomField + ".STIntersects(geography::STGeomFromText('POLYGON(('+@points+'))', @srid))=1");
}
else if (Parameters.querytype.ToLower().Equals("buffer"))
{
query.Append("SELECT *," + geomField + ".Reduce(@reduce) as " + geomField + "XAML FROM [dbo].[" + Parameters.table + "] WITH(INDEX(the_geo_sidx)) WHERE ");
if (Parameters.points.Split(',').Length > 1)
{ //Polyline Buffer
query.Append(geomField + ".STIntersects(geography::STGeomFromText('LINESTRING('+@points+')', @srid).STBuffer(@radius))=1");
}
else
{ //Point Buffer
query.Append(geomField + ".STIntersects(geography::STGeomFromText('POINT('+@points+')', @srid).STBuffer(@radius))=1");
}
}
log.Info(query);
queryStart = DateTime.Now;
SqlCommand cmd = new SqlCommand(query.ToString(), conn);
cmd.Parameters.Add(new SqlParameter("reduce", Parameters.reduce));
cmd.Parameters.Add(new SqlParameter("radius", Parameters.radius));
cmd.Parameters.Add(new SqlParameter("srid", srid));
cmd.Parameters.Add(new SqlParameter("points", Parameters.points));
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
XAMLFields shp = new XAMLFields();
shp.Fields = new Dictionary<string, string>();
for (int i = 0; i < rdr.FieldCount; i++)
{
log.Debug(rdr[i].GetType() + " " + rdr.GetName(i));
if (rdr[i].GetType().Equals(typeof(SqlGeometry))||
rdr.GetName(i).Equals(geomField))
{ //skip
log.Debug("skip " + rdr[i].GetType());
}
else
{
string test2 = rdr.GetName(i) + " " + geomField + "XAML";
if (rdr.GetName(i).Equals("ID"))
{
shp.ID = Parameters.table+"_"+rdr[i].ToString();
log.Debug(rdr[i].GetType() + " " + rdr.GetName(i) + " " + rdr[i].ToString());
}
else if (rdr.GetName(i).Equals(geomField+"XAML"))
{
log.Debug(rdr[i].GetType() + " " + rdr.GetName(i) + " " + rdr[i].ToString());
SqlGeography geo = (SqlGeography)rdr[geomField + "XAML"];
StringBuilder geoxaml = new StringBuilder();
log.Debug(geo.STGeometryType().ToString().ToUpper());
switch (geo.STGeometryType().ToString().ToUpper())
{
case "POINT":
{
geoxaml.Append(XamlPoint(geo, Parameters.table,shp.ID));
break;
}
case "LINESTRING":
{
geoxaml.Append(XamlLinestring(geo, Parameters.table, shp.ID));
break;
}
case "POLYGON":
{
geoxaml.Append(XamlPolygon(geo, Parameters.table, shp.ID));
break;
}
case "MULTILINESTRING":
{
geoxaml.Append(XamlMultiLinestring(geo, Parameters.table, shp.ID));
break;
}
case "MULTIPOLYGON":
{
geoxaml.Append(XamlMultiPolygon(geo, Parameters.table, shp.ID));
break;
}
case "GEOMETRYCOLLECTION":
{
geoxaml.Append(XamlGeometryCollection(geo, Parameters.table, shp.ID));
break;
}
}
xaml.Append(geoxaml.ToString());
}
else
{
shp.Fields.Add(rdr.GetName(i), rdr[i].ToString());
log.Debug(rdr.GetName(i) + " : " + rdr[i].ToString());
}
}
}
log.Debug(shp.ID + " " + _XAMLResponse.XAML);
_XAMLResponse.OutputFields.Add(shp);
}
xaml.AppendLine("</m:MapLayer>");
_XAMLResponse.XAML = xaml.ToString();
}
catch (ArithmeticException e)
{
ServiceException(_XAMLResponse, e.Message);
}
catch (ArgumentException e)
{
ServiceException(_XAMLResponse, e.Message);
}
catch (Exception e)
{
ServiceException(_XAMLResponse, e.Message);
}
finally
{
if (rdr != null) rdr.Close();
if (conn != null) conn.Close();
}
queryStop = DateTime.Now;
log.Debug(String.Format("Query Time: {0,0:0}ms", (queryStop - queryStart).TotalMilliseconds));
_XAMLResponse.QueryTime = (queryStop - queryStart).TotalMilliseconds;
_XAMLResponse.totalPoints = totalPoints;
return _XAMLResponse;
}
/// <summary>
/// GetSQLDataXAML
/// returns XAML MapLayer with results of SQL Server query
/// </summary>
/// <param name="Parameters">XAMLParameters required for query</param>
public XAMLResponse GetSQLDataXAML(XAMLParameters Parameters)
{
DateTime queryStart = DateTime.Now;
DateTime queryStop;
XAMLResponse _XAMLResponse = new XAMLResponse();
_XAMLResponse.ErrorCode = 0;
_XAMLResponse.OutputMessage = "Success";
_XAMLResponse.OutputFields = new List <XAMLFields>();
StringBuilder xaml = new StringBuilder();
string layerstr = "layer" + CultureInfo.CurrentCulture.TextInfo.ToTitleCase(Parameters.table);
//XML MapLayer header string for return XAML
xaml.Append("<m:MapLayer x:Name=\"" + layerstr + "\"");
xaml.Append(" xmlns:m=\"clr-namespace:Microsoft.Maps.MapControl;assembly=Microsoft.Maps.MapControl\"");
xaml.Append(" xmlns=\"http://schemas.microsoft.com/winfx/2006/xaml/presentation\"");
xaml.Append(" xmlns:x=\"http://schemas.microsoft.com/winfx/2006/xaml\"");
xaml.Append(">");
string connStr = ConfigurationManager.ConnectionStrings["DataConnectionString"].ConnectionString;
SqlConnection conn = new SqlConnection(connStr);
SqlDataReader rdr = null;
try
{
if (Parameters.querytype == null || Parameters.querytype.Length == 0)
{
throw new ArgumentException("Invalid Parameters: querytype=\"" + Parameters.querytype + "\"");
}
if (Parameters.querytype.ToLower().Equals("buffer") && Parameters.radius <= 0)
{
throw new ArgumentException("Invalid Parameters: querytype buffer requires radius > 0 - \"" + Parameters.radius + "\"");
}
int test = Parameters.points.Split(',').Length;
if (Parameters.points.Length == 0 && Parameters.querytype.ToLower().Equals("buffer"))
{
throw new ArgumentException("Invalid Parameters: points must contain at least one point for querytype buffer");
}
if (Parameters.points.Split(',').Length != 5 && Parameters.querytype.ToLower().Equals("bbox"))
{
throw new ArgumentException("Invalid Parameters: points must contain 5 points for a closed querytype bbox");
}
StringBuilder query = new StringBuilder();
if (Parameters.querytype.ToLower().Equals("bbox"))
{ //BBox
query.Append("SELECT *," + geomField + ".Reduce(@reduce) as " + geomField + "XAML FROM [dbo].[" + Parameters.table + "] WITH(INDEX(the_geo_sidx)) WHERE ");
query.Append(geomField + ".STIntersects(geography::STGeomFromText('POLYGON(('+@points+'))', @srid))=1");
}
else if (Parameters.querytype.ToLower().Equals("buffer"))
{
query.Append("SELECT *," + geomField + ".Reduce(@reduce) as " + geomField + "XAML FROM [dbo].[" + Parameters.table + "] WITH(INDEX(the_geo_sidx)) WHERE ");
if (Parameters.points.Split(',').Length > 1)
{ //Polyline Buffer
query.Append(geomField + ".STIntersects(geography::STGeomFromText('LINESTRING('+@points+')', @srid).STBuffer(@radius))=1");
}
else
{ //Point Buffer
query.Append(geomField + ".STIntersects(geography::STGeomFromText('POINT('+@points+')', @srid).STBuffer(@radius))=1");
}
}
log.Info(query);
queryStart = DateTime.Now;
SqlCommand cmd = new SqlCommand(query.ToString(), conn);
cmd.Parameters.Add(new SqlParameter("reduce", Parameters.reduce));
cmd.Parameters.Add(new SqlParameter("radius", Parameters.radius));
cmd.Parameters.Add(new SqlParameter("srid", srid));
cmd.Parameters.Add(new SqlParameter("points", Parameters.points));
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
XAMLFields shp = new XAMLFields();
shp.Fields = new Dictionary <string, string>();
for (int i = 0; i < rdr.FieldCount; i++)
{
log.Debug(rdr[i].GetType() + " " + rdr.GetName(i));
if (rdr[i].GetType().Equals(typeof(SqlGeometry)) ||
rdr.GetName(i).Equals(geomField))
{ //skip
log.Debug("skip " + rdr[i].GetType());
}
else
{
string test2 = rdr.GetName(i) + " " + geomField + "XAML";
if (rdr.GetName(i).Equals("ID"))
{
shp.ID = Parameters.table + "_" + rdr[i].ToString();
log.Debug(rdr[i].GetType() + " " + rdr.GetName(i) + " " + rdr[i].ToString());
}
else if (rdr.GetName(i).Equals(geomField + "XAML"))
{
log.Debug(rdr[i].GetType() + " " + rdr.GetName(i) + " " + rdr[i].ToString());
SqlGeography geo = (SqlGeography)rdr[geomField + "XAML"];
StringBuilder geoxaml = new StringBuilder();
log.Debug(geo.STGeometryType().ToString().ToUpper());
switch (geo.STGeometryType().ToString().ToUpper())
{
case "POINT":
{
geoxaml.Append(XamlPoint(geo, Parameters.table, shp.ID));
break;
}
case "LINESTRING":
{
geoxaml.Append(XamlLinestring(geo, Parameters.table, shp.ID));
break;
}
case "POLYGON":
{
geoxaml.Append(XamlPolygon(geo, Parameters.table, shp.ID));
break;
}
case "MULTILINESTRING":
{
geoxaml.Append(XamlMultiLinestring(geo, Parameters.table, shp.ID));
break;
}
case "MULTIPOLYGON":
{
geoxaml.Append(XamlMultiPolygon(geo, Parameters.table, shp.ID));
break;
}
case "GEOMETRYCOLLECTION":
{
geoxaml.Append(XamlGeometryCollection(geo, Parameters.table, shp.ID));
break;
}
}
xaml.Append(geoxaml.ToString());
}
else
{
shp.Fields.Add(rdr.GetName(i), rdr[i].ToString());
log.Debug(rdr.GetName(i) + " : " + rdr[i].ToString());
}
}
}
log.Debug(shp.ID + " " + _XAMLResponse.XAML);
_XAMLResponse.OutputFields.Add(shp);
}
xaml.AppendLine("</m:MapLayer>");
_XAMLResponse.XAML = xaml.ToString();
}
catch (ArithmeticException e)
{
ServiceException(_XAMLResponse, e.Message);
}
catch (ArgumentException e)
{
ServiceException(_XAMLResponse, e.Message);
}
catch (Exception e)
{
ServiceException(_XAMLResponse, e.Message);
}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
queryStop = DateTime.Now;
log.Debug(String.Format("Query Time: {0,0:0}ms", (queryStop - queryStart).TotalMilliseconds));
_XAMLResponse.QueryTime = (queryStop - queryStart).TotalMilliseconds;
_XAMLResponse.totalPoints = totalPoints;
return(_XAMLResponse);
}