PageRenderTime 44ms CodeModel.GetById 19ms RepoModel.GetById 0ms 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. ' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND,
  12. ' EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED
  13. ' WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
  14. '**********************************************************************************/
  15. Imports System.Data.SqlClient
  16. Namespace VBWinFormDataGridView.DataGridViewPaging
  17. Public Class MainForm
  18. Private PageSize As Integer = 30
  19. Private CurrentPageIndex As Integer = 1
  20. Private TotalPage As Integer
  21. Private connstr As String = "Persist Security Info=False;" _
  22. + "Integrated Security=SSPI;" _
  23. + "Initial Catalog=Northwind;" _
  24. + "server=localhost"
  25. Private conn As SqlConnection
  26. Private adapter As SqlDataAdapter
  27. Private command As SqlCommand
  28. Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  29. Me.conn = New SqlConnection(connstr)
  30. Me.adapter = New SqlDataAdapter()
  31. ' Get total count of the pages
  32. Me.GetTotalPageCount()
  33. Me.dataGridView1.ReadOnly = True
  34. ' Load the first page of data
  35. Me.dataGridView1.DataSource = GetPageData(1)
  36. End Sub
  37. Private Sub GetTotalPageCount()
  38. command.CommandText = "Select Count(OrderID) From Orders"
  39. Try
  40. conn.Open()
  41. Dim rowCount As Integer = CType(command.ExecuteScalar(), Integer)
  42. Me.TotalPage = rowCount / PageSize
  43. If rowCount Mod PageSize > 0 Then
  44. Me.TotalPage += 1
  45. End If
  46. Finally
  47. conn.Close()
  48. End Try
  49. End Sub
  50. Private Function GetPageData(ByVal page As Integer) As DataTable
  51. Dim dt As DataTable = New DataTable()
  52. If page = 1 Then
  53. command.CommandText = "Select Top " + PageSize + " * From Orders Order By OrderID"
  54. Else
  55. Dim lowerPageBoundary = (page - 1) * PageSize
  56. command.CommandText = "Select Top " + PageSize _
  57. + " * From Orders " _
  58. + " WHERE OrderID NOT IN " _
  59. + " (SELECT TOP " + lowerPageBoundary _
  60. + " OrderID From Orders Order By OrderID) " _
  61. + " Order By OrderID"
  62. End If
  63. Try
  64. Me.conn.Open()
  65. Me.adapter.SelectCommand = command
  66. Me.adapter.Fill(dt)
  67. Finally
  68. conn.Close()
  69. End Try
  70. Return dt
  71. End Function
  72. Private Sub toolStripButtonFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles toolStripButtonFirst.Click
  73. Me.CurrentPageIndex = 1
  74. Me.dataGridView1.DataSource = GetPageData(Me.CurrentPageIndex)
  75. End Sub
  76. Private Sub toolStripButtonPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles toolStripButtonPrev.Click
  77. If Me.CurrentPageIndex > 1 Then
  78. Me.CurrentPageIndex -= 1
  79. Me.dataGridView1.DataSource = GetPageData(Me.CurrentPageIndex)
  80. End If
  81. End Sub
  82. Private Sub toolStripButtonNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles toolStripButtonNext.Click
  83. If Me.CurrentPageIndex < Me.TotalPage Then
  84. Me.CurrentPageIndex += 1
  85. Me.dataGridView1.DataSource = GetPageData(Me.CurrentPageIndex)
  86. End If
  87. End Sub
  88. Private Sub toolStripButtonLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles toolStripButtonLast.Click
  89. Me.CurrentPageIndex = TotalPage
  90. Me.dataGridView1.DataSource = GetPageData(Me.CurrentPageIndex)
  91. End Sub
  92. End Class
  93. End Namespace