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);
}
