public static Professor Get(string nid)
{
Professor newProf = new Professor();
SqlConnection con = new System.Data.SqlClient.SqlConnection(Database.ConnectionString);
string query = @"SELECT Professor_ID, First_Name, Last_Name, Email, Emergency_Phone
FROM [dbo].[tlProfessors]
WHERE CBAUserName = '******'";
SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, con);
if (con.State == ConnectionState.Closed)
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
if (!rdr.HasRows)
return null;
rdr.Read();
newProf.id = Convert.ToInt32(rdr[0].ToString());
newProf.firstName = rdr[1].ToString();
newProf.lastName = rdr[2].ToString();
newProf.email = rdr[3].ToString();
newProf.phone = rdr[4].ToString();
con.Close();
return newProf;
}
public static List<Test> List(string nid)
{
List<Test> list = new List<Test>();
SqlConnection cn = new SqlConnection(Database.ConnectionString);
cn.Open();
SqlCommand cm = cn.CreateCommand();
cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = "GetAvailableTestList";
cm.Parameters.AddWithValue("@NID", nid);
SqlDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
Test test = new Test();
test.Id = Convert.ToInt32(dr["Test_ID"]);
test.Name = dr["Test_Name"].ToString();
Course course = new Course();
course.Prefix = dr["Course_Prefix"].ToString();
course.Number = dr["Course_Number"].ToString();
course.Title = dr["Title"].ToString();
Professor professor = new Professor();
professor.FirstName = dr["First_Name"].ToString();
professor.LastName = dr["Last_Name"].ToString();
course.Professor = professor;
test.Course = course;
list.Add(test);
}
cn.Close();
return list;
}
public static List<Test> List(int? courseId = null, int? term = null, bool? active = null, bool? availableInRegionalCampus = null, string nid = null, DateTime? endDate = null)
{
List<Test> list = new List<Test>();
SqlConnection cn = new SqlConnection(Database.ConnectionString);
cn.Open();
SqlCommand cm = cn.CreateCommand();
cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = "GetTestList";
cm.Parameters.AddWithValue("@CourseId", courseId);
cm.Parameters.AddWithValue("@Term", term);
cm.Parameters.AddWithValue("@Active", active);
cm.Parameters.AddWithValue("@AvailableInRegionalCampus", availableInRegionalCampus);
cm.Parameters.AddWithValue("@NID", nid);
cm.Parameters.AddWithValue("@EndDate", endDate);
SqlDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
Test test = new Test();
test.Id = Convert.ToInt32(dr["Test_ID"]);
test.Name = dr["Test_Name"].ToString();
test.StartDate = Convert.ToDateTime(dr["Open_Date_Time"]);
test.EndDate = Convert.ToDateTime(dr["Close_Date_Time"]);
Course course = new Course();
course.Id = Convert.ToInt32(dr["Course_ID"]);
course.Prefix = dr["Course_Prefix"].ToString();
course.Number = dr["Course_Number"].ToString();
course.Title = dr["Title"].ToString();
Professor professor = new Professor();
professor.FirstName = dr["First_Name"].ToString();
professor.LastName = dr["Last_Name"].ToString();
course.Professor = professor;
test.Course = course;
list.Add(test);
}
cn.Close();
return list;
}
public static Test Get(int id)
{
SqlConnection cn = new SqlConnection(Database.ConnectionString);
cn.Open();
SqlCommand cm = cn.CreateCommand();
cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = "GetTest";
cm.Parameters.AddWithValue("@Id", id);
SqlDataReader dr = cm.ExecuteReader();
dr.Read();
Test test = new Test();
test.Id = Convert.ToInt32(dr["Test_ID"]);
test.Name = dr["Test_Name"].ToString();
test.StartDate = Convert.ToDateTime(dr["Open_Date_Time"]);
test.EndDate = Convert.ToDateTime(dr["Close_Date_Time"]);
Course course = new Course();
course.Id = Convert.ToInt32(dr["Course_ID"]);
course.Prefix = dr["Course_Prefix"].ToString();
course.Number = dr["Course_Number"].ToString();
course.Title = dr["Title"].ToString();
Professor professor = new Professor();
professor.FirstName = dr["First_Name"].ToString();
professor.LastName = dr["Last_Name"].ToString();
course.Professor = professor;
test.Course = course;
cn.Close();
return test;
}