Sql Server分頁
最近不知道是不是頭暈還是怎樣，居然跑回去寫ASP了@@
雖然我剛出社會時，第一個碰觸的就是ASP，約莫過了半年，就開始轉換ASP.NET了，那時剛碰.net 1.1，覺得拉一個控制項，然後隨便寫一寫，一個功能就好了，過不了多久，.net 2.0出來了，這時用的是VS 2005，哇，功能更是齊全，分頁、排序只要在控制項上面設定一下，後端程式寫一寫，就可以達到功能，況且在配色方面，他也有預設的顏色讓你選擇，而且程式與美工部分沒有互相甘擾，算是還滿不錯的….
不過還是要回到我們的主題，這邊要講的是如何利用ASP+SQL SERVER達到分頁的功能，ASP的分頁，我想大家都會，這邊就不介紹了，而SQL SERVER的分頁，我想大家應該比較少用到，而我也上網搜尋了滿多，這邊就用程式來帶過。
首先我們建立一個算資料筆數的Store Procedure
CREATE procedure dbo.recordCount
(
@TableName nvarchar(100),--資料庫表名
@strWhere nvarchar(500),--查詢條件
@count int output---輸出值，記錄集總數
)
as
declare @sqlStr nvarchar(1000)
if @strWhere != ''
set @sqlStr = N'select @COUNT = count(id) from '+ @TableName +' where 1=1 ' + @strWhere
else
set @sqlStr = N'select @COUNT = count(id) from '+ @TableName
exec sp_executesql @sqlStr,N'@count int output',@count output
GO
(
@TableName nvarchar(100),--資料庫表名
@strWhere nvarchar(500),--查詢條件
@count int output---輸出值，記錄集總數
)
as
declare @sqlStr nvarchar(1000)
if @strWhere != ''
set @sqlStr = N'select @COUNT = count(id) from '+ @TableName +' where 1=1 ' + @strWhere
else
set @sqlStr = N'select @COUNT = count(id) from '+ @TableName
exec sp_executesql @sqlStr,N'@count int output',@count output
GO
接著撰寫處理分頁的Store Procedure
CREATE PROCEDURE dbo.sp_pageview
@tablename varchar(200) , --表名
@strGetFields varchar(200), --查詢列名
@PageIndex int , --頁碼
@pageSize int, --頁面大小
@strWhere varchar(100) , --查詢條件
@strOrder varchar(100) , --排序列名
@intOrder bit --排序類型 1為降序
AS
begin
declare @strSql varchar(500) --主語句
declare @strTemp varchar(100) --臨時變量
declare @strOrders varchar(50) --排序語句
declare @table varchar(70)
if @intOrder = 0
begin
--為0是升序
set @strTemp = '>(select max'
set @strOrders = ' order by '+@strOrder+' asc '
end
else
begin
--否則為降序
set @strTemp = '<(select min'
set @strOrders = ' order by '+@strOrder+' desc '
end
if @PageIndex =1 --第一列直接讀出記錄
begin
if @strWhere = ''
begin
set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' '+@strOrders
end
else
begin
set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+ ' from '+@tablename+' where '+@strWhere+' '+@strOrders
end
end
else
begin
set @strSql = 'select top'+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strOrder+' '+@strTemp+' ('+@strOrder+')'
+' from (select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+ ' '+@strOrders+ ') as tempTable ) '+@strOrders
if @strWhere != ' '
begin
set @strSql = 'select top '+str(@pageSize)+ ' '+@strGetFields+' from '+@tablename+ ' where '+@strOrder+ ' '+@strTemp+' ('+@strOrder+') '
+' from(select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strWhere+' ' +@strOrders+') as tempTable) and '+@strWhere+' '+@strOrders
end
end
exec(@strSql)
end
GO
@tablename varchar(200) , --表名
@strGetFields varchar(200), --查詢列名
@PageIndex int , --頁碼
@pageSize int, --頁面大小
@strWhere varchar(100) , --查詢條件
@strOrder varchar(100) , --排序列名
@intOrder bit --排序類型 1為降序
AS
begin
declare @strSql varchar(500) --主語句
declare @strTemp varchar(100) --臨時變量
declare @strOrders varchar(50) --排序語句
declare @table varchar(70)
if @intOrder = 0
begin
--為0是升序
set @strTemp = '>(select max'
set @strOrders = ' order by '+@strOrder+' asc '
end
else
begin
--否則為降序
set @strTemp = '<(select min'
set @strOrders = ' order by '+@strOrder+' desc '
end
if @PageIndex =1 --第一列直接讀出記錄
begin
if @strWhere = ''
begin
set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' '+@strOrders
end
else
begin
set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+ ' from '+@tablename+' where '+@strWhere+' '+@strOrders
end
end
else
begin
set @strSql = 'select top'+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strOrder+' '+@strTemp+' ('+@strOrder+')'
+' from (select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+ ' '+@strOrders+ ') as tempTable ) '+@strOrders
if @strWhere != ' '
begin
set @strSql = 'select top '+str(@pageSize)+ ' '+@strGetFields+' from '+@tablename+ ' where '+@strOrder+ ' '+@strTemp+' ('+@strOrder+') '
+' from(select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strWhere+' ' +@strOrders+') as tempTable) and '+@strWhere+' '+@strOrders
end
end
exec(@strSql)
end
GO
上面2個Store Procedure是我們的前置作業，接著這邊就要利用我們的主角了—ASP，我們要在ASP中呼叫SQL SERVER，來幫我達到分頁的效果
<!--#include file="../conn/conn.inc"-->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=big5" />
<script language="javascript">
function checkpage()
{
if(isNaN(document.fenye.page.value))
{
alert("跳轉頁碼請輸入數字！");
document.fenye.page.focus();
document.fenye.page.value='';
return false;
}
if(document.fenye.page.value=='')
{
alert("跳轉頁碼不能為空！");
document.fenye.page.focus();
document.fenye.page.value='';
return false;
}
}
</script>
<title>預存程序分頁</title>
<%
dim TableName,Page,TotalRs,PageNum,TotalPage,SearchChar,strGetFields,strOrder
'預存程序參數
TableName="allion_subject"
strGetFields=" subjectid,subjectname,subjectcontext "'字串開始結束加上空格
strOrder="subjectid"
PageNum=2
'搜索關鍵字
key=request("key")
'按什麼字段搜索
kind=request("kind")
'分頁?數
page=cint(request("page"))
trs=request("trs")
tpa=request("tpa")
'查詢條件判斷
if key<>"" then
SearchChar=" and "& kind &" like '%"&key&"%'"
SearchChar1=" "& kind &" like ''%"&key&"%''"'撰寫注意與預存程序中的where關鍵字間的間距，如果多條件，後面也要有間距
else
SearchChar=""
SearchChar1=""
'response.Write SearchChar1
'response.End()
end if
'返回總記錄數
Set MyComm=Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection=conn
'MyConStr是數據庫連接字串
MyComm.CommandText="recordCount"
'指定預存程序名稱
MyComm.CommandType=4
'表明這是一個預存程序
MyComm.Prepared=true
'要求將SQL命令先行編譯
'宣告參數
MyComm.Parameters.append MyComm.CreateParameter( "@TableName",200,1,500,tablename)
MyComm.Parameters.append MyComm.CreateParameter( "@strWhere",200,1,500,SearchChar)
MyComm.Parameters.append MyComm.CreateParameter( "@count",3,2)
MyComm.Execute
'取得輸出參數
TotalRs=MyComm.Parameters( "@count").value
Set MyComm=Nothing
'計算總頁數
if TotalRs mod 30 =0 then
TotalPage=TotalRs \30
else
TotalPage=TotalRs \30 + 1
end if
'頁碼合法判斷
if page="" or page<=0 then
page=1
end if
'page=48
'呼叫預存程序
sql="exec sp_pageview '"&TableName&"','"&strGetFields&"',"&page&","&PageNum&",'"&SearchChar1&"','"&strOrder&"',0"
response.Write sql
'response.End()
set rs=server.CreateObject("adodb.recordset")
rs.open sql,conn,1,1
%>
</head>
<body>
<table width="800" height="46" border="1" align="center">
<form action="list.asp" method="post">
<tr>
<td colspan="3">
關鍵字：<input type="text" name="key" />
<input type="radio" value="how" name="kind" />功能<input type="radio" checked="checked"
value="what" name="kind" />產品<input type="radio" value="ip" name="kind" />商家
<input type="submit" name="Submit" value="搜索" />
</td>
</tr>
</form>
</table>
<table width="800" height="46" border="1" align="center">
<tr>
<td height="35" align="center">
<strong>ID</strong></td>
<td align="center">
<strong>名稱</strong>
</td>
<td height="35" align="center">
<strong>內容</strong></td>
</tr>
<%
do while not rs.eof
%>
<tr>
<td height="30">
<%=rs("subjectid")%>
</td>
<td height="30">
<%=rs("subjectname")%>
</td>
<td height="30">
<%=rs("subjectcontext")%>
</td>
</tr>
<%
rs.movenext
loop
%>
</table>
<table width="800" height="46" border="1" align="center">
<form action="list.asp" method="post" name="fenye" onsubmit="return checkpage()">
<tr>
<td colspan="3" align="center">
共<%=TotalRs%>條
<%=PageNum%>
條/頁當前第<%=Page%>/<%=TotalPage%>頁
<%if TotalPage=1 or TotalPage=0 then
response.Write " 首頁上一頁 下一頁末頁"
else
%>
<%if page=1 then
response.Write" 首頁上一頁 <a href='list.asp?page="&page+1&"&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"'>下一頁</a> <a href='list.asp?page="&TotalPage&"&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"'>末頁</a>"
end if%>
<%if page>1 and page<TotalPage then
response.Write"<a href='list.asp?page=1&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"'>首頁</a> <a href='list.asp?page="&page-1&"&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"'>上一頁</a> <a href='list.asp?page="&page+1&"&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"'>下一頁</a> <a href='list.asp?page="&TotalPage&"&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"'>末頁</a>"
end if
end if
%>
<%if page=TotalPage then
response.Write"<a href='list.asp?page=1&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"'>首頁</a> <a href='list.asp?page="&page-1&"&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"'>上一頁</a> 下一頁末頁"
end if%>
轉到第<input id="gopage" maxlength="10" size="5" value="<%=page%>" name="page" />頁<input
id="submit123" type="submit" value="Go" name="submit123" /><input type="hidden" value="<%=TotalRs%>"
name="trs" /><input type="hidden" value="<%=TotalPage%>" name="tpa" />
<input type="hidden" name="keyword" value="<%=keyword%>" /><input type="hidden" name="kind"
value="<%=kind%>" /></td>
</tr>
</form>
</table>
</body>
</html>
<%
rs.close
set rs=nothing
conn.close
set conn=nothing
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=big5" />
<script language="javascript">
function checkpage()
{
if(isNaN(document.fenye.page.value))
{
alert("跳轉頁碼請輸入數字！");
document.fenye.page.focus();
document.fenye.page.value='';
return false;
}
if(document.fenye.page.value=='')
{
alert("跳轉頁碼不能為空！");
document.fenye.page.focus();
document.fenye.page.value='';
return false;
}
}
</script>
<title>預存程序分頁</title>
<%
dim TableName,Page,TotalRs,PageNum,TotalPage,SearchChar,strGetFields,strOrder
'預存程序參數
TableName="allion_subject"
strGetFields=" subjectid,subjectname,subjectcontext "'字串開始結束加上空格
strOrder="subjectid"
PageNum=2
'搜索關鍵字
key=request("key")
'按什麼字段搜索
kind=request("kind")
'分頁?數
page=cint(request("page"))
trs=request("trs")
tpa=request("tpa")
'查詢條件判斷
if key<>"" then
SearchChar=" and "& kind &" like '%"&key&"%'"
SearchChar1=" "& kind &" like ''%"&key&"%''"'撰寫注意與預存程序中的where關鍵字間的間距，如果多條件，後面也要有間距
else
SearchChar=""
SearchChar1=""
'response.Write SearchChar1
'response.End()
end if
'返回總記錄數
Set MyComm=Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection=conn
'MyConStr是數據庫連接字串
MyComm.CommandText="recordCount"
'指定預存程序名稱
MyComm.CommandType=4
'表明這是一個預存程序
MyComm.Prepared=true
'要求將SQL命令先行編譯
'宣告參數
MyComm.Parameters.append MyComm.CreateParameter( "@TableName",200,1,500,tablename)
MyComm.Parameters.append MyComm.CreateParameter( "@strWhere",200,1,500,SearchChar)
MyComm.Parameters.append MyComm.CreateParameter( "@count",3,2)
MyComm.Execute
'取得輸出參數
TotalRs=MyComm.Parameters( "@count").value
Set MyComm=Nothing
'計算總頁數
if TotalRs mod 30 =0 then
TotalPage=TotalRs \30
else
TotalPage=TotalRs \30 + 1
end if
'頁碼合法判斷
if page="" or page<=0 then
page=1
end if
'page=48
'呼叫預存程序
sql="exec sp_pageview '"&TableName&"','"&strGetFields&"',"&page&","&PageNum&",'"&SearchChar1&"','"&strOrder&"',0"
response.Write sql
'response.End()
set rs=server.CreateObject("adodb.recordset")
rs.open sql,conn,1,1
%>
</head>
<body>
<table width="800" height="46" border="1" align="center">
<form action="list.asp" method="post">
<tr>
<td colspan="3">
關鍵字：<input type="text" name="key" />
<input type="radio" value="how" name="kind" />功能<input type="radio" checked="checked"
value="what" name="kind" />產品<input type="radio" value="ip" name="kind" />商家
<input type="submit" name="Submit" value="搜索" />
</td>
</tr>
</form>
</table>
<table width="800" height="46" border="1" align="center">
<tr>
<td height="35" align="center">
<strong>ID</strong></td>
<td align="center">
<strong>名稱</strong>
</td>
<td height="35" align="center">
<strong>內容</strong></td>
</tr>
<%
do while not rs.eof
%>
<tr>
<td height="30">
<%=rs("subjectid")%>
</td>
<td height="30">
<%=rs("subjectname")%>
</td>
<td height="30">
<%=rs("subjectcontext")%>
</td>
</tr>
<%
rs.movenext
loop
%>
</table>
<table width="800" height="46" border="1" align="center">
<form action="list.asp" method="post" name="fenye" onsubmit="return checkpage()">
<tr>
<td colspan="3" align="center">
共<%=TotalRs%>條
<%=PageNum%>
條/頁當前第<%=Page%>/<%=TotalPage%>頁
<%if TotalPage=1 or TotalPage=0 then
response.Write " 首頁上一頁 下一頁末頁"
else
%>
<%if page=1 then
response.Write" 首頁上一頁 <a href='list.asp?page="&page+1&"&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"'>下一頁</a> <a href='list.asp?page="&TotalPage&"&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"'>末頁</a>"
end if%>
<%if page>1 and page<TotalPage then
response.Write"<a href='list.asp?page=1&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"'>首頁</a> <a href='list.asp?page="&page-1&"&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"'>上一頁</a> <a href='list.asp?page="&page+1&"&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"'>下一頁</a> <a href='list.asp?page="&TotalPage&"&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"'>末頁</a>"
end if
end if
%>
<%if page=TotalPage then
response.Write"<a href='list.asp?page=1&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"'>首頁</a> <a href='list.asp?page="&page-1&"&key="&key&"&kind="&kind&"&trs="&trs&"&tpa="&tpa&"'>上一頁</a> 下一頁末頁"
end if%>
轉到第<input id="gopage" maxlength="10" size="5" value="<%=page%>" name="page" />頁<input
id="submit123" type="submit" value="Go" name="submit123" /><input type="hidden" value="<%=TotalRs%>"
name="trs" /><input type="hidden" value="<%=TotalPage%>" name="tpa" />
<input type="hidden" name="keyword" value="<%=keyword%>" /><input type="hidden" name="kind"
value="<%=kind%>" /></td>
</tr>
</form>
</table>
</body>
</html>
<%
rs.close
set rs=nothing
conn.close
set conn=nothing
%>
上述提供的程式與操作，僅供大家參考。
沒有留言:
張貼留言