[HttpPut]//Add a book to the library
public IHttpActionResult AddaBook([FromBody] SeeLibrary book)
{
using (var connection = new SqlConnection(connectionString))
{
var query = $@"INSERT INTO [dbo].[Books]
([Title]
,[Author]
,[IsCheckedOut])
VALUES (@Title, @Author, @IsCheckedOut)";
var cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@Title", book.Title);
cmd.Parameters.AddWithValue("@Author", book.Author);
cmd.Parameters.AddWithValue("@IsCheckedOut", book.IsCheckedOut);
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
return(Ok(book));
}
public List <SeeLibrary> ListofBooks()
{
var rv = new List <SeeLibrary>();
using (var connection = new SqlConnection(connectionString))
{
var sqlCommand = new SqlCommand
(@"SELECT *
FROM [dbo].[Books]
", connection);
connection.Open();
var reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
var book = new SeeLibrary(reader);
rv.Add(book);
}
connection.Close();
return(rv);
}
}
public IHttpActionResult CheckBookOut(int id)
{
using (var connection = new SqlConnection(connectionString))
{
// see if that book is checked out (GET FORM THE DATABASE)
var select = "SELECT * FROM Books WHERE Id = @Id";
var selectCmd = new SqlCommand(select, connection);
selectCmd.Parameters.AddWithValue("@Id", id);
connection.Open();
var reader = selectCmd.ExecuteReader();
var book = new SeeLibrary();
while (reader.Read())
{
book = new SeeLibrary(reader);
}
connection.Close();
if (book.IsCheckedOut)
{
return(Ok(new { Message = "The book is already checked out", DueBackDate = book.NeatDueBackDate }));
}
else
{
var query = @"UPDATE[dbo].[Books]
SET
[IsCheckedOut]= @IsCheckedOut
,[DueBackDate]= @DueBackDate
WHERE Id = @Id";
var cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@IsCheckedOut", !book.IsCheckedOut);
cmd.Parameters.AddWithValue("@DueBackDate", DateTime.Now.AddDays(10));
cmd.Parameters.AddWithValue("@Id", id);
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
return(Ok(new { Message = "You have Checked out a Book! It's due back on", DueBackDate = book.NeatDueBackDate }));
}
}
}
[HttpPost]//Update a books information
public IHttpActionResult UpdateaBook([FromBody] int id, SeeLibrary ListofBooks)
{
try
{
using (var connection = new SqlConnection(connectionString))
{
var query = $@"UPDATE[dbo].[Books]
SET[Title] = @Title
,[Author] = @Author
,[YearPublished] = @YearPublished
,[Genre] = @Genre
,[IsCheckedOut] = @IsCheckedOut
,[LastCheckedOutDate] = @LastCheckedOutDate
,[DueBackDate] = @DueBackDate
WHERE Id = @Id";
var cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@Title", ListofBooks.Title);
cmd.Parameters.AddWithValue("@Author", ListofBooks.Author);
cmd.Parameters.AddWithValue("@IsCheckedOut", ListofBooks.IsCheckedOut);
cmd.Parameters.AddWithValue("@YearPublished", ListofBooks.YearPublished);
cmd.Parameters.AddWithValue("@Genre", ListofBooks.Genre);
cmd.Parameters.AddWithValue("@LastCheckedOutDate", ListofBooks.LastCheckedOutDate);
cmd.Parameters.AddWithValue("@DueBackDate", ListofBooks.DueBackDate);
cmd.Parameters.AddWithValue("@Id", id);
connection.Open();
int rows = cmd.ExecuteNonQuery();
connection.Close();
return(Ok(rows));
}
}
catch (SqlException sqlEx)
{
//sqlEx = MessageBox.Show("there was an issue!");
return(Ok());
}
}
public IHttpActionResult CheckBookIn(int id)
{
using (var connection = new SqlConnection(connectionString))
{
// see if that book is checked out (GET FORM THE DATABASE)
var select = "SELECT * FROM Books WHERE Id = @Id";
var selectCmd = new SqlCommand(select, connection);
selectCmd.Parameters.AddWithValue("@Id", id);
connection.Open();
var reader = selectCmd.ExecuteReader();
var book = new SeeLibrary();
while (reader.Read())
{
book = new SeeLibrary(reader);
}
connection.Close();
if (!book.IsCheckedOut)
{
return(Ok(new { Message = "The book is already checked in", LastCheckedOutDate = book.LastCheckedOutDate }));
}
else
{
var query = $@"UPDATE[dbo].[Books]
SET
[IsCheckedOut]= @IsCheckedOut
WHERE Id = @Id";
var cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@IsCheckedOut", !book.IsCheckedOut);
cmd.Parameters.AddWithValue("@Id", id);
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
return(Ok(new { Message = "Thanks for bringing our book back! Have a giddy day!" }));
}
}
}