自定义分页存储过程及VB.NET 调用

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

自定义分页存储过程 及VB.NET 调用

猜你在找的VB相关文章

自定义分页存储过程及VB.NET 调用