使用外键对象示例

using System;
using System.Collections.Generic;
using System.Text;
using MyBookShop.Models;
using MyBookShop.DBUtility;
using System.Data.SqlClient;
using System.Data;

namespace MyBookShop.DAL
{
    public class BookService
    {
        public BookService() { }
      
        /// <summary>
        /// 通过查询book表中的id返回一个实体类Book
        /// </summary>
        /// <param name="id"></param>
        /// <returns>Book</returns>
        public static Book GetBookById(int id)
        {
            string sql = "select * from Books where Id=@Id";
            int publisherId = 0;
            int categoryId = 0;
            //传参
            SqlParameter para=new SqlParameter("@Id",id);
            //返回一个sqldatareader对象
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.connString, CommandType.Text, sql, para))
            {
                if (dr.Read())
                {
                    Book book = new Book();
                    book.Id = Convert.ToInt32(dr["Id"]);
                    book.Title = Convert.ToString(dr["Title"]);
                    book.Author = Convert.ToString(dr["Author"]);
                    book.PublishDate = (DateTime)dr["PublishDate"];
                    book.ISBN = Convert.ToString(dr["ISBN"]);
                    book.WordsCount = Convert.ToInt32(dr["WordsCount"]);
                    book.UnitPrice = (decimal)dr["UnitPrice"];
                    book.ContentDescription = Convert.ToString(dr["ContentDescription"]);
                    book.AuthorDescription = Convert.ToString(dr["AurhorDescription"]);
                    book.EditorComment = Convert.ToString(dr["EditorComment"]);
                    book.TOC = Convert.ToString(dr["TOC"]);
                    book.Clicks = Convert.ToInt32(dr["Clicks"]);
                    //分别获取外键表对象的id
                    publisherId = Convert.ToInt32(dr["PublisherId"]);
                    categoryId = Convert.ToInt32(dr["CategoryId"]);
                    //调用方法来进行获取外键表的对象
                    book.Publisher = PublisherService.GetPublisherById(publisherId);
                    book.Category = CategoryService.GetCategoryById(categoryId);

                    return book;

                }
                else           //必须写esle呀,不然提示,并非所有的代码都有返回值
                {
                    dr.Close();
                    return null;
                }
            }
           
        }