public List <Interview> fetchGridview()
{
List <Interview> interviews = new List <Interview>();
SqlConnection myConn = new SqlConnection(DBConnect);
StringBuilder tripCommand = new StringBuilder();
tripCommand.AppendLine("Select * from Interview i");
tripCommand.AppendLine("INNER JOIN Student s on s.AdminNo = i.AdminNo");
tripCommand.AppendLine("WHERE StudentStatus = 'Interview'");
SqlDataAdapter da = new SqlDataAdapter(tripCommand.ToString(), myConn);
DataSet ds = new DataSet();
da.Fill(ds, "interviewTable");
int rec_cnt = ds.Tables["interviewTable"].Rows.Count;
if (rec_cnt > 0)
{
foreach (DataRow row in ds.Tables["interviewTable"].Rows)
{
Interview myInt = new Interview();
myInt.interviewId = Convert.ToInt32(row["InterviewId"]);
myInt.studentAdminNo = row["AdminNo"].ToString();
myInt.studentName = row["StudentName"].ToString();
myInt.remarks = row["remarks"].ToString();
interviews.Add(myInt);
}
}
else
{
interviews = null;
}
return(interviews);
}
public List <Interview> retrieveInterview()
{
List <Interview> interviews = new List <Interview>();
SqlDataAdapter da;
DataSet ds = new DataSet();
StringBuilder tripCommand = new StringBuilder();
tripCommand.AppendLine("Select * from Interview i");
tripCommand.AppendLine("INNER JOIN Trip t on t.TripId = i.TripId");
tripCommand.AppendLine("INNER JOIN Student s on s.AdminNo = i.AdminNo ");
tripCommand.AppendLine("WHERE StudentStatus = 'Pending'");
SqlConnection myConn = new SqlConnection(DBConnect);
da = new SqlDataAdapter(tripCommand.ToString(), myConn);
da.Fill(ds, "interviewTable");
int rec_cnt = ds.Tables["interviewTable"].Rows.Count;
if (rec_cnt > 0)
{
foreach (DataRow row in ds.Tables["interviewTable"].Rows)
{
Interview myInt = new Interview();
myInt.studentName = row["StudentName"].ToString();
myInt.studentPic = row["ProfilePicture"].ToString();
myInt.tripName = row["TripTitle"].ToString();
myInt.tripEnd = Convert.ToDateTime(row["TripEnd"]);
myInt.tripStart = Convert.ToDateTime(row["TripStart"]);
myInt.tripLocation = row["Location"].ToString();
myInt.interviewId = Convert.ToInt32(row["InterviewId"]);
myInt.interviewDate = row["interviewDate"].ToString();
myInt.interviewTime = row["interviewTime"].ToString();
interviews.Add(myInt);
}
}
else
{
interviews = null;
}
return(interviews);
}
public List <Interview> fetchSession(string adminNo)
{
List <Interview> intSession = new List <Interview>();
SqlConnection myConn = new SqlConnection(DBConnect);
StringBuilder tripCommand = new StringBuilder();
tripCommand.AppendLine("Select * from Interview i");
tripCommand.AppendLine("INNER JOIN Staff s on s.staffId = i.staffId");
tripCommand.AppendLine("INNER JOIN Trip t on t.tripId = i.tripId");
tripCommand.AppendLine("WHERE AdminNo = @pAdminNo AND interviewSession IS NOT NULL");
SqlDataAdapter da = new SqlDataAdapter(tripCommand.ToString(), myConn);
da.SelectCommand.Parameters.AddWithValue("@pAdminNo", adminNo);
DataSet ds = new DataSet();
da.Fill(ds, "interviewTable");
int rec_cnt = ds.Tables["interviewTable"].Rows.Count;
if (rec_cnt > 0)
{
foreach (DataRow row in ds.Tables["interviewTable"].Rows)
{
Interview myInt = new Interview();
myInt.interviewSession = row["interviewSession"].ToString();
myInt.interviewToken = row["interviewToken"].ToString();
myInt.tripName = row["tripTitle"].ToString();
myInt.interviewDate = row["interviewDate"].ToString();
myInt.interviewTime = row["interviewTime"].ToString();
myInt.staffHonorifics = row["Honorifics"].ToString();
myInt.staffName = row["Name"].ToString();
intSession.Add(myInt);
}
}
else
{
intSession = null;
}
return(intSession);
}
public Interview fetchSingleInterview(int intId)
{
Interview interview = new Interview();
SqlConnection myConn = new SqlConnection(DBConnect);
StringBuilder tripCommand = new StringBuilder();
tripCommand.AppendLine("Select * from Interview i");
tripCommand.AppendLine("INNER JOIN Staff s on s.staffId = i.staffId");
tripCommand.AppendLine("INNER JOIN Trip t on t.tripId = i.tripId");
tripCommand.AppendLine("WHERE interviewId = @pIntId");
SqlDataAdapter da = new SqlDataAdapter(tripCommand.ToString(), myConn);
da.SelectCommand.Parameters.AddWithValue("@pIntId", intId);
DataSet ds = new DataSet();
da.Fill(ds, "interviewTable");
int rec_cnt = ds.Tables["interviewTable"].Rows.Count;
if (rec_cnt > 0)
{
DataRow row = ds.Tables["interviewTable"].Rows[0];
interview.interviewSession = row["interviewSession"].ToString();
interview.interviewToken = row["interviewToken"].ToString();
interview.tripName = row["tripTitle"].ToString();
interview.interviewDate = row["interviewDate"].ToString();
interview.interviewTime = row["interviewTime"].ToString();
interview.staffHonorifics = row["Honorifics"].ToString();
interview.staffName = row["Name"].ToString();
interview.tripid = Convert.ToInt32(row["tripId"]);
}
else
{
interview = null;
}
return(interview);
}