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); }