<!--
蛙蛙推荐:如何做一个高效的ASP数据库操作程序
一般情况下我们做的ASP数据库程序都是ADO+ACCESS,并且都是使用一些查询字符串加记录集来操作数据库,最多也只使用了connection和recordset两个对象以及它们的几个常用的属性和方法,其实ADO的使用远不仅这些,我们还有command对象和Parameters对象没有用呢,而这两个对象用好了会提高你整个ASP程序的性能.
我这里写了一个歌词管理程序,用的是sqlserver数据库和存储过程实现的,(这里没有用参数化查询,也正是为了演示ado对sqlserver和存储过程的用法).
希望大家能从我的示例代码中学到新的东西,嘿嘿.
注意:我把示例代码里面的asp边界符(就是尖括号加上一个百分号的那个标识)替换成了全角中文的尖括号,因为很多论坛会过滤这个符号,再你复制后记着把它替换成英文半角的.
-->
<!-- 数据库脚本 -->
<!-- 先在sqlserver里新建个数据库song然后在查询分析器里选择这个数据库,赋值下面的t-sql代码执行批查询,最后把这个页放在虚拟目录下,并把其中的数据库连接字符串修改成适合你的数据库配置的字符串,运行本页就可以了 -->
<!--
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[check_song]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[check_song]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[insert_song]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[insert_song]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_song_list]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_song_list]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_wawa_song]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_wawa_song]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[wawa_song]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[wawa_song]
GO

CREATE TABLE [dbo].[wawa_song] (
[song_id] [int] IDENTITY (1, 1) NOT NULL ,
[song_name] [char] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[song_content] [varchar] (4000) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[song_author] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[author_id] [int] NULL
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/*
过程check_song,通过@song_name变量来查询数据表中是否有重复的记录，如果有则设定@state这个输入参数的值为1,该值直接影响到addnew过程的运行
*/
create proc check_song
@song_name char(40),
@state int output
as
begin
if exists(select song_name from wawa_song
where song_name=@song_name)
set @state = 1
else
set @state = 0
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/*
过程insert_song
*/
CREATE proc insert_song
@song_name char(40),
@song_content varchar(4000),
@song_author char(20)
as
begin
declare @state int
exec check_song @song_name,@state output
if @state = 0
begin
begin tran
insert into wawa_song(song_name,song_content,song_author) values (@song_name,@song_content,@song_author)
commit tran
raiserror('%s添加成功！',16,1,@song_name)
end
else
begin
raiserror ('用户名%s已存在！',16,1,@song_name)
return
end
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [p_song] AS
select * from wawa_song order by song_id desc
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

create proc p_wawa_song
@id int
as
select song_id,song_name,song_author,song_content from wawa_song where song_id=@id
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-->
<!-- /数据库脚