public List <CustomerInfoByAirlineName> GetFlightsByOriginCountryName(string originCountryName) { long countryCode = 0; List <CustomerInfoByAirlineName> resultInfo = new List <CustomerInfoByAirlineName>(); using (SqlConnection conn = new SqlConnection(FlightCenterConfig.connectionString)) { SqlCommand cmd = new SqlCommand("GetCountryCodeByName", conn); cmd.Connection.Open(); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@countryName", originCountryName)); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default); while (reader.Read() == true) { countryCode = (long)reader["ID"]; } cmd.Connection.Close(); } using (SqlConnection conn = new SqlConnection(FlightCenterConfig.connectionString)) { SqlCommand cmd = new SqlCommand("GetFlightInfoByOriCountryCode", conn); cmd.Connection.Open(); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@countryCode", countryCode)); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default); while (reader.Read() == true) { CustomerInfoByAirlineName info = new CustomerInfoByAirlineName() { flightNum = (long)reader["ID"], destCountryCode = (long)reader["DESTINATION_COUNTRY_CODE"], departureTime = (DateTime)reader["DEPARTURE_TIME"], landingTime = (DateTime)reader["LANDING_TIME"], tickets = (int)reader["REMAINING_TICKETS"] }; resultInfo.Add(info); } cmd.Connection.Close(); } for (int i = 0; i < resultInfo.Count(); i++) { using (SqlConnection conn = new SqlConnection(FlightCenterConfig.connectionString)) { SqlCommand cmd = new SqlCommand("GetCountriesByDestCode", conn); cmd.Connection.Open(); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@destCountryCode", resultInfo[i].destCountryCode)); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default); while (reader.Read() == true) { resultInfo[i].destFlightLocation = (string)reader["COUNTRY_NAME"]; } cmd.Connection.Close(); } } foreach (CustomerInfoByAirlineName info in resultInfo) { info.originFlightLocation = originCountryName; } return(resultInfo); }
public List <CustomerInfoByAirlineName> GetFlightsByFlightNum(long flightNum) { List <CustomerInfoByAirlineName> returnList = new List <CustomerInfoByAirlineName>(); using (SqlConnection conn = new SqlConnection(FlightCenterConfig.connectionString)) { SqlCommand cmd = new SqlCommand("GetFlightsByFlightNumFromDb", conn); cmd.Connection.Open(); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@flightNum", flightNum)); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default); //ill show u, after this u can see the query inside the ssms //this is the reader im talking about.. while (reader.Read() == true) { CustomerInfoByAirlineName flightInfo = new CustomerInfoByAirlineName() { flightNum = flightNum, originalCountryCode = (long)reader["ORIGIN_COUNTRY_CODE"], destCountryCode = (long)reader["DESTINATION_COUNTRY_CODE"], departureTime = (DateTime)reader["DEPARTURE_TIME"], landingTime = (DateTime)reader["LANDING_TIME"], tickets = (int)reader["REMAINING_TICKETS"] }; returnList.Add(flightInfo); } cmd.Connection.Close(); } for (int i = 0; i < returnList.Count(); i++) { using (SqlConnection conn = new SqlConnection(FlightCenterConfig.connectionString)) { SqlCommand cmd = new SqlCommand("GetCountriesByOriCode", conn); cmd.Connection.Open(); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@originCountryCode", returnList[i].originalCountryCode)); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default); while (reader.Read() == true) { returnList[i].originFlightLocation = (string)reader["COUNTRY_NAME"]; } cmd.Connection.Close(); } } for (int i = 0; i < returnList.Count(); i++) { using (SqlConnection conn = new SqlConnection(FlightCenterConfig.connectionString)) { SqlCommand cmd = new SqlCommand("GetCountriesByDestCode", conn); cmd.Connection.Open(); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@destCountryCode", returnList[i].destCountryCode)); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default); while (reader.Read() == true) { returnList[i].destFlightLocation = (string)reader["COUNTRY_NAME"]; } cmd.Connection.Close(); } } return(returnList); }