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.Windows.Forms
Imports System.Data
Imports System.Data.Common
Imports System.Data.Sqlclient

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

   '  Connection string.
   Private strConn As String = _
         "data source=207.202.168.30;" & _
         "initial catalog=Northwind;" & _
         "user id=DeliveryDriver;" & _
         "pwd=DD;" & _
         "workstation id=SNOWDEN;" & _
         "packet size=4096;" & _
         "persist security info=False;"

   '  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 SqlConnection(strConn)
      Dim cmndDB As New SqlCommand(strGetProductIDs, connDB)
      Dim drdrDB As SqlDataReader

      '  Open the connection.
      connDB.Open()

      '  Submit the SQL statement and receive
      '     the SqlReader 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)

      '  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 SqlConnection(strConn)
      Dim cmndDB As New SqlCommand(strSQL, connDB)
      Dim drdrDB As SqlDataReader

      '  Open the connection.
      connDB.Open()

      '  Submit the SQL statement and receive
      '     the SqlReader 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 SqlDataReader)

      '  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 SqlConnection(strConn)
      Dim cmndDB As New SqlCommand

      Try
         '  Open the connection.
         connDB.Open()

         '  Initialize the command (including 
         '    creating the parameters).
         cmndDB.CommandText = "procModifyProductInfo"
         cmndDB.CommandType = CommandType.StoredProcedure
         cmndDB.Connection = connDB
         cmndDB.Parameters.Add( _
            New SqlParameter( _
            "@RETURN_VALUE", SqlDbType.Int, 4, _
            ParameterDirection.ReturnValue, False, _
            0, 0, "", DataRowVersion.Current, Nothing))
         cmndDB.Parameters.Add( _
            New SqlParameter( _
            "@ProductID", SqlDbType.Int, 4))
         cmndDB.Parameters.Add( _
            New SqlParameter( _
            "@ProductName", SqlDbType.NVarChar, 40))
         cmndDB.Parameters.Add( _
            New SqlParameter( _
            "@UnitPrice", SqlDbType.Money, 4))

         '  Assign values to the parameters.
         cmndDB.Parameters("@ProductID").Value = _
            Integer.Parse(comboKeys.SelectedItem.ToString())
         cmndDB.Parameters("@ProductName").Value = _
            textProductName.Text
         cmndDB.Parameters("@UnitPrice").Value = 123.45

         '  Execute the stored procedure.
         cmndDB.ExecuteNonQuery()

         '  Check the SQL Server return value.
         If cmndDB.Parameters("@RETURN_VALUE").Value <> 0 Then
            MessageBox.Show( _
               "You should have already caught a SqlException.")
         End If

      Catch exSQL As SqlException
         Dim errSQL As SqlError
         For Each errSQL In exSQL.Errors
            MessageBox.Show(errSQL.Message)
         Next

      Catch ex As Exception

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