.NET三层架构解析：数据库设计 -建站知识-成都网站建设|郫县网页制作|微信开发|微信商城-中原动力

建站知识

.NET三层架构解析：数据库设计

来源：网络作者：网络

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