C# 基础项目——课程管理系统DEMO（四）之DAL


C# 基础项目——课程管理系统DEMO（四）之DAL

一：DAL层

1. DAL层基本构成：

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 }
SQLHelper

 

 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 }
CourseService

 

 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 }
CourseCategoryService

 

 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 }
TeacherService

 

 

 

END

posted @ 2022-03-16 14:54  云谷の风  阅读(226)  评论(0)  编辑  收藏  举报