public List <Course> GetCoursesRequiredByDegree(int degreeId) { string command; if (degreeId > 0) { command = CourseSelect() + $@" INNER JOIN dbo.tblDegreeRequirement drq ON crs.crs_id = drq.crs_id WHERE drq.deg_id = '{degreeId}' "; } else { command = CourseSelect() + $@" INNER JOIN dbo.tblDegreeRequirement drq ON crs.crs_id = drq.crs_id "; } DatabaseService service = new DatabaseService(); SqlDataReader reader = service.Read(command); List <Course> courses = CourseListRead(reader); service.Close(); return(courses); }
public bool Delete(Account account) { string command = $@" DELETE gpf FROM dbo.tblGPFSession gpf WHERE gpf.acc_id = '{account.Id}' DELETE coh FROM dbo.tblCourseHistory coh WHERE coh.acc_id = '{account.Id}' DELETE acc FROM dbo.tblAccount acc WHERE acc.acc_id = '{account.Id}' "; DatabaseService service = new DatabaseService(); int rowsAffected = service.Write(command); service.Close(); if (rowsAffected > 0) { return(true); } else { return(false); } }
public bool AddCourseToHistory(Account account, int courseId) { string command = $@" INSERT INTO dbo.tblCourseHistory ( acc_id, crs_id ) SELECT acc.acc_id, '{courseId}' FROM dbo.tblAccount acc WHERE acc.acc_id = '{account.Id}' "; DatabaseService service = new DatabaseService(); int rowsAffected = service.Write(command); service.Close(); if (rowsAffected > 0) { return(true); } else { return(false); } }
public List <Course> GetCoursesByConcentration(int concentrationId) { string command; if (concentrationId > 0) { command = CourseSelect() + $@" INNER JOIN dbo.tblConcentrationCourse ctc ON crs.crs_id = ctc.crs_id WHERE ctc.con_id = '{concentrationId}' "; } else { command = CourseSelect() + $@" INNER JOIN dbo.tblConcentrationCourse ctc ON crs.crs_id = ctc.crs_id "; } DatabaseService service = new DatabaseService(); SqlDataReader reader = service.Read(command); List <Course> courses = CourseListRead(reader); service.Close(); return(courses); }
public List <Course> GetCoursePrereqs(int courseId) { string command; if (courseId > 0) { command = CourseSelect() + $@" INNER JOIN dbo.tblCoursePrerequisite prq ON crs.crs_id = prq.prq_id WHERE prq.crs_id = {courseId} "; } else { command = CourseSelect() + $@" INNER JOIN dbo.tblCoursePrerequisite prq ON crs.crs_id = prq.prq_id "; } DatabaseService service = new DatabaseService(); SqlDataReader reader = service.Read(command); List <Course> courses = CourseListRead(reader); service.Close(); return(courses); }
public bool Register(Account account) { if (account == null || string.IsNullOrWhiteSpace(account.Username) || string.IsNullOrWhiteSpace(account.Password)) { return(false); } string command = $@" INSERT INTO dbo.tblAccount ( acc_username, acc_password, acc_first_name, acc_last_name ) SELECT '{account.Username}', '{account.Password}', '{account.FirstName}', '{account.LastName}' "; /** Fields can be updated from profile after registering. * acc_street, * acc_city, * acc_state, * acc_zip, * acc_phone, * acc_role, * deg_id, * con_id * '{account.Street}', * '{account.City}', * '{account.State}', * '{account.Zip}', * '{account.Phone}', * '{account.Role}', * '{account.Degree.Id}', * '{account.Concentration.Id}' */ DatabaseService service = new DatabaseService(); int rowsAffected = service.Write(command); service.Close(); if (rowsAffected > 0) { return(true); } else { return(false); } }
public List <Concentration> GetConcentrationsByDegreeId(int degreeId) { string command; if (degreeId > 0) { command = $@" SELECT con.con_id, con.con_title, con.deg_id FROM dbo.tblConcentration con WHERE con.deg_id = '{degreeId}' "; } else { command = $@" SELECT con.con_id, con.con_title, con.deg_id FROM dbo.tblConcentration con "; } DatabaseService service = new DatabaseService(); SqlDataReader reader = service.Read(command); List <Concentration> concentrations = new List <Concentration>(); if (reader != null && reader.HasRows) { while (reader.Read()) { Concentration concentration = new Concentration(); concentration.Id = (int)reader["con_id"]; if (reader["con_title"] != DBNull.Value) { concentration.Title = (string)reader["con_title"]; } if (reader["deg_id"] != DBNull.Value) { concentration.DegreeId = (int)reader["deg_id"]; } concentrations.Add(concentration); } } service.Close(); return(concentrations); }
public List <Course> GetCourses() { string command = CourseSelect(); DatabaseService service = new DatabaseService(); SqlDataReader reader = service.Read(command); List <Course> courses = CourseListRead(reader); service.Close(); return(courses); }
public List <GPFSession> GetSessions() { string command = GPFSessionSelect(); DatabaseService service = new DatabaseService(); SqlDataReader reader = service.Read(command); List <GPFSession> sessions = GPFSessionListRead(reader); service.Close(); return(sessions); }
public List <Account> GetAccounts() { string command = AccountSelect(); DatabaseService service = new DatabaseService(); SqlDataReader reader = service.Read(command); List <Account> accounts = AccountListRead(reader); service.Close(); return(accounts); }
public Account GetAccount(string username) { string command = AccountSelect() + $@" WHERE acc.acc_username = '******' "; DatabaseService service = new DatabaseService(); SqlDataReader reader = service.Read(command); Account account = AccountRead(reader); service.Close(); return(account); }
public Course GetCoursesById(int courseId) { string command = CourseSelect() + $@" WHERE crs.crs_id = '{courseId}' "; DatabaseService service = new DatabaseService(); SqlDataReader reader = service.Read(command); Course course = CourseRead(reader); service.Close(); return(course); }
public List <GPFSession> GetSessionsByAccountId(int accountId) { string command = GPFSessionSelect() + $@" WHERE gpf.acc_id = '{accountId}' "; DatabaseService service = new DatabaseService(); SqlDataReader reader = service.Read(command); List <GPFSession> sessions = GPFSessionListRead(reader); service.Close(); return(sessions); }
public List <Account> GetAccountsByRole(AccountRole role) { string command = AccountSelect() + $@" WHERE acc.acc_role = '{role.Value}' "; DatabaseService service = new DatabaseService(); SqlDataReader reader = service.Read(command); List <Account> accounts = AccountListRead(reader); service.Close(); return(accounts); }
public GPFSession GetSessionById(int sessionId) { string command = GPFSessionSelect() + $@" WHERE gpf.gpf_id = '{sessionId}' "; DatabaseService service = new DatabaseService(); SqlDataReader reader = service.Read(command); GPFSession session = GPFSessionRead(reader); service.Close(); return(session); }
public List <Course> GetAllElectivesByConcentration(int excludeConcentrationId) { string command = CourseSelect() + $@" INNER JOIN dbo.tblConcentrationCourse ctc ON crs.crs_id = ctc.crs_id WHERE ctc.con_id <> '{excludeConcentrationId}' "; DatabaseService service = new DatabaseService(); SqlDataReader reader = service.Read(command); List <Course> courses = CourseListRead(reader); service.Close(); return(courses); }
public List <Course> GetCourseHistory(int accountId) { string command = CourseSelect() + $@" INNER JOIN dbo.tblCourseHistory coh ON crs.crs_id = coh.crs_id WHERE coh.acc_id = '{accountId}' "; DatabaseService service = new DatabaseService(); SqlDataReader reader = service.Read(command); List <Course> courses = CourseListRead(reader); service.Close(); return(courses); }
public Degree GetDegreeById(int degreeId) { string command = DegreeSelect() + $@" WHERE deg.deg_id = '{degreeId}' "; DatabaseService service = new DatabaseService(); SqlDataReader reader = service.Read(command); Degree degree = DegreeRead(reader); if (degree != null) { degree.Concentrations = GetConcentrationsByDegreeId(degree.Id); } service.Close(); return(degree); }
public List <Degree> GetDegrees() { string command = DegreeSelect(); DatabaseService service = new DatabaseService(); SqlDataReader reader = service.Read(command); List <Degree> degrees = DegreeListRead(reader); if (degrees != null && degrees.Count > 0) { foreach (var degree in degrees) { degree.Concentrations = GetConcentrationsByDegreeId(degree.Id); } } service.Close(); return(degrees); }
public bool SaveSession(GPFSession session) { string command = $@" INSERT INTO dbo.tblGPFSession ( acc_id, deg_id, con_id, gpf_entering_year, gpf_entering_quarter, gpf_classes_per_quarter, gpf_class_delivery ) SELECT acc.acc_id, '{session.Degree.Id}', '{session.Concentration.Id}', '{session.EnteringTerm.Year}', '{session.EnteringTerm.Quarter.Value}', '{session.ClassesPerQuarter}', '{session.ClassDeliveryOption.Value}' FROM dbo.tblAccount acc WHERE acc.acc_id = '{session.Account.Id}' "; DatabaseService service = new DatabaseService(); int rowsAffected = service.Write(command); service.Close(); if (rowsAffected > 0) { return(true); } else { return(false); } }
public bool Login(string username, string password) { string command = AccountSelect() + $@" WHERE acc.acc_username = '******' AND acc.acc_password = '******' "; DatabaseService service = new DatabaseService(); SqlDataReader reader = service.Read(command); Account account = AccountRead(reader); service.Close(); if (account != null && account.Id > 0) { return(true); } else { return(false); } }
public bool RemoveCourseFromHistory(Account account, int courseId) { string command = $@" DELETE coh FROM dbo.tblCourseHistory coh WHERE coh.acc_id = '{account.Id}' AND coh.crs_id = '{courseId}' "; DatabaseService service = new DatabaseService(); int rowsAffected = service.Write(command); service.Close(); if (rowsAffected > 0) { return(true); } else { return(false); } }
public bool Update(Account account) { if (account == null) { return(false); } string command = $@" UPDATE acc SET "; if (!String.IsNullOrWhiteSpace(account.FirstName)) { command += $" acc.acc_first_name = '{account.FirstName}',"; } if (!String.IsNullOrWhiteSpace(account.LastName)) { command += $" acc.acc_last_name = '{account.LastName}',"; } if (!String.IsNullOrWhiteSpace(account.Street)) { command += $" acc.acc_street = '{account.Street}',"; } if (!String.IsNullOrWhiteSpace(account.City)) { command += $" acc.acc_city = '{account.City}',"; } if (!String.IsNullOrWhiteSpace(account.State)) { command += $" acc.acc_state = '{account.State}',"; } if (!String.IsNullOrWhiteSpace(account.Zip)) { command += $" acc.acc_zip = '{account.Zip}',"; } if (account.Role != null) { command += $" acc.acc_role = '{account.Role.Value}',"; } if (account.Degree != null) { command += $" acc.deg_id = '{account.Degree.Id}',"; } if (account.Concentration != null) { command += $" acc.con_id = '{account.Concentration.Id}'"; } command += $@" FROM dbo.tblAccount acc WHERE acc.acc_id = '{account.Id}' "; /** * acc.acc_phone = '{account.Phone}', * acc.acc_role = '{account.Role.Value}', */ DatabaseService service = new DatabaseService(); int rowsAffected = service.Write(command); service.Close(); if (rowsAffected > 0) { return(true); } else { return(false); } }