数据访问类收集

  1 namespace DAL
  2 {
  3     /// <summary>
  4     /// 菜品预定管理
  5     /// </summary>
  6     public class DishBookService
  7     {
  8         /// <summary>
  9         /// 客户预定
 10         /// </summary>
 11         /// <param name="objDishBook"></param>
 12         /// <returns></returns>
 13         public int Book(DishBook objDishBook)
 14         {
 15             string sql = "insert into DishBook (HotelName,ConsumeTime,ConsumePersons,RoomType,CustomerName,";
 16             sql += "CustomerPhone,CustomerEmail,Comments)";
 17             sql += " values(@HotelName,@ConsumeTime,@ConsumePersons,@RoomType,@CustomerName,@CustomerPhone,@CustomerEmail,@Comments)";
 18             SqlParameter[] param = new SqlParameter[]
 19            {
 20                new SqlParameter("@HotelName",objDishBook.HotelName),
 21                new SqlParameter("@ConsumePersons",objDishBook.ConsumePersons)  ,
 22                new SqlParameter("@RoomType",objDishBook.RoomType) ,
 23                new SqlParameter("@CustomerName",objDishBook.CustomerName) ,
 24                new SqlParameter("@CustomerPhone",objDishBook.CustomerPhone) ,
 25                new SqlParameter("@CustomerEmail",objDishBook.CustomerEmail) ,
 26                new SqlParameter("@ConsumeTime",objDishBook.ConsumeTime) ,
 27                new SqlParameter("@Comments",objDishBook.Comments)  
 28            };
 29             return SQLHelper.Update(sql, param);
 30         }
 31         /// <summary>
 32         /// 获取未关闭的预定
 33         /// </summary>
 34         /// <returns></returns>
 35         public List<DishBook> GetAllDishBook()
 36         {
 37             string sql = "select  HotelName,BookId,ConsumeTime,ConsumePersons,RoomType,CustomerName,";
 38             sql += "CustomerPhone,CustomerEmail,Comments,BookTime,OrderStatus from DishBook where OrderStatus=0 or OrderStatus=1 order by BookTime DESC";
 39             List<DishBook> list = new List<DishBook>();
 40             SqlDataReader objReader = SQLHelper.GetReader(sql);
 41             while (objReader.Read())
 42             {
 43                 list.Add(new DishBook()
 44                     {
 45                         HotelName = objReader["HotelName"].ToString(),
 46                         BookId = Convert.ToInt32(objReader["BookId"]),
 47                          ConsumeTime =Convert .ToDateTime(objReader ["ConsumeTime"]),
 48                         ConsumePersons = Convert.ToInt32(objReader["ConsumePersons"]),
 49                         RoomType = objReader["RoomType"].ToString(),
 50                         CustomerName = objReader["CustomerName"].ToString(),
 51                         CustomerPhone = objReader["CustomerPhone"].ToString(),
 52                         CustomerEmail = objReader["CustomerEmail"].ToString(),
 53                         Comments = objReader["Comments"].ToString(),
 54                         BookTime = Convert.ToDateTime(objReader["BookTime"]),
 55                         OrderStatus = Convert.ToInt32(objReader["OrderStatus"])
 56                     });
 57             }
 58             objReader.Close();
 59             return list;
 60         }
 61         /// <summary>
 62         /// 根据预定编号查询预定详细
 63         /// </summary>
 64         /// <param name="bookId"></param>
 65         /// <returns></returns>
 66         public DishBook GetDishBookById(string bookId)
 67         {
 68             string sql = "select  HotelName,BookId,ConsumePersons,RoomType,CustomerName,";
 69             sql += "CustomerPhone,CustomerEmail,Comments,BookTime,OrderStatus from DishBook where BookId=@BookId";
 70             DishBook objBook = null;
 71             SqlDataReader objReader = SQLHelper.GetReader(sql);
 72             if (objReader.Read())
 73             {
 74                 objBook=new DishBook()
 75                 {
 76                     HotelName = objReader["HotelName"].ToString(),
 77                     BookId = Convert.ToInt32(objReader["BookId"]),
 78                     ConsumePersons = Convert.ToInt32(objReader["ConsumePersons"]),
 79                     RoomType = objReader["HotelName"].ToString(),
 80                     CustomerName = objReader["CustomerName"].ToString(),
 81                     CustomerPhone = objReader["CustomerPhone"].ToString(),
 82                     CustomerEmail = objReader["CustomerEmail"].ToString(),
 83                     Comments = objReader["Comments"].ToString(),
 84                     BookTime = Convert.ToDateTime(objReader["BookTime"]),
 85                     OrderStatus = Convert.ToInt32(objReader["OrderStatus"])
 86                 };
 87             }
 88             objReader.Close();
 89             return objBook;
 90         }
 91         /// <summary>
 92         /// 修改预定状态
 93         /// </summary>
 94         /// <param name="bookId"></param>
 95         /// <param name="orderStatus"></param>
 96         /// <returns></returns>
 97         public int ModiyBook(string bookId,string orderStatus)
 98         {
 99             string sql = "update DishBook set OrderStatus=@OrderStatus where BookId=@BookId";
100             SqlParameter[] param = new SqlParameter[]
101             { 
102                  new SqlParameter("@OrderStatus", orderStatus), 
103                  new SqlParameter("@BookId", bookId)
104             };
105             return SQLHelper.Update(sql, param);
106         }
107     }
108 }

  1 namespace DAL
  2 {
  3     /// <summary>
  4     /// 菜品数据访问类
  5     /// </summary>
  6     public class DishService
  7     {
  8         /// <summary>
  9         /// 获取所有菜品分类
 10         /// </summary>
 11         /// <returns></returns>
 12         public List<DishCategory> GetAllCategory()
 13         {
 14             string sql = "select CategoryId,CategoryName from DishCategory";
 15             List<DishCategory> list = new List<DishCategory>();
 16             SqlDataReader objReader = SQLHelper.GetReader(sql);
 17             while (objReader.Read())
 18             {
 19                 list.Add(new DishCategory()
 20                     {
 21                         CategoryId = Convert.ToInt32(objReader["CategoryId"]),
 22                         CategoryName = objReader["CategoryName"].ToString()
 23                     });
 24             }
 25             objReader.Close();
 26             return list;
 27         }
 28         /// <summary>
 29         /// 新增菜品（返回新增菜品ID号）
 30         /// </summary>
 31         /// <param name="objDish"></param>
 32         /// <returns></returns>
 33         public int AddDish(Dish objDish)
 34         {
 35             string sql = "insert into Dishes (DishName,UnitPrice,CategoryId)";
 36             sql += " values(@DishName,@UnitPrice,@CategoryId);select @@identity";
 37             SqlParameter[] param = new SqlParameter[]
 38            {
 39                new SqlParameter("@DishName",objDish.DishName),
 40                new SqlParameter("@UnitPrice",objDish.UnitPrice),
 41                new SqlParameter("@CategoryId",objDish.CategoryId)           
 42            };
 43             return Convert.ToInt32(SQLHelper.GetSingleResult(sql, param));
 44         }
 45         /// <summary>
 46         /// 修改菜品
 47         /// </summary>
 48         /// <param name="objDish"></param>
 49         /// <returns></returns>
 50         public int ModiyDish(Dish objDish)
 51         {
 52             string sql = "update Dishes set  DishName=@DishName,UnitPrice=@UnitPrice,CategoryId=@CategoryId";
 53             sql += " where DishId=@DishId";
 54             SqlParameter[] param = new SqlParameter[]
 55            {
 56               new SqlParameter("@DishName",objDish.DishName),
 57                new SqlParameter("@UnitPrice",objDish.UnitPrice),
 58                new SqlParameter("@CategoryId",objDish.CategoryId),
 59                new SqlParameter("@DishId",objDish.DishId)
 60            };
 61             return SQLHelper.Update(sql, param);
 62         }
 63         /// <summary>
 64         /// 删除菜品
 65         /// </summary>
 66         /// <param name="dishId"></param>
 67         /// <returns></returns>
 68         public int DeleteDish(string dishId)
 69         {
 70             string sql = "delete from Dishes where DishId=@DishId";
 71             SqlParameter[] param = new SqlParameter[] { new SqlParameter("@DishId", dishId) };
 72             return SQLHelper.Update(sql, param);
 73         }
 74         /// <summary>
 75         /// 根据编号查询菜品
 76         /// </summary>
 77         /// <param name="dishId"></param>
 78         /// <returns></returns>
 79         public Dish GetDishById(string dishId)
 80         {
 81             string sql = "select  DishName,UnitPrice,CategoryId,DishId from Dishes where DishId=@DishId";
 82             SqlParameter[] param = new SqlParameter[] { new SqlParameter("@DishId", dishId) };
 83             Dish objDish = null;
 84             SqlDataReader objReader = SQLHelper.GetReader(sql, param);
 85             if (objReader.Read())
 86             {
 87                 objDish = new Dish()
 88                 {
 89                     DishId = Convert.ToInt32(objReader["DishId"]),
 90                     CategoryId = Convert.ToInt32(objReader["CategoryId"]),
 91                     DishName = objReader["DishName"].ToString(),
 92                     UnitPrice = Convert.ToInt32(objReader["UnitPrice"])
 93                 };
 94             }
 95             objReader.Close();
 96             return objDish;
 97         }
 98         /// <summary>
 99         /// 查询菜品
100         /// </summary>
101         /// <returns></returns>
102         public List<Dish> GetDishes(string categoryId)
103         {
104             string sql = "select  DishName,UnitPrice,Dishes.CategoryId,DishId,CategoryName from Dishes inner join DishCategory on DishCategory.CategoryId=Dishes.CategoryId";
105             List<Dish> list = new List<Dish>();
106             SqlDataReader objReader = null;
107             if (categoryId == null || categoryId == string.Empty)
108             {
109                 objReader = SQLHelper.GetReader(sql);
110             }
111             else
112             {
113                 sql += " where Dishes.CategoryId=@CategoryId";
114                 SqlParameter[] param = new SqlParameter[] { new SqlParameter("@CategoryId", categoryId) };
115                 objReader = SQLHelper.GetReader(sql, param);
116             }
117             while (objReader.Read())
118             {
119                 list.Add(new Dish()
120                 {
121                     DishId = Convert.ToInt32(objReader["DishId"]),
122                     CategoryId = Convert.ToInt32(objReader["CategoryId"]),
123                     DishName = objReader["DishName"].ToString(),
124                     UnitPrice = Convert.ToInt32(objReader["UnitPrice"]),
125                     CategoryName = objReader["CategoryName"].ToString()
126                 });
127             }
128             objReader.Close();
129             return list;
130         }
131     }
132 }

  1 namespace DAL
  2 {
  3     public class NewsService
  4     {
  5         /// <summary>
  6         /// 发布新闻
  7         /// </summary>
  8         /// <param name="objNews"></param>
  9         /// <returns></returns>
 10         public int PublishNews(News objNews)
 11         {
 12             string sql = "insert into News (NewsTitle,NewsContents,CategoryId)";
 13             sql += " values(@NewsTitle,@NewsContents,@CategoryId)";
 14             SqlParameter[] param = new SqlParameter[]
 15            {
 16                new SqlParameter("@NewsTitle",objNews.NewsTitle),
 17                new SqlParameter("@NewsContents",objNews.NewsContents),
 18                new SqlParameter("@CategoryId",objNews.CategoryId)           
 19            };
 20             return SQLHelper.Update(sql, param);
 21         }
 22         /// <summary>
 23         /// 修改新闻
 24         /// </summary>
 25         /// <param name="objNews"></param>
 26         /// <returns></returns>
 27         public int ModiyNews(News objNews)
 28         {
 29             string sql = "update News ";
 30             sql += " set NewsTitle=@NewsTitle,NewsContents=@NewsContents,CategoryId=@CategoryId";
 31             sql += " where NewsId=@NewsId";
 32             SqlParameter[] param = new SqlParameter[]
 33            {
 34                new SqlParameter("@NewsTitle",objNews.NewsTitle),
 35                new SqlParameter("@NewsContents",objNews.NewsContents),
 36                new SqlParameter("@CategoryId",objNews.CategoryId),
 37                new SqlParameter("@NewsId",objNews.NewsId)
 38            };
 39             return SQLHelper.Update(sql, param);
 40         }
 41         /// <summary>
 42         /// 删除新闻
 43         /// </summary>
 44         /// <param name="newsId"></param>
 45         /// <returns></returns>
 46         public int DeleteNews(string newsId)
 47         {
 48             string sql = "delete from News where NewsId=@NewsId";
 49             SqlParameter[] param = new SqlParameter[] { new SqlParameter("@NewsId", newsId) };
 50             return SQLHelper.Update(sql, param);
 51         }
 52         /// <summary>
 53         /// 根据新闻编号获取新闻对象
 54         /// </summary>
 55         /// <param name="newsId"></param>
 56         /// <returns></returns>
 57         public News GetNewsById(string newsId)
 58         {
 59             string sql = "select  NewsId,NewsTitle,NewsContents,CategoryId,PublishTime from News where NewsId=@NewsId";
 60             SqlParameter[] param = new SqlParameter[] { new SqlParameter("@NewsId", newsId) };
 61             News objNews = null;
 62             SqlDataReader objReader = SQLHelper.GetReader(sql, param);
 63             if (objReader.Read())
 64             {
 65                 objNews = new News()
 66                 {
 67                     NewsId = Convert.ToInt32(objReader["NewsId"]),
 68                     CategoryId = Convert.ToInt32(objReader["CategoryId"]),
 69                     NewsContents = objReader["NewsContents"].ToString(),
 70                     NewsTitle = objReader["NewsTitle"].ToString(),
 71                     PublishTime = Convert.ToDateTime(objReader["PublishTime"])
 72                 };
 73             }
 74             objReader.Close();
 75             return objNews;
 76         }
 77         /// <summary>
 78         /// 查询最新发布的新闻
 79         /// </summary>
 80         /// <returns></returns>
 81         public List<News> GetNews(int count)
 82         {
 83             string sql = "select top " + count + "  NewsId,NewsTitle,CategoryName,";
 84             sql += "PublishTime from News inner join NewsCategory on NewsCategory.CategoryId=News.CategoryId Order By PublishTime DESC";
 85             List<News> list = new List<News>();
 86             SqlDataReader objReader = SQLHelper.GetReader(sql);
 87             while (objReader.Read())
 88             {
 89                 list.Add(new News()
 90                 {
 91                     NewsId = Convert.ToInt32(objReader["NewsId"]),
 92                     NewsTitle = objReader["NewsTitle"].ToString(),
 93                     PublishTime = Convert.ToDateTime(objReader["PublishTime"]),
 94                     CategoryName = objReader["CategoryName"].ToString()
 95                 });
 96             }
 97             objReader.Close();
 98             return list;
 99         }
100     }
101 }

  1 namespace DAL
  2 {
  3     /// <summary>
  4     /// 招聘管理数据访问类
  5     /// </summary>
  6     public class RecruitmentService
  7     {
  8         /// <summary>
  9         /// 发布招聘信息
 10         /// </summary>
 11         /// <param name="objRecruitment"></param>
 12         /// <returns></returns>
 13         public int PublishRecruiment(Recruitment objRecruitment)
 14         {
 15             string sql = "insert into Recruitment (PostName,PostType,PostPlace,PostDesc,";
 16             sql += "PostRequire,Experience,EduBackground,RequireCount,";
 17             sql += "Manager,PhoneNumber,Email)";
 18             sql += " values('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},'{8}','{9}','{10}')";
 19             sql = string.Format(sql, objRecruitment.PostName,
 20                  objRecruitment.PostType,
 21                  objRecruitment.PostPlace,
 22                   objRecruitment.PostDesc,
 23                   objRecruitment.PostRequire,
 24                   objRecruitment.Experience,
 25                   objRecruitment.EduBackground,
 26                    objRecruitment.RequireCount,
 27                    objRecruitment.Manager,
 28                    objRecruitment.PhoneNumber,
 29                    objRecruitment.Email);
 30             return SQLHelper.Update(sql);
 31         }
 32         /// <summary>
 33         /// 查询所有职位列表
 34         /// </summary>
 35         /// <returns></returns>
 36         public List<Recruitment> GetAllRecList()
 37         {
 38             string sql = "select PostId,PostName,PostPlace,RequireCount,PostType,PostDesc,PostRequire,Experience,EduBackground,Manager,PhoneNumber,Email  from Recruitment";
 39             List<Recruitment> list = new List<Recruitment>();
 40             SqlDataReader objReader = SQLHelper.GetReader(sql);
 41             while (objReader.Read())
 42             {
 43                 list.Add(new Recruitment()
 44                     {
 45                         PostId = Convert.ToInt32(objReader["PostId"]),
 46                         PostName = objReader["PostName"].ToString(),
 47                         PostPlace = objReader["PostPlace"].ToString(),
 48                         RequireCount = Convert.ToInt32(objReader["RequireCount"]),
 49                         PostType = objReader["PostType"].ToString(),
 50                         PostDesc = objReader["PostDesc"].ToString(),
 51                         PostRequire = objReader["PostRequire"].ToString(),
 52                         Experience = objReader["Experience"].ToString(),
 53                         EduBackground = objReader["EduBackground"].ToString(),
 54                         Manager = objReader["Manager"].ToString(),
 55                         PhoneNumber = objReader["PhoneNumber"].ToString(),
 56                         Email = objReader["Email"].ToString()
 57                     });
 58             }
 59             objReader.Close();
 60             return list;
 61         }
 62         /// <summary>
 63         /// 根据职位编号查询职位详细信息
 64         /// </summary>
 65         /// <param name="postId"></param>
 66         /// <returns></returns>
 67         public Recruitment GetPostById(string postId)
 68         {
 69             string sql = "select PostId,PostName,PostPlace,RequireCount,PostType,PostDesc,PostRequire,Experience,EduBackground,Manager,PhoneNumber,Email,PublishTime  from Recruitment";
 70             sql += " where PostId=@PostId";
 71             Recruitment objRec = null;
 72             SqlParameter[] param = new SqlParameter[]
 73             {
 74                new SqlParameter ("@PostId",postId)
 75             };
 76             SqlDataReader objReader = SQLHelper.GetReader(sql, param);
 77             if (objReader.Read())
 78             {
 79                 objRec = new Recruitment()
 80                         {
 81                             PostId = Convert.ToInt32(objReader["PostId"]),
 82                             PostName = objReader["PostName"].ToString(),
 83                             PostPlace = objReader["PostPlace"].ToString(),
 84                             RequireCount = Convert.ToInt32(objReader["RequireCount"]),
 85                             PostType = objReader["PostType"].ToString(),
 86                             PostDesc = objReader["PostDesc"].ToString(),
 87                             PostRequire = objReader["PostRequire"].ToString(),
 88                             Experience = objReader["Experience"].ToString(),
 89                             EduBackground = objReader["EduBackground"].ToString(),
 90                             Manager = objReader["Manager"].ToString(),
 91                             PhoneNumber = objReader["PhoneNumber"].ToString(),
 92                             Email = objReader["Email"].ToString(),
 93                             PublishTime = Convert.ToDateTime(objReader["PublishTime"])
 94                         };
 95             }
 96             objReader.Close();
 97             return objRec;
 98         }
 99         /// <summary>
100         /// 修改招聘信息
101         /// </summary>
102         /// <param name="objRecruitment"></param>
103         /// <returns></returns>
104         public int ModifyRecruiment(Recruitment objRecruitment)
105         {
106             string sql = "update Recruitment set PostName=@PostName,PostType=@PostType,PostPlace=@PostPlace,PostDesc=@PostDesc,";
107             sql += "PostRequire=@PostRequire,Experience=@Experience,EduBackground=@EduBackground,RequireCount=@RequireCount,PublishTime=getdate(),";
108             sql += "Manager=@Manager,PhoneNumber=@PhoneNumber,Email=@Email  where PostId=@PostId";
109             SqlParameter[] param = new SqlParameter[]
110             {
111                    new SqlParameter("@PostName",objRecruitment.PostName),
112                    new SqlParameter("@PostType",objRecruitment.PostType),
113                    new SqlParameter("@PostPlace",objRecruitment.PostPlace),             
114                    new SqlParameter("@PostDesc",objRecruitment.PostDesc),
115                    new SqlParameter("@PostRequire",objRecruitment.PostRequire),
116                    new SqlParameter("@Experience",objRecruitment.Experience),
117                    new SqlParameter("@EduBackground",objRecruitment.EduBackground),
118                    new SqlParameter("@RequireCount",objRecruitment.RequireCount),
119                    new SqlParameter("@Manager",objRecruitment.Manager),
120                    new SqlParameter("@PhoneNumber",objRecruitment.PhoneNumber),
121                    new SqlParameter("@Email",objRecruitment.Email),
122                    new SqlParameter("@PostId",objRecruitment.PostId)
123             };
124             return SQLHelper.Update(sql, param);
125         }
126         /// <summary>
127         /// 删除招聘信息
128         /// </summary>
129         /// <param name="postId"></param>
130         /// <returns></returns>
131         public int DeleteRecruiment(string postId)
132         {
133             string sql = "delete from Recruitment where PostId=@PostId";
134             SqlParameter[] param = new SqlParameter[] { new SqlParameter("@PostId", postId) };
135             return SQLHelper.Update(sql, param);
136         }
137     }
138 }

 1 namespace DAL
 2 {
 3     /// <summary>
 4     /// 投诉建议
 5     /// </summary>
 6     public class SuggestionService
 7     {
 8         /// <summary>
 9         /// 提交投诉
10         /// </summary>
11         /// <param name="objSuggestion"></param>
12         /// <returns></returns>
13         public int SubmitSuggestion(Suggestion objSuggestion)
14         {
15             string sql = "insert into Suggestion (CustomerName,ConsumeDesc,SuggestionDesc,PhoneNumber,Email)";
16             sql += " values(@CustomerName,@ConsumeDesc,@SuggestionDesc,@PhoneNumber,@Email)";
17             SqlParameter[] param = new SqlParameter[]
18            {
19                new SqlParameter("@CustomerName",objSuggestion.CustomerName),
20                new SqlParameter("@ConsumeDesc",objSuggestion.ConsumeDesc),
21                new SqlParameter("@SuggestionDesc",objSuggestion.SuggestionDesc),              
22                new SqlParameter("@PhoneNumber",objSuggestion.PhoneNumber),
23                new SqlParameter("@Email",objSuggestion.Email)
24            };
25             return Convert.ToInt32(SQLHelper.GetSingleResult(sql, param));
26         }
27         /// <summary>
28         /// 获取最新的建议
29         /// </summary>
30         /// <returns></returns>
31         public List<Suggestion> GetSuggestion()
32         {
33             string sql = "select  SuggestionId,CustomerName,ConsumeDesc,SuggestionDesc,SuggestTime,PhoneNumber,Email,StatusId from Suggestion";
34             sql += " where StatusId=0 Order by SuggestTime DESC";
35             List<Suggestion> list = new List<Suggestion>();
36             SqlDataReader objReader = SQLHelper.GetReader(sql);
37             while (objReader.Read())
38             {
39                 list.Add(new Suggestion()
40                     {
41                         SuggestionId=Convert .ToInt32(objReader ["SuggestionId"]),
42                         CustomerName = objReader["CustomerName"].ToString(),
43                         ConsumeDesc = objReader["ConsumeDesc"].ToString(),
44                         SuggestionDesc = objReader["SuggestionDesc"].ToString(),
45                         SuggestTime = Convert.ToDateTime(objReader["SuggestTime"]),
46                         PhoneNumber = objReader["PhoneNumber"].ToString(),
47                         Email = objReader["Email"].ToString(),
48                         StatusId = Convert.ToInt32(objReader["StatusId"])
49                     });
50             }
51             objReader.Close();
52             return list;
53         }
54         /// <summary>
55         /// 受理投诉
56         /// </summary>
57         /// <param name="suggestionId"></param>
58         /// <returns></returns>
59         public int HandlSuggestion(string suggestionId)
60         {
61             string sql = "update Suggestion set statusId=1 where SuggestionId=@SuggestionId";
62             SqlParameter[] param = new SqlParameter[] { new SqlParameter("@SuggestionId", suggestionId) };
63             return SQLHelper.Update(sql, param);
64         }
65     }
66 }

 1 namespace DAL
 2 {
 3     /// <summary>
 4     /// 管理员数据访问类
 5     /// </summary>
 6     public class SysAdminService
 7     {
 8         /// <summary>
 9         /// 用户登录
10         /// </summary>
11         /// <param name="loginId"></param>
12         /// <param name="loginpwd"></param>
13         /// <returns></returns>
14         public SysAdmin AdminLogin(string loginId, string loginpwd)
15         {
16             string sql = "select LoginName from SysAdmins where loginId={0} and loginPwd='{1}'";
17             sql = string.Format(sql, loginId, loginpwd);
18             SysAdmin objAdmin = null;
19             SqlDataReader objReader = SQLHelper.GetReader(sql);
20             if (objReader.Read())
21             {
22                 objAdmin = new SysAdmin()
23                 {
24                     LoginId = Convert.ToInt32(loginId),
25                     LoginPwd = loginpwd,
26                     LoginName = objReader["LoginName"].ToString()
27                 };
28             }
29             objReader.Close();
30             return objAdmin;
31         }
32     }
33 }

 1 namespace DAL
 2 {
 3     public  class AdminService
 4     {
 5         /// <summary>
 6         /// 用户登录
 7         /// </summary>
 8         /// <param name="objAdmin">用户对象</param>
 9         /// <returns></returns>
10         public SysAdmin AdminLogin(SysAdmin objAdmin)
11         {
12             string sql = "select AdminName from Admins where LoginId=@LoginId and  LoginPwd=@LoginPwd";
13             SqlParameter[] param = new SqlParameter[]
14             {
15                 new SqlParameter("@LoginId",objAdmin.LoginId),
16                 new SqlParameter("@LoginPwd",objAdmin.LoginPwd),      
17             };
18             try
19             {
20                 SqlDataReader objReader = SQLHelper.GetReader(sql, param,false);
21                 if (objReader.Read())
22                 {
23                     objAdmin.AdminName = objReader["AdminName"].ToString();
24                 }
25                 else
26                 {
27                     objAdmin = null;
28                 }
29                 objReader.Close();
30             }
31             catch (Exception ex)
32             {
33                 
34                 throw new Exception("用户登录数据访问出现异常"+ex.Message);
35             }
36             return objAdmin;
37         }
38 
39         /// <summary>
40         /// 修改登录密码
41         /// </summary>
42         /// <param name="objAdmin"></param>
43         /// <returns></returns>
44         public int ModifyPwd(SysAdmin objAdmin)
45         {
46             string sql = "update Admins set LoginPwd=@LoginPwd where LoginId=@LoginId";
47             SqlParameter[] param = new SqlParameter[]
48             {
49             new SqlParameter("@LoginPwd",objAdmin.LoginPwd),
50             new SqlParameter("@LoginId",objAdmin.LoginId)
51             };
52             return SQLHelper.Update(sql, param, false);
53 
54         }
55     
56     
57     }
58 }

 1 namespace DAL
 2 {
 3      public class ScoreListService
 4      {
 5          #region 成绩查询
 6 
 7          /// <summary>
 8          /// 根据班级查询考试成绩列表
 9          /// </summary>
10          /// <param name="className"></param>
11          /// <returns></returns>
12          public List<StudentExt> GetScoreList(string className)
13          {
14              string sql = "select Students.StudentId,StudentName,ClassName,CSharp,SQLServerDB  from  Students ";
15              sql += " inner join  StudentClass on StudentClass.ClassId=Students.ClassId ";
16              sql += " inner join ScoreList on ScoreList.StudentId=Students.StudentId ";
17              if (className != null && className.Length != 0)
18              {
19                  sql += string.Format(" where ClassName='{0}'",className);
20              }
21              SqlDataReader objReader = SQLHelper.GetReader(sql);
22              List<StudentExt> list = new List<StudentExt>();
23              while (objReader.Read())
24              {
25                  list.Add(new StudentExt()
26                  {
27                      StudentId=Convert.ToInt32(objReader["StudentId"]),
28                      StudentName=objReader["StudentName"].ToString(),
29                      ClassName=objReader["ClassName"].ToString(),
30                      CSharp=Convert.ToInt32(objReader["CSharp"]),
31                      SQLServerDB=Convert.ToInt32(objReader["SQLServerDB"])
32                  });
33              }
34              objReader.Close();
35              return list;
36          }
37 
38              public Dictionary<string,string> GetScoreInfo()
39              {
40              string sql="select stuCount=count(*),avgCSharp=avg(CSharp),avgDB=avg(SQLServerDB) from ScoreList;";
41                  sql+="select absentCount=count(*) from Students where StudentId not in(select StudentId from ScoreList)";
42                  Dictionary<string ,string> scoreInfo=null;
43                  SqlDataReader objReader=SQLHelper.GetReader(sql);
44                  if(objReader.Read())
45                  {
46                  scoreInfo=new Dictionary<string,string>();
47                  scoreInfo.Add("stuCount",objReader["stuCount"].ToString());
48                  scoreInfo.Add("avgCSharp",objReader["avgCSharp"].ToString());
49                  scoreInfo.Add("avgDB",objReader["avgDB"].ToString());
50                  }
51                  if(objReader.NextResult())
52                  {
53                      if (objReader.Read())
54                      {
55                           //   scoreInfo.Add("absentCount", objReader["absentCount"].ToString());
56                      scoreInfo.Add("absentCount",objReader["absentCount"].ToString());
57                      }
58                  }
59                  objReader.Close();
60                  return scoreInfo;
61              }
62          /// <summary>
63          /// 
64          /// 获取所有未参加考试的学员名单位
65          /// </summary>
66          /// <returns></returns>
67              public List<string> GetAbsentList()
68              {
69                  string sql = "select StudentName from Students where StudentId not in(select StudentId from ScoreList)";
70                  SqlDataReader objReader = SQLHelper.GetReader(sql);
71                  List<string> list = new List<string>();
72                  while (objReader.Read())
73                  {
74                      list.Add(objReader["StudentName"].ToString());
75                  }
76                  objReader.Close();
77                  return list;
78              }
79       
80          #endregion
81             #region  基于数据集DataSet的数据查询
82          /// <summary>
83          /// 获取所有的考试信息（存储在DataSet中）
84          /// </summary>
85          /// <returns></returns>
86              public DataSet GetAllScroeList()
87              {
88                  string sql = "select Students.StudentId ,StudentName,ClassName,CSharp, SQLServerDB";
89                  sql += " from Students";
90                  sql += " inner join StudentClass on StudentClass.ClassId=Students.ClassId";
91                  sql += "  inner join ScoreList on ScoreList.StudentId=Students.StudentId ";
92                  return SQLHelper.GetDataSet(sql);
93              }
94             #endregion
95 
96 
97      }
98 }

 1 namespace DAL
 2 {
 3     /// <summary>
 4     /// 班组数据访问类
 5     /// </summary>
 6     public  class StudentClassService
 7     {
 8         /// <summary>
 9         /// 获取所有的班级对象
10         /// </summary>
11         /// <returns></returns>
12         public List<StudentClass> GetAllClasses()
13         {
14             string sql = "select ClassName,ClassId from StudentClass";
15             SqlDataReader objReader = SQLHelper.GetReader(sql);
16             List<StudentClass > list =new List<StudentClass>();
17             while (objReader.Read())
18             {
19                 list.Add(new StudentClass()
20                 {
21                     ClassId = Convert.ToInt32(objReader["ClassId"]),
22                     ClassName = objReader["ClassName"].ToString()
23                 });
24              }
25             objReader.Close();
26             return list;
27         }
28 
29         /// <summary>
30         /// 获取所有的班级（存放在数据集里面），用DataSet来实现
31         /// </summary>
32         /// <returns></returns>
33         public DataSet GetAllClass2()
34         {
35             string sql = "select ClassId,CLassName from StudentClass";
36             return SQLHelper.GetDataSet(sql);
37         }
38     }
39 }

  1 namespace DAL
  2 {
  3     /// <summary>
  4     /// 学员信息数据访问类
  5     /// </summary>
  6     public  class StudentService
  7     {
  8         
  9         #region  添加学员对象
 10         /// <summary>
 11         /// 判断当前身份证号是否已经存在
 12         /// </summary>
 13         /// <param name="studentNo"></param>
 14         /// <returns></returns>
 15         public bool IsIdNoExisted(string studentNo)
 16         {
 17             string sql = "select count(*) from Students where StudentIdNo={0}";
 18             sql = string.Format(sql, studentNo);
 19             int result =Convert.ToInt32 (SQLHelper.GetSingleResult(sql));
 20             if (result == 1) return true;
 21             else return false;
 22         }
 23         /// <summary>
 24         /// 添加学员
 25         /// </summary>
 26         /// <param name="objStudent"></param>
 27         /// <returns></returns>
 28 
 29         public int addStudent(Student objStudent)
 30         { 
 31         //[1]编写SQL语句
 32             StringBuilder sqlBuilder = new StringBuilder();
 33             sqlBuilder.Append("insert into Students(StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)");
 34             sqlBuilder.Append("  values('{0}','{1}','{2}',{3},{4},'{5}','{6}',{7})");//非值类型都要加上单引号
 35          //[2]解析对象
 36             string sql = string.Format(sqlBuilder.ToString(),
 37                 objStudent.StudentName, objStudent.Gender, objStudent.Birthday, objStudent.StudentIdNo, objStudent.Age, objStudent.PhoneNumber, objStudent.StudentAddress, objStudent.ClassId);
 38          //[3]提交到数据库
 39             try
 40             {
 41                 return SQLHelper.Update(sql);
 42             }
 43             catch (SqlException ex)
 44             {
 45                 throw new Exception("数据库操作出现异常！具体信息：" + ex.Message);
 46             }
 47             catch (Exception ex)
 48             {
 49                 throw ex;
 50             }
 51 
 52         }
 53 
 54         #endregion
 55 
 56         #region  查询学员
 57         /// <summary>
 58         /// 根据班级名称查询学员信息
 59         /// </summary>
 60         /// <param name="className"></param>
 61         /// <returns></returns>
 62         public List<StudentExt> GetStudentByClass(string className)
 63         {
 64             string sql = "select StudentName,StudentId,Gender,Birthday,ClassName  from  Students";
 65             sql += "  inner join StudentClass  on StudentClass.ClassId=Students.ClassId ";
 66             sql += "  where ClassName='{0}'";
 67             sql = string.Format(sql,className);
 68             SqlDataReader objReader = SQLHelper.GetReader(sql);
 69             List<StudentExt> list=new List<StudentExt>();
 70             while (objReader.Read ())
 71             {
 72             list.Add(new StudentExt()
 73                 {
 74                 StudentId =Convert.ToInt32 (objReader ["StudentId"]),
 75                 StudentName=objReader ["StudentName"].ToString (),
 76                 Gender =objReader ["Gender"].ToString(),
 77                 Birthday =Convert.ToDateTime (objReader ["Birthday"]),
 78                 ClassName =objReader ["ClassName"].ToString()
 79                 });
 80              }
 81             objReader .Close();
 82             return list;
 83         }
 84         /// <summary>
 85         /// 根据学号查询学员对象
 86         /// </summary>
 87         /// <param name="studentId"></param>
 88         /// <returns></returns>
 89         public StudentExt GetStudentById(string studentId)
 90         {
 91             string sql = "select StudentId,StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassName  from Students";
 92             sql += "  inner join StudentClass on Students.ClassId=StudentClass.ClassId";
 93             sql += "  where StudentId=" + studentId;
 94             SqlDataReader objReader = SQLHelper.GetReader(sql);
 95             StudentExt objStudentExt = null;  //因为有可能查不到
 96             if (objReader.Read())
 97             {
 98                 objStudentExt = new StudentExt()
 99                 {
100                     StudentId = Convert.ToInt32(objReader["StudentId"]),
101                     StudentName = objReader["StudentName"].ToString(),
102                     Gender = objReader["Gender"].ToString(),
103                     Birthday = Convert.ToDateTime(objReader["Birthday"]),
104                     ClassName = objReader["ClassName"].ToString(),
105                     StudentIdNo=objReader["StudentIdNO"].ToString(),
106                     PhoneNumber=objReader["PhoneNumber"].ToString(),
107                     StudentAddress=objReader["StudentAddress"].ToString()
108                 };
109             }
110             objReader.Close();
111             return objStudentExt;
112 
113         }
114 
115         #endregion
116 
117         #region 修改学员对象
118 
119         /// <summary>
120         /// 修改学员时判断身份证号是否和其他学员重复
121         /// </summary>
122         /// <param name="studentIdNo"></param>
123         /// <param name="studentId"></param>
124         /// <returns></returns>
125         public bool IsIdNoExisted(string studentIdNo,string studentId)
126         {
127             string sql = "select count(*) from Students where StudentIdNo={0}  and  StudentId<>{1}";
128             sql = string.Format(sql, studentIdNo, studentId);
129             int result = Convert.ToInt32(SQLHelper.GetSingleResult(sql));
130             if (result == 1) return true;
131             else return false;
132         }
133 
134         /// <summary>
135         /// 修改学员对象
136         /// </summary>
137         /// <param name="objStudent"></param>
138         /// <returns></returns>
139         public int ModifyStudent(Student objStudent)
140         {
141             StringBuilder sqlBuilder = new StringBuilder();
142             sqlBuilder.Append("Update Students set  StudentName='{0}' ,Gender='{1}',Birthday='{2}',");
143             sqlBuilder.Append(" StudentIdNo={3},Age={4},PhoneNumber='{5}',StudentAddress='{6}',ClassId={7}");
144             sqlBuilder.Append(" where StudentId={8} ");
145             //解析对象
146             string sql = string.Format(sqlBuilder.ToString(), objStudent.StudentName, objStudent.Gender, objStudent.Birthday, objStudent.StudentIdNo, objStudent.Age, objStudent.PhoneNumber, objStudent.StudentAddress, objStudent.ClassId, objStudent.StudentId);
147             try
148             {
149                 return SQLHelper.Update(sql);
150             }
151             catch (SqlException ex)
152             {
153                 throw new Exception("数据库操作出现异常信息：" + ex.Message);
154             }
155             catch (Exception ex)
156             {
157                 throw ex;
158             }
159         }
160 
161         #endregion
162 
163         #region  删除学员对象
164 
165         public int DeleteStudentById(string studentId)
166         {
167             string sql = "delete from Students where StudentId=" + studentId;
168             try
169             {
170                 return SQLHelper.Update(sql);
171             }
172             catch (SqlException ex)
173             {
174                 if (ex.Number == 547)
175                     throw new Exception("该学号初其他数据表引用，不能直接删除该学员对象！");
176                 else
177                     throw new Exception ("数据库操作出现异常！具体信息：" + ex.Message);
178             }
179             catch (Exception ex)
180             {
181                 throw ex;
182             }
183 
184         }
185 
186         #endregion
187     }
188 }

  1 namespace DAL
  2 {
  3     /// <summary>
  4     /// 学员信息数据访问类
  5     /// </summary>
  6     public class StudentService
  7     {
  8 
  9         #region  添加学员对象
 10         /// <summary>
 11         /// 判断当前身份证号是否已经存在
 12         /// </summary>
 13         /// <param name="studentNo"></param>
 14         /// <returns></returns>
 15         public bool IsIdNoExisted(string studentNo)
 16         {
 17             string sql = "select count(*) from Students where StudentIdNo={0}";
 18             sql = string.Format(sql, studentNo);
 19             int result = Convert.ToInt32(SQLHelper.GetSingleResult(sql));
 20             if (result == 1) return true;
 21             else return false;
 22         }
 23         /// <summary>
 24         /// 添加学员
 25         /// </summary>
 26         /// <param name="objStudent"></param>
 27         /// <returns></returns>
 28 
 29         public int addStudent(Student objStudent)
 30         {
 31             //[1]编写SQL语句
 32             StringBuilder sqlBuilder = new StringBuilder();
 33             sqlBuilder.Append("insert into Students(StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)");
 34             sqlBuilder.Append("  values('{0}','{1}','{2}',{3},{4},'{5}','{6}',{7})");//非值类型都要加上单引号
 35             //[2]解析对象
 36             string sql = string.Format(sqlBuilder.ToString(),
 37                 objStudent.StudentName, objStudent.Gender, objStudent.Birthday, objStudent.StudentIdNo, objStudent.Age, objStudent.PhoneNumber, objStudent.StudentAddress, objStudent.ClassId);
 38             //[3]提交到数据库
 39             try
 40             {
 41                 return SQLHelper.Update(sql);
 42             }
 43             catch (SqlException ex)
 44             {
 45                 throw new Exception("数据库操作出现异常！具体信息：" + ex.Message);
 46             }
 47             catch (Exception ex)
 48             {
 49                 throw ex;
 50             }
 51 
 52         }
 53 
 54         #endregion
 55 
 56         #region  查询学员
 57         /// <summary>
 58         /// 根据班级名称查询学员信息
 59         /// </summary>
 60         /// <param name="className"></param>
 61         /// <returns></returns>
 62         public List<StudentExt> GetStudentByClass(string className)
 63         {
 64             string sql = "select StudentName,StudentId,Gender,Birthday,ClassName  from  Students";
 65             sql += "  inner join StudentClass  on StudentClass.ClassId=Students.ClassId ";
 66             sql += "  where ClassName='{0}'";
 67             sql = string.Format(sql, className);
 68             SqlDataReader objReader = SQLHelper.GetReader(sql);
 69             List<StudentExt> list = new List<StudentExt>();
 70             while (objReader.Read())
 71             {
 72                 list.Add(new StudentExt()
 73                 {
 74                     StudentId = Convert.ToInt32(objReader["StudentId"]),
 75                     StudentName = objReader["StudentName"].ToString(),
 76                     Gender = objReader["Gender"].ToString(),
 77                     Birthday = Convert.ToDateTime(objReader["Birthday"]),
 78                     ClassName = objReader["ClassName"].ToString()
 79                 });
 80             }
 81             objReader.Close();
 82             return list;
 83         }
 84         /// <summary>
 85         /// 根据学号查询学员对象
 86         /// </summary>
 87         /// <param name="studentId"></param>
 88         /// <returns></returns>
 89         public StudentExt GetStudentById(string studentId)
 90         {
 91             string sql = "select StudentId,StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassName  from Students";
 92             sql += "  inner join StudentClass on Students.ClassId=StudentClass.ClassId";
 93             sql += "  where StudentId=" + studentId;
 94             SqlDataReader objReader = SQLHelper.GetReader(sql);
 95             StudentExt objStudentExt = null;  //因为有可能查不到
 96             if (objReader.Read())
 97             {
 98                 objStudentExt = new StudentExt()
 99                 {
100                     StudentId = Convert.ToInt32(objReader["StudentId"]),
101                     StudentName = objReader["StudentName"].ToString(),
102                     Gender = objReader["Gender"].ToString(),
103                     Birthday = Convert.ToDateTime(objReader["Birthday"]),
104                     ClassName = objReader["ClassName"].ToString(),
105                     StudentIdNo = objReader["StudentIdNO"].ToString(),
106                     PhoneNumber = objReader["PhoneNumber"].ToString(),
107                     StudentAddress = objReader["StudentAddress"].ToString()
108                 };
109             }
110             objReader.Close();
111             return objStudentExt;
112 
113         }
114 
115         #endregion
116 
117         #region 修改学员对象
118 
119         /// <summary>
120         /// 修改学员时判断身份证号是否和其他学员重复
121         /// </summary>
122         /// <param name="studentIdNo"></param>
123         /// <param name="studentId"></param>
124         /// <returns></returns>
125         public bool IsIdNoExisted(string studentIdNo, string studentId)
126         {
127             string sql = "select count(*) from Students where StudentIdNo={0}  and  StudentId<>{1}";
128             sql = string.Format(sql, studentIdNo, studentId);
129             int result = Convert.ToInt32(SQLHelper.GetSingleResult(sql));
130             if (result == 1) return true;
131             else return false;
132         }
133 
134         /// <summary>
135         /// 修改学员对象
136         /// </summary>
137         /// <param name="objStudent"></param>
138         /// <returns></returns>
139         public int ModifyStudent(Student objStudent)
140         {
141             StringBuilder sqlBuilder = new StringBuilder();
142             sqlBuilder.Append("Update Students set  StudentName='{0}' ,Gender='{1}',Birthday='{2}',");
143             sqlBuilder.Append(" StudentIdNo={3},Age={4},PhoneNumber='{5}',StudentAddress='{6}',ClassId={7}");
144             sqlBuilder.Append(" where StudentId={8} ");
145             //解析对象
146             string sql = string.Format(sqlBuilder.ToString(), objStudent.StudentName, objStudent.Gender, objStudent.Birthday, objStudent.StudentIdNo, objStudent.Age, objStudent.PhoneNumber, objStudent.StudentAddress, objStudent.ClassId, objStudent.StudentId);
147             try
148             {
149                 return SQLHelper.Update(sql);
150             }
151             catch (SqlException ex)
152             {
153                 throw new Exception("数据库操作出现异常信息：" + ex.Message);
154             }
155             catch (Exception ex)
156             {
157                 throw ex;
158             }
159         }
160 
161         #endregion
162 
163         #region  删除学员对象
164 
165         public int DeleteStudentById(string studentId)
166         {
167             string sql = "delete from Students where StudentId=" + studentId;
168             try
169             {
170                 return SQLHelper.Update(sql);
171             }
172             catch (SqlException ex)
173             {
174                 if (ex.Number == 547)
175                     throw new Exception("该学号初其他数据表引用，不能直接删除该学员对象！");
176                 else
177                     throw new Exception("数据库操作出现异常！具体信息：" + ex.Message);
178             }
179             catch (Exception ex)
180             {
181                 throw ex;
182             }
183 
184         }
185 
186         #endregion
187     }
188 }