dataviews.vb

东软内部材料（六）ado .net相关

Public Class frmDataViews
    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
        Me.daCustomers.Fill(Me.dsMaster1.CustomerList)
        Me.daOrders.Fill(Me.dsMaster1.OrderTotals)
        Me.daEmployees.Fill(Me.dsMaster1.EmployeeList)
    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub
    Friend WithEvents cnNorthwind As System.Data.SqlClient.SqlConnection
    Friend WithEvents cmdSelectCustomers As System.Data.SqlClient.SqlCommand
    Friend WithEvents daCustomers As System.Data.SqlClient.SqlDataAdapter
    Friend WithEvents cmdSelectOrders As System.Data.SqlClient.SqlCommand
    Friend WithEvents lblEmployees As System.Windows.Forms.Label
    Friend WithEvents lblClients As System.Windows.Forms.Label
    Friend WithEvents lblOrders As System.Windows.Forms.Label
    Friend WithEvents dgOrders As System.Windows.Forms.DataGrid
    Friend WithEvents lbEmployees As System.Windows.Forms.ListBox
    Friend WithEvents lbClients As System.Windows.Forms.ListBox
    Friend WithEvents daOrders As System.Data.SqlClient.SqlDataAdapter
    Friend WithEvents MasterID As System.Data.DataColumn
    Friend WithEvents MasterValue As System.Data.DataColumn
    Friend WithEvents dtChild As System.Data.DataTable
    Friend WithEvents ChildID As System.Data.DataColumn
    Friend WithEvents MasterLink As System.Data.DataColumn
    Friend WithEvents ChildValue As System.Data.DataColumn
    Friend WithEvents daEmployees As System.Data.SqlClient.SqlDataAdapter
    Friend WithEvents cmdSelectEmployees As System.Data.SqlClient.SqlCommand
        Friend WithEvents btnCreate As System.Windows.Forms.Button
    Friend WithEvents btnRowState As System.Windows.Forms.Button
    Friend WithEvents btnSort As System.Windows.Forms.Button
    Friend WithEvents btnFind As System.Windows.Forms.Button

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.Container

    '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 dsMaster1 As DataSets.dsMaster
    Friend WithEvents dvOrders As System.Data.DataView
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.cnNorthwind = New System.Data.SqlClient.SqlConnection()
        Me.lbEmployees = New System.Windows.Forms.ListBox()
        Me.daEmployees = New System.Data.SqlClient.SqlDataAdapter()
        Me.cmdSelectEmployees = New System.Data.SqlClient.SqlCommand()
        Me.lblEmployees = New System.Windows.Forms.Label()
        Me.lblOrders = New System.Windows.Forms.Label()
        Me.daOrders = New System.Data.SqlClient.SqlDataAdapter()
        Me.cmdSelectOrders = New System.Data.SqlClient.SqlCommand()
        Me.btnFind = New System.Windows.Forms.Button()
        Me.lbClients = New System.Windows.Forms.ListBox()
        Me.btnSort = New System.Windows.Forms.Button()
        Me.cmdSelectCustomers = New System.Data.SqlClient.SqlCommand()
        Me.btnCreate = New System.Windows.Forms.Button()
        Me.daCustomers = New System.Data.SqlClient.SqlDataAdapter()
        Me.btnRowState = New System.Windows.Forms.Button()
        Me.lblClients = New System.Windows.Forms.Label()
        Me.dgOrders = New System.Windows.Forms.DataGrid()
        Me.dvOrders = New System.Data.DataView()
        Me.dsMaster1 = New DataSets.dsMaster()
        CType(Me.dgOrders, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.dvOrders, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.dsMaster1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'cnNorthwind
        '
        Me.cnNorthwind.ConnectionString = "data source=localhost;initial catalog=nwind;integrated security=SSPI;persist " & _
        "security info=False;packet size=4096"
        '
        'lbEmployees
        '
        Me.lbEmployees.DataSource = Me.dsMaster1.EmployeeList
        Me.lbEmployees.DisplayMember = "FullName"
        Me.lbEmployees.Location = New System.Drawing.Point(8, 24)
        Me.lbEmployees.Name = "lbEmployees"
        Me.lbEmployees.Size = New System.Drawing.Size(184, 95)
        Me.lbEmployees.TabIndex = 1
        '
        'daEmployees
        '
        Me.daEmployees.SelectCommand = Me.cmdSelectEmployees
        Me.daEmployees.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "EmployeeList", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("EmployeeID", "EmployeeID"), New System.Data.Common.DataColumnMapping("FirstName", "FirstName"), New System.Data.Common.DataColumnMapping("LastName", "LastName")})})
        '
        'cmdSelectEmployees
        '
        Me.cmdSelectEmployees.CommandText = "SELECT EmployeeID, FirstName, LastName FROM EmployeeList"
        Me.cmdSelectEmployees.Connection = Me.cnNorthwind
        '
        'lblEmployees
        '
        Me.lblEmployees.AutoSize = True
        Me.lblEmployees.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblEmployees.Location = New System.Drawing.Point(8, 8)
        Me.lblEmployees.Name = "lblEmployees"
        Me.lblEmployees.Size = New System.Drawing.Size(66, 13)
        Me.lblEmployees.TabIndex = 0
        Me.lblEmployees.Text = "Employees:"
        '
        'lblOrders
        '
        Me.lblOrders.AutoSize = True
        Me.lblOrders.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblOrders.Location = New System.Drawing.Point(8, 136)
        Me.lblOrders.Name = "lblOrders"
        Me.lblOrders.Size = New System.Drawing.Size(43, 13)
        Me.lblOrders.TabIndex = 0
        Me.lblOrders.Text = "Orders:"
        '
        'daOrders
        '
        Me.daOrders.SelectCommand = Me.cmdSelectOrders
        Me.daOrders.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "OrderTotals", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("CustomerID", "CustomerID"), New System.Data.Common.DataColumnMapping("OrderID", "OrderID"), New System.Data.Common.DataColumnMapping("OrderDate", "OrderDate"), New System.Data.Common.DataColumnMapping("Subtotal", "Subtotal")})})
        '
        'cmdSelectOrders
        '
        Me.cmdSelectOrders.CommandText = "SELECT EmployeeID, CustomerID, OrderID, OrderDate, Subtotal FROM OrderTotals"
        Me.cmdSelectOrders.Connection = Me.cnNorthwind
        '
        'btnFind
        '
        Me.btnFind.Location = New System.Drawing.Point(416, 120)
        Me.btnFind.Name = "btnFind"
        Me.btnFind.Size = New System.Drawing.Size(96, 23)
        Me.btnFind.TabIndex = 4
        Me.btnFind.Text = "Find"
        '
        'lbClients
        '
        Me.lbClients.DataSource = Me.dsMaster1.CustomerList
        Me.lbClients.DisplayMember = "CompanyName"
        Me.lbClients.Location = New System.Drawing.Point(200, 24)
        Me.lbClients.Name = "lbClients"
        Me.lbClients.Size = New System.Drawing.Size(200, 95)
        Me.lbClients.TabIndex = 2
        Me.lbClients.ValueMember = "CustomerID"
        '
        'btnSort
        '
        Me.btnSort.Location = New System.Drawing.Point(416, 88)
        Me.btnSort.Name = "btnSort"
        Me.btnSort.Size = New System.Drawing.Size(96, 23)
        Me.btnSort.TabIndex = 4
        Me.btnSort.Text = "Sort"
        '
        'cmdSelectCustomers
        '
        Me.cmdSelectCustomers.CommandText = "SELECT CustomerID, CompanyName, City, Country, PostalCode, Region FROM CustomerLi" & _
        "st"
        Me.cmdSelectCustomers.Connection = Me.cnNorthwind
        '
        'btnCreate
        '
        Me.btnCreate.Location = New System.Drawing.Point(416, 24)
        Me.btnCreate.Name = "btnCreate"
        Me.btnCreate.Size = New System.Drawing.Size(96, 24)
        Me.btnCreate.TabIndex = 4
        Me.btnCreate.Text = "Create"
        '
        'daCustomers
        '
        Me.daCustomers.SelectCommand = Me.cmdSelectCustomers
        Me.daCustomers.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "CustomerList", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("CustomerID", "CustomerID"), New System.Data.Common.DataColumnMapping("CompanyName", "CompanyName"), New System.Data.Common.DataColumnMapping("City", "City"), New System.Data.Common.DataColumnMapping("Country", "Country"), New System.Data.Common.DataColumnMapping("PostalCode", "PostalCode"), New System.Data.Common.DataColumnMapping("Region", "Region")})})
        '
        'btnRowState
        '
        Me.btnRowState.Location = New System.Drawing.Point(416, 56)
        Me.btnRowState.Name = "btnRowState"
        Me.btnRowState.Size = New System.Drawing.Size(96, 24)
        Me.btnRowState.TabIndex = 4
        Me.btnRowState.Text = "Row State"
        '
        'lblClients
        '
        Me.lblClients.AutoSize = True
        Me.lblClients.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblClients.Location = New System.Drawing.Point(200, 8)
        Me.lblClients.Name = "lblClients"
        Me.lblClients.Size = New System.Drawing.Size(44, 13)
        Me.lblClients.TabIndex = 0
        Me.lblClients.Text = "Clients:"
        '
        'dgOrders
        '
        Me.dgOrders.DataMember = ""
        Me.dgOrders.DataSource = Me.dvOrders
        Me.dgOrders.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.dgOrders.Location = New System.Drawing.Point(8, 152)
        Me.dgOrders.Name = "dgOrders"
        Me.dgOrders.Size = New System.Drawing.Size(392, 208)
        Me.dgOrders.TabIndex = 3
        '
        'dvOrders
        '
        Me.dvOrders.Sort = "OrderID"
        Me.dvOrders.Table = Me.dsMaster1.OrderTotals
        '
        'dsMaster1
        '
        Me.dsMaster1.DataSetName = "dsMaster"
        Me.dsMaster1.Locale = New System.Globalization.CultureInfo("en-US")
        Me.dsMaster1.Namespace = "http://www.tempuri.org/dsMaster.xsd"
        '
        'frmDataViews
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(520, 373)
        Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.btnFind, Me.btnSort, Me.btnRowState, Me.btnCreate, Me.dgOrders, Me.lbClients, Me.lbEmployees, Me.lblOrders, Me.lblClients, Me.lblEmployees})
        Me.Name = "frmDataViews"
        Me.Text = "DataViews"
        CType(Me.dgOrders, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.dvOrders, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.dsMaster1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        Dim drCurrent As System.Data.DataRow
        Dim dvNew As New System.Data.DataView()

        'retrieve the selected row in lbOrders
        drCurrent = Me.lbClients.SelectedItem.Row

        'configure the dataview
        dvNew.Table = Me.dsMaster1.OrderTotals
        dvNew.RowFilter = "CustomerID = '" & drCurrent(0) & " '"

        'rebind the datagrid
        Me.dgOrders.DataSource = dvNew
    End Sub

    Private Sub btnRowState_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRowState.Click
        Dim drNew As System.Data.DataRowView

        'Add a new order
        drNew = Me.dvOrders.AddNew()
        drNew("CustomerID") = "ALFKI"
        drNew("EmployeeID") = 1
        drNew("OrderID") = 0

        'Set the RowStateFilter
        Me.dvOrders.RowStateFilter = DataViewRowState.Added

        'Refresh the datagrid
        Me.dgOrders.Refresh()
    End Sub

    Private Sub btnSort_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSort.Click
        'Change the sort order
        Me.dvOrders.Sort = "EmployeeID, CustomerID, OrderID DESC"

        'Refresh the datagrid
        Me.dgOrders.Refresh()
    End Sub

    Private Sub btnFind_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnFind.Click
        Dim idxFound As Integer
        Dim strMessage As String

        idxFound = Me.dvOrders.Find(10255)

        strMessage = "The OrderID is " & _
            Me.dvOrders(idxFound).Item("OrderID")
        strMessage &= vbCrLf & "The CustomerID is " & _
            Me.dvOrders(idxFound).Item("CustomerID")
        strMessage &= vbCrLf & "The EmployeeID is " & _
            Me.dvOrders(idxFound).Item("EmployeeID")
        MessageBox.Show(strMessage)
    End Sub
End Class