static public void AddNewUser(Entities.User user)
{
using (OracleConnection conn = DatabaseConection.GetOracleConnection())
{
using (OracleCommand OA = new OracleCommand("Insert_new_user", conn))
{
OA.CommandType = CommandType.StoredProcedure;
OracleParameter OP1 = new OracleParameter();
OP1.ParameterName = "nm";
OP1.OracleDbType = OracleDbType.Varchar2;
OP1.Direction = ParameterDirection.Input;
OP1.Value = user.Username;
OracleParameter OP2 = new OracleParameter();
OP2.ParameterName = "pasword";
OP2.OracleDbType = OracleDbType.Varchar2;
OP2.Direction = ParameterDirection.Input;
OP2.Value = user.Password;
OracleParameter OP3 = new OracleParameter();
OP3.ParameterName = "mail";
OP3.OracleDbType = OracleDbType.Varchar2;
OP3.Direction = ParameterDirection.Input;
OP3.Value = user.Email;
OA.Parameters.Add(OP1);
OA.Parameters.Add(OP2);
OA.Parameters.Add(OP3);
OA.ExecuteNonQuery();
}
}
}
public static Dictionary <int, string> GetCountries()
{
using (OracleConnection conn = DatabaseConection.GetOracleConnection())
{
using (OracleDataAdapter da = new OracleDataAdapter("Select_Countries", conn))
{
da.SelectCommand.CommandType = CommandType.StoredProcedure;
OracleParameter OP = new OracleParameter();
OP.ParameterName = "S";
OP.OracleDbType = OracleDbType.RefCursor;
OP.Direction = ParameterDirection.Output;
da.SelectCommand.Parameters.Add(OP);
DataTable Country_table = new DataTable();
da.Fill(Country_table);
Dictionary <int, string> Temp_CountryDictionary = new Dictionary <int, string>();
foreach (DataRow row in Country_table.Rows)
{
Temp_CountryDictionary.Add(Int32.Parse(row["Country_id"].ToString()), row["Country_Name"].ToString());
}
return(Temp_CountryDictionary);
}
}
}
private Dictionary <int, string> GetHotel(int ID)
{
Dictionary <int, string> LocalDictionary = new Dictionary <int, string>();
using (OracleConnection conn = DatabaseConection.GetOracleConnection())
{
using (OracleDataAdapter OA = new OracleDataAdapter("Select_Hotels", conn))
{
OA.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
OracleParameter OP = new OracleParameter();
OP.ParameterName = "S";
OP.OracleDbType = OracleDbType.RefCursor;
OP.Direction = System.Data.ParameterDirection.Output;
OracleParameter OP1 = new OracleParameter();
OP1.ParameterName = "loc_id";
OP1.OracleDbType = OracleDbType.Int32;
OP1.Direction = System.Data.ParameterDirection.Input;
OP1.Value = ID;
OA.SelectCommand.Parameters.Add(OP);
OA.SelectCommand.Parameters.Add(OP1);
DataTable City_table = new DataTable();
OA.Fill(City_table);
foreach (DataRow row in City_table.Rows)
{
LocalDictionary.Add(Int32.Parse(row["IdHotel"].ToString()), row["Nume Hotel"].ToString());
}
return(LocalDictionary);
}
}
}
public static DataTable GetRooms(int ID)
{
using (OracleConnection conn = DatabaseConection.GetOracleConnection())
{
using (OracleDataAdapter OA = new OracleDataAdapter("Select_Rooms2", conn))
{
OA.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
OracleParameter OP = new OracleParameter();
OP.ParameterName = "S";
OP.OracleDbType = OracleDbType.RefCursor;
OP.Direction = System.Data.ParameterDirection.Output;
OracleParameter OP1 = new OracleParameter();
OP1.ParameterName = "H_Id";
OP1.OracleDbType = OracleDbType.Int32;
OP1.Direction = System.Data.ParameterDirection.Input;
OP1.Value = ID;
OA.SelectCommand.Parameters.Add(OP1);
OA.SelectCommand.Parameters.Add(OP);
DataTable City_table = new DataTable();
OA.Fill(City_table);
return(City_table);
}
}
}
/// <summary>
/// Inserts the new rented room in the table designated them
/// </summary>
/// <param name="renter"></param>
public static void InsertRenter(Entities.Rent_Rooms renter, Entities.RentCars car)
{
using (OracleConnection conn = DatabaseConection.GetOracleConnection())
{
using (OracleCommand OA = new OracleCommand("Make_Reservation", conn))
{
OA.CommandType = CommandType.StoredProcedure;
OracleParameter OP1 = new OracleParameter();
OP1.ParameterName = "RoomID";
OP1.OracleDbType = OracleDbType.Int32;
OP1.Direction = System.Data.ParameterDirection.Input;
OP1.Value = renter.ROOM_ID;
OracleParameter OP2 = new OracleParameter();
OP2.ParameterName = "dateS";
OP2.OracleDbType = OracleDbType.Date;
OP2.Direction = System.Data.ParameterDirection.Input;
OP2.Value = renter.START_DATE;
OracleParameter OP3 = new OracleParameter();
OP3.ParameterName = "dateE";
OP3.OracleDbType = OracleDbType.Date;
OP3.Direction = System.Data.ParameterDirection.Input;
OP3.Value = renter.END_DATE;
OracleParameter OP4 = new OracleParameter();
OP4.ParameterName = "ReservID";
OP4.OracleDbType = OracleDbType.Varchar2;
OP4.Direction = System.Data.ParameterDirection.Input;
OP4.Value = renter.ReservationID;
OracleParameter OP5 = new OracleParameter();
OP5.ParameterName = "Car";
if (car != null)
{
OP5.OracleDbType = OracleDbType.Int32;
OP5.Direction = System.Data.ParameterDirection.Input;
OP5.Value = car.RENT_CARS_TYPE_ID.ToString();
}
else
{
OP5.OracleDbType = OracleDbType.Int32;
OP5.Direction = System.Data.ParameterDirection.Input;
OP5.Value = DBNull.Value;
}
OA.Parameters.Add(OP2);
OA.Parameters.Add(OP3);
OA.Parameters.Add(OP1);
OA.Parameters.Add(OP4);
OA.Parameters.Add(OP5);
OA.ExecuteNonQuery();
}
}
}
public static DataTable GetOcupiedRooms(string StoreProcedureName)
{
using (OracleConnection conn = DatabaseConection.GetOracleConnection())
{
using (OracleDataAdapter OA = new OracleDataAdapter(StoreProcedureName, conn))
{
OA.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
OracleParameter OP = new OracleParameter();
OP.ParameterName = "S";
OP.OracleDbType = OracleDbType.RefCursor;
OP.Direction = System.Data.ParameterDirection.Output;
OA.SelectCommand.Parameters.Add(OP);
DataTable Filed_Rooms = new DataTable();
OA.Fill(Filed_Rooms);
return(Filed_Rooms);
}
}
}
static public string GetEmail(string username, string password)
{
using (OracleConnection conn = DatabaseConection.GetOracleConnection())
{
using (OracleCommand OA = new OracleCommand("GETemail", conn))
{
OA.CommandType = CommandType.StoredProcedure;
OracleParameter OP1 = new OracleParameter();
OP1.ParameterName = "usernameVar";
OP1.OracleDbType = OracleDbType.Varchar2;
OP1.Direction = ParameterDirection.Input;
OP1.Value = username;
OracleParameter OP2 = new OracleParameter();
OP2.ParameterName = "passwordVar";
OP2.OracleDbType = OracleDbType.Varchar2;
OP2.Direction = ParameterDirection.Input;
OP2.Value = password;
OracleParameter OP3 = new OracleParameter();
OP3.ParameterName = "e_mail";
OP3.OracleDbType = OracleDbType.Varchar2;
OP3.Direction = ParameterDirection.ReturnValue;
OA.Parameters.Add(OP1);
OA.Parameters.Add(OP2);
OA.Parameters.Add(OP3);
OA.ExecuteNonQuery();
return(Convert.ToString(OA.Parameters["e_mail"].Value));
}
}
}
public static void Get_Cars_Rent(int H_ID)
{
using (OracleConnection conn = DatabaseConection.GetOracleConnection())
{
using (OracleDataAdapter OA = new OracleDataAdapter("SelectRentCars", conn))
{
OA.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
OracleParameter OP = new OracleParameter();
OP.ParameterName = "S";
OP.OracleDbType = OracleDbType.RefCursor;
OP.Direction = System.Data.ParameterDirection.Output;
OracleParameter OP1 = new OracleParameter();
OP1.ParameterName = "ID_Hotel";
OP1.OracleDbType = OracleDbType.Int32;
OP1.Direction = System.Data.ParameterDirection.Input;
OP1.Value = H_ID;
OA.SelectCommand.Parameters.Add(OP);
OA.SelectCommand.Parameters.Add(OP1);
DataTable Cars_rent = new DataTable();
OA.Fill(Cars_rent);
Rents = new List <Entities.RentCars>();
Rents = (from DataRow dt in Cars_rent.Rows
select new Entities.RentCars()
{
CAR_NAME_TYPE = dt["CarType"].ToString(),
RENT_CARS_TYPE_ID = Int32.Parse(dt["RentID"].ToString()),
RENT_PRICE = int.Parse(dt["RentPrice"].ToString()),
HOTEL_ID = int.Parse(dt["HotelID"].ToString())
}).ToList();
}
}
}