using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace DataAccessLayer
{
public class ConnectionDb
{
/*PLEASE DO NOT REMOVE/MODIFY THE VARIABLES and METHODS - InsertData and
GetAllData LISTED BELOW/
/Internally used method starts*/
public static SqlConnection sqlConnection = null;
public static SqlCommand sqlCommand = null;
public static SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
public static DataSet dataset = new DataSet();
public static DataTable dataTable;
static readonly string connection =
ConfigurationManager.ConnectionStrings["HotelManagement"].ConnectionString;
public static int InsertData()
{
using (sqlConnection = new SqlConnection(connection))
{
sqlConnection.Open();
StringBuilder commandBuilder = new StringBuilder();
commandBuilder.Append("if not exists(select 1 from reservation
where iRoomNo=101 and dCheckInDate='2010-03-02' and dCheckoutDate='2017-03-04') ");
commandBuilder.Append("begin insert into reservation
values(134,'03/02/2010','03/04/2017',101,'Booked',6000) end");
SqlCommand cmd = new SqlCommand(commandBuilder.ToString())
{
Connection = sqlConnection
};
return cmd.ExecuteNonQuery();
}
}
public static DataTable GetAllData()
{
using (sqlConnection = new SqlConnection(connection))
{
sqlConnection.Open();
sqlCommand = new SqlCommand("select * from Reservation",
sqlConnection);
sqlDataAdapter = new SqlDataAdapter(sqlCommand);
dataset = new DataSet();
sqlDataAdapter.Fill(dataset);
dataTable = dataset.Tables[0];
return dataTable;
}
}
/*Internally used method end*/
public DataTable GetRoomTypes()
{
try
{
sqlConnection = new SqlConnection(connection);
sqlConnection.Open();
sqlCommand = new SqlCommand("select * from Roomtype",
sqlConnection);
sqlDataAdapter = new SqlDataAdapter(sqlCommand);
dataTable = new DataTable();
sqlDataAdapter.Fill(dataTable);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally { sqlConnection.Close(); }
return dataTable;
}
public int AddBooking(int cust_id, DateTime check_in, DateTime check_out,
int room_no, string reservation_Status, float total_Charge)
{
int reservationID = 0;
int res;
try
{
if (ValidateBooking(check_in, room_no) == false)
{
sqlConnection = new SqlConnection(connection);
sqlConnection.Open();
sqlCommand = new SqlCommand("insert into Reservation
values(@p1,@p2,@p3,@p4,@p5,@p6)", sqlConnection);
sqlCommand.Parameters.AddWithValue("@p1", cust_id);
sqlCommand.Parameters.AddWithValue("@p2", check_in);
sqlCommand.Parameters.AddWithValue("@p3", check_out);
sqlCommand.Parameters.AddWithValue("@p4", room_no);
sqlCommand.Parameters.AddWithValue("@p5", reservation_Status);
sqlCommand.Parameters.AddWithValue("@p6", total_Charge);
res = sqlCommand.ExecuteNonQuery();
reservationID++;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
sqlConnection.Close();
}
return reservationID;
}
public static bool ValidateBooking(DateTime checkInDate, int roomNo)
{
using (sqlConnection = new SqlConnection(connection))
{
sqlConnection.Open();
sqlCommand = new SqlCommand("select * from Reservation where
iRoomNo=@p1 and @p2 between dCheckInDate and dCheckoutDate", sqlConnection);
sqlCommand.Parameters.AddWithValue("@p1", roomNo);
sqlCommand.Parameters.AddWithValue("@p2", checkInDate);
sqlDataAdapter = new SqlDataAdapter(sqlCommand);
dataset = new DataSet();
sqlDataAdapter.Fill(dataset);
if (dataset.Tables[0].Rows.Count > 0)
{
return true;
}
return false;
}
}
public DataTable GetRoomDetails(string roomType)
{
try
{
using (sqlConnection = new SqlConnection(connection))
{
sqlConnection.Open();
sqlCommand = new SqlCommand("select * from Roomtype where
Roomtype=@p1", sqlConnection);
sqlCommand.Parameters.AddWithValue("@p1", roomType);
sqlDataAdapter = new SqlDataAdapter(sqlCommand);
dataTable = new DataTable();
sqlDataAdapter.Fill(dataTable);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return dataTable;
}
public static DataTable SearchRoomByDate(DateTime searchDate)
{
DataTable dataTable = null;
try
{
using (sqlConnection = new SqlConnection(connection))
{
sqlConnection.Open();
sqlCommand = new SqlCommand("select * from Reservation where
@p1 between dCheckInDate and dCheckoutDate", sqlConnection);
sqlCommand.Parameters.AddWithValue("@p1", searchDate);
sqlDataAdapter = new SqlDataAdapter(sqlCommand);
dataTable = new DataTable();
sqlDataAdapter.Fill(dataTable);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return dataTable;
}
}
}