formmain.vb

Programming the .NET Compact Framework with vb 源代码

' -----------------------------------------------------------------------------
' Code from _Programming the .NET Compact Framework with VB_
' and _Programming the .NET Compact Framework with C#_
' (c) Copyright 2002-2004 Paul Yao and David Durant. 
' All rights reserved.
' -----------------------------------------------------------------------------

Imports System
Imports System.IO
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlServerCe
Imports System.Windows.Forms

Public Class FormMain
   Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

   Public Sub New()
      MyBase.New()

      'This call is required by the Windows Form Designer.
      InitializeComponent()

      'Add any initialization after the InitializeComponent() call

   End Sub

   Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
      MyBase.Dispose(disposing)
   End Sub

   'NOTE: The following procedure is required by the Windows Form Designer
   'It can be modified using the Windows Form Designer.  
   'Do not modify it using the code editor.
   Friend WithEvents comboKeys As System.Windows.Forms.ComboBox
   Friend WithEvents textProductID As System.Windows.Forms.TextBox
   Friend WithEvents textProductName As System.Windows.Forms.TextBox
   Friend WithEvents lblProductID As System.Windows.Forms.Label
   Friend WithEvents lblProductName As System.Windows.Forms.Label
   Friend WithEvents textCategoryName As System.Windows.Forms.TextBox
   Friend WithEvents lblCategoryName As System.Windows.Forms.Label
   <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
      Me.comboKeys = New System.Windows.Forms.ComboBox
      Me.textProductID = New System.Windows.Forms.TextBox
      Me.textProductName = New System.Windows.Forms.TextBox
      Me.textCategoryName = New System.Windows.Forms.TextBox
      Me.lblProductID = New System.Windows.Forms.Label
      Me.lblProductName = New System.Windows.Forms.Label
      Me.lblCategoryName = New System.Windows.Forms.Label
      '
      'comboKeys
      '
      Me.comboKeys.Size = New System.Drawing.Size(48, 22)
      '
      'textProductID
      '
      Me.textProductID.Location = New System.Drawing.Point(112, 40)
      Me.textProductID.Size = New System.Drawing.Size(120, 22)
      Me.textProductID.Text = ""
      '
      'textProductName
      '
      Me.textProductName.Location = New System.Drawing.Point(112, 80)
      Me.textProductName.Size = New System.Drawing.Size(120, 22)
      Me.textProductName.Text = ""
      '
      'textCategoryName
      '
      Me.textCategoryName.Location = New System.Drawing.Point(112, 120)
      Me.textCategoryName.Size = New System.Drawing.Size(120, 22)
      Me.textCategoryName.Text = ""
      '
      'lblProductID
      '
      Me.lblProductID.Location = New System.Drawing.Point(16, 48)
      Me.lblProductID.Size = New System.Drawing.Size(80, 16)
      '
      'lblProductName
      '
      Me.lblProductName.Location = New System.Drawing.Point(16, 88)
      Me.lblProductName.Size = New System.Drawing.Size(80, 16)
      '
      'lblCategoryName
      '
      Me.lblCategoryName.Location = New System.Drawing.Point(16, 128)
      Me.lblCategoryName.Size = New System.Drawing.Size(80, 16)
      '
      'FormMain
      '
      Me.Controls.Add(Me.lblCategoryName)
      Me.Controls.Add(Me.lblProductName)
      Me.Controls.Add(Me.lblProductID)
      Me.Controls.Add(Me.textCategoryName)
      Me.Controls.Add(Me.textProductName)
      Me.Controls.Add(Me.textProductID)
      Me.Controls.Add(Me.comboKeys)
      Me.MinimizeBox = False
      Me.Text = "FormMain"

   End Sub

#End Region

   '  File path and name.
   Private strFile As String = _
      "My Documents\ourProduceCo.sdf"

   '  Connection string.
   Private strConn As String = _
      "Data Source=" & strFile

   '  Select product keys
   Private strGetProductIDs As String = _
      " SELECT ProductID " & _
      "   FROM Products "

   '  Select one product, joined with its category.
   Private strGetOneProduct As String = _
      " SELECT ProductID, ProductName, CategoryName " & _
      "   FROM Products P " & _
      "   JOIN Categories C on C.CategoryID = P.CategoryID " & _
      "  WHERE P.ProductID = "

   '  Used to bypass the SelectIndexChanged event 
   '     during the loading of the ComboBox.
   Private boolLoading As Boolean = True

   Private Sub FormMain_Load(ByVal sender As Object, _
                             ByVal e As EventArgs _
                             ) _
                             Handles MyBase.Load
      '  Load product keys into the ComboBox
      '     and select the first one.
      LoadProductIDs()
      comboKeys.SelectedIndex = 0
   End Sub

   Private Sub comboKeys_SelectedIndexChanged( _
                           ByVal sender As Object, _
                           ByVal e As EventArgs _
                           ) _
                           Handles comboKeys.SelectedIndexChanged

      '  A product key has been selected, retrieve
      '     and display the corresponding product.
      If Not boolLoading Then
         LoadOneProduct(comboKeys.SelectedItem)
      End If
   End Sub

   Private Sub textProductName_Validated( _
                              ByVal sender As Object, _
                              ByVal e As EventArgs _
                              ) _
                              Handles textProductName.Validated

      '  Update this product row in the database.
      UpdateSelectedRow(textProductID.Text, textProductName.Text)
   End Sub

   Private Sub LoadProductIDs()
      '  Clear the ComboBox
      comboKeys.Items.Clear()

      '  A connection, a command, and a reader.
      Dim connDB As New SqlCeConnection(strConn)
      Dim cmndDB As New SqlCeCommand(strGetProductIDs, connDB)
      Dim drdrDB As SqlCeDataReader

      '  Open the connection.
      connDB.Open()

      '  Submit the SQL statement and receive
      '     the SqlCeReader for the results set. 
      drdrDB = cmndDB.ExecuteReader()

      '  Read each row.  Add the contents of its
      '     only column as an entry in the ComboBox.
      '     Close the reader when done.
      While drdrDB.Read()
         comboKeys.Items.Add(drdrDB.Item("ProductID"))
      End While
      drdrDB.Close()

      '  Close the connection.
      connDB.Close()

      '  Start responding to ComboBox's
      '     SelectedIndexChanged events.
      Me.boolLoading = False
   End Sub

   Private Sub LoadOneProduct(ByVal intProductID As Integer)

      '  A connection, a command, and a reader.
      Dim connDB As New SqlCeConnection(strConn)
      Dim cmndDB As New SqlCeCommand
      Dim drdrDB As SqlCeDataReader

      '  Open the connection.
      connDB.Open()

      '  Set the command object to retrieve
      '     rows from one table via one index.
      '     Then retrieve the reader.
      With cmndDB
         .Connection = connDB
         .CommandType = CommandType.TableDirect
         .CommandText = "Products"
         .IndexName = "PKProducts"
         drdrDB = .ExecuteReader()
      End With

      '  Retrieve the first (only) row from the
      '     Products table that has the ProductID
      '     that was selected in the ComboBox.
      '  Load the fields into the form's controls.
      '  Close the reader.
      With drdrDB
         .Seek(DbSeekOptions.FirstEqual, intProductID)
         If .Read() Then
            LoadControlsFromRow(drdrDB)
         End If
         .Close()
      End With

      '  Close the connection.
      connDB.Close()
   End Sub

   'Private Sub LoadOneProduct(ByVal intProductID As Integer)

   '   '  Append the desired ProductID to the SELECT statement.
   '   Dim strSQL As String = strGetOneProduct & intProductID

   '   '  A connection, a command, and a reader.
   '   Dim connDB As New SqlCeConnection(strConn)
   '   Dim cmndDB As New SqlCeCommand
   '   Dim drdrDB As SqlCeDataReader

   '   '  Open the connection.
   '   connDB.Open()

   '   '  Submit the SQL statement and receive
   '   '     the SqlCeReader for the one-row 
   '   '     results set. 
   '   drdrDB = cmndDB.ExecuteReader()

   '   '  Read the first (only) row.  
   '   '     Display it.  Close the reader.
   '   If drdrDB.Read() Then
   '      LoadControlsFromRow(drdrDB)
   '   End If
   '   drdrDB.Close()

   '   '  Close the connection.
   '   connDB.Close()
   'End Sub

   Private Sub LoadControlsFromRow( _
                              ByVal drdrDB As SqlCeDataReader)

      '  Transfer the colum titles and the field
      '     contents of the current row from the
      '     reader to the form's controls.
      With drdrDB
         lblProductID.Text = .GetName(0)
         textProductID.Text = .GetValue(0)
         lblProductName.Text = .GetName(1)
         textProductName.Text = .GetValue(1)
         lblCategoryName.Text = .GetName(2)
         textCategoryName.Text = .GetValue(2)
      End With
   End Sub

   Private Sub UpdateSelectedRow(ByVal intProductID As Integer, _
                                 ByVal strProductName As String)

      '  A connection and a command.
      Dim connDB As New SqlCeConnection(strConn)
      Dim cmndDB As New SqlCeCommand

      '  Open the connection.
      connDB.Open()

      '  Update the product name for the selected product.
      With cmndDB
         .Connection = connDB
         .CommandText = _
            " UPDATE Products " & _
               " SET ProductName = " & _
                     "'" & strProductName & "'" & _
               " WHERE ProductID = " & _
                     intProductID
         .ExecuteNonQuery()
      End With

      '  Close the connection.
      connDB.Close()
   End Sub
End Class