//method to retrieve all BoligID to show in the ComboBox of SagUI
//returns a List of BoligBLL
protected virtual List <SagBLL> FetchSag()
{
//INITIALIZE List OF BoligBLL boliger
List <SagBLL> sager = new List <SagBLL>();
using (var conn = new SqlConnection(s1.GetConnectionString()))
{
//SQL QUERY
string sqlCommand = "SELECT * FROM Sag ORDER BY SagsID";
//SQL COMMAND
SqlCommand cmd = new SqlCommand(sqlCommand, conn);
try
{
//OPEN CONNECTION
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
//BEGIN TRANSACTION
Transactions.BeginReadCommittedTransaction(conn);
//EXECUTE READER (QUERY)
using (SqlDataReader reader = cmd.ExecuteReader())
{
//RETRIEVE BoligBLL AND ADD IN boliger
while (reader.Read())
{
sager.Add(new SagBLL((int)reader["SagsID"], reader["Status"].ToString()));
}
//CLOSE READER
reader.Close();
}
//COMMIT OR ROLLBACK
if (!Transactions.Commit(conn))
{
Transactions.Rollback(conn);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
//CLOSE CONNECTION
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
//RETURN
return(sager);
}
//method to retrieve all EjendomsmæglerID to show in the ComboBox of SagUI
//returns a List og EjendomsmæglerBLL
public List <EjendomsmæglerBLL> FetchEjendomsmægler()
{
//INITIALIZE List OF EjendomsmæglerBLL ejendomsmæglere
List <EjendomsmæglerBLL> ejendomsmægler = new List <EjendomsmæglerBLL>();
using (var conn = new SqlConnection(s1.GetConnectionString()))
{
//SQL QUERY
string sqlCommand = "SELECT * FROM Ejendomsmægler";
//SQL COMMAND
SqlCommand cmd = new SqlCommand(sqlCommand, conn);
try
{
//OPEN CONNECTION
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
//BEGIN TRANSACTION
Transactions.BeginReadCommittedTransaction(conn);
//EXECUTE READER (QUERY)
using (SqlDataReader reader = cmd.ExecuteReader())
{
//RETRIEVE EjendomsmæglerBLL AND ADD IN ejendomsmægler
while (reader.Read())
{
ejendomsmægler.Add(new EjendomsmæglerBLL((int)reader["MæglerID"], reader["Fnavn"].ToString(), reader["Enavn"].ToString()));
}
//CLOSE READER
reader.Close();
}
//COMMIT OR ROLLBACK
if (!Transactions.Commit(conn))
{
Transactions.Rollback(conn);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
//CLOSE CONNECTION
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
//RETURN
return(ejendomsmægler);
}
public List <SælgerBLL> FetchSælger()
{
List <SælgerBLL> sælgere = new List <SælgerBLL>();
using (var conn = new SqlConnection(s1.GetConnectionString()))
{
string sqlCommand = "SELECT * FROM Sælger";
SqlCommand cmd = new SqlCommand(sqlCommand, conn);
try
{
//OPEN CONNECTION
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
//BEGIN TRANSACTION
Transactions.BeginReadCommittedTransaction(conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
sælgere.Add(new SælgerBLL((int)reader["SælgerID"], reader["Fnavn"].ToString(), reader["Enavn"].ToString()));
}
if (reader != null)
{
reader.Close();
}
}
if (!Transactions.Commit(conn))
{
Transactions.Rollback(conn);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
//CLOSE CONNECTION
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
//RETURN
return(sælgere);
}
//method to retrieve all BoligID to show in the ComboBox of SagUI
//returns a List of BoligBLL
protected override List <BoligBLL> FetchBolig()
{
//INITIALIZE List OF BoligBLL boliger
List <BoligBLL> boliger = new List <BoligBLL>();
using (var conn = new SqlConnection(s1.GetConnectionString()))
{
//SQL QUERY
string sqlCommand = "SELECT * " +
"FROM Bolig " +
"WHERE BoligID " +
"IN (" +
"SELECT Sag.BoligID " +
"FROM Sag " +
"WHERE Sag.Status = 'Åben'" +
") " +
"AND Bolig.Kvadratmeter >= 145";
//SQL COMMAND
SqlCommand cmd = new SqlCommand(sqlCommand, conn);
try
{
//OPEN CONNECTION
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
//BEGIN TRANSACTION
Transactions.BeginReadCommittedTransaction(conn);
//EXECUTE READER (QUERY)
using (SqlDataReader reader = cmd.ExecuteReader())
{
//RETRIEVE BoligBLL AND ADD IN boliger
while (reader.Read())
{
boliger.Add(new BoligBLL((int)reader["BoligID"],
reader["Vej"].ToString(),
(int)reader["Postnummer"],
reader["Type"].ToString(),
(int)reader["Værelser"],
(int)reader["Etager"],
(int)reader["Kvadratmeter"],
(int)reader["Udbudspris"],
(bool)reader["HaveFlag"],
(DateTime)reader["Bygningsår"],
(DateTime)reader["RenoveringsÅr"]));
}
//CLOSE READER
if (reader != null)
{
reader.Close();
}
}
//COMMIT OR ROLLBACK
if (!Transactions.Commit(conn))
{
Transactions.Rollback(conn);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
//CLOSE CONNECTION
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
//SORT
boliger.Sort();
//there is a very nice lambda function that can sort by two criteria
//boliger = boliger.OrderBy(x => x.Vej).ThenBy(x => x.Udbudspris).ToList();
//RETURN
return(boliger);
}
//Metode til at få alle KøberID vist i comboboksen i HandelUI
//Returnere en liste og KøberBLL
public List <KøberBLL> FetchKøber()
{
//Initialisere liste af KøberBLL købere
List <KøberBLL> køber = new List <KøberBLL>();
using (var conn = new SqlConnection(s1.GetConnectionString()))
{
//SQL QUERY
string sqlcommand = "SELECT * FROM Køber ORDER BY KøberID";
//SQL COMMAND
SqlCommand command = new SqlCommand(sqlcommand, conn);
try
{
//OPEN CONNECTION
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
//BEGIN TRANSACTION
Transactions.BeginReadCommittedTransaction(conn);
//EXECUTE READER (QUERY)
using (SqlDataReader reader = command.ExecuteReader())
{
//RETRIEVE KøberBLL AND ADD IN køber
while (reader.Read())
{
køber.Add(new KøberBLL((int)reader["KøberID"], reader["Fnavn"].ToString(), reader["Enavn"].ToString()));
}
//CLOSE READER
if (reader != null)
{
reader.Close();
}
}
//COMMIT OR ROLLBACK
if (!Transactions.Commit(conn))
{
Transactions.Rollback(conn);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
//CLOSE CONNECTION
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
//RETURN
return(køber);
}