public XAMLResponse GetSQLDataAreaXAML(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;
StringBuilder query = new StringBuilder();
try
{
switch (Parameters.querytype.ToLower())
{
case "Buffer":
{
query.Append("SELECT TOP 100 *," + geomField + ".Reduce(@reduce) as " + geomField + "XAML FROM [dbo].[" + Parameters.table + "] where country = 'PRT'");
break;
}
default:
break;
}
queryStart = DateTime.Now;
SqlCommand cmd = new SqlCommand(query.ToString(), conn);
cmd.Parameters.Add(new SqlParameter("reduce", Parameters.reduce));
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Parameters = GenerateXaml(Parameters, _XAMLResponse, xaml, rdr);
}
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;
_XAMLResponse.QueryTime = (queryStop - queryStart).TotalMilliseconds;
_XAMLResponse.totalPoints = totalPoints;
return _XAMLResponse;
}
/// <summary>
/// ServiceException
/// sets response for exception
/// </summary>
/// <param name="response">XAMLResponse</param>
/// <param name="message">string exception messsage</param>
public void ServiceException(XAMLResponse response, string message)
{
response.ErrorCode = 1;
response.OutputMessage = message;
response.OutputFields.Clear();
response.XAML = "";
}
private XAMLParameters GenerateXaml(XAMLParameters Parameters, XAMLResponse _XAMLResponse, StringBuilder xaml, SqlDataReader rdr)
{
XAMLFields shp = new XAMLFields();
shp.Fields = new Dictionary<string, string>();
for (int i = 0; i < rdr.FieldCount; i++)
{
if (rdr[i].GetType().Equals(typeof(SqlGeometry)) ||
rdr.GetName(i).Equals(geomField))
{ //skip
}
else
{
string test2 = rdr.GetName(i) + " " + geomField + "XAML";
if (rdr.GetName(i).Equals("ID"))
{
shp.ID = Parameters.table + "_" + rdr[i].ToString();
}
else if (rdr.GetName(i).Equals(geomField + "XAML"))
{
SqlGeography geo = (SqlGeography)rdr[geomField + "XAML"];
StringBuilder geoxaml = new StringBuilder();
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().UF8toNormalString());
}
}
}
_XAMLResponse.OutputFields.Add(shp);
return Parameters;
}
/// <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["ConnectionString"].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 TOP 100 *," + geomField + ".Reduce(@reduce) as " + geomField + "XAML FROM [dbo].[" + Parameters.table + "] WITH(INDEX(the_geo_sidx)) WHERE marine = 0 and ");
if (Parameters.points.Split(',').Length > 1)
{ //Polyline Buffer
query.Append(geomField + ".STIntersects(geography::STGeomFromText('LINESTRING('+@points+')', @srid).STBuffer(@radius))=1 ORDER BY rep_area desc");
}
else
{ //Point Buffer
query.Append(geomField + ".STIntersects(geography::STGeomFromText('POINT('+@points+')', @srid).STBuffer(@radius))=1 ORDER BY rep_area desc");
}
}
else
{
if (Parameters.querytype.ToLower().Equals("name"))
{
query.Append("SELECT TOP 50 *," + geomField + ".Reduce(@reduce) as " + geomField + "XAML FROM [dbo].[" + Parameters.table + "] WHERE marine = 0 and ");
query.Append("country = @points ORDER BY rep_area desc");
}
else if (Parameters.querytype.ToLower().Equals("id"))
{
query.Append("SELECT *," + geomField + ".Reduce(@reduce) as " + geomField + "XAML FROM [dbo].[" + Parameters.table + "] WHERE marine = 0 and ");
query.Append("ID = @points");
}
}
}
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())
{
Parameters = GenerateXaml(Parameters, _XAMLResponse, xaml, rdr);
}
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;
_XAMLResponse.QueryTime = (queryStop - queryStart).TotalMilliseconds;
_XAMLResponse.totalPoints = totalPoints;
return _XAMLResponse;
}