调用分页存储过程的函数
CREATE PROCEDURE sp_page
@tb varchar(50), --表名
@col varchar(50), --按该列来进行分页
@coltype int, --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(800),--查询条件
@pages int OUTPUT --总页数
AS
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
END
SET @sql='SELECT @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize AS varchar)+
') FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT--计算总页数
IF @orderby=0
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'>(SELECT MAX('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
@col+' DESC'
IF @page=1--第一页
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+
@where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
EXEC(@sql)
GO
这是一个不错的分页存储过程,由于每次在页面中调用比较麻烦,就写了个针对此调用存储过程的Module :
Module MdlCommon
Public DBSet As DataSet '查询得到的记录集
Public ErrorMsg As String '存放错误信息
Public Pages As Integer
Public Function GetResult(ByVal tb As String, ByVal col As String, ByVal collist As String, ByVal condition As String, ByVal coltype As Integer, ByVal orderby As Integer, ByVal Page As Integer, ByVal Pagesize As Integer, ByRef errMsg As String) As DataSet
Dim cnn As SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand
Dim adpt As SqlClient.SqlDataAdapter
Dim rst As New DataSet
errMsg = ""
Try
cnn = New SqlClient.SqlConnection("data source=(local);initial catalog=Northwind;user id=sa;pwd='hyaocuk!'")
cmd = New SqlClient.SqlCommand("sp_page", cnn)
cmd.CommandType = CommandType.StoredProcedure
'给存储过程的参数赋值
Dim sp_temp As SqlClient.SqlParameter
sp_temp = cmd.Parameters.Add("@tb", SqlDbType.VarChar, 50)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = tb
sp_temp = cmd.Parameters.Add("@col", SqlDbType.VarChar, 50)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = col
sp_temp = cmd.Parameters.Add("@collist", SqlDbType.VarChar, 800)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = collist
sp_temp = cmd.Parameters.Add("@condition ", SqlDbType.VarChar, 800)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = condition
sp_temp = cmd.Parameters.Add("@pagesize", SqlDbType.Int)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = Pagesize
sp_temp = cmd.Parameters.Add("@page", SqlDbType.Int)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = Page
sp_temp = cmd.Parameters.Add("@orderby", SqlDbType.Int)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = orderby
sp_temp = cmd.Parameters.Add("@coltype", SqlDbType.Int)
sp_temp.Direction = ParameterDirection.Input
sp_temp.Value = coltype
sp_temp = cmd.Parameters.Add("@pages", SqlDbType.Int)
sp_temp.Direction = ParameterDirection.Output
adpt = New SqlClient.SqlDataAdapter
adpt.SelectCommand = cmd
adpt.Fill(rst)
GetResult = rst
Pages = cmd.Parameters("@pages").Value
Catch ex As Exception
errMsg = ex.Message
Finally
rst = Nothing
cnn = Nothing
End Try
End Function
End Module
调用时候:
Private Sub dgSortReport_ItemCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles dgSortReport.ItemCreated
If e.Item.ItemType = ListItemType.Pager Then
Dim pager As TableCell = CType(e.Item.Controls(0), TableCell)
Dim i As Integer
For i = 0 To pager.Controls.Count Step 2
Dim o As Object = CType(pager.Controls(i), Object)
If TypeOf (o) Is LinkButton Then
Dim h As LinkButton = CType(o, LinkButton)
h.Text = "[ " + h.Text + " ]"
Else
Dim l As Label = CType(o, Label)
l.Text = "第" + l.Text + "页"
End If
Next i
End If
End Sub
'绑定datagrid的函数
Protected Sub CreateDatasource(ByVal CurrentPage As Integer)
Dim tb, col, collist, condition As String
'查询的表名
tb = "Orders"
'排序列的列名
col = "OrderID"
'返回的列名列表
collist = "EmployeeID,RequiredDate"
'查询的表件
condition = "OrderID>0"
Dim coltype, orderby, pagesize, page As Integer
coltype = 0
orderby = 0
pagesize = 10
page = CurrentPage
DBSet = GetResult(tb, col, collist, condition, coltype, orderby, page, pagesize, ErrorMsg)
dgSortReport.VirtualItemCount = Pages
dgSortReport.DataSource = DBSet.Tables(0).DefaultView
dgSortReport.DataBind()
End Sub
Private Sub dgSortReport_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles dgSortReport.PageIndexChanged
dgSortReport.CurrentPageIndex = e.NewPageIndex
' 页码值是从零开始的( 所以要加一)
CreateDatasource(e.NewPageIndex + 1)
End Sub
就这么简单,其实大家都有自己的分页存储代码,写这么一个专对此存储过程进行调用的函数的还是不错的