【IT168 技术】Net三层架构解析系列文章：
.NET三层架构解析之SQLHelper设计
.NET三层架构解析之Model四种设计方式
要开发用户管理系统,我们首先要了解需求,现在就举一个简单需求,用户表,假设有两种角色用一个字段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]
[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
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
@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
