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

        #region 新增教员
        /// <summary>
        /// 新增教员
        /// </summary>
        /// <param name="teacher">教员实体对象</param>
        /// <returns>true = 成功, false = 失败.</returns>
        public bool Create(Teacher teacher)
        {
            string sql = "INSERT INTO Teachers(Name, SectionCode, SpecialityId) VALUES(@Name, @SectionCode, @SpecialityId)";
            
            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@Name", teacher.Name),
                new SqlParameter("@SectionCode", teacher.Section.SectionCode),
                new SqlParameter("@SpecialityId", teacher.Speciality.SpecialityId)
            };

            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="teacher">教员实体对象</param>
        /// <returns>true = 成功, false = 失败.</returns>
        public bool Update(Teacher teacher)
        {
            string sql = "UPDATE Teachers SET Name = @Name, SectionCode = @SectionCode, SpecialityId = @SpecialityId WHERE TeacherId = @TeacherId";
            
            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@Name", teacher.Name),
                new SqlParameter("@SectionCode", teacher.Section.SectionCode),
                new SqlParameter("@SpecialityId", teacher.Speciality.SpecialityId),
                new SqlParameter("@TeacherId", teacher.TeacherId)
            };

            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="teacherId">教员编号</param>
        /// <returns>true = 成功, false = 失败.</returns>
        public bool Delete(int teacherId)
        {
            string sql = "DELETE FROM Teachers WHERE TeacherId = @TeacherId";

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

            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="teacherId">教员编号</param>
        /// <returns>教员实体对象</returns>
        public Teacher GetTeacher(int teacherId)
        {
            string sql = "SELECT TeacherId, Name, SectionCode, SpecialityId FROM Teachers WHERE TeacherId = @TeacherId";
            
            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@TeacherId", teacherId)
            };

            Teacher teacher = null;

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql, parms))
                {
                    if (reader.Read())
                    {
                        teacher = new Teacher();
                        teacher.TeacherId = reader.GetInt32(0);
                        teacher.Name = reader.GetString(1);

                        string sectionCode = reader.GetString(2);
                        teacher.Section = new CourseManagerDAL().GetSection(sectionCode);

                        int specialityId = reader.GetInt32(3);
                        teacher.Speciality = new CourseManagerDAL().GetSpeciality(specialityId);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return teacher;
        }
        #endregion

        #region 获取教员信息列表
        /// <summary>
        /// 获取教员信息列表
        /// </summary>
        /// <returns>教员信息列表</returns>
        public IList<Teacher> GetTeachers()
        {
            string sql = "SELECT TeacherId, Name, SectionCode, SpecialityId FROM Teachers";

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

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql))
                {
                    while (reader.Read())
                    {
                        Teacher teacher = new Teacher();
                        teacher.TeacherId = reader.GetInt32(0);
                        teacher.Name = reader.GetString(1);

                        string sectionCode = reader.GetString(2);
                        teacher.Section = new CourseManagerDAL().GetSection(sectionCode);

                        int specialityId = reader.GetInt32(3);
                        teacher.Speciality = new CourseManagerDAL().GetSpeciality(specialityId);

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

            return list;
        }
        #endregion
    }
}