public Suburb getSuburb(string suburbName)
{
using (DBConnect db = new DBConnect())
{
suburb = db.getSuburb(suburbName); // get the suburb details out of the database packed into an instance of Suburb for convenience/my sanity
if (!suburb.Name.Equals("empty")) // This is an extremely shameful hack I will fix it after I sleep
{
return(suburb);
}
else
{
throw new Exception("Didn't get a suburb from the database"); // TODO: Handle this exception
}
}
}
public Suburb getSuburb(string suburbName)
{
if (connection.State == ConnectionState.Closed)
{
OpenConnection();
}
// Using a prepared statement now to mitigate the gaping security hole that is sending
// user input to the database
SqlCommand cmd = new SqlCommand("SELECT s.suburb, s.postcode, s.latitude, s.longitude, s.wikiURL, s.picURL FROM where2next.suburb_gnaf s WHERE s.suburb = @suburbname", connection); // much better to do this in a single line
SqlParameter snameParam = new SqlParameter("@suburbname", SqlDbType.VarChar, 40); // Limit to 40 chars because we don't need more, excessive capabilities tend to present risks
snameParam.IsNullable = false;
snameParam.Value = suburbName;
cmd.Parameters.Add(snameParam);
cmd.Prepare();
using (var dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) // This should close the connection for us when the reader is closed
{
if (dataReader.HasRows)
{
dataReader.Read();
var name = dataReader.GetString(0);
var postcode = dataReader.GetInt32(1).ToString();
var latitude = dataReader.GetDouble(2).ToString();
var longitude = dataReader.GetDouble(3).ToString();
var wikiUrl = dataReader.GetString(4);
var picUrl = dataReader.GetString(5);
Suburb suburb = new Suburb(name, postcode, latitude, longitude, wikiUrl, picUrl);
return(suburb);
}
else
{
return(new Suburb());
}
}
}
// Method invoked by the search button, or automatically if we reach this site from a quiz
// result. Handles all the side-effect-y UI garbage. Returns false if the input is invalid or
// the suburb is not found, so a "not found" page can be displayed. This could probably be
// broken up into smaller methods
public bool Search(string suburbName)
{
if (!suburbName.All(x => Char.IsLetter(x) || Char.IsWhiteSpace(x) || x.Equals('-')))
{
return(false); // keeps any garbage away from the rest of the method. Suburb names don't have numbers or symbols
}
Page.Title = string.Format("Where2Next Profile: {0}", lti.ToTitleCase(suburbName.ToLower()));
try
{
suburb = getSuburb(suburbName);
profileCard.Controls.Clear();
// Create title of card
profileCard.Controls.Add(new LiteralControl(string.Format("<h1>{0}</h1><h3>{1}</h3><hr/>", lti.ToTitleCase(suburb.Name.ToLower()), suburb.Postcode)));
// Try adding a picture
if (suburb.PicUrl.Length > 0)
{
Image suburbPic = new Image();
suburbPic.AlternateText = suburb.Name;
suburbPic.ImageUrl = suburb.PicUrl;
suburbPic.CssClass = "suburbPic";
suburbPic.ImageAlign = ImageAlign.AbsMiddle;
profileCard.Controls.Add(suburbPic);
}
// Get list of services
var services = getServices().OrderBy(x => x.Item1);
var sb = new StringBuilder();
sb.Append("<table class=\"serviceTable\"><thead><tr><th><h3>Services</h3></th></tr></thead><tbody>");
foreach (var subList in services)
{
if (subList.Item2.Count >= 1)
{
sb.AppendFormat("<tr><th>{0}</th></tr>", subList.Item1.Item2);
foreach (var service in subList.Item2.OrderBy(x => x.serviceName))
{
sb.AppendFormat("<tr><td>{0}</td><td>{1}</td></tr>", service.serviceName, service.address);
}
}
}
sb.Append("</tbody></table>");
profileCard.Controls.Add(new LiteralControl(sb.ToString()));
// Add info about distance from CBD
var distanceFromCityInfo = string.Format("<div class=\"distanceCard\"> <p>{0} is {1:0.0}km from the Melbourne CBD.</p> </div>", lti.ToTitleCase(suburb.Name.ToLower()), suburb.DistanceFromCity);
profileCard.Controls.Add(new LiteralControl(distanceFromCityInfo));
// Get house price data if we can
var priceData = getSuburbPriceData();
var priceDataBuilder = new StringBuilder();
priceDataBuilder.Append("<div class=\"priceCard\">");
foreach (var data in priceData)
{
if (data.exists)
{
string dwellingType = "";
switch (priceData.IndexOf(data))
{
case 0:
dwellingType = "house";
break;
case 1:
dwellingType = "unit";
break;
}
priceDataBuilder.AppendFormat("<p>The median {3} price in {0} is <strong>{1:C0}</strong><br> <em>#{2} in Victoria</em> </p> <br>", lti.ToTitleCase(data.suburbName.ToLower()), data.price5, data.ranking, dwellingType); // This would be a LOT better if I wrote something to handle ordinals (e.g. 1st, 2nd, 3rd). Maybe later.
}
}
priceDataBuilder.Append("</div>");
profileCard.Controls.Add(new LiteralControl(priceDataBuilder.ToString()));
return(true);
}
catch (Exception)
{
SuburbNotFound();
return(false); // This is side-effecty and not really consistent with other code I've written in a more functional style. Oh well. This is why I hate UI.
}
}