using

using
using
using
using
using
using

System;
System.Collections.Generic;
System.Linq;
System.Text;
System.Windows.Forms;
System.Data;
System.Data.OleDb;

namespace LibrarySystem
{
class ClassTools
{
//declare variables and objects
string cnString = "";
OleDbConnection cn;
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter adptr = new OleDbDataAdapter();
DataSet ds = new DataSet();
public ClassTools()
{
//provider for MS Access
cnString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=C:\Users\madri\Desktop\COPRO
THESIS\LibrarySystem\LibrarySystem\bin\Debug\Database.accdb";
cn = new OleDbConnection(cnString);
}
public void FillDataGrid(string sql, ref DataGridView dg)
{
try
{
cn.Open(); //opening connection to db
//instantiate command and adapter with query
cmd = new OleDbCommand(sql, cn);
adptr = new OleDbDataAdapter(cmd);
ds = new DataSet();
//remove previous data
adptr.Fill(ds);
// load result to dataset
dg.DataSource = "";
//clear contents of datagrid
dg.DataSource = ds.Tables[0]; //load records to datagrid
dg.AutoResizeColumns();
//autofit content
}
catch (Exception e)
{
MessageBox.Show("" + e.Message);
}
cn.Close();
}
public void ExecuteQuery(string sql)
{
try
{
cn.Open();
cmd = new OleDbCommand(sql, cn);
cmd.ExecuteNonQuery(); //implement sql command

}
catch (Exception e)
{
MessageBox.Show("" + e.Message);
}
cn.Close();
}
public OleDbDataReader RetrieveRecords(string sql, ref OleDbDataReader reader)
{
try
{
cn.Open();
cmd = new OleDbCommand(sql, cn);
reader = cmd.ExecuteReader();
return reader;
//cn.Close();
}
catch (Exception e)
{
MessageBox.Show("" + e.Message);
return null;
}
}
public void CloseConnection()
{
cn.Close();
}
}

using
using
using
using
using
using
using
using
using

System;
System.Collections.Generic;
System.ComponentModel;
System.Data;
System.Drawing;
System.Linq;
System.Text;
System.Windows.Forms;
System.Data.OleDb;

namespace LibrarySystem
{
public partial class FormLogin : Form
{
ClassTools cn = new ClassTools();
public FormLogin()
{
InitializeComponent();
}
private void btnLogin_Click(object sender, EventArgs e)
{
String user = "", pass = "";
user = txtUser.Text.Trim();
pass = txtPass.Text;
OleDbDataReader reader = null;
String sql = String.Format(@"SELECT * FROM Users
WHERE Username = '{0}' AND Password = '{1}'", user, pass);
reader = cn.RetrieveRecords(sql, ref reader);
if (reader.HasRows)
{
FormMain frm = new FormMain(user);
frm.Show();
this.Hide();
}
else
{
MessageBox.Show("Incorrect Username or Password", "Login");
}
cn.CloseConnection();
}
private void btnExit_Click(object sender, EventArgs e)
{
Application.Exit();
}
}

using
using
using
using
using
using
using
using
using
using

System;
System.Collections.Generic;
System.ComponentModel;
System.Data;
System.Drawing;
System.Linq;
System.Text;
System.Threading.Tasks;
System.Windows.Forms;
System.Data.OleDb;

namespace LibrarySystem
{
public partial class FormMain : Form
{
ClassTools cs = new ClassTools();
string[] selectedBook = new string[5];
string[] selectedBook2 = new string[5];
string[] selectedBook3 = new string[8];
string userAdmin, date;
System.DateTime dueDate;
System.TimeSpan borrowDate;
System.DateTime dateToday;
public FormMain()
{
InitializeComponent();
}
public FormMain(string admin)
{
InitializeComponent();
this.userAdmin = admin;
}
private void FormMain_Load(object sender, EventArgs e)
{
cs.FillDataGrid("SELECT ID,Book,Author,Category,Quantity FROM LibraryBook", ref
dgSearchBooks);
dgSearchBooks.MultiSelect = false;
dgSearchBooks.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dgSearchBooks.AutoResizeColumns();
cs.FillDataGrid("SELECT
ID,Username,Password,FirstName,MiddleName,LastName,Address,Course,YearLevel,[Book
Borrowed] FROM Users", ref dgUser);
dgUser.MultiSelect = false;
dgUser.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dgUser.AutoResizeColumns();
cs.FillDataGrid("SELECT * FROM LibraryBook", ref dgBook);
dgBook.MultiSelect = false;
dgBook.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dgBook.AutoResizeColumns();
if(userAdmin != "admin")
{

button1.Enabled = false;
button2.Enabled = false;
button5.Enabled = false;
button10.Enabled = false;
buttonAddAccount.Enabled = false;
buttonUpdateAccount.Enabled = false;
buttonDeleteAccount.Enabled = false;
tabPage4.Dispose();
tabPage1.Dispose();

"%'";

}
}
// Manage Book - Text Box Search
private void textBox1_TextChanged(object sender,
{
string sql = "";
switch (comboBoxSearch.SelectedIndex)
{
case 0:
sql = "SELECT * FROM LibraryBook WHERE
break;
case 1:
sql = "SELECT * FROM LibraryBook WHERE
break;
case 2:
sql = "SELECT * FROM LibraryBook WHERE
break;
}
cs.FillDataGrid(sql, ref dgSearchBooks);

EventArgs e)

Book LIKE '%" + textBox1.Text + "%'";

Author LIKE '%" + textBox1.Text + "%'";
Category LIKE '%" + textBox1.Text +

}
// Button Log-Out
private void button4_Click(object sender, EventArgs e)
{
FormLogin login = new FormLogin();
login.Show();
this.Hide();
}
// Manage Book - Button Exit
private void button3_Click(object sender, EventArgs e)
{
Application.Exit();
}
// Manage Book - Button Delete
private void button5_Click(object sender, EventArgs e)
{
try
{
DialogResult result = MessageBox.Show("Do you want to delete this book?",
"Confirmation", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
string sql = string.Format(@"DELETE FROM LibraryBook WHERE ID = {0}",
dgSearchBooks.CurrentRow.Cells[0].Value);
cs.ExecuteQuery(sql);
cs.FillDataGrid("SELECT ID,Book,Author,Category,Quantity FROM LibraryBook", ref
dgSearchBooks);
cs.CloseConnection();
cs.FillDataGrid("SELECT * FROM LibraryBook", ref dgBook);

}
}
catch (Exception x)
{
MessageBox.Show(x.Message);
}
}
// Manage Book - Button Update
private void button2_Click(object sender, EventArgs e)
{
try
{
DialogResult result = MessageBox.Show("Do you want to update this book?",
"Confirmation", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
string a = textBox2.Text;
string b = textBox3.Text;
string c = textBox4.Text;
int d = int.Parse(textBox5.Text);
string sql = string.Format("UPDATE LibraryBook SET Book = '{0}', Author = '{1}',
Category = '{2}', Quantity = {3} WHERE ID =
{4}",a,b,c,d,dgSearchBooks.CurrentRow.Cells[0].Value);
cs.ExecuteQuery(sql);
cs.CloseConnection();
cs.FillDataGrid("SELECT ID,Book,Author,Category,Quantity FROM LibraryBook", ref
dgSearchBooks);
cs.CloseConnection();
cs.FillDataGrid("SELECT * FROM LibraryBook", ref dgBook);
}
}
catch (Exception x)
{
MessageBox.Show(x.Message);
}
}
private void dgSearchBooks_SelectionChanged(object sender, EventArgs e)
{
selectedBook2[0] = dgSearchBooks.CurrentRow.Cells[1].Value.ToString();
selectedBook2[1] = dgSearchBooks.CurrentRow.Cells[2].Value.ToString();
selectedBook2[2] = dgSearchBooks.CurrentRow.Cells[3].Value.ToString();
selectedBook2[3] = dgSearchBooks.CurrentRow.Cells[4].Value.ToString();
textBox2.Text
textBox3.Text
textBox4.Text
textBox5.Text

=
=
=
=

selectedBook2[0].ToString();
selectedBook2[1].ToString();
selectedBook2[2].ToString();
selectedBook2[3].ToString();

}
// Manage Book - Button Add
private void button1_Click(object sender, EventArgs e)
{
try
{

DialogResult result = MessageBox.Show("Do you want to add this book?",

"Confirmation", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
string a = textBox2.Text;
string b = textBox3.Text;
string c = textBox4.Text;
int d = int.Parse(textBox5.Text);
string sql = string.Format("INSERT INTO LibraryBook (Book, Author, Category,
Quantity) VALUES('{0}','{1}','{2}',{3})", a, b, c, d);
cs.ExecuteQuery(sql);
cs.CloseConnection();
cs.FillDataGrid("SELECT ID,Book,Author,Category,Quantity FROM LibraryBook", ref
dgSearchBooks);
cs.CloseConnection();
cs.FillDataGrid("SELECT * FROM LibraryBook", ref dgBook);
}
}
catch (Exception x)
{
}

MessageBox.Show(x.Message);

}
// User Account - Button Exit
private void buttonExitProg_Click(object sender, EventArgs e)
{
Application.Exit();
}
private void dgUser_SelectionChanged(object sender, EventArgs e)
{
selectedBook3[0] = dgUser.CurrentRow.Cells[1].Value.ToString();
selectedBook3[1] = dgUser.CurrentRow.Cells[2].Value.ToString();
selectedBook3[2] = dgUser.CurrentRow.Cells[3].Value.ToString();
selectedBook3[3] = dgUser.CurrentRow.Cells[4].Value.ToString();
selectedBook3[4] = dgUser.CurrentRow.Cells[5].Value.ToString();
selectedBook3[5] = dgUser.CurrentRow.Cells[6].Value.ToString();
selectedBook3[6] = dgUser.CurrentRow.Cells[7].Value.ToString();
selectedBook3[7] = dgUser.CurrentRow.Cells[8].Value.ToString();

textBox7.Text = selectedBook3[0];
textBox15.Text = selectedBook3[1];
textBox8.Text = selectedBook3[2];
textBox9.Text = selectedBook3[3];
textBox10.Text = selectedBook3[4];
textBox13.Text = selectedBook3[5];
textBox11.Text = selectedBook3[6];
textBox12.Text = selectedBook3[7];

private void dgUser_DataBindingComplete(object sender,

DataGridViewBindingCompleteEventArgs e)
{
//if (dgUser != null)
//{

// dgUser.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
// dgUser.Columns[dgUser.ColumnCount - 1].AutoSizeMode =
DataGridViewAutoSizeColumnMode.Fill;
//}
}
private void dgSearchBooks_DataBindingComplete(object sender,
DataGridViewBindingCompleteEventArgs e)
{
if (dgSearchBooks != null)
{
dgSearchBooks.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dgSearchBooks.Columns[dgSearchBooks.ColumnCount - 1].AutoSizeMode =
DataGridViewAutoSizeColumnMode.Fill;
}
}
// User Account - Button Delete
private void buttonDeleteAccount_Click(object sender, EventArgs e)
{
DialogResult result = MessageBox.Show("Do you want to delete this user?",
"Confirmation", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
string sql = string.Format("DELETE FROM Users WHERE ID = {0}",
dgUser.CurrentRow.Cells[0].Value);
cs.ExecuteQuery(sql);
cs.CloseConnection();
cs.FillDataGrid("SELECT * FROM Users", ref dgUser);
}
}
// User Account - Button Update
private void buttonUpdateAccount_Click(object sender, EventArgs e)
{
DialogResult result = MessageBox.Show("Do you want to update this user?",
"Confirmation", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
string sql = string.Format(@"UPDATE Users SET Username = '{0}', [Password] = '{1}',
FirstName = '{2}', MiddleName = '{3}', LastName = '{4}',
Address = '{5}', Course = '{6}', YearLevel = {7} WHERE ID = {8}",
textBox7.Text,textBox15.Text,textBox8.Text,textBox9.Text,textBox10.Text,textBox13.Text,textBox1
1.Text,int.Parse(textBox12.Text),dgUser.CurrentRow.Cells[0].Value);
cs.ExecuteQuery(sql);
cs.CloseConnection();
cs.FillDataGrid("SELECT
ID,Username,Password,FirstName,MiddleName,LastName,Address,Course,YearLevel,[Book
Borrowed] FROM Users", ref dgUser);
}
}
// Transaction - Text Box Search
private void textBox6_TextChanged(object sender, EventArgs e)
{
string sql = "";
switch (comboBox2.SelectedIndex)

"%'";

case 0:
sql = "SELECT * FROM LibraryBook WHERE Author LIKE '%" + textBox6.Text + "%'";
break;
case 1:
sql = "SELECT * FROM LibraryBook WHERE Book LIKE '%" + textBox6.Text + "%'";
break;
case 2:
sql = "SELECT * FROM LibraryBook WHERE Category LIKE '%" + textBox6.Text +

break;
}
cs.FillDataGrid(sql, ref dgBook);

private void dgBook_DataBindingComplete(object sender,

DataGridViewBindingCompleteEventArgs e)
{
//if (dgBook != null)
//{
// dgBook.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
// dgBook.Columns[dgBook.ColumnCount - 1].AutoSizeMode =
DataGridViewAutoSizeColumnMode.Fill;
//}
}
// Transaction - Button Exit
private void button13_Click(object sender, EventArgs e)
{
Application.Exit();
}
// Transaction - Button Borrow
private void buttonBorrow_Click(object sender, EventArgs e)
{
date = dateTimePicker1.Value.ToShortDateString();
textBox14.Text = date;
int borrow = int.Parse(numericUpDown1.Value.ToString());
dateToday = System.DateTime.Now;
borrowDate = new System.TimeSpan(borrow, 0, 0, 0);
dueDate = dateToday.Add(borrowDate);
textBox16.Text = dueDate.ToShortDateString();
OleDbDataReader reader = null;
string sql2 = string.Format("SELECT * FROM LibraryBook WHERE ID =
{0}",dgBook.CurrentRow.Cells[0].Value);
string val = "";
reader = cs.RetrieveRecords(sql2, ref reader);
if (reader.HasRows)
{
while (reader.Read())
{
val = reader["Quantity"].ToString();
}
}
int val1 = int.Parse(val);
cs.CloseConnection();

if(val1 != 0)
{
if(numericUpDown1.Value == 0)
{
MessageBox.Show("Input how many days");
}
else
{
DateTime dateDueBack = Convert.ToDateTime(textBox16.Text);
string sql = string.Format("UPDATE LibraryBook SET Quantity = {0}, [Date
Borrowed] = '{1}', [Date Due Back] = '{2}', Borrower = '{3}' WHERE ID = {4}", val1 - 1, date,
dueDate.ToShortDateString(), userAdmin,dgBook.CurrentRow.Cells[0].Value);
cs.ExecuteQuery(sql);
cs.CloseConnection();
cs.FillDataGrid("SELECT * FROM LibraryBook", ref dgBook);
cs.CloseConnection();
cs.FillDataGrid("SELECT ID,Book,Author,Category,Quantity FROM LibraryBook", ref
dgSearchBooks);
cs.CloseConnection();
string sql1 = string.Format("UPDATE Users SET [Book Borrowed] = '{0}' WHERE
Username = '{1}'", dgBook.CurrentRow.Cells[1].Value.ToString(), userAdmin);
cs.ExecuteQuery(sql1);
cs.CloseConnection();
cs.FillDataGrid("SELECT ID,Username,
[Password],FirstName,MiddleName,LastName,Address,Course,YearLevel,[Book Borrowed] FROM
Users", ref dgUser);
}
}
else
{
MessageBox.Show("Book is out of stock.","Out of
stock.",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
}
// User Account - Button Add
private void buttonAddAccount_Click(object sender, EventArgs e)
{
DialogResult result = MessageBox.Show("Do you want to add this user?", "Confirmation",
MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
string sql = string.Format(@"INSERT INTO Users(Username,
[Password],FirstName,MiddleName,LastName,Address,Course,YearLevel)
VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7}) ",
textBox7.Text, textBox15.Text, textBox8.Text, textBox9.Text, textBox10.Text,
textBox13.Text, textBox11.Text, int.Parse(textBox12.Text));
cs.ExecuteQuery(sql);
cs.CloseConnection();
cs.FillDataGrid("SELECT ID,Username,
[Password],FirstName,MiddleName,LastName,Course,YearLevel,Address,[Book Borrowed] FROM
Users", ref dgUser);
}

}
// Manage Book - Button Clear
private void button10_Click(object sender, EventArgs e)
{
textBox2.Clear();
textBox3.Clear();
textBox4.Clear();
textBox5.Clear();
}
// User Account - Button Clear
private void button11_Click_1(object sender, EventArgs e)
{
textBox7.Clear();
textBox15.Clear();
textBox8.Clear();
textBox9.Clear();
textBox10.Clear();
textBox11.Clear();
textBox12.Clear();
textBox13.Clear();
}
private void dgBook_SelectionChanged(object sender, EventArgs e)
{
textBox14.Text = dgBook.CurrentRow.Cells[5].Value.ToString();
textBox16.Text = dgBook.CurrentRow.Cells[6].Value.ToString();
}
// Transaction - Button Return
private void button11_Click(object sender, EventArgs e)
{
DateTime dateDueBack;
DateTime dateReturned;
double daysOverdue;
double FineRate = 10;
double Fine;
if (textBox16.Text.Trim() != "" && textBox17.Text.Trim() != "")
{
dateDueBack = Convert.ToDateTime(textBox16.Text);
dateReturned = Convert.ToDateTime(textBox17.Text);
daysOverdue = (dateReturned - dateDueBack).TotalDays;
if (daysOverdue <= 0)
{
textBox18.Text = "0";
textBox19.Text = "0";
}
else
{
textBox18.Text = daysOverdue.ToString();

double overDueValue = double.Parse(textBox18.Text);

Fine = FineRate * overDueValue;
textBox19.Text = Fine.ToString();

OleDbDataReader reader = null;

string sql2 = string.Format("SELECT * FROM LibraryBook WHERE ID = {0}",
dgBook.CurrentRow.Cells[0].Value);
string val = "";
reader = cs.RetrieveRecords(sql2, ref reader);
if (reader.HasRows)
{
while (reader.Read())
{
val = reader["Quantity"].ToString();
}
}
int val1 = int.Parse(val);
cs.CloseConnection();

string sql = string.Format("UPDATE LibraryBook SET Quantity = {0}, [Date Borrowed] =

'{1}', [Date Due Back] = '{2}', Borrower = ' ' WHERE ID = {3}", val1 + 1, " ", " ",
dgBook.CurrentRow.Cells[0].Value);
cs.ExecuteQuery(sql);
cs.CloseConnection();
cs.FillDataGrid("SELECT * FROM LibraryBook", ref dgBook);
cs.CloseConnection();
cs.FillDataGrid("SELECT ID,Book,Author,Category,Quantity FROM LibraryBook", ref
dgSearchBooks);
cs.CloseConnection();
string sql1 = string.Format("UPDATE Users SET [Book Borrowed] = ' ' WHERE Username
= '{1}'", 1, userAdmin);
cs.ExecuteQuery(sql1);
cs.CloseConnection();
cs.FillDataGrid("SELECT ID,Username,
[Password],FirstName,MiddleName,LastName,Address,Course,YearLevel,[Book Borrowed] FROM
Users", ref dgUser);

cs.CloseConnection();
string sql3 = string.Format("");
}
else
{
MessageBox.Show("Input date return");
}

}
}