void GetBookByID()
{
try
{
SqlConnection con = new SqlConnection(strcon);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("SELECT * from dbo.book_master_tbl WHERE book_id='" + TextBox_BookId.Text.Trim() + "';", con);
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
if (dataTable.Rows.Count >= 1)
{
TextBox_BookName.Text = dataTable.Rows[0]["book_name"].ToString();
TextBox_Date.Text = dataTable.Rows[0]["publish_date"].ToString();
TextBox_Edition.Text = dataTable.Rows[0]["edition"].ToString();
TextBox_BookCost.Text = dataTable.Rows[0]["book_cost"].ToString().Trim();
TextBox_Pages.Text = dataTable.Rows[0]["no_of_pages"].ToString().Trim();
TextBox_ActualStock.Text = dataTable.Rows[0]["actual_stock"].ToString().Trim();
TextBox_CurrentStock.Text = dataTable.Rows[0]["current_stock"].ToString().Trim();
TextBox_BookDescription.Text = dataTable.Rows[0]["book_description"].ToString();
/*TextBox_CurrentStock.Text = "" + (Convert.ToInt32(dataTable.Rows[0]["actual_stock"].ToString()) - Convert.ToInt32(dataTable.Rows[0]["current_stock"].ToString()));*/
DropDownList_Language.SelectedValue = dataTable.Rows[0]["language"].ToString().Trim();
DropDownList_Publisher.SelectedValue = dataTable.Rows[0]["publisher_name"].ToString().Trim();
DropDownList_AuthorName.SelectedValue = dataTable.Rows[0]["author_name"].ToString().Trim();
ListBox_Genre.ClearSelection();
string[] genre = dataTable.Rows[0]["genre"].ToString().Trim().Split(',');
for (int i = 0; i < genre.Length; i++)
{
for (int j = 0; j < ListBox_Genre.Items.Count; j++)
{
if (ListBox_Genre.Items[j].ToString() == genre[i])
{
ListBox_Genre.Items[j].Selected = true;
}
}
}
global_actual_stock = Convert.ToInt32(dataTable.Rows[0]["actual_stock"].ToString().Trim());
global_current_stock = Convert.ToInt32(dataTable.Rows[0]["current_stock"].ToString().Trim());
global_issued_books = global_actual_stock - global_current_stock;
global_filepath = dataTable.Rows[0]["book_img_link"].ToString();
}
else
{
Response.Write("<script>alert('Invalid Book ID');</script>");
}
}
catch (Exception ex)
{
}
}
protected void Update_Click(object sender, EventArgs e)
{
if (!IsOk())
{
string filename;
if (FileUpload1.HasFile)
{
filename = $"books/{Path.GetFileName(FileUpload1.PostedFile.FileName)}";
}
else
{
filename = file_path;
}
string genre = "";
foreach (int i in ListBox_Genre.GetSelectedIndices())
{
genre += ListBox_Genre.Items[i] + ",";
}
SqlCommand cmd = new SqlCommand("UPDATE book_master_tbl set book_name=@book_name, genre=@genre, " +
"author_name=@author_name, publisher_name=@publisher_name, publish_data=@publish_date, language=@language, " +
"edition=@edition, book_cost=@book_cost, nr_of_pages=@no_of_pages, book_description=@book_description, " +
"actual_stock=@actual_stock, current_stock=@current_stock, book_img_link=@book_img_link where book_id='"
+ BookID.Text.Trim() + "'", Con1.Connect());
cmd.Parameters.AddWithValue("@book_name", BookName.Text.Trim());
cmd.Parameters.AddWithValue("@genre", genre);
cmd.Parameters.AddWithValue("@author_name", DropDownList_AuthorName.SelectedItem.Value);
cmd.Parameters.AddWithValue("@publisher_name", DropDownList_Publisher.SelectedItem.Value);
cmd.Parameters.AddWithValue("@publish_date", Date.Text.Trim());
cmd.Parameters.AddWithValue("@language", DropDownList_Language.SelectedItem.Value);
cmd.Parameters.AddWithValue("@edition", Edition.Text.Trim());
cmd.Parameters.AddWithValue("@book_cost", BookCost.Text.Trim());
cmd.Parameters.AddWithValue("@no_of_pages", Pages.Text.Trim());
cmd.Parameters.AddWithValue("@book_description", Description.Text.Trim());
cmd.Parameters.AddWithValue("@actual_stock", Stock.Text.Trim());
int stock = Int32.Parse(Stock.Text.Trim()) - Int32.Parse(CurrentStock.Text.Trim());
cmd.Parameters.AddWithValue("@current_stock", stock.ToString());
cmd.Parameters.AddWithValue("@book_img_link", filename);
cmd.ExecuteNonQuery();
Show();
}
else
{
Response.Write("<script>alert(' Carte Inexistenta ');</script>");
}
}
protected void Go(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand($"SELECT * FROM book_master_tbl where book_id='{BookID.Text.Trim()}' OR book_name ='{BookName.Text.Trim()}';", Con1.Connect());
SqlDataAdapter addaptor = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
addaptor.Fill(dt);
if (dt.Rows.Count >= 1)
{
cmd = new SqlCommand($"SELECT * FROM book_issue_tbl where book_id='{BookID.Text.Trim()}';", Con1.Connect());
addaptor = new SqlDataAdapter(cmd);
DataTable dtt = new DataTable();
addaptor.Fill(dtt);
int IssueNumber = dtt.Rows.Count;
IssuedBooks.Text = IssueNumber.ToString();
BookName.Text = dt.Rows[0]["book_name"].ToString().Trim();
Date.Text = dt.Rows[0]["publish_data"].ToString().Trim();
Pages.Text = dt.Rows[0]["nr_of_pages"].ToString().Trim();
BookCost.Text = dt.Rows[0]["nr_of_pages"].ToString().Trim();
Stock.Text = dt.Rows[0]["actual_stock"].ToString().Trim();
CurrentStock.Text = dt.Rows[0]["current_stock"].ToString();
Description.Text = dt.Rows[0]["book_description"].ToString().Trim();
ListBox_Genre.ClearSelection();
string[] genre = dt.Rows[0]["genre"].ToString().Split(',');
for (int i = 0; i < genre.Length; i++)
{
for (int j = 0; j < ListBox_Genre.Items.Count; j++)
{
if (ListBox_Genre.Items[j].ToString() == genre[i])
{
ListBox_Genre.Items[j].Selected = true;
}
}
}
DropDownList_AuthorName.SelectedValue = dt.Rows[0]["author_name"].ToString().Trim();
DropDownList_Language.SelectedValue = dt.Rows[0]["language"].ToString().Trim();
DropDownList_Publisher.SelectedValue = dt.Rows[0]["publisher_name"].ToString().Trim();
}
else
{
Response.Write("<script>alert(' ID incorect ');</script>");
}
}
void AddNewBook()
{
try {
string genres = "";
foreach (int i in ListBox_Genre.GetSelectedIndices())
{
genres = genres + ListBox_Genre.Items[i] + ",";
}
genres = genres.Remove(genres.Length - 1);
string filepath = "~/book_inventory/books1.png";
string filename = Path.GetFileName(FileUpload.PostedFile.FileName);
FileUpload.SaveAs(Server.MapPath("book_inventory/" + filename));
filepath = "~/book_inventory/" + filename;
SqlConnection con = new SqlConnection(strcon);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("Insert Into dbo.book_master_tbl(book_id,book_name," +
"genre,author_name,publisher_name,publish_date,language,edition,book_cost,no_of_pages," +
"book_description,actual_stock,current_stock,book_img_link) values(@book_id,@book_name," +
"@genre,@author_name,@publisher_name,@publish_date,@language,@edition,@book_cost,@no_of_pages," +
"@book_description,@actual_stock,@current_stock,@book_img_link)", con);
cmd.Parameters.AddWithValue("@book_id", TextBox_BookId.Text.Trim());
cmd.Parameters.AddWithValue("@book_name", TextBox_BookName.Text.Trim());
cmd.Parameters.AddWithValue("@genre", genres);
cmd.Parameters.AddWithValue("@author_name", DropDownList_AuthorName.SelectedItem.Value);
cmd.Parameters.AddWithValue("@publisher_name", DropDownList_Publisher.SelectedItem.Value);
cmd.Parameters.AddWithValue("@publish_date", TextBox_Date.Text.Trim());
cmd.Parameters.AddWithValue("@language", DropDownList_Language.SelectedItem.Value);
cmd.Parameters.AddWithValue("@edition", TextBox_Edition.Text.Trim());
cmd.Parameters.AddWithValue("@book_cost", TextBox_BookCost.Text.Trim());
cmd.Parameters.AddWithValue("@no_of_pages", TextBox_Pages.Text.Trim());
cmd.Parameters.AddWithValue("@book_description", TextBox_BookDescription.Text.Trim());
cmd.Parameters.AddWithValue("@actual_stock", TextBox_ActualStock.Text.Trim());
cmd.Parameters.AddWithValue("@current_stock", TextBox_CurrentStock.Text.Trim());
cmd.Parameters.AddWithValue("@book_img_link", filepath);
cmd.ExecuteNonQuery();
con.Close();
Response.Write("<script>alert('Book added successfully.');</script>");
BookTable.DataBind();
} catch (Exception ex) {
Response.Write("<script>alert('" + ex.Message + "');</script>");
}
}
protected void Add_Click(object sender, EventArgs e)
{
if (IsOk())
{
try
{
SqlCommand cmd = new SqlCommand($"SELECT * FROM book_issue_tbl where book_id='{BookID.Text.Trim()}';", Con1.Connect());
SqlDataAdapter addaptor = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
addaptor.Fill(dt);
string stock = (Int32.Parse(Stock.Text.Trim()) - dt.Rows.Count).ToString();
string s = CurrentStock.Text.Trim();
string gen = "";
foreach (int i in ListBox_Genre.GetSelectedIndices())
{
gen += $"{ListBox_Genre.Items[i].Text},";
}
string filename;
if (FileUpload1.HasFile)
{
filename = $"books/{Path.GetFileName(FileUpload1.PostedFile.FileName)}";
}
else
{
filename = "gol";
}
cmd = new SqlCommand($"INSERT INTO book_master_tbl(book_id,book_name,author_name,publisher_name,publish_data,language," +
$"edition,book_cost,nr_of_pages,book_description,actual_stock,current_stock,book_img_link,genre) values('{BookID.Text.Trim()}','{BookName.Text.Trim()}'," +
$"'{DropDownList_AuthorName.SelectedValue.Trim()}','{DropDownList_Publisher.SelectedValue.Trim()}','{Date.Text.Trim()}'," +
$"'{DropDownList_Language.SelectedValue.Trim()}','{Edition.Text.Trim()}','{BookCost.Text.Trim()}','{Pages.Text.Trim()}'," +
$"'{Description.Text.Trim()}','{Stock.Text.Trim()}','{stock}', @file,@gen)", Con1.Connect());
cmd.Parameters.AddWithValue("@file", filename);
cmd.Parameters.AddWithValue("@gen", gen);
cmd.ExecuteNonQuery();
Response.Write("<script>alert(' Successful ');</script>");
Show();
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "');</script>");
}
}
else
{
Response.Write("<script>alert('Already In');</script>");
}
Show();
}
void UpdateBookByID()
{
if (CheckIfBookExists())
{
try
{
/*int actual_stock = Convert.ToInt32(TextBox_ActualStock.Text.Trim());
* int current_stock = Convert.ToInt32(TextBox_CurrentStock.Text.Trim());
*
* if (global_actual_stock == actual_stock)
* {
*
* }
* else
* {
* if (actual_stock < global_issued_books)
* {
* Response.Write("<script>alert('Actual Stock value cannot be less than the Issued books');</script>");
* return;
* }
* else
* {
* current_stock = actual_stock - global_issued_books;
* TextBox_CurrentStock.Text = "" + current_stock;
* }
* }*/
string genres = "";
foreach (int i in ListBox_Genre.GetSelectedIndices())
{
genres = genres + ListBox_Genre.Items[i] + ",";
}
genres = genres.Remove(genres.Length - 1);
string filepath = "~/book_inventory/books1";
string filename = Path.GetFileName(FileUpload.PostedFile.FileName);
if (filename == "" || filename == null)
{
filepath = global_filepath;
}
else
{
FileUpload.SaveAs(Server.MapPath("book_inventory/" + filename));
filepath = "~/book_inventory/" + filename;
}
SqlConnection con = new SqlConnection(strcon);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("UPDATE dbo.book_master_tbl set book_name=@book_name, " +
"genre=@genre, author_name=@author_name, publisher_name=@publisher_name, publish_date=@publish_date, " +
"language=@language, edition=@edition, book_cost=@book_cost, no_of_pages=@no_of_pages, " +
"book_description=@book_description, actual_stock=@actual_stock, current_stock=@current_stock, " +
"book_img_link=@book_img_link Where book_id='" + TextBox_BookId.Text.Trim() + "'", con);
cmd.Parameters.AddWithValue("@book_name", TextBox_BookName.Text.Trim());
cmd.Parameters.AddWithValue("@genre", genres);
cmd.Parameters.AddWithValue("@author_name", DropDownList_AuthorName.SelectedItem.Value);
cmd.Parameters.AddWithValue("@publisher_name", DropDownList_Publisher.SelectedItem.Value);
cmd.Parameters.AddWithValue("@publish_date", TextBox_Date.Text.Trim());
cmd.Parameters.AddWithValue("@language", DropDownList_Language.SelectedItem.Value);
cmd.Parameters.AddWithValue("@edition", TextBox_Edition.Text.Trim());
cmd.Parameters.AddWithValue("@book_cost", TextBox_BookCost.Text.Trim());
cmd.Parameters.AddWithValue("@no_of_pages", TextBox_Pages.Text.Trim());
cmd.Parameters.AddWithValue("@book_description", TextBox_BookDescription.Text.Trim());
cmd.Parameters.AddWithValue("@actual_stock", TextBox_ActualStock.Text.Trim());
cmd.Parameters.AddWithValue("@current_stock", TextBox_CurrentStock.Text.Trim());
cmd.Parameters.AddWithValue("@book_img_link", filepath);
cmd.ExecuteNonQuery();
con.Close();
BookTable.DataBind();
Response.Write("<script>alert('Book Updated Successfully');</script>");
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "');</script>");
}
}
else
{
Response.Write("<script>alert('Invalid Book ID');</script>");
}
}
