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

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

2011-03-22 19:15 by spring yang, 3313 visits, 网摘, 收藏, 编辑

要开发用户管理系统,我们首先要了解需求,现在就举一个简单需求,用户表,假设有两种角色用一个字段departID来判断,管理员和员工,

我们要先建一个用户表custom和一个部门表department:

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]

建完数据库表后,开始写存储过程,插入一条数据:

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

create PROCEDURE [dbo].[spInsertDepartment]

@departname nvarchar(50),

@description nvarchar(50)

AS

BEGIN

insert into department(departname,description)values(@departname,@description)

END

RETURN @@Identity

现建两个更新一条数据的存储过程:

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

create procedure spupdatedepart

(

@departname nvarchar(50),

@description nchar(10),

@id int

)

as

UPDATE [dbo].[department]

SET [departname] = @departname

,[description] = @departname

WHERE id=@id

再新建两个取出所有用户的存储过程:

CREATE PROCEDURE [dbo].[spGetcustom]

AS

BEGIN

select * from custom order by id desc

END

create PROCEDURE [dbo].[spGetAlldepartment]

AS

BEGIN

select * from department

END

再新建一个根据ID取出一条数据的存储过程:

CREATE PROCEDURE [dbo].[spGetcustomer]

@id int

AS

BEGIN

select * from custom where id = @id

END

现建一个根据部门名取部门ID的存储过程:

create PROCEDURE [dbo].[spGetdepartmenter]

@departname nvarchar(50)

AS

BEGIN

select * from department where departname = @departname

END

再建两个根据ID删除数据的存储过程:

create PROCEDURE [dbo].[spDeletecustom]

@id int

AS

BEGIN

delete custom where id = @id

END

CREATE PROCEDURE spdeletedepart

@id int

AS

BEGIN

delete department where id = @id

END

GO

数据库设计就建好了,这只是一个简单的示例.欢迎拍砖.

下次讲解SQLHelper的设计.