/// <summary>
/// Add the schedule for the database
/// </summary>
/// <parameter name="ScheduleInfo"></parameter>
/// <returns>Returns the number of rows affected</returns>
public int AddSchedule(Model.Entities.AirTravel.Schedule scheduleInfo)
{
IDbConnection conn = null;
int RouteId = 0;
RouteId = GetRouteID(scheduleInfo);
if (RouteId == 0)
{
throw new RouteNotAvailableForScheduleDAOException("Route not available for the given schedule information");
}
try
{
var scheduleID = InsertSchedule(scheduleInfo, ref conn, RouteId);
foreach (FlightCost item in scheduleInfo.GetFlightCosts())
{
int classId = (int)item.Class;
InsertFlightCost(scheduleID, classId, item.CostPerTicket);
}
}
catch (Common.ConnectToDatabaseException ex)
{
throw new ScheduleDAOException("Unable to add schedule", ex);
}
catch (Exception ex)
{
throw new ScheduleDAOException("Unable to add schedule", ex);
}
return(0);
}
/// <summary>
/// Gets the route id for a schedule from the database
/// </summary>
/// <parameter name="ScheduleInfo"></parameter>
/// <returns>Returns the route id for a given schedule</returns>
public int GetRouteID(Model.Entities.AirTravel.Schedule ScheduleInfo)
{
try
{
db = GetDatabaseConnection();
int RouteId = 0;
using (IDataReader Reader = db.ExecuteReader("getRouteId", ScheduleInfo.RouteInfo.FromCity.Name, ScheduleInfo.RouteInfo.ToCity.Name))
{
while (Reader.Read())
{
RouteId = int.Parse(Reader["RouteId"].ToString());
}
return(RouteId);
}
}
catch (Common.ConnectToDatabaseException)
{
throw new ScheduleDAOException("Unable to get route id");
}
catch (Exception)
{
throw new ScheduleDAOException("Unable to get route id");
}
}
/// <summary>
/// Add the schedule for the database
/// </summary>
/// <parameter name="scheduleInfo"></parameter>
/// <returns>Returns the number of rows affected by the insert</returns>
public int AddSchedule(Model.Entities.AirTravel.Schedule scheduleInfo)
{
int numberOfRows = 0;
try
{
long c = 0;
using (IDataReader reader = GetDatabaseConnection().ExecuteReader("InsertSchedule", scheduleInfo.FlightInfo.ID, scheduleInfo.RouteInfo.ID, scheduleInfo.DepartureTime, scheduleInfo.ArrivalTime, scheduleInfo.DurationInMins, scheduleInfo.IsActive))
{
if (reader.Read())
{
c = long.Parse(reader[0].ToString());
numberOfRows++;
}
foreach (FlightCost item in scheduleInfo.GetFlightCosts())
{
int ClassId = (int)item.Class;
string query = "insert into FlightCosts(ScheduleID,ClassID,CostPerTicket) values(" + c + "," + ClassId + "," + item.CostPerTicket + ")";
numberOfRows += GetDatabaseConnection().ExecuteNonQuery(CommandType.Text, query);
}
}
}
catch (Common.ConnectToDatabaseException)
{
throw new ScheduleDAOException("Unable to add schedule");
}
catch (Exception)
{
throw new ScheduleDAOException("Unable to add schedule");
}
return(numberOfRows);
}
/// <summary>
/// Gets the route id for a schedule from the database
/// </summary>
/// <parameter name="ScheduleInfo"></parameter>
/// <returns>Returns the route id for a given schedule</returns>
public int GetRouteID(Model.Entities.AirTravel.Schedule ScheduleInfo)
{
try
{
db = GetConnection();
int RouteId = 0;
using (IDataReader Reader = ExecuteStoredProcedureResults(db, "getRouteId",
new SqlParameter()
{
ParameterName = "@fromcity", DbType = DbType.Int64, Value = ScheduleInfo.RouteInfo.FromCity.CityId
},
new SqlParameter()
{
ParameterName = "@tocity", DbType = DbType.Int64, Value = ScheduleInfo.RouteInfo.ToCity.CityId
}
))
{
if (Reader.Read())
{
RouteId = int.Parse(Reader["RouteId"].ToString());
}
return(RouteId);
}
}
catch (Common.ConnectToDatabaseException)
{
throw new ScheduleDAOException("Unable to get route id");
}
catch (Exception)
{
throw new ScheduleDAOException("Unable to get route id");
}
}
/// <summary>
/// Update the existing schedule for the database
/// </summary>
/// <parameter name="scheduleInfo"></parameter>
/// <returns>Returns the number of rows affected by the update</returns>
public int UpdateSchedule(Model.Entities.AirTravel.Schedule scheduleInfo)
{
try
{
db = GetDatabaseConnection();
int routeId = 0;
using (IDataReader Reader = GetDatabaseConnection().ExecuteReader("getRouteId", scheduleInfo.RouteInfo.FromCity.CityId, scheduleInfo.RouteInfo.ToCity.CityId))
{
while (Reader.Read())
{
routeId = int.Parse(Reader["RouteId"].ToString());
}
}
return(db.ExecuteNonQuery("UpdateSchedule", scheduleInfo.ID, scheduleInfo.FlightInfo.ID, routeId, Convert.ToDateTime(scheduleInfo.DepartureTime.ToString()), Convert.ToDateTime(scheduleInfo.ArrivalTime.ToString()), scheduleInfo.DurationInMins, scheduleInfo.IsActive));
}
catch (Common.ConnectToDatabaseException)
{
throw new ScheduleDAOException("Unable to update schedule");
}
catch (Exception)
{
throw new ScheduleDAOException("Unable to update schedule");
}
}
/// <summary>
/// Gets the route id for a schedule from the database
/// </summary>
/// <parameter name="ScheduleInfo"></parameter>
/// <returns>Returns the route id for a given schedule</returns>
public int GetRouteID(Model.Entities.AirTravel.Schedule scheduleInfo)
{
IDbConnection conn = null;
try
{
conn = this.GetConnection();
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "getRouteId";
cmd.CommandType = CommandType.StoredProcedure;
IDataParameter p1 = cmd.CreateParameter();
p1.ParameterName = "@fromcity";
p1.Value = scheduleInfo.RouteInfo.FromCity.Name;
cmd.Parameters.Add(p1);
IDataParameter p2 = cmd.CreateParameter();
p2.ParameterName = "@tocity";
p2.Value = scheduleInfo.RouteInfo.ToCity.Name;
cmd.Parameters.Add(p2);
int RouteId = 0;
using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
RouteId = int.Parse(reader["RouteId"].ToString());
}
return(RouteId);
}
}
catch (Common.ConnectToDatabaseException)
{
throw new ScheduleDAOException("Unable to get route id");
}
catch (Exception)
{
throw new ScheduleDAOException("Unable to get route id");
}
}
/// <summary>
/// Update the existing schedule for the database
/// </summary>
/// <parameter name="scheduleInfo"></parameter>
/// <returns>Returns the status of the update</returns>
public bool UpdateSchedule(Model.Entities.AirTravel.Schedule scheduleInfo)
{
bool isUpdated = false;
try
{
db = GetDatabaseConnection();
db.ExecuteNonQuery("UpdateSchedule", scheduleInfo.ID, scheduleInfo.FlightInfo.ID, scheduleInfo.RouteInfo.ID, scheduleInfo.DepartureTime, scheduleInfo.ArrivalTime, scheduleInfo.DurationInMins, scheduleInfo.IsActive);
isUpdated = true;
}
catch (Common.ConnectToDatabaseException)
{
throw new ScheduleDAOException("Unable to update schedule");
}
catch (Exception)
{
throw new ScheduleDAOException("Unable to update schedule");
}
return(isUpdated);
}
/// <summary>
/// Update the existing schedule for the database
/// </summary>
/// <parameter name="ScheduleInfo"></parameter>
/// <returns>int</returns>
public bool UpdateSchedule(Model.Entities.AirTravel.Schedule scheduleInfo)
{
IDbConnection conn = null;
bool isUpdated = false;
try
{
int RouteId = GetRouteID(scheduleInfo);
conn = this.GetConnection();
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "UpdateSchedule";
cmd.CommandType = CommandType.StoredProcedure;
IDataParameter p1 = cmd.CreateParameter();
p1.ParameterName = "@scheduleid";
p1.Value = scheduleInfo.ID;
cmd.Parameters.Add(p1);
IDataParameter p2 = cmd.CreateParameter();
p2.ParameterName = "@flightid";
p2.Value = scheduleInfo.FlightInfo.ID;
cmd.Parameters.Add(p2);
IDataParameter p3 = cmd.CreateParameter();
p3.ParameterName = "@routeid";
p3.Value = RouteId;
cmd.Parameters.Add(p3);
//ADO.NET Bug
//STYCBG15.3 - Unable to update the schedule
//Need to change the timespan to datetime and then send the parameter
IDataParameter p4 = cmd.CreateParameter();
p4.ParameterName = "@arrivaltime";
DateTime dtAT = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, scheduleInfo.ArrivalTime.Hours, scheduleInfo.ArrivalTime.Minutes, 0);
p4.Value = dtAT;
cmd.Parameters.Add(p4);
//ADO.NET Bug
//STYCBG15.3 - Unable to update the schedule
//Need to introduce the line to store into value
//Need to change the timespan to datetime and then send the parameter
IDataParameter p7 = cmd.CreateParameter();
p7.ParameterName = "@departuretime";
DateTime dtDT = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, scheduleInfo.DepartureTime.Hours, scheduleInfo.DepartureTime.Minutes, 0);
p7.Value = dtDT;
cmd.Parameters.Add(p7);
IDataParameter p5 = cmd.CreateParameter();
p5.ParameterName = "@durationinmins";
p5.Value = scheduleInfo.DurationInMins;
cmd.Parameters.Add(p5);
IDataParameter p6 = cmd.CreateParameter();
p6.ParameterName = "@isactive";
p6.Value = scheduleInfo.IsActive;
cmd.Parameters.Add(p6);
cmd.ExecuteNonQuery();
isUpdated = true;
}
catch (Common.ConnectToDatabaseException)
{
throw new ScheduleDAOException("Unable to update schedule");
}
catch (Exception)
{
throw new ScheduleDAOException("Unable to update schedule");
}
finally
{
if (conn.State == ConnectionState.Open && conn != null)
{
conn.Close();
}
}
return(isUpdated);
}
/// <summary>
/// Inserts Data into Schedule Table and returns the schedule id
/// </summary>
/// <param name="ScheduleInfo"></param>
/// <param name="conn"></param>
/// <param name="RouteId"></param>
/// <returns>Returns the inserted schedule's id</returns>
private long InsertSchedule(Model.Entities.AirTravel.Schedule scheduleInfo, ref IDbConnection conn, int routeId)
{
conn = this.GetConnection();
conn.Open();
string insertSQL = @"insert into Schedules(FlightId,RouteId,DepartureTime,ArrivalTime,DurationInMins,IsActive)
values(@flightid,@routeid,@departmenttime,@arrivaltime,@dur,@isactive)";
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = insertSQL;
cmd.CommandType = CommandType.Text;
IDataParameter p1 = cmd.CreateParameter();
p1.ParameterName = "@flightid";
p1.Value = scheduleInfo.FlightInfo.ID;
cmd.Parameters.Add(p1);
IDataParameter p2 = cmd.CreateParameter();
p2.ParameterName = "@routeid";
p2.Value = routeId;
cmd.Parameters.Add(p2);
IDataParameter p3 = cmd.CreateParameter();
p3.ParameterName = "@departmenttime";
DateTime dTime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, scheduleInfo.DepartureTime.Hours, scheduleInfo.DepartureTime.Minutes, 0);
p3.Value = dTime;
cmd.Parameters.Add(p3);
IDataParameter p4 = cmd.CreateParameter();
p4.ParameterName = "@arrivaltime";
DateTime aTime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, scheduleInfo.ArrivalTime.Hours, scheduleInfo.ArrivalTime.Minutes, 0);
p4.Value = aTime;
cmd.Parameters.Add(p4);
IDataParameter p5 = cmd.CreateParameter();
p5.ParameterName = "@dur";
p5.Value = scheduleInfo.DurationInMins;
cmd.Parameters.Add(p5);
IDataParameter p6 = cmd.CreateParameter();
p6.ParameterName = "@isactive";
p6.Value = scheduleInfo.IsActive;
cmd.Parameters.Add(p6);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT @@IDENTITY AS TEMPVALUE";
long scheduleID = Convert.ToInt64(cmd.ExecuteScalar());
return(scheduleID);
}
/// <summary>
/// Update the existing schedule for the database
/// </summary>
/// <parameter name="ScheduleInfo"></parameter>
/// <returns>int</returns>
public bool UpdateSchedule(Model.Entities.AirTravel.Schedule scheduleInfo)
{
IDbConnection conn = null;
bool isUpdated = false;
try
{
int RouteId = GetRouteID(scheduleInfo);
conn = this.GetConnection();
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "UpdateSchedule";
IDataParameter p1 = cmd.CreateParameter();
p1.ParameterName = "@scheduleid";
p1.Value = scheduleInfo.ID;
cmd.Parameters.Add(p1);
IDataParameter p2 = cmd.CreateParameter();
p2.ParameterName = "@flightid";
p2.Value = scheduleInfo.FlightInfo.ID;
cmd.Parameters.Add(p2);
IDataParameter p3 = cmd.CreateParameter();
p3.ParameterName = "@routeid";
p3.Value = RouteId;
cmd.Parameters.Add(p3);
IDataParameter p4 = cmd.CreateParameter();
p4.ParameterName = "@arrivaltime";
p4.Value = scheduleInfo.ArrivalTime;
cmd.Parameters.Add(p4);
IDataParameter p7 = cmd.CreateParameter();
p7.ParameterName = "@departuretime";
cmd.Parameters.Add(p7);
IDataParameter p5 = cmd.CreateParameter();
p5.ParameterName = "@durationinmins";
p5.Value = scheduleInfo.DurationInMins;
cmd.Parameters.Add(p5);
IDataParameter p6 = cmd.CreateParameter();
p6.ParameterName = "@isactive";
p6.Value = scheduleInfo.IsActive;
cmd.Parameters.Add(p6);
cmd.ExecuteNonQuery();
isUpdated = true;
}
catch (Common.ConnectToDatabaseException)
{
throw new ScheduleDAOException("Unable to update schedule");
}
catch (Exception)
{
throw new ScheduleDAOException("Unable to update schedule");
}
finally
{
conn.Close();
}
return(isUpdated);
}
/// <summary>
/// Update the existing schedule for the database
/// </summary>
/// <parameter name="scheduleInfo"></parameter>
/// <returns>Returns the number of rows affected by the update</returns>
public int UpdateSchedule(Model.Entities.AirTravel.Schedule scheduleInfo)
{
try
{
db = GetConnection();
int routeId = 0;
using (IDataReader Reader = ExecuteStoredProcedureResults(db, "getRouteId",
new SqlParameter()
{
ParameterName = "@fromcity", DbType = DbType.Int64, Value = scheduleInfo.RouteInfo.FromCity.CityId
},
new SqlParameter()
{
ParameterName = "@tocity", DbType = DbType.Int64, Value = scheduleInfo.RouteInfo.ToCity.CityId
}
))
{
if (Reader.Read())
{
routeId = int.Parse(Reader["RouteId"].ToString());
}
}
byte IsActive = (scheduleInfo.IsActive) ? (byte)1 : (byte)0;
return(ExecuteStoredProcedure(db, "UpdateSchedule",
new SqlParameter()
{
ParameterName = "@scheduleid", DbType = DbType.Int32, Value = scheduleInfo.ID
},
new SqlParameter()
{
ParameterName = "@flightid", DbType = DbType.Int32, Value = scheduleInfo.FlightInfo.ID
},
new SqlParameter()
{
ParameterName = "@routeid", DbType = DbType.Int32, Value = routeId
},
new SqlParameter()
{
ParameterName = "@departuretime", DbType = DbType.DateTime, Value = Convert.ToDateTime(scheduleInfo.DepartureTime.ToString())
},
new SqlParameter()
{
ParameterName = "@arrivaltime", DbType = DbType.DateTime, Value = Convert.ToDateTime(scheduleInfo.ArrivalTime.ToString())
},
new SqlParameter()
{
ParameterName = "@durationinmins", DbType = DbType.Int32, Value = scheduleInfo.DurationInMins
},
new SqlParameter()
{
ParameterName = "@isactive", DbType = DbType.Boolean, Value = IsActive
}
));
}
catch (Common.ConnectToDatabaseException)
{
throw new ScheduleDAOException("Unable to update schedule");
}
catch (Exception)
{
throw new ScheduleDAOException("Unable to update schedule");
}
}
/// <summary>
/// Add the schedule for the database
/// </summary>
/// <parameter name="scheduleInfo"></parameter>
/// <returns>Returns the number of rows affected by the insert</returns>
public int AddSchedule(Model.Entities.AirTravel.Schedule scheduleInfo)
{
int numberOfRows = 0;
IDbTransaction tran = null;
try
{
using (db = GetConnection())
{
int routeId = 0;
using (IDataReader reader = ExecuteStoredProcedureResults(GetConnection(), "getRouteId",
new SqlParameter()
{
ParameterName = "@fromcity", DbType = DbType.Int64, Value = scheduleInfo.RouteInfo.FromCity.CityId
},
new SqlParameter()
{
ParameterName = "@tocity", DbType = DbType.Int64, Value = scheduleInfo.RouteInfo.ToCity.CityId
}
))
{
if (reader.Read())
{
routeId = int.Parse(reader["RouteId"].ToString());
}
}
tran = db.BeginTransaction();
int ScheduleID = 0;
//using (IDataReader reader = GetDatabaseConnection().ExecuteReader("InsertSchedule", scheduleInfo.FlightInfo.ID, routeId, Convert.ToDateTime(scheduleInfo.DepartureTime.ToString()), Convert.ToDateTime(scheduleInfo.ArrivalTime.ToString()), scheduleInfo.DurationInMins, scheduleInfo.IsActive))
ScheduleID = Convert.ToInt32(ExecuteStoredProcedureScalar(db, tran, "InsertSchedule",
new SqlParameter()
{
ParameterName = "@flightid", DbType = DbType.Int32, Value = scheduleInfo.FlightInfo.ID
},
new SqlParameter()
{
ParameterName = "@routeid", DbType = DbType.Int32, Value = routeId
},
new SqlParameter()
{
ParameterName = "@departuretime", DbType = DbType.DateTime, Value = Convert.ToDateTime(scheduleInfo.DepartureTime.ToString())
},
new SqlParameter()
{
ParameterName = "@arrivaltime", DbType = DbType.DateTime, Value = Convert.ToDateTime(scheduleInfo.ArrivalTime.ToString())
},
new SqlParameter()
{
ParameterName = "@dur", DbType = DbType.Int32, Value = scheduleInfo.DurationInMins
},
new SqlParameter()
{
ParameterName = "@isactive", DbType = DbType.Boolean, Value = scheduleInfo.IsActive
}
));
if (ScheduleID > 0)
{
numberOfRows++;
foreach (FlightCost item in scheduleInfo.GetFlightCosts())
{
int ClassId = (int)item.Class;
string query = "insert into FlightCosts(ScheduleID,ClassID,CostPerTicket) values(" + ScheduleID + "," + ClassId + "," + item.CostPerTicket + ")";
numberOfRows += ExecuteQuery(db, tran, query);
}
}
tran.Commit();
db.Close();
}
}
catch (Common.ConnectToDatabaseException)
{
RollbackTransactionAndCloseConnection(tran);
throw new ScheduleDAOException("Unable to add schedule");
}
catch (Exception)
{
RollbackTransactionAndCloseConnection(tran);
throw new ScheduleDAOException("Unable to add schedule");
}
return(numberOfRows);
}
