第5講_三層架構(gòu)與學(xué)生信息管理_Win界面

1、第5講 三層架構(gòu)與學(xué)生信息信息管理_Win界面本節(jié)是對(duì)整個(gè)Win界面三層架構(gòu)數(shù)據(jù)庫(kù)開發(fā)的小結(jié)，介紹如何構(gòu)建一個(gè)簡(jiǎn)易的三層架構(gòu)（如下圖），并以開發(fā)一個(gè)簡(jiǎn)單的學(xué)生信息管理為例，說(shuō)明三層架構(gòu)應(yīng)用開發(fā)的過(guò)程。5.1 創(chuàng)建模型（Model）首先根據(jù)數(shù)據(jù)表的結(jié)構(gòu)創(chuàng)建與數(shù)據(jù)表對(duì)應(yīng)的數(shù)據(jù)類（建議類名與數(shù)據(jù)表名相同），將這些數(shù)據(jù)類放在一個(gè)命名為“Model”的獨(dú)立類庫(kù)中。除了與數(shù)據(jù)表對(duì)應(yīng)的數(shù)據(jù)類以外，還可以根據(jù)應(yīng)用開發(fā)的需要，還要建立一些其它的數(shù)據(jù)類（如：本示例中的StudentOrder類）。本示例的Modelo類庫(kù)項(xiàng)目如下。5.1.1 學(xué)生類Student.cs類的代碼如下： public class St

2、udent public string Sno get; set; public string Sname get; set; public string Ssex get; set; public int Sage get; set; public string Sdept get; set; public Student() public Student (string sno, string sname, string ssex, int sage, string sdept) Sno = sno; Sname = sname; Ssex = ssex; Sage = sage; Sde

3、pt = sdept; 5.1.2 課程類Course.cs類的代碼如下： public class Course public int Cno get; set; public string Cname get; set; public int Cpno get; set; public int Credit get; set; public Course() public Course(int cno, string cname, int cpno, int credit) Cno = cno; Cname = cname; Cpno = cpno; Credit = credit; 5.

4、1.3 成績(jī)類SC.cs類的代碼如下：（一個(gè)類文件中也可以定義多個(gè)類） public class SC public string Sno get; set; public int Cno get; set; public double Grade get; set; public SC() public SC(string sno, int cno, double grade) Sno = sno; Cno = cno; Grade = grade; public class SCAll public string Sno get; set; public string Sname get;

5、 set; public string Ssex get; set; public int Sage get; set; public string Sdept get; set; public int Cno get; set; public string Cname get; set; public int Cpno get; set; public int Credit get; set; public double Grade get; set; public SCAll(Student s, Course c, SC sc) Sno = s.Sno; Sname = s.Sname;

6、 Ssex = s.Ssex; Sage = s.Sage; Sdept = s.Sdept; Cno = c.Cno; Cname = c.Cname; Cpno = c.Cpno; Credit = c.Credit; Grade = sc.Grade; 5.1.4 學(xué)生排名類StudentOrder.cs類的代碼如下： public class StudentOrder public int Order get; set; public string Sno get; set; public string Sname get; set; public double GradeSum ge

7、t; set; public StudentOrder (int order, string sno, string sname, double gradeSum) Order = order; Sno = sno; Sname = sname; GradeSum = gradeSum; 5.2 創(chuàng)建數(shù)據(jù)訪問(wèn)層（DAL）創(chuàng)建一個(gè)數(shù)據(jù)訪問(wèn)層類庫(kù)，使用前面創(chuàng)建的SqlDbHelper類實(shí)現(xiàn)真正的數(shù)據(jù)庫(kù)存取操作，包括：查詢、插入、修改和刪除操作，查詢操作可能要提供多種方式的查詢，本示例中創(chuàng)建了一個(gè)DAL的類庫(kù)，類庫(kù)中包括了三個(gè)類文件，分別對(duì)應(yīng)三個(gè)數(shù)據(jù)表的CURD（插改查刪）操作，建立類文件命名為“&

8、lt;表名>DAO.cs”，如下圖。注意：本類庫(kù)在編譯前需要引用Model類庫(kù)。5.2.1 學(xué)生信息存取StudentDAO.cs類的代碼如下： public class StudentDAO public List<Student> GetStudents() SqlDbHelper dbHelper = new SqlDbHelper(); string sql = "SELECT * FROM Student" SqlDataReader reader = dbHelper.ExecuteReader(sql); List<Student>

9、; students = new List<Student>(); while (reader.Read() string no = reader"Sno".ToString().Trim(); string name = reader"Sname".ToString().Trim(); string sex = reader"Ssex".ToString().Trim(); int age = Convert.ToInt32(reader"Sage"); string dept = reader&qu

10、ot;Sdept".ToString().Trim(); students.Add(new Student(no, name, sex, age, dept); reader.Close(); return students; public void Insert(Student s) SqlDbHelper dbHelper = new SqlDbHelper(); string sql = string.Format("INSERT INTO Student VALUES('0', '1', '2', 3, '4&

11、#39;)", s.Sno, s.Sname, s.Ssex, s.Sage, s.Sdept); dbHelper.ExecuteNonQuery(sql); public void Update(Student s) SqlDbHelper dbHelper = new SqlDbHelper(); string sql = string.Format("UPDATE Student SET Sname='1', Ssex='2', " + "Sage=3, Sdept='4' WHERE Sno=&#

12、39;0'", s.Sno, s.Sname, s.Ssex, s.Sage, s.Sdept); dbHelper.ExecuteNonQuery(sql); public void Delete(string sno) SqlDbHelper dbHelper = new SqlDbHelper(); string sql = string.Format("DELETE FROM Student WHERE Sno='0'", sno); dbHelper.ExecuteNonQuery(sql); 5.2.2 課程信息存取Course

13、DAO.cs類的代碼如下： public class CourseDAO public List<Course> GetCourses() SqlDbHelper dbHelper = new SqlDbHelper(); string sql = "SELECT * FROM Course" SqlDataReader reader = dbHelper.ExecuteReader(sql); List<Course> courses = new List<Course>(); while (reader.Read() int no =

14、 Convert.ToInt32(reader"Cno"); string name = reader"Cname".ToString().Trim(); int pno = Convert.IsDBNull(reader"Cpno") ? -1 : Convert.ToInt32(reader"Cpno"); int credit = Convert.ToInt32(reader"Credit"); courses.Add(new Course(no, name, pno, credit);

15、reader.Close(); return courses; public void Insert(Course c) SqlDbHelper dbHelper = new SqlDbHelper(); string sql = string.Format("INSERT INTO Course VALUES(0, '1', 2, 3)", c.Cno, c.Cname, c.Cpno, c.Credit); dbHelper.ExecuteNonQuery(sql); public void Update(Course c) SqlDbHelper db

16、Helper = new SqlDbHelper(); string sql = string.Format("UPDATE Course SET Cname='1', Cpno=2, Credit=3 " + "WHERE Cno=0", c.Cno, c.Cname, c.Cpno, c.Credit); dbHelper.ExecuteNonQuery(sql); public void Delete(int cno) SqlDbHelper dbHelper = new SqlDbHelper(); string sql = st

17、ring.Format("DELETE FROM Course WHERE Cno=0", cno); dbHelper.ExecuteNonQuery(sql); 5.2.3 成績(jī)信息存取SCDAO.cs類的代碼如下： public class SCDAO public List<SCAll> GetSCAlls() SqlDbHelper dbHelper = new SqlDbHelper(); string sql = "SELECT S.Sno, S.Sname, S.Ssex, S.Sage, S.Sdept, " + "

18、;C.Cno, C.Cname, C.Cpno, C.Credit, SC.Grade " + "FROM Student S INNER JOIN SC ON S.Sno=SC.Sno " + "INNER JOIN Course C ON SC.Cno=C.Cno" SqlDataReader reader = dbHelper.ExecuteReader(sql); List<SCAll> scAlls = new List<SCAll>(); while (reader.Read() string sno = r

19、eader"Sno".ToString().Trim(); string sname = reader"Sname".ToString().Trim(); string ssex = reader"Ssex".ToString().Trim(); int sage = Convert.ToInt32(reader"Sage"); string sdept = reader"Sdept".ToString().Trim(); Student s = new Student(sno, sname,

20、ssex, sage, sdept); int cno = Convert.ToInt32(reader"Cno"); string cname = reader"Cname".ToString().Trim(); int cpno = Convert.IsDBNull(reader"Cpno") ? -1 : Convert.ToInt32(reader"Cpno"); int credit = Convert.ToInt32(reader"Credit"); Course c = new C

21、ourse(cno, cname, cpno, credit); double grade = Convert.ToDouble(reader"Grade"); SC sc = new SC(sno, cno, grade); scAlls.Add(new SCAll(s, c, sc); reader.Close(); return scAlls; public List<StudentOrder> GetStudentOrders() SqlDbHelper dbHelper = new SqlDbHelper(); string sql = "S

22、ELECT S.Sno, S.Sname, A.GradeSum " + "FROM Student S INNER JOIN (" + "SELECT Sno, SUM(Grade) GradeSum FROM SC " + "GROUP BY Sno) A ON S.Sno = A.Sno " + "ORDER BY A.GradeSum DESC" SqlDataReader reader = dbHelper.ExecuteReader(sql); List<StudentOrder>

23、 studentOrders = new List<StudentOrder>(); int count = 0, order = 0; double gradeSumLast = -1; while (reader.Read() string sno = reader"Sno".ToString().Trim(); string sname = reader"Sname".ToString().Trim(); double gradeSum = Convert.ToDouble(reader"GradeSum"); co

24、unt+; if (gradeSum != gradeSumLast | order <= 0) order = count; studentOrders.Add(new StudentOrder(order, sno, sname, gradeSum); reader.Close(); return studentOrders; public List<SC> GetSCs() SqlDbHelper dbHelper = new SqlDbHelper(); string sql = "SELECT * FROM SC" SqlDataReader r

25、eader = dbHelper.ExecuteReader(sql); List<SC> scs = new List<SC>(); while (reader.Read() string sno = reader"Sno".ToString().Trim(); int cno = Convert.ToInt32(reader"Cno"); double grade = Convert.ToDouble(reader"Grade"); SC sc = new SC(sno, cno, grade); scs.

26、Add(new SC(sno, cno, grade); reader.Close(); return scs; public void Insert(SC sc) SqlDbHelper dbHelper = new SqlDbHelper(); string sql = string.Format("INSERT INTO SC VALUES('0', 1, 2)", sc.Sno, sc.Cno, sc.Grade); dbHelper.ExecuteNonQuery(sql); public void Update(SC sc) SqlDbHelpe

27、r dbHelper = new SqlDbHelper(); string sql = string.Format("UPDATE SC SET Grade=2 WHERE Sno='0' AND Cno=1", sc.Sno, sc.Cno, sc.Grade); dbHelper.ExecuteNonQuery(sql); public void Delete(string sno, int cno) SqlDbHelper dbHelper = new SqlDbHelper(); string sql = string.Format("D

28、ELETE FROM SC WHERE Sno='0' AND Cno=1", sno, cno); dbHelper.ExecuteNonQuery(sql); 5.3 創(chuàng)建業(yè)務(wù)邏輯層（BLL）業(yè)務(wù)邏輯層是為界面層提供服務(wù)的，通過(guò)數(shù)據(jù)訪問(wèn)層（DAL）來(lái)訪問(wèn)數(shù)據(jù)庫(kù)中的數(shù)據(jù)。關(guān)于業(yè)務(wù)邏輯層類庫(kù)中建立哪些類，沒(méi)有很好的分法。建議還是按照用戶功能模塊來(lái)劃分比較好。本示例的功能簡(jiǎn)單，我們大致劃分為三個(gè)功能模塊：信息管理、信息查詢、統(tǒng)計(jì)分析，這樣我們?cè)贐LL類庫(kù)中建立了三個(gè)業(yè)務(wù)類即：信息管理（Manager）、信息查詢（Query）、統(tǒng)計(jì)分析（StatAnaly）。業(yè)務(wù)邏輯層被

29、封裝在一個(gè)名為BLL的類庫(kù)中，如下圖。注意：本類庫(kù)在編譯前需要引用Model和DAL類庫(kù)。5.3.1 信息管理類在信息管理（Manager）類中需要實(shí)現(xiàn)對(duì)三個(gè)基本表數(shù)據(jù)的添加、修改和刪除操作，示例中實(shí)現(xiàn)了學(xué)生表的插刪改功能、課程表和成績(jī)表的插入功能，代碼如下。 public class Manager public void StudentInsert(Student s) StudentDAO sDAO = new StudentDAO(); sDAO.Insert(s); public void StudentUpdate(Student s) StudentDAO sDAO = new

30、StudentDAO(); sDAO.Update(s); public void StudentDelete(string no) StudentDAO sDAO = new StudentDAO(); sDAO.Delete(no); public void CourseInsert(Course c) CourseDAO cDAO = new CourseDAO(); cDAO.Insert(c); public void SCInsert(SC sc) SCDAO scDAO = new SCDAO(); scDAO.Insert(sc); 5.3.2 信息查詢類在信息查詢（Query

31、）類中實(shí)現(xiàn)了對(duì)學(xué)生信息、課程信息、成績(jī)信息的一些查詢功能，具體功能參見如下代碼。 public class Query public List<Student> GetStudents() StudentDAO sDAO = new StudentDAO(); List<Student> students0 = sDAO.GetStudents(); return students0; public List<Student> GetStudentsByNo(string no) StudentDAO sDAO = new StudentDAO(); Lis

32、t<Student> students0 = sDAO.GetStudents(); if (string.IsNullOrEmpty(no) return students0; List<Student> students = new List<Student>(); foreach (Student s in students0) if (s.Sno = no) students.Add(s); return students; public List<Student> GetStudentsBySexDept(string sex, str

33、ing dept) StudentDAO sDAO = new StudentDAO(); List<Student> students0 = sDAO.GetStudents(); if (string.IsNullOrEmpty(sex) && string.IsNullOrEmpty(dept) return students0; List<Student> students = new List<Student>(); foreach (Student s in students0) if (!string.IsNullOrEmpty

34、(sex) && !string.IsNullOrEmpty(dept) if (s.Ssex = sex && s.Sdept.IndexOf(dept) >= 0) students.Add(s); else if (!string.IsNullOrEmpty(sex) if (s.Ssex = sex) students.Add(s); else if (!string.IsNullOrEmpty(dept) if (s.Sdept.IndexOf(dept) >= 0) students.Add(s); return students; pu

35、blic List<Course> GetCourses() CourseDAO cDAO = new CourseDAO(); List<Course> courses = cDAO.GetCourses(); return courses; public List<SCAll> GetSCAlls() SCDAO sDAO = new SCDAO(); List<SCAll> scAlls = sDAO.GetSCAlls(); return scAlls; 5.3.3 統(tǒng)計(jì)分析類在統(tǒng)計(jì)分析（StatAnaly）類中只實(shí)現(xiàn)了按學(xué)生總成績(jī)排名的

36、功能，其它功能（如：不及格名單）需要以后補(bǔ)充，完成的代碼如下。 public class StatAnaly public List<StudentOrder> GetStudentOrders() SCDAO scDAO = new SCDAO(); List<StudentOrder> studentOrders = scDAO.GetStudentOrders(); return studentOrders; 5.4 創(chuàng)建界面層這里說(shuō)的界面層實(shí)際上就是指Win界面的應(yīng)用程序，是在三層架構(gòu)中處于界面層的位置，實(shí)現(xiàn)時(shí)需要在當(dāng)前解決方案中添加一個(gè)“Windows窗體應(yīng)用

37、程序”，本示例是一個(gè)簡(jiǎn)單的學(xué)生信息管理系統(tǒng)，建議本示例的項(xiàng)目名稱為“ChA106_學(xué)生信息管理”，如下圖。注意：本類庫(kù)在編譯前需要引用Model和BLL類庫(kù)，還有System.Configuration。5.4.1 主窗體實(shí)現(xiàn)學(xué)生信息管理系統(tǒng)雖然簡(jiǎn)單，但也具有一個(gè)完整系統(tǒng)的特質(zhì)，我們需要建立一個(gè)主窗體，給主窗體上添加一個(gè)菜單控件，將所有功能放置在各個(gè)菜單項(xiàng)中，然后通過(guò)用戶點(diǎn)擊各個(gè)菜單項(xiàng)來(lái)完成整個(gè)系統(tǒng)的功能。主窗體的設(shè)計(jì)如下圖。 5.4.2 “學(xué)生信息”功能實(shí)現(xiàn)“學(xué)生信息”功能與“ChA105_插刪改數(shù)據(jù)庫(kù)”項(xiàng)目中功能完全一樣，只不過(guò)這里不再直接使用SqlConnection, SqlComma

38、nd等對(duì)象訪問(wèn)數(shù)據(jù)，也不直接使用SqlDbHelper類來(lái)訪問(wèn)數(shù)據(jù)，而是使用三層架構(gòu)中的BLL類庫(kù)中的類來(lái)存取數(shù)據(jù)庫(kù)中的數(shù)據(jù)。設(shè)計(jì)“學(xué)生信息”窗體設(shè)計(jì)的“學(xué)生信息”窗體名為frmStudentInfo，窗體的樣式如下圖，上部分是用于編輯學(xué)生信息的文本編輯框，下部分是用于顯示當(dāng)前所有學(xué)生信息的DataGridView。響應(yīng)窗體的“Load”事件 private void FrmStudentInfo_Load(object sender, EventArgs e) Query query = new Query(); List<Student> students = query.Ge

39、tStudents(); dataGridView1.DataSource = students; 響應(yīng)“添加”按鈕的“Click”事件 private void btnInsert_Click(object sender, EventArgs e) Student s = new Student(); s.Sno = txtSno.Text; s.Sname = txtSname.Text; s.Ssex = txtSsex.Text; s.Sage = int.Parse(txtSage.Text); s.Sdept = txtSdept.Text; Manager manager = n

40、ew Manager(); manager.StudentInsert(s); FrmStudentInfo_Load(null, null); 響應(yīng)DataGridView的“CellEnter”事件 private void dataGridView1_CellEnter(object sender, DataGridViewCellEventArgs e) DataGridViewRow row = dataGridView1.CurrentRow; txtSno.Text = row.Cells"Sno".Value.ToString(); txtSname.Tex

41、t = row.Cells"Sname".Value.ToString(); txtSsex.Text = row.Cells"Ssex".Value.ToString(); txtSage.Text = row.Cells"Sage".Value.ToString(); txtSdept.Text = row.Cells"Sdept".Value.ToString(); 響應(yīng)“修改”按鈕的“Click”事件 private void btnUpdate_Click(object sender, EventArgs

42、 e) Student s = new Student(); s.Sno = txtSno.Text; s.Sname = txtSname.Text; s.Ssex = txtSsex.Text; s.Sage = int.Parse(txtSage.Text); s.Sdept = txtSdept.Text; Manager manager = new Manager(); manager.StudentUpdate(s); FrmStudentInfo_Load(null, null); 響應(yīng)“刪除”按鈕的“Click”事件 private void btnDelete_Click(o

43、bject sender, EventArgs e) Manager manager = new Manager(); manager.StudentDelete(txtSno.Text); FrmStudentInfo_Load(null, null); 響應(yīng)主窗體“學(xué)生信息”菜單的"Click"事件 private void miStudentInfo_Click(object sender, EventArgs e) FrmStudentInfo frmStudentInfo = new FrmStudentInfo(); frmStudentInfo.ShowDia

44、log(); 測(cè)試“學(xué)生信息”功能在本項(xiàng)目的第一次測(cè)試運(yùn)行前，需要確認(rèn)app.config文件的存在，并設(shè)置數(shù)據(jù)庫(kù)訪問(wèn)的連接信息，具體的內(nèi)容參見如下代碼。<?xml version="1.0" encoding="utf-8" ?><configuration> <connectionStrings> <add name="ConnectionString" providerName="System.Data.SqlClient" connectionString=&quo

45、t;Data Source=.Sql2008;Integrated Security=True; Initial Catalog=DBS"/> </connectionStrings> <appSettings> <add key="ConnEncrypt" value="false"/> </appSettings></configuration>將“ChA106_學(xué)生信息管理”設(shè)置為啟動(dòng)項(xiàng)目，或者鼠標(biāo)右擊項(xiàng)目“ChA106_學(xué)生信息管理”，選擇菜單項(xiàng)“調(diào)試 > 啟動(dòng)新實(shí)例”即可運(yùn)行當(dāng)前項(xiàng)目，在主窗體中選擇菜單“數(shù)據(jù)管理 > 學(xué)生信息”菜單項(xiàng)，即可彈出“學(xué)生信息”對(duì)話框，如