动物尾巴的样子像什么:步步为营 .NET三层架构解析 二、数据库设计-程序开发-红黑联盟

步步为营 .NET三层架构解析 二、数据库设计
 
 
文章录入：7747.Net    责任编辑：7747.Net  13 
 【字体：小 大】
 
要开发用户管理系统,我们首先要了解需求,现在就举一个简单需求,用户表,假设有两种角色用一个字段departID来判断,管理员和员工,
我们要先建一个用户表custom和一个部门表department:
view sourceprint?CREATE TABLE [dbo].[custom](      [id] [int] IDENTITY(1,1) NOT NULL,      [cname] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,      [departID] [int] NOT NULL,      [age] [int] NOT NULL,      [ename] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,      [password] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,   CONSTRAINT [PK_custom] PRIMARY KEY CLUSTERED   (      [id] ASC  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  ) ON [PRIMARY];     CREATE TABLE [dbo].[department](      [id] [int] IDENTITY(1,1) NOT NULL,      [departname] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,      [description] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,   CONSTRAINT [PK_department] PRIMARY KEY CLUSTERED   (      [id] ASC  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  ) ON [PRIMARY] 建完数据库表后,开始写存储过程,插入一条数据:
view sourceprint?CREATE PROCEDURE [dbo].[spInsertCustom]  @cname nvarchar(50),  @ename nvarchar(50),  @age int,  @departID int,  @password nvarchar(50)  AS  BEGIN  insert into custom(cname,departID,age,ename,password) values (@cname,@departID,@age,@ename,@password)  END     RETURN @@Identity view sourceprint?create PROCEDURE [dbo].[spInsertDepartment]  @departname nvarchar(50),  @description nvarchar(50)  AS  BEGIN      insert into department(departname,description)values(@departname,@description)  END     RETURN @@Identity 现建两个更新一条数据的存储过程:
view sourceprint?CREATE PROCEDURE [dbo].[spupdatecustom]   @id int,  @cname nvarchar(50),  @departID int,  @age int,  @ename nvarchar(50),  @password nvarchar(50)  AS  BEGIN      update          custom       set     cname = @cname,      departID = @departID,      age = @age,      ename = @ename,      password = @password      where id = @id  END  COMMIT TRAN view sourceprint?create procedure spupdatedepart  (  @departname nvarchar(50),  @description nchar(10),  @id int )  as UPDATE [dbo].[department]     SET [departname] = @departname        ,[description] = @departname   WHERE id=@id 再新建两个取出所有用户的存储过程:view sourceprint?CREATE PROCEDURE [dbo].[spGetcustom]         AS  BEGIN      select * from custom order by id desc  END view sourceprint?create PROCEDURE [dbo].[spGetAlldepartment]     AS  BEGIN      select * from department   END 再新建一个根据ID取出一条数据的存储过程:view sourceprint?CREATE PROCEDURE [dbo].[spGetcustomer]  @id int AS  BEGIN   select * from custom where id = @id  END 现建一个根据部门名取部门ID的存储过程:view sourceprint?create PROCEDURE [dbo].[spGetdepartmenter]  @departname nvarchar(50)  AS  BEGIN      select * from department where departname = @departname  END 再建两个根据ID删除数据的存储过程:view sourceprint?create PROCEDURE [dbo].[spDeletecustom]  @id int AS  BEGIN       delete custom where id = @id  END view sourceprint?CREATE PROCEDURE spdeletedepart  @id int AS  BEGIN      delete department where id = @id  END  GO 数据库设计就建好了,这只是一个简单的示例.欢迎拍砖.下次讲解SQLHelper的设计. 摘自红色黑客联盟(www.7747.net) 原文：http://www.7747.net/kf/201103/86545.html