PageRenderTime 26ms CodeModel.GetById 15ms app.highlight 9ms RepoModel.GetById 1ms app.codeStats 0ms

/Visual Studio 2008/VBWinFormDataGridView/DataGridViewPaging/MainForm.vb

#
Visual Basic | 108 lines | 77 code | 15 blank | 16 comment | 0 complexity | 30a458c72f80a67d4b2cc914a8d51b65 MD5 | raw file
  1'********************************* Module Header **********************************\
  2' Module Name:  DataGridViewPaging.vb
  3' Project:      VBWinFormDataGridView
  4' Copyright (c) Microsoft Corporation.
  5' 
  6' This sample demonstrates how to page data in the  DataGridView control;
  7' 
  8' This source is subject to the Microsoft Public License.
  9' See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL.
 10' All other rights reserved.
 11
 12' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND,
 13' EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED
 14' WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
 15'**********************************************************************************/
 16
 17Imports System.Data.SqlClient
 18
 19Namespace VBWinFormDataGridView.DataGridViewPaging
 20
 21    Public Class MainForm
 22        Private PageSize As Integer = 30
 23        Private CurrentPageIndex As Integer = 1
 24        Private TotalPage As Integer
 25        Private connstr As String = "Persist Security Info=False;" _
 26        + "Integrated Security=SSPI;" _
 27        + "Initial Catalog=Northwind;" _
 28        + "server=localhost"
 29        Private conn As SqlConnection
 30        Private adapter As SqlDataAdapter
 31        Private command As SqlCommand
 32
 33        Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 34            Me.conn = New SqlConnection(connstr)
 35            Me.adapter = New SqlDataAdapter()
 36
 37            ' Get total count of the pages
 38            Me.GetTotalPageCount()
 39            Me.dataGridView1.ReadOnly = True
 40
 41            ' Load the first page of data
 42            Me.dataGridView1.DataSource = GetPageData(1)
 43        End Sub
 44
 45        Private Sub GetTotalPageCount()
 46            command.CommandText = "Select Count(OrderID) From Orders"
 47            Try
 48                conn.Open()
 49                Dim rowCount As Integer = CType(command.ExecuteScalar(), Integer)
 50                Me.TotalPage = rowCount / PageSize
 51                If rowCount Mod PageSize > 0 Then
 52                    Me.TotalPage += 1
 53                End If
 54            Finally
 55                conn.Close()
 56            End Try
 57        End Sub
 58
 59        Private Function GetPageData(ByVal page As Integer) As DataTable
 60            Dim dt As DataTable = New DataTable()
 61            If page = 1 Then
 62                command.CommandText = "Select Top " + PageSize + " * From Orders Order By OrderID"
 63            Else
 64                Dim lowerPageBoundary = (page - 1) * PageSize
 65                command.CommandText = "Select Top " + PageSize _
 66                + " * From Orders " _
 67                + " WHERE OrderID NOT IN " _
 68                + " (SELECT TOP " + lowerPageBoundary _
 69                + " OrderID From Orders Order By OrderID) " _
 70                + " Order By OrderID"
 71
 72            End If
 73            Try
 74                Me.conn.Open()
 75                Me.adapter.SelectCommand = command
 76                Me.adapter.Fill(dt)
 77            Finally
 78                conn.Close()
 79            End Try
 80            Return dt
 81        End Function
 82
 83        Private Sub toolStripButtonFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles toolStripButtonFirst.Click
 84            Me.CurrentPageIndex = 1
 85            Me.dataGridView1.DataSource = GetPageData(Me.CurrentPageIndex)
 86        End Sub
 87
 88        Private Sub toolStripButtonPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles toolStripButtonPrev.Click
 89            If Me.CurrentPageIndex > 1 Then
 90                Me.CurrentPageIndex -= 1
 91                Me.dataGridView1.DataSource = GetPageData(Me.CurrentPageIndex)
 92            End If
 93        End Sub
 94
 95        Private Sub toolStripButtonNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles toolStripButtonNext.Click
 96            If Me.CurrentPageIndex < Me.TotalPage Then
 97                Me.CurrentPageIndex += 1
 98                Me.dataGridView1.DataSource = GetPageData(Me.CurrentPageIndex)
 99            End If
100        End Sub
101
102        Private Sub toolStripButtonLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles toolStripButtonLast.Click
103            Me.CurrentPageIndex = TotalPage
104            Me.dataGridView1.DataSource = GetPageData(Me.CurrentPageIndex)
105        End Sub
106    End Class
107
108End Namespace