using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Models;
using IDAL;
/************************************
 * 類名：ClassService
 * 創建日期：2007-7-13
 * 功能描述：提供班級信息操作
 * *********************************/
namespace DAL.SqlServer
{
   public class ClassService:IClassService
   {
       #region Private Members
       //從配置文件中讀取數據庫連接字符串
       private readonly string connString = ConfigurationManager.ConnectionStrings["MySchoolConnectionString"].ToString();
       private readonly string dboOwner = ConfigurationManager.ConnectionStrings["DataBaseOwner"].ToString();
       #endregion

       #region Public Methods
       /// <summary>
       /// 通過班級名稱得到班級ＩＤ
       /// </summary>
       /// <param name="className">班級名稱</param>
       /// <returns>班級ＩＤ</returns>
       public  string GetClassIDByClassName(string className)
       {
           string number = string.Empty;
           using (SqlConnection conn = new SqlConnection(connString))
           { 
               SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_SelectClassIDByClassName", conn);
               objCommand.CommandType = CommandType.StoredProcedure;
               objCommand.Parameters.Add("@ClassName", SqlDbType.NVarChar, 50).Value = className;
               conn.Open();
               using (SqlDataReader objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection))
               {
                   if(objReader.Read())
                       number = Convert.ToString(objReader["ClassID"]);
                   objReader.Close();
                   objReader.Dispose();
               }
               conn.Close();
               conn.Dispose();
               return number;
           }
       }
       /// <summary>
       /// 通過年級ＩＤ得到年級對應的班級
       /// </summary>
       /// <param name="gradeID">年級ＩＤ</param>
       /// <returns>班級集合</returns>
       public  ArrayList GetClassByGradeID(int gradeID)
       {
           ArrayList classList = new ArrayList();
           using (SqlConnection conn = new SqlConnection(connString))
           {
               SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_SelectClassesByGradeID", conn);
               objCommand.CommandType = CommandType.StoredProcedure;
               objCommand.Parameters.Add("@GradeID", SqlDbType.Int).Value = gradeID;
               conn.Open();
               using (SqlDataReader objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection))
               {
                   while(objReader.Read())
                       classList.Add(objReader["ClassName"]);
                   objReader.Close();
                   objReader.Dispose();
               }
               conn.Close();
               conn.Dispose();
           }
           return classList;
       }
        #endregion
   }
}