/// <summary>
/// Insert roundtrip to database in table roundtrip
/// </summary>
/// <param name="roundtrip">roundtrips</param>
/// <returns>true - if operation done successfully,
/// false - if we have exception</returns>
public int InsertRoundtripsToDatabase(Roundtrip roundtrip)
{
try
{
_connection.Open();
MySqlCommand insert = _connection.CreateCommand();
insert.CommandText =
"INSERT INTO roundtrip(departure,arrival,dep_date,dep_time,arr_date,arr_time,serviceClass,validate_carrier,validateFlight_number,operate_carrier,operateFlight_number,travelDuration) VALUES (@departure,@arrival,@depDate,@depTime,@arrDate,@arrTime,@serviceClass,@validateCarrier,@validateFlightNumber,@operateCarrier,@operateFlightNumber,@travelDuration);";
insert.Parameters.AddWithValue("@departure", roundtrip.Departure);
insert.Parameters.AddWithValue("@arrival", roundtrip.Arrival);
insert.Parameters.AddWithValue("@depDate", roundtrip.DepDate);
insert.Parameters.AddWithValue("@depTime", roundtrip.DepTime);
insert.Parameters.AddWithValue("@arrDate", roundtrip.ArrDate);
insert.Parameters.AddWithValue("@arrTime", roundtrip.ArrTime);
insert.Parameters.AddWithValue("@serviceClass", roundtrip.ServiceClass);
insert.Parameters.AddWithValue("@validateCarrier", roundtrip.ValidateCarrier);
insert.Parameters.AddWithValue("@validateFlightNumber", roundtrip.ValidateCarrierNumber);
insert.Parameters.AddWithValue("@operateCarrier", roundtrip.OperateCarrier);
insert.Parameters.AddWithValue("@operateFlightNumber", roundtrip.OperateCarrierNumber);
insert.Parameters.AddWithValue("@travelDuration", roundtrip.TravelDuration);
insert.ExecuteNonQuery();
_connection.Close();
return(GetRoundtripsId());
}
catch (MySqlException exception)
{
_connection.Close();
Debug.Assert(true, exception.Message);
return(0);
}
finally
{
_connection.Close();
}
}
/// <summary>
/// Check if roundtrip with given parameters exist in roundtrip table in database
/// </summary>
/// <param name="roundtrip">roundtrip</param>
/// <returns>Id entry if exist, and 0 if no entry with roundtrip with given parameters</returns>
/// <exception cref="Exception">MySqlException</exception>
public int RoundtripIsExistsInDatabase(Roundtrip roundtrip)
{
int id = 0;
try
{
_connection.Open();
MySqlCommand command = _connection.CreateCommand();
command.CommandText =
"SELECT Id FROM roundtrip WHERE departure=@departure AND arrival=@arrival AND serviceClass=@serviceClass AND validate_carrier=@validateCarrier AND validateFlight_number=@validateFlightNumber AND operate_carrier=@operateCarrier AND operateFlight_number=@operateFlightNumber AND travelDuration=@travelDuration AND dep_date=@dep_date AND dep_time=@dep_time AND arr_date=@arr_date AND arr_time=@arr_time;";
command.Parameters.AddWithValue("@departure", roundtrip.Departure);
command.Parameters.AddWithValue("@arrival", roundtrip.Arrival);
command.Parameters.AddWithValue("@serviceClass", roundtrip.ServiceClass);
command.Parameters.AddWithValue("@validateCarrier", roundtrip.ValidateCarrier);
command.Parameters.AddWithValue("@validateFlightNumber", roundtrip.ValidateCarrierNumber);
command.Parameters.AddWithValue("@operateCarrier", roundtrip.OperateCarrier);
command.Parameters.AddWithValue("@operateFlightNumber", roundtrip.OperateCarrierNumber);
command.Parameters.AddWithValue("@travelDuration", roundtrip.TravelDuration);
command.Parameters.AddWithValue("@dep_date", $"{roundtrip.DepDate:yyyy-MM-dd}");
command.Parameters.AddWithValue("@dep_time", $"{roundtrip.DepTime:HH:mm:ss}");
command.Parameters.AddWithValue("@arr_date", $"{roundtrip.ArrDate:yyyy-MM-dd}");
command.Parameters.AddWithValue("@arr_time", $"{roundtrip.ArrTime:HH:mm:ss}");
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
id = reader.GetInt32("Id");
}
}
_connection.Close();
return(id);
}
catch (MySqlException exception)
{
_connection.Close();
Debug.Assert(true, exception.Message);
return(0);
}
finally
{
_connection.Close();
}
}