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