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