private void DeleteBtn_Click(object sender, EventArgs e)
{
try
{
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
//Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = $"DELETE FROM Members WHERE MemberId={GMemberId}";
sqlcom.ExecuteNonQuery();
if (LibraryDBCon.State == ConnectionState.Open)
{
LibraryDBCon.Close();
}
Close();
this.DialogResult = DialogResult.OK;
} catch
{
MessageBox.Show("Something Went Wrong, Please Try Again");
}
}
void GetBookInfo(string bookId)
{
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
// Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = $"SELECT Books.ISNB, Books.Title, Departments.DepName FROM Books INNER JOIN Departments ON Books.Department = Departments.DepId WHERE (Books.BookId = {bookId})";
SqlDataReader Data = sqlcom.ExecuteReader();
Data.Read();
BookISNB.Text = $"Book ISNB: {Data[0]}";
BookTitle.Text = $"Book Title: {Data[1]}";
BookDepartment.Text = $"Book Department: {Data[2]}";
if (LibraryDBCon.State == ConnectionState.Open)
{
LibraryDBCon.Close();
}
Data.Close();
}
private void FillOldMemberData(string BookId)
{
try
{
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
// Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = $"SELECT * FROM Members WHERE Members.MemberId = {GMemberId}";
SqlDataReader Data = sqlcom.ExecuteReader();
Data.Read();
MemberIdTB.Text = Data[0].ToString();
MemberNameTB.Text = Data[1].ToString();
MemberEmailTB.Text = Data[2].ToString();
MemberPhoneTB.Text = Data[3].ToString();
MemberPrivilageTB.SelectedValue = Data[4].ToString();
if (LibraryDBCon.State == ConnectionState.Open)
{
LibraryDBCon.Close();
}
Data.Close();
} catch (Exception ex)
{
MessageBox.Show("Something Went Wrong, Please Try Again");
}
}
private void FillVisitsTable()
{
try
{
// Open DataBase
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
// Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = "SELECT Visits.VisitId, Members.Name AS 'Member Name', Visits.CheckIn AS 'Check-In Time', Visits.CheckOut As 'Check-Out Time', CONVERT(varchar, Visits.Date, 111) AS 'Day Date' FROM Visits INNER JOIN Members ON Visits.MemberId = Members.MemberId";
// Get The Data From DataBase Base And Set It Into DataGridView(Books Table)
SqlDataAdapter DA = new SqlDataAdapter(sqlcom);
DataTable DT = new DataTable();
DA.Fill(DT);
VisitsTable.ReadOnly = true;
VisitsTable.Columns.Clear();
VisitsTable.DataSource = DT;
VisitsTable.Columns[0].Visible = false;
} catch
{
MessageBox.Show("Something Went Wrong, Please Try Again");
}
}
private void FillPrivilage()
{
try
{
// Open DataBase
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
// Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = "SELECT RoleId, Role FROM Roles";
SqlDataAdapter da = new SqlDataAdapter(sqlcom);
DataSet ds = new DataSet();
da.Fill(ds, "Roles");
MemberPrivilageTB.DisplayMember = "Role";
MemberPrivilageTB.ValueMember = "RoleId";
MemberPrivilageTB.DataSource = ds.Tables["Roles"];
if (LibraryDBCon.State == ConnectionState.Open)
{
LibraryDBCon.Close();
}
} catch (Exception ex)
{
MessageBox.Show("Something Went Wrong, Please Try Again");
}
}
private void UpdateBtn_Click(object sender, EventArgs e)
{
if (ValidData())
{
try
{
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
//Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = $"UPDATE Books SET ISNB = '{ISNB.Text}', Title = '{Title.Text}', Author = '{Author.Text}', Department = {Department.SelectedValue}, Price = {Price.Text}, Status = {Status.SelectedValue} WHERE BookId={BookId}";
sqlcom.ExecuteNonQuery();
if (LibraryDBCon.State == ConnectionState.Open)
{
LibraryDBCon.Close();
}
Close();
this.DialogResult = DialogResult.OK;
}catch
{
MessageBox.Show("Something Went Wrong, Please Check Your Data And Try Again");
}
}
}
private void AddMemberBtn_Click(object sender, EventArgs e)
{
if (ValidData())
{
try
{
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
//Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = $"INSERT INTO Members (MemberId, Name, Email, Phone, Privilage) Values ('{MemberIdTB.Text}', '{MemberNameTB.Text}', '{MemberEmailTB.Text}', {MemberPhoneTB.Text}, {PrivilageCB.SelectedValue})";
sqlcom.ExecuteNonQuery();
if (LibraryDBCon.State == ConnectionState.Open)
{
LibraryDBCon.Close();
}
Close();
DialogResult = DialogResult.OK;
} catch
{
MessageBox.Show("Something Went Wrong, Please Try Again");
}
}
}
private void FillDep()
{
try
{
// Open DataBase
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
// Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = "SELECT DepId, DepName FROM Departments";
SqlDataAdapter da = new SqlDataAdapter(sqlcom);
DataSet ds = new DataSet();
da.Fill(ds, "Department");
Department.DisplayMember = "DepName";
Department.ValueMember = "DepID";
Department.DataSource = ds.Tables["Department"];
if (LibraryDBCon.State == ConnectionState.Open)
{
LibraryDBCon.Close();
}
}catch
{
MessageBox.Show("Something Went Wrong, Please Try Again");
}
}
private void FillOldBookData(string BookId)
{
try
{
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
// Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = $"SELECT * FROM Books INNER JOIN Departments ON Books.Department = Departments.DepId WHERE (Books.BookId = {BookId})";
SqlDataReader Data = sqlcom.ExecuteReader();
Data.Read();
ISNB.Text = Data[1].ToString();
Title.Text = Data[2].ToString();
Author.Text = Data[3].ToString();
Department.SelectedValue = Data[4].ToString();
Price.Text = Data[5].ToString();
Status.SelectedValue = Data[6].ToString();
if (LibraryDBCon.State == ConnectionState.Open)
{
LibraryDBCon.Close();
}
} catch (Exception Ex)
{
MessageBox.Show("Something Went Wrong, Please Try Again");
}
}
private void VisitsCheckInBtn_Click(object sender, EventArgs e)
{
DateTime currentTime = DateTime.Now;
string CheckInTime = $"{currentTime.Hour.ToString(): D2}:{currentTime.Minute.ToString(): D2}:{currentTime.Second.ToString(): D2}";
string CheckInDay = $"{currentTime.Year.ToString()}/{currentTime.Month.ToString(): D2}/{currentTime.Day.ToString(): D2}";
try
{
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
// Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = $"INSERT INTO Visits (MemberId, Date ,CheckIn, CheckOut) VALUES({VisitMemberId}, '{CheckInDay}', '{CheckInTime}', '00:00:00')";
sqlcom.ExecuteNonQuery();
if (LibraryDBCon.State == ConnectionState.Open)
{
LibraryDBCon.Close();
}
FillVisitsTable();
VisitsMemberName.Visible = false;
VisitsCheckOutBtn.Visible = false;
VisitsCheckInBtn.Visible = false;
VisitsMemberIdTB.Reset();
} catch
{
MessageBox.Show("Something Went Wrong, Please Try Again");
}
}
private void VisitsCheckOutBtn_Click(object sender, EventArgs e)
{
DateTime currentTime = DateTime.Now;
string CheckOutTime = $"{currentTime.Hour.ToString(): D2} : {currentTime.Minute.ToString(): D2} : {currentTime.Second.ToString(): D2}";
try
{
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
// Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = $"UPDATE Visits SET CheckOut = '{CheckOutTime}' WHERE VisitId = {VisitsId}";
sqlcom.ExecuteNonQuery();
if (LibraryDBCon.State == ConnectionState.Open)
{
LibraryDBCon.Close();
}
FillVisitsTable();
VisitsMemberName.Visible = false;
VisitsCheckOutBtn.Visible = false;
VisitsCheckInBtn.Visible = false;
VisitsMemberIdTB.Reset();
} catch
{
MessageBox.Show("Something Went Wrong, Please Try Again");
}
}
private void GetMemberInfo()
{
try
{
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
// Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = $"SELECT MemberId, Name, Email FROM Members WHERE MemberId = {GMemberId}";
SqlDataReader Data = sqlcom.ExecuteReader();
Data.Read();
MemberId.Text = $"Member ID: {Data[0]}";
MemeberName.Text = $"Member Name: {Data[1]}";
MemberEmail.Text = $"Member Email: {Data[2]}";
if (LibraryDBCon.State == ConnectionState.Open)
{
LibraryDBCon.Close();
}
Data.Close();
} catch
{
MessageBox.Show("Something Went Wrong, Please Try Again");
}
}
private void AddBtn_Click(object sender, EventArgs e)
{
if (ValidData())
{
try
{
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
//Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = $"INSERT INTO Books (ISNB, Title, Author, Department, Price, Status) Values ('{ISNB.Text}', '{Title.Text}', '{Author.Text}', {Department.SelectedValue}, {Price.Text}, {Status.SelectedValue})";
sqlcom.ExecuteNonQuery();
if (LibraryDBCon.State == ConnectionState.Open)
{
LibraryDBCon.Close();
}
this.Close();
this.DialogResult = DialogResult.OK;
}
catch
{
MessageBox.Show("Something Went Wrong, Please Try Again");
}
}
}
private void UpdateBtn_Click(object sender, EventArgs e)
{
if (ValidData())
{
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
try
{
//Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = $"UPDATE Members SET MemberId = {MemberIdTB.Text}, Name = '{MemberNameTB.Text}', Email = '{MemberEmailTB.Text}', Phone = '{MemberPhoneTB.Text}', Privilage = {MemberPrivilageTB.SelectedValue} WHERE MemberId = {GMemberId}";
sqlcom.ExecuteNonQuery();
} catch (Exception Ex)
{
MessageBox.Show("Something Went Wrong, Please Check Your Data And Try Again");
}
if (LibraryDBCon.State == ConnectionState.Open)
{
LibraryDBCon.Close();
}
Close();
this.DialogResult = DialogResult.OK;
}
}
private void MemberSearch(object sender, EventArgs e)
{
string SearchColumn = MemberSearchType.SelectedItem.ToString().Split(' ')[1];
if (SearchColumn == "Id")
{
SearchColumn = "MemberId";
}
string SearchWord = MemberSearchWord.Text.Trim();
string SearchPrivilage = ((MemberPrivilageComBox.SelectedValue.ToString() == "0") ? "" : $" AND (Members.Privilage = {MemberPrivilageComBox.SelectedValue.ToString()})");
try
{
// Open DataBase
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
// Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = $"SELECT Members.MemberId, Members.Name, Members.Email, Members.Phone, Roles.Role FROM Members INNER JOIN Roles ON Members.Privilage = Roles.RoleId WHERE(Members.{SearchColumn} LIKE '%{SearchWord}%'){SearchPrivilage}";
// Get The Data From DataBase Base And Set It Into DataGridView(Books Table)
SqlDataAdapter DA = new SqlDataAdapter(sqlcom);
DataTable DT = new DataTable();
DA.Fill(DT);
MembersTable.ReadOnly = true;
MembersTable.Columns.Clear();
MembersTable.DataSource = DT;
// Add Edit And Delete Buttons At the End Of The Table
MembersTable.Columns.Add("Edit", "Edit");
MembersTable.Columns.Add("Delete.", "Delete");
// Set The width Of Some Columns
MembersTable.Columns[5].Width = 50; // Edit Btn
MembersTable.Columns[6].Width = 60; // Delete Btn
// Colse Database
if (LibraryDBCon.State == ConnectionState.Open)
{
LibraryDBCon.Close();
}
} catch
{
MessageBox.Show("Something Went Wrong, Please Try Again");
}
}
private void BookSearch(object sender, EventArgs e)
{
string SearchColumn = BookSearchType.SelectedItem.ToString().Split(' ')[1];
string SearchWord = BookSearchTB.Text.Trim();
string SearchDep = (BookDepComBox.SelectedValue.ToString() == "0") ? "" : $" AND (Books.Department = {BookDepComBox.SelectedValue.ToString()})";
string SearchStatus = ((BookStatusComBox.SelectedValue.ToString() == "0") ? "" : $" AND (Books.Status = {BookStatusComBox.SelectedValue.ToString()})");
try
{
// Open DataBase
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
// Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = $"SELECT Books.BookId, Books.ISNB, Books.Title AS 'Book Title', Books.Author, Departments.DepName AS 'Department', Books.Price, Status.Status FROM Books INNER JOIN Departments ON Books.Department = Departments.DepId INNER JOIN Status ON Books.Status = Status.StatusId WHERE(Books.{SearchColumn} LIKE '%{SearchWord}%'){SearchDep}{SearchStatus}";
// Get The Data From DataBase Base And Set It Into DataGridView(Books Table)
SqlDataAdapter DA = new SqlDataAdapter(sqlcom);
DataTable DT = new DataTable();
DA.Fill(DT);
BooksTable.ReadOnly = true;
BooksTable.Columns.Clear();
BooksTable.DataSource = DT;
// Add Edit And Delete Buttons At the End Of The Table
BooksTable.Columns.Add("Edit", "Edit");
BooksTable.Columns.Add("Delete.", "Delete");
// Hide Book Id Collums
BooksTable.Columns[0].Visible = false;
// Set The width Of Some Columns
BooksTable.Columns[2].Width = 250; // Book Title
BooksTable.Columns[7].Width = 50; // Edit Btn
BooksTable.Columns[8].Width = 60; // Delete Btn
// Colse Database
if (LibraryDBCon.State == ConnectionState.Open)
{
LibraryDBCon.Close();
}
} catch
{
MessageBox.Show("Something Went Wrong, Please Try Again");
}
}
private void UserLogin(object sender, EventArgs e)
{
var hashedPass = GenerateHash(PasswordTB.Text);
//MessageBox.Show(LibraryDBCon.ServerVersion);
// Open DataBase
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
// Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = $"SELECT FullName FROM Librarians WHERE UserName = '******' AND PassHash = '{hashedPass}'";
SqlDataReader DR = sqlcom.ExecuteReader();
if (DR.Read())
{
// Set The Id To Static To Be Avilabile To The Other Forms
UserName = DR[0].ToString();
DashBoard Admin = new DashBoard();
this.Hide();
// Reset User Name And Password Field
UserNameTB.ResetText();
PasswordTB.ResetText();
if (Admin.ShowDialog() == DialogResult.OK)
{
this.Show();
}
else
{
this.Close();
}
}
else
{
MetroFramework.MetroMessageBox.Show(this, "Worng Name or Password.", "Error Message", 100);
}
// Close Data Reader
DR.Close();
// Close DataBase Connection
if (LibraryDBCon.State == ConnectionState.Open)
{
LibraryDBCon.Close();
}
}
// fill the Books Data Grid View
void FillBooksTable()
{
try
{
// Open DataBase
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
// Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = "SELECT Books.BookId, Books.ISNB, Books.Title As 'Book Title', Books.Author, Departments.DepName AS 'Department', Books.Price, Status.Status FROM Books INNER JOIN Departments ON Books.Department = Departments.DepId INNER JOIN Status ON Books.Status = Status.StatusId";
// Get The Data From DataBase Base And Set It Into DataGridView(Books Table)
SqlDataAdapter DA = new SqlDataAdapter(sqlcom);
DataTable DT = new DataTable();
DA.Fill(DT);
BooksTable.ReadOnly = true;
BooksTable.Columns.Clear();
BooksTable.DataSource = DT;
// Add Edit And Delete Buttons At the End Of The Table
BooksTable.Columns.Add("Edit", "Edit");
BooksTable.Columns.Add("Delete.", "Delete");
// Hide Book Id Collums
BooksTable.Columns[0].Visible = false;
// Set The width Of Some Columns
BooksTable.Columns[2].Width = 250; // Book Title
BooksTable.Columns[7].Width = 50; // Edit Btn
BooksTable.Columns[8].Width = 60; // Delete Btn
// Close Database
if (LibraryDBCon.State == ConnectionState.Open)
{
LibraryDBCon.Close();
}
} catch
{
MessageBox.Show("Something Went Wrong, Please Try Again");
}
}
// fill the Members Data Grid View
void FillMembersTable(bool old = false)
{
try
{
// Open DataBase
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
// Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = "SELECT Members.MemberId, Members.Name, Members.Email, Members.Phone, Roles.Role AS 'Privilage' FROM Members INNER JOIN Roles ON Members.Privilage = Roles.RoleId";
// Get The Data From DataBase Base And Set It Into DataGridView(Books Table)
SqlDataAdapter DA = new SqlDataAdapter(sqlcom);
DataTable DT = new DataTable();
DA.Fill(DT);
MembersTable.ReadOnly = true;
MembersTable.Columns.Clear();
MembersTable.DataSource = DT;
// Add Edit And Delete Buttons At the End Of The Table
MembersTable.Columns.Add("Edit", "Edit");
MembersTable.Columns.Add("Delete.", "Delete");
// Set The width Of Some Columns
MembersTable.Columns[5].Width = 50; // Edit Btn
MembersTable.Columns[6].Width = 60; // Delete Btn
// Colse Database
if (LibraryDBCon.State == ConnectionState.Open)
{
LibraryDBCon.Close();
}
} catch
{
MessageBox.Show("Something Went Wrong, Please Try Again");
}
}
private void FillMemberPrivilageComBox()
{
try
{
// Open DataBase
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
// Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = "SELECT RoleId, Role FROM Roles";
SqlDataAdapter da = new SqlDataAdapter(sqlcom);
DataSet ds = new DataSet();
// Add general Option At Top
DataTable dt = new DataTable("Roles");
dt.Columns.Add(new DataColumn("RoleId", typeof(int)));
dt.Columns.Add(new DataColumn("Role", typeof(string)));
DataRow dr = dt.NewRow();
dr["RoleId"] = 0;
dr["Role"] = "All Privilages";
dt.Rows.Add(dr);
ds.Tables.Add(dt);
da.Fill(ds, "Roles");
MemberPrivilageComBox.DisplayMember = "Role";
MemberPrivilageComBox.ValueMember = "RoleId";
MemberPrivilageComBox.DataSource = ds.Tables["Roles"];
if (LibraryDBCon.State == ConnectionState.Open)
{
LibraryDBCon.Close();
}
}
catch
{
MessageBox.Show("Something Went Wrong, Please Try Again");
}
}
private void VisitMemberId_KeyPress(object sender, KeyPressEventArgs e)
{
if (!char.IsControl(e.KeyChar) && !char.IsDigit(e.KeyChar))
{
e.Handled = true;
}
if (e.KeyChar == 13)
{
try
{
if (LibraryDBCon.State == ConnectionState.Closed)
{
LibraryDBCon.Open();
}
// Create Connection With DataBase
sqlcom.Connection = LibraryDBCon;
sqlcom.CommandType = CommandType.Text;
sqlcom.CommandText = $"SELECT Name FROM Members WHERE MemberId = {VisitsMemberIdTB.Text}";
SqlDataReader Data = sqlcom.ExecuteReader();
// If Memeber Id Found
if (Data.Read())
{
VisitMemberId = VisitsMemberIdTB.Text;
VisitsMemberName.Text = Data[0].ToString();
VisitsMemberName.Visible = true;
sqlcom.CommandText = $"SELECT VisitId, CheckOut FROM Visits WHERE MemberId = {VisitsMemberIdTB.Text}";
Data.Close();
Data = sqlcom.ExecuteReader();
while (Data.Read())
{
VisitsId = Data[0].ToString();
if (Data[1].ToString() == "00:00:00")
{
VisitsCheckOutBtn.BringToFront();
VisitsCheckOutBtn.Visible = true;
break;
}
}
if (VisitsCheckOutBtn.Visible == false)
{
VisitsCheckInBtn.BringToFront();
VisitsCheckInBtn.Visible = true;
}
}
else
{
VisitsMemberName.Visible = false;
VisitsCheckOutBtn.Visible = false;
VisitsCheckInBtn.Visible = false;
}
if (LibraryDBCon.State == ConnectionState.Open)
{
LibraryDBCon.Close();
}
Data.Close();
} catch
{
MessageBox.Show("Something Went Wrong, Please Try Again");
}
}
}
