public static void brwBook(Book b, Borrower br)
{
SQLiteConnection conn = new SQLiteConnection("data source=libDB.sqlite;");
SQLiteCommand cmd;
string sql;
int rowID = 0;
try
{
conn.Open();
sql = $"SELECT ROWID FROM bookData WHERE bookName = \"{b.bookName}\" AND ISBN = \"{b.ISBN}\" AND writer LIKE \"%{b.writer}%\";";
cmd = new SQLiteCommand(sql, conn);
SQLiteDataReader reader = cmd.ExecuteReader();
if (reader.HasRows) rowID = Convert.ToInt32(reader["ROWID"]);
sql = $"SELECT * FROM bookData WHERE bookName = \"{b.bookName}\" AND ISBN = \"{b.ISBN}\" AND writer LIKE \"%{b.writer}%\";";
cmd = new SQLiteCommand(sql, conn);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
if (Convert.ToInt32(reader["left"]) > 0)
{
try
{
sql = $"INSERT INTO borrowData VALUES (\"{br.name}\", \"{DateTime.Now}\", \"{rowID}\");";
cmd = new SQLiteCommand(sql, conn);
cmd.ExecuteNonQuery();
sql = $"UPDATE bookData SET left = \"{Convert.ToInt32(reader["left"]) - 1}\" WHERE bookName = \"{b.bookName}\" AND ISBN = \"{b.ISBN}\" AND writer LIKE \"%{b.writer}%\"";
cmd = new SQLiteCommand(sql, conn);
cmd.ExecuteNonQuery();
MessageBox.Show("ยืมสำเร็จ");
}
catch (SQLiteException e)
{
Console.WriteLine(e);
}
}
else
{
MessageBox.Show("ยืมไม่สำเร็จ");
}
}
}
finally
{
if (conn.State != System.Data.ConnectionState.Closed)
conn.Close();
}
}
