asd

Connection.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace LibrarySystem
{
    class Connection
    {
        private static string server = "";
        private static string database = "";
        private static string uid = "";
        private static string password = "";
        private static string connstr = "";

        public static string  getConnectionStr(){
            return connstr = "SERVER=" + server + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";
        }


        public static void setServer(string server)
        {
            Connection.server = server;
        }
        public static void setDatabase(string database)
        {
            Connection.database = database;
        }

        public static void setUid(string uid)
        {
            Connection.uid = uid;
        }
        public static void setPassword(string pass)
        {
            Connection.password = pass;
        }
    }
}

DatabaseHelper.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
namespace LibrarySystem
{
    class DatabaseHelper
    {
        MySqlConnection con;

        public DatabaseHelper(string connstr)
        {
            con = new MySqlConnection(connstr);

        }

        public void Instantiate()
        {



        }

        public MySqlConnection getConnection()
        {
            return this.con;
        }

        public void executecmd(string query)
        {
            MySqlCommand cmd = new MySqlCommand(query, getConnection());
            cmd.ExecuteNonQuery();


        }
        public bool openConnection()
        {
            try
            {
                con.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message.ToString());
                return false;
            }
        }


        public bool CloseConnection()
        {
            try
            {
                con.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }
    }

}

frmForgot.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
namespace LibrarySystem
{
    public partial class frmForgot : Form
    {
        DatabaseHelper dbhelper;
        public frmForgot()
        {
            InitializeComponent();
            dbhelper = new DatabaseHelper(Connection.getConnectionStr());
        }
        private string answer = "";

        private void txtanswer_TextChanged(object sender, EventArgs e)
        {

        }


        private void txtusername_TextChanged(object sender, EventArgs e)
        {
            dbhelper.openConnection();
            string query = "SELECT Question, Answer from answersView WHERE user_name=@user";
            MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
            cmd.Parameters.AddWithValue("user", txtusername.Text);
            MySqlDataReader reader = cmd.ExecuteReader();
            int count = 0;
            while (reader.Read())
            {
                count++;
                if (count == 1)
                {
                    txtquestion.Text = "Question: " + reader["Question"].ToString();
                    answer = reader["Answer"].ToString();
                }
            }
            if (count == 0)
            {
                txtquestion.Text = "Username: " + txtusername.Text + " not found in database!";
                answer = "";
            }
            dbhelper.CloseConnection();
        }

        private void btnRetrieve_Click(object sender, EventArgs e)
        {
            if (txtanswer.Text == answer)
            {
                if(txtpassword.Text == txtnewpassword.Text)
                {
                    if(txtpassword.Text != "")
                    {
                        if (dbhelper.openConnection())
                        {
                            try
                            {

                                string query = "UPDATE tblaccounts SET pass_word = @pass WHERE user_name = @user";
                                MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                                cmd.Parameters.AddWithValue("user", txtusername.Text);
                                cmd.Parameters.AddWithValue("pass", MD5Hasher.GetMd5Hash(txtnewpassword.Text));
                                cmd.ExecuteNonQuery();
                                MessageBox.Show("Your password is changed!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            }
                            catch (Exception ex)
                            {
                                MessageBox.Show(ex.Message);
                            }
                        }

                    }
                    else
                    {
                        MessageBox.Show("New password cant\'t be blank!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Warning);

                    }


                }
                else
                {
                    MessageBox.Show("New password mismatched!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
                dbhelper.CloseConnection();
            }
            else
            {
                MessageBox.Show("Answer mismatched!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }
    }
}

frmLibrarian.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
namespace LibrarySystem
{
    public partial class frmLibrarian : Form
    {
        DatabaseHelper dbhelper;
        DataTable books;
        DataTable borrowedBooks;
        string accid = "";
        bool editing = false;
        string tempsection = "";
        string tempgrade= "";
        public frmLibrarian()
        {
            InitializeComponent();
            Connection.setServer("127.0.0.1");
            Connection.setDatabase("dblibrary");
            Connection.setUid("root");
            Connection.setPassword("");

            dbhelper = new DatabaseHelper(Connection.getConnectionStr());
            loadBooks();
            addEditButton();
            getName();
            loadAccountInfo();
            loadBorrowedBooks();
            loadsecurity();

            cboagrade.DropDownStyle = ComboBoxStyle.DropDownList;
            cboasecurity.DropDownStyle = ComboBoxStyle.DropDownList;
            cbotype.DropDownStyle = ComboBoxStyle.DropDownList;

            cbotype.SelectedIndex = 0;
            cboasecurity.SelectedIndex = 0;
            cboagrade.SelectedIndex = 0;

            loadaedAccounts("student", "");
        }

        private void loadBooks()
        {

            if (dbhelper.openConnection())
            {
                string query = "SELECT * FROM tblbooks";
                MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                books = new DataTable();
                da.Fill(books);
                dgvBooks.DataSource = books;
                dgvAddBook.DataSource = books;
                dbhelper.CloseConnection();
            }
            if (dbhelper.openConnection())
            {
                int noOfDistinctBook = 0;
                string query = "Select COUNT(*) as NoOfBooks from tblbooks";
                MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    noOfDistinctBook = int.Parse(reader["NoOfBooks"].ToString());
                }
                dbhelper.CloseConnection();
                lblbookcount.Text = "Book Count: " + noOfDistinctBook.ToString();
            }
        }

        private void getName()
        {
            if (dbhelper.openConnection())
            {
                string fullname = "";
                string query = "Select CONCAT(last_name, ',' , first_name, ' ', middle_name) as FullName from tbllibrarian WHERE AccountID=@id";
                MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                cmd.Parameters.AddWithValue("id", GlobalVar.user_id);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    fullname = reader["Fullname"].ToString();
                }
                dbhelper.CloseConnection();
                lblWelcome.Text = "Welcome: " + fullname;
            }
        }

        private void frmLibrarian_FormClosing(object sender, FormClosingEventArgs e)
        {
            DialogResult dr = MessageBox.Show("Are you sure you want to logout?", "Logout", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (dr == DialogResult.No)
            {
                e.Cancel = true;

            }
        }

        private void btnLogout_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void loadAccountInfo()
        {
            try
            {
                if (dbhelper.openConnection())
                {
                    string query = "SELECT * FROM tbllibrarian WHERE AccountID= @id";
                    MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                    cmd.Parameters.AddWithValue("id", GlobalVar.user_id);
                    MySqlDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        fname.Text = reader["first_name"].ToString();
                        mname.Text = reader["middle_name"].ToString();
                        lname.Text = reader["last_name"].ToString();
                    }
                    dbhelper.CloseConnection();
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }
        private void changePassword(string currpass, string newpass, string confirmpass)
        {
            DialogResult dr = MessageBox.Show("Are you sure you want to change your password?", "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (dr == DialogResult.Yes)
            {
                if (currpass == "" || newpass == "" || confirmpass == "")
                {
                    MessageBox.Show("Fields Can\'t be blank!", "Failed", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
                else
                {
                    if (currpass == GlobalVar.user_pass)
                    {
                        if (newpass == confirmpass)
                        {
                            if (dbhelper.openConnection())
                            {
                                string query = "UPDATE tblaccounts set pass_word=@newpass WHERE AccountID=@id";
                                MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                                cmd.Parameters.AddWithValue("id", GlobalVar.user_id);
                                cmd.Parameters.AddWithValue("newpass", MD5Hasher.GetMd5Hash(newpass));
                                cmd.ExecuteNonQuery();
                                dbhelper.CloseConnection();

                            }
                            MessageBox.Show("You have successfully changed your password!", "Succes", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            GlobalVar.user_pass = newpass;
                            txtconfirmpass.Text = "";
                            txtcurrpass.Text = "";
                            txtnewpass.Text = "";
                        }
                        else
                        {
                            MessageBox.Show("New password mismatched! Please Retype your new password!", "Failed", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        }
                    }
                    else
                    {
                        MessageBox.Show("Current password mismatched! Please Retype your current password!", "Failed", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    }
                }
            }


        }
        private void loadBorrowedBooks()
        {
            if (dbhelper.openConnection())
            {
                string query = "SELECT Concat(s.last_name, ',', s.first_name, ' ', s.middle_name ) as 'Borrower', BookName, Issuer, Quantity, DateBorrowed, DueDate FROM borrowedBooksView INNER JOIN tblstudents as s ON AccountID = BorrowerID";
                MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                cmd.Parameters.AddWithValue("id", GlobalVar.user_id);
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                borrowedBooks = new DataTable();
                da.Fill(borrowedBooks);
                dgvborrowed.DataSource = borrowedBooks;
                dbhelper.CloseConnection();
            }
            if (dbhelper.openConnection())
            {
                int noOfDistinctBook = 0;
                string query = "Select COUNT(*) as NoOfBooks from borrowedBooksView";
                MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    noOfDistinctBook = int.Parse(reader["NoOfBooks"].ToString());
                }
                dbhelper.CloseConnection();
                lblBookCountBorrowed.Text = "Book Count: " + noOfDistinctBook.ToString();
            }
        }
        private void changeUsername(string newusername)
        {
            DialogResult dr = MessageBox.Show("Are you sure you want to change your username?", "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (dr == DialogResult.Yes)
            {
                int count = 0;
                if (dbhelper.openConnection())
                {
                    string query = "Select COUNT(*) as Total from tblaccounts WHERE user_name=@user";
                    MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                    cmd.Parameters.AddWithValue("user", newusername);
                    MySqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        count = int.Parse(reader["Total"].ToString());
                    }
                    dbhelper.CloseConnection();
                }
                MessageBox.Show(count.ToString());
                if (count == 0)
                {
                    if (dbhelper.openConnection())
                    {
                        string query = "UPDATE tblaccounts set user_name=@newuser WHERE AccountID=@id";
                        MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                        cmd.Parameters.AddWithValue("id", GlobalVar.user_id);
                        cmd.Parameters.AddWithValue("newuser", newusername);
                        cmd.ExecuteNonQuery();
                        dbhelper.CloseConnection();

                    }
                    MessageBox.Show("You have successfully changed your username!", "Succes", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    GlobalVar.user_logged = txtuser.Text;
                    txtuser.Text = "";
                }
                else
                {
                    MessageBox.Show("It seems that your preferred username is already taken!", "Taken", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }
        }

        private void btnchangeusername_Click(object sender, EventArgs e)
        {
            changeUsername(txtuser.Text);
        }

        private void btnpass_Click(object sender, EventArgs e)
        {
            changePassword(txtcurrpass.Text, txtnewpass.Text, txtconfirmpass.Text);
        }

        private void btnedit_Click(object sender, EventArgs e)
        {
            if (btnedit.Text == "Edit")
            {
                fname.ReadOnly = false;
                mname.ReadOnly = false;
                lname.ReadOnly = false;


                btnedit.Text = "Save";
            }
            else if (btnedit.Text == "Save")
            {

                if (mname.Text == "" || fname.Text == "" || lname.Text == "")
                {
                    MessageBox.Show("Please fill up necessary fields!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
                else
                {
                    if (dbhelper.openConnection())
                    {
                        string query = "UPDATE tbllibrarian set first_name=@fname, middle_name=@mname, last_name=@lname WHERE AccountID=@id";
                        MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                        cmd.Parameters.AddWithValue("fname", fname.Text);
                        cmd.Parameters.AddWithValue("mname", mname.Text);
                        cmd.Parameters.AddWithValue("lname", lname.Text);
                        cmd.Parameters.AddWithValue("id", GlobalVar.user_id);
                        cmd.ExecuteNonQuery();
                        dbhelper.CloseConnection();

                    }
                    MessageBox.Show("Account information updated!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);

                    fname.ReadOnly = true;
                    mname.ReadOnly = true;
                    lname.ReadOnly = true;
                    btnedit.Text = "Edit";

                    getName();
                }
            }
        }

        private void btnAddacount_Click(object sender, EventArgs e)
        {
            if (txtapassword.Text == txtaconfirm.Text)
            {
                if (dbhelper.openConnection())
                {
                    if(cbotype.Text == "student")
                    {
                        string query = "Insert into tblaccounts(user_name, pass_word, Account_Type) VALUES (@user, @pass, @type);Insert into tblstudents(AccountID, first_name, middle_name, last_name, grade, section) VALUES (LAST_INSERT_ID(), @fname , @mname, @lname , @grade , @section )";
                        MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                        cmd.Parameters.AddWithValue("user", txtausername.Text);
                        cmd.Parameters.AddWithValue("pass", MD5Hasher.GetMd5Hash(txtapassword.Text));
                        cmd.Parameters.AddWithValue("type", cbotype.Text);
                        cmd.Parameters.AddWithValue("fname", txtafname.Text);
                        cmd.Parameters.AddWithValue("mname", txtamname.Text);
                        cmd.Parameters.AddWithValue("lname", txtaLname.Text);
                        cmd.Parameters.AddWithValue("grade", cboagrade.Text);
                        cmd.Parameters.AddWithValue("section", txtasection.Text);
                        cmd.ExecuteNonQuery();

                    }
                    else if(cbotype.Text == "librarian")
                    {
                        string query = "Insert into tblaccounts(user_name, pass_word, Account_Type) VALUES (@user, @pass, @type);Insert into tbllibrarian(AccountID, first_name, middle_name, last_name) VALUES (LAST_INSERT_ID(), @fname , @mname, @lname)";
                        MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                        cmd.Parameters.AddWithValue("user", txtausername.Text);
                        cmd.Parameters.AddWithValue("pass", MD5Hasher.GetMd5Hash(txtapassword.Text));
                        cmd.Parameters.AddWithValue("type", cbotype.Text);
                        cmd.Parameters.AddWithValue("fname", txtafname.Text);
                        cmd.Parameters.AddWithValue("mname", txtamname.Text);
                        cmd.Parameters.AddWithValue("lname", txtaLname.Text);
                        cmd.ExecuteNonQuery();
                    }


                    string query2 = "INSERT INTO tblanswers(AccountID, QuestionID, Answer) VALUES (LAST_INSERT_ID(), (SELECT tblsecques.QuestionID FROM tblsecques WHERE Question=CONVERT(@question USING utf8)), @answer)";
                    MySqlCommand cmd2 = new MySqlCommand(query2, dbhelper.getConnection());
                    cmd2.Parameters.AddWithValue("question", cboasecurity.Text);
                    cmd2.Parameters.AddWithValue("answer", txtaanswer.Text);
                    cmd2.ExecuteNonQuery();
                }

                dbhelper.CloseConnection();
                MessageBox.Show("Added new Account!");

                dgvaedAccount.Rows.Clear();
                loadaedAccounts(cbosearchtype.Text, "");
                clearFields();

            }
            else
            {
                MessageBox.Show("Password do not match!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }


        private void loadsecurity()
        {
            if (dbhelper.openConnection())
            {
                string query = "SELECT * from tblsecques";
                MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    cboasecurity.Items.Add(reader.GetString(1));
                }
            }
            dbhelper.CloseConnection();

        }

        private void cbotype_SelectedIndexChanged(object sender, EventArgs e)
        {
            if(editing == false)
            {
                if (cbotype.Text == "librarian")
                {
                    cboagrade.Text = "";
                    txtasection.Text = "";
                    cboagrade.Enabled = false;
                    txtasection.ReadOnly = true;
                }
                else if (cbotype.Text == "student")
                {
                    cboagrade.SelectedIndex = 0;
                    txtasection.Text = tempsection;
                    cboagrade.Enabled = true;
                    txtasection.ReadOnly = false;
                }
            }
            else
            {
                if (cbotype.Text == "librarian")
                {
                    cboagrade.Text = "";
                    txtasection.Text = "";
                    cboagrade.Enabled = false;
                    txtasection.ReadOnly = true;

                    txtausername.Text = "Owner of account can only change username!";
                    txtapassword.Text = "Owner of account can only change password!";
                }
                else if (cbotype.Text == "student")
                {
                    cboagrade.Text = tempgrade;
                    txtasection.Text = tempsection;

                    cboagrade.Enabled = true;
                    txtasection.ReadOnly = false;
                }
            }
        }


        private void loadaedAccounts(string type, string search)
        {
            dgvaedAccount.ColumnCount = 2;
            dgvaedAccount.Columns[0].Name = "#";
            dgvaedAccount.Columns[0].Width = 60;
            dgvaedAccount.Columns[1].Name = "FullName";


            if (type == "student")
            {
                if (dbhelper.openConnection())
                {
                    string query = "";
                    if(search != "")
                    {
                        query = string.Format("SELECT * FROM studentsView WHERE FullName Like '%{0}%'", search);
                    }
                    else
                    {
                        query = "SELECT * FROM studentsView";
                    }
                    MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                    MySqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        string[] info = { reader["AccountID"].ToString(), reader["FullName"].ToString() };
                        dgvaedAccount.Rows.Add(info);


                    }
                }
                dbhelper.CloseConnection();

            }
            else if (type == "librarian")
            {
                if (dbhelper.openConnection())
                {
                    string query = "";
                    if(search != "")
                    {
                        query = string.Format("SELECT * FROM librarianView WHERE Like '%{0}%'", search);
                    }
                    else
                    {
                        query = "SELECT * FROM librarianView";
                    }

                    MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                    MySqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        string[] info = { reader["AccountID"].ToString(), reader["FullName"].ToString() };
                        dgvaedAccount.Rows.Add(info);


                    }
                }
                dbhelper.CloseConnection();
            }
            DataGridViewButtonColumn btn = new DataGridViewButtonColumn();
            dgvaedAccount.Columns.Add(btn);
            btn.HeaderText = "Edit Data";
            btn.Text = "Edit";
            btn.Name = "btn";
            btn.UseColumnTextForButtonValue = true;

            DataGridViewButtonColumn btndel = new DataGridViewButtonColumn();
            dgvaedAccount.Columns.Add(btndel);
            btndel.HeaderText = "Delete Account";
            btndel.Text = "Delete";
            btndel.Name = "btndel";
            btndel.UseColumnTextForButtonValue = true;

        }

        private void cbosearchtype_SelectedIndexChanged(object sender, EventArgs e)
        {
            dgvaedAccount.Rows.Clear();

            loadaedAccounts(cbosearchtype.Text, "");
        }

        private void dgvaedAccount_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

            if (e.RowIndex >= 0 && e.RowIndex != dgvaedAccount.RowCount -1)
            {

                if(e.ColumnIndex == 2)
                {
                    editing = true;
                    if (editing)
                    {
                        btnCancel.Enabled = true;
                        btnSave.Enabled = true;
                        btnAddacount.Enabled = false;
                    }

                    DataGridViewRow row = this.dgvaedAccount.Rows[e.RowIndex];
                    accid = row.Cells["#"].Value.ToString();

                    txtausername.ReadOnly = true;
                    txtapassword.ReadOnly = true;
                    txtaconfirm.ReadOnly = true;

                    //display its data in
                    if (cbosearchtype.Text == "student")
                    {
                        if (dbhelper.openConnection())
                        {
                            string query = "SELECT * FROM tblstudents WHERE AccountID=@id";
                            MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                            cmd.Parameters.AddWithValue("id", accid);
                            MySqlDataReader reader = cmd.ExecuteReader();
                            while (reader.Read())
                            {
                                cbotype.Text = "student";
                                txtafname.Text = reader["first_name"].ToString();
                                txtamname.Text = reader["middle_name"].ToString();
                                txtaLname.Text = reader["last_name"].ToString();
                                cboagrade.Text = reader["grade"].ToString();
                                txtasection.Text = reader["section"].ToString();
                                txtausername.Text = "Owner of account can only change username!";
                                txtapassword.Text = "Owner of account can only change password!";
                                txtaconfirm.Text = "-----------";

                                tempsection = txtasection.Text;
                                tempgrade = cboagrade.Text;
                            }
                        }
                        dbhelper.CloseConnection();

                        //get security question and answer
                        if (dbhelper.openConnection())
                        {
                            string query = "SELECT * FROM answersView WHERE AccountID=@id";
                            MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                            cmd.Parameters.AddWithValue("id", accid);
                            MySqlDataReader reader = cmd.ExecuteReader();
                            while (reader.Read())
                            {
                                cboasecurity.Text = reader["Question"].ToString();
                                txtaanswer.Text = reader["Answer"].ToString();

                            }
                        }
                        dbhelper.CloseConnection();

                    }
                    else if (cbosearchtype.Text == "librarian")
                    {

                    }
                }else if (e.ColumnIndex == 3)
                {
                    DataGridViewRow row = this.dgvaedAccount.Rows[e.RowIndex];
                    accid = row.Cells["#"].Value.ToString();
                    string fullname = row.Cells["FullName"].Value.ToString();
                    int count = 0;

                    DialogResult dr = MessageBox.Show("Are you sure you want to delete account of " + fullname + "?","Delete Account", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                    if(dr == DialogResult.Yes)
                    {
                        if (dbhelper.openConnection())
                        {
                            string query = "Select Count(*) as Total From tblborrowed where BorrowerID=@id";
                            MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                            cmd.Parameters.AddWithValue("id", accid);
                            MySqlDataReader reader = cmd.ExecuteReader();

                            while (reader.Read())
                            {
                                count = int.Parse(reader["Total"].ToString());
                            }
                            dbhelper.CloseConnection();
                        }
                        if(count > 0)
                        {
                            MessageBox.Show("Account has loaned books!");
                        }
                        else if(count == 0)
                        {
                            if(cbosearchtype.Text == "librarian")
                            {
                                if (dbhelper.openConnection())
                                {
                                    string query = "Delete from tblaccounts where AccountID=@id;Delete From tblibrarian Where AccountID=@id; Delete From tblanswers Where AccountID=@id; Delete from tblloghistory where AccountID=@id";
                                    MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                                    cmd.Parameters.AddWithValue("id", accid);
                                    cmd.ExecuteNonQuery();
                                }
                            }
                            else if (cbosearchtype.Text == "student")
                            {
                                if (dbhelper.openConnection())
                                {
                                    string query = "Delete from tblaccounts where AccountID=@id;Delete From tblstudents Where AccountID=@id; Delete From tblanswers Where AccountID=@id; Delete from tblloghistory where AccountID=@id";
                                    MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                                    cmd.Parameters.AddWithValue("id", accid);
                                    cmd.ExecuteNonQuery();
                                }
                            }
                            dbhelper.CloseConnection();
                            dgvaedAccount.Rows.Clear();
                            loadaedAccounts(cbosearchtype.Text, "");
                            MessageBox.Show("Deleted Account!", "Sucess", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        }
                    }
                }
            }
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            editing = false;
            if (editing == false)
            {

                clearFields();
            }
        }

        private void clearFields()
        {
            btnCancel.Enabled = false;
            btnSave.Enabled = false;
            btnAddacount.Enabled = true;

            txtausername.ReadOnly = false;
            txtapassword.ReadOnly = false;
            txtaconfirm.ReadOnly = false;
            accid = "";

            cbotype.Text = "student";
            txtafname.Text = "";
            txtamname.Text = "";
            txtaLname.Text = "";
            cboagrade.Text = "7";
            txtasection.Text = "";
            txtausername.Text = "";
            txtapassword.Text = "";
            txtaconfirm.Text = "";

            txtaanswer.Text = "";
            cboasecurity.SelectedIndex = 0;

            tempgrade = "";
            tempsection = "";
        }

        private void txtasearch_TextChanged(object sender, EventArgs e)
        {
            dgvaedAccount.Rows.Clear();
            loadaedAccounts(cbosearchtype.Text, txtasearch.Text);
        }

        private void txtafname_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (!Char.IsControl(e.KeyChar) && !char.IsLetter(e.KeyChar) && !Char.IsWhiteSpace(e.KeyChar)) //text only input and spaces
                e.Handled = true;
            base.OnKeyPress(e);
        }

        private void adstxtsearch_TextChanged(object sender, EventArgs e)
        {
            try
            {
                DataView DV = new DataView(books);
                DV.RowFilter = string.Format(adscboSearchBy.Text + " LIKE '%{0}%'", adstxtsearch.Text);
                dgvBooks.DataSource = DV;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Invalid Input!" + ex.ToString());
            }
        }

      //===========Add new book
      private void addEditButton()
        {
            DataGridViewButtonColumn btn = new DataGridViewButtonColumn();
            dgvAddBook.Columns.Add(btn);
            btn.HeaderText = "Edit Data";
            btn.Text = "Edit";
            btn.Name = "btn";
            btn.UseColumnTextForButtonValue = true;
        }

        private void addtxtsearch_TextChanged(object sender, EventArgs e)
        {
            try
            {
                DataView DV = new DataView(books);
                DV.RowFilter = string.Format(addcbosearchby.Text + " LIKE '%{0}%'", addtxtsearch.Text);
                dgvAddBook.DataSource = DV;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Invalid Input!" + ex.ToString());
            }
        }
    }
}
frmStart.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
namespace LibrarySystem
{
    public partial class frmstart : Form
    {
        DatabaseHelper dbhelper;
        int trials = 3;
        public frmstart()
        {
            InitializeComponent();

            Connection.setServer("127.0.0.1");
            Connection.setDatabase("dblibrary");
            Connection.setUid("root");
            Connection.setPassword("");

            dbhelper = new DatabaseHelper(Connection.getConnectionStr());

            txttrials.Text = "Trials Left:             " + trials.ToString();
        }

        private void btnLogin_Click(object sender, EventArgs e)
        {
            if (txtusername.Text == "" || txtpassword.Text == "")
            {

                MessageBox.Show("Username / Password can't be blank!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {
                if (trials == 0 || trials <= 0)
                {
                    MessageBox.Show("You have used all your trials!, Please restart the application!", "Login Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                else
                {
                    GlobalVar.user_logged = txtusername.Text;

                    GlobalVar.user_pass = txtpassword.Text;
                    if (dbhelper.openConnection())
                    {
                        try
                        {
                            string query = "SELECT Account_Type, AccountID, Account_Type FROM tblaccounts WHERE user_name = @user AND pass_word = @pass";
                            MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                            cmd.Parameters.AddWithValue("@user", txtusername.Text);
                            cmd.Parameters.AddWithValue("@pass", MD5Hasher.GetMd5Hash(txtpassword.Text));
                            MySqlDataReader reader = cmd.ExecuteReader();
                            int count = 0;

                            while (reader.Read())
                            {
                                count++;
                                GlobalVar.user_type = reader["Account_Type"].ToString();
                                GlobalVar.user_id = reader["AccountID"].ToString();
                            }

                            if (count == 1)
                            {
                                MessageBox.Show("Login Succesful!");
                                trials = 3;
                                txttrials.Text = "Trials Left:             " + trials.ToString();
                                if (GlobalVar.user_type == "student")
                                {
                                    frmStudent std = new frmStudent();
                                    std.ShowDialog();
                                }
                                else if (GlobalVar.user_type == "librarian")
                                {
                                    frmLibrarian lib = new frmLibrarian();
                                    lib.ShowDialog();

                                }

                                txtusername.Text = "";
                                txtpassword.Text = "";
                            }
                            else if (count == 0)
                            {
                                MessageBox.Show("Login Failed!");
                                trials--;
                                txttrials.Text = "Trials Left:             " + trials.ToString();

                            }
                        }
                        catch (MySqlException ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                    }

                    dbhelper.CloseConnection();

                }
            }
        }

        private void lkForgot_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            frmForgot forg = new frmForgot();
            forg.ShowDialog();
        }
    }
}
frmStudents.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace LibrarySystem
{
    public partial class frmStudent : Form
    {
        DatabaseHelper dbhelper;
        DataTable books;
        DataTable borrowedBooks;
        public frmStudent()
        {
            InitializeComponent();
            Connection.setServer("127.0.0.1");
            Connection.setDatabase("dblibrary");
            Connection.setUid("root");
            Connection.setPassword("");
            dbhelper = new DatabaseHelper(Connection.getConnectionStr());
            loadBooks();
            loadBorrowedBooks();
            getName();
            loadAccountInfo();
        }

        private void loadBooks()
        {

            if (dbhelper.openConnection())
            {
                string query = "SELECT * FROM tblbooks";
                MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                books = new DataTable();
                da.Fill(books);
                DgvBooks.DataSource = books;
                dbhelper.CloseConnection();
            }
            //count number of books in sink
            if (dbhelper.openConnection())
            {
                int noOfDistinctBook = 0;
                string query = "Select COUNT(*) as NoOfBooks from tblbooks";
                MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    noOfDistinctBook = int.Parse(reader["NoOfBooks"].ToString());
                }
                dbhelper.CloseConnection();
                lblbookcount.Text = "Book Count: " + noOfDistinctBook.ToString();
            }
        }

        private void loadBorrowedBooks()
        {

            if (dbhelper.openConnection())
            {
                string query = "SELECT BookName, Issuer, Quantity, DateBorrowed, DueDate FROM borrowedBooksView WHERE BorrowerID=@id";
                MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                cmd.Parameters.AddWithValue("id", GlobalVar.user_id);
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                borrowedBooks = new DataTable();
                da.Fill(borrowedBooks);
                dgvBorrowed.DataSource = borrowedBooks;
                dbhelper.CloseConnection();
            }
            //count borrowed books
            if (dbhelper.openConnection())
            {
                int noOfDistinctBook = 0;
                string query = "Select COUNT(*) as NoOfBooks from borrowedBooksView WHERE BorrowerID=@id";
                MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                cmd.Parameters.AddWithValue("id", GlobalVar.user_id);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    noOfDistinctBook = int.Parse(reader["NoOfBooks"].ToString());
                }
                dbhelper.CloseConnection();
                lblBookCountBorrowed.Text = "Book Count: " + noOfDistinctBook.ToString();
            }
        }

        private void getName()
        {
            if (dbhelper.openConnection())
            {
                string fullname = "";
                string query = "Select CONCAT(last_name, ',' , first_name, ' ', middle_name) as FullName from tblstudents WHERE AccountID=@id";
                MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                cmd.Parameters.AddWithValue("id", GlobalVar.user_id);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    fullname = reader["Fullname"].ToString();
                }
                dbhelper.CloseConnection();
                lblWelcome.Text = "Welcome: " + fullname;
            }
        }

        private void btnLogout_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void frmStudent_FormClosing(object sender, FormClosingEventArgs e)
        {
            DialogResult dr = MessageBox.Show("Are you sure you want to logout?", "Logout", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if(dr == DialogResult.No)
            {
                e.Cancel = true;

            }
        }
        private void loadAccountInfo()
        {
            try
            {
                if (dbhelper.openConnection())
                {
                    string query = "SELECT * FROM tblstudents WHERE AccountID= @id";
                    MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                    cmd.Parameters.AddWithValue("id", GlobalVar.user_id);
                    MySqlDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        fname.Text = reader["first_name"].ToString();
                        mname.Text = reader["middle_name"].ToString();
                        lname.Text = reader["last_name"].ToString();
                        grade.Text = reader["grade"].ToString();
                        section.Text = reader["section"].ToString();
                    }
                    dbhelper.CloseConnection();
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }
        private void changePassword(string currpass, string newpass, string confirmpass)
        {
            DialogResult dr = MessageBox.Show("Are you sure you want to change your password?", "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (dr == DialogResult.Yes)
            {
                if (currpass == "" || newpass == "" || confirmpass == "")
                {
                    MessageBox.Show("Fields Can\'t be blank!", "Failed", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
                else
                {
                    if (currpass == GlobalVar.user_pass)
                    {
                        if (newpass == confirmpass)
                        {
                            if (dbhelper.openConnection())
                            {
                                string query = "UPDATE tblaccounts set pass_word=@newpass WHERE AccountID=@id";
                                MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                                cmd.Parameters.AddWithValue("id", GlobalVar.user_id);
                                cmd.Parameters.AddWithValue("newpass", MD5Hasher.GetMd5Hash(newpass));
                                cmd.ExecuteNonQuery();
                                dbhelper.CloseConnection();

                            }
                            MessageBox.Show("You have successfully changed your password!", "Succes", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            GlobalVar.user_pass = newpass;
                            txtconfirmpass.Text = "";
                            txtcurrpass.Text = "";
                            txtnewpass.Text = "";
                        }
                        else
                        {
                            MessageBox.Show("New password mismatched! Please Retype your new password!", "Failed", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        }
                    }
                    else
                    {
                        MessageBox.Show("Current password mismatched! Please Retype your current password!", "Failed", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    }
                }
            }


        }

        private void changeUsername(string newusername)
        {
            DialogResult dr = MessageBox.Show("Are you sure you want to change your username?", "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (dr == DialogResult.Yes)
            {
                //check if there is duplicate
                int count = 0;
                if (dbhelper.openConnection())
                {
                    string query = "Select COUNT(*) as Total from tblaccounts WHERE user_name=@user";
                    MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                    cmd.Parameters.AddWithValue("user", newusername);
                    MySqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        count = int.Parse(reader["Total"].ToString());
                    }
                    dbhelper.CloseConnection();
                }
                MessageBox.Show(count.ToString());
                if (count == 0)
                {
                    if (dbhelper.openConnection())
                    {
                        string query = "UPDATE tblaccounts set user_name=@newuser WHERE AccountID=@id";
                        MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                        cmd.Parameters.AddWithValue("id", GlobalVar.user_id);
                        cmd.Parameters.AddWithValue("newuser", newusername);
                        cmd.ExecuteNonQuery();
                        dbhelper.CloseConnection();

                    }
                    MessageBox.Show("You have successfully changed your username!", "Succes", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    GlobalVar.user_logged = txtnewuser.Text;
                    txtnewuser.Text = "";
                }
                else
                {
                    MessageBox.Show("It seems that your preferred username is already taken!", "Taken", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }
        }

        private void btnChangeUsername_Click(object sender, EventArgs e)
        {
            changeUsername(txtnewuser.Text);
        }

        private void btnchangepass_Click(object sender, EventArgs e)
        {
            changePassword(txtcurrpass.Text, txtnewpass.Text, txtconfirmpass.Text);
        }

        private void btnedit_Click(object sender, EventArgs e)
        {
            if(btnedit.Text == "Edit")
            {
                //enable text fields for modification
                fname.ReadOnly = false;
                mname.ReadOnly = false;
                lname.ReadOnly = false;
                grade.ReadOnly = false;
                section.ReadOnly = false;

                btnedit.Text = "Save";
            }
            else if(btnedit.Text == "Save")
            {
                //change account info



                if(mname.Text == "" || fname.Text =="" || lname.Text == "" || grade.Text == "" || section.Text == "")
                {
                    MessageBox.Show("Please fill up necessary fields!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
                else
                {
                    if(int.Parse(grade.Text) > 12 || int.Parse(grade.Text) < 7)
                    {
                        MessageBox.Show("Invalid Grade!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    }
                    else
                    {
                        if (dbhelper.openConnection())
                        {
                            string query = "UPDATE tblstudents set first_name=@fname, middle_name=@mname, last_name=@lname, grade=@grd , section=@section WHERE AccountID=@id";
                            MySqlCommand cmd = new MySqlCommand(query, dbhelper.getConnection());
                            cmd.Parameters.AddWithValue("fname", fname.Text);
                            cmd.Parameters.AddWithValue("mname", mname.Text);
                            cmd.Parameters.AddWithValue("lname", lname.Text);
                            cmd.Parameters.AddWithValue("grd", grade.Text);
                            cmd.Parameters.AddWithValue("section", section.Text);
                            cmd.Parameters.AddWithValue("id", GlobalVar.user_id);
                            cmd.ExecuteNonQuery();
                            dbhelper.CloseConnection();

                        }
                        MessageBox.Show("Account information updated!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);

                        fname.ReadOnly = true;
                        mname.ReadOnly = true;
                        lname.ReadOnly = true;
                        grade.ReadOnly = true;
                        section.ReadOnly = true;
                        btnedit.Text = "Edit";

                        getName();
                    }
                }

            }
        }
    }

}

GlobalVar.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LibrarySystem
{
    class GlobalVar
    {
        public static String user_logged = String.Empty;
        public static String user_type = "";
        public static String user_pass = "";
        public static String user_id = "";
    }
}

MD5Hasher.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
namespace LibrarySystem
{
    class MD5Hasher
    {
        public static string GetMd5Hash(string input)
        {
            MD5 md5Hash = MD5.Create();
            // Convert the input string to a byte array and compute the hash.
            byte[] data = md5Hash.ComputeHash(Encoding.UTF8.GetBytes(input));

            // Create a new Stringbuilder to collect the bytes
            // and create a string.
            StringBuilder sBuilder = new StringBuilder();

            // Loop through each byte of the hashed data
            // and format each one as a hexadecimal string.
            for (int i = 0; i < data.Length; i++)
            {
                sBuilder.Append(data[i].ToString("x2"));
            }

            // Return the hexadecimal string.
            return sBuilder.ToString();
        }

    }
}

Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;

namespace LibrarySystem
{
    static class Program
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new frmstart());
        }
    }
}