classteachingmanagerdal.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>
    /// ClassTeachingManagerDAL 的数据访问层
    /// </summary>
    public class ClassTeachingManagerDAL : IClassTeachingManager
    {
        public ClassTeachingManagerDAL()
        { }

        #region 添加授课信息
        /// <summary>
        /// 添加授课信息
        /// </summary>
        /// <param name="teaching">授课实体对象</param>
        /// <returns>true = 成功, false = 失败.</returns>
        public bool Create(Teaching teaching)
        {
            string sql = "INSERT INTO Teachings(ClassCode,TeacherId,CourseId) VALUES(@ClassCode,@TeacherId,@CourseId)";

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@ClassCode", teaching.Classe.ClassCode),
                new SqlParameter("@TeacherId", teaching.Teacher.TeacherId),
                new SqlParameter("@CourseId",  teaching.Course.CourseId)
            };

            int rows = 0;

            try
            {
                rows = SQLDBHelper.ExecuteNonQuery(sql, param);
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return (rows > 0) ? true : false;
        }
        #endregion

        #region 根据班级类型获取状态为正常的班级列表的授课信息
        /// <summary>
        /// 根据班级类型获取状态为正常的班级列表的授课信息
        /// </summary>
        /// <param name="typeCode">班级名称</param>
        /// <returns>授课列表</returns>
        public IList<Teaching> GetClassTeachings(string typeCode)
        {
            string sql = " SELECT T.ClassCode, TeacherId, CourseId "
                       + " FROM Teachings AS T "
                       + " INNER JOIN Classes AS C "
                       + " ON C.ClassCode = T.ClassCode "
                       + " WHERE TypeCode = @TypeCode AND ClassStatus = 0";

            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@TypeCode", typeCode)
            };

            List<Teaching> list = new List<Teaching>();
            int teacherId = 0;
            int courseId = 0;

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

                        Teaching.Classe.ClassCode = reader.GetString(0);

                        teacherId = reader.GetInt32(1);
                        Teaching.Teacher = new TeacherManagerDAL().GetTeacher(teacherId);

                        courseId = reader.GetInt32(2);
                        Teaching.Course = new CourseManagerDAL().GetCourse(courseId);

                        list.Add(Teaching);
                    }

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

            return list;
        }
        #endregion

        #region 获取状态为正常班级的列表
        /// <summary>
        /// 获取状态为正常班级的列表
        /// </summary>
        /// <returns>班级列表</returns>
        public IList<Class> GetClasses()
        {
            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>
        /// <param name="teacherId">教员编号</param>
        /// <returns>学习阶段编号</returns>
        public string GetClassSectionByTeacherId(int teacherId)
        {
            string sql = " SELECT TeacherId, SectionCode, SpecialityId FROM Teachers WHERE TeacherId=@TeacherId";

            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@TeacherId", teacherId)
            };

            string sectionCode = string.Empty;

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql, parms))
                {
                    if (reader.Read())
                    {
                        sectionCode = reader.GetString(1);
                    }

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

            return sectionCode;
        }
        #endregion

        #region 根据班级编号获取学习阶段编号
        /// <summary>
        /// 根据班级编号获取学习阶段编号
        /// </summary>
        /// <param name="classCode">班级编号</param>
        /// <returns>学习阶段编号</returns>
        public string GetClassSectionByClassCode(string classCode)
        {
            string sql = " SELECT ClassCode, SectionCode FROM CLasses WHERE ClassCode=@ClassCode AND ClassStatus=0";

            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@ClassCode", classCode)
            };

            string sectionCode = string.Empty;

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql, parms))
                {
                    if (reader.Read())
                    {
                        sectionCode = reader.GetString(1);
                    }

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

            return sectionCode;
        }
        #endregion

        #region 根据教员编号获取该教员带班的数量
        /// <summary>
        /// 根据教员编号获取该教员带班的数量
        /// </summary>
        /// <param name="teacherId">教员编号</param>
        /// <returns>教员带班数量</returns>
        public int GetClassCountByTeacherId(int teacherId)
        {
            string sql = " SELECT COUNT(TeacherId) FROM Teachings WHERE TeacherId=@TeacherId GROUP BY TeacherId";

            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@TeacherId", teacherId)
            };

            int count = 0;

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql, parms))
                {
                    if (reader.Read())
                    {
                        count = reader.GetInt32(0) ;
                    }

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

            return count;
        }
        #endregion

        #region 获得授课班级列表
        /// <summary>
        /// 获得授课班级列表
        /// </summary>
        /// <returns>授课班级列表</returns>
        public IList<Teaching> GetClassTeachings()
        {
            string sql = "SELECT ClassCode, TeacherId, CourseId  FROM Teachings";

            List<Teaching> list = new List<Teaching>();
            int teacherId = 0;
            int courseId = 0;

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

                        Teaching.Classe.ClassCode = reader.GetString(0);

                        teacherId = reader.GetInt32(1);
                        Teaching.Teacher = new TeacherManagerDAL().GetTeacher(teacherId);

                        courseId = reader.GetInt32(2);
                        Teaching.Course = new CourseManagerDAL().GetCourse(courseId);

                        list.Add(Teaching);
                    }

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

            return list;
        }
        #endregion

        #region 获得某个班级的授课信息
        /// <summary>
        /// 获得某个班级的授课信息
        /// </summary>
        /// <param name="classCode">班级名称</param>
        /// <returns>授课实体对象</returns>
        public Teaching GetClassTeaching(string classCode)
        {
            string sql = "SELECT ClassCode, TeacherId, CourseId  FROM Teachings WHERE ClassCode=@ClassCode";

            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@ClassCode", classCode)
            };

            Teaching teaching = null;
            int teacherId = 0;
            int courseId = 0;

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

                        teaching.Classe.ClassCode = reader.GetString(0);

                        teacherId = reader.GetInt32(1);
                        teaching.Teacher = new TeacherManagerDAL().GetTeacher(teacherId);

                        courseId = reader.GetInt32(2);
                        teaching.Course = new CourseManagerDAL().GetCourse(courseId);
                    }

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

            return teaching;
        }
        #endregion

        #region 检查班级名称是否存在
        /// <summary>
        /// 检查班级名称是否存在
        /// </summary>
        /// <param name="classCode">班级名称</param>
        /// <returns>true = 有, false = 没有.</returns>
        public bool CheckIsExit(string classCode)
        {
            string sql = "SELECT ClassCode, TeacherId, CourseId  FROM Teachings WHERE ClassCode=@ClassCode";

            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@ClassCode", classCode)
            };

            bool flg = false;

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql, parms))
                {
                    if (reader.Read())
                    {
                        flg = true;
                    }

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

            return flg;
        }
        #endregion

        #region 更新授课信息
        /// <summary>
        /// 更新授课信息
        /// </summary>
        /// <param name="teaching">授课实体对象</param>
        /// <returns>true = 成功, false = 失败.</returns>
        public bool Update(Teaching teaching)
        {
            string sql = "UPDATE Teachings SET TeacherId = @TeacherId, CourseId = @CourseId WHERE ClassCode = @ClassCode";

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@TeacherId", teaching.Teacher.TeacherId),
                new SqlParameter("@CourseId",  teaching.Course.CourseId),
                new SqlParameter("@ClassCode", teaching.Classe.ClassCode)
            };

            int rows = 0;

            try
            {
                rows = SQLDBHelper.ExecuteNonQuery(sql, param);
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return (rows > 0) ? true : false;
        }
        #endregion

        #region 删除授课信息
        /// <summary>
        /// 删除授课信息
        /// </summary>
        /// <param name="classCode">班级名称</param>
        /// <returns>true = 成功, false = 失败.</returns>
        public bool Delete(string classCode)
        {
            string sql = "DELETE FROM Teachings WHERE ClassCode = @ClassCode";

            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@ClassCode", classCode)
            };

            int rows = 0;

            try
            {
                rows = SQLDBHelper.ExecuteNonQuery(sql, parms);
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return (rows > 0) ? true : false;
        }
        #endregion
    }
}