public static CourseCode GetNewestCourseCodeById(int id)
{
CourseCode courseCode = null;
using (var connection = DbConn.GetConnection())
{
connection.Open();
SqlCommand selectCommand = new SqlCommand("mcftacademics.dbo.SelectNewestCourseCodeById", connection);
selectCommand.CommandType = System.Data.CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@id", id);
//execute the sql statement
SqlDataReader reader = selectCommand.ExecuteReader();
//loop through the resultset
if (reader.Read())
{
courseCode = CourseCodeFromRow(reader);
}
}
return(courseCode);//return the coursecode
}
// moved from StudentDAL
internal static List <Grade> GetGradesForStudent(Student student)
{
List <Grade> grades = new List <Grade>();
Grade grade = null;
using (var connection = DbConn.GetConnection())
{
connection.Open();
SqlCommand selectCommand = new SqlCommand("mcftacademics.dbo.SelectStudentGradeById2", connection);
selectCommand.CommandType = System.Data.CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@studentId", student.Id);
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
grade = GradeDAL.GradeFromRow(reader);
grades.Add(grade);
}
}
return(grades);
}
public static Grade GetSummerPracticum(Student s)
{
Grade grade = null;
using (var connection = DbConn.GetConnection())
{
connection.Open();
SqlCommand selectCommand = new SqlCommand("mcftacademics.dbo.SelectSummerPracticumByStudentId", connection);
selectCommand.CommandType = System.Data.CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@studentId", s.Id);
//execute the sql statement
SqlDataReader reader = selectCommand.ExecuteReader();
//loop through the resultset
if (reader.Read())
{
grade = GradeDAL.GradeFromRow(reader);
}
}
return(grade);//return the grade
}
public static List <string> SearchCourseCodes(string code)
{
List <string> courseCodes = new List <string>();
using (var connection = DbConn.GetConnection())
{
connection.Open();
SqlCommand selectCommand = new SqlCommand("mcftacademics.dbo.SearchCourseCodes", connection);
selectCommand.CommandType = System.Data.CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@code", code);
//execute the sql statement
SqlDataReader reader = selectCommand.ExecuteReader();
//loop through the resultset
while (reader.Read())
{
courseCodes.Add(reader["courseCode"].ToString());
}
}
return(courseCodes);//return the list of coursecodes
}
public static IEnumerable <Role> GetRolesForUser(User user)
{
var roles = new List <Role>();
using (var connection = DbConn.GetConnection())
{
connection.Open();
var command = connection.CreateCommand();
// without _ById, this accepts Username instead
command.CommandText = "mcftacademics.dbo.Get_UserRoles_ById";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@userIdentity", user.Id);
var reader = command.ExecuteReader();
while (reader.Read())
{
roles.Add(RoleFromRow(reader, user));
}
}
return(roles);
}
public static Role Grant(Role role)
{
using (var connection = DbConn.GetConnection())
{
connection.Open();
var sql = "[mcftacademics].dbo.[Grant_UserRole]";
var query = connection.CreateCommand();
query.CommandType = CommandType.StoredProcedure;
query.CommandText = sql;
query.Parameters.AddWithValue("@userIdentity", role.User.Id);
query.Parameters.AddWithValue("@roleName", role.Name);
// depends on set nocount off being in the procedure
var reader = query.ExecuteReader();
if (!reader.Read())
{
return(null);
}
return(RoleFromRow(reader, role.User));
}
}
public static List <Course> GetCoursesByInstructor(int userid)
{
List <Course> courses = new List <Course>();
using (var connection = DbConn.GetConnection())
{
connection.Open();
SqlCommand selectCommand = new SqlCommand("mcftacademics.dbo.SelectCoursesByInstructorNoCode", connection);
selectCommand.CommandType = System.Data.CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@userid", userid);
//execute the sql statement
SqlDataReader reader = selectCommand.ExecuteReader();
//loop through the resultset
while (reader.Read())
{
Course c = CourseFromRow(reader);
courses.Add(c);
}
}
return(courses);//return the list of courses
}
public static List <Student> GetStudentsInCourse(Course course)
{
List <Student> students = new List <Student>();
using (var connection = DbConn.GetConnection())
{
connection.Open();
SqlCommand selectCommand = new SqlCommand("mcftacademics.dbo.SelectStudentsByCourseId", connection);
selectCommand.CommandType = System.Data.CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@courseId", course.Id);
//execute the sql statement
SqlDataReader reader = selectCommand.ExecuteReader();
//loop through the resultset
while (reader.Read())
{
Student s = StudentFromRow(reader);
students.Add(s);
}
}
return(students);//return the list of students
}
public static List <Student> GetAllStudents()
{
List <Student> students = new List <Student>();
using (var connection = DbConn.GetConnection())
{
connection.Open();
SqlCommand selectCommand = new SqlCommand("mcftacademics.dbo.SelectAllStudents", connection);
selectCommand.CommandType = System.Data.CommandType.StoredProcedure;
//execute the sql statement
SqlDataReader reader = selectCommand.ExecuteReader();
//loop through the resultset
while (reader.Read())
{
Student s = StudentFromRow(reader);
students.Add(s);
}
return(students);//return the list of students
}
return(null);
}
internal static Grade GetGradesForStudentInCourse(Course course, Student student)
{
Grade grade = null;
using (var connection = DbConn.GetConnection())
{
connection.Open();
SqlCommand selectCommand = new SqlCommand("mcftacademics.dbo.SelectGradeByCourseAndStudent", connection);
selectCommand.CommandType = System.Data.CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@courseId", course.Id);
selectCommand.Parameters.AddWithValue("@studentId", student.Id);
//execute the sql statement
SqlDataReader reader = selectCommand.ExecuteReader();
//loop through the resultset
if (reader.Read())
{
grade = GradeDAL.GradeFromRow(reader, course);
}
}
return(grade);//return the grade
}
public static bool ToggleGradeLock(int studentId, int courseId, DateTime?unlockedUntil)
{
SqlConnection conn = DbConn.GetConnection();
if (unlockedUntil == null)
{
unlockedUntil = DateTime.Now;
}
bool result;
try
{
conn.Open();
SqlCommand updateCommand = new SqlCommand("mcftacademics.dbo.ToggleGradeLock", conn);
updateCommand.CommandType = System.Data.CommandType.StoredProcedure;
updateCommand.Parameters.AddWithValue("@studentId", studentId);
updateCommand.Parameters.AddWithValue("@courseId", courseId);
updateCommand.Parameters.AddWithValue("@unlockUntil", unlockedUntil);
int rows = updateCommand.ExecuteNonQuery();
if (rows > 0)
{
result = true;
}
else
{
result = false;
}
}
catch (Exception ex)
{
result = false;
}
finally
{
conn.Close();
}
return(result);
}
public static bool UpdateFormula(string formula)
{
SqlConnection conn = DbConn.GetConnection();
bool result;
using (var connection = DbConn.GetConnection())
{
conn.Open();
SqlCommand updateCommand = new SqlCommand("mcftacademics.dbo.UpdateFormula", conn);
updateCommand.CommandType = System.Data.CommandType.StoredProcedure;
updateCommand.Parameters.AddWithValue("@formula", formula);
int rows = updateCommand.ExecuteNonQuery();
if (rows > 0)
{
result = true;
}
else
{
result = false;
}
}
return(result);
}
// Similar to Update with args here...
public static int AddCourse(Course c, Staff leadStaff, Staff supportStaff, IEnumerable <Prerequisite> prerequisites)
{
int id;
using (var connection = DbConn.GetConnection())
{
connection.Open();
var transaction = connection.BeginTransaction("AddCourse");
try
{
SqlCommand insertCommand = new SqlCommand("mcftacademics.dbo.InsertCourse", connection);
insertCommand.Transaction = transaction;
insertCommand.CommandType = System.Data.CommandType.StoredProcedure;
insertCommand.Parameters.AddWithValue("@name", c.Name);
insertCommand.Parameters.AddWithValue("@credit", c.Credit);
insertCommand.Parameters.AddWithValue("@description", c.Description);
insertCommand.Parameters.AddWithValue("@lectureHours", c.LectureHours);
insertCommand.Parameters.AddWithValue("@labHours", c.LabHours);
insertCommand.Parameters.AddWithValue("@examHours", c.ExamHours);
insertCommand.Parameters.AddWithValue("@revisionNumber", c.RevisionNumber);
insertCommand.Parameters.AddWithValue("@program", c.Program);
insertCommand.Parameters.AddWithValue("@accreditation", c.Accreditation);
int rows = insertCommand.ExecuteNonQuery();
if (rows > 0)
{
SqlCommand selectCommand = new SqlCommand("mcftacademics.dbo.SelectLastCourseInsert", connection);
selectCommand.Transaction = transaction;
id = Convert.ToInt32(selectCommand.ExecuteScalar());
//Drop existing staff (so they're not multiple instructors)
//and add back the lead staff and support if there is one
StaffDAL.DropStaff(connection, id, transaction);
StaffDAL.AddStaff(connection, id, leadStaff, transaction);
if (supportStaff != null)
{
StaffDAL.AddStaff(connection, id, supportStaff, transaction);
}
PrerequisiteDAL.DropPrereqs(connection, id, transaction);
foreach (Prerequisite prereq in prerequisites)
{
prereq.CourseId = id;
PrerequisiteDAL.AddPrereq(connection, prereq, transaction);
}
transaction.Commit();
}
else
{
throw new Exception();
}
}
catch (Exception)
{
try
{
transaction.Rollback();
}
catch
{
// if THAT fails
throw;
}
throw;
}
}
return(id);
}
