当前位置:编程学习 > asp >>

步步为营 .NET三层架构解析 二、数据库设计

要开发用户管理系统,我们首先要了解需求,现在就举一个简单需求,用户表,假设有两种角色用一个字段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的设计.

 

补充:Web开发 , ASP.Net ,
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,