可视化编程技术作业二.doc

《可视化编程技术作业二.doc》由会员分享，可在线阅读，更多相关《可视化编程技术作业二.doc（17页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、可视化编程技术作业（二）班级：11网络B班 姓名：张耀东 学号：37一、问题描述：对SQL Server中的Northwind数据库创建一数据库应用程序，该应用程序能够实现浏览每一雇员的相关信息。二、 分析与设计该应用程序涉及到对雇员数据进行显示、操作的用户界面，涉及到读取雇员数据库表中的记录。为了避免对数据库进行反复操作，我们声明方法getAll一次将数据库表中的所有数据读出，并用每条雇员记录创建一个雇员对象将雇员对象添加到ArrayList对象中，这样雇员表中的每条记录以雇员对象的形式暂存在ArrayList对象中，雇员表中的一条对应一个ArrayList列表中的一个雇员对象。然后，我们对

2、ArrayList列表中的雇员对象进行操作，浏览每个雇员的数据。根据以上思路，我们必须声明一个雇员Employee类,以存储雇员记录的数据。雇员数据库表有多少字段，雇员类就应该有多少字段，并在雇员类为每个字段声明属性。同时声明一个Employee类，该类有一个ArrayList对象字段，有getAll方法和获取ArrayList列表中雇员对象的方法（包括包括获取第一条、上一条、下一条、最后一条、添加、修改、删除雇员的方法）。图形界面类与Employee类和EmployeeDA类进行交互，而不直接与数据库进行交互。三、解决方案1、 创建项目和编写Employee类及EmployeeDA类。（1）

3、 创建项目名为Northwind的Windows应用程序。（2） 创建Employee类。向Northwind项目添加Employee类：using System;using System.Collections.Generic;using System.Linq;using System.Text;namespace Northwind public class Employee private int _EmployeeID; public int EmployeeID get return _EmployeeID; set _EmployeeID = value; private Str

4、ing _LastName; public String LastName get return _LastName; set _LastName = value; private String _FirstName; public String FirstName get return _FirstName; set _FirstName = value; private String _Title; public String Title get return _Title; set _Title = value; private String _TitleOfCourtesy; publ

5、ic String TitleOfCourtesy get return _TitleOfCourtesy; set _TitleOfCourtesy = value; private String _BirthDate; public String BirthDate get return _BirthDate; set _BirthDate = value; private String _HireDate; public String HireDate get return _HireDate; set _HireDate = value; private String _Address

6、; public String Address get return _Address; set _Address = value; private String _City; public String City get return _City; set _City = value; private String _Region; public String Region get return _Region; set _Region = value; private String _PostalCode; public String PostalCode get return _Post

7、alCode; set _PostalCode = value; private String _Country; public String Country get return _Country; set _Country = value; private String _HomePhone; public String HomePhone get return _HomePhone; set _HomePhone = value; private String _Extension; public String Extension get return _Extension; set _

8、Extension = value; private String _Notes; public String Notes get return _Notes; set _Notes = value; private int _ReportsTo; public int ReportsTo get return _ReportsTo; set _ReportsTo = value; private String _photoPath; public String PhotoPath get return _photoPath; set _photoPath = value; public Em

9、ployee() public Employee(int id, String mLastName, String mFirstName, String mTitle, String mTitleOfCourtesy, String mBirthDate, String mHireDate, String mAddress, String mCity, String mRegion, String mPostalCode, String mCountry, String mHomePhone, String mExtension, int mReportsTo) _EmployeeID = i

10、d; _LastName = mLastName; _FirstName = mFirstName; _Title = mTitle; _TitleOfCourtesy = mTitleOfCourtesy; _BirthDate = mBirthDate; _HireDate = mHireDate; _Address = mAddress; _City = mCity; _Region = mRegion; _PostalCode = mPostalCode; _Country = mCountry; _HomePhone = mHomePhone; _Extension = mExten

11、sion; _ReportsTo = mReportsTo; public override string ToString() return this.FirstName + + this.LastName; （3） 创建EmployeeDA类。向Northwind项目添加如下EmployeeDA类：using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Windows.Forms;using System.Data.SqlClient;using Syste

12、m.Collections;namespace Northwind public class EmployeeDA private static ArrayList employess = new ArrayList(); private static int i = 0; private static SqlConnection aConnection; /- private static void GetDBConnection() try string strConn = Data Source=(local);User ID=sa;Password=1234;Initial Catal

13、og=Northwind; aConnection = new SqlConnection(strConn); /aConnection.ConnectionString = strConn; catch(Exception oExcept) MessageBox.Show(oExcept.Message); /- public static ArrayList GetAll() employess.Clear(); SqlCommand oCmd; SqlDataReader oDR = null; String strSQL = SELECT * from Employees; try G

14、etDBConnection(); oCmd = new SqlCommand(); oCmd.Connection = aConnection; oCmd.Connection.Open(); oCmd.CommandText = strSQL; oDR = oCmd.ExecuteReader(); while (oDR.Read() Employee aEmployee = new Employee(); aEmployee.EmployeeID = Int32.Parse(oDREmployeeID.ToString(); aEmployee.LastName = oDRLastNam

15、e.ToString(); aEmployee.FirstName = oDRFirstName.ToString(); aEmployee.BirthDate = oDRBirthDate.ToString(); aEmployee.HireDate = oDRHireDate.ToString(); aEmployee.Title = oDRTitle.ToString(); aEmployee.TitleOfCourtesy = oDRTitleOfCourtesy.ToString(); aEmployee.Address = oDRAddress.ToString(); aEmplo

16、yee.Country = oDRCountry.ToString(); aEmployee.Extension = oDRExtension.ToString(); aEmployee.HomePhone = oDRHomePhone.ToString(); aEmployee.Notes = oDRNotes.ToString(); aEmployee.PostalCode = oDRPostalCode.ToString(); aEmployee.Region = oDRRegion.ToString(); if (oDR.IsDBNull(16) aEmployee.ReportsTo

17、 = 0; else aEmployee.ReportsTo = Int32.Parse(oDRReportsTo.ToString(); if (oDR.IsDBNull(17) aEmployee.PhotoPath = ; else aEmployee.PhotoPath = oDRPhotoPath.ToString(); employess.Add(aEmployee); catch (Exception oExcept) MessageBox.Show(oExcept.Message); finally CloseSqlDataReader(oDR); CloseConnectio

18、n(); return employess; /- private static void CloseConnection() if(aConnection != null) aConnection.Close(); /- private static void CloseSqlDataReader(SqlDataReader oDR) if (oDR != null) oDR.Close(); /- public static Employee GetCurrentEmployee() if (employess.Count 0) Employee aEmployee = (Employee

19、)employessi; return aEmployee; else return null; /- public static Employee GetFirstEmployee() Employee aEmployee = new Employee(); if (employess.Count 0) i = 0; aEmployee = (Employee)employessi; return aEmployee; /- public static Employee GetNextEmployee() Employee aEmployee = new Employee(); if (i

20、0) i = i - 1; aEmployee = (Employee)employessi; return aEmployee; return aEmployee = (Employee)employess0; /- public static Employee GetLastEmployee() Employee aEmployee = new Employee(); if (employess.Count 0) i = employess.Count - 1; aEmployee = (Employee)employessi; return aEmployee; /- public st

21、atic void AddNew(Employee aEmployee) SqlDataReader oDR = null; string strSQL = INSERT INTO Employees; strSQL += (Address,BirthDate,Country,Extension, + FirstName,LastName,HireDate,; strSQL += HomePhone,Notes,PostalCode,Region, + City,ReportsTo,Title,TitleOfCourtesy); strSQL += VALUES(; strSQL += + a

22、Employee.Address + + ,; strSQL += + aEmployee.BirthDate + + ,; strSQL += + aEmployee.Country + + ,; strSQL += + aEmployee.Extension + + ,; strSQL += + aEmployee.FirstName + + ,; strSQL += + aEmployee.LastName + + ,; strSQL += + aEmployee.HireDate + + ,; strSQL += + aEmployee.HomePhone + + ,; strSQL

23、+= + aEmployee.Notes + + ,; strSQL += + aEmployee.PostalCode + + ,; strSQL += + aEmployee.Region + + ,; strSQL += + aEmployee.City + + ,; strSQL += aEmployee.ReportsTo + ,; strSQL += + aEmployee.Title + + ,; /strSQL += + aEmployee.getPhotoPath() + + ,; strSQL += + aEmployee.TitleOfCourtesy + + ); tr

24、y GetDBConnection(); SqlCommand oCmd = new SqlCommand(strSQL, aConnection); oCmd.Connection.Open(); oCmd.ExecuteNonQuery(); i = employess.Count; catch (Exception oExcept) MessageBox.Show(oExcept.Message); finally CloseSqlDataReader(oDR); CloseConnection(); /- public static void Update(Employee aEmpl

25、oyee) SqlDataReader oDR = null; Employee currentEmployee = GetCurrentEmployee(); int id = currentEmployee.EmployeeID; String strSQL = UPDATE Employees SET; strSQL += Address= + + aEmployee.Address + + ,; strSQL += BirthDate= + + aEmployee.BirthDate + + ,; strSQL += Country= + + aEmployee.Country + +

26、 ,; strSQL += Extension= + + aEmployee.Extension + + ,; strSQL += FirstName= + + aEmployee.FirstName + + ,; strSQL += LastName= + + aEmployee.LastName + + ,; strSQL += HireDate= + + aEmployee.HireDate + + ,; strSQL += HomePhone= + + aEmployee.HomePhone + + ,; strSQL += Notes= + + aEmployee.Notes + +

27、 ,; /strSQL += + aEmployee.PhotoPath + + ,; strSQL += PostalCode= + + aEmployee.PostalCode + + ,; strSQL += Region= + + aEmployee.Region + + ,; strSQL += City= + + aEmployee.City + + ,; strSQL += ReportsTo= + aEmployee.ReportsTo + ,; strSQL += Title= + + aEmployee.Title + + ,; strSQL += TitleOfCourt

28、esy= + + aEmployee.TitleOfCourtesy + ; strSQL += WHERE EmployeeID= + id; try GetDBConnection(); SqlCommand oCmd = new SqlCommand(strSQL, aConnection); oCmd.Connection.Open(); oCmd.ExecuteNonQuery(); catch (Exception oExcept) MessageBox.Show(oExcept.Message); finally CloseSqlDataReader(oDR); CloseConnection(); /-