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

大标 2022年3月16日15:08:45
评论
21


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

  • 我的微信
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
大标
  • 本文由 发表于 2022年3月16日15:08:45
  • 转载请务必保留本文链接:https://www.tanhuibiao.com/script/net-c/4722.html
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: