How to write class methods that return collections of instances

Question

I'm struggling to define a class method that populates and returns a collection of instances. The issue I don't know how to get around is that I have private attributes to populate.

Let's use the example of a Book class. I don't want the code to directly set (say) the availability of a book. I want the code to have to use a CheckOut method on a Book instance. So we have something like:

public class Book
{
  private int ID;
  private bool pAvailableForCheckout;

  public string Title { get; set; }
  public bool AvailableForCheckout { get { return pAvailableForCheckout } }

  // instance methods

  public Book(int BookID)
  {
     // Load book from DB by ID
  }
  public CheckOut()
  {
     // perform everything involved with checking a book out
  }
  // .. other methods like saving a book, checking out books etc.

  // class method

  public static List<Book> FindAll()
  {
     // load Dataset of books
     // foreach record in DB, use the Book(int BookID) constructor and add to List
     // return list of books
  }
}

So, I can do use this in my code:

foreach(Book curBook in Book.FindAll())
  { /* do something with book */ }

The problem with the above implementation is that I have to use N+1 hits to the database to load all the books instead of just 1 query. How do I get around this?

I'm sure this is programming 101, but I needed to ask.

Answer 1

Why don't you check the availability of the book at database side using a SQL where statement?

Answer 2

You could create a protected constructor which populates the private properties directly.

Answer 3

The foreach should be iterating over a list of already instantiated objects, they won't need to connect to the DB.

You need to create a constructor that accepts the properties of your book object so that you can instantiate a book from an existing set of data rather than a new hit to the DB.

so:

Constructor:

public book (String title, String avail) {Title=title...}

And in the method

public static void FindAll()
{
List<Books> books = new List<books>();
using (Sqlconnection conn = new sqlconnection(connstring))
using (sqlcommand cmd = new SqlCommand("select title, available from book ", conn)
{
  SqlDatareader dr = cmd.executereader()
  while (dr.read())
  {
    books.add(new Book(dr["title"], dr["avail"])
  }

}

foreach(Book curBook in Book.FindAll())
  { /* do something with book */ }

}

Answer 4

For an example somewhat extreme in its ideological purity:

First, an interface for classes that can retrieve objects of type T from the database given their ID:

interface IAdapter<T>
{
   T Retrieve(int id);
}

Now, the Book class, which no longer exposes a public constructor, but instead a static method that uses an IAdapter<Book> to retrieve the book from the database:

public class Book
{
    public static IAdapter<Book> Adapter { get; set; }

    public static Book Create(int id)
    {
       return Adapter.Retrieve(id);
    }

    // constructor is internal so that the Adapter can create Book objects
    internal Book() { }

    public int ID { get; internal set; }
    public string Title { get; internal set; }
    public bool AvailableForCheckout { get; internal set; }

}

You have to write the class implementing IAdapter<Book> yourself, and assign Book.Adapter to an instance of it so that Book.Create() will be able to pull things from the database.

I say "ideological purity" because this design enforces a pretty rigid separation of concerns: there's nothing in the Book class that knows how to talk to the database - or even that there is a database.

For instance, here's one possible implementation of IAdapter<Book>:

public class DataTableBookAdapter : IAdapter<Book>
{
   public DataTable Table { get; set; }
   private List<Book> Books = new List<Book>();

   Book Retrieve(int id)
   {
      Book b = Books.Where(x => x.ID = id).FirstOrDefault();
      if (b != null)
      {
         return b;
      }

      BookRow r = Table.Find(id);
      b = new Book();

      b.ID = r.Field<int>("ID");
      b.Title = r.Field<string>("Title");
      b.AvailableForCheckout = r.Field<bool>("AvailableForCheckout");

      return b;
   }
}

Some other class is responsible for creating and populating the DataTable that this class uses. You could write a different implementation that uses a SqlConnection to talk to the database directly.

You can even write this:

public IAdapter<Book> TestBookAdapter : IAdapter<Book>
{
   private List<Book> Books = new List<Book>();

   public TestBookAdapter()
   {
      Books.Add(new Book { ID=1, Title="Test data", AvailableForCheckout=false };
      Books.Add(new Book { ID=2, Title="Test data", AvailableForCheckout=true };
   }

   Book Retrieve(int id)
   {
      return Books.Where(x => x.ID == id);
   }
}

This implementation doesn't use a database at all - you'd use this when writing unit tests for the Book class.

Note that both of these classes maintain a private List<Book> property. This guarantees that every time you call Book.Create() with a given ID, you get back the same Book instance. There's an argument to be made for making this a feature of the Book class instead - you'd make a static private List<Book> property in Book and write logic to make the Create method maintain it.

You use the same approach for pushing data back to the database - add Update, Delete, and Insert methods to IAdapter<T> and implement them in your adapter classes, and have Book call those methods at the appropriate time.