蛙蛙推荐:如何做一个高效的ASP数据库操作程序

<!--

一般情况下我们做的ASP数据库程序都是ADO+ACCESS,并且都是使用一些查询字符串加记录集来操作数据库,最多也只使用了connection和recordset两个对象以及它们的几个常用的属性和方法,其实ADO的使用远不仅这些,我们还有command对象和Parameters对象没有用呢,而这两个对象用好了会提高你整个ASP程序的性能.

我这里写了一个歌词管理程序,用的是sqlserver数据库和存储过程实现的,(这里没有用参数化查询,也正是为了演示ado对sqlserver和存储过程的用法).

希望大家能从我的示例代码中学到新的东西,嘿嘿.

注意:我把示例代码里面的asp边界符(就是尖括号加上一个百分号的那个标识)替换成了全角中文的尖括号,因为很多论坛会过滤这个符号,再你复制后记着把它替换成英文半角的.

-->

<!--

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]

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]

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]

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]

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]

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]

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

过程check_song,通过@song_name变量来查询数据表中是否有重复的记录，如果有则设定@state这个输入参数的值为1,该值直接影响到addnew过程的运行

create proc check_song

@song_name char(40),

@state int output

begin

if exists(select song_name from wawa_song

where song_name=@song_name)

set @state = 1

else

set @state = 0

end

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

过程insert_song

CREATE proc insert_song

@song_name char(40),

@song_content varchar(4000),

@song_author char(20)

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

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS OFF

CREATE PROCEDURE [p_song] AS

select * from wawa_song order by song_id desc

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS OFF

create proc p_wawa_song

@id int

select song_id,song_name,song_author,song_content from wawa_song where song_id=@id

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

-->

《％

Dim conn,strconn

Set conn = Server.CreateObject("ADODB.Connection")

'如果你的数据库的连接字符串和下面一句不符合,可以修改下句代码来适合你的数据库配置

strconn="Driver={sql server};server=192.168.0.110;database=song1;uid=sa;pwd=sa;"

conn.Open strconn

％》

《％

Dim fileName,postion

fileName = Request.ServerVariables("script_name")

postion = InstrRev(fileName,"/")+1

fileName = Mid(fileName,postion)

％》

《％

Function wawaHTML(result)

if not isNull(result) then

result = Server.HtmlEncode(result)

result = replace(result,vbcrlf,"<br>")

result = replace(result," "," ")

result = replace(result,chr(9),"    ") 'Tab键

wawaHTML=result

else

wawaNHTML= "没有内容"

end if

end Function

％》

《％

Dim rs_wawa

set rs_wawa=server.createobject("adodb.recordset")

rs_wawa.open "p_song",conn,1,1,4

dim pages,allpages,page

pages=10

rs_wawa.pageSize=pages

allPages = rs_wawa.pageCount

page = clng(Request("page"))

if isempty(page) or page<0 or page=0 then page=1

if page >rs_wawa.pagecount then page=rs_wawa.pagecount

if not(rs_wawa.bof and rs_wawa.eof) then

rs_wawa.AbsolutePage = page

end if

％》

《％

if request("action")="view" then

if request("id")<>"" then

dim id

id=clng(Trim(Request.QueryString("id")))

set cm = Server.CreateObject("ADODB.Command")

Set cm.ActiveConnection = conn

cm.CommandText = "p_wawa_song"

cm.CommandType = 4

set p = cm.Parameters

p.Append cm.CreateParameter("@id",3,1,,id)

dim rs_song

set rs_song=server.createobject("adodb.recordset")

rs_song.open cm,,1,1

else

response.Write("没有传递参数")

response.End()

end if

％》

《％

if not isempty(request.Form("submit")) then

call addnew

end if

sub addnew

on error resume next

song_author=request("song_author")

song_content=request("song_content")

song_name=request("song_name")

set cm = Server.CreateObject("ADODB.Command")

Set cm.ActiveConnection = conn

cm.CommandText = "insert_song"

cm.CommandType = 4

set p = cm.Parameters

p.Append cm.CreateParameter("@song_name",130,1,60,song_name)

p.append cm.CreateParameter("@song_content",202,1,4000,song_content)

p.append cm.CreateParameter("@song_author",130,1,20,song_author)

cm.Execute

if err.number<>0 then

response.write err.description

else

response.write "ddd"

response.end

end if

set cm = nothing

response.Redirect fileName

end sub

％》

<tr>

《％if request("action")="add" then％》

</tr>

</tr>

</tr>

</tr>

</tr>

</form>

</table>

《％end if％》

《％if request("action")="view" then％》

</tr>

<td width="50%">《％=rs_song("song_author")％》</td>

</tr>

</tr>

<td colspan="2">《％=wawaHTML(cstr(rs_song("song_content")))％》</td>

</tr>

</table>

《％end if％》

《％if request("action")="hello" or request("action")="" then％》

<td colspan="2"><div align="center">欢迎使用蛙蛙歌词管理系统</div></td>

</tr>

落英缤纷时节。<br>

晓来谁染枫林醉？点点都是离人泪.<br>

活着，就是快乐！<br>

自信，就是美丽！<br>

有人爱，就是幸福。 <br>

<br>

</td>

</tr>

</table>

《％end if％》

</td>

《％

if not(rs_wawa.bof and rs_wawa.eof) then

While Not rs_wawa.EOF and pages>0

％》

</tr>

《％

rs_wawa.MoveNext

pages=pages-1

Wend

rs_wawa.close:set rs_wawa=nothing

else

％》

<td>还没有添加歌曲呢</td>

</tr>

</table>

《％

end if

conn.close:Set conn = Nothing

％》

《％if page<>1 then％》

《％end if％》

《％if page<>allpages then ％》

《％ End If ％》

当前第《％=page％》几页   共《％=allpages％》页

</center>

</td>

</tr>

</table>