共享一段 ASPX数据存取流程代码

发布时间：2023-07-04 14:27:28 所属栏目：Asp教程来源：

导读：CREATE PROCEDURE GoalerPageSp

@IntPageSize int,

@IntCurrPage int,

@strFields nvarchar(2000),

@strTable varchar(200),

@strWhere varchar(800),

@strOrderType varchar(200),

@st

CREATE PROCEDURE GoalerPageSp

@IntPageSize int,

@IntCurrPage int,

@strFields nvarchar(2000),

@strTable varchar(200),

@strWhere varchar(800),

@strOrderType varchar(200),

@strKeyField varchar(50)

AS

SET NOCOUNT ON

DECLARE @tmpSQL nvarchar(4000)--存放动态SQL语句

DECLARE @tmpWhere varchar(800)

DECLARE @tmpAndWhere varchar(800)--用于第N(>1)页上边的查询条件

DECLARE @tmpOrder varchar(200)

DECLARE @tmpD_X varchar(2)

DECLARE @tmpMin_MAX varchar(3)

--设置条件--

IF @strWhere IS NULL OR RTRIM(@strWhere)=''

BEGIN --没有查询条件

SET @tmpWhere=''

SET @tmpAndWhere=''

END

ELSE

BEGIN --有查询条件

SET @tmpWhere=' WHERE '+@strWhere

SET @tmpAndWhere=' AND '+@strWhere

END

--设置排序--

IF @strOrderType != 0

BEGIN--倒序

SET @tmpD_X = '<'

SET @tmpMin_MAX = 'MIN'

SET @tmpOrder=' ORDER BY ' +@strKeyField+ ' DESC'

END

ELSE

BEGIN

SET @tmpD_X = '>'

SET @tmpMin_MAX = 'MAX'

SET @tmpOrder=' ORDER BY ' +@strKeyField+ ' ASC'

END

--SQL查询--

IF @IntCurrPage=1

Set @tmpSQL='SELECT TOP '+CAST(@IntPageSize AS VARCHAR)+' '+@strFields+' FROM '+@strTable+' '+@tmpWhere+' '+@tmpOrder

ELSE

SET @tmpSQL='SELECT TOP '+CAST(@IntPageSize AS VARCHAR)+' '+@strFields+' FROM '+@strTable+' WHERE ('+@strKeyField+' '+@tmpD_X+' (SELECT '+@tmpMin_MAX+'('+@strKeyField+') FROM (SELECT TOP '+CAST(@IntPageSize*(@IntCurrPage-1) AS VARCHAR)+' '+@strKeyField+' FROM '+@strTable+' '+@tmpWhere+' '+@tmpOrder+') AS T))'+@tmpAndWhere+' '+@tmpOrder

EXEC(@tmpSQL)

GO

调用方法：

IntPageSize=20

strTable=" [TableName] " '数据表名称

strFields=" Field1,Field2,Field3,Field4 " '需要读取的列名

strKeyField="Field1" '主键：这里假设Field1为主键

strWhere="" '条件：FieldA='b'

strOrderType=1 '排序方式：1为倒序，0为顺序

CurrPage=Request.QueryString("Page")

IF(CurrPage<>"" And Isnumeric(CurrPage))THEN

CurrPage=CLNG(CurrPage)

IF(CurrPage<1)THEN CurrPage=1

ELSE

CurrPage=1

END IF

IF strWhere<>"" THEN

tmpWhere=" WHERE "&strWhere

ELSE

tmpWhere=""

END IF

IF(SESSION("RecCount")<>"")THEN

IF(SESSION("strWhere")<>strWhere)THEN

RecCount=Conn.Execute("SELECT COUNT("&strKeyField&") FROM "&strTable&tmpWhere)(0)

SESSION("RecCount")=RecCount

SESSION("strWhere")=strWhere

ELSE

RecCount=SESSION("RecCount")

END IF

ELSE

RecCount=Conn.Execute("SELECT COUNT(*) FROM "&strTable&tmpWhere)(0)

SESSION("RecCount")=RecCount

SESSION("strWhere")=strWhere

END IF

IF(RecCount MOD IntPageSize <>0)THEN

IntPageCount=INT(RecCount/IntPageSize)+1

ELSE

IntPageCount=RecCount/IntPageSize

END IF

SET Cmd=Server.CreateObject("Adodb.Command")

Cmd.CommandType=4

SET Cmd.ActiveConnection=Conn

Cmd.CommandText="GoalerPageSp"

Cmd.Parameters.Append Cmd.CreateParameter("@IntPageSize",4,1,4,IntPageSize)

Cmd.Parameters.Append Cmd.CreateParameter("@IntCurrPage",4,1,4,CurrPage)

Cmd.Parameters.Append Cmd.CreateParameter("@strFields",200,1,2000,strFields)

Cmd.Parameters.Append Cmd.CreateParameter("@strTable",200,1,200,strTable)

Cmd.Parameters.Append Cmd.CreateParameter("@strWhere",200,1,800,strWhere)

Cmd.Parameters.Append Cmd.CreateParameter("@strOrderType",4,1,4,strOrderType)

Cmd.Parameters.Append Cmd.CreateParameter("@strKeyField",200,1,50,strKeyField)

SET RS=Cmd.Execute()

IF RecCount<1 THEN

Response.Write("没有记录")

ELSE

GetRecord=RS.GetRows(IntPageSize)

For i=0 To Ubound(GetRecord,2)

Response.Write(GetRecord(0,i),GetRecord(1,i),GetRecord(2,i)) '...输出内容

NEXT

GetRecord=Null

END IF

SET RS=NOTHING

有用的朋友请自己慢慢调试吧，总记录是用ASP来取的，存储在SESSION里边，如果每次都统计一次总记录，将会非常费时，当然，如果你想在存储过程里来取总记录和总页数然后返回也是可以的，下边是代码：

--获取记录总数--

SET @tmpSQL='SELECT @getRecordCounts=COUNT('+@strKeyField+') FROM '+@strTable+@tmpWhere

EXEC sp_executesql @tmpSQL,N'@getRecordCounts int output',@getRecordCounts OUTPUT

--获取总页数--

SET @tempFolatNumber=@getRecordCounts%@IntPageSize

IF @getRecordCounts<=@IntPageSize

SET @getPageCounts=1

ELSE

BEGIN

IF @tempFolatNumber != 0

SET @getPageCounts=(@getRecordCounts/@IntPageSize)+1

ELSE

SET @getPageCounts=(@getRecordCounts/@IntPageSize)

END

别忘了返回定义参数：

@getRecordCounts int output,--返回总记录

@getPageCounts int output--返回总页数

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!