private void searchBtn_Click(object sender, EventArgs e)
{
bookDetail.Rows.Clear();
String[,] book = new String[DBCommand.countRow(), 10];
try
{
book = DBCommand.searchBook(nameField.Text, "", writerField.Text, "", "", publishedYrBox.Text, "");
for (int i = 0; i < book.GetLength(0); i++)
{
bookDetail.Rows.Add();
for (int j = 0; j < 10; j++)
{
bookDetail.Rows[i].Cells[j].Value = book[i, j];
}
}
}
catch (FileLoadException e2)
{
Console.WriteLine(e2);
bookDetail.Rows.Add();
for (int j = 0; j < 10; j++)
{
bookDetail.Rows[0].Cells[j].Value = "N/A";
}
}
}
private void BorrowBook_Load(object sender, EventArgs e)
{
bookDetail.Rows.Clear();
string[,] book = new String[DBCommand.countRow(), 10];
try
{
book = DBCommand.searchBook("", "", "", "", "", "", "");
for (int i = 0; i < book.GetLength(0); i++)
{
bookDetail.Rows.Add();
for (int j = 0; j < 10; j++)
{
bookDetail.Rows[i].Cells[j].Value = book[i, j];
}
}
}
catch (FileLoadException e2)
{
Console.WriteLine(e2);
bookDetail.Rows.Add();
for (int j = 0; j < 10; j++)
{
bookDetail.Rows[0].Cells[j].Value = "N/A";
}
}
}
private void addBtn_Click(object sender, EventArgs e)
{
try {
Book b = new Book(nameField.Text, subNameField.Text, writerField.Text, subwriterField.Text, translatorField.Text,
Int32.Parse(editionField.Text), Int32.Parse(amountField.Text), publisherField.Text, Int32.Parse(publishedYrBox.Text), ISBNField.Text, deweyField.Text, categoryBox.Text);
DBCommand.addBook(b);
}
catch (FormatException e2)
{
int num = 0;
String message = "";
if (Int32.TryParse(amountField.Text, num))
{
if (num <= 0)
{
message += "\nกรุณากรอกจำนวนเล่มให้ถูกต้อง";
}
}
else
{
message += "\nกรุณากรอกจำนวนเล่มให้ถูกต้อง";
}
if (Int32.TryParse(editionField.Text, num))
{
if (num <= 0)
{
message += "\nกรุณากรอกจำนวนเล่มให้ถูกต้อง";
}
}
else
{
message += "\nกรุณากรอกจำนวนเล่มให้ถูกต้อง";
}
if (Int32.TryParse(publishedYrBox.Text, num))
{
if (num <= 0)
{
message += "\nกรุณากรอกจำนวนเล่มให้ถูกต้อง";
}
}
else
{
message += "\nกรุณากรอกจำนวนเล่มให้ถูกต้อง";
}
/*
* if (amountField.Text == "") MessageBox.Show("กรุณากรอกจำนวนเล่ม");
* else if(Int32.Parse(amountField.Text) <= 0) MessageBox.Show("กรุณากรอกจำนวนเล่มที่ถูกต้อง");
* else if(editionField.Text == "") MessageBox.Show("กรุณากรอกครั้งที่พิมพ์");
* else if (Int32.Parse(editionField.Text) <= 0) MessageBox.Show("กรุณากรอกครั้งที่พิมพ์ที่ถูกต้อง");
* else if (publishedYrBox.Text == "") MessageBox.Show("กรุณากรอกปีที่พิมพ์");
* else if (Int32.Parse(publishedYrBox.Text) <= 0) MessageBox.Show("กรุณากรอกปีที่พิมพ์ที่ถูกต้อง");
*/
}
}
private void borrowBtn_Click(object sender, EventArgs e)
{
if (ISBNField.Text == "")
{
MessageBox.Show("กรุณากรอกเลขประจำหนังสือ (ISBN) ให้ถูกต้อง");
return;
}
//ยังคิดไม่ออก แต่ยืมได้ละ ขาดเวลาเล่มเดียวกันแต่คนละ edition (ซึ่งใน db จริงยังไม่มีกรณีนี้)
Book b = new Book(nameField.Text, "", writerField.Text, "", "", "", "", "", "", ISBNField.Text, "", "");
DBCommand.brwBook(b, new Borrower(borrowerField.Text));
}
public static string[,] searchBook(String bookName, String bookSubname, String writer, String translator, String publisher, String publishedYr, String category)
{
SQLiteConnection conn = new SQLiteConnection("data source=libDB.sqlite;");
SQLiteCommand cmd;
string sql;
try
{
conn.Open();
int row = DBCommand.countRow();
string[,] data = new String[row, 10];
int i = 0;
//SELECT
sql = String.Format("SELECT * FROM bookData WHERE bookName LIKE \"%{0}%\" AND bookSubname LIKE \"%{1}%\" AND writer LIKE \"%{2}%\" AND translator LIKE \"%{3}%\" AND publisher LIKE \"%{4}%\" AND publishedYr LIKE \"%{5}%\" AND category LIKE \"%{6}%\";"
, bookName, bookSubname, writer, translator, publisher, publishedYr, category);
cmd = new SQLiteCommand(sql, conn);
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
data[i, 0] += reader["bookName"];
data[i, 1] += reader["left"].ToString() + "/" + reader["total"];
data[i, 2] += reader["category"];
data[i, 3] += reader["writer"] + ", " + reader["subwriter"];
data[i, 4] += reader["translator"];
data[i, 5] += reader["publisher"];
data[i, 6] += reader["publishedYr"];
data[i, 7] += reader["edition"];
data[i, 8] += reader["ISBN"];
data[i, 9] += reader["deweyNumber"];
i++;
}
return(data);
}
catch (SQLiteException e)
{
Console.WriteLine(e);
return(new string[1, 10]);
}
finally
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
}
}
private void delBtn_Click(object sender, EventArgs e)
{
try
{
Convert.ToInt32(delamountField);
if (delISBNField.Text == "")
{
MessageBox.Show("กรุณากรอกเลขประจำหนังสือ (ISBN)");
return;
}
Book b = new Book(delnameField.Text, "", "", "", "", "", delamountField.Text, "", "", delISBNField.Text, "", "");
DBCommand.delBook(b);
}
catch (FormatException e2)
{
MessageBox.Show("กรุณากรอกจำนวนเล่ม");
}
}
public static string[,] searchBrw(string bookName, string ISBN, string writer, string borrower)
{
SQLiteConnection conn = new SQLiteConnection("data source=libDB.sqlite;");
SQLiteCommand cmd;
string sql;
int row = 0, i = 0;
try
{
conn.Open();
row = DBCommand.countRowbrw();
string[,] brwdata = new string[row, 6];
sql = "SELECT * FROM borrowData";
cmd = new SQLiteCommand(sql, conn);
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
sql = $"SELECT * FROM bookData WHERE ROWID = \"{reader["bookID"]}\"";
cmd = new SQLiteCommand(sql, conn);
SQLiteDataReader r2 = cmd.ExecuteReader();
brwdata[i, 0] = r2["bookName"].ToString();
brwdata[i, 1] = reader["borrower"].ToString();
brwdata[i, 2] = "";
brwdata[i, 3] = DateTime.Now.ToString();
brwdata[i, 4] = r2["ISBN"].ToString();
brwdata[i, 5] = r2["writer"].ToString();
i++;
}
return(brwdata);
}
catch (SQLiteException e)
{
Console.WriteLine(e);
return(new string[1, 10]);
}
finally
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
}
}
private void addBtn_Click(object sender, EventArgs e)
{
try
{
Convert.ToInt32(amountField.Text);
}
catch (FormatException e2)
{
MessageBox.Show("กรุณากรอกจำนวนเล่มที่ถูกต้อง");
return;
}
try
{
if (editionField.Text != "-")
{
Convert.ToInt32(editionField.Text);
}
}
catch (FormatException e2)
{
MessageBox.Show("กรุณากรอกครั้งที่พิมพ์ที่ถูกต้อง");
return;
}
try
{
if (publishedYrBox.Text != "-")
{
Convert.ToInt32(publishedYrBox.Text);
}
}
catch (FormatException e2)
{
MessageBox.Show("กรุณากรอกปีที่พิมพ์ที่ถูกต้อง");
return;
}
Book b = new Book(nameField.Text, subNameField.Text, writerField.Text, subwriterField.Text, translatorField.Text,
editionField.Text, amountField.Text, publisherField.Text, publishedYrBox.Text, ISBNField.Text, deweyField.Text, categoryBox.Text);
DBCommand.addBook(b);
}
private void delBtn_Click(object sender, EventArgs e)
{
try
{
if (delISBNField.Text == "")
{
MessageBox.Show("กรุณากรอกเลขประจำหนังสือ (ISBN)");
}
Book b = new Book(delnameField.Text, "", "", "", "", 0, Int32.Parse(delamountField.Text), "", 0, delISBNField.Text, "", "");
DBCommand.delBook(b);
}
catch (FormatException e2)
{
if (delamountField.Text == "")
{
MessageBox.Show("กรุณากรอกจำนวนเล่ม");
}
else if (Int32.Parse(delamountField.Text) <= 0)
{
MessageBox.Show("กรุณากรอกจำนวนเล่มที่ถูกต้อง");
}
}
}
private void returnBtn_Click(object sender, EventArgs e)
{
string bookName = nameField.Text, ISBN = ISBNField.Text, writer = writerField.Text, borrower = borrowerField.Text;
DBCommand.rtnBook(bookName, borrower);
}
