3层结构实验报告

1、专业技能训练报告学 院：计算机学院课程名称：网络应用开发专业技能训练专业班级：学生：_学号：学生：学号：学生：学号：指导教师：黄涛完成时间：2015年6月27日目录1 网络应用开发实验 31.1 问题描述 . 31.2 需求分析 . 31.3 概要设计 . 41.4 流程图 . 41.5 详细设计 . 51.6 调试分析 . 201.7 运行结果及分析 . 20网络应用开发评分表 221网络应用开发实验1.1问题描述一个数据库中，有两个数据表。其中 custom, department表如下结构。Iidintmcrwrnenvarchar；M；c=depadDintmageintenameiw

2、archar50；LJpordrwarcharKiS3Custom与department的关系如下图所示。custom设计一个基于 Web的应用程序，采用3层结构的方式实现对custom，department 表中的记录进行：插入、修改、删除、查询的操作。使用语言：C#语言编译环境：visual studio 20131.2需求分析使用三层架构来开发系统和，开发人员可以只关注整个结构中的其中某一层；可以 很容易的用新的实现来替换原有层次的实现；可以降低层与层之间的依赖；有利于标准 化；利于各层逻辑的复用；结构更加的明确；在后期维护的时候，极降低了维护成本和 维护时间。1.3概要设计三层架构(3

3、-tierarchitecture)通常意义上的三层架构就是将整个业务应用划分为：表现层(Presentation layer )、业务逻辑层(Business Logic Layer )、数据访问 层(Data access layer )。我们先设计一个model，用于传递和记录相应表的一行数据值，简化传参的过程。 然后设计数据访问层(DAL,用于对数据表的数据处理；同时设计一个相应的SQLhelper 类，用于对数据库的操作(增加、删除、修改、查询)进行封装。接着再设计业务逻辑 层(BLL)，用于操作数据访问层，解决具体业务问题，最后设计表现层，可用web页面或者winform页面，主要

4、对用户的请求接受，以及数据的返回，为客户端提供应用程序 的访问1.4流程图UI页面1.5详细设计1.创建数据库，命名为 CustANDDepaDREAWPLUS (SQL Server 110引 H叵LJ数据廉 id LU题ffi騰庫 田Lj畑库快照-j CXjStANOpmp 甘 J +. 口数据库关系圈0 Ej出口视團1 i_j同义词 a Li可编握性 t LJ Service Broker 凶L-J存储 卩安全性建立Custom表Qintcnamenarchar(SO)departlDintageintenamenvarchar(50)passwordnvarchar(50)建立 Dep

5、artment 表列答允许Null ®?圍int ndepa rtnamenvarchar(50descriptionnchar(IQ)圈列名允许Null值表格示例数据idcnamedeparUDageenamepassword卜02nUseirhangUserZhang2事四222Us«rUUserUa123User WangUserWang144UserZhaoUserZhac5陈七255UserChenUserChen*NULLNULLNULLNULLNULLNULLidde-partna medescription0国防那保护国宝安全2不般不听从的部门3ft*

6、74;按术不过关的4具他打开VS2013创建一个项目，依次创建BLL类库（业务层），DAL类库（DAL层），Model（实体数据层）类库和SQLHelper类库（数据库操作类）结果如图解夬方TireeLdyer''5咛项目 0 画 BLL DAL b 匣 Model t> SQLHelper P 暑WebApplication2. Model（数据实体模型）数据实体模式用于映射数据表或试图，该类包括custom表和department表的字段属性Departme ntModel.cs7&91a1216Qus inq wingi using using usingS

7、ystem；Sj/sten- Collections .Generic;SystemLinq;ssten.Text;Ssten .Th广e£(Jing-TE5k£；-namespace HodLIT亍引用public cldss DepartmentHodfl6个引用public int id < get; set; >S个引用public string dppartnamt? < get; set; >6于引用public string descrintion < get; set; r-* WVWWVTHWtaTW'i h Cust

8、omModel.esBusing uing using using usingSjjstpn;S jj s t e n. Co Unctions-Generic;System,Linq;Esten.Text;Sj/still. Threading .Tasks S牛101213仙1516 ir1819E namespace MadL25个引用m public class CustontlodelC6个引用public irit id * get; set; S个弓I用public string cnane get; set; 8个引用public int deprtlD get; set; 5

9、个引用public int age < get; set; 8个引用public string enane f get set; 3个引用public string passvord < get; set; >2.SQLHelper （数据库操作类）封装了数据操作的常用方法，包括数据库对象的建立，数据连接与释放，无返回值的查询操作，单反回值的查询操作以及多返回值得查询操作namespace SQLHelper<ie个引用public class SqlHeLppr<public static readonlu strinq conn str = "I&g

10、t;ata Source =.; Initial e Catalog=CustAHDDepa :User Ig驴：P归列叶4心町256065"/打开数据库¥H </suiwiar/ <returns></returns>0个引用public static SqlConnection OpenConnection()<SnlConnection conn = new SqlConnectionCcannstr);conn.Openf);return conn;/ <sunnary>/无返回值数据库操作/ </sumiika

11、r>/ <ipdrn nane="cmdText,X/paran>/ <pat*n njne-'*parHameter5*'></paran>/ <returns></returns>6个弓I用public static int ExecutetionQueryfstring cndTextr paramsSqlParameter parameters)<usin3 qlCcmnection conn = nw SqlCnnection(connstr)<conn.Open();return

12、 ExecuteNonQuer(conn, cndText. paraneters);>/ <sunnary>/单返回值査询操作/ </sumnary>/ <iparam name="cmdTexf'Xfparam?/ Cparan nane="parameters"></paran>/ <returns></returns>3个引用public static object ExecuteScalar(string cmdText» params SqlPraneter

13、 paraneters)<using CSqlConnpctlon conn = n?w SqlConnection(connstr)<conn.Openf);return EwecuteScal ar (conn, cmdText* paHnaneters):/ <umina ry>/參返回值数据操彳乍/ </supiRary>/ <param nane-"cmdText,'X/param>/ <parri nanpparaniptersX/pjram?/ff <returns></returns>

14、;5个引用public static OataTable ExecuteDataTable(string cndTextf parans SqlParanetetparameters)usin9 (沁她JSEiiSB conn =闻 SqlConnection<connstr)J<conn.Opn();return ExcutpDJtaTabl(conn, cmdlxt, pjranetes):1个引用public static int ExecutNonQuertj(SqLCunnectiun conn, string end Text v params S qlParamete

15、rf J iparametprs)<using (SqlConRnd end = conn.Crejt«Coniidrid()<cnd.ConR-andTet = cndTet;end -Parameter s7return cnd-ExecuteNonQuery();1个弓I用public static object ExecuteScalar(SqlConnection conn* string cmdTextv parans SqlParaneter parametersusing (SglCoimnand end = conn. CreatedonmaridC )

16、<c nd .Conn*.a ndTp*t - cndTeKt: emd-Parameters-AddRangefparameters): return end_ExecuteScalar();public static LdtdTdbl? ExecutebataTablefSqlCDnnection ccnn9 string cndText.parants SqlParampterl parameters)usingemu = conncredteconnandO)emdConnandText - cndText:emd .PoromaddRngo(pjrjnctore);using

17、( iulDd L j Adduter jdpter = new SqlD<atdAdapter (emd)DataTable dt = neu DataTabl?(); adapter,Fill(dt);return dt;nt/IfJ/<uiviiidiry>写入数掏室空酋判断</sunmaryi> namp=BBuialLipll></pAraiTi> <returns></returris>0个引用public static object ToDBUluetobject lue)*VSfWWWWW"fa.

18、Jreturn value = ni_LJ. ?_Lu色:value:/<s»umiidiry>取出数据空值判断</sunmaryi><pjrJu najnp=B"dbUalup'BX/parAui><rpturns></rp,tiirris>c个引用public static Qtijpct FronDBUalue(object dbUalue)ret urn dbUalue - DBNull.Ualue ? rull : dbUalue;3.DAL（数据操作层）CustomDAL.esus ing us

19、 ing us ing us ing us ing us ing us ing us ing usingSystem;System.Collect on s.Ge nericSystem.Data ;System.Data.SqlClie nt;System.L inq;System.Text;System.Threadi ng.Tasks;SQLHe per;Model;namespace DALpublic class CustomDAL I z<summary>III/数据模型转换III </summary></summary>/ vparam n a

20、me="row"x/param>/vretur ns></retur ns>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

21、"custom.ename = ( string ) row "ename" custom.password = ( string ) row "password"return custom;/ <summary>/验证用户/ </summary>/ <param n ame="custom"></param>/<retur ns></retur ns>public int CheckUser( CustomModel custom)stri ng en

22、 ame = custom.e name;string password = custom.password ; returnConvert .ToInt32 (SqlHelper .ExecuteScalar ("select * from T_Custom where ename=enameand password=password",new SqlParameter ("ename", enam©,new SqlParameter ("password" , password);/ <summary>/ 获

23、取记录数/ </summary>/ vretur ns></retur ns>public int GetRecordCounts ()return Convert .ToInt32 (SqlHelper .ExecuteScalar ("select count(*) from T_Custom");-/ <summary>/查询所有的数据/ </summary>/vretur ns>v/retur ns>public IEnumerable <CustomModel> ListAll ()Li

24、st <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>/ vparam n ame="id"

25、>v/param>/<retur ns></retur ns>public CustomModel GetByld(int id )DataTable dt = SqlHelper .ExecuteDataTable ("select * from T_Custom where id=id",-new SqlParameter ("id" , id );if ( dt.Rows.Count<= 0)return null ;elsereturn ToModel( dt.Rows 0);/ <summary&

26、gt;/ 根据departid查询记录/ </summary>/ vparam n ame="departld"></param>/vretur ns></retur ns>public DataTable GetByValueDepartld (int departld )return SqlHelper .ExecuteDataTable ("select * from T_Custom where departID=departId" ,new SqlParameter ("departld

27、" , departld );/ <summary>/根据id获取数据集合用于Gridview控件的数据绑定/ </summary>/ vparam n ame="id"></param>/vretur ns>v/retur ns>public DataTable GetByValueld (int id )return SqlHelper .ExecuteDataTable ("select * from T_Custom where id=id" new SqlParameter (&q

28、uot;id" , id );/vsummary>/根据查询记录_/v/summary>/vparam n ame="n ame">v/param>/vretur ns>v/retur ns>public DataTable GetByNamstri ngn am©return SqlHelper .ExecuteDataTable ("select * from T_Custom whereame=name" new SqlParameter ("name", nam©

29、;);/ <summary>/根据年龄查询记录/ </summary>/ vparam n ame="age">v/param>/vretur ns>v/retur ns>public DataTable GetByage( int age)returnSqlHelper .ExecuteDataTable ("select * from T_Custom where age=age"new SqlParameter ("age" , age);/ vsummary>/更新一条记录

30、* 改III <Isummary>III <param n ame="custom"></param> pubIic void Update( CustomModeI custom) SqIHeIper .ExecuteN on Query ("update T_Customsetame=c name,departlD=departlD,age=age,e name=e name,password=password where id=id" ("cname", ame)("departID

31、" , custom.departID )("age" , custom.age ) |("ename", custom.ename)("password" , custom.password )("id" , custom.id );new SqIParameter new SqIParameter new SqIParameter new SqIParameter new SqIParameter new SqIParameterIIIIIIIIIIII<summary>扌插入 条记录 *

32、土增<Isummary><param n ame="custom"><Iparam>pubIic void Insert (CustomModeI custom)SqIHeIper .ExecuteNonQuery ("INSERT INTOT_Custom(id,c name,departlD,age,e name,password)VALUES(id,c name,departlD,a ge,e name,password)",newSqIParameter,newSqIParameter,newSqIParame

33、ter,newSqIParameter,newSqIParameter,newSqIParameter("cname", ame) ("departID" , custom.departID ) ("age" , custom.age ) |("ename", custom.ename)("password" , custom.password ) ("id" , custom.id ); |IIIIIIIIIIII<summary>删除id=id的记录* 删&l

34、t;Isummary><param name="id"><Iparam> pubIic void DeIeteByld (int id )SqIHeIper .ExecuteN on Query ("deIete from T_Custom where id=id" SqIParameter ("id" , id );-newdepartme ntDAL .csusing System.Data ;using System.Data.SqICIie nt;using System.Linq;using S

35、ystem.Text;using System.Threading.Tasks;using SQLHeIper;using ModeI;using System;using System.CoIIections.Generic;namespace DALpublicclass Departme ntDAL/<summary>/数据模型的转换/</summary>/<param n ame="row"x/param>/<retur ns></retur ns>private DepartmentModel ToMod

36、el( DataRow row)Departme ntModel departme nt=new DepartmentModel ();department.id= ( int ) row"id"department.departname= ( string ) row "departname"department.description = ( string ) row "description"return department ;/ <summary>/ 获取记录数/ </summary>/<ret

37、ur ns></retur ns>public int GetRecordCounts ()returnConvert .ToInt32 (SqlHelper .ExecuteScalar ("select count(*) fromT_Department");/<summary>/根据id 获取数据 *查/</summary>/<param n ame="id"></param>/<retur ns></retur ns>public DepartmentMod

38、el GetById (int id )DataTable dt = SqlHelper .ExecuteDataTable ("select * from T_Department whereid=id",new SqlParameter ("id" , id );if(dt.Rows.Count <= 0)return null ;elsereturn ToModel( dt.Rows 0);/ <summary>/根据id获取数据集合用于Gridview控件的数据绑定/</summary>/<param n am

39、e="id"></param>/<retur ns></retur ns>public DataTable GetByValueId (int id )return SqlHelper .ExecuteDataTable("select * from T Departme nt where id=id",new SqlParameter ("id" , id );/<summary>/根据部门名称查询并返回记录集1/</summary>/<param n ame=

40、"depart name"x/param>/<retur ns></retur ns>public DataTable GetByNamgstri ngdepart name)return SqlHelper .ExecuteDataTable ("select * from T_Department where departname=departname",n ew SqlParameter ("depart name" , depart name);/ <summary>/更新 条记录 *

41、j改/ </summary>/<param n ame="departme nt"></param>public void Update( Departme ntModel departme nt)SqlHelper .ExecuteN on Query ("update T Departme nt setdepart name=depart name,descripti on=descripti on where id=id",new SqlParameter ("departname" , de

42、partment.departname ),new SqlParameter ("description" , department.description)new SqlParameter ("id" , department.id );/ <summary>/插入一条记录/ </summary>/<param n ame="departme nt"></param>public void Insert ( DepartmentModel department )SqlHelper .

43、ExecuteN on Query ("INSERT INTOT_Departme nt(id,depart name,descripti on )VALUES(id,depart name,descripti on)",new SqlParameter ("id" , department.id ),new SqlParameter ("departname" , department.departname ),new SqlParameter ("description" , department.descri

44、ption );/ <summary>III删除一条记录/ </summary>/<param n ame="id"></param>public void DeleteById (int id)newSqlHelper .ExecuteN on Query ("delete from T_Departme nt where id=id" SqIParameter ("id" , id);4. BLL(业务逻辑层)customBLL .csusing System;using Syste

45、m.Collections.Generic;using System.Data ;using System.Linq;using System.Runtime.InteropServices;using System.Text;using System.Threading.Tasks;using DALusing Model; | namespace BLLpublic class CustomBLLDAL.CustomDAL customDal = new CustomDAL);/ <summary>/ 检查登陆名及密码/ </summary>/ <param

46、n ame="custom"></param>/ <retur ns></retur ns>public bool CheckUser(CustomModel custom)if ( customDal.CheckUser (custom)=1)returntrue ;else1returnfalse ;/<summary>/获取记录数/</summary>/<retur ns></retur ns>public int RecordCo un t () retur ncustomDa

47、l.GetRecordCo unts ();/ <summary>III添加对象/ </summary>/vparam n ame="custom"></param>public void addUser( Model. CustomModel custom)customDal.Insert(custom);/ <summary>/ 根据departid值查询并返回记录|/ </summary>/<param n ame="id"></param>_|/<re

48、tur ns></retur ns>public DataTable GetBydepartId (int departid )return customDal.GetByValueDepartld (departid );/ <summary>/ 根据id值查询并返回记录/ </summary>/<param n ame="id"></param>/<retur ns></retur ns>public DataTable GetByValueId (int id )return c

49、ustomDal.GetByValueld (id );/ <summary>/根据id返回数据模型/ </summary>/<param n ame="id"></param>_|/<retur ns></retur ns>public CustomModel GetById (int id ) return customDal.GetById (id );/ <summary>/根据查询并返回记录/ </summary>/<param n ame="n ame

50、"></param>/<retur ns></retur ns>public DataTable GetByNamstri ngn am©|return customDal.GetByName ( nam©/ <summary>/根据年龄查询并返回记录/ </summary>/vparam n ame="age"></param>/vretur ns></retur ns>public DataTable GetByAge( int age)r

51、eturn customDal.GetByage (age);/ <summary>/根据id值删除记录/ </summary>/vparam n ame="id">v/param>public void DeleteByld (int id )customDal.DeleteByld (id );/ <summary>III 更新记录/ </summary>/ vparam n ame="custom"></param> public void Update( CustomM

52、odel custom) customDal.Update (custom);departme ntBLL .csusing System;using System.Collections.Generic;using System.Data ;using System.Linq;using System.Text;using System.Threading.Tasks;using DALusing Model; | namespace BLLpublic class Departme ntBLLDAL.DepartmentDAL departmentDal = new DepartmentD

53、AL();/ <summary>/ 获取记录数/ </summary>/ <retur ns></retur ns>public int RecordCo un t () Iretur n departme ntDal.GetRecordCo unts();/ <summary>/ 添加对象/ </summary>/ <param n ame="departme nt"></param>public void addDepartment ( Model. DepartmentMo

54、del department ) IdepartmentDal.lnsert(department );/ <summary>/ 根据id值查询并返回记录/ </summary>/ <param n ame="id"></param>/ <retur ns></retur ns>public DataTable GetByValueId (int id ) Ireturn departmentDal.GetByValueld(id );/ <summary>/ 根据id返回数据模型/ <

55、;/summary>/ vparam n ame="id"x/param>/ vretur ns></retur ns> publicDepartmentModel GetByld (int id )return departmentDal.GetByld (id );/ <summary>/ 根据查询并返回记录/ </summary>/ <param n ame="n ame"></param>/ <retur ns></retur ns>public DataTable GetBydepar