/// <summary>
/// Returns a list of all JobPost from the database
/// </summary>
/// <returns>a jobPostList</returns>
public List <JobPost> GetAll()
{
List <JobPost> jobPostList = new List <JobPost>();
using (SqlConnection connection = conn.OpenConnection())
{
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM JobPost";
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
DbWorkHour dbWorkHour = new DbWorkHour();
DbCompany dbCompany = new DbCompany();
DbJobCategory dbJobCategory = new DbJobCategory();
JobPost jobPost = new JobPost
{
Id = (int)reader["Id"],
Title = (string)reader["Title"],
Description = (string)reader["Description"],
StartDate = (DateTime)reader["StartDate"],
EndDate = (DateTime)reader["EndDate"],
JobTitle = (string)reader["JobTitle"],
workHours = dbWorkHour.Get((int)reader["WorkHoursId"]),
Address = (string)reader["Address"],
company = dbCompany.Get((int)reader["CompanyId"]),
jobCategory = dbJobCategory.Get((int)reader["JobCategoryId"])
};
jobPostList.Add(jobPost);
}
}
}
return(jobPostList);
}
/// <summary>
/// Returns a JobPost Object from the database
/// </summary>
/// <param name="id"></param>
/// <returns>jobPost</returns>
public JobPost Get(int id)
{
DbWorkHour dbWorkHour = new DbWorkHour();
DbCompany dbCompany = new DbCompany();
DbJobCategory dbJobCategory = new DbJobCategory();
JobPost jobPost = new JobPost();
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM JobPost WHERE Id = @Id";
cmd.Parameters.AddWithValue("Id", id);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
jobPost.Id = (int)reader["Id"];
jobPost.Title = (string)reader["Title"];
jobPost.Description = (string)reader["Description"];
jobPost.StartDate = (DateTime)reader["StartDate"];
jobPost.EndDate = (DateTime)reader["EndDate"];
jobPost.JobTitle = (string)reader["JobTitle"];
jobPost.workHours = dbWorkHour.Get((int)reader["WorkHoursId"]);
jobPost.Address = (string)reader["Address"];
jobPost.company = dbCompany.Get((int)reader["CompanyId"]);
jobPost.jobCategory = dbJobCategory.Get((int)reader["JobCategoryId"]);
}
;
return(jobPost);
}
}
}
/// <summary>
/// Is the method to Get the applier from the Database.
/// This includes all variables on Applier - except password.
/// This also includes jobcategory for the specific Applier.
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Applier Get(int id)
{
using (SqlConnection connection = conn.OpenConnection())
{
Applier applier = new Applier();
using (SqlCommand cmd = connection.CreateCommand())
{
DBJobCV dbjobCV = new DBJobCV();
cmd.CommandText = "SELECT * FROM Applier WHERE Id = @id";
cmd.Parameters.AddWithValue("id", id);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
applier.Id = (int)reader["Id"];
applier.Email = (string)reader["Email"];
applier.Country = (string)reader["Country"];
applier.Description = (string)reader["Description"];
applier.BannerURL = (string)reader["BannerURL"];
applier.ImageURL = (string)reader["ImageURL"];
applier.MaxRadius = (int)reader["MaxRadius"];
applier.HomePage = (string)reader["HomePage"];
applier.FName = (string)reader["FName"];
applier.LName = (string)reader["LName"];
applier.Age = (int)reader["Age"];
applier.Status = (bool)reader["Status"];
applier.CurrentJob = (string)reader["CurrentJob"];
applier.Birthdate = (DateTime)reader["Birthdate"];
// applier.jobCV = dbjobCV.Get((int)reader["JobCVId"]);
}
//Closes the current reader for the applier.
reader.Close();
//Executes the JobCategory command for Applier.
cmd.CommandText = "SELECT * FROM ApplierJobCategory WHERE ApplierId = @ApplierId";
cmd.Parameters.AddWithValue("ApplierId", applier.Id);
SqlDataReader readerA = cmd.ExecuteReader();
List <JobCategory> jobcategoryList = new List <JobCategory>();
while (readerA.Read())
{
DbJobCategory dbJobCategory = new DbJobCategory();
JobCategory jobCategory = dbJobCategory.Get((int)readerA["JobcategoryId"]);
jobcategoryList.Add(jobCategory);
}
//Sets the JobCategoryList equal to Applier JobCategoryList.
applier.jobCategoryList = jobcategoryList;
return(applier);
}
}
}
/// <summary>
/// Returns the Applier's JobCategory list. This metod should only be called inside the Get(id) method. Unless other ussage is needed.
/// </summary>
/// <param name="applier"></param>
/// <returns></returns>
public Applier GetJobCategoryOnApplier(Applier applier)
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM ApplierJobCategory WHERE ApplierId = @ApplierId";
cmd.Parameters.AddWithValue("ApplierId", applier.Id);
List <JobCategory> jobcategoryList = new List <JobCategory>();
DbJobCategory dbJobCategory = new DbJobCategory();
try
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
JobCategory jobCategory = dbJobCategory.Get((int)reader["JobcategoryId"]);
jobcategoryList.Add(jobCategory);
}
}
}
//Sets the JobCategoryList equal to Applier JobCategoryList.
applier.JobCategoryList = jobcategoryList;
}
catch (SqlException e)
{
throw e;
}
return(applier);
}
}
}
