?using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Collections;
using System.Collections.Generic;
/// <summary>
///目錄的相關(guān)操作
/// </summary>
public class Category
{
    //定義靜態(tài)變量
    private const string PARM_CATEGORY_NAME = "@CategoryName";
    private const string PARM_CATEGORY_DESN = "@CategoryDescription";
    private const string SQL_INSERT_CATEGORIES = "INSERT INTO category (CategoryName,CategoryDescription) VALUES( @CategoryName,@CategoryDescription)";

    private const string SQL_SELECT_CATEGORIES = "SELECT CategoryID, CategoryName, CategoryDescription FROM Category";
    private const string SQL_SELECT_CATEGORY = "SELECT CategoryID, CategoryName, CategoryDescription FROM Category WHERE CategoryID = @CategoryID";
    private const string PARM_CATEGORY_ID = "@CategoryID";

    private const string SQL_DELETE_CATEGORIE = "DELETE  FROM Category WHERE CategoryID = @CategoryID";
    private const string SQL_SELECT_BOOK = "SELECT  productname FROM Product WHERE CategoryID = @CategoryID";

	public Category()
	{
	}
    public void AddCategory(CategoryInfo cate)
    {
        StringBuilder strSQL = new StringBuilder();
        SqlCommand cmd = new SqlCommand();
        // 獲取緩存的參數(shù)列表
        SqlParameter[] CategoryParms = GetCategoryParameters();
        // 設(shè)置參數(shù)的值
        CategoryParms[0].Value = cate.Name;
        CategoryParms[1].Value = cate.Description;
        //將參數(shù)添加到SQL命令中
        foreach (SqlParameter parm in CategoryParms)
            cmd.Parameters.Add(parm);

        // 創(chuàng)建連接字符串
        using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
        {

            // 添加SQL語句
            strSQL.Append(SQL_INSERT_CATEGORIES);
            conn.Open();
            //設(shè)置SqlCommand的屬性
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strSQL.ToString();
            //執(zhí)行添加語句
            cmd.ExecuteNonQuery();
            //清空參數(shù)列表
            cmd.Parameters.Clear();
        }
    }
    /// <summary>
    /// 返回或設(shè)置緩存的參數(shù)列表
    /// </summary>
    /// <returns>參數(shù)列表</returns>
    private static SqlParameter[] GetCategoryParameters()
    {
        //獲取緩存的參數(shù)
        SqlParameter[] parms = SqlHelper.GetCachedParameters(SQL_INSERT_CATEGORIES);
        //如果沒有緩存，則創(chuàng)建一個新的參數(shù)列表
        if (parms == null)
        {
            parms = new SqlParameter[] {
					new SqlParameter(PARM_CATEGORY_NAME, SqlDbType.VarChar, 50),
					new SqlParameter(PARM_CATEGORY_DESN, SqlDbType.VarChar, 300)};

            //將參數(shù)列表緩存起來
            SqlHelper.CacheParameters(SQL_INSERT_CATEGORIES, parms);
        }
        return parms;
    }
    /// <summary>
    /// 獲取所有的目錄
    /// </summary>	    	 
    public IList<CategoryInfo> GetCategories()
    {
        //初始化目錄列表
        IList<CategoryInfo> categories = new List<CategoryInfo>();

        //執(zhí)行返回數(shù)據(jù)集的方法
        using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_CATEGORIES, null))
        {
            //逐條讀取數(shù)據(jù)集中的數(shù)據(jù)記錄
            while (rdr.Read())
            {
                //用實體接收數(shù)據(jù)
                CategoryInfo cat = new CategoryInfo(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2));
                //將數(shù)據(jù)實體添加到目錄列表中
                categories.Add(cat);
            }
        }
        return categories;
    }

    /// <summary>
    ///　通過ID獲取目錄
    /// </summary>
    /// <param name="categoryId">目錄的id</param>
    /// <returns>返回指定的目錄</returns>
    public CategoryInfo GetCategory(int categoryId)
    {
        CategoryInfo category = null;
        //創(chuàng)建參數(shù)，并賦值
        SqlParameter parm = new SqlParameter(PARM_CATEGORY_ID, SqlDbType.Int);
        parm.Value = categoryId;

        //執(zhí)行查詢	
        using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_CATEGORY, parm))
        {
            if (rdr.Read())
                category = new CategoryInfo(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2));
            else
                category = new CategoryInfo();
        }
        return category;
    }
    /// <summary>
    /// 刪除指定ID的目錄
    /// </summary>
    /// <param name="catid">目錄的ID</param>
    /// <returns>是否成功</returns>
    public bool DelCategory(int catid)
    { 
        //首先判斷目錄下是否有書
        //創(chuàng)建參數(shù)，并賦值
        SqlParameter parm = new SqlParameter(PARM_CATEGORY_ID, SqlDbType.Int);
        parm.Value = catid;
        //執(zhí)行查詢	
        using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_BOOK, parm))
        {
            //表示此目錄下沒有書存在
            if (rdr.Read())
                return false;
        }
        //開始執(zhí)行刪除操作
        StringBuilder strSQL = new StringBuilder();
        SqlCommand cmd = new SqlCommand();
        //    //創(chuàng)建參數(shù)，并賦值
        //SqlParameter parm1 = new SqlParameter(PARM_CATEGORY_ID, SqlDbType.Int);
        //parm1.Value = catid;
        cmd.Parameters.Add(parm);

        // 創(chuàng)建連接字符串
        using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
        {
            // 添加SQL語句
            strSQL.Append(SQL_DELETE_CATEGORIE);
            conn.Open();
            //設(shè)置SqlCommand的屬性
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strSQL.ToString();
            //執(zhí)行添加語句
            cmd.ExecuteNonQuery();
            //清空參數(shù)列表
            cmd.Parameters.Clear();
            return true;
        }
    }
}