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

可视化编程技术作业（二）班级：11网络B班 姓名：张耀东 学号：37一、问题描述：对SQL Server中的Northwind数据库创建一数据库应用程序，该应用程序能够实现浏览每一雇员的相关信息。二、 分析与设计该应用程序涉及到对雇员数据进行显示、操作的用户界面，涉及到读取雇员数据库表中的记录。为了避免对数据库进行反复操作，我们声明方法getAll一次将数据库表中的所有数据读出，并用每条雇员记录创建一个雇员对象将雇员对象添加到ArrayList对象中，这样雇员表中的每条记录以雇员对象的形式暂存在ArrayList对象中，雇员表中的一条对应一个ArrayList列表中的一个雇员对象。然后，我们对ArrayList列表中的雇员对象进行操作，浏览每个雇员的数据。根据以上思路，我们必须声明一个雇员Employee类,以存储雇员记录的数据。雇员数据库表有多少字段，雇员类就应该有多少字段，并在雇员类为每个字段声明属性。同时声明一个Employee类，该类有一个ArrayList对象字段，有getAll方法和获取ArrayList列表中雇员对象的方法（包括包括获取第一条、上一条、下一条、最后一条、添加、修改、删除雇员的方法）。图形界面类与Employee类和EmployeeDA类进行交互，而不直接与数据库进行交互。三、解决方案1、 创建项目和编写Employee类及EmployeeDA类。（1） 创建项目名为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 String _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; public 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; 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 _PostalCode; 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 _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 Employee() 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 = id; _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 = mExtension; _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 System.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 Catalog=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 GetDBConnection(); 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(oDR"EmployeeID".ToString(); aEmployee.LastName = oDR"LastName".ToString(); aEmployee.FirstName = oDR"FirstName".ToString(); aEmployee.BirthDate = oDR"BirthDate".ToString(); aEmployee.HireDate = oDR"HireDate".ToString(); aEmployee.Title = oDR"Title".ToString(); aEmployee.TitleOfCourtesy = oDR"TitleOfCourtesy".ToString(); aEmployee.Address = oDR"Address".ToString(); aEmployee.Country = oDR"Country".ToString(); aEmployee.Extension = oDR"Extension".ToString(); aEmployee.HomePhone = oDR"HomePhone".ToString(); aEmployee.Notes = oDR"Notes".ToString(); aEmployee.PostalCode = oDR"PostalCode".ToString(); aEmployee.Region = oDR"Region".ToString(); if (oDR.IsDBNull(16) aEmployee.ReportsTo = 0; else aEmployee.ReportsTo = Int32.Parse(oDR"ReportsTo".ToString(); if (oDR.IsDBNull(17) aEmployee.PhotoPath = " " else aEmployee.PhotoPath = oDR"PhotoPath".ToString(); employess.Add(aEmployee); catch (Exception oExcept) MessageBox.Show(oExcept.Message); finally CloseSqlDataReader(oDR); CloseConnection(); 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)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 < employess.Count - 1) i = i + 1; aEmployee = (Employee)employessi; return aEmployee; i = employess.Count - 1; return aEmployee = (Employee)employessemployess.Count - 1; /- public static Employee GetPrevEmployee() Employee aEmployee = new Employee(); if (i > 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 static 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 += "'" + aEmployee.Address + "'" + "," strSQL += "'" + aEmployee.BirthDate + "'" + "," strSQL += "'" + aEmployee.Country + "'" + "," strSQL += "'" + aEmployee.Extension + "'" + "," strSQL += "'" + aEmployee.FirstName + "'" + "," strSQL += "'" + aEmployee.LastName + "'" + "," strSQL += "'" + aEmployee.HireDate + "'" + "," strSQL += "'" + aEmployee.HomePhone + "'" + "," strSQL += "'" + aEmployee.Notes + "'" + "," strSQL += "'" + aEmployee.PostalCode + "'" + "," strSQL += "'" + aEmployee.Region + "'" + "," strSQL += "'" + aEmployee.City + "'" + "," strSQL += aEmployee.ReportsTo + "," strSQL += "'" + aEmployee.Title + "'" + "," /strSQL += "'" + aEmployee.getPhotoPath() + "'" + "," strSQL += "'" + aEmployee.TitleOfCourtesy + "'" + ")" try 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 aEmployee) 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 + "'" + "," strSQL += " Extension=" + "'" + aEmployee.Extension + "'" + "," strSQL += " FirstName=" + "'" + aEmployee.FirstName + "'" + "," strSQL += " LastName=" + "'" + aEmployee.LastName + "'" + "," strSQL += " HireDate=" + "'" + aEmployee.HireDate + "'" + "," strSQL += " HomePhone=" + "'" + aEmployee.HomePhone + "'" + "," strSQL += " Notes=" + "'" + aEmployee.Notes + "'" + "," /strSQL += "'" + aEmployee.PhotoPath + "'" + "," strSQL += " PostalCode=" + "'" + aEmployee.PostalCode + "'" + "," strSQL += " Region=" + "'" + aEmployee.Region + "'" + "," strSQL += " City= " + "'" + aEmployee.City + "'" + "," strSQL += " ReportsTo=" + aEmployee.ReportsTo + "," strSQL += " Title=" + "'" + aEmployee.Title + "'" + "," strSQL += " TitleOfCourtesy=" + "'" + 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(); /-