public void Save()
{
using (SqlConnection connection = DBConfiguration.GetConnection())
{
SqlCommand cmd = connection.CreateCommand();
cmd.Parameters.Add("@hotelId", SqlDbType.Int).Value = this.HotelId;
cmd.CommandText = (@"SELECT hotelId
FROM Hotels
WHERE hotelId = @hotelId");
bool hasRows = false;
using (SqlDataReader reader = cmd.ExecuteReader())
{
hasRows = reader.HasRows;
}
if (hasRows)
{
Update(connection);
}
else
{
Add(connection);
}
}
}
public ApplicationContext CreateDbContext(string[] args)
{
var builder = new DbContextOptionsBuilder <ApplicationContext>();
var connectionString = DBConfiguration.GetConnection();
builder.UseMySql(connectionString);
return(new ApplicationContext(builder.Options));
}
/* public void Save()
* {
* using ( SqlConnection connection = DBConfiguration.GetConnection() )
* {
* SqlCommand cmd = connection.CreateCommand();
* cmd.Parameters.Add( "@hotelId", SqlDbType.Int ).Value = this.HotelId;
* cmd.Parameters.Add( "@hotelName", SqlDbType.VarChar ).Value = this.Name;
* cmd.Parameters.Add( "@phone", SqlDbType.VarChar ).Value = this.Phone;
* cmd.Parameters.Add( "@state", SqlDbType.Int ).Value = this.State;
* cmd.Parameters.Add( "@address", SqlDbType.VarChar ).Value = this.Address;
* cmd.Parameters.Add( "@email", SqlDbType.VarChar ).Value = this.Email;
*
* cmd.CommandText = ( @"UPDATE Hotels
* SET name = @hotelName,
* addres = @address,
* phone = @phone,
* email = @email,
* state = @state
* WHERE hotelId = @hotelId" );
* cmd.ExecuteReader();
* }
* }*/
public static void Delete(int hotelId)
{
using (SqlConnection connection = DBConfiguration.GetConnection())
{
SqlCommand cmd = connection.CreateCommand();
cmd.Parameters.Add("@hotelId", SqlDbType.Int).Value = hotelId;
cmd.CommandText = @"DELETE FROM Hotels WHERE hotelId = @hotelId";
using (SqlCommand command = cmd)
{
command.ExecuteNonQuery();
}
}
}
public void Add()
{
using (SqlConnection connection = DBConfiguration.GetConnection())
{
SqlCommand cmd = connection.CreateCommand();
cmd.Parameters.Add("@hotelId", SqlDbType.Int).Value = this.HotelId;
cmd.Parameters.Add("@roomName", SqlDbType.VarChar).Value = this.Name;
cmd.Parameters.Add("@adultBeds", SqlDbType.Int).Value = this.AdultBeds;
cmd.Parameters.Add("@state", SqlDbType.Int).Value = this.State;
cmd.Parameters.Add("@childrenBeds", SqlDbType.Int).Value = this.ChildrenBeds;
cmd.Parameters.Add("@roomId", SqlDbType.Int).Value = this.RoomId;
cmd.CommandText = (@"INSERT INTO Rooms ( hotelId, name, adultBeds, childrenBeds, state)
VALUES ( @hotelId, @roomName, @adultBeds, @childrenBeds, @state)");
cmd.ExecuteReader();
}
}
public static List <Hotel> GetAllHotels()
{
List <Hotel> hotelList = new List <Hotel>();
using (SqlConnection connection = DBConfiguration.GetConnection())
{
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = (@"SELECT *
FROM Hotels");
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Hotel hotel = new Hotel(reader);
hotelList.Add(hotel);
}
}
}
return(hotelList);
}
public static List <Room> GetAllRooms(int HotelId)
{
List <Room> roomList = new List <Room>();
using (SqlConnection connection = DBConfiguration.GetConnection())
{
SqlCommand cmd = connection.CreateCommand();
cmd.Parameters.Add("@HotelId", SqlDbType.Int).Value = HotelId;
cmd.CommandText = (@"SELECT *
FROM Rooms
WHERE hotelId = @HotelId");
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Room room = new Room(reader);
roomList.Add(room);
}
}
}
return(roomList);
}
public static Room GetInstance(int id)
{
Room room = null;
using (SqlConnection connection = DBConfiguration.GetConnection())
{
SqlCommand cmd = connection.CreateCommand();
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
cmd.CommandText = (@"SELECT *
FROM Rooms
WHERE roomId=@id");
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
room = new Room(reader);
}
}
}
return(room);
}
public void Save()
{
using (SqlConnection connection = DBConfiguration.GetConnection())
{
SqlCommand cmd = connection.CreateCommand();
cmd.Parameters.Add("@hotelId", SqlDbType.Int).Value = this.HotelId;
cmd.Parameters.Add("@roomName", SqlDbType.VarChar).Value = this.Name;
cmd.Parameters.Add("@adultBeds", SqlDbType.Int).Value = this.AdultBeds;
cmd.Parameters.Add("@state", SqlDbType.Int).Value = this.State;
cmd.Parameters.Add("@childrenBeds", SqlDbType.Int).Value = this.ChildrenBeds;
cmd.Parameters.Add("@roomId", SqlDbType.Int).Value = this.RoomId;
cmd.CommandText = (@"UPDATE Rooms
SET name = @roomName,
hotelId = @hotelId,
adultBeds = @adultBeds,
childrenBeds = @childrenBeds,
state = @state
WHERE roomId = @roomId");
cmd.ExecuteReader();
}
}
public static Hotel GetInstance(int id)
{
Hotel hotel = null;
using (SqlConnection connection = DBConfiguration.GetConnection())
{
SqlCommand cmd = connection.CreateCommand();
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
cmd.CommandText = (@"SELECT *
FROM Hotels
WHERE hotelId=@id");
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
hotel = new Hotel(reader);
}
}
}
return(hotel);
}
static public List <SearchResult> FindTariff(SearchRequest searchParams)
{
List <SearchResult> searchResultList = new List <SearchResult>();
using (SqlConnection connection = DBConfiguration.GetConnection())
{
int foundHotelId = -1;
int foundRoomlId = -1;
SqlCommand findHotelId = connection.CreateCommand();
findHotelId.Parameters.Add("@hotelName", SqlDbType.VarChar).Value = searchParams.HotelName;
findHotelId.CommandText = (@"SELECT [Hotels].[hotelId] FROM [dbo].[Hotels] WHERE [Hotels].[name]=@hotelName");
using (SqlDataReader reader = findHotelId.ExecuteReader())
{
while (reader.Read())
{
foundHotelId = reader.GetInt32(reader.GetOrdinal("hotelId"));
}
}
if (foundHotelId == -1)
{
return(new List <SearchResult>());
}
SqlCommand findRoomId = connection.CreateCommand();
findRoomId.Parameters.Add("@roomName", SqlDbType.VarChar).Value = searchParams.RoomName;
findRoomId.Parameters.Add("@hotelId", SqlDbType.Int).Value = foundHotelId;
findRoomId.CommandText = (@"SELECT [Rooms].[roomId] FROM [dbo].[Rooms] WHERE [Rooms].[name]=@roomName AND [Rooms].[hotelId]=@hotelId");
using (SqlDataReader reader = findRoomId.ExecuteReader())
{
while (reader.Read())
{
foundRoomlId = reader.GetInt32(reader.GetOrdinal("roomId"));
}
}
if (foundRoomlId == -1)
{
return(new List <SearchResult>());
}
SqlCommand cmd = connection.CreateCommand();
cmd.Parameters.Add("@hotelId", SqlDbType.Int).Value = foundHotelId;
cmd.Parameters.Add("@roomId", SqlDbType.VarChar).Value = foundRoomlId;
cmd.Parameters.Add("@date", SqlDbType.Date).Value = searchParams.Date;
cmd.CommandText = (@"SELECT
[Price].[tariffId]
,[Tariff].[name]
,[Price].[price]
,[Price].[datePrice]
,[Price].[roomId]
FROM [dbo].[Price]
LEFT JOIN [Tariff] ON [Price].[tariffId] = [Tariff].[tariffId]
WHERE [Tariff].[hotelId] = @hotelId
AND [Price].[roomId] = @roomId
AND [Price].[datePrice] = @date");
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
SearchResult result = new SearchResult();
result.SetData(reader);
searchResultList.Add(result);
}
}
}
return(searchResultList);
}