ADO.NET for the Object-Oriented Programmer – Part Two

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Diagnostics;
using ObjectDataBinding.Model;

namespace ObjectDataBinding.DataAccess
{
    class AuthorListDAO
    {
    	#region Declarations

        #endregion

    	#region Constructor

        public AuthorListDAO()
        {
        }

    	#endregion

    	#region Methods

        public void LoadAuthorList(AuthorList authorList)
        {
            // Build query to get authors and their books
            StringBuilder sqlQuery = new StringBuilder();
            sqlQuery.Append("Select AuthorID, LastName, FirstName, SSNumber From Authors; ");
            sqlQuery.Append("Select BookID, SkuNumber, AuthorID, Title, Price From Books");

            // Get a data set from the query
            DataSet dataSet = DataProvider.GetDataSet(sqlQuery.ToString());

            // Create variables for data set tables
            DataTable authorsTable = dataSet.Tables[0];
            DataTable booksTable = dataSet.Tables[1];

            // Create a data relation from Authors (parent table) to Books (child table)
            DataColumn parentColumn = authorsTable.Columns["AuthorID"];
            DataColumn childColumn = booksTable.Columns["AuthorID"];
            DataRelation authorsToBooks = new DataRelation("AuthorsToBooks", parentColumn, childColumn);
            dataSet.Relations.Add(authorsToBooks);

            // Load our AuthorList from the data set
            AuthorItem nextAuthor = null;
            BookItem nextBook = null;
            foreach (DataRow parentRow in authorsTable.Rows)
            {
                // Create a new author
                bool dontCreateDatabaseRecord = false;
                nextAuthor = new AuthorItem(dontCreateDatabaseRecord);

                // Fill in author properties
                nextAuthor.ID = Convert.ToInt32(parentRow["AuthorID"]);
                nextAuthor.FirstName = parentRow["FirstName"].ToString();
                nextAuthor.LastName = parentRow["LastName"].ToString();
                nextAuthor.LastName = parentRow["LastName"].ToString();
                nextAuthor.SSNumber = parentRow["SSNumber"].ToString();

                // Get author's books
                DataRow[] childRows = parentRow.GetChildRows(authorsToBooks);

                // Create BookItem object for each of the authors books
                foreach (DataRow childRow in childRows)
                {
                    // Create a new book
                    nextBook = new BookItem();

                    // Fill in book's properties
                    nextBook.ID = Convert.ToInt32(childRow["BookID"]);
                    nextBook.SkuNumber = childRow["SkuNumber"].ToString();
                    nextBook.Title = childRow["Title"].ToString();
                    nextBook.Price = Convert.ToDecimal(childRow["Price"]);

                    // Add the book to the author
                    nextAuthor.Books.Add(nextBook);
                }

                // Add the author to the author list
                authorList.Add(nextAuthor);
            }

            // Dispose of the data set
            dataSet.Dispose();
        }

    	#endregion
    }
}