public static Copy Find(int copyId) { SqlConnection conn = DB.Connection(); conn.Open(); SqlDataReader rdr = null; SqlCommand cmd = new SqlCommand("SELECT * FROM copies WHERE id = @CopyId;", conn); SqlParameter copyIdParameter = new SqlParameter(); copyIdParameter.ParameterName = "@CopyId"; copyIdParameter.Value = copyId; cmd.Parameters.Add(copyIdParameter); int foundCopyId = 0; int foundCopyBookId = 0; rdr = cmd.ExecuteReader(); while(rdr.Read()) { foundCopyId = rdr.GetInt32(0); foundCopyBookId = rdr.GetInt32(1); } Copy foundCopy = new Copy(foundCopyBookId, foundCopyId); if(rdr!=null) rdr.Close(); if(conn!=null) conn.Close(); return foundCopy; }
public List<Copy> GetCheckOutRecord(bool history) { List<Copy> checkedOutCopies = new List<Copy> {}; SqlConnection conn = DB.Connection(); SqlDataReader rdr = null; conn.Open(); SqlCommand cmd = new SqlCommand("SELECT copies.* FROM patrons JOIN checkouts ON (checkouts.patron_id = patrons.id) JOIN copies ON (checkouts.copy_id = copies.id) WHERE patrons.id=@PatronId AND checkouts.returned=@Returned ORDER BY due_date;", conn); SqlParameter patronIdParameter = new SqlParameter(); patronIdParameter.ParameterName = "@PatronId"; patronIdParameter.Value = this.GetId(); cmd.Parameters.Add(patronIdParameter); SqlParameter returnedParameter = new SqlParameter(); returnedParameter.ParameterName = "@Returned"; returnedParameter.Value = history; cmd.Parameters.Add(returnedParameter); rdr = cmd.ExecuteReader(); while(rdr.Read()) { int Id = rdr.GetInt32(0); int bookId = rdr.GetInt32(1); Copy newCopy = new Copy(bookId, Id); checkedOutCopies.Add(newCopy); } if (rdr != null) { rdr.Close(); } if (conn != null) { conn.Close(); } return checkedOutCopies; }
public List<Copy> GetCopies() { List<Copy> allCopies = new List<Copy>{}; SqlConnection conn = DB.Connection(); SqlDataReader rdr = null; conn.Open(); SqlCommand cmd = new SqlCommand("SELECT * FROM copies WHERE book_id = @BookId;", conn); SqlParameter idParameter = new SqlParameter(); idParameter.ParameterName = "@BookId"; idParameter.Value = this.GetId(); cmd.Parameters.Add(idParameter); rdr = cmd.ExecuteReader(); while(rdr.Read()) { int Id = rdr.GetInt32(0); int bookId = rdr.GetInt32(1); Copy newCopy = new Copy(bookId, Id); allCopies.Add(newCopy); } if (rdr != null) { rdr.Close(); } if (conn != null) { conn.Close(); } return allCopies; }