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