private string getDownloadFile() { string downloadFile = ""; if (dbConnection.OpenConnection()) { MySqlCommand command = new MySqlCommand(); command.Connection = dbConnection.getConnection(); command.CommandText = "SELECT app_path FROM applications WHERE application_id = @application_id"; command.Parameters.AddWithValue("@application_id", appId); using (MySqlDataReader dr = command.ExecuteReader()) { try { while (dr.Read()) { downloadFile = dr[0].ToString(); } } catch (Exception ex) { System.Console.WriteLine("Not found..."); } } dbConnection.CloseConnection(); command.Dispose(); } return(downloadFile); }
public void RefreshUserList() { usersListView.Items.Clear(); if (dbConnection.OpenConnection()) { MySqlCommand command = new MySqlCommand(); command.Connection = dbConnection.getConnection(); if (search) { command.CommandText = "SELECT member_id, f_name, l_name, position FROM member_info WHERE f_name LIKE @fname AND l_name LIKE @lname AND position LIKE @position;"; command.Parameters.Add("@fname", MySqlDbType.VarChar, 200).Value = "%" + txtFirstName.Text + "%"; command.Parameters.Add("@lname", MySqlDbType.VarChar, 200).Value = "%" + txtLastName.Text + "%"; command.Parameters.Add("@position", MySqlDbType.VarChar, 200).Value = "%" + positionLevel.Text + "%"; } else { command.CommandText = "SELECT member_id, f_name, l_name, position FROM member_info"; } using (MySqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { ListViewItem item = new ListViewItem(); item.Text = dr[0].ToString(); item.SubItems.Add(dr[1].ToString()); item.SubItems.Add(dr[2].ToString()); item.SubItems.Add(dr[3].ToString()); usersListView.Items.Add(item); } } dbConnection.CloseConnection(); command.Dispose(); } }
public void RefreshApplicationList() { applicationList.Items.Clear(); if (dbConnection.OpenConnection()) { MySqlCommand command = new MySqlCommand(); command.Connection = dbConnection.getConnection(); if (securityStatus == 4) { command.CommandText = "SELECT job_id, application_id, name, phone, email, approved FROM applications WHERE job_id = @job_id AND passOn = 'yes'"; } else if (securityStatus == 3) { command.CommandText = "SELECT job_id, application_id, name, phone, email, approved FROM applications WHERE job_id = @job_id AND passOn is NULL"; } else { command.CommandText = "SELECT job_id, application_id, name, phone, email, approved FROM applications WHERE job_id = @job_id"; } command.Parameters.AddWithValue("@job_id", jobId); using (MySqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { ListViewItem item = new ListViewItem(); item.Text = dr[0].ToString(); item.SubItems.Add(dr[1].ToString()); item.SubItems.Add(dr[2].ToString()); item.SubItems.Add(dr[3].ToString()); item.SubItems.Add(dr[4].ToString()); item.SubItems.Add(dr[5].ToString()); applicationList.Items.Add(item); } } dbConnection.CloseConnection(); command.Dispose(); foreach (ListViewItem li in applicationList.Items) { if (li.SubItems[5].Text == "Yes") { li.BackColor = Color.LightGreen; } } foreach (ListViewItem li in applicationList.Items) { if ((Int32.Parse(li.SubItems[1].Text)) == sentApp) { li.BackColor = Color.LightBlue; } } } }
public void RefreshJobList() { jobList.Items.Clear(); if (dbConnection.OpenConnection()) { MySqlCommand command = new MySqlCommand(); command.Connection = dbConnection.getConnection(); command.CommandText = "SELECT job_id, job_name, job_status FROM dropboxes"; using (MySqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { ListViewItem item = new ListViewItem(); item.Text = dr[0].ToString(); item.SubItems.Add(dr[1].ToString()); item.SubItems.Add(dr[2].ToString()); jobList.Items.Add(item); } } dbConnection.CloseConnection(); command.Dispose(); foreach (ListViewItem li in jobList.Items) { if (li.SubItems[2].Text == "Approved") { li.BackColor = Color.LightGreen; } } } }
static void Main() { Application.EnableVisualStyles(); Application.SetCompatibleTextRenderingDefault(false); // TODO: Create a new Connection and test before showing mainForm. DBConnection dbConnection = new DBConnection("159.203.38.0", "ears_db", "root", "12345"); if (dbConnection.OpenConnection()) { dbConnection.CloseConnection(); SignInForm signInForm = new SignInForm(dbConnection); signInForm.Show(); Application.Run(); } else { dbConnection.CloseConnection(); MessageBox.Show(null, "Error connecting to the database. Please try again.", "Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void btnReset_Click(object sender, EventArgs e) { if (txtNewPassword.TextLength < 1) { MessageBox.Show(this, "Please enter a new password.", "Password Reset", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (txtConfirmPassword.TextLength < 1) { MessageBox.Show(this, "Please confirm the new password.", "Password Reset", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (!txtNewPassword.Text.Equals(txtConfirmPassword.Text)) { MessageBox.Show(this, "The paasswords entered do not match.", "Password Reset", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (dbConnection.OpenConnection()) { MySqlCommand command = new MySqlCommand(); command.Connection = dbConnection.getConnection(); command.CommandText = "UPDATE user_login SET password = @password WHERE member_id=@id"; command.Parameters.AddWithValue("@password", txtConfirmPassword.Text); command.Parameters.AddWithValue("@id", id); command.ExecuteNonQuery(); dbConnection.CloseConnection(); command.Dispose(); MessageBox.Show(this, "Password successfully updated.", "Password Reset", MessageBoxButtons.OK, MessageBoxIcon.Information); this.Close(); } }
private void btnCreate_OnClick(object sender, EventArgs e) { try { string name = nameBox.Text; string phone = phoneBox.Text; string email = emailBox.Text; if (name == null || phone == null || email == null || filePath == null) { throw new Exception(); } string fileFolder = ""; if (dbConnection.OpenConnection()) { MySqlCommand command = new MySqlCommand(); command.Connection = dbConnection.getConnection(); command.CommandText = "SELECT job_path FROM dropboxes WHERE job_id = @job_id"; command.Parameters.AddWithValue("@job_id", jobId); using (MySqlDataReader dr = command.ExecuteReader()) { try { while (dr.Read()) { fileFolder = dr[0].ToString(); } } catch (Exception ex) { System.Console.WriteLine("Not found..."); } } dbConnection.CloseConnection(); command.Dispose(); } string newApplication = fileFolder + "/" + fileName; WebRequest request = WebRequest.Create(newApplication); request.Method = WebRequestMethods.Ftp.UploadFile; request.Credentials = new NetworkCredential("ftpuser", "pickleparty"); byte[] bytes = System.IO.File.ReadAllBytes(@filePath); request.ContentLength = bytes.Length; using (Stream request_stream = request.GetRequestStream()) { request_stream.Write(bytes, 0, bytes.Length); request_stream.Close(); } if (dbConnection.OpenConnection()) { MySqlCommand command = new MySqlCommand(); command.Connection = dbConnection.getConnection(); command.CommandText = "INSERT INTO applications (job_id, name, phone, email, app_path, approved) VALUES (@job_id, @name, @phone, @email, @app_path, @approved)"; command.Parameters.AddWithValue("@job_id", jobId); Console.WriteLine("JOB ID" + jobId); command.Parameters.AddWithValue("@name", name); command.Parameters.AddWithValue("@phone", phone); command.Parameters.AddWithValue("@email", email); command.Parameters.AddWithValue("@app_path", (fileFolder + "/" + fileName)); command.Parameters.AddWithValue("@approved", "No"); command.ExecuteNonQuery(); dbConnection.CloseConnection(); command.Dispose(); } MessageBox.Show("Application successfully created!", "EARS System Message", MessageBoxButtons.OK, MessageBoxIcon.Information); this.Close(); } catch (Exception x) { Console.WriteLine("All fields must be complete..."); Console.WriteLine(x); } }
private void btnSignIn_Click(object sender, EventArgs e) { if (txtUsername.TextLength > 0) { if (txtPassword.TextLength > 0) { if (dbConnection.OpenConnection()) { int memberId = 0; String username = ""; String password = ""; int securityStatus = 0; MySqlCommand command = new MySqlCommand(); command.Connection = dbConnection.getConnection(); command.CommandText = "SELECT member_id, username, password, security_status FROM user_login WHERE username=@username"; command.Parameters.AddWithValue("@username", this.txtUsername.Text); using (MySqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { memberId = Int32.Parse(dr[0].ToString()); username = dr[1].ToString(); password = dr[2].ToString(); securityStatus = Int32.Parse(dr[3].ToString()); } } dbConnection.CloseConnection(); command.Dispose(); if (username.Equals(txtUsername.Text) && password.Equals(txtPassword.Text)) { switch (securityStatus) { case 1: // System administrator SystemAdminForm systemAdminForm = new SystemAdminForm(dbConnection); systemAdminForm.Show(); signIn = true; this.Close(); break; case 2: // Secretary case 3: // member case 4: // chair MemberForm memberForm = new MemberForm(dbConnection, securityStatus, memberId); memberForm.Show(); signIn = true; this.Close(); break; } } else { MessageBox.Show(this, "Invalid username or password.", "Sign In", MessageBoxButtons.OK, MessageBoxIcon.Error); } } } else { MessageBox.Show(this, "Please enter a valid password.", "Invalid Password", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { MessageBox.Show(this, "Please enter a valid username.", "Invalid Username", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void btnFinish_Click(object sender, EventArgs e) { if (txtFirstName.TextLength < 1 || txtLastName.TextLength < 1 || txtUsername.TextLength < 1 || txtPassword.TextLength < 1) { MessageBox.Show(this, "You must fill in all fields.", "Add User", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } int securityStatus = 0; switch (securityComboBox.Text) { case "Administrator": securityStatus = 1; break; case "Secretary": securityStatus = 2; break; case "Committee Member": securityStatus = 3; break; case "Chairperson": securityStatus = 4; break; } if (dbConnection.OpenConnection()) { int id = 0; MySqlCommand command = new MySqlCommand(); command.Connection = dbConnection.getConnection(); command.CommandText = "INSERT INTO user_login (username, password, security_status) VALUES (@username, @password, @securityStatus)"; command.Parameters.AddWithValue("@username", txtUsername.Text); command.Parameters.AddWithValue("@password", txtPassword.Text); command.Parameters.AddWithValue("@securityStatus", securityStatus); command.ExecuteNonQuery(); command.CommandText = "SELECT member_id FROM user_login WHERE username=@username"; using (MySqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { id = Int32.Parse(dr[0].ToString()); } } command.CommandText = "INSERT INTO member_info (member_id, f_name, l_name, position) VALUES (@id, @firstName, @lastName, @position)"; command.Parameters.AddWithValue("@id", id); command.Parameters.AddWithValue("@firstName", txtFirstName.Text); command.Parameters.AddWithValue("@lastName", txtLastName.Text); command.Parameters.AddWithValue("@position", securityComboBox.Text); command.ExecuteNonQuery(); dbConnection.CloseConnection(); command.Dispose(); MessageBox.Show(this, "User successfully added.", "User Added", MessageBoxButtons.OK, MessageBoxIcon.Information); this.Close(); } }