数据访问类收集

namespace DAL
{
    /// <summary>
    /// 菜品预定管理
    /// </summary>
    public class DishBookService
    {
        /// <summary>
        /// 客户预定
        /// </summary>
        /// <param name="objDishBook"></param>
        /// <returns></returns>
        public int Book(DishBook objDishBook)
        {
            string sql = "insert into DishBook (HotelName,ConsumeTime,ConsumePersons,RoomType,CustomerName,";
            sql += "CustomerPhone,CustomerEmail,Comments)";
            sql += " values(@HotelName,@ConsumeTime,@ConsumePersons,@RoomType,@CustomerName,@CustomerPhone,@CustomerEmail,@Comments)";
            SqlParameter[] param = new SqlParameter[]
           {
               new SqlParameter("@HotelName",objDishBook.HotelName),
               new SqlParameter("@ConsumePersons",objDishBook.ConsumePersons)  ,
               new SqlParameter("@RoomType",objDishBook.RoomType) ,
               new SqlParameter("@CustomerName",objDishBook.CustomerName) ,
               new SqlParameter("@CustomerPhone",objDishBook.CustomerPhone) ,
               new SqlParameter("@CustomerEmail",objDishBook.CustomerEmail) ,
               new SqlParameter("@ConsumeTime",objDishBook.ConsumeTime) ,
               new SqlParameter("@Comments",objDishBook.Comments)  
           };
            return SQLHelper.Update(sql, param);
        }
        /// <summary>
        /// 获取未关闭的预定
        /// </summary>
        /// <returns></returns>
        public List<DishBook> GetAllDishBook()
        {
            string sql = "select  HotelName,BookId,ConsumeTime,ConsumePersons,RoomType,CustomerName,";
            sql += "CustomerPhone,CustomerEmail,Comments,BookTime,OrderStatus from DishBook where OrderStatus=0 or OrderStatus=1 order by BookTime DESC";
            List<DishBook> list = new List<DishBook>();
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            while (objReader.Read())
            {
                list.Add(new DishBook()
                    {
                        HotelName = objReader["HotelName"].ToString(),
                        BookId = Convert.ToInt32(objReader["BookId"]),
                         ConsumeTime =Convert .ToDateTime(objReader ["ConsumeTime"]),
                        ConsumePersons = Convert.ToInt32(objReader["ConsumePersons"]),
                        RoomType = objReader["RoomType"].ToString(),
                        CustomerName = objReader["CustomerName"].ToString(),
                        CustomerPhone = objReader["CustomerPhone"].ToString(),
                        CustomerEmail = objReader["CustomerEmail"].ToString(),
                        Comments = objReader["Comments"].ToString(),
                        BookTime = Convert.ToDateTime(objReader["BookTime"]),
                        OrderStatus = Convert.ToInt32(objReader["OrderStatus"])
                    });
            }
            objReader.Close();
            return list;
        }
        /// <summary>
        /// 根据预定编号查询预定详细
        /// </summary>
        /// <param name="bookId"></param>
        /// <returns></returns>
        public DishBook GetDishBookById(string bookId)
        {
            string sql = "select  HotelName,BookId,ConsumePersons,RoomType,CustomerName,";
            sql += "CustomerPhone,CustomerEmail,Comments,BookTime,OrderStatus from DishBook where BookId=@BookId";
            DishBook objBook = null;
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            if (objReader.Read())
            {
                objBook=new DishBook()
                {
                    HotelName = objReader["HotelName"].ToString(),
                    BookId = Convert.ToInt32(objReader["BookId"]),
                    ConsumePersons = Convert.ToInt32(objReader["ConsumePersons"]),
                    RoomType = objReader["HotelName"].ToString(),
                    CustomerName = objReader["CustomerName"].ToString(),
                    CustomerPhone = objReader["CustomerPhone"].ToString(),
                    CustomerEmail = objReader["CustomerEmail"].ToString(),
                    Comments = objReader["Comments"].ToString(),
                    BookTime = Convert.ToDateTime(objReader["BookTime"]),
                    OrderStatus = Convert.ToInt32(objReader["OrderStatus"])
                };
            }
            objReader.Close();
            return objBook;
        }
        /// <summary>
        /// 修改预定状态
        /// </summary>
        /// <param name="bookId"></param>
        /// <param name="orderStatus"></param>
        /// <returns></returns>
        public int ModiyBook(string bookId,string orderStatus)
        {
            string sql = "update DishBook set OrderStatus=@OrderStatus where BookId=@BookId";
            SqlParameter[] param = new SqlParameter[]
            { 
                 new SqlParameter("@OrderStatus", orderStatus), 
                 new SqlParameter("@BookId", bookId)
            };
            return SQLHelper.Update(sql, param);
        }
    }
}

namespace DAL
{
    /// <summary>
    /// 菜品数据访问类
    /// </summary>
    public class DishService
    {
        /// <summary>
        /// 获取所有菜品分类
        /// </summary>
        /// <returns></returns>
        public List<DishCategory> GetAllCategory()
        {
            string sql = "select CategoryId,CategoryName from DishCategory";
            List<DishCategory> list = new List<DishCategory>();
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            while (objReader.Read())
            {
                list.Add(new DishCategory()
                    {
                        CategoryId = Convert.ToInt32(objReader["CategoryId"]),
                        CategoryName = objReader["CategoryName"].ToString()
                    });
            }
            objReader.Close();
            return list;
        }
        /// <summary>
        /// 新增菜品（返回新增菜品ID号）
        /// </summary>
        /// <param name="objDish"></param>
        /// <returns></returns>
        public int AddDish(Dish objDish)
        {
            string sql = "insert into Dishes (DishName,UnitPrice,CategoryId)";
            sql += " values(@DishName,@UnitPrice,@CategoryId);select @@identity";
            SqlParameter[] param = new SqlParameter[]
           {
               new SqlParameter("@DishName",objDish.DishName),
               new SqlParameter("@UnitPrice",objDish.UnitPrice),
               new SqlParameter("@CategoryId",objDish.CategoryId)           
           };
            return Convert.ToInt32(SQLHelper.GetSingleResult(sql, param));
        }
        /// <summary>
        /// 修改菜品
        /// </summary>
        /// <param name="objDish"></param>
        /// <returns></returns>
        public int ModiyDish(Dish objDish)
        {
            string sql = "update Dishes set  DishName=@DishName,UnitPrice=@UnitPrice,CategoryId=@CategoryId";
            sql += " where DishId=@DishId";
            SqlParameter[] param = new SqlParameter[]
           {
              new SqlParameter("@DishName",objDish.DishName),
               new SqlParameter("@UnitPrice",objDish.UnitPrice),
               new SqlParameter("@CategoryId",objDish.CategoryId),
               new SqlParameter("@DishId",objDish.DishId)
           };
            return SQLHelper.Update(sql, param);
        }
        /// <summary>
        /// 删除菜品
        /// </summary>
        /// <param name="dishId"></param>
        /// <returns></returns>
        public int DeleteDish(string dishId)
        {
            string sql = "delete from Dishes where DishId=@DishId";
            SqlParameter[] param = new SqlParameter[] { new SqlParameter("@DishId", dishId) };
            return SQLHelper.Update(sql, param);
        }
        /// <summary>
        /// 根据编号查询菜品
        /// </summary>
        /// <param name="dishId"></param>
        /// <returns></returns>
        public Dish GetDishById(string dishId)
        {
            string sql = "select  DishName,UnitPrice,CategoryId,DishId from Dishes where DishId=@DishId";
            SqlParameter[] param = new SqlParameter[] { new SqlParameter("@DishId", dishId) };
            Dish objDish = null;
            SqlDataReader objReader = SQLHelper.GetReader(sql, param);
            if (objReader.Read())
            {
                objDish = new Dish()
                {
                    DishId = Convert.ToInt32(objReader["DishId"]),
                    CategoryId = Convert.ToInt32(objReader["CategoryId"]),
                    DishName = objReader["DishName"].ToString(),
                    UnitPrice = Convert.ToInt32(objReader["UnitPrice"])
                };
            }
            objReader.Close();
            return objDish;
        }
        /// <summary>
        /// 查询菜品
        /// </summary>
        /// <returns></returns>
        public List<Dish> GetDishes(string categoryId)
        {
            string sql = "select  DishName,UnitPrice,Dishes.CategoryId,DishId,CategoryName from Dishes inner join DishCategory on DishCategory.CategoryId=Dishes.CategoryId";
            List<Dish> list = new List<Dish>();
            SqlDataReader objReader = null;
            if (categoryId == null || categoryId == string.Empty)
            {
                objReader = SQLHelper.GetReader(sql);
            }
            else
            {
                sql += " where Dishes.CategoryId=@CategoryId";
                SqlParameter[] param = new SqlParameter[] { new SqlParameter("@CategoryId", categoryId) };
                objReader = SQLHelper.GetReader(sql, param);
            }
            while (objReader.Read())
            {
                list.Add(new Dish()
                {
                    DishId = Convert.ToInt32(objReader["DishId"]),
                    CategoryId = Convert.ToInt32(objReader["CategoryId"]),
                    DishName = objReader["DishName"].ToString(),
                    UnitPrice = Convert.ToInt32(objReader["UnitPrice"]),
                    CategoryName = objReader["CategoryName"].ToString()
                });
            }
            objReader.Close();
            return list;
        }
    }
}

namespace DAL
{
    public class NewsService
    {
        /// <summary>
        /// 发布新闻
        /// </summary>
        /// <param name="objNews"></param>
        /// <returns></returns>
        public int PublishNews(News objNews)
        {
            string sql = "insert into News (NewsTitle,NewsContents,CategoryId)";
            sql += " values(@NewsTitle,@NewsContents,@CategoryId)";
            SqlParameter[] param = new SqlParameter[]
           {
               new SqlParameter("@NewsTitle",objNews.NewsTitle),
               new SqlParameter("@NewsContents",objNews.NewsContents),
               new SqlParameter("@CategoryId",objNews.CategoryId)           
           };
            return SQLHelper.Update(sql, param);
        }
        /// <summary>
        /// 修改新闻
        /// </summary>
        /// <param name="objNews"></param>
        /// <returns></returns>
        public int ModiyNews(News objNews)
        {
            string sql = "update News ";
            sql += " set NewsTitle=@NewsTitle,NewsContents=@NewsContents,CategoryId=@CategoryId";
            sql += " where NewsId=@NewsId";
            SqlParameter[] param = new SqlParameter[]
           {
               new SqlParameter("@NewsTitle",objNews.NewsTitle),
               new SqlParameter("@NewsContents",objNews.NewsContents),
               new SqlParameter("@CategoryId",objNews.CategoryId),
               new SqlParameter("@NewsId",objNews.NewsId)
           };
            return SQLHelper.Update(sql, param);
        }
        /// <summary>
        /// 删除新闻
        /// </summary>
        /// <param name="newsId"></param>
        /// <returns></returns>
        public int DeleteNews(string newsId)
        {
            string sql = "delete from News where NewsId=@NewsId";
            SqlParameter[] param = new SqlParameter[] { new SqlParameter("@NewsId", newsId) };
            return SQLHelper.Update(sql, param);
        }
        /// <summary>
        /// 根据新闻编号获取新闻对象
        /// </summary>
        /// <param name="newsId"></param>
        /// <returns></returns>
        public News GetNewsById(string newsId)
        {
            string sql = "select  NewsId,NewsTitle,NewsContents,CategoryId,PublishTime from News where NewsId=@NewsId";
            SqlParameter[] param = new SqlParameter[] { new SqlParameter("@NewsId", newsId) };
            News objNews = null;
            SqlDataReader objReader = SQLHelper.GetReader(sql, param);
            if (objReader.Read())
            {
                objNews = new News()
                {
                    NewsId = Convert.ToInt32(objReader["NewsId"]),
                    CategoryId = Convert.ToInt32(objReader["CategoryId"]),
                    NewsContents = objReader["NewsContents"].ToString(),
                    NewsTitle = objReader["NewsTitle"].ToString(),
                    PublishTime = Convert.ToDateTime(objReader["PublishTime"])
                };
            }
            objReader.Close();
            return objNews;
        }
        /// <summary>
        /// 查询最新发布的新闻
        /// </summary>
        /// <returns></returns>
        public List<News> GetNews(int count)
        {
            string sql = "select top " + count + "  NewsId,NewsTitle,CategoryName,";
            sql += "PublishTime from News inner join NewsCategory on NewsCategory.CategoryId=News.CategoryId Order By PublishTime DESC";
            List<News> list = new List<News>();
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            while (objReader.Read())
            {
                list.Add(new News()
                {
                    NewsId = Convert.ToInt32(objReader["NewsId"]),
                    NewsTitle = objReader["NewsTitle"].ToString(),
                    PublishTime = Convert.ToDateTime(objReader["PublishTime"]),
                    CategoryName = objReader["CategoryName"].ToString()
                });
            }
            objReader.Close();
            return list;
        }
    }
}

namespace DAL
{
    /// <summary>
    /// 招聘管理数据访问类
    /// </summary>
    public class RecruitmentService
    {
        /// <summary>
        /// 发布招聘信息
        /// </summary>
        /// <param name="objRecruitment"></param>
        /// <returns></returns>
        public int PublishRecruiment(Recruitment objRecruitment)
        {
            string sql = "insert into Recruitment (PostName,PostType,PostPlace,PostDesc,";
            sql += "PostRequire,Experience,EduBackground,RequireCount,";
            sql += "Manager,PhoneNumber,Email)";
            sql += " values('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},'{8}','{9}','{10}')";
            sql = string.Format(sql, objRecruitment.PostName,
                 objRecruitment.PostType,
                 objRecruitment.PostPlace,
                  objRecruitment.PostDesc,
                  objRecruitment.PostRequire,
                  objRecruitment.Experience,
                  objRecruitment.EduBackground,
                   objRecruitment.RequireCount,
                   objRecruitment.Manager,
                   objRecruitment.PhoneNumber,
                   objRecruitment.Email);
            return SQLHelper.Update(sql);
        }
        /// <summary>
        /// 查询所有职位列表
        /// </summary>
        /// <returns></returns>
        public List<Recruitment> GetAllRecList()
        {
            string sql = "select PostId,PostName,PostPlace,RequireCount,PostType,PostDesc,PostRequire,Experience,EduBackground,Manager,PhoneNumber,Email  from Recruitment";
            List<Recruitment> list = new List<Recruitment>();
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            while (objReader.Read())
            {
                list.Add(new Recruitment()
                    {
                        PostId = Convert.ToInt32(objReader["PostId"]),
                        PostName = objReader["PostName"].ToString(),
                        PostPlace = objReader["PostPlace"].ToString(),
                        RequireCount = Convert.ToInt32(objReader["RequireCount"]),
                        PostType = objReader["PostType"].ToString(),
                        PostDesc = objReader["PostDesc"].ToString(),
                        PostRequire = objReader["PostRequire"].ToString(),
                        Experience = objReader["Experience"].ToString(),
                        EduBackground = objReader["EduBackground"].ToString(),
                        Manager = objReader["Manager"].ToString(),
                        PhoneNumber = objReader["PhoneNumber"].ToString(),
                        Email = objReader["Email"].ToString()
                    });
            }
            objReader.Close();
            return list;
        }
        /// <summary>
        /// 根据职位编号查询职位详细信息
        /// </summary>
        /// <param name="postId"></param>
        /// <returns></returns>
        public Recruitment GetPostById(string postId)
        {
            string sql = "select PostId,PostName,PostPlace,RequireCount,PostType,PostDesc,PostRequire,Experience,EduBackground,Manager,PhoneNumber,Email,PublishTime  from Recruitment";
            sql += " where PostId=@PostId";
            Recruitment objRec = null;
            SqlParameter[] param = new SqlParameter[]
            {
               new SqlParameter ("@PostId",postId)
            };
            SqlDataReader objReader = SQLHelper.GetReader(sql, param);
            if (objReader.Read())
            {
                objRec = new Recruitment()
                        {
                            PostId = Convert.ToInt32(objReader["PostId"]),
                            PostName = objReader["PostName"].ToString(),
                            PostPlace = objReader["PostPlace"].ToString(),
                            RequireCount = Convert.ToInt32(objReader["RequireCount"]),
                            PostType = objReader["PostType"].ToString(),
                            PostDesc = objReader["PostDesc"].ToString(),
                            PostRequire = objReader["PostRequire"].ToString(),
                            Experience = objReader["Experience"].ToString(),
                            EduBackground = objReader["EduBackground"].ToString(),
                            Manager = objReader["Manager"].ToString(),
                            PhoneNumber = objReader["PhoneNumber"].ToString(),
                            Email = objReader["Email"].ToString(),
                            PublishTime = Convert.ToDateTime(objReader["PublishTime"])
                        };
            }
            objReader.Close();
            return objRec;
        }
        /// <summary>
        /// 修改招聘信息
        /// </summary>
        /// <param name="objRecruitment"></param>
        /// <returns></returns>
        public int ModifyRecruiment(Recruitment objRecruitment)
        {
            string sql = "update Recruitment set PostName=@PostName,PostType=@PostType,PostPlace=@PostPlace,PostDesc=@PostDesc,";
            sql += "PostRequire=@PostRequire,Experience=@Experience,EduBackground=@EduBackground,RequireCount=@RequireCount,PublishTime=getdate(),";
            sql += "Manager=@Manager,PhoneNumber=@PhoneNumber,Email=@Email  where PostId=@PostId";
            SqlParameter[] param = new SqlParameter[]
            {
                   new SqlParameter("@PostName",objRecruitment.PostName),
                   new SqlParameter("@PostType",objRecruitment.PostType),
                   new SqlParameter("@PostPlace",objRecruitment.PostPlace),             
                   new SqlParameter("@PostDesc",objRecruitment.PostDesc),
                   new SqlParameter("@PostRequire",objRecruitment.PostRequire),
                   new SqlParameter("@Experience",objRecruitment.Experience),
                   new SqlParameter("@EduBackground",objRecruitment.EduBackground),
                   new SqlParameter("@RequireCount",objRecruitment.RequireCount),
                   new SqlParameter("@Manager",objRecruitment.Manager),
                   new SqlParameter("@PhoneNumber",objRecruitment.PhoneNumber),
                   new SqlParameter("@Email",objRecruitment.Email),
                   new SqlParameter("@PostId",objRecruitment.PostId)
            };
            return SQLHelper.Update(sql, param);
        }
        /// <summary>
        /// 删除招聘信息
        /// </summary>
        /// <param name="postId"></param>
        /// <returns></returns>
        public int DeleteRecruiment(string postId)
        {
            string sql = "delete from Recruitment where PostId=@PostId";
            SqlParameter[] param = new SqlParameter[] { new SqlParameter("@PostId", postId) };
            return SQLHelper.Update(sql, param);
        }
    }
}

namespace DAL
{
    /// <summary>
    /// 投诉建议
    /// </summary>
    public class SuggestionService
    {
        /// <summary>
        /// 提交投诉
        /// </summary>
        /// <param name="objSuggestion"></param>
        /// <returns></returns>
        public int SubmitSuggestion(Suggestion objSuggestion)
        {
            string sql = "insert into Suggestion (CustomerName,ConsumeDesc,SuggestionDesc,PhoneNumber,Email)";
            sql += " values(@CustomerName,@ConsumeDesc,@SuggestionDesc,@PhoneNumber,@Email)";
            SqlParameter[] param = new SqlParameter[]
           {
               new SqlParameter("@CustomerName",objSuggestion.CustomerName),
               new SqlParameter("@ConsumeDesc",objSuggestion.ConsumeDesc),
               new SqlParameter("@SuggestionDesc",objSuggestion.SuggestionDesc),              
               new SqlParameter("@PhoneNumber",objSuggestion.PhoneNumber),
               new SqlParameter("@Email",objSuggestion.Email)
           };
            return Convert.ToInt32(SQLHelper.GetSingleResult(sql, param));
        }
        /// <summary>
        /// 获取最新的建议
        /// </summary>
        /// <returns></returns>
        public List<Suggestion> GetSuggestion()
        {
            string sql = "select  SuggestionId,CustomerName,ConsumeDesc,SuggestionDesc,SuggestTime,PhoneNumber,Email,StatusId from Suggestion";
            sql += " where StatusId=0 Order by SuggestTime DESC";
            List<Suggestion> list = new List<Suggestion>();
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            while (objReader.Read())
            {
                list.Add(new Suggestion()
                    {
                        SuggestionId=Convert .ToInt32(objReader ["SuggestionId"]),
                        CustomerName = objReader["CustomerName"].ToString(),
                        ConsumeDesc = objReader["ConsumeDesc"].ToString(),
                        SuggestionDesc = objReader["SuggestionDesc"].ToString(),
                        SuggestTime = Convert.ToDateTime(objReader["SuggestTime"]),
                        PhoneNumber = objReader["PhoneNumber"].ToString(),
                        Email = objReader["Email"].ToString(),
                        StatusId = Convert.ToInt32(objReader["StatusId"])
                    });
            }
            objReader.Close();
            return list;
        }
        /// <summary>
        /// 受理投诉
        /// </summary>
        /// <param name="suggestionId"></param>
        /// <returns></returns>
        public int HandlSuggestion(string suggestionId)
        {
            string sql = "update Suggestion set statusId=1 where SuggestionId=@SuggestionId";
            SqlParameter[] param = new SqlParameter[] { new SqlParameter("@SuggestionId", suggestionId) };
            return SQLHelper.Update(sql, param);
        }
    }
}

namespace DAL
{
    /// <summary>
    /// 管理员数据访问类
    /// </summary>
    public class SysAdminService
    {
        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="loginId"></param>
        /// <param name="loginpwd"></param>
        /// <returns></returns>
        public SysAdmin AdminLogin(string loginId, string loginpwd)
        {
            string sql = "select LoginName from SysAdmins where loginId={0} and loginPwd='{1}'";
            sql = string.Format(sql, loginId, loginpwd);
            SysAdmin objAdmin = null;
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            if (objReader.Read())
            {
                objAdmin = new SysAdmin()
                {
                    LoginId = Convert.ToInt32(loginId),
                    LoginPwd = loginpwd,
                    LoginName = objReader["LoginName"].ToString()
                };
            }
            objReader.Close();
            return objAdmin;
        }
    }
}

namespace DAL
{
    public  class AdminService
    {
        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="objAdmin">用户对象</param>
        /// <returns></returns>
        public SysAdmin AdminLogin(SysAdmin objAdmin)
        {
            string sql = "select AdminName from Admins where LoginId=@LoginId and  LoginPwd=@LoginPwd";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@LoginId",objAdmin.LoginId),
                new SqlParameter("@LoginPwd",objAdmin.LoginPwd),      
            };
            try
            {
                SqlDataReader objReader = SQLHelper.GetReader(sql, param,false);
                if (objReader.Read())
                {
                    objAdmin.AdminName = objReader["AdminName"].ToString();
                }
                else
                {
                    objAdmin = null;
                }
                objReader.Close();
            }
            catch (Exception ex)
            {
                
                throw new Exception("用户登录数据访问出现异常"+ex.Message);
            }
            return objAdmin;
        }

        /// <summary>
        /// 修改登录密码
        /// </summary>
        /// <param name="objAdmin"></param>
        /// <returns></returns>
        public int ModifyPwd(SysAdmin objAdmin)
        {
            string sql = "update Admins set LoginPwd=@LoginPwd where LoginId=@LoginId";
            SqlParameter[] param = new SqlParameter[]
            {
            new SqlParameter("@LoginPwd",objAdmin.LoginPwd),
            new SqlParameter("@LoginId",objAdmin.LoginId)
            };
            return SQLHelper.Update(sql, param, false);

        }
    
    
    }
}

namespace DAL
{
     public class ScoreListService
     {
         #region 成绩查询

         /// <summary>
         /// 根据班级查询考试成绩列表
         /// </summary>
         /// <param name="className"></param>
         /// <returns></returns>
         public List<StudentExt> GetScoreList(string className)
         {
             string sql = "select Students.StudentId,StudentName,ClassName,CSharp,SQLServerDB  from  Students ";
             sql += " inner join  StudentClass on StudentClass.ClassId=Students.ClassId ";
             sql += " inner join ScoreList on ScoreList.StudentId=Students.StudentId ";
             if (className != null && className.Length != 0)
             {
                 sql += string.Format(" where ClassName='{0}'",className);
             }
             SqlDataReader objReader = SQLHelper.GetReader(sql);
             List<StudentExt> list = new List<StudentExt>();
             while (objReader.Read())
             {
                 list.Add(new StudentExt()
                 {
                     StudentId=Convert.ToInt32(objReader["StudentId"]),
                     StudentName=objReader["StudentName"].ToString(),
                     ClassName=objReader["ClassName"].ToString(),
                     CSharp=Convert.ToInt32(objReader["CSharp"]),
                     SQLServerDB=Convert.ToInt32(objReader["SQLServerDB"])
                 });
             }
             objReader.Close();
             return list;
         }

             public Dictionary<string,string> GetScoreInfo()
             {
             string sql="select stuCount=count(*),avgCSharp=avg(CSharp),avgDB=avg(SQLServerDB) from ScoreList;";
                 sql+="select absentCount=count(*) from Students where StudentId not in(select StudentId from ScoreList)";
                 Dictionary<string ,string> scoreInfo=null;
                 SqlDataReader objReader=SQLHelper.GetReader(sql);
                 if(objReader.Read())
                 {
                 scoreInfo=new Dictionary<string,string>();
                 scoreInfo.Add("stuCount",objReader["stuCount"].ToString());
                 scoreInfo.Add("avgCSharp",objReader["avgCSharp"].ToString());
                 scoreInfo.Add("avgDB",objReader["avgDB"].ToString());
                 }
                 if(objReader.NextResult())
                 {
                     if (objReader.Read())
                     {
                          //   scoreInfo.Add("absentCount", objReader["absentCount"].ToString());
                     scoreInfo.Add("absentCount",objReader["absentCount"].ToString());
                     }
                 }
                 objReader.Close();
                 return scoreInfo;
             }
         /// <summary>
         /// 
         /// 获取所有未参加考试的学员名单位
         /// </summary>
         /// <returns></returns>
             public List<string> GetAbsentList()
             {
                 string sql = "select StudentName from Students where StudentId not in(select StudentId from ScoreList)";
                 SqlDataReader objReader = SQLHelper.GetReader(sql);
                 List<string> list = new List<string>();
                 while (objReader.Read())
                 {
                     list.Add(objReader["StudentName"].ToString());
                 }
                 objReader.Close();
                 return list;
             }
      
         #endregion
            #region  基于数据集DataSet的数据查询
         /// <summary>
         /// 获取所有的考试信息（存储在DataSet中）
         /// </summary>
         /// <returns></returns>
             public DataSet GetAllScroeList()
             {
                 string sql = "select Students.StudentId ,StudentName,ClassName,CSharp, SQLServerDB";
                 sql += " from Students";
                 sql += " inner join StudentClass on StudentClass.ClassId=Students.ClassId";
                 sql += "  inner join ScoreList on ScoreList.StudentId=Students.StudentId ";
                 return SQLHelper.GetDataSet(sql);
             }
            #endregion


     }
}

namespace DAL
{
    /// <summary>
    /// 班组数据访问类
    /// </summary>
    public  class StudentClassService
    {
        /// <summary>
        /// 获取所有的班级对象
        /// </summary>
        /// <returns></returns>
        public List<StudentClass> GetAllClasses()
        {
            string sql = "select ClassName,ClassId from StudentClass";
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            List<StudentClass > list =new List<StudentClass>();
            while (objReader.Read())
            {
                list.Add(new StudentClass()
                {
                    ClassId = Convert.ToInt32(objReader["ClassId"]),
                    ClassName = objReader["ClassName"].ToString()
                });
             }
            objReader.Close();
            return list;
        }

        /// <summary>
        /// 获取所有的班级（存放在数据集里面），用DataSet来实现
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllClass2()
        {
            string sql = "select ClassId,CLassName from StudentClass";
            return SQLHelper.GetDataSet(sql);
        }
    }
}

namespace DAL
{
    /// <summary>
    /// 学员信息数据访问类
    /// </summary>
    public  class StudentService
    {
        
        #region  添加学员对象
        /// <summary>
        /// 判断当前身份证号是否已经存在
        /// </summary>
        /// <param name="studentNo"></param>
        /// <returns></returns>
        public bool IsIdNoExisted(string studentNo)
        {
            string sql = "select count(*) from Students where StudentIdNo={0}";
            sql = string.Format(sql, studentNo);
            int result =Convert.ToInt32 (SQLHelper.GetSingleResult(sql));
            if (result == 1) return true;
            else return false;
        }
        /// <summary>
        /// 添加学员
        /// </summary>
        /// <param name="objStudent"></param>
        /// <returns></returns>

        public int addStudent(Student objStudent)
        { 
        //[1]编写SQL语句
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("insert into Students(StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)");
            sqlBuilder.Append("  values('{0}','{1}','{2}',{3},{4},'{5}','{6}',{7})");//非值类型都要加上单引号
         //[2]解析对象
            string sql = string.Format(sqlBuilder.ToString(),
                objStudent.StudentName, objStudent.Gender, objStudent.Birthday, objStudent.StudentIdNo, objStudent.Age, objStudent.PhoneNumber, objStudent.StudentAddress, objStudent.ClassId);
         //[3]提交到数据库
            try
            {
                return SQLHelper.Update(sql);
            }
            catch (SqlException ex)
            {
                throw new Exception("数据库操作出现异常！具体信息：" + ex.Message);
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        #endregion

        #region  查询学员
        /// <summary>
        /// 根据班级名称查询学员信息
        /// </summary>
        /// <param name="className"></param>
        /// <returns></returns>
        public List<StudentExt> GetStudentByClass(string className)
        {
            string sql = "select StudentName,StudentId,Gender,Birthday,ClassName  from  Students";
            sql += "  inner join StudentClass  on StudentClass.ClassId=Students.ClassId ";
            sql += "  where ClassName='{0}'";
            sql = string.Format(sql,className);
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            List<StudentExt> list=new List<StudentExt>();
            while (objReader.Read ())
            {
            list.Add(new StudentExt()
                {
                StudentId =Convert.ToInt32 (objReader ["StudentId"]),
                StudentName=objReader ["StudentName"].ToString (),
                Gender =objReader ["Gender"].ToString(),
                Birthday =Convert.ToDateTime (objReader ["Birthday"]),
                ClassName =objReader ["ClassName"].ToString()
                });
             }
            objReader .Close();
            return list;
        }
        /// <summary>
        /// 根据学号查询学员对象
        /// </summary>
        /// <param name="studentId"></param>
        /// <returns></returns>
        public StudentExt GetStudentById(string studentId)
        {
            string sql = "select StudentId,StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassName  from Students";
            sql += "  inner join StudentClass on Students.ClassId=StudentClass.ClassId";
            sql += "  where StudentId=" + studentId;
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            StudentExt objStudentExt = null;  //因为有可能查不到
            if (objReader.Read())
            {
                objStudentExt = new StudentExt()
                {
                    StudentId = Convert.ToInt32(objReader["StudentId"]),
                    StudentName = objReader["StudentName"].ToString(),
                    Gender = objReader["Gender"].ToString(),
                    Birthday = Convert.ToDateTime(objReader["Birthday"]),
                    ClassName = objReader["ClassName"].ToString(),
                    StudentIdNo=objReader["StudentIdNO"].ToString(),
                    PhoneNumber=objReader["PhoneNumber"].ToString(),
                    StudentAddress=objReader["StudentAddress"].ToString()
                };
            }
            objReader.Close();
            return objStudentExt;

        }

        #endregion

        #region 修改学员对象

        /// <summary>
        /// 修改学员时判断身份证号是否和其他学员重复
        /// </summary>
        /// <param name="studentIdNo"></param>
        /// <param name="studentId"></param>
        /// <returns></returns>
        public bool IsIdNoExisted(string studentIdNo,string studentId)
        {
            string sql = "select count(*) from Students where StudentIdNo={0}  and  StudentId<>{1}";
            sql = string.Format(sql, studentIdNo, studentId);
            int result = Convert.ToInt32(SQLHelper.GetSingleResult(sql));
            if (result == 1) return true;
            else return false;
        }

        /// <summary>
        /// 修改学员对象
        /// </summary>
        /// <param name="objStudent"></param>
        /// <returns></returns>
        public int ModifyStudent(Student objStudent)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("Update Students set  StudentName='{0}' ,Gender='{1}',Birthday='{2}',");
            sqlBuilder.Append(" StudentIdNo={3},Age={4},PhoneNumber='{5}',StudentAddress='{6}',ClassId={7}");
            sqlBuilder.Append(" where StudentId={8} ");
            //解析对象
            string sql = string.Format(sqlBuilder.ToString(), objStudent.StudentName, objStudent.Gender, objStudent.Birthday, objStudent.StudentIdNo, objStudent.Age, objStudent.PhoneNumber, objStudent.StudentAddress, objStudent.ClassId, objStudent.StudentId);
            try
            {
                return SQLHelper.Update(sql);
            }
            catch (SqlException ex)
            {
                throw new Exception("数据库操作出现异常信息：" + ex.Message);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region  删除学员对象

        public int DeleteStudentById(string studentId)
        {
            string sql = "delete from Students where StudentId=" + studentId;
            try
            {
                return SQLHelper.Update(sql);
            }
            catch (SqlException ex)
            {
                if (ex.Number == 547)
                    throw new Exception("该学号初其他数据表引用，不能直接删除该学员对象！");
                else
                    throw new Exception ("数据库操作出现异常！具体信息：" + ex.Message);
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        #endregion
    }
}

namespace DAL
{
    /// <summary>
    /// 学员信息数据访问类
    /// </summary>
    public class StudentService
    {

        #region  添加学员对象
        /// <summary>
        /// 判断当前身份证号是否已经存在
        /// </summary>
        /// <param name="studentNo"></param>
        /// <returns></returns>
        public bool IsIdNoExisted(string studentNo)
        {
            string sql = "select count(*) from Students where StudentIdNo={0}";
            sql = string.Format(sql, studentNo);
            int result = Convert.ToInt32(SQLHelper.GetSingleResult(sql));
            if (result == 1) return true;
            else return false;
        }
        /// <summary>
        /// 添加学员
        /// </summary>
        /// <param name="objStudent"></param>
        /// <returns></returns>

        public int addStudent(Student objStudent)
        {
            //[1]编写SQL语句
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("insert into Students(StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)");
            sqlBuilder.Append("  values('{0}','{1}','{2}',{3},{4},'{5}','{6}',{7})");//非值类型都要加上单引号
            //[2]解析对象
            string sql = string.Format(sqlBuilder.ToString(),
                objStudent.StudentName, objStudent.Gender, objStudent.Birthday, objStudent.StudentIdNo, objStudent.Age, objStudent.PhoneNumber, objStudent.StudentAddress, objStudent.ClassId);
            //[3]提交到数据库
            try
            {
                return SQLHelper.Update(sql);
            }
            catch (SqlException ex)
            {
                throw new Exception("数据库操作出现异常！具体信息：" + ex.Message);
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        #endregion

        #region  查询学员
        /// <summary>
        /// 根据班级名称查询学员信息
        /// </summary>
        /// <param name="className"></param>
        /// <returns></returns>
        public List<StudentExt> GetStudentByClass(string className)
        {
            string sql = "select StudentName,StudentId,Gender,Birthday,ClassName  from  Students";
            sql += "  inner join StudentClass  on StudentClass.ClassId=Students.ClassId ";
            sql += "  where ClassName='{0}'";
            sql = string.Format(sql, className);
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            List<StudentExt> list = new List<StudentExt>();
            while (objReader.Read())
            {
                list.Add(new StudentExt()
                {
                    StudentId = Convert.ToInt32(objReader["StudentId"]),
                    StudentName = objReader["StudentName"].ToString(),
                    Gender = objReader["Gender"].ToString(),
                    Birthday = Convert.ToDateTime(objReader["Birthday"]),
                    ClassName = objReader["ClassName"].ToString()
                });
            }
            objReader.Close();
            return list;
        }
        /// <summary>
        /// 根据学号查询学员对象
        /// </summary>
        /// <param name="studentId"></param>
        /// <returns></returns>
        public StudentExt GetStudentById(string studentId)
        {
            string sql = "select StudentId,StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassName  from Students";
            sql += "  inner join StudentClass on Students.ClassId=StudentClass.ClassId";
            sql += "  where StudentId=" + studentId;
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            StudentExt objStudentExt = null;  //因为有可能查不到
            if (objReader.Read())
            {
                objStudentExt = new StudentExt()
                {
                    StudentId = Convert.ToInt32(objReader["StudentId"]),
                    StudentName = objReader["StudentName"].ToString(),
                    Gender = objReader["Gender"].ToString(),
                    Birthday = Convert.ToDateTime(objReader["Birthday"]),
                    ClassName = objReader["ClassName"].ToString(),
                    StudentIdNo = objReader["StudentIdNO"].ToString(),
                    PhoneNumber = objReader["PhoneNumber"].ToString(),
                    StudentAddress = objReader["StudentAddress"].ToString()
                };
            }
            objReader.Close();
            return objStudentExt;

        }

        #endregion

        #region 修改学员对象

        /// <summary>
        /// 修改学员时判断身份证号是否和其他学员重复
        /// </summary>
        /// <param name="studentIdNo"></param>
        /// <param name="studentId"></param>
        /// <returns></returns>
        public bool IsIdNoExisted(string studentIdNo, string studentId)
        {
            string sql = "select count(*) from Students where StudentIdNo={0}  and  StudentId<>{1}";
            sql = string.Format(sql, studentIdNo, studentId);
            int result = Convert.ToInt32(SQLHelper.GetSingleResult(sql));
            if (result == 1) return true;
            else return false;
        }

        /// <summary>
        /// 修改学员对象
        /// </summary>
        /// <param name="objStudent"></param>
        /// <returns></returns>
        public int ModifyStudent(Student objStudent)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("Update Students set  StudentName='{0}' ,Gender='{1}',Birthday='{2}',");
            sqlBuilder.Append(" StudentIdNo={3},Age={4},PhoneNumber='{5}',StudentAddress='{6}',ClassId={7}");
            sqlBuilder.Append(" where StudentId={8} ");
            //解析对象
            string sql = string.Format(sqlBuilder.ToString(), objStudent.StudentName, objStudent.Gender, objStudent.Birthday, objStudent.StudentIdNo, objStudent.Age, objStudent.PhoneNumber, objStudent.StudentAddress, objStudent.ClassId, objStudent.StudentId);
            try
            {
                return SQLHelper.Update(sql);
            }
            catch (SqlException ex)
            {
                throw new Exception("数据库操作出现异常信息：" + ex.Message);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region  删除学员对象

        public int DeleteStudentById(string studentId)
        {
            string sql = "delete from Students where StudentId=" + studentId;
            try
            {
                return SQLHelper.Update(sql);
            }
            catch (SqlException ex)
            {
                if (ex.Number == 547)
                    throw new Exception("该学号初其他数据表引用，不能直接删除该学员对象！");
                else
                    throw new Exception("数据库操作出现异常！具体信息：" + ex.Message);
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        #endregion
    }
}