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);
        }
Example #5
0
 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;
 }