调用分页存储过程的函数

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

就这么简单,其实大家都有自己的分页存储代码,写这么一个专对此存储过程进行调用的函数的还是不错的

转载于:https://www.cnblogs.com/kasafuma/archive/2005/08/04/207481.html