自定义分页存储过程 及VB.NET 调用
发布时间：2020-12-16 22:20:27 所属栏目：大数据 来源：网络整理
导读：ALTER PROCEDURE [dbo].[GetRecordFromPage] @tblName nvarchar(255),-- 表名 @fldName nvarchar(255),-- 字段名 @PageSize int = 10,-- 页尺寸 @PageIndex int = 1,-- 页码 @OrderType int = 0,-- 设置排序类型,非0 值则降序 @IsCount int = 0,-- 返回记录
|
ALTER PROCEDURE [dbo].[GetRecordFromPage]
@tblName nvarchar(255),-- 表名 @fldName nvarchar(255),-- 字段名 @PageSize int = 10,-- 页尺寸 @PageIndex int = 1,-- 页码 @OrderType int = 0,-- 设置排序类型,非0 值则降序 @IsCount int = 0,-- 返回记录总数,非0 值则返回 @PageCount int output,--总页数，作为返回值 @strWhere nvarchar(255) = '' -- 查询条件(注意: 不要加where) AS declare @i int,@RecordCount int declare @strSQL nvarchar(4000) -- 主语句 declare @strTmp nvarchar(1000) -- 临时变量 declare @strOrder nvarchar(500) -- 排序类型 if @OrderType != 0 begin set @strTmp = '<(select min' set @strOrder = ' order by [' + @fldName + '] desc' end else begin set @strTmp = '>(select max' set @strOrder = ' order by [' + @fldName +'] asc' end set @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)' + @strOrder if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder if @PageIndex = 1 begin set @strTmp = '' if @strWhere != '' set @strTmp = ' where (' + @strWhere + ')' set @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + ']' + @strTmp + ' ' + @strOrder end exec (@strSQL) if @IsCount != 0 set @strSQL = 'select @i=count(' + @fldName + ') from [' + @tblName + '] where ' + @strWhere + ' ' exec sp_executesql @strSQL,N'@i int output',@i output set @RecordCount = @i /*得到总页数，注意使用convert先转换整型为浮点型，防止小数部分丢失*/ set @PageCount = ceiling (convert( float,@i)/@PageSize) return @i VB调用的过程，和使用 Private Sub PageSelect(ByVal tblName As String,ByVal fldName As String,ByVal PageSize As Integer, ByVal PageIndex As Integer,ByVal OrderType As Integer,ByVal IsCount As Integer, ByVal strWhere As String) Try Dim conn As New SqlConnection conn.ConnectionString = ConfigurationManager.ConnectionStrings("wingsbook").ToString() Dim myCommand As New SqlDataAdapter Dim ds As DataSet myCommand = New SqlDataAdapter("GetRecordFromPage",conn) myCommand.SelectCommand.CommandType = CommandType.StoredProcedure myCommand.SelectCommand.Parameters.Clear() myCommand.SelectCommand.Parameters.Add("@tblName",SqlDbType.NVarChar,255).Value = tblName myCommand.SelectCommand.Parameters.Add("@fldName",255).Value = fldName myCommand.SelectCommand.Parameters.Add("@PageSize",SqlDbType.Int).Value = PageSize myCommand.SelectCommand.Parameters.Add("@PageIndex",SqlDbType.Int).Value = PageIndex myCommand.SelectCommand.Parameters.Add("@OrderType",SqlDbType.Int).Value = OrderType myCommand.SelectCommand.Parameters.Add("@IsCount",SqlDbType.Int).Value = IsCount Dim PageCount As SqlParameter = myCommand.SelectCommand.Parameters.Add("@PageCount",SqlDbType.Int) PageCount.Direction = ParameterDirection.Output Dim CountS As SqlParameter = myCommand.SelectCommand.Parameters.Add("@CountS",SqlDbType.Int) CountS.Direction = ParameterDirection.ReturnValue myCommand.SelectCommand.Parameters.Add("@strWhere",255).Value = strWhere ds = New DataSet() myCommand.Fill(ds,"tableset") Me.DataGridView1.DataSource = ds.Tables(0) keepnum = PageCount.Value keepcount = CountS.Value countsum.Text = CountS.Value & " (总页数：" & PageCount.Value & ")" Catch ex As Exception MessageBox.Show(ex.Message) End Try End Sub 调用： PageSelect("Com_Customer","id",100,1,"1=1")
使用
declare
@i
int
,
@PageCount
int
exec
@i
=
GetRecordFromPage
'Customer_info'
,
'id'
,
1000
,
2
,
1
,
1
,
@PageCount
output
,
'id>14000'
select
@PageCount
as
[out]
print
@i
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |