public List <IVacancy> VacancyWithNULLRecruitmentRequestID() //
{ //used for creating RecruitmentRequests
//ADO.NET program for searching vacancies with recruitmentRequestID as null
SqlConnection conn = DBUtility.GetConnection();
vacancyList.Clear();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_vacancyWithNULLRecruitmentRequestID_667015";
//cmd.Parameters.AddWithValue("@recruitmentRequestID", recruitmentRequestID);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int vacancyID = reader.GetInt32(0);
int noOfPositions = reader.GetInt32(1);
string skills = reader.GetString(2);
string domain = reader.GetString(3);
int experience = reader.GetInt32(4);
DateTime requiredDate = reader.GetDateTime(5);
string location = reader.GetString(6);
int status = reader.GetInt32(7);
//retrieving all vacancies associated to the recruitmentrequestID given
vacancy = VacancyFactory.CreateVacancy(vacancyID, noOfPositions, skills, domain, experience, requiredDate, location, status);
vacancyList.Add(vacancy);
}
conn.Close();
return(vacancyList);
}
//FUNCTION FOR DISPLAYING VACANCY
public List <IVacancy> SelectVacancy(int employeeID)
{
SqlConnection conn = DBUtility.GetConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_selectVacancy_667015";
cmd.Parameters.AddWithValue("@employeeId", employeeID);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int vacancyID = reader.GetInt32(0);
int noOfPositions = reader.GetInt32(1);
string skills = reader.GetString(2);
string domain = reader.GetString(3);
int experience = reader.GetInt32(4);
DateTime requiredDate = reader.GetDateTime(5);
string location = reader.GetString(6);
int status = reader.GetInt32(7);
//retirving all vacancies associated to the recruitmentrequestID given
vacancy = VacancyFactory.CreateVacancy(vacancyID, noOfPositions, skills, domain, experience, requiredDate, location, status);
vacancyList.Add(vacancy);
}
conn.Close();
return(vacancyList);
}
public List <IVacancy> SearchRecruitmentRequest(int recruitmentRequestID)
{ //used for displays the vacancies wrt to given recruitmentRequest
//ADO.NET program for searching recruitmentRequest
//SqlConnection myConnection = DBUtility.getConnection();
//using (myConnection)
//{
// SqlCommand myCommand = new SqlCommand("sp_searchRecruitmentRequest_667961", myConnection);
// myCommand.CommandType = CommandType.StoredProcedure;
// myCommand.Parameters.AddWithValue("@RecruitmentRequestID", recruitmentRequestID);
// myConnection.Open();
// myCommand.ExecuteNonQuery();
// myConnection.Close();
//}
//ADO.NET program for searching recruitmentrequest
SqlConnection conn = DBUtility.GetConnection();
vacancyList.Clear();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_searchRecruitmentRequest_667015";
cmd.Parameters.AddWithValue("@recruitmentRequestID", recruitmentRequestID);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int vacancyID = reader.GetInt32(0);
int noOfPositions = reader.GetInt32(1);
string skills = reader.GetString(2);
string domain = reader.GetString(3);
int experience = reader.GetInt32(4);
DateTime requiredDate = reader.GetDateTime(5);
string location = reader.GetString(6);
int status = reader.GetInt32(7);
//retirving all vacancies associated to the recruitmentrequestID given
vacancy = VacancyFactory.CreateVacancy(vacancyID, noOfPositions, skills, domain, experience, requiredDate, location, status);
vacancyList.Add(vacancy);
}
conn.Close();
return(vacancyList);
}
// To retrieve all the vacancies
public List <IVacancy> GetVacancy()
{
List <IVacancy> lstVacancy = new List <IVacancy>();
SqlCommand objMyCommand;
SqlConnection objMyConnection = DBUtility.getConnection();
try
{
using (objMyConnection)
{
objMyCommand = new SqlCommand("sp_getVacancy", objMyConnection);
objMyCommand.CommandType = CommandType.StoredProcedure;
objMyConnection.Open();
SqlDataReader objSqlDataReader = objMyCommand.ExecuteReader();
while (objSqlDataReader.Read())
{
int VacancyID = Int32.Parse(objSqlDataReader["VacancyID"].ToString());
int NoOfPositions = Int32.Parse(objSqlDataReader["NoOfPositions"].ToString());
String Skills = objSqlDataReader["Skills"].ToString();
int Experience = Int32.Parse(objSqlDataReader["Experience"].ToString());
String Location = objSqlDataReader["Location"].ToString();
String BusinessDomain = objSqlDataReader["BusinessDomain"].ToString();
DateTime RequiredByDate = DateTime.Parse(objSqlDataReader["RequiredByDate"].ToString());
int Status = Int32.Parse(objSqlDataReader["Status"].ToString());
int VacancyRequestID = Int32.Parse(objSqlDataReader["VacancyRequestID"].ToString());
int RecruitmentRequestID = Int32.Parse(objSqlDataReader["RecruitmentRequestID"].ToString());
IVacancy v = VacancyFactory.Create_Vacancy(VacancyID, NoOfPositions, Skills, Experience, Location, BusinessDomain, RequiredByDate, Status, VacancyRequestID, RecruitmentRequestID);
lstVacancy.Add(v);
}
objSqlDataReader.Close();
}
return(lstVacancy);
}
catch (Exception EE)
{
Console.WriteLine(EE);
Console.Read();
return(null);
}
finally
{
objMyConnection.Close();
}
}
//FUNCTION FOR DISPALYING UNAPPROVED VACANCY
public List <IVacancy> ViewUnapprovedVacancies(int employeeID)
{
vacancyList.Clear();
//ADO.NET program for displaying unapproved vacancies
SqlConnection myConnection = DBUtility.GetConnection(); //make the connection object
SqlCommand myCommand = new SqlCommand(); //make sqlcommand object
myCommand.Connection = myConnection; //give connection with the command object
myCommand.CommandType = CommandType.StoredProcedure; //bind with CommandType
myCommand.CommandText = "sp_viewUnapprovedVacancies_545444"; //bind with CommandText
myCommand.Parameters.AddWithValue("@employeeId", employeeID);
myConnection.Open(); //Open the connection
DataTable unapprovedTable = new DataTable(); //make DataTable
SqlDataReader unapprovedDataReader = myCommand.ExecuteReader(); //Make DataReader
unapprovedTable.Load(unapprovedDataReader); //Put all values from DataReader to DataTable
for (int index = 0; index < unapprovedTable.Rows.Count; index++) //Use for loop to count the rows of the table
{
int vacancyID = (int)unapprovedTable.Rows[index][0]; //get the value from the 1st column of the table
int noOfPositions = (int)unapprovedTable.Rows[index][1]; //get the value from the 2nd column of the table
string skills = (string)unapprovedTable.Rows[index][2]; //get the value from the 3rd column of the table
string domain = (string)unapprovedTable.Rows[index][3];
int experience = (int)unapprovedTable.Rows[index][4]; //get the value from the 4th column of the table
DateTime requiredDate = (DateTime)unapprovedTable.Rows[index][5]; //get the value from the 5th column of the table
string location = (string)unapprovedTable.Rows[index][6];
int status = (int)unapprovedTable.Rows[index][7]; //get the value from the 6th column of the table
vacancy = VacancyFactory.CreateVacancy(vacancyID, noOfPositions, skills, domain, experience, requiredDate, location, status);
vacancyList.Add(vacancy);
}
myConnection.Close(); //Close the connection
return(vacancyList); //Return the Vacancy type list
}
