private void btnSearch_Click(object sender, EventArgs e)
{
DataSet mDataSet = new DataSet();
if (tbEnrollmentKey.Text != "")
{
mDataSet = DbHandler.getInstance().Select("SELECT * FROM student WHERE enrollment='" + tbEnrollmentKey.Text + "'");
if (mDataSet.Tables[0].Rows.Count != 0)
{
UserHelper.getInstance().studentID = int.Parse(mDataSet.Tables[0].Rows[0][0].ToString());
tbStudentName.Text = mDataSet.Tables[0].Rows[0][1].ToString();
tbStudentClass.Text = mDataSet.Tables[0].Rows[0][4].ToString();
dtpBorrowDate.Value = DateTime.Now;
}
else
{
MessageBox.Show("Please enter valid enrollment key!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
Clear();
}
}
else
{
MessageBox.Show("Please enter valid enrollment key!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
Clear();
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
string enrollment = tbEnrollment.Text;
if (enrollment != "")
{
DataSet mDataSet = new DataSet();
mDataSet = DbHandler.getInstance().Select("SELECT * FROM student WHERE enrollment='" + enrollment + "'");
if (mDataSet.Tables[0].Rows.Count > 0)
{
int studentId = int.Parse(mDataSet.Tables[0].Rows[0][0].ToString());
dgvIssuedBooks.DataSource = DbHandler.getInstance().populateDataGridView("SELECT borrowID, takenDate, bookName, studentName FROM BookBorrow, books, student WHERE BookBorrow.bookID = books.bookID AND BookBorrow.studentID=student.studentID AND student.studentID=" + studentId + " AND BookBorrow.studentID=" + studentId + "");
}
else
{
MessageBox.Show("Enter a valid enrollment key!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
Clear();
}
}
else
{
MessageBox.Show("Enter a valid enrollment key!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
Clear();
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Are you sure you want to delete item?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
DbHandler.getInstance().Delete("DELETE FROM student WHERE studentID=" + this.id + "");
MessageBox.Show("Item deleted!\n\n Refresh table!", "Notification", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void BinData()
{
DataSet mDataSet = new DataSet();
mDataSet = DbHandler.getInstance().Select("SELECT * FROM books");
cbBooks.DataSource = mDataSet.Tables[0];
cbBooks.ValueMember = "bookID";
cbBooks.DisplayMember = "bookName";
cbBooks.Enabled = true;
}
private void btnSaveClick()
{
if (tbBookName.Text != "" && tbBookAuthor.Text != "" && tbBookPublication.Text != "" && tbBookQuantity.Text != "")
{
string sqlQuery = "INSERT INTO books (bookName, bookAuthor, bookPublication, bookQuantity) VALUES ('" + tbBookName.Text + "', '" + tbBookAuthor.Text + "', '" + tbBookPublication.Text + "', " + int.Parse(tbBookQuantity.Text) + ")";
DbHandler.getInstance().Insert(sqlQuery);
MessageBox.Show("Book Inserted", "Notification", MessageBoxButtons.OK, MessageBoxIcon.Information);
resetFields();
}
else
{
MessageBox.Show("One or more required fields are empty", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private void tbSearchStudents_TextChanged(object sender, EventArgs e)
{
if (tbSearchStudents.Text != "")
{
DataSet mDataSet = new DataSet();
mDataSet = DbHandler.getInstance().Select("SELECT * FROM student WHERE studentName LIKE '%" + tbSearchStudents.Text + "%'");
dgvViewStudents.DataSource = mDataSet.Tables[0];
}
else
{
makeView();
}
}
private void login()
{
mDataSet = new DataSet();
string userName = tbUserName.Text;
string password = tbPassword.Text;
mDataSet = DbHandler.getInstance().Select("SELECT * FROM loginTable WHERE username = '******' AND password = '******'");
if (mDataSet.Tables[0].Rows.Count == 0)
{
MessageBox.Show("Wrong username or password!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
UserHelper.getInstance().userID = int.Parse(mDataSet.Tables[0].Rows[0][0].ToString());
ProgramManager.getInstance().SwitchScreen(this, new Dashboard());
}
}
private void btnReturn_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Are you sure you want to return the book?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
DbHandler.getInstance().Delete("DELETE FROM BookBorrow WHERE borrowID=" + UserHelper.getInstance().borrowBookID + "");
DataSet mDataSet = new DataSet();
mDataSet = DbHandler.getInstance().Select("SELECT bookQuantity FROM books WHERE bookID=" + UserHelper.getInstance().bookID + "");
int quantity = int.Parse(mDataSet.Tables[0].Rows[0][0].ToString());
quantity = quantity + 1;
DbHandler.getInstance().Update("UPDATE books SET bookQuantity=" + quantity + " WHERE bookID=" + UserHelper.getInstance().bookID + "");
MessageBox.Show("You have returned your book!", "Notification", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Please, make a valid \n\nbook selection inside table!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private void dgvViewStudents_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex == -1)
{
MessageBox.Show("Wrong selection!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
if (dgvViewStudents.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString() != "")
{
setComboBoxData();
int id = int.Parse(dgvViewStudents.Rows[e.RowIndex].Cells[0].Value.ToString());
panelUpdateStudents.Visible = true;
DataSet mDataSet = new DataSet();
mDataSet = DbHandler.getInstance().Select("SELECT * FROM student WHERE studentID = " + id);
tbStudentNameUpdate.Text = mDataSet.Tables[0].Rows[0][1].ToString();
dtpBirthdateUpdate.Value = DateTime.Parse(mDataSet.Tables[0].Rows[0][2].ToString());
if (mDataSet.Tables[0].Rows[0][3].ToString() == "Male")
{
cbGenderUpdate.SelectedValue = "Male";
}
else
{
cbGenderUpdate.SelectedValue = "Female";
}
tbStudentClassUpdate.Text = mDataSet.Tables[0].Rows[0][4].ToString();
this.id = Int64.Parse(mDataSet.Tables[0].Rows[0][0].ToString());
}
else
{
MessageBox.Show("Wrong selection!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
if (tbStudentNameUpdate.Text == "" || tbStudentClassUpdate.Text == "" || cbGenderUpdate.SelectedIndex == 0)
{
MessageBox.Show("Some of the fields are wrong", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
if (MessageBox.Show("Are you sure you want to update data?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
string studentNameUpdate = tbStudentNameUpdate.Text;
string studentBirthdateUpdate = dtpBirthdateUpdate.Value.ToShortDateString();
string studentGenderUpdate = cbGenderUpdate.SelectedValue.ToString();
string studentClassUpdate = tbStudentClassUpdate.Text;
DbHandler.getInstance().Update("UPDATE student SET studentName='" + studentNameUpdate + "', birthdate='" + studentBirthdateUpdate + "',gender='" + studentGenderUpdate + "', class='" + studentClassUpdate + "' WHERE studentID =" + this.id + "");
MessageBox.Show("Student updated!\n\n Please refresh data.", "Notification", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Student is not updated", "Notification", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
private void dgvIssuedBooks_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (dgvIssuedBooks.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString() != "")
{
string builder = "";
DataSet mDataSet = new DataSet();
UserHelper.getInstance().borrowBookID = int.Parse(dgvIssuedBooks.Rows[e.RowIndex].Cells[0].Value.ToString());
mDataSet = DbHandler.getInstance().Select("SELECT * FROM BookBorrow WHERE borrowID=" + UserHelper.getInstance().borrowBookID + "");
builder = builder + mDataSet.Tables[0].Rows[0][1].ToString() + " ";
UserHelper.getInstance().studentID = int.Parse(mDataSet.Tables[0].Rows[0][5].ToString());
UserHelper.getInstance().bookID = int.Parse(mDataSet.Tables[0].Rows[0][4].ToString());
mDataSet = DbHandler.getInstance().Select("SELECT * FROM books WHERE bookID=" + UserHelper.getInstance().bookID + "");
builder = builder + " " + mDataSet.Tables[0].Rows[0][1].ToString();
tbBookData.Text = builder;
}
else
{
MessageBox.Show("Wrong selection!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
if (tbBookAuthor.Text == "" || tbBookName.Text == "" || tbBookPublication.Text == "" || tbBookQuantity.Text == "")
{
MessageBox.Show("Some of the fields are empty", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
if (MessageBox.Show("Are you sure you want to update data?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
string bookNameUpdate = tbBookName.Text;
string bookAuthorUpdate = tbBookAuthor.Text;
string bookPublicationUpdate = tbBookPublication.Text;
int bookQuantityUpdate = int.Parse(tbBookQuantity.Text.ToString());
DbHandler.getInstance().Update("UPDATE books SET bookName='" + bookNameUpdate + "', bookAuthor='" + bookAuthorUpdate + "',bookPublication='" + bookPublicationUpdate + "', bookQuantity=" + bookQuantityUpdate + " WHERE bookID =" + this.id + "");
MessageBox.Show("Book updated!\n\n Please refresh data.", "Notification", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Books are not updated", "Notification", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
private void dgvViewBooks_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (dgvViewBooks.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString() != "")
{
int id = int.Parse(dgvViewBooks.Rows[e.RowIndex].Cells[0].Value.ToString());
panelBookAlter.Visible = true;
DataSet mDataSet = new DataSet();
mDataSet = DbHandler.getInstance().Select("SELECT * FROM books WHERE bookID = " + id);
tbBookName.Text = mDataSet.Tables[0].Rows[0][1].ToString();
tbBookAuthor.Text = mDataSet.Tables[0].Rows[0][2].ToString();
tbBookPublication.Text = mDataSet.Tables[0].Rows[0][3].ToString();
tbBookQuantity.Text = mDataSet.Tables[0].Rows[0][4].ToString();
this.id = Int64.Parse(mDataSet.Tables[0].Rows[0][0].ToString());
}
else
{
MessageBox.Show("Wrong selection!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void makeView()
{
tbSearchStudents.Text = "";
dgvViewStudents.DataSource = DbHandler.getInstance().populateDataGridView("SELECT * FROM student");
}
private void makeView()
{
tbBookNameSearch.Text = "";
dgvViewBooks.DataSource = DbHandler.getInstance().populateDataGridView("SELECT * FROM books");
}
