/// <summary>
/// voegt een nieuwe reservatie toe
/// </summary>
/// <param name="reserv"></param>
/// <returns> True of False (naargelang het toevoegen gelukt is) </returns>
public static Boolean AddReservation(Reservation reserv)
{
SqlConnection conn = new SqlConnection(SamenSterkerDB.GetConnection());
String insertStatement = "INSERT INTO reservation(Number, StartDate, EndDate, LocationId) " +
"VALUES (@number, @startDate, @endDate, @locationId)";
SqlCommand insertCommand = new SqlCommand(insertStatement, conn);
insertCommand.Parameters.AddWithValue("@number", reserv.number);
insertCommand.Parameters.AddWithValue("@startDate", reserv.startDate);
insertCommand.Parameters.AddWithValue("@endDate", reserv.endDate);
insertCommand.Parameters.AddWithValue("@locationId", reserv.locationId);
try
{
conn.Open();
int success;
success = insertCommand.ExecuteNonQuery();
if (success == 1)
{
return(true);
}
return(false);
}
catch (SqlException ex)
{
Logging logger = new Logging("log.txt");
logger.WriteLine("ReservationDB", ex.Message);
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// verwijdert een bedrijf met zijn gegevens
/// </summary>
/// <param name="comp"></param>
/// <returns> True of False (naargelang de update geslaagd is) </returns>
public static Boolean DeleteCompany(Company comp)
{
SqlConnection conn = new SqlConnection(SamenSterkerDB.GetConnection());
String deleteStatement =
"DELETE FROM company " +
"WHERE Id = @Id ";
SqlCommand deleteCommand = new SqlCommand(deleteStatement, conn);
deleteCommand.Parameters.AddWithValue("@Id", comp.id);
try
{
conn.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
Logging logger = new Logging("log.txt");
logger.WriteLine("CompanyDB", ex.Message);
throw ex;
}
}
public static List <Location> GetLocationList()
{
List <Location> LocationList = new List <Location>();
SqlConnection conn = new SqlConnection(SamenSterkerDB.GetConnection());
string selectStatement = "SELECT Id, Name " +
"FROM location ";
SqlCommand selectCommand = new SqlCommand(selectStatement, conn);
try
{
conn.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Location loc = new Location();
loc.id = (int)reader["Id"];
loc.name = reader["Name"].ToString();
LocationList.Add(loc);
}
reader.Close();
}
catch (SqlException ex)
{
Logging logger = new Logging("log.txt");
logger.WriteLine("LocationDB", ex.Message);
throw ex;
}
finally
{
/// connectie sluiten
conn.Close();
}
return(LocationList);
}
/// <summary>
/// past de gegevens van een bedrijf aan
/// </summary>
/// <param name="old"></param>
/// <param name="updated"></param>
/// <returns> True of False (naargelang de update geslaagd is) </returns>
public static Boolean UpdateCompany(Company old, Company updated)
{
SqlConnection conn = new SqlConnection(SamenSterkerDB.GetConnection());
String updateStatement =
"UPDATE Company SET " +
"Name = @newName, " +
"Street = @newStreet, " +
"Zipcode = @newZipcode, " +
"City = @newCity, " +
"Country = @newCountry, " +
"Email = @newEmail, " +
"Phone = @newPhone, " +
"WHERE " +
"Id = @Id ";
SqlCommand updateCommand = new SqlCommand(updateStatement, conn);
updateCommand.Parameters.AddWithValue("@newName", updated.name);
updateCommand.Parameters.AddWithValue("@newStreet", updated.street);
updateCommand.Parameters.AddWithValue("@newZipcode", updated.zipcode);
updateCommand.Parameters.AddWithValue("@newCity", updated.city);
updateCommand.Parameters.AddWithValue("@newCountry", updated.country);
updateCommand.Parameters.AddWithValue("@newEmail", updated.email);
updateCommand.Parameters.AddWithValue("@newPhone", updated.phone);
updateCommand.Parameters.AddWithValue("@Id", old.id);
try
{
conn.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
Logging logger = new Logging("log.txt");
logger.WriteLine("CompanyDB", ex.Message);
throw ex;
}
}
/// <summary>
/// past een reservatie aan
/// </summary>
/// <param name="old"></param>
/// <param name="updated"></param>
/// <returns> True of False (naargelang de update gelukt is) </returns>
public static Boolean UpdateReservation(Reservation old, Reservation updated)
{
SqlConnection conn = new SqlConnection(SamenSterkerDB.GetConnection());
String updateStatement =
"UPDATE Reservation SET " +
"Number = @newNumber, " +
"StartDate = @newStartDate, " +
"EndDate = @newEndDate, " +
"CompanyId = @newCompanyId, " +
"LocationId = @newLocationId, " +
"WHERE " +
"Id = @Id ";
SqlCommand updateCommand = new SqlCommand(updateStatement, conn);
updateCommand.Parameters.AddWithValue("@newNumber", updated.number);
updateCommand.Parameters.AddWithValue("@newStartDate", updated.startDate);
updateCommand.Parameters.AddWithValue("@newEndDate", updated.endDate);
updateCommand.Parameters.AddWithValue("@newCompanyId", updated.companyId);
updateCommand.Parameters.AddWithValue("@newLocationId", updated.locationId);
updateCommand.Parameters.AddWithValue("@Id", old.id);
try
{
conn.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
Logging logger = new Logging("log.txt");
logger.WriteLine("ReservationDB", ex.Message);
throw ex;
}
}
/// <summary>
/// haalt een lijst op met de gegevens van bedrijven
/// </summary>
/// <returns> companyList (lijst van bedrijven) </returns>
public static List <Company> GetCompanyList()
{
List <Company> companyList = new List <Company>();
SqlConnection conn = new SqlConnection(SamenSterkerDB.GetConnection());
string selectStatement = "SELECT Id, Name, Street, Zipcode, City, Country, Email, Phone " +
"FROM company ";
SqlCommand selectCommand = new SqlCommand(selectStatement, conn);
try
{
conn.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Company comp = new Company();
comp.id = (int)reader["Id"];
comp.name = reader["Name"].ToString();
comp.street = reader["Street"].ToString();
comp.zipcode = (int)reader["Zipcode"];
comp.city = reader["City"].ToString();
comp.country = reader["Country"].ToString();
comp.email = reader["Email"].ToString();
comp.phone = reader["Phone"].ToString();
companyList.Add(comp);
}
reader.Close();
}
catch (SqlException ex)
{
Logging logger = new Logging("log.txt");
logger.WriteLine("CompanyDB", ex.Message);
throw ex;
}
finally
{
/// connectie sluiten
conn.Close();
}
return(companyList);
}
/// <summary>
/// haalt een lijst met de reservaties op
/// </summary>
/// <returns> reservationList (lijst van reservaties) </returns>
public static List <Reservation> GetReservationList()
{
List <Reservation> reservationList = new List <Reservation>();
SqlConnection conn = new SqlConnection(SamenSterkerDB.GetConnection());
string selectStatement = "SELECT reservation.Id, reservation.Number, reservation.StartDate, reservation.EndDate, reservation.CompanyId, reservation.LocationId, reservation.CreateDate, location.Name as Name " +
"FROM reservation INNER JOIN location ON reservation.LocationId = location.Id ";
SqlCommand selectCommand = new SqlCommand(selectStatement, conn);
try
{
conn.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Reservation reserv = new Reservation();
reserv.id = (int)reader["Id"];
reserv.number = (int)reader["Number"];
reserv.startDate = (DateTime)reader["StartDate"];
reserv.endDate = (DateTime)reader["EndDate"];
reserv.companyId = (int)reader["CompanyId"];
reserv.locationId = (int)reader["LocationId"];
reserv.createDate = (DateTime)reader["CreateDate"];
reserv.locationName = reader["Name"].ToString();
reservationList.Add(reserv);
}
reader.Close();
}
catch (SqlException ex)
{
Logging logger = new Logging("log.txt");
logger.WriteLine("ReservationDB", ex.Message);
throw ex;
}
finally
{
/// connectie sluiten
conn.Close();
}
return(reservationList);
}
public static List <Contract> GetContractList()
{
List <Contract> contractList = new List <Contract>();
SqlConnection conn = new SqlConnection(SamenSterkerDB.GetConnection());
string selectStatement = "SELECT Id, Number, StartDate, EndDate, CompanyId, ContractFormulaId " +
"FROM contract ";
SqlCommand selectCommand = new SqlCommand(selectStatement, conn);
try
{
conn.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Contract cont = new Contract();
cont.id = (int)reader["Id"];
cont.number = (int)reader["Number"];
cont.startDate = (DateTime)reader["StartDate"];
cont.endDate = (DateTime)reader["EndDate"];
cont.companyId = (int)reader["CompanyId"];
cont.contractFormulaId = (int)reader["ContractFormulaId"];
contractList.Add(cont);
}
reader.Close();
}
catch (SqlException ex)
{
Logging logger = new Logging("log.txt");
logger.WriteLine("ContractDB", ex.Message);
throw ex;
}
finally
{
/// connectie sluiten
conn.Close();
}
return(contractList);
}
public static List <ContractFormula> GetContractFormulaList()
{
List <ContractFormula> contractFormulaList = new List <ContractFormula>();
SqlConnection conn = new SqlConnection(SamenSterkerDB.GetConnection());
string selectStatement = "SELECT Id, Description, MaxUsageHoursPerPeriod, PeriodInMonths, NoticePeriodInMonths, Price " +
"FROM contractFormula ";
SqlCommand selectCommand = new SqlCommand(selectStatement, conn);
try
{
conn.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
ContractFormula contrForm = new ContractFormula();
contrForm.id = (int)reader["Id"];
contrForm.description = reader["Description"].ToString();
contrForm.maxUsageHoursPerPeriod = (int)reader["MaxUsageHoursPerPeriod"];
contrForm.periodInMonths = (int)reader["PeriodInMonths"];
contrForm.noticePeriodInMonths = (int)reader["NoticePeriodInMonths"];
contrForm.price = (double)reader["Price"];
contractFormulaList.Add(contrForm);
}
reader.Close();
}
catch (SqlException ex)
{
Logging logger = new Logging("log.txt");
logger.WriteLine("ContractFormulaDB", ex.Message);
throw ex;
}
finally
{
/// connectie sluiten
conn.Close();
}
return(contractFormulaList);
}
/// <summary>
/// voegt een nieuw bedrijf toe
/// </summary>
/// <param name="comp"></param>
/// <returns> True of False (of het toevoegen al dan niet gelukt is) </returns>
public static Boolean AddCompany(Company comp)
{
SqlConnection conn = new SqlConnection(SamenSterkerDB.GetConnection());
String insertStatement = "INSERT INTO company(Name, Street, Zipcode, City, Country, Email, Phone) " +
"VALUES (@name, @street, @zipcode, @city, @country, @email, @phone)";
SqlCommand insertCommand = new SqlCommand(insertStatement, conn);
insertCommand.Parameters.AddWithValue("@name", comp.name);
insertCommand.Parameters.AddWithValue("@street", comp.street);
insertCommand.Parameters.AddWithValue("@zipcode", comp.zipcode);
insertCommand.Parameters.AddWithValue("@city", comp.city);
insertCommand.Parameters.AddWithValue("@country", comp.country);
insertCommand.Parameters.AddWithValue("@email", comp.email);
insertCommand.Parameters.AddWithValue("@phone", comp.phone);
try
{
conn.Open();
int success;
success = insertCommand.ExecuteNonQuery();
if (success == 1)
{
return(true);
}
return(false);
}
catch (SqlException ex)
{
Logging logger = new Logging("log.txt");
logger.WriteLine("CompanyDB", ex.Message);
throw;
}
finally
{
conn.Close();
}
}
