urfa

protected IEnumerable<Company> GetCompanies(County county, string city, Activity activity)
		{
            Response.Write(city);
            MySqlConnection conn = MainData.GetMySqlConn();
			MySqlCommand cmd = new MySqlCommand("", conn);

			cmd.CommandText = "SELECT cc.ccID, cc.cLocalName FROM companies_cities cc ";
			if (activity != null)
			{
				cmd.CommandText += "INNER JOIN companies_cities_activity ca ON cc.ccID = ca.ccID AND aID = ?aID ";
				cmd.Parameters.AddWithValue("?aID", activity.ID);
			}
			if (city != null)
			{
				cmd.CommandText += "LEFT JOIN city_near cn ON cc.city = cn.city_near WHERE (cc.city = ?city OR cn.city = ?city) ";
				cmd.Parameters.AddWithValue("?city", city);
			}
			if (county != null)
			{
				if (city != null) cmd.CommandText += "AND "; else cmd.CommandText += "WHERE ";
				cmd.CommandText += "cc.countyID = ?countyid ";
				cmd.Parameters.AddWithValue("?countyid", county.countyID);
			}

			cmd.CommandText += "GROUP BY cc.ccID ORDER BY cc.cLocalName";

			conn.Open();
			MySqlDataReader r = cmd.ExecuteReader();
			while (r.Read())
			{
				Company company = new Company();
				company.ID = r.GetInt32(0);
				company.Name = r.GetString(1);
				yield return company;
			}
            //yield return new Company(ID = r.GetInt32(0), Name = r.GetString(1));
            conn.Close();
		}
    }