怎样用SQL Server和c语言或C#编写个应用程序？比如说电话本

NEW0620IRENE 2009-06-06 05:07:55

create table groups
(
g_id int identity(1,1) primary key,
g_name varchar(20),
up int
)

insert into groups values('显示所有组',1)
insert into groups values('显示所有用户',2)

select * from users

-- 组的存储过程
create procedure add_group
(
@g_name varchar(20),
@g_up int
)
as
begin
insert into dbo.groups (g_name,up) values(@g_name,@g_up)
end

select * from groups

alter procedure del_group
(
@id int
)
as
begin
declare @i int
select @i=g_id from groups where g_id = @id
delete groups where g_id = @i
declare @j int
select @j=count(*) from groups where up = @i
declare @h int
set @h =1
while(@h<=@j)
begin
declare @g int
select top(@h) @g=g_id from groups where up = @i
delete groups where g_id = @g
exec del_group1 @g
set @h = @h+1
end
end

create procedure del_group1
(
@g_id int
)
as
begin
declare @j int
select @j=count(*) from groups where up = @g_id
declare @h int
set @h =1
while(@h<=@j)
begin
declare @g int
select top(@h) @g=g_id from groups where up = @g_id
delete groups where g_id = @g
exec del_group1 @g
set @h = @h+1
end
end

create proc serach_all_group
as
begin
Select * from groups where g_name<>'显示所有组' and g_name<>'显示所有用户'
end

create proc search_one_group
as
begin
Select * from groups where up=1 and g_name<>'显示所有组'
end

alter proc Amend_GroupUP
(

@g_up int,
@g_id int
)
as
begin
update groups set up = @g_up where g_id = @g_id
end

select * from groups

delete groups where g_id <> 1 and g_id <>2

create procedure make_group
(
@g_new_name varchar(20),
@g_id int
)
as
begin
update dbo.groups set g_name=@g_new_name where g_id = @g_id
end

create procedure into_group--组里面新建组
(
@g_new_name varchar(20),
@g_id int,
@up int
)
as
begin
insert into dbo.groups (g_name,up) values(@g_new_name,@up)
end

create proc serch_g -- 根据组的名字查组的所有信息
(
@gname varchar(20)
)
as
begin
select * from groups where g_name=@gname
end

create proc serch_g_id --根据组的上级编号查组的信息
(
@id int
)
as
begin
select * from groups where up=@id
end

create proc search_group
(
@g_id int
)
as
begin
select * from groups where g_id = @g_id
end

-- 组的存储过程

create table users
(
id int identity(1,1) primary key,
g_id int,
g_name varchar(20),
u_name varchar(20),
email varchar(50),
address varchar(50),
phone varchar(50),
fax varchar(50),
mobil varchar(50),
date datetime,
remark varchar(50),
img image
)

--联系人的存储过程
create procedure add_person -- 添加联系人
(
@g_id int,
@g_name varchar(20),
@u_name varchar(20),
@email varchar(50),
@address varchar(50),
@phone varchar(50),
@fax varchar(50),
@mobil varchar(50),
@date datetime,
@remark varchar(50),
@img image
)
as
begin
insert into dbo.users (g_id,g_name,u_name,email,address,phone,fax,mobil,date,remark,img)
values(@g_id,@g_name,@u_name,@email,@address,@phone,@fax,@mobil,@date,@remark,@img)
end
Go

create trigger update_users_gname
on users
for insert
as
update users
set g_name = '未分组'
where g_name ='显示所有用户'

create procedure make_person -- 添加联系人
(

@g_id int,
@g_name varchar(20),
@u_name varchar(20),
@email varchar(50),
@address varchar(50),
@phone varchar(50),
@fax varchar(50),
@mobil varchar(50),
@date datetime,
@remark varchar(50),
@img image,
@id int
)
as
begin

update dbo.users set g_name = @g_name where id = @id
update dbo.users set u_name = @u_name where id = @id
update dbo.users set email = @email where id = @id
update dbo.users set address = @address where id = @id
update dbo.users set phone = @phone where id = @id
update dbo.users set fax = @fax where id = @id
update dbo.users set mobil = @mobil where id = @id
update dbo.users set date = @date where id = @id
update dbo.users set remark = @remark where id = @id
update dbo.users set g_id = @g_id where id = @id
update dbo.users set img = @img where id = @id
end
Go

create procedure del_person -- 添加联系人
(
@id int
)
as
begin
delete users where id=@id
end
Go

create proc search_u_info -- 查询联系人
(
@u_name varchar(20)
)
as
begin
select * from users where u_name=@u_name
end

create proc search_g_u_all
(
@g_id varchar(20)
)
as
begin
select * from users where g_id=@g_id
end

alter proc searchs
(
@u_name varchar(20)
)
as
begin
select * from users where u_name like @u_name
end

create proc search_id_all
(
@g_id int
)
as
begin
select * from users where g_id = @g_id
end

create proc serac_users
(
@id int
)
as
begin
select * from users where id = @id
end

create proc make_u_g_name
(
@id int,
@g_id int,
@g_name varchar(20)
)
as
begin
update users set g_id = @g_id where id = @id
update users set g_name = @g_name where id =@id
end

create proc search_all_users
as
begin
select * from users
end

create trigger update_users
on groups
for delete
as
update users
set g_name = '未分组'
where g_name in (select g_name from deleted)

--联系人的存储过程

create table logins
(
Lid int identity(1,1) primary key,
username varchar(20),
password varchar(20)
)

create proc Search_LoginUser
(
@LoginName varchar(20),
@LoginPWD varchar(20)
)
as
begin
select * from logins where username = @LoginName and password = @LoginPWD
end

create proc add_login
(
@username varchar(20),
@password varchar(20)
)
as
begin
insert into logins (username,password) values(@username,@password)
end

alter proc CopyUser
(

@UserID int,
@GroupID int,
@UserGroupName varchar(20)
)
as
begin
insert into users(g_id,g_name,u_name,email,address,phone,fax,mobil,date,remark,img)
select g_id,g_name,u_name,email,address,phone,fax,mobil,date,remark,img from users where id = @UserID

update users set g_id = @GroupID where id = @UserID
update users set g_name = @UserGroupName where id = @UserID
end

...全文