static public object GetCountryAndItsCapitalCityName(string countryName) { SQLiteConnection connection = new SQLiteConnection($"Data Source = c:\\sagit\\31.3.19HW.db; Version=3;"); connection.Open(); using (SQLiteCommand cmd = new SQLiteCommand($"SELECT c.name , a.capiname from country c JOIN capitalCity a on c.id=a.country_id WHERE '{countryName}'=c.name ", connection)) { using (SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read() == true) { Country c = new Country { Name = (string)reader["name"], }; CapitalCity a = new CapitalCity { Name = (string)reader["capiname"], }; var result = new { a, c }; return result; } } } connection.Close(); return null; }
public static List<object> GetListOfCountriesAndItsCapitalCitys(string a) { List<object> result = new List<object>(); SQLiteConnection connection = new SQLiteConnection($"Data Source = c:\\sagit\\31.3.19HW.db; Version=3;"); connection.Open(); using (SQLiteCommand cmd = new SQLiteCommand($"SELECT c.name ,a.capid, a.capiname,a.numCitizens from country c JOIN capitalCity a on c.id=a.country_id WHERE '{a}'<c.name ", connection)) { using (SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read() == true) { Country c = new Country { Name = (string)reader["name"], }; CapitalCity b = new CapitalCity { Id = (Int64)reader["capid"], Name = (string)reader["capiname"], NumCitizens = (int)reader["numCitizens"] }; object r = new { b, c }; result = result.Add(r); return result; } } } }