classmanagerdal.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>
    /// ClassRoomManagerDAL 的数据访问层
    /// </summary>
    public class ClassManagerDAL : IClassManager
    {
        public ClassManagerDAL()
        { }

        #region 新开班级
        /// <summary>
        /// 新开班级
        /// </summary>
        /// <param name="classe">班级实体对象</param>
        /// <returns>true = 成功, false = 失败.</returns>
        public bool Create(Class classe)
        {
            string sql = "INSERT INTO Classes(ClassCode, SectionCode, TypeCode, ProductId, ClassStatus) VALUES(@ClassCode, @SectionCode, @TypeCode, @ProductId, @ClassStatus)";
            
            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@ClassCode", classe.ClassCode),
                new SqlParameter("@SectionCode", classe.Section.SectionCode),
                new SqlParameter("@TypeCode", classe.ClassType.TypeCode),
                new SqlParameter("@ProductId", classe.Product.ProductId),
                new SqlParameter("@ClassStatus", classe.ClassStatus)
            };

            int rows = 0;

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

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

        #region 更新班级信息
        /// <summary>
        /// 更新班级信息
        /// </summary>
        /// <param name="classe">班级实体对象</param>
        /// <returns>true = 成功, false = 失败.</returns>
        public bool Update(Class classe)
        {
            string sql = "UPDATE Classes SET SectionCode = @SectionCode, TypeCode = @TypeCode, ProductId = @ProductId, ClassStatus = @ClassStatus WHERE ClassCode = @ClassCode ";
            
            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@SectionCode", classe.Section.SectionCode),
                new SqlParameter("@TypeCode", classe.ClassType.TypeCode),
                new SqlParameter("@ProductId", classe.Product.ProductId),
                new SqlParameter("@ClassStatus", classe.ClassStatus),
                new SqlParameter("@ClassCode", classe.ClassCode)
            };

            int rows = 0;

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

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

        #region 获取某个班级
        /// <summary>
        /// 获取某个班级
        /// </summary>
        /// <param name="classCode">班级编码</param>
        /// <returns>班级实体对象</returns>
        public Class GetClass(string classCode)
        {
            string sql = "SELECT ClassCode, SectionCode, TypeCode, ProductId, ClassStatus FROM Classes WHERE ClassCode = @ClassCode";
            
            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@ClassCode", classCode)
            };

            Class classe = null;

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql, parms))
                {
                    if (reader.Read())
                    {
                        classe = new Class();
                        classe.ClassCode = reader.GetString(0);

                        string sectionCode = reader.GetString(1);
                        classe.Section = new CourseManagerDAL().GetSection(sectionCode);

                        string typeCode = reader.GetString(2);
                        classe.ClassType = new ClassManagerDAL().GetClassType(typeCode);

                        int productId = reader.GetInt32(3);
                        classe.Product = new CourseManagerDAL().GetProduct(productId);

                        classe.ClassStatus = reader.GetInt32(4);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return classe;
        }
        #endregion

        #region 获取班级列表
        /// <summary>
        /// 获取班级列表
        /// </summary>
        /// <returns>班级列表</returns>
        public IList<Class> GetClasses()
        {
            string sql = "SELECT ClassCode, SectionCode, TypeCode, ProductId, ClassStatus FROM Classes";

            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);

                        string sectionCode = reader.GetString(1);
                        classe.Section = new CourseManagerDAL().GetSection(sectionCode);

                        string typeCode = reader.GetString(2);
                        classe.ClassType = new ClassManagerDAL().GetClassType(typeCode);

                        int productId = reader.GetInt32(3);
                        classe.Product = new CourseManagerDAL().GetProduct(productId);

                        classe.ClassStatus = reader.GetInt32(4);

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

            return list;
        }
        #endregion

        #region 根据班级类型获取班级列表
        /// <summary>
        /// 根据班级类型获取班级列表
        /// </summary>
        /// <param name="typeCode">班级类型</param>
        /// <returns>班级列表</returns>
        public IList<Class> GetClasses(string typeCode)
        {
            string sql = " SELECT ClassCode, SectionCode, TypeCode, ProductId, ClassStatus FROM Classes "
                       + " WHERE TypeCode = @TypeCode";

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

            IList<Class> list = new List<Class>();
            string _sectionCode = string.Empty;
            string _typeCode = string.Empty;
            int _productId = 0;

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

                        classe.ClassCode = reader.GetString(0);

                        _sectionCode = reader.GetString(1);
                        classe.Section = new CourseManagerDAL().GetSection(_sectionCode);

                        _typeCode = reader.GetString(2);
                        classe.ClassType = new ClassManagerDAL().GetClassType(_typeCode);

                        _productId = reader.GetInt32(3);
                        classe.Product = new CourseManagerDAL().GetProduct(_productId);

                        classe.ClassStatus = reader.GetInt32(4);

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

            return list;
        }
        #endregion

        #region 根据班级名称获取班级列表
        /// <summary>
        /// 根据班级名称获取班级列表
        /// </summary>
        /// <param name="classCode">班级名称</param>
        /// <returns>班级列表</returns>
        public bool GetClassesByClassCode(string classCode)
        {
            string sql = " SELECT ClassCode FROM Classes 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>
        /// <returns>班级类型列表</returns>
        public IList<ClassType> GetClassTypes()
        {
            string sql = "SELECT TypeCode, Title FROM ClassTypes";

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

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

                        classType.TypeCode = reader.GetString(0);
                        classType.Title = reader.GetString(1);

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

            return list;
        }
        #endregion

        #region 获得班级类型
        /// <summary>
        /// 获得班级类型
        /// </summary>
        /// <param name="typeCode">班级类型</param>
        /// <returns>班级实体对象</returns>
        public ClassType GetClassType(string typeCode)
        {
            string sql = "SELECT TypeCode, Title FROM ClassTypes WHERE TypeCode = @TypeCode";

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

            ClassType classType = null;

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

                        classType.TypeCode = reader.GetString(0);
                        classType.Title = reader.GetString(1);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return classType;
        }
        #endregion

        #region 班级结业
        /// <summary>
        /// 班级结业
        /// </summary>
        /// <param name="classCode">班级名称</param>
        /// <returns>true = 成功, false = 失败.</returns>
        public bool UpdateClassFinish(string classCode)
        {
            string sql = "UPDATE Classes SET ClassStatus=3 WHERE ClassCode=@ClassCode";

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

            int rows = 0;

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

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

        #region 班级合并
        /// <summary>
        /// 班级合并
        /// </summary>
        /// <param name="classCode">班级名称</param>
        /// <returns>true = 成功, false = 失败.</returns>
        public bool UpdateClassConsolidation(string classCode)
        {
            string sql = "UPDATE classes SET ClassStatus=2 WHERE ClassCode=@ClassCode";

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

            int rows = 0;

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

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

        #region 班级升学
        /// <summary>
        /// 班级升学
        /// </summary>
        /// <param name="classCode">班级名称</param>
        /// <param name="sectionCode">阶段</param>
        /// <returns>true = 成功, false = 失败.</returns>
        public bool UpdateClassNextTerm(string classCode)
        {
            string sql = "UPDATE Classes SET ClassStatus=1 WHERE ClassCode=@ClassCode";

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

            int rows = 0;

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

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