public static bool AddCourseCode(int id, CourseCode c) { bool result; using (var connection = DbConn.GetConnection()) { connection.Open(); SqlCommand insertCommand = new SqlCommand("mcftacademics.dbo.AddCourseCode", connection); insertCommand.CommandType = System.Data.CommandType.StoredProcedure; insertCommand.Parameters.AddWithValue("@id", id); insertCommand.Parameters.AddWithValue("@code", c.Code); insertCommand.Parameters.AddWithValue("@startDate", c.From); insertCommand.Parameters.AddWithValue("@endDate", c.To); insertCommand.Parameters.AddWithValue("@semester", c.Semester); int rows = insertCommand.ExecuteNonQuery(); if (rows > 0) { result = true; } else { result = false; } } return(result); }
public static string GetClassRank(Student s) { SortedList <int, decimal> students = new SortedList <int, decimal>(); using (var connection = DbConn.GetConnection()) { connection.Open(); SqlCommand selectCommand = new SqlCommand("mcftacademics.dbo.SelectAllStudentsByClass", connection); selectCommand.CommandType = System.Data.CommandType.StoredProcedure; selectCommand.Parameters.AddWithValue("@program", s.Program); selectCommand.Parameters.AddWithValue("@graduationDate", s.GraduationDate); //execute the sql statement SqlDataReader reader = selectCommand.ExecuteReader(); //loop through the resultset while (reader.Read()) { Student student = StudentFromRow(reader); students.Add(student.Id, student.GetAverage()); } int rank = students.IndexOfKey(s.Id) + 1; int total = students.Count; return(rank + " of " + total); } return(null); }
public static Student GetStudentByStudentId(int id) { SqlConnection conn = DbConn.GetConnection(); try { conn.Open(); //open the connection SqlCommand selectCommand = new SqlCommand("mcftacademics.dbo.SelectStudentsByStudentId", conn); selectCommand.CommandType = System.Data.CommandType.StoredProcedure; selectCommand.Parameters.AddWithValue("@studentId", id); //execute the sql statement SqlDataReader reader = selectCommand.ExecuteReader(); //loop through the resultset while (reader.Read()) { Student s = StudentFromRow(reader); return(s); } } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(null);//if no student was found }
public static bool UpdateGrade(Grade grade, int studentId) { SqlConnection conn = DbConn.GetConnection(); bool result; try { conn.Open(); SqlCommand updateCommand = new SqlCommand("mcftacademics.dbo.UpdateGradeByStudentIdAndCourseId", conn); updateCommand.CommandType = System.Data.CommandType.StoredProcedure; updateCommand.Parameters.AddWithValue("@studentId", studentId); updateCommand.Parameters.AddWithValue("@courseId", grade.Subject.Id); updateCommand.Parameters.AddWithValue("@grade", grade.GradeAssigned); updateCommand.Parameters.AddWithValue("@comment", grade.Comment); updateCommand.Parameters.AddWithValue("@supplemental", grade.Supplemental); 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 AddTranscript(string path, Student student) { SqlConnection conn = DbConn.GetConnection(); bool result; try { conn.Open(); SqlCommand insertCommand = new SqlCommand("mcftacademics.dbo.InsertTranscript", conn); insertCommand.CommandType = System.Data.CommandType.StoredProcedure; insertCommand.Parameters.AddWithValue("@studentId", student.Id); insertCommand.Parameters.AddWithValue("@path", path); int rows = insertCommand.ExecuteNonQuery(); if (rows > 0) { result = true; } else { result = false; } } catch (Exception ex) { result = false; } finally { conn.Close(); } return(result); }
// After refactoring to not hold some stuff like staff inside of // itself, we're taking them as arguments (since you often need // to change the others too). We could also make it so setting them // to null doesn't change them as well. // XXX: Not sure this is the best solution. We'll see if it works though. public static bool UpdateCourse(Course c, Staff leadStaff, Staff supportStaff, IEnumerable <Prerequisite> prerequisites) { bool result = false; using (var connection = DbConn.GetConnection()) { connection.Open(); var transaction = connection.BeginTransaction("UpdateCourse for " + c.Id); try { SqlCommand updateCommand = new SqlCommand("mcftacademics.dbo.UpdateCourseById", connection); updateCommand.Transaction = transaction; updateCommand.CommandType = System.Data.CommandType.StoredProcedure; updateCommand.Parameters.AddWithValue("@id", c.Id); updateCommand.Parameters.AddWithValue("@name", c.Name); updateCommand.Parameters.AddWithValue("@credit", c.Credit); updateCommand.Parameters.AddWithValue("@description", c.Description); updateCommand.Parameters.AddWithValue("@lectureHours", c.LectureHours); updateCommand.Parameters.AddWithValue("@labHours", c.LabHours); updateCommand.Parameters.AddWithValue("@examHours", c.ExamHours); updateCommand.Parameters.AddWithValue("@revisionNumber", c.RevisionNumber); updateCommand.Parameters.AddWithValue("@program", c.Program); updateCommand.Parameters.AddWithValue("@accreditation", c.Accreditation); int rows = updateCommand.ExecuteNonQuery(); if (rows > 0) { //Drop existing staff (so they're not multiple instructors) //and add back the lead staff and support if there is one StaffDAL.DropStaff(connection, c.Id, transaction); StaffDAL.AddStaff(connection, c.Id, leadStaff, transaction); if (supportStaff != null) { StaffDAL.AddStaff(connection, c.Id, supportStaff, transaction); } PrerequisiteDAL.DropPrereqs(connection, c.Id, transaction); foreach (Prerequisite prereq in prerequisites) { PrerequisiteDAL.AddPrereq(connection, prereq, transaction); } transaction.Commit(); result = true; } } catch (Exception) { try { transaction.Rollback(); } catch { // if THAT fails throw; } throw; } } return(result); }
public static List <CourseCode> GetCourseCodes(int id) { using (var connection = DbConn.GetConnection()) { connection.Open(); return(GetCourseCodes(connection, id)); } }
public static bool AddPrereq(Prerequisite prereq) { using (var connection = DbConn.GetConnection()) { connection.Open(); return(AddPrereq(connection, prereq)); } }
public static bool DropPrereqs(int id) { using (var connection = DbConn.GetConnection()) { connection.Open(); return(DropPrereqs(connection, id)); } }
public static List <Prerequisite> GetPrereqs(int id) { using (var connection = DbConn.GetConnection()) { connection.Open(); return(GetPrereqs(connection, id)); } }
public static bool DropCourseCodes(int id) { using (var connection = DbConn.GetConnection()) { connection.Open(); SqlCommand deleteCommand = new SqlCommand("mcftacademics.dbo.DropAllCourseCodesById", connection); deleteCommand.CommandType = System.Data.CommandType.StoredProcedure; deleteCommand.Parameters.AddWithValue("@id", id); int rows = deleteCommand.ExecuteNonQuery(); return(rows > 0); } }
public static int GetIdByCourseCode(string code) { int id; using (var connection = DbConn.GetConnection()) { connection.Open(); SqlCommand selectCommand = new SqlCommand("mcftacademics.dbo.SelectIdByCourseCode", connection); selectCommand.CommandType = System.Data.CommandType.StoredProcedure; selectCommand.Parameters.AddWithValue("@code", code); //execute the sql statement id = Convert.ToInt32(selectCommand.ExecuteScalar()); } return(id); }
public static bool ChangePassword(User user, string newPasswordHashed) { using (var connection = DbConn.GetConnection()) { connection.Open(); var sql = "[mcftacademics].dbo.Update_Password"; var query = connection.CreateCommand(); query.CommandType = CommandType.StoredProcedure; query.CommandText = sql; query.Parameters.AddWithValue("@userIdentity", user.Id); query.Parameters.AddWithValue("@userPassword", newPasswordHashed); // depends on set nocount off being in the procedure return(query.ExecuteNonQuery() > 0); } }
public static IEnumerable <User> GetAllUsers() { using (var connection = DbConn.GetConnection()) { connection.Open(); var command = connection.CreateCommand(); command.CommandText = "mcftacademics.dbo.Get_All_Users"; command.CommandType = CommandType.StoredProcedure; var reader = command.ExecuteReader(); while (reader.Read()) { yield return(UserFromRow(reader)); } } }
public static bool Revoke(Role role) { using (var connection = DbConn.GetConnection()) { connection.Open(); var sql = "[mcftacademics].dbo.[Revoke_UserRole_ById]"; var query = connection.CreateCommand(); query.CommandType = CommandType.StoredProcedure; query.CommandText = sql; query.Parameters.AddWithValue("@userIdentity", role.User.Id); query.Parameters.AddWithValue("@roleIdentity", role.Id); // depends on set nocount off being in the procedure return(query.ExecuteNonQuery() > 0); } }
// XXX: This is probably very wrong. CourseStaff is halfway a // table for m:n relations, but can also represent temps... which // the schema can't allow for. As such, instructors can only be // represented by User since it's the only other PK usable, so... // (This should really be changed.) public static IEnumerable <Grade> GetGradesForInstructor(User staff) { using (var connection = DbConn.GetConnection()) { connection.Open(); var command = connection.CreateCommand(); command.CommandText = "mcftacademics.dbo.Get_Grades_ByStaff"; command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@staffId", staff.Id); var reader = command.ExecuteReader(); while (reader.Read()) { yield return(GradeFromRow(reader)); } } }
public static List <ReportColumn> SelectReportDataYearly(string program, int semester, int year, bool semesterReport) { SqlConnection conn = DbConn.GetConnection(); List <ReportColumn> reportData = new List <ReportColumn>(); try { conn.Open(); SqlCommand selectCommand = new SqlCommand("mcftacademics.dbo.SelectReportData2", conn); selectCommand.CommandType = System.Data.CommandType.StoredProcedure; selectCommand.Parameters.AddWithValue("@program", program); selectCommand.Parameters.AddWithValue("@semester", semester); selectCommand.Parameters.AddWithValue("@year", year); SqlDataReader reader = selectCommand.ExecuteReader(); //loop through the resultset while (reader.Read()) { var studentId = (int)reader["studentId"]; var firstName = (string)reader["firstName"]; var lastName = (string)reader["lastName"]; var grade = (decimal)reader["grade"]; var supplemental = (bool)reader["isSupplemental"]; var courseId = (int)reader["courseId"]; var name = (string)reader["name"]; var prog = (string)reader["program"]; var courseCode = (string)reader["courseCode"]; var startDate = (DateTime)reader["startDate"]; var endDate = (DateTime)reader["endDate"]; ReportColumn reportRecord = new ReportColumn(studentId, firstName, lastName, grade, supplemental, courseId, name, prog, courseCode, startDate, endDate, semester, semesterReport); reportData.Add(reportRecord); } return(reportData); } catch (Exception ex) { } finally { conn.Close(); } return(reportData); }
internal static string GetFormula() { string formula = ""; using (var connection = DbConn.GetConnection()) { connection.Open(); SqlCommand selectCommand = new SqlCommand("mcftacademics.dbo.SelectFormula", connection); selectCommand.CommandType = System.Data.CommandType.StoredProcedure; SqlDataReader reader = selectCommand.ExecuteReader(); if (reader.Read()) { formula = reader["Formula"].ToString(); } } return(formula); }
public static User GetUser(string username) { using (var connection = DbConn.GetConnection()) { connection.Open(); var command = connection.CreateCommand(); command.CommandText = "mcftacademics.dbo.Get_User_ByName"; command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@username", username); var reader = command.ExecuteReader(); if (!reader.Read()) { return(null); } return(UserFromRow(reader)); } }
internal static decimal GetAverageForStudentByYear(Student student, int year) { //Get all Grades for the Student, by program if no semester passed in, or by semester if it is IEnumerable <Grade> grades = student.GetGradesForYear(year); List <decimal> results = new List <decimal>(); decimal average = 0; using (var connection = DbConn.GetConnection()) { connection.Open(); SqlCommand selectCommand = new SqlCommand("mcftacademics.dbo.SelectFormula", connection); selectCommand.CommandType = System.Data.CommandType.StoredProcedure; SqlDataReader reader = selectCommand.ExecuteReader(); if (reader.Read()) { // Define the context of our expression ExpressionContext context = new ExpressionContext(); // Allow the expression to use all static public methods of System.Math context.Imports.AddType(typeof(Math)); //Get the total Course credit hours for use in the expression context.Variables["c"] = grades.Sum(gg => gg.Subject.Credit); foreach (Grade g in grades) { // Define an int variable if (g.Supplemental) { context.Variables["a"] = 60m; } else { context.Variables["a"] = g.GradeAssigned; } context.Variables["b"] = g.Subject.Credit; // Create a dynamic expression that evaluates to an Object IDynamicExpression eDynamic = context.CompileDynamic(reader["Formula"].ToString()); // Evaluate the expressions decimal result = (decimal)eDynamic.Evaluate(); results.Add(result); } average = results.Sum(); } } return(Math.Round(average, 2)); }
public static int AddStudent(Student s) { int id; using (var connection = DbConn.GetConnection()) { connection.Open(); var transaction = connection.BeginTransaction("AddStudent"); try { SqlCommand insertCommand = new SqlCommand("mcftacademics.dbo.InsertAndEnrollStudent", connection); insertCommand.Transaction = transaction; insertCommand.CommandType = System.Data.CommandType.StoredProcedure; insertCommand.Parameters.AddWithValue("@firstName", s.FirstName); insertCommand.Parameters.AddWithValue("@lastName", s.LastName); insertCommand.Parameters.AddWithValue("@studentCode", s.StudentCode); insertCommand.Parameters.AddWithValue("@program", s.Program); insertCommand.Parameters.AddWithValue("@gradDate", s.GraduationDate); insertCommand.Parameters.AddWithValue("@academicAccommodation", s.AcademicAccommodation); var reader = insertCommand.ExecuteReader(); if (!reader.Read()) { return(0); } id = Convert.ToInt32(reader["id"]); reader.Close(); transaction.Commit(); return(id); } catch (Exception ex) { try { transaction.Rollback(); } catch { // if THAT fails throw; } throw; } } return(id); }
public static List <int> GetGradeRanges() { using (var connection = DbConn.GetConnection()) { List <int> years = new List <int>(); connection.Open(); var command = connection.CreateCommand(); command.CommandText = "mcftacademics.dbo.SelectGradeRanges"; command.CommandType = CommandType.StoredProcedure; var reader = command.ExecuteReader(); while (reader.Read()) { years.Add(reader.GetInt32(0)); } years.Sort(); return(years); } }
public static Role GetRole(User user, int roleId) { using (var connection = DbConn.GetConnection()) { connection.Open(); var command = connection.CreateCommand(); command.CommandText = "mcftacademics.dbo.Get_UserRole_ById"; command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@userIdentity", user.Id); command.Parameters.AddWithValue("@roleIdentity", roleId); var reader = command.ExecuteReader(); if (!reader.Read()) { return(null); } return(RoleFromRow(reader, user)); } }
public static Student GetStudent(int id) { using (var connection = DbConn.GetConnection()) { connection.Open(); SqlCommand selectCommand = new SqlCommand("mcftacademics.dbo.[SelectStudentsByStudentId]", connection); selectCommand.CommandType = System.Data.CommandType.StoredProcedure; selectCommand.Parameters.AddWithValue("@studentId", id); //execute the sql statement SqlDataReader reader = selectCommand.ExecuteReader(); //loop through the resultset if (reader.Read()) { return(StudentFromRow(reader)); } } return(null); }
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 User CreateUser(User user) { using (var connection = DbConn.GetConnection()) { connection.Open(); var sql = "[mcftacademics].dbo.Create_User"; var query = connection.CreateCommand(); query.CommandType = CommandType.StoredProcedure; query.CommandText = sql; query.Parameters.AddWithValue("@userPassword", user.Password); query.Parameters.AddWithValue("@userRealName", user.Name); query.Parameters.AddWithValue("@userName", user.Username); var reader = query.ExecuteReader(); if (!reader.Read()) { return(null); } return(UserFromRow(reader)); } }
public static Course GetCourseById(int id) { Course course = null; using (var connection = DbConn.GetConnection()) { connection.Open(); SqlCommand selectCommand = new SqlCommand("mcftacademics.dbo.SelectCourseByid", 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()) { course = CourseFromRow(reader); } } return(course);//return the course }
public static List <Course> GetAllCourses() { List <Course> courses = new List <Course>(); using (var connection = DbConn.GetConnection()) { connection.Open(); SqlCommand selectCommand = new SqlCommand("mcftacademics.dbo.Get_AllCourses", connection); selectCommand.CommandType = System.Data.CommandType.StoredProcedure; //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 <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 }
// 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); }