private void AddDelBook_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 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";
}
}
}
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();
}
}
}
