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;
/************************************
 * 類名：GradeService
 * 創建日期：2007-7-13
 * 功能描述：提供班級信息操作
 * *********************************/
namespace DAL.SqlServer
{
    public class GradeService : IGradeService
    {
        #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>
        /// <returns>年級集合</returns>
        public  List<Grade> GetAllGrades()
        {
            List<Grade> GradeList = new List<Grade>();

            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_SelectGradesAll", conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                conn.Open();
                using (SqlDataReader objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (objReader.Read())
                    {
                        Grade grade = new Grade();
                        grade.Name = Convert.ToString(objReader["GradeName"]);
                        GradeList.Add(grade);
                    }
                    objReader.Close();
                    objReader.Dispose();
                }
                conn.Close();
                conn.Dispose();
            }
            return GradeList;
        }
        /// <summary>
        /// 通過年級名稱得到年級ＩＤ
        /// </summary>
        /// <param name="gradeName">年級名稱</param>
        /// <returns>年級ＩＤ</returns>
        public  int GetGradeByGradeName(string gradeName)
        {
            int number = 0;
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_SelectGradeByGradeName", conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.Add("@GradeName", SqlDbType.NVarChar, 50).Value = gradeName;
                conn.Open();
                using (SqlDataReader objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (objReader.Read())
                        number = Convert.ToInt32(objReader["GradeID"]);
                    objReader.Close();
                    objReader.Dispose();
                }
                conn.Close();
                conn.Dispose();
            }
            return number;
        }
        #endregion
    }
}