/// <summary>
/// Saves a user
/// </summary>
/// <param name="theUser">User to be saved</param>
public static void SaveUser(UserData theUser)
{
if (theUser.ID > 0)
userEntity.addUser(theUser);
else
userEntity.updateUser(theUser);
}
/// <summary>
/// Checks to see if the user has inputed a password matching the user
/// </summary>
/// <param name="theUser">the user trying to log in</param>
/// <param name="Password">the password of the user</param>
/// <returns></returns>
public static bool AuthenticateUser(UserData theUser, string Password)
{
SHA1 MyHasher = new SHA1CryptoServiceProvider();
byte[] result = MyHasher.ComputeHash(Encoding.Default.GetBytes(Password));
StringBuilder HexString = new StringBuilder();
for (int i = 0; i < result.Length; i++)
HexString.Append(result[i].ToString("x2"));
return (HexString.ToString().ToUpper() == theUser.Password.ToUpper());
}
/// <summary>
/// Saves a user
/// </summary>
/// <param name="theUser">User to be saved</param>
public static void SaveUser(UserData theUser)
{
try
{
if (theUser.Id == 0)
userEntity.addUser(theUser);
else
userEntity.updateUser(theUser);
}
catch (System.Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message);
}
}
/// <summary>
/// Deletes the given user from the database
/// </summary>
/// <param name="userData"></param>
public void deleteUser(UserData userData)
{
try
{
this.SQL = "DELETE FROM `users` WHERE `user_id` = " + userData.ID + ";";
}
catch (System.Exception e)
{
throw new System.Exception(e.Message, e.InnerException);
}
finally
{
this.CloseConnection();
}
}
/// <summary>
/// Adds the user data to the database
/// </summary>
/// <param name="userData"></param>
public void addUser(UserData userData)
{
try
{
SHA1 MyHasher = new SHA1CryptoServiceProvider();
byte[] result = MyHasher.ComputeHash(Encoding.Default.GetBytes(userData.Password));
StringBuilder HexString = new StringBuilder();
for (int i = 0; i < result.Length; i++)
HexString.Append(result[i].ToString("x2"));
this.SQL = "INSERT INTO `users` (`user_name`, `full_name`, `role_id`, `password`) " +
"VALUES (\"" + userData.UserName + "\", \"" + userData.FullName + "\", " + (int)userData.Role + ", \"" + HexString.ToString().ToUpper() + "\");";
this.InitializeCommand();
this.OpenConnection();
if (this.ExecuteStoredProcedure() == 0)
throw new Exception("Unable to add the user to the database.");
else
{
this.SQL = "SELECT MAX(u.`user_id`) FROM `users` u;";
this.InitializeCommand();
this.DataReader = this.Command.ExecuteReader();
if (this.DataReader.HasRows)
{
this.DataReader.Read();
userData.Id = this.DataReader.GetInt32(0);
}
}
}
catch (System.Exception e)
{
throw new System.Exception(e.Message, e.InnerException);
}
finally
{
this.CloseConnection();
}
}
public void AddUser(CourseData theCourse, UserData theUser)
{
if (DataReader != null)
DataReader.Close();
SQL = "INSERT INTO `rel_courses_users` (`course_id`, `user_id`) VALUES (\"" +
theCourse.Id + "\", \"" +
theUser.Id + "\");";
InitializeCommand();
OpenConnection();
int result = ExecuteStoredProcedure();
CloseConnection();
if (result == 0)
throw new Exception("The user could not be added to the course");
}
/// <summary>
/// Deletes the given user from the database
/// </summary>
/// <param name="userData"></param>
public void deleteUser(UserData userData)
{
try
{
this.SQL = "DELETE FROM `users` WHERE `user_id` = " + userData.Id + ";";
this.InitializeCommand();
this.OpenConnection();
if (this.ExecuteStoredProcedure() == 0)
throw new System.Exception("Unable to delete the user.");
}
catch (System.Exception e)
{
throw new System.Exception(e.Message, e.InnerException);
}
finally
{
this.CloseConnection();
}
}
private void btnLogin_Click(object sender, EventArgs e)
{
try
{
myUser = UserController.GetUser(txtUserName.Text);
//Before considering the password, the user must exist in the database
if (myUser != null)
{
if (UserController.AuthenticateUser(myUser, txtPassword.Text))
{
this.Dispose();
}
else
MessageBox.Show("The User name or password is incorrect.");
}
}
catch
{
MessageBox.Show("The User name or password is incorrect.");
}
}
/// <summary>
/// Gets a list of UserData objects representing all students currently in the database
/// </summary>
/// <returns></returns>
public List<UserData> GetStudents()
{
List<UserData> theList = new List<UserData>();
try
{
this.SQL = "SELECT * FROM `users` u WHERE u.role_id = 2;";
this.InitializeCommand();
this.OpenConnection();
this.DataReader = this.Command.ExecuteReader();
if (this.DataReader.HasRows)
{
UserData newUser;
while (this.DataReader.Read())
{
newUser = new UserData(this.DataReader.GetInt32("user_id"));
newUser.FullName = this.DataReader.GetString("full_name");
newUser.Password = this.DataReader.GetString("password");
newUser.Role = (UserData.Roles)this.DataReader.GetInt32("role_id");
newUser.UserName = this.DataReader.GetString("user_name");
theList.Add(newUser);
}
}
}
catch (System.Exception ex)
{
throw new System.Exception(ex.Message, ex.InnerException);
}
finally
{
this.CloseConnection();
}
return theList;
}
/// <summary>
/// Adds the user data to the database
/// </summary>
/// <param name="userData"></param>
public void addUser(UserData userData)
{
try
{
this.SQL = "INSERT INTO `users` (`user_name`, `full_name`, `role_id`, `password) " +
"VALUES (\"" + userData.UserName + "\", \"" + userData.FullName + "\", " + userData.Role + ", \"" + userData.Password + "\");";
this.InitializeCommand();
this.OpenConnection();
if (this.ExecuteStoredProcedure() == 0)
throw new Exception("Unable to add the user to the database.");
}
catch (System.Exception e)
{
throw new System.Exception(e.Message, e.InnerException);
}
finally
{
this.CloseConnection();
}
}
/// <summary>
/// Checks to see if the user has taken the given test
/// </summary>
/// <param name="theUser"></param>
/// <param name="theQuiz"></param>
/// <returns></returns>
public bool TestTaken(UserData theUser, QuizData theQuiz)
{
bool return_val = false;
if (DataReader != null)
DataReader.Close();
SQL = "select IFNULL(COUNT(`user_id`), 0) FROM `rel_quizzes_users` r WHERE r.`user_id` = \"" + theUser.Id + "\" and r.`quiz_id` = \"" + theQuiz.Id + "\";";
InitializeCommand();
OpenConnection();
DataReader = Command.ExecuteReader();
DataReader.Read();
return_val = DataReader.GetUInt16("IFNULL(COUNT(`user_id`), 0)") == 1;
CloseConnection();
return return_val;
}
/// <summary>
/// Gets a UserData object based on the user's ID
/// </summary>
/// <param name="userID"></param>
/// <returns></returns>
public UserData getUser(int userID)
{
UserData newUser = null;
try
{
this.SQL = "SELECT * FROM users u WHERE u.user_id = \"" + userID + "\";";
this.InitializeCommand();
this.OpenConnection();
this.DataReader = this.Command.ExecuteReader();
if (this.DataReader.HasRows)
{
DataReader.Read();
newUser = new UserData(this.DataReader.GetInt32("user_id"));
newUser.FullName = this.DataReader.GetString("full_name");
newUser.Password = this.DataReader.GetString("password");
newUser.Role = (UserData.Roles)this.DataReader.GetInt32("role_id");
newUser.UserName = this.DataReader.GetString("user_name");
}
else
throw new Exception("Unable find user with user ID \"" + userID + "\".");
}
catch (System.Exception e)
{
throw new System.Exception(e.Message, e.InnerException);
}
finally
{
this.CloseConnection();
}
return newUser;
}
/// <summary>
/// deletes a specified user from the database
/// </summary>
/// <param name="theUser">user to delete</param>
public static void DeleteUser(UserData theUser)
{
userEntity.deleteUser(theUser);
}
/// <summary>
/// Gets a list of UserData objects representing the students enrolled in a particular course
/// </summary>
/// <param name="CourseID"></param>
/// <returns></returns>
public List<UserData> GetStudentsByCourse(int CourseID)
{
List<UserData> theList = new List<UserData>();
try
{
this.SQL = "SELECT u.* FROM `users` u JOIN `rel_courses_users` uc ON u.`user_id` = uc.`user_id` " +
"WHERE u.`role_id` = 2 AND uc.`course_id` = " + CourseID + ";";
this.InitializeCommand();
this.OpenConnection();
this.DataReader = this.Command.ExecuteReader();
if (this.DataReader.HasRows)
{
UserData newUser;
while (this.DataReader.Read())
{
newUser = new UserData(this.DataReader.GetInt32("user_id"));
newUser.FullName = this.DataReader.GetString("full_name");
newUser.Password = this.DataReader.GetString("password");
newUser.Role = (UserData.Roles)this.DataReader.GetInt32("role_id");
newUser.UserName = this.DataReader.GetString("user_name");
theList.Add(newUser);
}
}
}
catch (System.Exception ex)
{
throw new System.Exception(ex.Message, ex.InnerException);
}
finally
{
this.CloseConnection();
}
return theList;
}
/// <summary>
/// removes a specified user from a specified course
/// </summary>
/// <param name="course">course to remove user from</param>
/// <param name="user">user to be removed</param>
public static void RemoveUser(CourseData course, UserData user)
{
courseEntity.RemoveUser(course, user);
}
/// <summary>
/// gets a list of courses associated with a specified user
/// </summary>
/// <param name="user">user to look up classes for</param>
/// <returns></returns>
public static List<CourseData> GetCourses(UserData user)
{
return courseEntity.ReadCourses(user);
}
/// <summary>
/// adds a user to a specified course
/// </summary>
/// <param name="course">the course to add the user to</param>
/// <param name="user">the user to add to the course</param>
public static void AddUser(CourseData course, UserData user)
{
courseEntity.AddUser(course, user);
}
/// <summary>
/// Handles click event for new student button
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnNew_Click(object sender, EventArgs e)
{
UserData newUser = new UserData();
newUser.Role = UserData.Roles.Student;
frmUser user = new frmUser(newUser);
user.ShowDialog();
//check to see if the user was added to database... if not then we probably cancelled
if (newUser.Id != 0)
{
//add user to user list
ListViewItem item = lvwStudentsNotInCourse.Items.Add(newUser.FullName, 0);
item.Tag = newUser;
}
}
public static double GetStudentPercentage(UserData student, QuizData quiz)
{
return theEntity.StudentResults(student, quiz);
}
/// <summary>
/// Gets a List of courses a given user is enrolled in
/// </summary>
/// <param name="theUser">a User object that is being queried</param>
/// <returns>A List of Course objects</returns>
public List<CourseData> ReadCourses(UserData theUser)
{
List<CourseData> return_data = new List<CourseData>();
if (DataReader != null)
DataReader.Close();
SQL = "SELECT * FROM `courses` c INNER JOIN `rel_courses_users` r ON r.`course_id` = c.`course_id` WHERE r.`user_id` = \"" + theUser.ID + "\";";
InitializeCommand();
OpenConnection();
DataReader = Command.ExecuteReader();
if (DataReader.HasRows) {
while (DataReader.Read()) {
CourseData temp = new CourseData(DataReader.GetUInt16("`course_id`"));
temp.Name = DataReader.GetString("`name`");
return_data.Add(temp);
}
}
CloseConnection();
return return_data;
}
/// <summary>
/// Updated the user data in the database
/// </summary>
/// <param name="userData"></param>
public void updateUser(UserData userData)
{
SHA1 MyHasher = new SHA1CryptoServiceProvider();
byte[] result = MyHasher.ComputeHash(Encoding.Default.GetBytes(userData.Password));
StringBuilder HexString = new StringBuilder();
for (int i = 0; i < result.Length; i++)
HexString.Append(result[i].ToString("X2"));
DataReader.Close();
this.SQL = "UPDATE `users` u SET " +
"u.`user_name` = \"" + userData.UserName + "\", " +
"u.`full_name` = \"" + userData.FullName + "\", " +
"u.`role_id` = \"" + (int)userData.Role + "\", " +
"u.`password` = \"" + HexString.ToString() + "\" " +
"WHERE u.`user_id` = " + userData.Id + ";";
InitializeCommand();
OpenConnection();
if (ExecuteStoredProcedure() == 0)
throw new Exception("Unable to update User Data");
CloseConnection();
DataReader.Close();
}
public void RemoveUser(CourseData theCourse, UserData theUser)
{
if (DataReader != null)
DataReader.Close();
SQL = "DELETE FROM `rel_courses_users` WHERE `rel_courses_users`.`course_id` = \"" + theCourse.Id + "\" and `rel_courses_users`.`user_id` = \"" + theUser.Id + "\";";
InitializeCommand();
OpenConnection();
int result = ExecuteStoredProcedure();
CloseConnection();
if (result == 0)
throw new Exception("Unable to remove the user from the course");
}
/// <summary>
/// Updated the user data in the database
/// </summary>
/// <param name="userData"></param>
public void updateUser(UserData userData)
{
DataReader.Close();
this.SQL = "UPDATE `users` u SET " +
"u.`user_name` = \"" + userData.UserName + "\", " +
"u.`full_name` = \"" + userData.FullName + "\", " +
"u.`role_id` = \"" + userData.Role + "\", " +
"u.`password` = \"" + userData.Password + "\" " +
"WHERE u.`user_id` = " + userData.ID + ";";
InitializeCommand();
OpenConnection();
int result = ExecuteStoredProcedure();
CloseConnection();
DataReader.Close();
if (result == 0)
throw new Exception("Unable to update User Data");
}
public ResultData(UserData theUser, QuizData theQuiz)
{
__Answers = new List<AnswerData>();
__Student = theUser;
__Quiz = theQuiz;
}
public double StudentResults(UserData theUser, QuizData theQuiz)
{
double return_val = 100.00;
int quest_count;
int correct_count;
if (DataReader != null)
{
DataReader.Close();
DataReader.Dispose();
Command.Dispose();
Command = null;
}
SQL = "SELECT IFNULL(COUNT(`question_id`), 0) FROM `rel_quizzes_questions` WHERE `rel_quizzes_questions`.`quiz_id` = " + theQuiz.Id + ";";
InitializeCommand();
OpenConnection();
DataReader = Command.ExecuteReader();
DataReader.Read();
quest_count = DataReader.GetUInt16("IFNULL(COUNT(`question_id`), 0)");
if (DataReader != null)
DataReader.Close();
SQL = "SELECT IFNULL(COUNT(r.`question_id`), 0) FROM `rel_quizzes_questions` r INNER JOIN `questions` q on q.`question_id` = r.`question_id` INNER JOIN `rel_questions_answers` s ON s.`question_id` = q.`question_id` INNER JOIN `answers` a ON a.`answer_id` = s.`answer_id` INNER JOIN `rel_answers_users` t ON t.`answer_id` = a.`answer_id` WHERE r.`quiz_id` = \"" + theQuiz.Id + "\" and a.`is_correct` = \"1\" and t.`user_id` = \"" + theUser.Id + "\";";
InitializeCommand();
DataReader = Command.ExecuteReader();
DataReader.Read();
correct_count = DataReader.GetUInt16("IFNULL(COUNT(r.`question_id`), 0)");
if (DataReader != null)
DataReader.Close();
if (correct_count == 0)
return_val = 0;
else {
if (quest_count == 0)
return_val = 0;
else
return_val = ((double)correct_count / (double)quest_count) * 100.00;
}
CloseConnection();
return return_val;
}
