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 }
浙公网安备 33010602011771号