coursemanagerdal.cs

guan yu pai ke xi tong de ruan jian

        #endregion

        #region 获得专业技术方向列表
        /// <summary>
        /// 获得专业技术方向列表
        /// </summary>
        /// <returns>专业技术方向列表</returns>
        public IList<Speciality> GetSpecialities()
        {
            string sql = "SELECT SpecialityId, Title FROM Specialities";

            IList<Speciality> list = new List<Speciality>();
            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql))
                {
                    while (reader.Read())
                    {
                        Speciality speciality = new Speciality();
                        speciality.SpecialityId = reader.GetInt32(0);
                        speciality.Title = reader.GetString(1);

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

            return list;
        }
        #endregion

        #region 获得专业技术方向
        /// <summary>
        /// 获得专业技术方向
        /// </summary>
        /// <param name="specialityId">技术方向编号</param>
        /// <returns>专业技术方向实体对象</returns>
        public Speciality GetSpeciality(int specialityId)
        {
            string sql = "SELECT SpecialityId, Title FROM Specialities WHERE SpecialityId = @SpecialityId";
            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@SpecialityId", specialityId)
            };

            Speciality speciality = null;
            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql, parms))
                {
                    if (reader.Read())
                    {
                        speciality = new Speciality();
                        speciality.SpecialityId = reader.GetInt32(0);
                        speciality.Title = reader.GetString(1);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return speciality;
        }
        #endregion

        #region 获得某教学产品的指定学习阶段的所有课程
        /// <summary>
        /// 获得某教学产品的指定学习阶段的所有课程
        /// </summary>
        /// <param name="productId">产品版本</param>
        /// <param name="sectionCode">学习阶段</param>
        /// <returns>课程列表</returns>
        public IList<Course> GetCourses(int productId, string sectionCode)
        {
            string sql = "SELECT CourseId, ProductId, SectionCode, Title, CourseCode, SpecialityId FROM Courses WHERE ProductId = @ProductId AND SectionCode = @SectionCode";
            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@ProductId", productId),
                new SqlParameter("@SectionCode", sectionCode)
            };

            IList<Course> list = new List<Course>();
            int _productId = 0;
            string _sectionCode = string.Empty;
            int _specialityId = 0;
            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql, parms))
                {
                    while (reader.Read())
                    {
                        Course course = new Course();
                        course.CourseId = reader.GetInt32(0);

                        _productId = reader.GetInt32(1);
                        course.Product = new CourseManagerDAL().GetProduct(_productId);

                        _sectionCode = reader.GetString(2);
                        course.Section = new CourseManagerDAL().GetSection(_sectionCode);

                        course.Title = reader.GetString(3);
                        course.CourseCode = reader.GetString(4);

                        _specialityId = reader.GetInt32(5);
                        course.Speciality = new CourseManagerDAL().GetSpeciality(_specialityId);

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

            return list;
        }
        #endregion

        #region 根据课程编号获取课程
        /// <summary>
        /// 根据课程编号获取课程
        /// </summary>
        /// <param name="courseId">课程编号</param>
        /// <returns>课程实体对象</returns>
        public Course GetCourse(int courseId)
        {
            string sql = "SELECT CourseId, ProductId, SectionCode, Title, CourseCode, SpecialityId FROM Courses WHERE CourseId = @CourseId";
            
            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@CourseId", courseId)
            };

            Course course = null;
            int _productId = 0;
            string _sectionCode = string.Empty;
            int _specialityId = 0;
            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql, parms))
                {
                    while (reader.Read())
                    {
                        course = new Course();
                        course.CourseId = reader.GetInt32(0);

                        _productId = reader.GetInt32(1);
                        course.Product = new CourseManagerDAL().GetProduct(_productId);

                        _sectionCode = reader.GetString(2);
                        course.Section = new CourseManagerDAL().GetSection(_sectionCode);

                        course.Title = reader.GetString(3);
                        course.CourseCode = reader.GetString(4);

                        _specialityId = reader.GetInt32(5);
                        course.Speciality = new CourseManagerDAL().GetSpeciality(_specialityId);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return course;
        }
        #endregion

        #region 获得课程名称列表
        /// <summary>
        /// 获得课程名称列表
        /// </summary>
        /// <returns>课程名称列表</returns>
        public IList<Course> GetCourses()
        {
            string sql = "SELECT CourseId, Title FROM Courses ";

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

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql))
                {
                    while (reader.Read())
                    {
                        Course course = new Course();
                        course.CourseId = reader.GetInt32(0);
                        course.Title = reader.GetString(1);

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

            return list;
        }
        #endregion

        #region 插入时检查课程名称是否存在
        /// <summary>
        /// 插入时检查课程名称是否存在
        /// </summary>
        /// <param name="title">课程名称</param>
        /// <param name="productId">产品名称</param>
        /// <param name="sectionCode">阶段名称</param>
        /// <returns>true = 有, false = 没有.</returns>
        public bool CheckIsExits(string title, int productId, string sectionCode)
        {
            string sql = " SELECT Title, ProductId, SectionCode FROM Courses WHERE Title=@Title "
                       + " AND ProductId=@ProductId AND SectionCode=@SectionCode";

            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@Title", title),
                new SqlParameter("@ProductId", productId),
                new SqlParameter("@SectionCode", sectionCode)
            };

            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
    }
}