list 如何取值 怎么改

Question

  我如何改啊 我用list存储一个对象 其中这个对象中还有一个对象 可是我在数据库查讯中 提示未关闭数据库 可是改了 又是一打开数据库了  
public IList<Book> GetBook() {
            IList<Book> list = new List<Book>();
            DBHelper db = new DBHelper();
            string sql = "select * from Books";
            int publisherId;
            int caategoryId;
            SqlDataReader reader = db.GetReader(sql);
            while (reader.Read())
            {
                Book book = new Book();
                book.Id = (int)reader["Id"];
                book.Title = (string)reader["Title"];
                book.Author = (string)reader["Author"]; ;
                book.PublishDate = (DateTime)reader["PublishDate"];
                book.ISBN = (string)reader["ISBN"];
                book.WordsCount = (int)reader["WordsCount"];
                book.UnitPrice = (decimal)reader["UnitPrice"];
                book.ContentDescription = (string)reader["ContentDescription"];
                book.AurhorDescription = (string)reader["AurhorDescription"];
                book.EditorComment = (string)reader["EditorComment"];
                book.TOC = (string)reader["TOC"];
                book.Clicks = (int)reader["Clicks"];
                publisherId = (int)reader["PublisherId"];
                caategoryId = (int)reader["CategoryId"];
                
                book.Publisher = new PublisherService().GetPublisherById(publisherId);
                book.Category = new CategoryService().GetCategoryById(caategoryId);
                list.Add(book);
            }
            reader.Close();
            return list;
        }

Answer 1

  book.Publisher = new PublisherService().GetPublisherById(publisherId);
  book.Category = new CategoryService().GetCategoryById(caategoryId);
你放到reader.close()后赋值,就是重新循环list 给上面的赋值

Answer 2

List泛型取对象。让我想到了linq.
List<Book> list = new List<Book>();
var bookArray = from books in DataContext.Books;
foreach(var item in bookArray)
{
   Book bk = new Book();
   ...
   list.Add(bk);
}

Answer 3

这么写当然不行,要不你先查询一个book集合放在DataTable里,再遍历给对象里的对象赋值.只能这样了,我们一直都这么做.

Answer 4

book.Publisher = new PublisherService().GetPublisherById(publisherId);
可以实现的
如IDataReader 

Answer 5

修改一下查询语句（用select a...inner join...），将publisher及id一次查询出来就不用再次去查