public ActionResult AddBook(Book book)
{
if (ModelState.IsValid)
{
String msg = book.AddBook(book);
ViewBag.Message = msg;
}
return ViewIfAdminLoggedIn();
}
public ActionResult UpdateDeleteBook(Book book)
{
if (ModelState.IsValid)
{
if (Request.Form["delete"] == "yes") { ViewBag.Message = book.DeleteBook(book); }
else { ViewBag.Message = book.UpdateBook(book); }
}
ViewBag.Results = book.AllBooks();
return ViewIfAdminLoggedIn();
}
public List<Book> Search(String term)
{
SqlConnection con;
DataTable table = new DataTable();
con = new SqlConnection(@"Data Source = (LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\LMS_DB.mdf;Integrated Security = True");
using (var cmd = new SqlCommand("SearchBy" + criteria, con))
using (var da = new SqlDataAdapter(cmd))
{
cmd.Parameters.Add("@" + criteria, SqlDbType.NVarChar).Value = term;
cmd.CommandType = CommandType.StoredProcedure;
da.Fill(table);
}
List<Book> Book_Array = new List<Book>();
foreach (DataRow row in table.Rows)
{
Book temp = new Book();
temp.Id = Convert.ToInt32(row["Id"].ToString().Trim());
temp.ISBN = (row["ISBN"].ToString().Trim());
temp.Title = (row["Title"].ToString().Trim());
temp.Author = (row["Author"].ToString().Trim());
temp.Category = (row["Category"].ToString().Trim());
temp.Quantity_Available = Convert.ToInt32(row["Quantity_Available"].ToString().Trim());
temp.Location = (row["Location"].ToString().Trim());
temp.Publisher = (row["Publisher"].ToString().Trim());
temp.Year_Published = Convert.ToInt32(row["Year_Published"].ToString().Trim());
Book_Array.Add(temp);
}
return Book_Array;
}
public String AddBook(Book book)
{
try
{
SqlConnection conn = null;
conn = new SqlConnection(@"Data Source = (LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\LMS_DB.mdf;Integrated Security = True");
conn.Open();
SqlCommand cmd = new SqlCommand("dbo.Administrator_AddBook", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ISBN", SqlDbType.NVarChar).Value = book.ISBN;
cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = book.Title;
cmd.Parameters.Add("@Author", SqlDbType.NVarChar).Value = book.Author;
cmd.Parameters.Add("@Category", SqlDbType.NVarChar).Value = book.Category;
cmd.Parameters.Add("@Publisher", SqlDbType.NVarChar).Value = book.Publisher;
cmd.Parameters.Add("@Year_Published", SqlDbType.SmallInt).Value = book.Year_Published;
cmd.Parameters.Add("@Quantity_Available", SqlDbType.Int).Value = book.Quantity_Available;
cmd.Parameters.Add("@Location", SqlDbType.NVarChar).Value = book.Location;
cmd.ExecuteNonQuery();
return "Successfully added the book";
}
catch (SqlException sqlEx)
{
if (sqlEx.Message.StartsWith("Violation of UNIQUE KEY constraint"))
{
return "Duplicate ISBN. Check again.";
}
else
throw;
}
}
public String DeleteBook(Book book)
{
SqlConnection conn = null;
conn = new SqlConnection(@"Data Source = (LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\LMS_DB.mdf;Integrated Security = True");
conn.Open();
SqlCommand cmd = new SqlCommand("dbo.Administrator_DeleteBook", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Id", SqlDbType.NVarChar).Value = book.Id;
cmd.ExecuteNonQuery();
return "Successfully deleted the book";
}