明经CAD社区

 找回密码
 注册

扫一扫,访问微社区

QQ登录

只需一步,快速开始

查看: 5869|回复: 8

[Dvbbs] [改造DVBBS83]升级你的7.1SQL版到8.3SQL版

[复制链接]
发表于 2010-4-15 18:42 | 显示全部楼层 |阅读模式
动网的7.1版开放了SQL版本供大家免费使用,但现在已经是8.3版本了,怎么才能也用8.3版本的SQL版。由于8.3版本比较新,D版肯定是找不到的,但D版的8.2版是找得到的,所以大家可以升级到8.3版的SQL后,用8.2版的储存过程,再经过以下改造就可以用了。1.dispbbs.asp改成:
  1. If IsSqlDataBase=1 And IsBuss=1 And Skin=0 Then
  2. 'mccad edit 将8.3存储方式改成8.2,因为8.3SQL版还没有D版出现  
  3.   Set Cmd = Dvbbs.iCreateObject("ADODB.Command")
  4.   Set Cmd.ActiveConnection=conn
  5.   Cmd.CommandText="dv_dispbbs"
  6.   Cmd.CommandType=4
  7.   Cmd.Parameters.Append cmd.CreateParameter("@boardid",3)
  8.   Cmd.Parameters.Append cmd.CreateParameter("@rootid",3)
  9.   Cmd.Parameters.Append cmd.CreateParameter("@pagenow",3)
  10.   Cmd.Parameters.Append cmd.CreateParameter("@pagesize",3)
  11.   Cmd.Parameters.Append cmd.CreateParameter("@totalusetable",200,1,20)
  12.   Cmd("@boardid")=Dvbbs.BoardID
  13.   Cmd("@rootid")=ReplyID
  14.   Cmd("@pagenow")=G_CurrentPage
  15.   Cmd("@pagesize")=G_ItemsPerPage
  16.   Cmd("@totalusetable")=TotalUsetable
  17.   Set Rs=Cmd.Execute
  18.   If Not Rs.EoF Then
  19.    G_ItemList=Rs.GetRows(-1)
  20.   Else
  21.    Dvbbs.AddErrCode(29)
  22.   End If
  23.   Rs.close()
  24.   Set Rs=Nothing
  25.   Set Cmd =  Nothing
  26. '  Dim mypage
  27. '  Set mypage=new Pager
  28. '  'If Not IsObject(Conn) Then ConnectionDatabase
  29. '  mypage.getconn=conn '得到数据库连接
  30. '  mypage.pagesize=G_ItemsPerPage '定义分页每一页的记录数
  31. '  mypage.TableName=TotalUsetable '要查询的表名
  32. '  mypage.Tablezd=sqlfields
  33. '  mypage.KeyName="announceid"
  34. '  mypage.OrderType=0
  35. '  mypage.PageWhere=sqlfieldswhere
  36. '  mypage.GetStyle =1
  37. '  Set Rs=mypage.getrs()
  38. '  If Not Rs.EoF Then
  39. '   G_ItemList=Rs.GetRows(-1)
  40. '  Else
  41. '   Rs.close():Set Rs=Nothing:Dvbbs.AddErrCode(29)
  42. '  End If
  43. '  Rs.close():Set Rs=Nothing
  44. '  'Set Cmd =  Nothing
  45. Else

2.index.asp改成:
  1. If IsSqlDataBase=1 And IsBuss=1 Then
  2. 'mccad edit 将8.3存储方式改成8.2,因为8.3SQL版还没有D版出现  
  3.   Set Cmd = Dvbbs.iCreateObject("ADODB.Command")
  4.   Set Cmd.ActiveConnection=conn
  5.   Cmd.CommandText="dv_TopicList"
  6.   Cmd.CommandType=4  
  7.   Cmd.Parameters.Append cmd.CreateParameter("@boardid",3)
  8.   Cmd.Parameters.Append cmd.CreateParameter("@pagenow",3)
  9.   Cmd.Parameters.Append cmd.CreateParameter("@pagesize",3)
  10.   Cmd.Parameters.Append cmd.CreateParameter("@topicmode",3)
  11.   Cmd.Parameters.Append cmd.CreateParameter("@inConditions",200,1,250)
  12.   Cmd.Parameters.Append cmd.CreateParameter("@inOrder",3)
  13.   Cmd.Parameters.Append cmd.CreateParameter("@inSort",3)
  14.   Cmd.Parameters.Append cmd.CreateParameter("@totalrec",3,2,4)
  15.   
  16.   Cmd("@boardid")=Dvbbs.BoardID
  17.   Cmd("@pagenow")=Page
  18.   Cmd("@pagesize")=Cint(Dvbbs.Board_Setting(26))
  19.   Cmd("@topicmode")=TopicMode
  20.   Cmd("@inConditions")=SQLQuery
  21.   Cmd("@inOrder")=OrderId
  22.   Cmd("@inSort")=SortId
  23.   Set Rs=Cmd.Execute
  24.   If Not Rs.EoF Then
  25.    Topic=Rs.GetRows(-1)
  26.   Else
  27.    Topic=Null
  28.   End If
  29.   Rs.close()
  30.   Set Rs=Nothing
  31.   Count = Cmd("@totalrec")
  32.   Set Cmd =  Nothing
  33.   'Dim mypage
  34.   'Set mypage=new Pager
  35.   'mypage.getconn=conn '得到数据库连接
  36.   'mypage.pagesize=Cint(Dvbbs.Board_Setting(26)) '定义分页每一页的记录数
  37.   'mypage.TableName="Dv_Topic" '要查询的表名
  38.   'mypage.Tablezd=sqlfields
  39.   'mypage.KeyName=OrderField
  40.   'mypage.OrderType=OrderType
  41.   'mypage.PageWhere=sqlfieldswhere
  42.   'mypage.GetStyle =1
  43.   'Set Rs=mypage.getrs()
  44.   'If Not (Rs.EoF And Rs.Bof) Then
  45.   ' Topic=Rs.GetRows(-1)
  46.   'Else
  47.   ' Topic=Null
  48.   'End If
  49.   'Count = mypage.int_totalRecord
  50.   'Rs.close()
  51.   'Set Rs=Nothing
  52. Else

3.dv_dispbbs 储存过程:
  1. CREATE PROCEDURE [dv_Dispbbs]
  2. @boardid int=0,
  3. @rootid int=0,
  4. @pagenow int=1,     
  5. @pagesize int=1,
  6. @totalusetable nvarchar(20)='dv_bbs1'
  7. AS
  8. set nocount on
  9. declare @int_top int
  10. declare @int_begin int
  11. declare @str_sql nvarchar(500)
  12. if @pagenow>1
  13. begin
  14.   select @int_top=(@pagenow-1)*@pagesize
  15.   SET @str_sql ='SELECT @int_begin = Max(announceid ) FROM (Select Top '+str(@int_top)+'  announceid From '+@totalusetable+'  where  RootID='+str(@rootid)+' and Boardid='+str(@boardid)+' Order By Announceid ) as t'
  16.   
  17.   exec sp_executesql @str_sql,N'@int_begin int output ',@int_begin output
  18.   set @str_sql='select AnnounceID,UserName,Topic,dateandtime,body,Expression,ip,RootID,signflag,isbest,PostUserid,layer,isagree,GetMoneyType,IsUpload,Ubblist,LockTopic,GetMoney,UseTools,PostBuyUser,ParentID,FlashId From '+@totalusetable+' where RootID='+str(@rootid)+' and announceid>'+str(@int_begin)+'  and Boardid='+str(@boardid)+' Order By Announceid'
  19.   set rowcount @pagesize
  20.   exec sp_executesql @str_sql
  21.   set nocount off
  22.   return
  23. end
  24. else
  25. begin
  26. set rowcount @pagesize
  27. set @str_sql='Select AnnounceID,UserName,Topic,dateandtime,body,Expression,ip,RootID,signflag,isbest,PostUserid,layer,isagree,GetMoneyType,IsUpload,Ubblist,LockTopic,GetMoney,UseTools,PostBuyUser,ParentID,FlashId From '+@totalusetable+' where  RootID='+str(@rootid)+' and Boardid='+str(@boardid)+' Order By Announceid'
  28. exec sp_executesql @str_sql
  29. return
  30. end
  31. GO
复制代码
4.dv_TopicList储存过程:CREATE PROCEDURE [dv_TopicList]
@boardid int=1,
@pagenow int=1, --当前页数            
@pagesize int=1, --定义每页面帖子数目
@topicmode int=0, --专题
@inConditions varchar(250)=' ',
@inOrder int=0,
@inSort int=0,
@totalrec int output --SET @TotalRec=@@ROWCOUNT
AS
SET nocount on
DECLARE @int_topnum int
DECLARE @int_timenum int
DECLARE @var_times varchar(5000)
DECLARE @OrderField varchar(20)
DECLARE @SortStr varchar(5)
DECLARE @strSQL nvarchar(4000)
DECLARE @Compare varchar(1)
DECLARE @nRet int
Declare @Compare1 nvarchar(20)
IF @inOrder=0
SET @OrderField='LastPostTime'
ELSE IF @inOrder=1
SET @OrderField='TopicId'
ELSE IF @inOrder=2
SET @OrderField='hits'
ELSE IF @inOrder=3
SET @OrderField='child'
ELSE
SET @OrderField='LastPostTime'

IF @inSort=0
BEGIN
SET @SortStr='DESC'
SET @Compare = '<'
Set @Compare1='Min'
END
ELSE
BEGIN
SET @SortStr='ASC'
SET @Compare = '>'
Set @Compare1='Max'
END


IF @pagenow>1
IF @topicmode>0
  BEGIN
   SELECT @int_timenum=(@pagenow-1) * @pagesize
   --SET ROWCOUNT @int_timenum
   SET @strSQL='SELECT @var_times ='+@Compare1+'(' + @OrderField  + ')  FROM (Select Top '+str(@int_timenum)+' ' + @OrderField + ' From Dv_Topic WHERE mode=@3 And boardID=@2 AND istop = 0 ' + @inConditions + 'ORDER BY ' + @OrderField + ' ' + @SortStr +') as t'  
   EXEC sp_executesql @strSQL,N'@var_times varchar(5000) output,@2 int,@3 int',@var_times output,@2=@boardID,@3=@topicmode

   SET ROWCOUNT @pagesize
   SET @strSQL='SELECT TopicID,boardid,title,postusername,postuserid,dateandtime,child,hits,votetotal,lastpost,lastposttime,istop,isvote,isbest,locktopic,Expression,TopicMode,Mode,GetMoney,GetMoneyType,UseTools,IsSmsTopic,HideName FROM dv_topic WHERE mode=@3 AND boardID=@2 AND istop = 0 AND ' + @OrderField + @Compare + ' @1 ' + @inConditions + ' ORDER BY ' + @OrderField + ' ' + @SortStr
   EXEC sp_executesql @strSQL,N'@1 varchar(5000),@2 int,@3 int',@1=@var_times,@2=@boardID,@3=@topicmode

   SET @strSQL='SELECT @nRet=COUNT(1) FROM Dv_Topic WHERE mode=@3 AND boardID=@2 AND istop=0 ' + @inConditions
   EXEC sp_executesql @strSQL,N'@nRet int output , @2 int,@3 int',@nRet output ,@2=@boardID,@3=@topicmode
   SELECT @totalrec=@nRet

   SET nocount OFF
   RETURN
  END
ELSE --@topicmode
  BEGIN
   SELECT @int_timenum=(@pagenow-1) * @pagesize
   --SET ROWCOUNT @int_timenum
   --SET @strSQL='SELECT @var_times=' + @OrderField + '  FROM Dv_Topic WHERE boardID=@2 AND istop=0 ' + @inConditions +' ORDER BY ' + @OrderField + ' ' + @SortStr
   SET @strSQL='SELECT @var_times ='+@Compare1+'(' + @OrderField  + ')  FROM (Select Top '+str(@int_timenum)+' ' + @OrderField + ' From Dv_Topic WHERE boardID=@2 AND istop=0 ' + @inConditions +' ORDER BY ' + @OrderField + ' ' + @SortStr +') as t'  
   EXEC sp_executesql @strSQL,N'@var_times varchar(5000) output,@2 int',@var_times output,@2=@boardID

   SET ROWCOUNT @pagesize
   SET @strSQL='SELECT TopicID,boardid,title,postusername,postuserid,dateandtime,child,hits,votetotal,lastpost,lastposttime,istop,isvote,isbest,locktopic,Expression,TopicMode,Mode,GetMoney,GetMoneyType,UseTools,IsSmsTopic,HideName FROM dv_topic WHERE boardID=@2 AND istop = 0 AND ' + @OrderField + @Compare + ' @1 ' + @inConditions + ' ORDER BY ' + @OrderField + ' ' + @SortStr
   EXEC sp_executesql @strSQL,N'@1 varchar(5000),@2 int',@1=@var_times,@2=@boardID

   SET @strSQL='SELECT @nRet=COUNT(1) FROM Dv_Topic WHERE boardID=@2 AND istop=0 ' + @inConditions
   EXEC sp_executesql @strSQL,N'@nRet int output ,@2 int',@nRet output , @2=@boardID
   SELECT @totalrec=@nRet

   SET nocount OFF
   RETURN
  END
ELSE --pagenow
IF @topicmode>0
  BEGIN
   SET ROWCOUNT @pagesize
   SET @strSQL='SELECT TopicID,boardid,title,postusername,postuserid,dateandtime,child,hits,votetotal,lastpost,lastposttime,istop,isvote,isbest,locktopic,Expression,TopicMode,Mode,GetMoney,GetMoneyType,UseTools,IsSmsTopic,HideName FROM Dv_topic WHERE mode=@3 AND boardID=@2 AND istop =
0 ' + @inConditions + ' ORDER BY ' + @OrderField + ' ' + @SortStr
   EXEC sp_executesql @strSQL,N'@2 int,@3 int',@2=@boardID,@3=@topicmode

   SET @strSQL='SELECT @nRet=COUNT(1) FROM Dv_Topic WHERE mode=@3 And boardID=@2 AND istop=0 ' + @inConditions
   EXEC sp_executesql @strSQL,N'@nRet int output,@2 int,@3 int',@nRet output,@2=@boardID,@3=@topicmode
   SELECT @totalrec=@nRet
  END
ELSE --topicmode
  BEGIN
   SET ROWCOUNT @pagesize
   SET @strSQL='SELECT TopicID,boardid,title,postusername,postuserid,dateandtime,child,hits,votetotal,lastpost,lastposttime,istop,isvote,isbest,locktopic,Expression,TopicMode,Mode,GetMoney,GetMoneyType,UseTools,IsSmsTopic,HideName FROM Dv_topic WHERE boardID=@2 AND istop=0 ' + @inConditions + ' ORDER BY ' + @OrderField + ' ' + @SortStr
   EXEC sp_executesql @strSQL,N'@2 int',@2=@boardID

   SET @strSQL='SELECT @nRet=COUNT(TopicID) FROM Dv_Topic WHERE boardID=@2 AND istop=0 ' + @inConditions
   EXEC sp_executesql @strSQL,N'@nRet int output,@2 int',@nRet output,@2=@boardID
   SELECT @totalrec=@nRet
  END

/****** 对象:  StoredProcedure [dv_disp]    脚本日期: 02/26/2008 15:59:30 ******/
SET ANSI_NULLS ON
GO
发表于 2010-4-15 22:08 | 显示全部楼层
官方来是8.3SQL牐优化了不少存储过程么,可能还有增加
发表于 2010-4-21 17:21 | 显示全部楼层
严重关注中,您在如下部分的重色部分,8.3版的SQL从哪儿来呢?可以下载到吗?
动网的7.1版开放了SQL版本供大家免费使用,但现在已经是8.3版本了,怎么才能也用8.3版本的SQL版。
由于8.3版本比较新,D版肯定是找不到的,但D版的8.2版是找得到的,所以大家可以升级到8.3版的SQL后,用8.2版的储存过程,再经过以下改造就可以用了。
发表于 2010-4-21 17:53 | 显示全部楼层
在您的提示之下,今天下午我成功升级为8.2sql版,嘿嘿~~~无比感谢,,若能得到您的进一步指点升级为8。3那就完美了~~~
 楼主| 发表于 2010-4-24 17:04 | 显示全部楼层

实际上SQL版与MSACCESS版是一样的程序,只是用的数据库不是而已。

发表于 2010-11-23 17:19 | 显示全部楼层
学习了~~
发表于 2011-1-4 00:07 | 显示全部楼层
发表于 2014-7-4 22:37 | 显示全部楼层
chaodna 发表于 2011-1-4 00:07

谢谢分享!        
发表于 2017-10-30 11:10 | 显示全部楼层
回帖是一种美德!感谢楼主的无私分享 谢谢
您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|CAD论坛|CAD教程|CAD下载|联系我们|关于明经|明经通道 ( 粤ICP备05003914号  
©2000-2017 明经通道 版权所有 本站代码,在未取得本站及作者授权的情况下,不得用于商业用途

GMT+8, 2018-1-23 22:08 , Processed in 0.262611 second(s), 29 queries , Gzip On.

Powered by Discuz! X3.3

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表