public static Checkout Find(int checkoutId) { MySqlConnection conn = DB.Connection(); conn.Open(); var cmd = conn.CreateCommand() as MySqlCommand; cmd.CommandText = @"SELECT * FROM checkouts WHERE id = @id;"; cmd.Parameters.AddWithValue("@id", checkoutId); var rdr = cmd.ExecuteReader() as MySqlDataReader; int id = 0; int bookId = 0; int patronId = 0; DateTime checkout = DateTime.MinValue; DateTime due = DateTime.MinValue; bool returned = false; while (rdr.Read()) { id = rdr.GetInt32(0); bookId = rdr.GetInt32(1); patronId = rdr.GetInt32(2); checkout = rdr.GetDateTime(3); due = rdr.GetDateTime(4); returned = rdr.GetBoolean(5); } Checkout foundCheckout = new Checkout(bookId, patronId, checkout, due, returned, id); conn.Close(); if (conn != null) { conn.Dispose(); } return(foundCheckout); }
public List <Checkout> GetReturnedCheckouts() { MySqlConnection conn = DB.Connection(); conn.Open(); var cmd = conn.CreateCommand() as MySqlCommand; cmd.CommandText = @"SELECT checkouts.* FROM patrons JOIN checkouts ON (checkouts.patrons_id = patrons.id) WHERE checkouts.patrons_id = @patronId AND returned = true ORDER BY due_date ASC;"; cmd.Parameters.AddWithValue("@patronId", this.Id); MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader; List <Checkout> allCheckouts = new List <Checkout> { }; while (rdr.Read()) { int id = rdr.GetInt32(0); int book_id = rdr.GetInt32(1); int patrons_id = rdr.GetInt32(2); DateTime checkout = rdr.GetDateTime(3); DateTime due = rdr.GetDateTime(4); bool returned = rdr.GetBoolean(5); Checkout newCheckout = new Checkout(book_id, patrons_id, checkout, due, returned, id); allCheckouts.Add(newCheckout); } conn.Close(); if (conn != null) { conn.Dispose(); } return(allCheckouts); }
public List <Checkout> GetAllPastCheckouts() { List <Checkout> allCheckouts = new List <Checkout> { }; MySqlConnection conn = DB.Connection(); conn.Open(); var cmd = conn.CreateCommand() as MySqlCommand; cmd.CommandText = @"SELECT * FROM checkouts WHERE (patron_id = @searchId) AND (is_returned = 1);"; cmd.Parameters.AddWithValue("@searchId", this.Id); MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader; while (rdr.Read()) { int id = rdr.GetInt32(0); int patronId = rdr.GetInt32(1); int copyId = rdr.GetInt32(2); DateTime checkoutDate = rdr.GetDateTime(3); DateTime dueDate = rdr.GetDateTime(4); bool isReturned = rdr.GetBoolean(5); Checkout newCheckout = new Checkout(patronId, copyId, checkoutDate, dueDate, isReturned, id); allCheckouts.Add(newCheckout); } conn.Close(); if (conn != null) { conn.Dispose(); } return(allCheckouts); }