Imports System.Data
Imports System.Data.SqlClient

Public Class frmCustomersOrders
    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

    '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

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

    '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 btnFill As System.Windows.Forms.Button
    Friend WithEvents btnUpdate As System.Windows.Forms.Button
    Friend WithEvents grdCustomersOrders As System.Windows.Forms.DataGrid
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.btnFill = New System.Windows.Forms.Button
        Me.btnUpdate = New System.Windows.Forms.Button
        Me.grdCustomersOrders = New System.Windows.Forms.DataGrid
        CType(Me.grdCustomersOrders, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'btnFill
        '
        Me.btnFill.Location = New System.Drawing.Point(544, 24)
        Me.btnFill.Name = "btnFill"
        Me.btnFill.TabIndex = 0
        Me.btnFill.Text = "Wypełnij"
        '
        'btnUpdate
        '
        Me.btnUpdate.Location = New System.Drawing.Point(544, 64)
        Me.btnUpdate.Name = "btnUpdate"
        Me.btnUpdate.TabIndex = 1
        Me.btnUpdate.Text = "Aktualizuj"
        '
        'grdCustomersOrders
        '
        Me.grdCustomersOrders.DataMember = ""
        Me.grdCustomersOrders.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.grdCustomersOrders.Location = New System.Drawing.Point(24, 24)
        Me.grdCustomersOrders.Name = "grdCustomersOrders"
        Me.grdCustomersOrders.Size = New System.Drawing.Size(504, 376)
        Me.grdCustomersOrders.TabIndex = 2
        '
        'frmCustomersOrders
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(632, 421)
        Me.Controls.Add(Me.grdCustomersOrders)
        Me.Controls.Add(Me.btnUpdate)
        Me.Controls.Add(Me.btnFill)
        Me.Name = "frmCustomersOrders"
        Me.Text = "Klienci i zamówienia"
        CType(Me.grdCustomersOrders, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region
    Private Sub frmCustomersOrders_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private ds As DataSet
    Private cn As New SqlConnection( _
       "server=localhost;uid=sa;database=Nowinki")
    Private Sub btnFill_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles btnFill.Click

        Dim da As New SqlDataAdapter
        grdCustomersOrders.DataSource = Nothing
        ds = New DataSet

        ' Konfigurowanie wsadowego polecenia SELECT
        da.SelectCommand = New SqlCommand
        da.SelectCommand.Connection = cn
        da.SelectCommand.CommandType = CommandType.Text
        da.SelectCommand.CommandText = _
           "select * from tblKlienci; select * from tblZamowienia"

        ' Odwzorowania tabel, które zwiększają czytelność nazw
        da.TableMappings.Add("Table", "Klienci")
        da.TableMappings.Add("Table1", "Zamowienia")

        ' Wczytywanie danych
        da.Fill(ds)

        ' Ręczne dodawanie relacji
        ds.Relations.Add("Klienci_Zamowienia", _
            ds.Tables("Klienci").Columns("ID"), _
            ds.Tables("Zamowienia").Columns("IDKlienta"))
        ' Wyświetlanie danych
        grdCustomersOrders.DataSource = ds
    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles btnUpdate.Click

        ' Tworzenie i konfigurowanie obiektów DataAdapter
        Dim daCustomers As New SqlDataAdapter( _
           "select * from tblKlienci", cn)

        Dim daOrders As New SqlDataAdapter( _
           "select * from tblZamowienia", cn)

        Dim cbCustomers As New SqlCommandBuilder(daCustomers)
        Dim cbOrders As New SqlCommandBuilder(daOrders)

        Try
            ' Aktualizowanie tabel, we "właściwej" kolejności
            Dim ChangedTable As New DataTable

            ' Usunięte wiersze w tabeli podrzędnej
            ChangedTable = _
               ds.Tables("Zamowienia").GetChanges(DataRowState.Deleted)
            If Not ChangedTable Is Nothing Then
                daOrders.Update(ChangedTable)
            End If

            ' Wszystkie zmienione wiersze w tabeli nadrzędnej
            ChangedTable = ds.Tables("Klienci").GetChanges
            If Not ChangedTable Is Nothing Then
                daCustomers.Update(ChangedTable)
            End If

            ' Dodane lub zmodyfikowane wiersze w tabeli podrzędnej
            ChangedTable = _
               ds.Tables("Zamowienia").GetChanges(DataRowState.Added _
               Or DataRowState.Modified)
            If Not ChangedTable Is Nothing Then
                daOrders.Update(ChangedTable)
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

End Class
