分享

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

 唐伯龙 2011-06-06

步步为营 .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的设计.

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多