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;
}