//search for a book based on inputted title public static List <Checkout> SearchDueDate(DateTime dueDate) { List <Checkout> allDue = new List <Checkout> { }; SqlConnection conn = DB.Connection(); conn.Open(); SqlCommand cmd = new SqlCommand("SELECT * FROM checkouts WHERE due_date = @DueDate;", conn); cmd.Parameters.Add(new SqlParameter("@DueDate", dueDate)); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { int foundId = rdr.GetInt32(0); DateTime foundDueDate = rdr.GetDateTime(1); // DateTime foundReturnDate = rdr.GetDateTime(2); int foundPatronsId = rdr.GetInt32(3); int foundCopiesId = rdr.GetInt32(4); Checkout foundCheckout = new Checkout(foundDueDate, foundPatronsId, foundCopiesId, foundId); allDue.Add(foundCheckout); } if (rdr != null) { rdr.Close(); } if (conn != null) { conn.Close(); } return(allDue); }
public List <Checkout> GetCheckouts() { SqlConnection conn = DB.Connection(); conn.Open(); SqlCommand cmd = new SqlCommand("SELECT * FROM checkouts WHERE patron_id = @PatronId", conn); SqlParameter patronIdParameter = new SqlParameter("@PatronId", this.GetId()); cmd.Parameters.Add(patronIdParameter); SqlDataReader rdr = cmd.ExecuteReader(); List <Checkout> checkoutList = new List <Checkout> { }; while (rdr.Read()) { int id = rdr.GetInt32(0); int patronId = rdr.GetInt32(1); int copyId = rdr.GetInt32(2); string dueDate = rdr.GetDateTime(3).ToString(); Checkout foundCheckout = new Checkout(patronId, copyId, dueDate, id); checkoutList.Add(foundCheckout); } if (rdr != null) { rdr.Close(); } if (conn != null) { conn.Close(); } return(checkoutList); }
//Returns a book to the library and sets the return date in Checkout object public void ReturnBook(int patronId) { SqlConnection conn = DB.Connection(); conn.Open(); DateTime today = DateTime.Today; SqlCommand cmd = new SqlCommand("UPDATE checkouts SET date_returned = @ReturnDate WHERE patrons_id = @PatronId AND copies_id IN (SELECT id FROM copies WHERE books_id = @BookId);", conn); cmd.Parameters.Add(new SqlParameter("@PatronId", patronId)); cmd.Parameters.Add(new SqlParameter("@BookId", this.GetId())); cmd.Parameters.Add(new SqlParameter("@ReturnDate", today)); SqlDataReader rdr = cmd.ExecuteReader(); int myCheckoutId = 0; while (rdr.Read()) { myCheckoutId = rdr.GetInt32(0); Checkout myCheckout = Checkout.Find(myCheckoutId); Console.WriteLine("MYCHECKOUT: " + myCheckout.GetReturnDate()); myCheckout.UpdateReturnDate(today); } if (rdr != null) { rdr.Close(); } if (conn != null) { conn.Close(); } }