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

        #region 新增课程
        /// <summary>
        /// 新增课程
        /// </summary>
        /// <param name="course">课程实体对象</param>
        /// <returns>true = 成功, false = 失败.</returns>
        public bool Create(Course course)
        {
            string sql = "INSERT INTO Courses(ProductId, SpecialityId, Title, CourseCode, SectionCode) VALUES(@ProductId, @SpecialityId, @Title, @CourseCode, @SectionCode);";
            sql += "SELECT @@IDENTITY";

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@ProductId", course.Product.ProductId),
                new SqlParameter("@SpecialityId", course.Speciality.SpecialityId),
                new SqlParameter("@Title", course.Title),
                new SqlParameter("@CourseCode", course.CourseCode),
                new SqlParameter("@SectionCode", course.Section.SectionCode)
            };

            object id = null;

            try
            {
                id = SQLDBHelper.ExecuteScalar(sql, param);
                course.CourseId = int.Parse(id.ToString());
            }
            catch (Exception ex)
            {
                string err = ex.Message;
            }

            return (course.CourseId > 0) ? true : false;
        }
        #endregion

        #region 终止课程
        /// <summary>
        /// 终止课程
        /// </summary>
        /// <param name="courseId">课程编号</param>
        /// <returns>true = 成功, false = 失败.</returns>
        public bool Delete(int courseId)
        {
            string sql = "DELETE FROM Courses WHERE CourseId = @CourseId";

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@CourseId", 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="course">课程实体对象</param>
        /// <returns>true = 成功, false = 失败.</returns>
        public bool Update(Course course)
        {
            string sql = "UPDATE Courses SET ProductId = @ProductId, SpecialityId = @SpecialityId, Title = @Title, CourseCode = @CourseCode, SectionCode = @SectionCode WHERE CourseId = @CourseId";

            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@ProductId", course.Product.ProductId),
                new SqlParameter("@SpecialityId", course.Speciality.SpecialityId),
                new SqlParameter("@Title", course.Title),
                new SqlParameter("@CourseCode", course.CourseCode),
                new SqlParameter("@SectionCode", course.Section.SectionCode),
                new SqlParameter("@CourseId", 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>
        /// <returns>教学产品列表</returns>
        public IList<Product> GetProducts()
        {
            string sql = "SELECT ProductId, Title FROM Products";

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

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

            return list;
        }
        #endregion

        #region 获得教学产品
        /// <summary>
        /// 获得教学产品
        /// </summary>
        /// <param name="productId">产品编号</param>
        /// <returns>教学产品实体对象</returns>
        public Product GetProduct(int productId)
        {
            string sql = "SELECT ProductId, Title FROM Products WHERE ProductId = @ProductId";
            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@ProductId", productId)
            };

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

            return product;
        }
        #endregion

        #region 获得学习阶段列表
        /// <summary>
        /// 获得学习阶段列表
        /// </summary>
        /// <returns>学习阶段列表</returns>
        public IList<Section> GetSections()
        {
            string sql = "SELECT SectionCode, Title FROM Sections";

            IList<Section> list = new List<Section>();
            try
            {
                using (SqlDataReader reader = SQLDBHelper.ExecuteReader(sql))
                {
                    while (reader.Read())
                    {
                        Section section = new Section();
                        section.SectionCode = reader.GetString(0);
                        section.Title = reader.GetString(1);

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

            return list;
        }
        #endregion

        #region 获得学习阶段
        /// <summary>
        /// 获得学习阶段
        /// </summary>
        /// <param name="sectionCode">阶段编码</param>
        /// <returns>学习阶段实体对象</returns>
        public Section GetSection(string sectionCode)
        {
            string sql = "SELECT SectionCode, Title FROM Sections WHERE SectionCode = @SectionCode";
            SqlParameter[] parms = new SqlParameter[]
            {
                new SqlParameter("@SectionCode", sectionCode)
            };

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

            return section;
        }