public static void Insert( string testName, int sectionID, bool enabled) { try { SQL_Escape.Escape(ref testName); DAL_Connection.Connection.Open(); string sqlString = string.Format( "INSERT INTO tests VALUES ( " + "'{0}',{1},{2});", testName, sectionID, Converter.BoolToInt(enabled)); SqlCommand command = new SqlCommand(sqlString, DAL_Connection.Connection); command.ExecuteNonQuery(); } catch (Exception exception) { throw exception; } finally { DAL_Connection.Connection.Close(); } }
/// <summary> /// Inserts to the database /// </summary> /// <param name="dayID">day ID</param> /// <param name="startTime">start time in 24 hour format</param> /// <param name="endTime">end time in 24 hour format</param> /// <param name="sectionID">section ID</param> /// <param name="classRoomNumber">class room number</param> public static void Insert( int dayID, decimal startTime, decimal endTime, int sectionID, string classRoomNumber) { try { SQL_Escape.Escape(ref classRoomNumber); DAL_Connection.Connection.Open(); string sqlString = string.Format( "INSERT INTO class_time VALUES " + "({0},{1},{2},{3},'{4}');", dayID, startTime, endTime, sectionID, classRoomNumber); SqlCommand command = new SqlCommand(sqlString, DAL_Connection.Connection); command.ExecuteNonQuery(); } catch (Exception exception) { throw exception; } finally { DAL_Connection.Connection.Close(); } }
/// <summary> /// Inserts data into database /// </summary> /// <param name="courseID">course number</param> /// <param name="courseName">course name</param> /// <param name="courseDescription">description</param> /// <param name="cost">cost</param> public static void Insert( string courseID, string courseName, string courseDescription, decimal cost) { try { DAL_Connection.Connection.Open(); SQL_Escape.Escape(ref courseID); SQL_Escape.Escape(ref courseName); SQL_Escape.Escape(ref courseDescription); string sqlString = string.Format( "INSERT INTO course VALUES ( " + "'{0}','{1}','{2}',{3});", courseID, courseName, courseDescription, cost); SqlCommand command = new SqlCommand(sqlString, DAL_Connection.Connection); command.ExecuteNonQuery(); } catch (Exception exception) { throw exception; } finally { DAL_Connection.Connection.Close(); } }
/// <summary> /// Inserts into datatable /// </summary> /// <param name="classRoomNumber">class room number</param> /// <param name="roomType">room type ID</param> public static void Insert( string classRoomNumber, int roomType) { try { DAL_Connection.Connection.Open(); SQL_Escape.Escape(ref classRoomNumber); string sqlString = string.Format( "INSERT INTO class_room VALUES ( " + "'{0}',{1});", classRoomNumber, roomType); SqlCommand command = new SqlCommand(sqlString, DAL_Connection.Connection); command.ExecuteNonQuery(); } catch (Exception exception) { throw exception; } finally { DAL_Connection.Connection.Close(); } }
/// <summary> /// Deletes data /// </summary> /// <param name="classRoomNumber">class room number</param> public static void Delete(string classRoomNumber) { int rowsDeleted = 0; try { DAL_Connection.Connection.Open(); SQL_Escape.Escape(ref classRoomNumber); string sqlString = "DELETE FROM class_room " + "WHERE class_room_number = '" + classRoomNumber + "';"; SqlCommand command = new SqlCommand(sqlString, DAL_Connection.Connection); rowsDeleted = command.ExecuteNonQuery(); } catch (Exception exception) { throw exception; } finally { DAL_Connection.Connection.Close(); } if (rowsDeleted == 0) { throw new KeyNotFoundException(Shared.NOT_FOUND_STRING); } }
public static void Insert( int roleID, string userName, string firstName, string lastName, string phoneNumber, string email, string city, string province, string country, string password) { string hashPassword = ""; string passwordSalt = ""; try { SQL_Escape.Escape(ref userName); SQL_Escape.Escape(ref firstName); SQL_Escape.Escape(ref lastName); SQL_Escape.Escape(ref phoneNumber); SQL_Escape.Escape(ref email); SQL_Escape.Escape(ref city); SQL_Escape.Escape(ref province); SQL_Escape.Escape(ref country); if (password == "") { hashPassword = "******"; passwordSalt = "NULL"; } else { passwordSalt = Password.GenerateSalt(20, 50); hashPassword = Password.CreateHash(password + passwordSalt); SQL_Escape.Escape(ref hashPassword); SQL_Escape.Escape(ref passwordSalt); hashPassword = "******" + hashPassword + "'"; passwordSalt = "'" + passwordSalt + "'"; } DAL_Connection.Connection.Open(); string sqlString = string.Format( "INSERT INTO users VALUES ( " + "{0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',{9}, {10} );", roleID, userName, firstName, lastName, phoneNumber, email, city, province, country, hashPassword, passwordSalt); SqlCommand command = new SqlCommand(sqlString, DAL_Connection.Connection); command.ExecuteNonQuery(); } catch (Exception exception) { throw exception; } finally { DAL_Connection.Connection.Close(); } }
public static void Update( int questionID, string question) { int rowsUpdated = 0; try { DAL_Connection.Connection.Open(); SQL_Escape.Escape(ref question); string sqlString = "UPDATE test_question SET " + "question = '" + question + "' " + "WHERE question_id = " + questionID + ";"; SqlCommand command = new SqlCommand(sqlString, DAL_Connection.Connection); rowsUpdated = command.ExecuteNonQuery(); } catch (Exception exception) { throw exception; } finally { DAL_Connection.Connection.Close(); } if (rowsUpdated == 0) { throw new KeyNotFoundException(Shared.NOT_FOUND_STRING); } }
public static void Update( int answerID, string answerValue, bool rightAnswer) { int rowsUpdated = 0; try { SQL_Escape.Escape(ref answerValue); DAL_Connection.Connection.Open(); string sqlString = "UPDATE question_answers SET " + "answer_value = '" + answerValue + "', " + "right_answer = " + Converter.BoolToInt(rightAnswer) + " " + "WHERE answer_id = " + answerID + ";"; SqlCommand command = new SqlCommand(sqlString, DAL_Connection.Connection); rowsUpdated = command.ExecuteNonQuery(); } catch (Exception exception) { throw exception; } finally { DAL_Connection.Connection.Close(); } if (rowsUpdated == 0) { throw new KeyNotFoundException(Shared.NOT_FOUND_STRING); } }
public static void Update( int userID, int roleID, string userName, string firstName, string lastName, string phoneNumber, string email, string city, string province, string country) { int rowsUpdated = 0; try { SQL_Escape.Escape(ref userName); SQL_Escape.Escape(ref firstName); SQL_Escape.Escape(ref lastName); SQL_Escape.Escape(ref phoneNumber); SQL_Escape.Escape(ref email); SQL_Escape.Escape(ref city); SQL_Escape.Escape(ref province); SQL_Escape.Escape(ref country); DAL_Connection.Connection.Open(); string sqlString = "UPDATE users SET " + "role = " + roleID + ", " + "user_name = '" + userName + "', " + "first_name = '" + firstName + "', " + "last_name = '" + lastName + "', " + "phone_number = '" + phoneNumber + "', " + "email = '" + email + "', " + "city = '" + city + "', " + "province = '" + province + "', " + "country = '" + country + "' " + "WHERE user_id = " + userID + ";"; SqlCommand command = new SqlCommand(sqlString, DAL_Connection.Connection); rowsUpdated = command.ExecuteNonQuery(); } catch (Exception exception) { throw exception; } finally { DAL_Connection.Connection.Close(); } if (rowsUpdated == 0) { throw new KeyNotFoundException(Shared.NOT_FOUND_STRING); } }
public static void Update( int gradeID, int sectionID, decimal weigth, int testID, string gradeName) { int rowsUpdated = 0; string strTestID = ""; try { DAL_Connection.Connection.Open(); SQL_Escape.Escape(ref gradeName); if (testID == 0) { strTestID = "NULL"; } else { strTestID = testID.ToString(); } string sqlString = "UPDATE grades SET " + "section_id = " + sectionID + ", " + "weight = " + weigth + ", " + "test_id = " + strTestID + ", " + "grade_name = '" + gradeName + "' " + "WHERE grade_id = " + gradeID + ";"; SqlCommand command = new SqlCommand(sqlString, DAL_Connection.Connection); rowsUpdated = command.ExecuteNonQuery(); } catch (Exception exception) { throw exception; } finally { DAL_Connection.Connection.Close(); } if (rowsUpdated == 0) { throw new KeyNotFoundException(Shared.NOT_FOUND_STRING); } }
public static void Update( int sectionID, string courseID, int semesterID, int year, int teacherID, int forumAccessibilityID) { int rowsUpdated = 0; string strTeacherID; try { SQL_Escape.Escape(ref courseID); if (teacherID == 0) { strTeacherID = "NULL"; } else { strTeacherID = teacherID.ToString(); } DAL_Connection.Connection.Open(); string sqlString = "UPDATE course_section SET " + "course_id = '" + courseID + "', " + "semester_id = " + semesterID + " , " + "year = " + year + ", " + "teacher_id = " + strTeacherID + " " + "WHERE section_id = " + sectionID + ";"; SqlCommand command = new SqlCommand(sqlString, DAL_Connection.Connection); rowsUpdated = command.ExecuteNonQuery(); } catch (Exception exception) { throw exception; } finally { DAL_Connection.Connection.Close(); } if (rowsUpdated == 0) { throw new KeyNotFoundException(Shared.NOT_FOUND_STRING); } }
/// <summary> /// Gets one row of data /// </summary> /// <param name="courseID">Course number</param> /// <returns>data</returns> public static DataTable GetData(string courseID) { SQL_Escape.Escape(ref courseID); string sqlString = "SELECT *, " + "CONCAT(course_id,':',course_name) as id_and_name " + "FROM course " + "WHERE course_id ='" + courseID + "';"; SqlDataAdapter adapter = new SqlDataAdapter(sqlString, DAL_Connection.Connection); DataTable dataTable = new DataTable(); adapter.Fill(dataTable); if (dataTable.Rows.Count == 0) { throw new KeyNotFoundException(Shared.NOT_FOUND_STRING); } return(dataTable); }
/// <summary> /// updates the database /// </summary> /// <param name="classTimeID">Class Time ID</param> /// <param name="dayID">day ID</param> /// <param name="startTime">start time in 24 hour format</param> /// <param name="endTime">end time in 24 hour format</param> /// <param name="sectionID">section ID</param> /// <param name="classRoomNumber">class room number</param> public static void Update( int classTimeID, int dayID, decimal startTime, decimal endTime, int sectionID, string classRoomNumber) { int rowsUpdated = 0; try { SQL_Escape.Escape(ref classRoomNumber); DAL_Connection.Connection.Open(); string sqlString = "UPDATE class_time SET " + "day = " + dayID + ", " + "start_time = " + startTime + ", " + "end_time = " + endTime + ", " + "section_id = " + sectionID + ", " + "class_Room_Number = '" + classRoomNumber + "' " + "WHERE class_time_id = " + classTimeID; SqlCommand command = new SqlCommand(sqlString, DAL_Connection.Connection); rowsUpdated = command.ExecuteNonQuery(); } catch (Exception exception) { throw exception; } finally { DAL_Connection.Connection.Close(); } if (rowsUpdated == 0) { throw new KeyNotFoundException(Shared.NOT_FOUND_STRING); } }
/// <summary> /// Updates the database /// </summary> /// <param name="courseID">course number</param> /// <param name="courseName">course name</param> /// <param name="courseDescription">description</param> /// <param name="cost">cost</param> public static void Update( string courseID, string courseName, string courseDescription, decimal cost) { int rowsUpdated = 0; try { DAL_Connection.Connection.Open(); SQL_Escape.Escape(ref courseID); SQL_Escape.Escape(ref courseName); SQL_Escape.Escape(ref courseDescription); string sqlString = "UPDATE course SET " + "course_name = '" + courseName + "', " + "course_descripton = '" + courseDescription + "', " + "cost = " + cost + " " + "WHERE course_id = '" + courseID + "';"; SqlCommand command = new SqlCommand(sqlString, DAL_Connection.Connection); rowsUpdated = command.ExecuteNonQuery(); } catch (Exception exception) { throw exception; } finally { DAL_Connection.Connection.Close(); } if (rowsUpdated == 0) { throw new KeyNotFoundException(Shared.NOT_FOUND_STRING); } }
public static void Insert( string courseID, int semesterID, int year, int teacherID, int forumAccessibilityID) { string strTeacherID; try { SQL_Escape.Escape(ref courseID); if (teacherID == 0) { strTeacherID = "NULL"; } else { strTeacherID = teacherID.ToString(); } DAL_Connection.Connection.Open(); string sqlString = string.Format( "INSERT INTO course_section VALUES " + "('{0}',{1},{2},{3},{4});", courseID, semesterID, year, strTeacherID, forumAccessibilityID); SqlCommand command = new SqlCommand(sqlString, DAL_Connection.Connection); command.ExecuteNonQuery(); } catch (Exception exception) { throw exception; } finally { DAL_Connection.Connection.Close(); } }
public static void Insert( int sectionID, decimal weigth, int testID, string gradeName) { string strTestID = ""; try { if (testID == 0) { strTestID = "NULL"; } else { strTestID = testID.ToString(); } DAL_Connection.Connection.Open(); SQL_Escape.Escape(ref gradeName); string sqlString = string.Format( "INSERT INTO grades VALUES ( " + "{0},{1},{2},'{3}');", sectionID, weigth, strTestID, gradeName); SqlCommand command = new SqlCommand(sqlString, DAL_Connection.Connection); command.ExecuteNonQuery(); } catch (Exception exception) { throw exception; } finally { DAL_Connection.Connection.Close(); } }
public static DataTable GetDataByCourseID(string courseID) { SQL_Escape.Escape(ref courseID); string sqlString = "SELECT course_section.* ," + "course.course_Name , " + "CONCAT(users.first_name,' ',users.last_name) AS teacher, " + "semester_name, " + "CONCAT(section_id,' - ',course.course_id) AS section_and_course_id, " + "CONCAT (year,'-',semester_name) AS year_and_semester " + "FROM course_Section " + "JOIN course ON course_section.course_id = course.course_id " + "LEFT JOIN users ON teacher_id = user_id " + "JOIN semester_lookup ON course_section.semester_id=semester_lookup.semester_id " + "WHERE course_section.course_id = '" + courseID + "';"; SqlDataAdapter adapter = new SqlDataAdapter(sqlString, DAL_Connection.Connection); DataTable dataTable = new DataTable(); adapter.Fill(dataTable); return(dataTable); }
public static DataTable GetData(string userName) { SQL_Escape.Escape(ref userName); string sqlString = "SELECT users.*, role_type, " + "CONCAT(first_name,' ',last_name) AS full_name " + "FROM users " + "JOIN user_role " + "ON users.role = role_id " + "WHERE user_name = '" + userName + "';"; SqlDataAdapter adapter = new SqlDataAdapter(sqlString, DAL_Connection.Connection); DataTable dataTable = new DataTable(); adapter.Fill(dataTable); if (dataTable.Rows.Count == 0) { throw new KeyNotFoundException(Shared.NOT_FOUND_STRING); } return(dataTable); }
public static void SetPassword( int userID, string password) { int rowsUpdated = 0; string hashPassword = ""; string passwordSalt = ""; try { SQL_Escape.Escape(ref hashPassword); passwordSalt = Password.GenerateSalt(20, 50); hashPassword = Password.CreateHash(password + passwordSalt); DAL_Connection.Connection.Open(); string sqlString = "UPDATE users SET " + "password = '******', " + "password_salt = '" + passwordSalt + "' " + "WHERE user_id = " + userID + ";"; SqlCommand command = new SqlCommand(sqlString, DAL_Connection.Connection); rowsUpdated = command.ExecuteNonQuery(); } catch (Exception exception) { throw exception; } finally { DAL_Connection.Connection.Close(); } if (rowsUpdated == 0) { throw new KeyNotFoundException(Shared.NOT_FOUND_STRING); } }