internal CourseClass GetCourseByName(string Name)
{
CourseClass cclass = new CourseClass();
cclass = dal.GetCourseByName(Name);
return(cclass);
}
internal CourseClass GetCourseByNumber(int number, string rubric)
{
CourseClass cclass = new CourseClass();
cclass = dal.GetCourseByNumber(number, rubric);
return(cclass);
}
internal CourseClass GetCourseByNumber(int number, string rubric)
{
CourseClass cclass = new CourseClass();
try
{
MyConnection.Open();
SqlCommand selectCommand = new SqlCommand("uspGetCourseByNumber", MyConnection);
selectCommand.CommandType = CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@ipvCourseNumber", number);
selectCommand.Parameters.AddWithValue("@ipvRubric", rubric);
SqlDataReader reader = selectCommand.ExecuteReader();
List <CourseClass> courseList = new List <CourseClass>();
if (reader.HasRows)
{
while (reader.Read())
{
CourseClass ccclass = new CourseClass();
ccclass.className = reader["coursename"].ToString();
ccclass.courseNumber = int.Parse(reader["coursenumber"].ToString());
ccclass.courseRubric = reader["rubric"].ToString();
ccclass.Grades = "Na";
ccclass.Semester = "Na";
ccclass.units = int.Parse(reader["units"].ToString());
ccclass.Year = 1975;
courseList.Add(ccclass);
}
if (courseList.Count == 1)
{
cclass = courseList[0];
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (MyConnection.State == ConnectionState.Open)
{
MyConnection.Close();
}
}
return(cclass);
}
/// <summary>
/// this module returns a list of foundation courses taking studentId and deptName as input
/// </summary>
/// <param name="stdId"></param>
/// <param name="deptName"></param>
/// <returns>returns a list of foundation courses added for student</returns>
internal List <CourseClass> GetFoundationCourses(int stdId, string deptName)
{
List <CourseClass> foundationCourses = new List <CourseClass>();
try
{
conn2.Open();
MyConnection.Open();
SqlCommand selectCommand = new SqlCommand("dbo.uspGetFoundationCourses", conn2);
selectCommand.CommandType = CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@ipvStudentId", stdId);
SqlDataReader reader = selectCommand.ExecuteReader();
DataTable table1 = new DataTable();
table1.Load(reader);
foreach (DataRow dr in table1.Rows)
{
CourseClass cClass = new CourseClass();
cClass.Semester = dr["Semester"].ToString();
cClass.courseNumber = int.Parse(dr["CourseNumber"].ToString());
cClass.courseRubric = dr["CourseRubric"].ToString();
cClass.className = dr["CourseName"].ToString();
cClass.Grades = dr["Grades"].ToString();
cClass.units = int.Parse(dr["Units"].ToString());
foundationCourses.Add(cClass);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn2.State == ConnectionState.Open)
{
conn2.Close();
}
if (MyConnection.State == ConnectionState.Open)
{
MyConnection.Close();
}
}
return(foundationCourses);
}
/// <summary>
/// Gets a list of all elective courses given deptName as input
/// </summary>
/// <param name="deptName"></param>
/// <returns>returns a list of all electives of a department</returns>
public List <CourseClass> GetAllElectiveCoursesByDept(string deptName)
{
List <CourseClass> electiveClasses = new List <CourseClass>();
try
{
SqlCommand myCommand = new SqlCommand();
string sql = null;
MyConnection.Open();
myCommand.Connection = MyConnection;
//sql = "SELECT Subject + Catalog + Long Title AS CourseName FROM [Sheet1$] where Subject="+"'"+deptName+"'";
sql = "SELECT rubric as [Subject],coursenumber as [Catalog],coursename as [Long Title],units as [Min Units] FROM courseCatalog where " + deptName + "='ELEC'";
myCommand.CommandText = sql;
SqlDataReader reader = myCommand.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
CourseClass cClass = new CourseClass();
cClass.courseRubric = reader["Subject"].ToString();
cClass.courseNumber = int.Parse(reader["Catalog"].ToString());
cClass.className = reader["Long Title"].ToString();
cClass.units = int.Parse(reader["Min Units"].ToString());
electiveClasses.Add(cClass);
}
}
MyConnection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (MyConnection.State == ConnectionState.Open)
{
MyConnection.Close();
}
}
return(electiveClasses);
}
/// <summary>
/// this module returns a list of elective courses taking studentId and deptName as input
/// </summary>
/// <param name="stdId"></param>
/// <param name="deptName"></param>
/// <returns>returns a list of elective courses added for student</returns>
internal List <CourseClass> GetElectiveCourses(int stdId, string deptName)
{
List <CourseClass> electiveCourses = new List <CourseClass>();
List <CourseClass> electiveCourses1 = new List <CourseClass>();
try
{
conn2.Open();
MyConnection.Open();
SqlCommand selectCommand = new SqlCommand("dbo.uspGetElectiveCourses", conn2);
selectCommand.CommandType = CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@ipvStudentId", stdId);
SqlDataReader reader = selectCommand.ExecuteReader();
//DataTable table1 = new DataTable();
//table1.Load(reader);
SqlCommand selectCommand2 = new SqlCommand("uspGetAllElectives", MyConnection);
selectCommand2.CommandType = CommandType.StoredProcedure;
selectCommand2.Parameters.AddWithValue("@ipvDeptName", deptName);
SqlDataReader reader2 = selectCommand2.ExecuteReader();
//DataTable table2 = new DataTable();
List <string> ccourseName = new List <string>();
//table2.Load(reader2);
//table1.Merge(table2);
if (reader.HasRows)
{
while (reader.Read())
{
CourseClass cClass = new CourseClass();
cClass.Semester = reader["Semester"].ToString();
cClass.courseNumber = int.Parse(reader["CourseNumber"].ToString());
cClass.courseRubric = reader["CourseRubric"].ToString();
cClass.className = reader["CourseName"].ToString();
cClass.Grades = reader["Grades"].ToString();
cClass.units = int.Parse(reader["Units"].ToString());
electiveCourses.Add(cClass);
ccourseName.Add(cClass.courseRubric + cClass.courseNumber);
}
}
if (reader2.HasRows)
{
while (reader2.Read())
{
bool isMatched = false;
CourseClass cClass = new CourseClass();
cClass.Semester = reader2["Semester"].ToString();
cClass.courseNumber = int.Parse(reader2["CourseNumber"].ToString());
cClass.courseRubric = reader2["CourseRubric"].ToString();
cClass.className = reader2["CourseName"].ToString();
cClass.Grades = reader2["Grades"].ToString();
cClass.units = int.Parse(reader2["Units"].ToString());
foreach (string str in ccourseName)
{
if (str == (cClass.courseRubric + cClass.courseNumber))
{
isMatched = true;
break;
}
}
if (!isMatched)
{
electiveCourses.Add(cClass);
}
}
}
//foreach (DataRow dr in table1.Rows)
//{
// CourseClass cClass = new CourseClass();
// cClass.Semester = dr["Semester"].ToString();
// cClass.courseNumber = int.Parse(dr["CourseNumber"].ToString());
// cClass.courseRubric = dr["CourseRubric"].ToString();
// cClass.className = dr["CourseName"].ToString();
// cClass.Grades = dr["Grades"].ToString();
// cClass.units = int.Parse(dr["Units"].ToString());
// electiveCourses.Add(cClass);
//}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn2.State == ConnectionState.Open)
{
conn2.Close();
}
if (MyConnection.State == ConnectionState.Open)
{
MyConnection.Close();
}
}
return(electiveCourses);
}