public IEnumerable <Cafe> GetCafesByUserId(int Id)
{
DbCafeTable dbCafeTable = new DbCafeTable();
List <Cafe> cafes = new List <Cafe>();
try
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM Cafe, ZipCity WHERE Cafe.UserID = @id and ZipCity.Zip = cafe.Zip";
command.Parameters.AddWithValue("@id", Id);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Cafe cafe = new Cafe
{
Id = reader.GetInt32(reader.GetOrdinal("ID")),
Name = reader.GetString(reader.GetOrdinal("CName")),
City = reader.GetString(reader.GetOrdinal("City")),
Address = reader.GetString(reader.GetOrdinal("CAddress")),
PriceRange = reader.GetDecimal(reader.GetOrdinal("PriceRange")),
Rating = reader.GetInt32(reader.GetOrdinal("Rating")),
OpenTime = reader.GetDateTime(reader.GetOrdinal("OpenTime")),
CloseTime = reader.GetDateTime(reader.GetOrdinal("CloseTime")),
PhoneNo = reader.GetString(reader.GetOrdinal("PhoneNo")),
Description = reader.GetString(reader.GetOrdinal("CDescription")),
Type = reader.GetString(reader.GetOrdinal("CType")),
Zip = reader.GetString(reader.GetOrdinal("Zip")),
};
cafe.Tables = dbCafeTable.GetAllTablesInCafe(cafe.Id);
cafes.Add(cafe);
}
}
}
}
catch (Exception)
{
throw;
}
return(cafes);
}
public Cafe GetCafeById(int Id)
{
DbCafeTable dbCafeTable = new DbCafeTable();
DbUser dbUser = new DbUser();
Cafe cafe = new Cafe();
try
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM Cafe, ZipCity WHERE ZipCity.Zip = Cafe.Zip AND Cafe.ID = @id";
command.Parameters.AddWithValue("@id", Id);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
cafe.Id = reader.GetInt32(reader.GetOrdinal("ID"));
cafe.Name = reader.GetString(reader.GetOrdinal("CName"));
cafe.City = reader.GetString(reader.GetOrdinal("City"));
cafe.Address = reader.GetString(reader.GetOrdinal("CAddress"));
cafe.PriceRange = reader.GetDecimal(reader.GetOrdinal("PriceRange"));
cafe.Rating = reader.GetInt32(reader.GetOrdinal("Rating"));
cafe.OpenTime = reader.GetDateTime(reader.GetOrdinal("OpenTime"));
cafe.CloseTime = reader.GetDateTime(reader.GetOrdinal("CloseTime"));
cafe.PhoneNo = reader.GetString(reader.GetOrdinal("PhoneNo"));
cafe.Description = reader.GetString(reader.GetOrdinal("CDescription"));
cafe.Type = reader.GetString(reader.GetOrdinal("CType"));
cafe.User = dbUser.GetUserByID(reader.GetInt32(reader.GetOrdinal("UserID")));
cafe.Zip = reader.GetString(reader.GetOrdinal("Zip"));
cafe.Tables = dbCafeTable.GetAllTablesInCafe(cafe.Id);
}
}
}
}
catch (Exception)
{
throw;
}
return(cafe);
}
public IEnumerable <Booking> GetAllCafeBookings(int cafeId)
{
DbCafe dbCafe = new DbCafe();
DbCafeTable dbCafeTable = new DbCafeTable();
DbUser dbUser = new DbUser();
List <Booking> bookings = new List <Booking>();
try
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM Booking WHERE Booking.CafeID = @cafeId;";
command.Parameters.AddWithValue("@cafeId", cafeId);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Booking booking = new Booking
{
Id = reader.GetInt32(reader.GetOrdinal("ID")),
BookedDate = reader.GetDateTime(reader.GetOrdinal("BookedDate")),
StartDate = reader.GetDateTime(reader.GetOrdinal("StartDate")),
EndDate = reader.GetDateTime(reader.GetOrdinal("EndDate")),
User = dbUser.GetUserByID(reader.GetInt32(reader.GetOrdinal("UserID"))),
Cafe = dbCafe.GetCafeById(reader.GetInt32(reader.GetOrdinal("CafeID"))),
Table = dbCafeTable.GetTableByID(reader.GetInt32(reader.GetOrdinal("TableID")))
};
bookings.Add(booking);
}
}
}
}
catch (Exception)
{
throw;
}
return(bookings);
}
public Booking GetBookingById(int Id)
{
DbCafe dbCafe = new DbCafe();
DbCafeTable dbCafeTable = new DbCafeTable();
DbUser dbUser = new DbUser();
Booking booking = new Booking();
try
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM Booking WHERE booking.ID = @id";
command.Parameters.AddWithValue("@id", Id);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
booking.Id = reader.GetInt32(reader.GetOrdinal("ID"));
booking.BookedDate = reader.GetDateTime(reader.GetOrdinal("BookedDate"));
booking.StartDate = reader.GetDateTime(reader.GetOrdinal("StartDate"));
booking.EndDate = reader.GetDateTime(reader.GetOrdinal("EndDate"));
booking.User = dbUser.GetUserByID(reader.GetInt32(reader.GetOrdinal("UserID")));
booking.Cafe = dbCafe.GetCafeById(reader.GetInt32(reader.GetOrdinal("CafeID")));
booking.Table = dbCafeTable.GetTableByID(reader.GetInt32(reader.GetOrdinal("TableID")));
}
}
}
}
catch (Exception)
{
throw;
}
return(booking);
}