public IEnumerable<Reservation> GetAllReservations()
{
SqlCommand sqlCommand = new SqlCommand("SELECT * FROM Reservations", sqlConnection);
List<Reservation> reservations = null;
try {
reservations = new List<Reservation>();
SqlDataReader reader = sqlCommand.ExecuteReader();
while(reader.Read()) {
Reservation r = new Reservation();
r.Id = Convert.ToInt32(reader[0]);
r.FromDate = Convert.ToDateTime(reader[3]);
r.ToDate = Convert.ToDateTime(reader[4]);
r.Bus = GetBus(Convert.ToString(reader[2]));
r.User = GetUser(Convert.ToInt32(reader[1]));
reservations.Add(r);
}
} catch(Exception e) {
Debug.WriteLine(e.Message);
}
return reservations;
}
public IEnumerable<Reservation> GetBusReservation(String regNo, DateTime date)
{
SqlCommand sqlCommand = new SqlCommand("SELECT * FROM Reservations r WHERE bus='AB12345' AND CONVERT(char(10),r.fromDate,126)=@date", sqlConnection);
List<Reservation> list = new List<Reservation>();
// regno
SqlParameter sqlParameter = new SqlParameter("@regNo", SqlDbType.NVarChar);
sqlParameter.Value = regNo;
sqlCommand.Parameters.Add(sqlParameter);
// date
sqlParameter = new SqlParameter("@date", SqlDbType.DateTime);
sqlParameter.Value = date;
sqlCommand.Parameters.Add(sqlParameter);
SqlDataReader reader = null;
try {
reader = sqlCommand.ExecuteReader();
while(reader.Read()) {
Reservation r = new Reservation();
r.Id = Convert.ToInt32(reader[0]);
r.FromDate = Convert.ToDateTime(reader[4]);
r.ToDate = Convert.ToDateTime(reader[3]);
r.User = GetUser(Convert.ToInt32(reader[1]));
r.Bus = GetBus(Convert.ToString(reader[2]));
list.Add(r);
}
} catch(Exception e) {
Debug.WriteLine(e.Message);
} finally {
if(reader != null)
reader.Close();
}
return list;
}
public Reservation GetReservation(int id)
{
SqlCommand sqlCommand = new SqlCommand("SELECT * FROM Reservations WHERE id=" + id, sqlConnection);
Reservation r = null;
SqlDataReader reader = null;
try {
reader = sqlCommand.ExecuteReader();
while(reader.Read()) {
r = new Reservation();
r.Id = Convert.ToInt32(reader[0]);
r.FromDate = Convert.ToDateTime(reader[3]);
r.ToDate = Convert.ToDateTime(reader[4]);
r.Bus = GetBus(Convert.ToString(reader[2]));
r.User = GetUser(Convert.ToInt32(reader[1]));
}
} catch(Exception e) {
Debug.WriteLine(e.Message);
} finally {
if(reader != null)
reader.Close();
}
return r;
}
public Reservation PostReservation(Reservation reservation)
{
SqlCommand sqlCommand = new SqlCommand("INSERT INTO Reservations VALUES(@username,@bus,@fromDate,@toDate); SELECT Scope_Identity();", sqlConnection);
// Username
SqlParameter sqlParameter = new SqlParameter("@username", SqlDbType.Int);
sqlParameter.Value = reservation.User.Mobile;
sqlCommand.Parameters.Add(sqlParameter);
// Bus
sqlParameter = new SqlParameter("@bus", SqlDbType.NVarChar);
sqlParameter.Value = reservation.Bus.RegNo;
sqlCommand.Parameters.Add(sqlParameter);
// fromDate
sqlParameter = new SqlParameter("@fromDate", SqlDbType.DateTime);
sqlParameter.Value = reservation.FromDate;
sqlCommand.Parameters.Add(sqlParameter);
// toDate
sqlParameter = new SqlParameter("@toDate", SqlDbType.DateTime);
sqlParameter.Value = reservation.ToDate;
sqlCommand.Parameters.Add(sqlParameter);
int id = -1;
try {
id = Convert.ToInt32(sqlCommand.ExecuteScalar());
} catch(Exception e) {
Debug.WriteLine(e.Message);
}
return GetReservation(id);
}
public IEnumerable<Reservation> GetUserReservations(int mobile)
{
SqlCommand sqlCommand = new SqlCommand("SELECT * FROM Reservations WHERE username="******";");
List<Reservation> list = null;
SqlDataReader reader = null;
try {
reader = sqlCommand.ExecuteReader();
list = new List<Reservation>();
while(reader.Read()) {
Reservation r = new Reservation();
r.Id = Convert.ToInt32(reader[0]);
r.User = Get(mobile);
r.ToDate = Convert.ToDateTime(reader[4]);
r.FromDate = Convert.ToDateTime(reader[3]);
r.Bus = GetBus(Convert.ToString(reader[2]));
list.Add(r);
}
} catch(Exception e) {
Debug.WriteLine(e.Message);
} finally {
if(reader != null) {
reader.Close();
}
}
return list;
}