datagridview视窗软件分页存储语句

CREATE proc cprk_cc

@page int,@pagesize int,@str char(200),@xs int
as
set @xs=(@page - 1) * @pagesize + 1
 set @str='select top 1 id from  (select top   '+cast(@xs as varchar)+' id from cprk ORDER BY id) a ORDER BY a.id'
exec (@str)
GO

 

 Public Sub initl_cprc()
        TC_Data()
        Dim strSQL As String = "select count(id) from cprk"
        Dim cmd As SqlCommand = New SqlCommand(strSQL, cn)
        sum = cmd.ExecuteScalar()
        cn.Close()
        cn.Dispose()
        ' Label1.Text = sum
        InitDataSet_cprc()
    End Sub
    Private Sub InitDataSet_cprc()
        pageSize = 5
        nMax = sum '//总记录数

        pageCount = (nMax) / (pageSize) '  //计算出总页数
        Dim ys As Double = nMax Mod pageSize
        ' MsgBox(ys)注意此处自动四舍五入，要特别单独处理
        If nMax Mod pageSize <= 4 Then
            pageCount = pageCount + 1
        End If
        page = 1 ' //当前页数从1开始
        nCurrent = 0 '  //当前记录数从0开始
        ' Label2.Text = pageCount
        LoadData_cprc()
    End Sub
    Public Sub LoadData_cprc()
        '第三步
        ds.Clear()
        ds.Dispose()
        '改用存储语句
        ' Dim strSQL As String = "SELECT TOP 1 id FROM (SELECT TOP " & Integer.Parse((page - 1)) * pageSize + 1 & "  id    FROM cprc ORDER BY id) DERIVEDTBL ORDER BY id DESC"
        TC_Data()
        Dim id_3 As Integer
        Dim cmd_3 As SqlCommand = New SqlCommand()
        cmd_3.CommandType = CommandType.Text
        cmd_3.CommandText = "cprk_cc " & page & "," & pageSize & "," & "''" & "," & "''"
        cmd_3.Connection = cn
        Dim myReader As SqlDataReader
        myReader = cmd_3.ExecuteReader()
        If myReader.Read() Then
            id_3 = Integer.Parse(myReader("id"))
        End If
        cn.Close()
        cn.Dispose()
        TC_Data()
        '改用存储语句
        'strSQL = "select top " & pageSize & " * from cprk where id>='" & id_3 & "' order by id"

        sda = New SqlDataAdapter("cprk_cc1 " & pageSize & "," & id_3 & ",''", cn)
        sda.Fill(ds, "cprc")

        cn.Close()
        cn.Dispose()

        'End If
        '  ds_str1.ds = ds
     
        dgv_cprc.AutoGenerateColumns = False
        dgv_cprc.DataSource = ds.Tables("cprc").DefaultView

    End Sub
    'Public Sub cprc_ll()
    '  TC_Data()
    '  ds.Dispose()
    '  ds.Clear()
    ' Dim strSQL As String = "select id, ddh, hm, cpmc, rcrq, bcrc, rcjs, mxmz, hjmz, mxjz,hjjz,  bm, zb, ww, jdrq, jdr from cprc"
    '    sda = New SqlDataAdapter(strSQL, cn)
    '    sda.Fill(ds, "cprc")
    '    dgv_cprc.AutoGenerateColumns = False
    '   dgv_cprc.DataSource = ds.Tables("cprc").DefaultView

    ' End Sub

    Private Sub 首张_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 首张.Click
        If bt = "成品入仓" Then
            page = 1
            LoadData_cprc()
        End If
    End Sub

    Private Sub 上张_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 上张.Click

        page = page - 1
        If bt = "成品入仓" Then
            If (page <= 0) Then
                page = page + 1
                'Label7.Text = "已经是第一页，请点击下一页查看！"
                Return
            Else
                ' Label7.Text = ""
                ' Label1.Text = ""
                LoadData_cprc()
            End If
        End If

    End Sub

    Private Sub 下张_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 下张.Click
        page = page + 1
        If bt = "成品入仓" Then
            If (page > pageCount) Then
                page = page - 1
                ' Label7.Text = "已经是最后一页，请点击上一页查看！"
                Return
            Else
                ' Label7.Text = ""
                LoadData_cprc()
            End If
        End If

    End Sub

    Private Sub 末张_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 末张.Click
        If bt = "成品入仓" Then
            'Label7.Text = "已经是最后一页，请点击上一页查看！"
            page = pageCount
            LoadData_cprc()
        End If

    End Sub