Imports System
Imports System.Data
Imports System.Data.SqlClient


Public Class frmDataSets
    Inherits System.Windows.Forms.Form
    Private dsEmployeeInfo As DataSet

#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 btnCreateDS As System.Windows.Forms.Button
    Friend WithEvents lstOutput As System.Windows.Forms.ListBox
    Friend WithEvents btnCreateRelations As System.Windows.Forms.Button
    Friend WithEvents btnChildRows As System.Windows.Forms.Button
    Friend WithEvents btnConstraints As System.Windows.Forms.Button
    Friend WithEvents btnDataAdapterFill As System.Windows.Forms.Button
    Friend WithEvents btnDataAdapterUpdates As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.btnCreateDS = New System.Windows.Forms.Button
        Me.lstOutput = New System.Windows.Forms.ListBox
        Me.btnCreateRelations = New System.Windows.Forms.Button
        Me.btnChildRows = New System.Windows.Forms.Button
        Me.btnConstraints = New System.Windows.Forms.Button
        Me.btnDataAdapterFill = New System.Windows.Forms.Button
        Me.btnDataAdapterUpdates = New System.Windows.Forms.Button
        Me.SuspendLayout()
        '
        'btnCreateDS
        '
        Me.btnCreateDS.Location = New System.Drawing.Point(8, 16)
        Me.btnCreateDS.Name = "btnCreateDS"
        Me.btnCreateDS.Size = New System.Drawing.Size(112, 24)
        Me.btnCreateDS.TabIndex = 0
        Me.btnCreateDS.Text = "Utwórz DataSet"
        '
        'lstOutput
        '
        Me.lstOutput.Location = New System.Drawing.Point(136, 16)
        Me.lstOutput.Name = "lstOutput"
        Me.lstOutput.Size = New System.Drawing.Size(424, 381)
        Me.lstOutput.TabIndex = 1
        '
        'btnCreateRelations
        '
        Me.btnCreateRelations.Location = New System.Drawing.Point(8, 48)
        Me.btnCreateRelations.Name = "btnCreateRelations"
        Me.btnCreateRelations.Size = New System.Drawing.Size(112, 24)
        Me.btnCreateRelations.TabIndex = 2
        Me.btnCreateRelations.Text = "Utwórz relacje"
        '
        'btnChildRows
        '
        Me.btnChildRows.Location = New System.Drawing.Point(8, 80)
        Me.btnChildRows.Name = "btnChildRows"
        Me.btnChildRows.Size = New System.Drawing.Size(112, 24)
        Me.btnChildRows.TabIndex = 3
        Me.btnChildRows.Text = "Wiersze podrzędne"
        '
        'btnConstraints
        '
        Me.btnConstraints.Location = New System.Drawing.Point(8, 112)
        Me.btnConstraints.Name = "btnConstraints"
        Me.btnConstraints.Size = New System.Drawing.Size(112, 24)
        Me.btnConstraints.TabIndex = 4
        Me.btnConstraints.Text = "Ograniczenia"
        '
        'btnDataAdapterFill
        '
        Me.btnDataAdapterFill.Location = New System.Drawing.Point(8, 144)
        Me.btnDataAdapterFill.Name = "btnDataAdapterFill"
        Me.btnDataAdapterFill.Size = New System.Drawing.Size(112, 40)
        Me.btnDataAdapterFill.TabIndex = 5
        Me.btnDataAdapterFill.Text = "Wypełnij przez DataAdapter"
        '
        'btnDataAdapterUpdates
        '
        Me.btnDataAdapterUpdates.Location = New System.Drawing.Point(8, 192)
        Me.btnDataAdapterUpdates.Name = "btnDataAdapterUpdates"
        Me.btnDataAdapterUpdates.Size = New System.Drawing.Size(112, 24)
        Me.btnDataAdapterUpdates.TabIndex = 6
        Me.btnDataAdapterUpdates.Text = "Aktualizacje"
        '
        'frmDataSets
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(576, 413)
        Me.Controls.Add(Me.btnDataAdapterUpdates)
        Me.Controls.Add(Me.btnDataAdapterFill)
        Me.Controls.Add(Me.btnConstraints)
        Me.Controls.Add(Me.btnChildRows)
        Me.Controls.Add(Me.btnCreateRelations)
        Me.Controls.Add(Me.lstOutput)
        Me.Controls.Add(Me.btnCreateDS)
        Me.Name = "frmDataSets"
        Me.Text = "Obiekty DataSet"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub btnCreateDS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateDS.Click
        CreateDataSet()
        AddData()
        DisplayDataSet()
        'DisplayDataSet(dsEmployeeInfo)
    End Sub

    Private Sub frmDataSets_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub CreateDataSet()
        ' Tworzenie zestawu danych dsEmployeeInfo
        dsEmployeeInfo = New DataSet
        ' Tworzenie tabeli "Pracownicy"
        Dim dtEmployees As DataTable = New DataTable("Pracownicy")
        dtEmployees.CaseSensitive = False
        dtEmployees.Columns.Add("Imie", Type.GetType("System.String"))
        dtEmployees.Columns.Add("Nazwisko", Type.GetType("System.String"))
        dtEmployees.Columns.Add("IDDzialu", Type.GetType("System.Int32"))
        ' Dodawanie tabeli Pracownicy do zestawu danych dsEmployeeInfo
        dsEmployeeInfo.Tables.Add(dtEmployees)
        ' Tworzenie tabeli "Dzialy"
        ' Zrobimy to za pomocą jednej z przeciążonych wersji funkcji.
        ' Sposób ten jest dłuższy w przypadku standardowych kolumn,
        ' ale umożliwia ustawienie innych właściwości kolumn
        ' (na przykład ReadOnly i Unique) przed dodaniem obiektu
        ' DataColumn do kolekcji Columns.
        Dim dtDepartments As DataTable
        dtDepartments = New DataTable
        dtDepartments.TableName = "Dzialy"
        dtDepartments.MinimumCapacity = 5
        dtDepartments.CaseSensitive = False
        Dim NewColumn As New DataColumn
        With NewColumn
            .ColumnName = "ID"
            .DataType = Type.GetType("System.Int32")
            .ReadOnly = True
            .Unique = True
            .AutoIncrement = True
        End With
        dtDepartments.Columns.Add(NewColumn)
        NewColumn = New DataColumn
        With NewColumn
            .ColumnName = "NazwaDzialu"
            .DataType = Type.GetType("System.String")
            .Unique = True
            .AllowDBNull = False
        End With
        dtDepartments.Columns.Add(NewColumn)
        ' Dodawanie tabeli Dzialy do zestawu danych EmployeeInfo
        dsEmployeeInfo.Tables.Add(dtDepartments)
    End Sub

    Private Sub AddData()
        Dim dtDepartments As DataTable = dsEmployeeInfo.Tables("Dzialy")
        Dim dtEmployees As DataTable = dsEmployeeInfo.Tables("Pracownicy")
        ' Dodawanie 4 rekordów do tabeli Dzialy
        Dim rowDept As DataRow
        rowDept = dtDepartments.NewRow
        rowDept("NazwaDzialu") = "Administracja"
        dtDepartments.Rows.Add(rowDept)
        rowDept = dtDepartments.NewRow
        rowDept("NazwaDzialu") = "Produkcja"
        dtDepartments.Rows.Add(rowDept)
        rowDept = dtDepartments.NewRow
        rowDept("NazwaDzialu") = "Sprzedaż"
        dtDepartments.Rows.Add(rowDept)
        rowDept = dtDepartments.NewRow
        rowDept("NazwaDzialu") = "Marketing"
        dtDepartments.Rows.Add(rowDept)

        ' Dodawanie 3 rekordów do tabeli Pracownicy
        Dim rowEmployee As DataRow
        rowEmployee = dtEmployees.NewRow
        rowEmployee("Imie") = "Janusz"
        rowEmployee("Nazwisko") = "Głowacki"
        rowEmployee("IDDzialu") = 2
        dtEmployees.Rows.Add(rowEmployee)

        rowEmployee = dtEmployees.NewRow
        rowEmployee("Imie") = "Jerzy"
        rowEmployee("Nazwisko") = "Makowiecki"
        rowEmployee("IDDzialu") = 3
        dtEmployees.Rows.Add(rowEmployee)

        rowEmployee = dtEmployees.NewRow
        rowEmployee("Imie") = "Szymon"
        rowEmployee("Nazwisko") = "Janowicz"
        rowEmployee("IDDzialu") = 3
        dtEmployees.Rows.Add(rowEmployee)

    End Sub

    Private Sub DisplayDataSet()
        Dim dr As DataRow
        Dim dc As DataColumn

        Me.lstOutput.Items.Add("WYŚWIETLANIE ZESTAWU DANYCH")
        Me.lstOutput.Items.Add("===========================")

        ' Wyświetlanie danych z tabeli Pracownicy
        For Each dr In dsEmployeeInfo.Tables("Pracownicy").Rows
            For Each dc In dsEmployeeInfo.Tables("Pracownicy").Columns
                Me.lstOutput.Items.Add(dc.ColumnName & " : " & dr(dc))
            Next
            Me.lstOutput.Items.Add("-----")
        Next
        Me.lstOutput.Items.Add("")
        ' Wyświetlanie danych z tabeli Dzialy
        ' Używamy indeksów zamiast instrukcji For Each
        Dim row As Integer
        Dim col As Integer

        For row = 0 To dsEmployeeInfo.Tables("Dzialy").Rows.Count - 1
            For col = 0 To dsEmployeeInfo.Tables("Dzialy").Columns.Count - 1
                Me.lstOutput.Items.Add( _
                 dsEmployeeInfo.Tables("Dzialy").Columns(col).ColumnName _
                        & " : " & _
                        dsEmployeeInfo.Tables("Dzialy").Rows(row)(col))
            Next col
            Me.lstOutput.Items.Add("-----")
        Next row
    End Sub

    Private Sub DisplayDataSet(ByVal ds As DataSet)
        ' Uniwersalna procedura, która wyświetla zawartość zestawu danych
        ' Obiekt DataSet należy przekazać jako parametr procedury
        Dim dt As DataTable
        Dim dr As DataRow
        Dim dc As DataColumn

        Me.lstOutput.Items.Add("WYŚWIETLANIE ZESTAWU DANYCH")
        Me.lstOutput.Items.Add("===============")

        For Each dt In ds.Tables
            Me.lstOutput.Items.Add("")
            Me.lstOutput.Items.Add("TABELA: " & dt.TableName)
            Me.lstOutput.Items.Add("----------------------------")
            For Each dr In dt.Rows
                For Each dc In dt.Columns
                    Me.lstOutput.Items.Add(dc.ColumnName & " : " & dr(dc))
                Next
                Me.lstOutput.Items.Add("-----")
            Next
        Next dt
    End Sub

    Private Sub btnCreateRelations_Click( _
     ByVal sender As System.Object, _
     ByVal e As System.EventArgs) _
     Handles btnCreateRelations.Click
        Dim rel As DataRelation

        CreateDataSet()

        ' Tworzenie relacji między tabelami Dzialy i Pracownicy
        rel = dsEmployeeInfo.Relations.Add("relDzialyPracownicy", _
          dsEmployeeInfo.Tables("Dzialy").Columns("ID"), _
          dsEmployeeInfo.Tables("Pracownicy").Columns("IDDzialu"))
        DisplayRelations(dsEmployeeInfo)
    End Sub

    Private Sub DisplayRelations(ByVal ds As DataSet)
        Dim rel As DataRelation
        ' Wyświetlanie nazw kolumn powiązanych relacjami
        Me.lstOutput.Items.Add("")
        Me.lstOutput.Items.Add("WYŚWIETLANIE RELACJI")
        For Each rel In ds.Relations
            ' Wyświetlanie nazwy relacji
            Me.lstOutput.Items.Add("NAZWA: " & rel.RelationName)
            ' Wyświetlanie nadrzędnej tabeli i pola
            Me.lstOutput.Items.Add(" TABELA NADRZĘDNA: " & _
              rel.ParentTable.ToString & " -- " & _
              rel.ParentColumns(0).ColumnName)
            ' Wyświetlanie podrzędnej tabeli i pola
            Me.lstOutput.Items.Add(" TABELA PODRZĘDNA: " & _
              rel.ChildTable.ToString & " -- " & _
              rel.ChildColumns(0).ColumnName)
        Next
        Me.lstOutput.Items.Add("")
    End Sub

    Private Sub btnChildRows_Click(ByVal sender As System.Object, _
      ByVal e As System.EventArgs) Handles btnChildRows.Click
        Dim rel As DataRelation
        CreateDataSet()
        AddData()
        ' Tworzenie relacji między tabelami Dzialy a Pracownicy
        rel = dsEmployeeInfo.Relations.Add("relDzialyPracownicy", _
          dsEmployeeInfo.Tables("Dzialy").Columns("ID"), _
          dsEmployeeInfo.Tables("Pracownicy").Columns("IDDzialu"))

        DisplayChildRows(dsEmployeeInfo.Tables("Dzialy"))
    End Sub

    Private Sub DisplayChildRows(ByVal dt As DataTable)
        Dim rel As DataRelation
        Dim relatedRows() As DataRow
        Dim row As DataRow
        Dim col As DataColumn
        Dim i As Integer
        Dim rowData As String

        Me.lstOutput.Items.Add("")
        Me.lstOutput.Items.Add("WIERSZE PODRZĘDNE")

        For Each row In dt.Rows
            For Each rel In dt.ChildRelations
                Me.lstOutput.Items.Add( _
                  dt.TableName & ": " & _
                  rel.ParentColumns(0).ColumnName & _
                  "= " & row(rel.ParentColumns(0).ToString))

                relatedRows = row.GetChildRows(rel)
                ' Wyświetlanie wartości wierszy
                For i = 0 To relatedRows.GetUpperBound(0)
                    rowData = "**** " & _
                                rel.ChildTable.TableName & ":"
                    For Each col In rel.ChildTable.Columns
                        rowData = rowData & " " & _
                                relatedRows(i)(col.ToString)
                    Next col
                    Me.lstOutput.Items.Add(rowData)
                Next i
            Next rel
        Next row
    End Sub

    Private Sub btnConstraints_Click(ByVal sender As _
             System.Object, ByVal e As System.EventArgs) _
             Handles btnConstraints.Click
        Dim dt As DataTable
        Dim rel As DataRelation

        CreateDataSet()

        ' Tworzenie relacji między tabelami Dzialy i Pracownicy
        rel = dsEmployeeInfo.Relations.Add( _
            "relDzialyPracownicy", _
            dsEmployeeInfo.Tables("Dzialy").Columns("ID"), _
            dsEmployeeInfo.Tables("Pracownicy").Columns("IDDzialu"))

        For Each dt In dsEmployeeInfo.Tables
            DisplayConstraints(dt)
        Next dt
    End Sub

    Private Sub DisplayConstraints(ByVal dt As DataTable)
        Dim i As Integer
        Dim cs As Constraint
        Dim uCS As UniqueConstraint
        Dim fkCS As ForeignKeyConstraint
        Dim columns() As DataColumn

        Me.lstOutput.Items.Add("")
        Me.lstOutput.Items.Add( _
                    "OGRANICZENIA TABELI: " & dt.TableName)
        Me.lstOutput.Items.Add( _
                    "====================================")

        For Each cs In dt.Constraints
            Me.lstOutput.Items.Add( _
                    "Nazwa ograniczenia: " & cs.ConstraintName)
            Me.lstOutput.Items.Add( _
                    "Typ: " & cs.GetType().ToString())

            If TypeOf cs Is UniqueConstraint Then
                uCS = CType(cs, UniqueConstraint)
                ' Pobieranie tablicy obiektów Column
                columns = uCS.Columns

                ' Wyświetlanie nazwy każdej kolumny
                For i = 0 To columns.Length - 1
                    Me.lstOutput.Items.Add( _
                            "Nazwa kolumny: " & _
                            columns(i).ColumnName)
                Next i
            ElseIf TypeOf cs Is ForeignKeyConstraint Then
                fkCS = CType(cs, ForeignKeyConstraint)

                ' Pobieranie kolumn podrzędnych i wyświetlanie ich nazw
                columns = fkCS.Columns
                For i = 0 To columns.Length - 1
                    Me.lstOutput.Items.Add( _
                        "Nazwa kolumny: " & _
                        columns(i).ColumnName)
                Next i

                ' Wyświetlanie nazwy powiązanej tabeli (nadrzędnej)
                Me.lstOutput.Items.Add( _
                        "Nazwa powiązanej tabeli: " & _
                        fkCS.RelatedTable.TableName)

                ' Pobieranie powiązanych kolumn (nadrzędnych)
                ' i wyświetlanie ich nazw
                columns = fkCS.RelatedColumns
                For i = 0 To columns.Length - 1
                    Me.lstOutput.Items.Add( _
                        "Nazwa powiązanej kolumny: " & _
                        columns(i).ColumnName)
                Next i
            End If
            Me.lstOutput.Items.Add("-----")
        Next cs
    End Sub

    Private Sub btnDataAdapterFill_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDataAdapterFill.Click
        ReadData()
    End Sub

    Private Sub ReadData()
        Dim rows As Integer
        Dim daDepartments As SqlDataAdapter = New SqlDataAdapter( _
                "select * from tblDzialy", _
                "server=localhost;uid=sa;database=Nowinki")

        Dim daEmployees As SqlDataAdapter = New SqlDataAdapter( _
                "select * from tblPracownicy", _
                "server=localhost;uid=sa;database=Nowinki")
        dsEmployeeInfo = New DataSet
        rows = daDepartments.Fill(dsEmployeeInfo, "Dzialy")
        rows = daEmployees.Fill(dsEmployeeInfo, "Pracownicy")
        DisplayDataSet(dsEmployeeInfo)

    End Sub



    Private Sub btnDataAdapterUpdates_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDataAdapterUpdates.Click
        Dim frmUpdates As New frmUpdates
        frmUpdates.Show()
    End Sub
End Class
