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> &nbsp; <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.
            }
        }