3层结构实验报告

专业技能训练报告学院：计算机学院课程名称：网络应用开发专业技能训练专业班级：学生：学号：学生：学号：学生：学号：指导教师：黄涛完成时间：2015年6月27日目录1网络应用开发实验 (3)1.1问题描述 (3)1.2需求分析 (3)1.3概要设计 (4)1.4流程图 (4)1.5详细设计 (5)1.6调试分析 (20)1.7运行结果及分析 (20)《网络应用开发》评分表 (22)1网络应用开发实验1.1问题描述一个数据库中，有两个数据表。

其中custom，department表如下结构。

Custom与department的关系如下图所示。

设计一个基于Web的应用程序，采用3层结构的方式实现对custom，department 表中的记录进行：插入、修改、删除、查询的操作。

使用语言：C#语言编译环境：visual studio 20131.2需求分析使用三层架构来开发系统和，开发人员可以只关注整个结构中的其中某一层；可以很容易的用新的实现来替换原有层次的实现；可以降低层与层之间的依赖；有利于标准化；利于各层逻辑的复用；结构更加的明确；在后期维护的时候，极降低了维护成本和维护时间。

1.3概要设计三层架构(3-tier architecture) 通常意义上的三层架构就是将整个业务应用划分为：表现层（Presentation layer）、业务逻辑层（Business Logic Layer）、数据访问层（Data access layer）。

我们先设计一个model，用于传递和记录相应表的一行数据值，简化传参的过程。

然后设计数据访问层（DAL），用于对数据表的数据处理；同时设计一个相应的SQLhelper 类，用于对数据库的操作（增加、删除、修改、查询）进行封装。

接着再设计业务逻辑层（BLL），用于操作数据访问层，解决具体业务问题，最后设计表现层，可用web页面或者winform页面，主要对用户的请求接受，以及数据的返回，为客户端提供应用程序的访问1.4流程图1.5详细设计1.创建数据库，命名为CustANDDepa建立Custom表建立Department表表格示例数据打开VS2013创建一个项目，依次创建BLL类库（业务层），DAL类库（DAL层），Model （实体数据层）类库和SQLHelper类库(数据库操作类)结果如图2.Model(数据实体模型)数据实体模式用于映射数据表或试图，该类包括custom表和department表的字段属性DepartmentModel.csCustomModel.cs2.SQLHelper（数据库操作类）封装了数据操作的常用方法，包括数据库对象的建立，数据连接与释放，无返回值的查询操作，单反回值的查询操作以及多返回值得查询操作。

3.DAL(数据操作层) CustomDAL.csusing System;using System.Collections.Generic; using System.Data;using System.Data.SqlClient; using System.Linq;using System.Text;using System.Threading.Tasks; using SQLHelper;using Model;namespace DAL{public class CustomDAL{///<summary>///数据模型转换///</summary>///<param name="row"></param>///<returns></returns>private CustomModel ToModel(DataRow row){CustomModel custom=new CustomModel();custom.id= (int)row["id"];ame= (string)row["cname"];custom.departID= (int)row["departID"];custom.age= (int)row["age"];custom.ename= (string)row["ename"];custom.password= (string)row["password"];return custom;}///<summary>///验证用户///</summary>///<param name="custom"></param>///<returns></returns>public int CheckUser(CustomModel custom){string ename=custom.ename;string password=custom.password;returnConvert.ToInt32(SqlHelper.ExecuteScalar("select * from T_Custom where ename=ename and password=password",new SqlParameter("ename", ename),new SqlParameter("password", password)));}///<summary>///获取记录数///</summary>///<returns></returns>public int GetRecordCounts(){return Convert.ToInt32(SqlHelper.ExecuteScalar("select count(*) fromT_Custom"));}///<summary>///查询所有的数据///</summary>///<returns></returns>public IEnumerable<CustomModel>ListAll(){List<CustomModel>list=new List<CustomModel>();DataTable dt=SqlHelper.ExecuteDataTable("select * from T_Custom");foreach (DataRow row in dt.Rows){CustomModel cust=ToModel(row);}return list;}///<summary>///根据id获取数据*************查///</summary>///<param name="id"></param>///<returns></returns>public CustomModel GetById(int id){DataTable dt=SqlHelper.ExecuteDataTable("select * from T_Custom where id=id",new SqlParameter("id", id));if (dt.Rows.Count<=0){return null;}else{return ToModel(dt.Rows[0]);}}///<summary>///根据departid查询记录///</summary>///<param name="departId"></param>///<returns></returns>public DataTable GetByValueDepartId(int departId){return SqlHelper.ExecuteDataTable("select * from T_Custom wheredepartID=departId",new SqlParameter("departId", departId));}///<summary>///根据id获取数据集合用于Gridview控件的数据绑定///</summary>///<param name="id"></param>///<returns></returns>public DataTable GetByValueId(int id){return SqlHelper.ExecuteDataTable("select * from T_Custom where id=id", new SqlParameter("id", id));}///<summary>///根据查询记录///</summary>///<param name="name"></param>///<returns></returns>public DataTable GetByName(string name){return SqlHelper.ExecuteDataTable("select * from T_Custom whereame=name", new SqlParameter("name", name));}///<summary>///根据年龄查询记录///</summary>///<param name="age"></param>///<returns></returns>public DataTable GetByage(int age){return SqlHelper.ExecuteDataTable("select * from T_Custom where age=age", new SqlParameter("age", age));}///<summary>///更新一条记录************改///</summary>///<param name="custom"></param>public void Update(CustomModel custom){SqlHelper.ExecuteNonQuery("update [T_Custom]setame=cname,departID=departID,age=age,ename=ename,password=password where id=id", new SqlParameter("cname", ame), new SqlParameter("departID", custom.departID), new SqlParameter("age", custom.age), new SqlParameter("ename", custom.ename), new SqlParameter("password", custom.password), new SqlParameter("id", custom.id));}///<summary>///插入一条记录*************增///</summary>///<param name="custom"></param>public void Insert(CustomModel custom){SqlHelper.ExecuteNonQuery("INSERT INTO[T_Custom]([id],[cname],[departID],[age],[ename],[password])VALUES(id,cname,departID,a ge,ename,password)", new SqlParameter("cname", ame), new SqlParameter("departID", custom.departID), new SqlParameter("age", custom.age), new SqlParameter("ename", custom.ename), new SqlParameter("password", custom.password), new SqlParameter("id", custom.id));}///<summary>///删除id=id的记录************删///</summary>///<param name="id"></param>public void DeleteById(int id){SqlHelper.ExecuteNonQuery("delete from T_Custom where id=id", new SqlParameter("id", id));}}}departmentDAL.csusing System;using System.Collections.Generic;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Text;using System.Threading.Tasks;using SQLHelper;using Model;namespace DAL{public class DepartmentDAL{///<summary>///数据模型的转换///</summary>///<param name="row"></param>///<returns></returns>private DepartmentModel ToModel(DataRow row){DepartmentModel department=new DepartmentModel();department.id= (int) row["id"];department.departname= (string) row["departname"];department.description= (string) row["description"];return department;}///<summary>///获取记录数///</summary>///<returns></returns>public int GetRecordCounts(){return Convert.ToInt32(SqlHelper.ExecuteScalar("select count(*) fromT_Department"));}///<summary>///根据id获取数据***************查///</summary>///<param name="id"></param>///<returns></returns>public DepartmentModel GetById(int id){DataTable dt=SqlHelper.ExecuteDataTable("select * from T_Department where id=id",new SqlParameter("id", id));if (dt.Rows.Count<=0){return null;}else{return ToModel(dt.Rows[0]);}}///<summary>///根据id获取数据集合用于Gridview控件的数据绑定///<param name="id"></param>///<returns></returns>public DataTable GetByValueId(int id){return SqlHelper.ExecuteDataTable("select * from T_Department where id=id", new SqlParameter("id", id));}///<summary>///根据部门名称查询并返回记录集///</summary>///<param name="departname"></param>///<returns></returns>public DataTable GetByName(string departname){return SqlHelper.ExecuteDataTable("select * from T_Department where departname=departname",new SqlParameter("departname", departname));}///<summary>///更新一条记录*****************改///</summary>///<param name="department"></param>public void Update(DepartmentModel department){SqlHelper.ExecuteNonQuery("update [T_Department] setdepartname=departname,description=description where id=id", new SqlParameter("departname", department.departname), new SqlParameter("description", department.description), new SqlParameter("id", department.id));}///<summary>///插入一条记录///</summary>///<param name="department"></param>public void Insert(DepartmentModel department){SqlHelper.ExecuteNonQuery("INSERT INTO[T_Department]([id],[departname],[description])VALUES(id,departname,description)", new SqlParameter("id", department.id), new SqlParameter("departname", department.departname), new SqlParameter("description", department.description));}///删除一条记录///</summary>///<param name="id"></param>public void DeleteById(int id){SqlHelper.ExecuteNonQuery("delete from T_Department where id=id", new SqlParameter("id", id));}}}4.BLL(业务逻辑层)customBLL.csusing System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Runtime.InteropServices;using System.Text;using System.Threading.Tasks;using DAL;using Model;namespace BLL{public class CustomBLL{DAL.CustomDAL customDal=new CustomDAL();///<summary>///检查登陆名及密码///</summary>///<param name="custom"></param>///<returns></returns>public bool CheckUser(CustomModel custom){if (customDal.CheckUser(custom) ==1){return true;}else{return false;}}///<summary>///获取记录数///</summary>///<returns></returns>public int RecordCount(){return customDal.GetRecordCounts();}///<summary>///添加对象///</summary>///<param name="custom"></param>public void addUser(Model.CustomModel custom){customDal.Insert(custom);}///<summary>///根据departid值查询并返回记录///</summary>///<param name="id"></param>///<returns></returns>public DataTable GetBydepartId(int departid){return customDal.GetByValueDepartId(departid); }///<summary>///根据id值查询并返回记录///</summary>///<param name="id"></param>///<returns></returns>public DataTable GetByValueId(int id){return customDal.GetByValueId(id);}///<summary>///根据id返回数据模型///</summary>///<param name="id"></param>///<returns></returns>public CustomModel GetById(int id){return customDal.GetById(id);}///<summary>///根据查询并返回记录///</summary>///<param name="name"></param>///<returns></returns>public DataTable GetByName(string name){return customDal.GetByName(name);}///<summary>///根据年龄查询并返回记录///</summary>///<param name="age"></param>///<returns></returns>public DataTable GetByAge(int age){return customDal.GetByage(age);}///<summary>///根据id值删除记录///</summary>///<param name="id"></param>public void DeleteById(int id){customDal.DeleteById(id);}///<summary>///更新记录///</summary>///<param name="custom"></param>public void Update(CustomModel custom){customDal.Update(custom);}}}departmentBLL.csusing System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Text;using System.Threading.Tasks;using DAL;using Model;namespace BLL{public class DepartmentBLL{DAL.DepartmentDAL departmentDal=new DepartmentDAL();///<summary>///获取记录数///</summary>///<returns></returns>public int RecordCount(){return departmentDal.GetRecordCounts();}///<summary>///添加对象///</summary>///<param name="department"></param>public void addDepartment(Model.DepartmentModel department) {departmentDal.Insert(department);}///<summary>///根据id值查询并返回记录///</summary>///<param name="id"></param>///<returns></returns>public DataTable GetByValueId(int id){return departmentDal.GetByValueId(id);}///<summary>///根据id返回数据模型///</summary>///<param name="id"></param>///<returns></returns>public DepartmentModel GetById(int id){return departmentDal.GetById(id);}///<summary>///根据查询并返回记录///</summary>///<param name="name"></param>///<returns></returns>public DataTable GetBydepartname(string departname) {return departmentDal.GetByName(departname);}///<summary>///根据id值删除记录///</summary>///<param name="id"></param>public void DeleteById(int id){departmentDal.DeleteById(id);}///<summary>///更新记录///</summary>///<param name="custom"></param>public void Update(DepartmentModel department){departmentDal.Update(department);}}}5．web(UI表示层)登陆1.6调试分析1.7运行结果及分析Custom表增加记录查询记录删除记录修改记录Department表操作逻辑相似《网络应用开发》评分表指导教师签名：日期：_2015年6月27日_。