classroommanagerdal.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 System.Configuration;

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

namespace Y2T03.CourseScheduler.CourseDAL
{
    /// <summary>
    /// ClassRoomManagerDAL 的数据访问层
    /// </summary>
    public class ClassRoomManagerDAL :IClassRoomManager
    {

        public ClassRoomManagerDAL()
        { }

        #region 新增教学设施
        /// <summary>
        /// 新增教学设施
        /// </summary>
        /// <param name="classRoom">教学设施实体对象</param>
        /// <returns>true = 成功, false = 失败</returns>
        public bool Create(ClassRoom classRoom)
        {
            string sql = "INSERT INTO ClassRooms(TypeId, ProductId, Title) VALUES(@TypeId, @ProductId, @Title)";

            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@TypeId", classRoom.RoomType.TypeId),
                new SqlParameter("@ProductId", classRoom.Product.ProductId),
                new SqlParameter("@Title", classRoom.Title)
            };

            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="roomId">教学设施编号</param>
        /// <returns>true = 成功, false = 失败</returns>
        public bool Delete(int roomId)
        {
            string sql = "DELETE FROM ClassRooms WHERE RoomId = @RoomId";

            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@RoomId", roomId)
            };

            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>
        /// <returns>教学设施类型列表</returns>
        public IList<RoomType> GetRoomTypes()
        {
            string sql = "SELECT TypeId, Title FROM RoomTypes";

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

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

            return list;
        }
        #endregion

        #region 获取教学设施类型
        /// <summary>
        /// 获取教学设施类型
        /// </summary>
        /// <param name="typeId">设施类型</param>
        /// <returns>教学设施实体对象</returns>
        public RoomType GetRoomType(int typeId)
        {
            string sql = "SELECT TypeId, Title FROM RoomTypes WHERE TypeId = @TypeId";
            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@TypeId", typeId)
            };

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

            return roomType;
        }
        #endregion

        #region 更新教学设施
        /// <summary>
        /// 更新教学设施
        /// </summary>
        /// <param name="classRoom">教学设施实体对象</param>
        /// <returns>true = 成功, false = 失败</returns>
        public bool Update(ClassRoom classRoom)
        {
            string sql = "UPDATE ClassRooms SET TypeId = @TypeId, ProductId = @ProductId, Title = @Title WHERE RoomId = @RoomId";
                        
            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@TypeId", classRoom.RoomType.TypeId),
                new SqlParameter("@ProductId", classRoom.Product.ProductId),
                new SqlParameter("@Title", classRoom.Title),
                new SqlParameter("@RoomId", classRoom.RoomId)
            };

            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="roomId">设施编号</param>
        /// <returns>教学设施实体对象</returns>
        public ClassRoom GetClassRoom(int roomId)
        {
            string sql = "SELECT RoomId, TypeId, ProductId, Title FROM ClassRooms WHERE RoomId = @RoomId";
            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@RoomId", roomId)
            };

            ClassRoom classRoom = null;
            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql, parms))
                {
                    if (reader.Read())
                    {
                        classRoom = new ClassRoom();
                        classRoom.RoomId = reader.GetInt32(0);

                        int typeId = reader.GetInt32(1);
                        classRoom.RoomType = new ClassRoomManagerDAL().GetRoomType(typeId);

                        int productId = reader.GetInt32(2);
                        classRoom.Product = new CourseManagerDAL().GetProduct(productId);

                        classRoom.Title = reader.GetString(3);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return classRoom;
        }
        #endregion

        #region 获取教学设施列表
        /// <summary>
        /// 获取教学设施列表
        /// </summary>
        /// <returns>教学设施列表</returns>
        public IList<ClassRoom> GetClassRooms()
        {
            string sql = "SELECT RoomId, TypeId, ProductId, Title FROM ClassRooms";

            IList<ClassRoom> list = new List<ClassRoom>();
            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql))
                {
                    while (reader.Read())
                    {
                        ClassRoom classRoom = new ClassRoom();
                        classRoom.RoomId = reader.GetInt32(0);

                        int typeId = reader.GetInt32(1);
                        classRoom.RoomType = new ClassRoomManagerDAL().GetRoomType(typeId);

                        int productId = reader.GetInt32(2);
                        classRoom.Product = new CourseManagerDAL().GetProduct(productId);

                        classRoom.Title = reader.GetString(3);

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

            return list;
        }
        #endregion

        #region 根据设施类型获取教学设施列表
        /// <summary>
        /// 根据设施类型获取教学设施列表
        /// </summary>
        /// <param name="typeId">设施类型</param>
        /// <returns>教学设施列表</returns>
        public IList<ClassRoom> GetClassRooms(int typeId)
        {
            string sql = "SELECT RoomId, TypeId, ProductId, Title FROM ClassRooms WHERE TypeId = @TypeId ORDER BY TypeId";

            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@TypeId", typeId)
            };

            IList<ClassRoom> list = new List<ClassRoom>();
            int _typeId = 0;
            int _productId = 0;

            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql, parms))
                {
                    while (reader.Read())
                    {
                        ClassRoom classRoom = new ClassRoom();
                        classRoom.RoomId = reader.GetInt32(0);

                        _typeId = reader.GetInt32(1);
                        classRoom.RoomType = new ClassRoomManagerDAL().GetRoomType(_typeId);

                        _productId = reader.GetInt32(2);
                        classRoom.Product = new CourseManagerDAL().GetProduct(_productId);

                        classRoom.Title = reader.GetString(3);

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

            return list;
        }
        #endregion

        #region 检查设施名称是否已经存在
        /// <summary>
        /// 检查设施名称是否已经存在
        /// </summary>
        /// <param name="title">设施名称</param>
        /// <returns>true = 有, false = 没有. </returns>
        public bool CheckIsExits(string title)
        {
            string sql = "SELECT RoomId, TypeId, ProductId, Title FROM ClassRooms WHERE Title = @Title";

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

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