public Add_New_User(String editId, String userType) { InitializeComponent(); fillComboProfileType(); label1.Text = "Edit User"; buttonSaveUser.Hide(); labelUserId.Text = editId; this.editID = editId; //id, generatedID, firstName, lastName, mobileNumber, landLineNumber, permenentAddress, residentAddress, isResidentSame, birthday, profileImageUrl, createDateTime, updateDateTime, isActive, user_login_history_id, user_profile_type_id String getUserDetailsSql = "SELECT firstName, lastName, mobileNumber, landLineNumber, permenentAddress, residentAddress, isResidentSame, birthday, profileImageUrl, user_login.emailAddress as emailAddress, user_login.password as password from user_profile left join user_profile_type on user_profile.user_profile_type_id = user_profile_type.id left join user_login on user_profile.id = user_login.user_profile_id left join user_role on user_login.user_role_id = user_role.id WHERE user_profile.generatedID = @currentId"; MySqlConnection mySqlConnection = DataConnection.getDBConnection(); mySqlConnection.Open(); MySqlCommand cmd_Profile = new MySqlCommand(getUserDetailsSql, mySqlConnection); cmd_Profile.CommandText = getUserDetailsSql; cmd_Profile.Parameters.AddWithValue("currentId", editId); MySqlDataReader DataReader = cmd_Profile.ExecuteReader(); if (userType.Equals("Librarian")) { comboBoxProfileType.SelectedItem = "Librarian"; while (DataReader.Read()) { textBoxFirstName.Text = DataReader.GetString("firstName"); textBoxLastName.Text = DataReader.GetString("lastName"); textBoxMobile.Text = DataReader.GetString("mobileNumber"); textBoxLand.Text = DataReader.GetString("landLineNumber"); textBoxPAddress.Text = DataReader.GetString("permenentAddress"); textBoxRAddress.Text = DataReader.GetString("residentAddress"); if (DataReader.GetString("isResidentSame").Equals("True")) { checkBoxConfirmAddress.Checked = true; } else { checkBoxConfirmAddress.Checked = false; } textBoxEmail.Text = DataReader.GetString("emailAddress"); textBoxPassword.Text = StringCipher.Decrypt(DataReader.GetString("password"), LoginDetails.passwordKey); textBoxRePassword.Text = StringCipher.Decrypt(DataReader.GetString("password"), LoginDetails.passwordKey); dateTimePickerBirthday.Text = DataReader.GetString("birthday"); /* if (DataReader.GetString("profileImageUrl").Equals("null")) * { * pictureBoxUser.Image = null; * } * else * { * byte[] imageVal = (byte[])DataReader[8]; * MemoryStream ms = new MemoryStream(imageVal); * pictureBoxUser.Image = Image.FromStream(ms); * }*/ } } else { checkBoxAdmin.Hide(); panel1.Hide(); comboBoxProfileType.SelectedItem = "Reader"; while (DataReader.Read()) { textBoxFirstName.Text = DataReader.GetString("firstName"); textBoxLastName.Text = DataReader.GetString("lastName"); textBoxMobile.Text = DataReader.GetString("mobileNumber"); textBoxLand.Text = DataReader.GetString("landLineNumber"); textBoxPAddress.Text = DataReader.GetString("permenentAddress"); textBoxRAddress.Text = DataReader.GetString("residentAddress"); if (DataReader.GetString("isResidentSame").Equals("True")) { checkBoxConfirmAddress.Checked = true; } else { checkBoxConfirmAddress.Checked = false; } //textBoxEmail.Text = DataReader.GetString("emailAddress"); // textBoxPassword.Text = StringCipher.Decrypt(DataReader.GetString("password"), LoginDetails.passwordKey); //textBoxRePassword.Text = StringCipher.Decrypt(DataReader.GetString("password"), LoginDetails.passwordKey); dateTimePickerBirthday.Text = DataReader.GetString("birthday"); String imageVal = DataReader.GetString("profileImageUrl"); /*Console.WriteLine(DataReader[8]); * if (imageVal == null) * { * pictureBoxUser.Image = null; * } * else * { * byte[] imageValBytes = Encoding.ASCII.GetBytes(imageVal); * MemoryStream ms = new MemoryStream(imageValBytes); * pictureBoxUser.Image = Image.FromStream(ms); * }*/ } } mySqlConnection.Close(); }
private void buttonUpdateUser_Click(object sender, EventArgs e) { byte[] images = null; if (imageLoacation != null && !imageLoacation.Equals("")) { FileStream fileStream = new FileStream(imageLoacation, FileMode.Open, FileAccess.Read); BinaryReader reader = new BinaryReader(fileStream); images = reader.ReadBytes((int)fileStream.Length); } String userId = labelUserId.Text; String fName = textBoxFirstName.Text; String lName = textBoxLastName.Text; String mobile = textBoxMobile.Text; String landNumber = textBoxLand.Text; String pAddress = textBoxPAddress.Text; String rAddress = textBoxRAddress.Text; String birthday = dateTimePickerBirthday.Text; bool isProfileTypeSelected = true; String email = textBoxEmail.Text; String password = textBoxPassword.Text; String rePassword = textBoxRePassword.Text; String profileType = ""; String userRole = "Librarian"; bool savePermission = false; String getProfileTypeIdSql = "SELECT id FROM user_profile_type WHERE name = @profileType"; String saveUserProfileSql = "UPDATE user_profile SET firstName=@firstName, lastName=@lastName, mobileNumber=@mobileNumber, landLineNumber=@landLineNumber, permenentAddress=@permenentAddress, residentAddress=@residentAddress, isResidentSame=@isResidentSame, birthday=@birthday, profileImageUrl=@profileImageUrl, updateDateTime=@updateDateTime WHERE generatedID = @generatedID"; String getUserRoleIdSql = "SELECT id FROM user_role WHERE name = @roleName"; String getUserProfileIdSql = "SELECT id FROM user_profile WHERE generatedID = @userId"; String saveUserLoginSql = "UPDATE user_login SET emailAddress=@emailAddress, password=@password, updateDateTime=@updateDateTime WHERE user_profile_id = @user_profile_id"; DateTime dateTime = DateTime.Now; dateTime.ToString("yyyyMMddHHmmss"); try { profileType = comboBoxProfileType.SelectedItem.ToString(); } catch { isProfileTypeSelected = false; } if (fName.Equals("") || lName.Equals("") || mobile.Equals("") || pAddress.Equals("") || rAddress.Equals("") || birthday.Equals("")) { MessageBox.Show("Some fields can't be Empty", "Register new user", MessageBoxButtons.OK, MessageBoxIcon.Warning); } else if (!isProfileTypeSelected) { MessageBox.Show("Please select the user profile type", "Register new user", MessageBoxButtons.OK, MessageBoxIcon.Warning); } else { if (profileType.Equals("Librarian")) { if (email.Equals("") || password.Equals("") || rePassword.Equals("")) { MessageBox.Show("Authentication details can't be empty", "Register new user", MessageBoxButtons.OK, MessageBoxIcon.Warning); } else { bool adminCheck = checkBoxAdmin.Checked; if (IsValidEmail(email)) { if (password.Equals(rePassword)) { savePermission = true; if (adminCheck) { DialogResult dialogResult = MessageBox.Show("Are you sure you want to make this user as an Administrator. Changes can't be roll back", "Register new user", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning); if (dialogResult == DialogResult.OK) { userRole = "Administrative Librarian"; savePermission = true; } else { savePermission = false; } } else { userRole = "Librarian"; } if (savePermission) { MySqlConnection mySqlConnection = DataConnection.getDBConnection(); mySqlConnection.Open(); MySqlCommand commandSaveLibrarian = new MySqlCommand(saveUserProfileSql, mySqlConnection); commandSaveLibrarian.CommandText = saveUserProfileSql; commandSaveLibrarian.Parameters.AddWithValue("@generatedID", editID); commandSaveLibrarian.Parameters.AddWithValue("@firstName", fName); commandSaveLibrarian.Parameters.AddWithValue("@lastName", lName); commandSaveLibrarian.Parameters.AddWithValue("@mobileNumber", mobile); commandSaveLibrarian.Parameters.AddWithValue("@landLineNumber", landNumber); commandSaveLibrarian.Parameters.AddWithValue("@permenentAddress", pAddress); commandSaveLibrarian.Parameters.AddWithValue("@residentAddress", rAddress); commandSaveLibrarian.Parameters.AddWithValue("@isResidentSame", isResidenceSame); commandSaveLibrarian.Parameters.AddWithValue("@birthday", birthday); if (imageLoacation != null) { commandSaveLibrarian.Parameters.AddWithValue("@profileImageUrl", images.ToString()); } else { commandSaveLibrarian.Parameters.AddWithValue("@profileImageUrl", null); } commandSaveLibrarian.Parameters.AddWithValue("@updateDateTime", dateTime); commandSaveLibrarian.ExecuteNonQuery(); MySqlCommand getUserProfileId = new MySqlCommand(getUserProfileIdSql, mySqlConnection); getUserProfileId.CommandText = getUserProfileIdSql; getUserProfileId.Parameters.AddWithValue("@userId", editID); MySqlDataReader readProfileID = getUserProfileId.ExecuteReader(); readProfileID.Read(); String userProfileId = readProfileID.GetString("id"); readProfileID.Close(); MySqlCommand saveUserLoginDetails = new MySqlCommand(saveUserLoginSql, mySqlConnection); saveUserLoginDetails.CommandText = saveUserLoginSql; saveUserLoginDetails.Parameters.AddWithValue("@emailAddress", email); saveUserLoginDetails.Parameters.AddWithValue("@password", StringCipher.Encrypt(password, LoginDetails.passwordKey)); saveUserLoginDetails.Parameters.AddWithValue("@updateDateTime", null); saveUserLoginDetails.Parameters.AddWithValue("@user_profile_id", userProfileId); saveUserLoginDetails.ExecuteNonQuery(); mySqlConnection.Close(); MessageBox.Show("Librarian Successfully Updated!", "Edit user", MessageBoxButtons.OK, MessageBoxIcon.Information); new Manage_Users().Show(); this.Hide(); } } else { MessageBox.Show("Passwords do not match", "Edit user", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { MessageBox.Show("Please enter a valid email address", "Edit user", MessageBoxButtons.OK, MessageBoxIcon.Warning); } } } else { //Reader Selected MySqlConnection mySqlConnection = DataConnection.getDBConnection(); mySqlConnection.Open(); MySqlCommand commandSaveReader = new MySqlCommand(saveUserProfileSql, mySqlConnection); commandSaveReader.CommandText = saveUserProfileSql; commandSaveReader.Parameters.AddWithValue("@generatedID", editID); commandSaveReader.Parameters.AddWithValue("@firstName", fName); commandSaveReader.Parameters.AddWithValue("@lastName", lName); commandSaveReader.Parameters.AddWithValue("@mobileNumber", mobile); commandSaveReader.Parameters.AddWithValue("@landLineNumber", landNumber); commandSaveReader.Parameters.AddWithValue("@permenentAddress", pAddress); commandSaveReader.Parameters.AddWithValue("@residentAddress", rAddress); commandSaveReader.Parameters.AddWithValue("@isResidentSame", isResidenceSame); commandSaveReader.Parameters.AddWithValue("@birthday", birthday); if (imageLoacation != null) { commandSaveReader.Parameters.AddWithValue("@profileImageUrl", images.ToString()); } else { commandSaveReader.Parameters.AddWithValue("@profileImageUrl", null); } commandSaveReader.Parameters.AddWithValue("@updateDateTime", dateTime); commandSaveReader.ExecuteNonQuery(); mySqlConnection.Close(); MessageBox.Show("Reader Successfully Updated!", "Edit user", MessageBoxButtons.OK, MessageBoxIcon.Information); new Manage_Users().Show(); this.Hide(); } } }
private void button1_Click(object sender, EventArgs e) { //+++++++++ get input values from login form +++++++++// String email = textBoxEmail.Text; String password = textBoxPassword.Text; //+++++++++ null & empty check for input values +++++++++// if ((email == null || email.Equals("")) && (password == null || password.Equals(""))) { MessageBox.Show("Email or Password Can't be Empty", "Authentication Error!", MessageBoxButtons.OK, MessageBoxIcon.Warning); } else { String loginQuery = "SELECT * FROM user_login WHERE emailAddress = @email"; String getUserRole = "SELECT * FROM user_role WHERE id = @userRoleId"; String insertLoginHistory = "INSERT INTO user_login_history (loginDateTime, logoutDateTime, user_login_id) VALUES (@loginDateTime, null, @user_login_id)"; String getLoginHistoryID = "SELECT * FROM user_login_history WHERE loginDateTime = @currentDate"; DateTime dateTime = DateTime.Now; dateTime.ToString("yyyyMMddHHmmss"); MySqlConnection mySqlConnection = DataConnection.getDBConnection(); mySqlConnection.Open(); MySqlCommand command = new MySqlCommand(loginQuery, mySqlConnection); command.CommandText = loginQuery; command.Parameters.AddWithValue("@email", email); MySqlDataReader mySqlDataReader = command.ExecuteReader(); if (mySqlDataReader.HasRows) { while (mySqlDataReader.Read()) { String encPassword = mySqlDataReader.GetString("password"); String decPassword = StringCipher.Decrypt(encPassword, LoginDetails.passwordKey); if (password.Equals(decPassword)) { String userRoleId = mySqlDataReader.GetString("user_role_id"); String userLoginId = mySqlDataReader.GetString("id"); LoginDetails.userLoginId = userLoginId; MySqlCommand commandGetUserRole = new MySqlCommand(getUserRole, mySqlConnection); commandGetUserRole.CommandText = getUserRole; commandGetUserRole.Parameters.AddWithValue("@userRoleId", userRoleId); MySqlCommand commandInsertLoginHistory = new MySqlCommand(insertLoginHistory, mySqlConnection); commandInsertLoginHistory.CommandText = insertLoginHistory; commandInsertLoginHistory.Parameters.AddWithValue("@loginDateTime", dateTime); commandInsertLoginHistory.Parameters.AddWithValue("@user_login_id", userLoginId); mySqlDataReader.Close(); commandInsertLoginHistory.ExecuteNonQuery(); MySqlCommand commandGetLoginHistoryId = new MySqlCommand(getLoginHistoryID, mySqlConnection); commandGetLoginHistoryId.CommandText = getLoginHistoryID; commandGetLoginHistoryId.Parameters.AddWithValue("@currentDate", dateTime); MySqlDataReader loginHistoryReader = commandGetLoginHistoryId.ExecuteReader(); loginHistoryReader.Read(); LoginDetails.userLoginHistoryID = loginHistoryReader.GetString("id"); loginHistoryReader.Close(); MySqlDataReader mySqlDataReader2 = commandGetUserRole.ExecuteReader(); if (mySqlDataReader2.HasRows) { mySqlDataReader2.Read(); LoginDetails.userRole = mySqlDataReader2.GetString("name"); if (LoginDetails.userRole.Equals("Administrative Librarian")) { Home home = new Home(); home.Show(); } else { Home home = new Home("librarian"); home.Show(); } this.Hide(); break; } else { MessageBox.Show("User type does not exists", "Authentication Error!", MessageBoxButtons.OK, MessageBoxIcon.Warning); break; } } else { MessageBox.Show("Incorrect Password", "Authentication Error!", MessageBoxButtons.OK, MessageBoxIcon.Error); textBoxPassword.Text = ""; } } } else { MessageBox.Show("Account does not exists", "Authentication Error!", MessageBoxButtons.OK, MessageBoxIcon.Warning); textBoxEmail.Text = ""; } mySqlConnection.Close(); } }