public double getTotalFineAmount(string dbpath)
{
DbCon con = new DbCon(dbpath);
DataRowCollection dr = con.ExecuteSelectCommand("select sum(fineamount) from student_fine");
return(Convert.ToDouble(dr[0].ItemArray[0]));
}
public int DeleteBook(string ID, string path)
{
DbCon con = new DbCon(path);
string cmdstr = "delete from Books where bookId='" + ID + "'";
return(con.ExecuteDDLCommand(cmdstr));
}
public bool BookIDExists(string ID)
{
bool res = true;
try
{
//cmd=new OleDbCommand("Select * from books ")
DbCon con = new DbCon();
DataRowCollection ds;
ds = con.ExecuteSelectCommand("select * from Books where BookID='" + ID + "'");
if (ds.Count > 0)
{
res = true;
}
else
{
res = false;
}
}
catch (Exception ex)
{
res = false;
MessageBox.Show(ex.Message);
}
return(res);
}
private void button1_Click(object sender, EventArgs e)
{
if (!Information.IsNumeric(textBox7.Text))
{
MessageBox.Show("Please provide numeric value in Book Price.");
textBox7.Focus();
return;
}
if (!Information.IsNumeric(textBox8.Text))
{
MessageBox.Show("Please provide numeric value in Book Price.");
textBox7.Focus();
return;
}
if (!Information.IsNumeric(textBox10.Text))
{
MessageBox.Show("Please provide numeric value in Book Price.");
textBox7.Focus();
return;
}
foreach (var item in groupBox2.Controls)
{
if (item is TextBox)
{
TextBox t = (TextBox)item;
if (t.Text == "")
{
MessageBox.Show("You have to fill all the fields");
return;
}
}
}
Books b = new Books();
b.BookID = textBox1.Text; b.BookName = textBox2.Text;
b.PublisherName = textBox3.Text; b.PublisherAdd = textBox4.Text; b.AuthorName = textBox5.Text;
b.AuthorAdd = textBox6.Text; b.BookPrice = Convert.ToDouble(textBox7.Text);
b.BookPafes = Convert.ToInt16(textBox8.Text); b.CatagoryName = textBox9.Text; b.Stock = Convert.ToInt16(textBox10.Text);
DbCon d = new DbCon();
int res = d.SaveBooks(b);
if (res <= 0)
{
MessageBox.Show("Sorry No Data is saved. Some error occurred");
}
else
{
MessageBox.Show("Record Saved Successfully");
}
Refresh_scr();
}
public bool UpdateStock(string ID, string dbpath)
{
DbCon con = new DbCon();
int k = con.ExecuteDDLCommand("UPDATE Books SET BookStock = BookStock + 1 WHERE (BookID = '" + ID + "')");
if (k > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool updateFine(student_fine sf, string path)
{
DbCon con = new DbCon(path);
int r = con.ExecuteDDLCommand("update student_fine set fineamount=" + sf.FineAmount + " where studentID='" + sf.StudentID + "'");
if (r > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool AddFine(student_fine sf, string path)
{
DbCon con = new DbCon(path);
int r = con.ExecuteDDLCommand("insert into student_fine values('" + sf.StudentID + "'," + sf.FineAmount + ")");
if (r > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool searchStudentByName(string name, string path)
{
DbCon con = new DbCon(path);
DataRowCollection dr =
con.ExecuteSelectCommand("select * from student where studentName like'%" + name + "%'");
if (dr.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool StudentExists(string ID, string path)
{
DbCon con = new DbCon(path);
DataRowCollection dr =
con.ExecuteSelectCommand("select * from student where studentId='" + ID + "'");
if (dr.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool ReturnBook(string StudID, string BookID, string path)
{
DbCon con = new DbCon(path);
string cmdstr = "update StudentIssueRegister set returndate=" + DateTime.Today + ",status='return' where StudentID='" + StudID + "' and bookid='" + BookID + "'";
int r = con.ExecuteDDLCommand(cmdstr);
if (r > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool IssueBook(issue_register ir, string path)
{
DbCon con = new DbCon(path);
int r = con.ExecuteDDLCommand("insert into StudentIssueRegister values ('" +
ir.StudentID + "','" + ir.BookID + "'," + ir.IssueDate + "," + ir.ReturnDate + ",'" + ir.Status + "')");
if (r > 0)
{
return(true);
}
else
{
return(false);
}
}
public int DeleteStudent(string ID, string path)
{
int res = 0;
DbCon con = new DbCon(path);
if (StudentExists(ID, path))
{
res = 0;
}
else
{
string cmdstr = "Delete from student where studentID='" + ID + "'";
res = con.ExecuteDDLCommand(cmdstr);
}
return(res);
}
public int AddStudent(Student s, string path)
{
int res = 0;
DbCon con = new DbCon(path);
if (StudentExists(s.StudentID, path))
{
res = 0;
}
else
{
string cmdstr = "insert into student values('" + s.StudentID + "','" + s.StudentName + "','" + s.StudentAdd + "','" + s.StudentMail + "','" + s.StudentPhone + "')";
res = con.ExecuteDDLCommand(cmdstr);
}
return(res);
}
public bool searchBookbycatagory(string id, string path)
{
bool res;
DbCon con = new DbCon(path);
DataRowCollection dr =
con.ExecuteSelectCommand("select * from books where BookcatName Like '%" + id + "%'");
if (dr.Count > 0)
{
res = true;
}
else
{
res = false;
}
return(res);
}
private void button1_Click(object sender, EventArgs e)
{
dataGridView1.Rows.Clear();
string cmdstr = "select * from Books where ";
if (textBox1.Text == "")
{
MessageBox.Show("Provide data in the search field");
return;
}
switch (comboBox1.SelectedIndex)
{
case 0:
cmdstr += " bookID='" + textBox1.Text + "'";
break;
case 1:
cmdstr += " bookname like'%" + textBox1.Text + "%'";
break;
case 2:
cmdstr += " bookpubname like'%" + textBox1.Text + "%'";
break;
case 3:
cmdstr += " bookautname like'%" + textBox1.Text + "%'";
break;
case 4:
cmdstr += " bookcatname like'%" + textBox1.Text + "%'";
break;
default:
break;
}
DbCon db = new DbCon();
DataRowCollection dr = db.ExecuteSelectCommand(cmdstr);
for (int i = 0; i < dr.Count; i++)
{
dataGridView1.Rows.Add(dr[i].ItemArray);
}
//dataGridView1.DataSource = dr;
}
public int SaveBooks(Books b, string dbpath)
{
try
{
DbCon con = new DbCon(dbpath);
string cmdstr;
cmdstr = "insert into books values('" + b.BookID + "','" + b.BookName + "','" + b.PublisherName + "','"
+ b.PublisherAdd + "','" + b.AuthorName + "','" + b.AuthorAdd + "'," + b.BookPrice + "," + b.BookPafes
+ ",'" + b.CatagoryName + "'," + b.Stock + ")";
int x = con.ExecuteDDLCommand(cmdstr);
return(x);
}
catch (Exception ex)
{
return(0);
//throw ex;
}
}
public int EditStudent(string Old, Student New, string path)
{
int res = 0;
DbCon con = new DbCon(path);
if (StudentExists(Old, path))
{
res = 0;
}
else
{
string cmdstr = "update student" +
"set " +
"studentName='" + New.StudentName + "'," +
"studentAdd='" + New.StudentAdd + "'," +
"studentEmail='" + New.StudentMail + "'," +
"studentph1='" + New.StudentPhone + "' " +
"where studentId='" + Old + "';";
res = con.ExecuteDDLCommand(cmdstr);
}
return(res);
}
public bool BookIDExists(string ID, string dbpath)
{
bool res = true;
try
{
DbCon con = new DbCon(dbpath);
DataRowCollection ds;
ds = con.ExecuteSelectCommand("select * from Books where BookID='" + ID + "'");
if (ds.Count > 0)
{
res = true;
}
else
{
res = false;
}
}
catch (Exception)
{
return(false);
}
return(res);
}
public DataRowCollection SearchBooks(string search, string dbpath)
{
DbCon con = new DbCon(dbpath);
return(con.ExecuteSelectCommand(search));
}