private Park GetClosestPark(string lat, string lng)
{
string queryString =
String.Format(@"SELECT TOP(1) Location.ToString(), Id, Name, Location.Lat AS Lat, Location.Long as Lng FROM Parks
WHERE Location.STDistance('POINT(' + @lat + ' ' + @lng + ')') IS NOT NULL
ORDER BY Location.STDistance('POINT(' + @lat + ' ' + @lng + ')')");
var park = new Park();
using (SqlConnection connection =
new SqlConnection(_connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@lat", lat);
command.Parameters.AddWithValue("@lng", lng);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
//Console.WriteLine("\t{0}\t{1}\t{2}",
//reader[0], reader[1], reader[2]);
park = new Park
{
id = reader["Id"].ToString(),
name = reader["Name"].ToString(),
x = reader["Lat"].ToString(),
y = reader["Lng"].ToString()
};
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
return park;
}
//[OutputCache(Duration = 3600, VaryByParam = "none")]
public JsonpResult Parks(string features, string callback = "dummy")
{
var parks = new List<Park>();
string queryString;
if (String.IsNullOrEmpty(features))
{
queryString = @"SELECT DISTINCT Id, Name, Location.Lat AS Lat, Location.Long as Lng FROM Parks
JOIN ParkFeatures ON ParkId = Id";
}
else
{
queryString = @"SELECT DISTINCT Id, Name, Location.Lat AS Lat, Location.Long as Lng FROM Parks
JOIN ParkFeatures ON ParkId = Id
WHERE FeatureName In (@features)";
}
var park = new Park();
using (SqlConnection connection =
new SqlConnection(_connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
if (!String.IsNullOrEmpty(features))
{
command.Parameters.AddWithValue("@features", features.ToUpper());
}
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
//Console.WriteLine("\t{0}\t{1}\t{2}",
//reader[0], reader[1], reader[2]);
parks.Add( new Park
{
id = reader["Id"].ToString(),
name = reader["Name"].ToString(),
x = reader["Lat"].ToString(),
y = reader["Lng"].ToString()
});
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
return Jsonp(parks, callback, JsonRequestBehavior.AllowGet);
}