C# 基础项目——课程管理系统DEMO(四)之DAL
C# 基础项目——课程管理系统DEMO(四)之DAL
一:DAL层
1. DAL层基本构成:
通用数据访问类SQLHelper |
![]() 1 namespace CourseManageDAL 2 { 3 class SQLHelper 4 { 5 //获取连接凭证(在config文件里面) 6 private static string connString = ConfigurationManager.ConnectionStrings["connString"].ToString(); 7 8 //insert、update、delete 9 10 //public static int Update(string sql)、增加默认参数SqlParameter[] param = null 11 public static int Update(string sql, SqlParameter[] param = null) 12 { 13 //创建连接对象 14 SqlConnection conn = new SqlConnection(connString); 15 SqlCommand cmd = new SqlCommand(sql, conn); 16 17 //带参数的SQL语句服务 18 if (param != null) 19 { 20 cmd.Parameters.AddRange(param); 21 } 22 //运行 23 try 24 { 25 conn.Open(); 26 return cmd.ExecuteNonQuery(); 27 } 28 catch (Exception ex) 29 { 30 throw new Exception("执行public static int Update(string sql)发生异常" + ex.Message); 31 } 32 finally 33 { 34 conn.Close(); 35 } 36 } 37 38 public static object GetSingleResult(string sql) 39 { 40 SqlConnection conn = new SqlConnection(connString); 41 SqlCommand cmd = new SqlCommand(sql); 42 43 try 44 { 45 conn.Open(); 46 return cmd.ExecuteScalar(); 47 } 48 catch(Exception ex) 49 { 50 throw new Exception("执行public static object GetSingleResult(string sql)发生异常" + ex.Message); 51 } 52 finally 53 { 54 conn.Close(); 55 } 56 } 57 58 public static SqlDataReader GetReader(string sql) 59 { 60 SqlConnection con = new SqlConnection(connString); 61 SqlCommand cmd = new SqlCommand(sql, con); 62 63 try 64 { 65 con.Open(); 66 //如果调用一次就把连接对象关了那么reader读不全,让reader自己去关闭连接对象:CommandBehavior.CloseConnection 67 return cmd.ExecuteReader(CommandBehavior.CloseConnection); 68 } 69 catch(Exception ex) 70 { 71 throw new Exception("执行public static SqlDataReader GetReader(string sql)发生异常" + ex.Message); 72 } 73 //这里不能con.Close()。调用一次读取一条, 74 //如果调用一次就把连接对象关了那么reader读不全,让reader自己去关闭连接对象 75 //finally 76 //{ 77 // con.Close(); 78 //} 79 } 80 } 81 }
|
CourseService |
![]() 1 namespace CourseManageDAL 2 { 3 public class CourseService 4 { 5 //public int AddCourse(Course course) 6 //{ 7 // //定义sql语句,并解析实体数据 8 // string sql = " insert into Course(CourseName, CourseContent, ClassHour, Credit, CategoryId, TeacherId)"; 9 // sql += $"values ('{course.CourseName}', '{course.CourseContent}',{course.ClassHour},{course.Credit},{course.CategoryId},{course.TeacherId})"; 10 // //执行SQL语句 11 // return SQLHelper.Update(sql); 12 //} 13 //以上方法,存在一个问题:单引号使用起来非常麻烦,同时还有可能有注入式攻击的危险 14 //微软针对以上方式,有两个解决方案:第一,使用带参数的SQL语句,第二使用存储过程 15 16 #region 添加课程 17 public int AddCourse(Course course) 18 { 19 //定义sql语句,并解析实体数据 20 string sql = " insert into Course(CourseName, CourseContent, ClassHour, Credit, CategoryId, TeacherId)"; 21 sql += " values(@CourseName, @CourseContent, @ClassHour, @Credit, @CategoryId, @TeacherId)"; 22 //封装SQL语句中的参数 23 SqlParameter[] param = new SqlParameter[] 24 { 25 new SqlParameter("@CourseName",course.CourseName), 26 new SqlParameter("@CourseContent",course.CourseContent), 27 new SqlParameter("@ClassHour",course.ClassHour), 28 new SqlParameter("@Credit",course.Credit), 29 new SqlParameter("@CategoryId",course.CategoryId), 30 new SqlParameter("@TeacherId",course.TeacherId), 31 }; 32 //执行带参数的SQL语句 33 return SQLHelper.Update(sql, param); 34 } 35 #endregion 36 37 #region 查询课程 38 /// <summary> 39 /// 根据多个查询条件动态组合查询 40 /// </summary> 41 /// <param name="categoryId">课程分类编号</param> 42 /// <param name="courseName">课程名称</param> 43 /// <returns></returns> 44 public List<Course> QueryCourse(int categoryId, string courseName) 45 { 46 //【1】定义SQL语句 47 string sql = "select CourseId,CourseName,CourseContent,ClassHour,Credit,CategoryId,TeacherName,Course.TeacherId from Course"; 48 sql += " inner join Teacher on Teacher.TeacherId=Course.TeacherId where"; 49 50 //【2】组合条件 51 string whereSql = string.Empty; 52 if (categoryId != -1) 53 { 54 whereSql += " and CategoryId=" + categoryId; 55 } 56 if (courseName != "") //这个地方没有必要检查null,因为我们通过文本框架文本传递的数据永远不可能为null 57 { 58 whereSql += $" and CourseName like '{courseName}%'"; 59 } 60 //实际开发中,如果还有其他的条件,请在这里继续添加if判断即可... 61 62 //将动态的查询条件和前面的基本查询语句结合 63 sql += whereSql.Substring(4);//把第一个and去掉后,组合 64 65 //【3】执行查询 66 SqlDataReader reader = SQLHelper.GetReader(sql); 67 //【4】封装结果 68 List<Course> list = new List<Course>(); 69 while (reader.Read()) 70 { 71 list.Add(new Course 72 { 73 CourseId = (int)reader["CourseId"], 74 CourseName = reader["CourseName"].ToString(), 75 CourseContent = reader["CourseContent"].ToString(), 76 ClassHour = (int)reader["ClassHour"], 77 Credit = (int)reader["Credit"], 78 CategoryId = (int)reader["CategoryId"], 79 TeacherId = (int)reader["TeacherId"], 80 TeacherName = reader["TeacherName"].ToString() 81 }); 82 } 83 reader.Close(); 84 return list; 85 } 86 #endregion 87 #region 修改课程 88 public int ModifyCourse(Course course) 89 { 90 //定义SQL语句 91 string sql = $"update Course Set CourseName=@CourseName,CourseContent=@CourseContent,ClassHour=@ClassHour,Credit=@Credit,CategoryId=@CategoryId "; 92 sql += " where CourseId=@CourseId"; 93 //封装参数 94 SqlParameter[] param = new SqlParameter[] 95 { 96 new SqlParameter("@CourseName",course.CourseName), 97 new SqlParameter("@CourseContent",course.CourseContent), 98 new SqlParameter("@ClassHour",course.ClassHour), 99 new SqlParameter("@Credit",course.Credit), 100 new SqlParameter("@CategoryId",course.CategoryId), 101 new SqlParameter("@CourseId",course.CourseId) 102 }; 103 //提交保存 104 return SQLHelper.Update(sql, param); 105 } 106 #endregion 107 #region 删除课程 108 /// <summary> 109 /// 删除课程 110 /// </summary> 111 /// <param name="course"></param> 112 /// <returns></returns> 113 public int DeleteCourse(Course course) 114 { 115 string sql = "delete from Course where CourseId=" + course.CourseId; 116 return SQLHelper.Update(sql); 117 } 118 #endregion 119 } 120 }
|
CourseCategoryService |
![]() 1 namespace CourseManageDAL 2 { 3 public class CourseCategoryService 4 { 5 /// <summary> 6 /// 查询全部课程分类对象,并封装到集合中 7 /// </summary> 8 /// <returns></returns> 9 public List<CourseCategory> GetCourseCategories() 10 { 11 string sql = "select CategoryName,CategoryId from CourseCategory"; 12 SqlDataReader reader = SQLHelper.GetReader(sql); 13 14 List<CourseCategory> list = new List<CourseCategory>(); 15 while (reader.Read()) 16 { 17 list.Add(new CourseCategory 18 { 19 CategoryId = (int)reader["CategoryId"], 20 CategoryName = reader["CategoryName"].ToString() 21 }); 22 } 23 reader.Close(); 24 return list; 25 } 26 } 27 }
|
TeacherService |
![]() 1 namespace CourseManageDAL 2 { 3 public class TeacherService 4 { 5 public Teacher TeacherLogin( Teacher teacher) 6 { 7 string sql = $"select TeacherName, TeacherId from Teacher where loginAccount='{teacher.LoginAccount}' and LoginPwd='{teacher.LoginPwd}'"; 8 9 SqlDataReader reader = SQLHelper.GetReader(sql); 10 11 if (reader.Read()) 12 { 13 teacher.TeacherId = (int)reader["TeacherId"]; 14 teacher.TeacherName = reader["TeacherName"].ToString(); 15 } 16 else 17 { 18 teacher = null; 19 } 20 reader.Close(); 21 return teacher; 22 } 23 } 24 }
|
END