autocoursedal.cs

guan yu pai ke xi tong de ruan jian

using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using Y2T03.CourseScheduler.CourseModel;
using Y2T03.CourseScheduler.IDAL;

namespace Y2T03.CourseScheduler.CourseDAL
{
    /// <summary>
    /// AutoCourseDAL 的数据访问层
    /// </summary>
    public class AutoCourseDAL : IAutoCourse
    {
        public AutoCourseDAL()
        { }

        #region 获得周信息列表
        /// <summary>
        /// 获得周信息列表
        /// </summary>
        /// <returns>周信息列表</returns>
        public IList<Week> GetWeeks()
        {
            string sql = "SELECT WeekId, Title FROM Weeks";

            IList<Week> list = new List<Week>();

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql))
                {
                    while (reader.Read())
                    {
                        Week week = new Week();

                        week.WeekId = reader.GetInt32(0);
                        week.Title = reader.GetString(1);

                        list.Add(week);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return list;
        }
        #endregion

        #region 获得周信息
        /// <summary>
        /// 获得周信息
        /// </summary>
        /// <param name="weekId">周序号</param>
        /// <returns>周实体对象</returns>
        public Week GetWeek(int weekId)
        {
            string sql = "SELECT WeekId, Title FROM Weeks WHERE WeekId = @WeekId";

            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@WeekId", weekId)
            };

            Week week = null;

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql, parms))
                {
                    if (reader.Read())
                    {
                        week = new Week();

                        week.WeekId = reader.GetInt32(0);
                        week.Title = reader.GetString(1);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return week;
        }
        #endregion

        #region 获得教学时段列表
        /// <summary>
        /// 获得教学时段列表
        /// </summary>
        /// <returns>教学时段列表</returns>
        public IList<TeachingTime> GetTeachingTimes()
        {
            string sql = "SELECT TimeId, Title FROM TeachingTimes";

            IList<TeachingTime> list = new List<TeachingTime>();

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql))
                {
                    while (reader.Read())
                    {
                        TeachingTime teachingTime = new TeachingTime();

                        teachingTime.TimeId = reader.GetInt32(0);
                        teachingTime.Title = reader.GetString(1);

                        list.Add(teachingTime);
                    }
                }
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return list;
        }
        #endregion

        #region 获得教学时段
        /// <summary>
        /// 获得教学时段
        /// </summary>
        /// <param name="timeId">教学时间编号</param>
        /// <returns>教学时间实体对象</returns>
        public TeachingTime GetTeachingTime(int timeId)
        {
            string sql = "SELECT TimeId, Title FROM TeachingTimes WHERE TimeId = @TimeId";

            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@TimeId", timeId)
            };

            TeachingTime teachingTime = null;

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql, parms))
                {
                    if (reader.Read())
                    {
                        teachingTime = new TeachingTime();

                        teachingTime.TimeId = reader.GetInt32(0);
                        teachingTime.Title = reader.GetString(1);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return teachingTime;
        }
        #endregion

        #region 获取状态为正常的班级列表
        /// <summary>
        /// 获取状态为正常的班级列表
        /// </summary>
        /// <returns>班级列表</returns>
        public IList<Class> GetNormalClasses()
        {
            string sql = "SELECT ClassCode, SectionCode, TypeCode, ProductId, ClassStatus FROM Classes WHERE ClassStatus=0";

            IList<Class> list = new List<Class>();

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql))
                {
                    while (reader.Read())
                    {
                        Class classe = new Class();

                        classe.ClassCode = reader.GetString(0);

                        list.Add(classe);
                    }

                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return list;
        }
        #endregion

        #region 获得教员所带班级的数量按降序排列
        /// <summary>
        /// 获得教员所带班级的数量按降序排列
        /// </summary>
        /// <returns>Teacher实体对象</returns>
        public IList<Teacher> GetTeachers()
        {
            string sql = "SELECT [Name], COUNT(Teachings.TeacherId) AS countent "
                        + "FROM Teachings "
                        + "INNER JOIN Teachers "
                        + "ON (Teachings.TeacherId = Teachers.TeacherId) "
                        + "GROUP BY [Name] "
                        + "ORDER BY countent DESC";

            IList<Teacher> list = new List<Teacher>();

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql))
                {
                    while (reader.Read())
                    {
                        Teacher teacher = new Teacher();

                        teacher.Name = reader.GetString(0);

                        list.Add(teacher);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return list;
        }
        #endregion

        #region 获得某教员所带脱产班级列表
        /// <summary>
        /// 获得某教员所带脱产班级列表
        /// </summary>
        /// <param name="name">教员名字</param>
        /// <returns>班级列表</returns>
        public IList<Class> GetClasses(string name)
        {
            string sql = "SELECT [Name], Teachings.ClassCode "
                        + " FROM Teachings "
                        + " INNER JOIN Teachers "
                        + " ON (Teachings.TeacherId = Teachers.TeacherId AND [Name] = @Name) "
                        + " INNER JOIN Classes "
                        + " ON (Teachings.ClassCode = Classes.ClassCode) "
                        + " WHERE TypeCode = 'T' AND ClassStatus = 0 "
                        + " GROUP BY [Name], Teachings.ClassCode";

            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@Name", name)
            };

            IList<Class> list = new List<Class>();

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql, parms))
                {
                    while (reader.Read())
                    {
                        Class classe = new Class();

                        classe.ClassCode = reader.GetString(1);



                        list.Add(classe);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return list;
        }
        #endregion

        #region 获得某教员所带要加课的脱产班级列表
        /// <summary>
        /// 获得某教员所带要加课的脱产班级列表
        /// </summary>
        /// <param name="name">教员名字</param>
        /// <returns>班级列表</returns>
        public IList<Class> GetAddCourseClasses(string name)
        {
            string sql = "SELECT [Name], Teachings.ClassCode "
                        + " FROM Teachings "
                        + " INNER JOIN Teachers "
                        + " ON (Teachings.TeacherId = Teachers.TeacherId AND [Name] = @Name) "
                        + " INNER JOIN Classes "
                        + " ON (Teachings.ClassCode = Classes.ClassCode) "
                        + " WHERE Classes.SectionCode <> 'S1' AND TypeCode = 'T' AND ClassStatus = 0 "
                        + " GROUP BY [Name], Teachings.ClassCode";

            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@Name", name)
            };

            IList<Class> list = new List<Class>();

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql, parms))
                {
                    while (reader.Read())
                    {
                        Class classe = new Class();