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

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

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

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。

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

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