要開發(fā)用戶管理系統(tǒng),我們首先要了解需求,現(xiàn)在就舉一個簡單需求,用戶表,假設有兩種角色用一個字段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]
|
建完數(shù)據(jù)庫表后,開始寫存儲過程,插入一條數(shù)據(jù):
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
|
現(xiàn)建兩個更新一條數(shù)據(jù)的存儲過程:
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
|
再新建一個根據(jù)ID取出一條數(shù)據(jù)的存儲過程:
CREATE PROCEDURE [dbo].[spGetcustomer]
|
@id
int
|
AS
|
BEGIN
|
?
select * from custom where id = @id
|
END
|
現(xiàn)建一個根據(jù)部門名取部門ID的存儲過程:
create PROCEDURE [dbo].[spGetdepartmenter]
|
@departname nvarchar(50)
|
AS
|
BEGIN
|
????
select * from department where departname = @departname
|
END
|
再建兩個根據(jù)ID刪除數(shù)據(jù)的存儲過程:
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
|
數(shù)據(jù)庫設計就建好了,這只是一個簡單的示例.歡迎拍磚.
下次講解SQLHelper的設計.
更多文章、技術交流、商務合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
