Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Xml.XPath

Public Class frmXML
    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 btnReadXML As System.Windows.Forms.Button
    Friend WithEvents grdData As System.Windows.Forms.DataGrid
    Friend WithEvents btnWriteXML As System.Windows.Forms.Button
    Friend WithEvents btnExecuteXML As System.Windows.Forms.Button
    Friend WithEvents btnNavigateSQL As System.Windows.Forms.Button
    Friend WithEvents btnAddRows As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.btnReadXML = New System.Windows.Forms.Button
        Me.grdData = New System.Windows.Forms.DataGrid
        Me.btnWriteXML = New System.Windows.Forms.Button
        Me.btnExecuteXML = New System.Windows.Forms.Button
        Me.btnNavigateSQL = New System.Windows.Forms.Button
        Me.btnAddRows = New System.Windows.Forms.Button
        CType(Me.grdData, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'btnReadXML
        '
        Me.btnReadXML.Location = New System.Drawing.Point(8, 8)
        Me.btnReadXML.Name = "btnReadXML"
        Me.btnReadXML.Size = New System.Drawing.Size(104, 32)
        Me.btnReadXML.TabIndex = 0
        Me.btnReadXML.Text = "Wczytaj XML"
        '
        'grdData
        '
        Me.grdData.DataMember = ""
        Me.grdData.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.grdData.Location = New System.Drawing.Point(144, 8)
        Me.grdData.Name = "grdData"
        Me.grdData.Size = New System.Drawing.Size(488, 416)
        Me.grdData.TabIndex = 1
        '
        'btnWriteXML
        '
        Me.btnWriteXML.Location = New System.Drawing.Point(8, 48)
        Me.btnWriteXML.Name = "btnWriteXML"
        Me.btnWriteXML.Size = New System.Drawing.Size(104, 32)
        Me.btnWriteXML.TabIndex = 2
        Me.btnWriteXML.Text = "Zapisz XML"
        '
        'btnExecuteXML
        '
        Me.btnExecuteXML.Location = New System.Drawing.Point(8, 88)
        Me.btnExecuteXML.Name = "btnExecuteXML"
        Me.btnExecuteXML.Size = New System.Drawing.Size(104, 32)
        Me.btnExecuteXML.TabIndex = 3
        Me.btnExecuteXML.Text = "Uruchom metodę ExecuteXmlReader"
        '
        'btnNavigateSQL
        '
        Me.btnNavigateSQL.Location = New System.Drawing.Point(8, 128)
        Me.btnNavigateSQL.Name = "btnNavigateSQL"
        Me.btnNavigateSQL.Size = New System.Drawing.Size(104, 32)
        Me.btnNavigateSQL.TabIndex = 4
        Me.btnNavigateSQL.Text = "Przeglądaj SQL"
        '
        'btnAddRows
        '
        Me.btnAddRows.Location = New System.Drawing.Point(8, 168)
        Me.btnAddRows.Name = "btnAddRows"
        Me.btnAddRows.Size = New System.Drawing.Size(104, 32)
        Me.btnAddRows.TabIndex = 5
        Me.btnAddRows.Text = "Dodaj wiersze do dokumentu XML"
        '
        'frmXML
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(640, 430)
        Me.Controls.Add(Me.btnAddRows)
        Me.Controls.Add(Me.btnNavigateSQL)
        Me.Controls.Add(Me.btnExecuteXML)
        Me.Controls.Add(Me.btnWriteXML)
        Me.Controls.Add(Me.grdData)
        Me.Controls.Add(Me.btnReadXML)
        Me.Name = "frmXML"
        Me.Text = "ADO.NET i XML"
        CType(Me.grdData, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub btnReadXML_Click(ByVal sender As System.Object, _
                        ByVal e As System.EventArgs) Handles btnReadXML.Click
        Dim dsPubs As New DataSet

        ' Wczytuje kod XML z pliku
        dsPubs.ReadXml("..\Pubs.xml")

        ' Wiąże obiekt DataSet z kontrolką DataGrid
        grdData.DataMember = "wydawnictwa"
        grdData.DataSource = dsPubs

    End Sub

    Private Sub btnWriteXML_Click(ByVal sender As System.Object, _
                    ByVal e As System.EventArgs) Handles btnWriteXML.Click
        Dim dsSales As New DataSet
        Dim cn As New SqlConnection _
                    ("data source=localhost;initial catalog=pubs;user id=sa")
        Dim daAuthors As New SqlDataAdapter("select * from sales ", cn)
        Dim daPublishers As New SqlDataAdapter("select * from stores ", cn)

        ' Wczytaj dane relacyjne z bazy danych
        daAuthors.Fill(dsSales, "Sales")
        daPublishers.Fill(dsSales, "Stores")

        ' Zapisz dane XML do pliku
        dsSales.WriteXml("..\StoreSales.xml")

        ' Zapisz składowany schemat w pliku XSD
        dsSales.WriteXmlSchema("..\StoreSales.xsd")

        ' Wprowadź kilka zmian - zmodyfikuj, usuń i wstaw nowy wiersz
        dsSales.Tables("Stores").Rows(0)("stor_id") = 999       ' Modyfikacja
        dsSales.Tables("Stores").Rows(1).Delete()               ' Usunięcie
        Dim rr As DataRow = dsSales.Tables("Stores").NewRow()
        rr("stor_name") = "Nowy Sklep"
        dsSales.Tables("Stores").Rows.Add(rr)                   ' Wstawienie

        ' Stwórz dokument DiffGram z samymi zmianami
        Dim ChangedDataSet = dsSales.GetChanges()
        ChangedDataSet.WriteXml("..\Changes.xml", XmlWriteMode.DiffGram)

        ' Zapisz wszystkie dane w formacie DiffGram
        dsSales.WriteXml("..\DiffGram.xml", XmlWriteMode.DiffGram)

    End Sub

    Private Sub btnExecuteXML_Click(ByVal sender As System.Object, _
                        ByVal e As System.EventArgs) Handles btnExecuteXML.Click
        Dim cn As New SqlConnection _
                        ("data source=localhost;initial catalog=pubs;user id=sa")
        Dim cmd As New SqlCommand _
                        ("select * from stores for xml auto, elements", cn)
        Dim reader As Xml.XmlReader
        Dim str As New System.Text.StringBuilder

        cn.Open()

        ' Wykonaj polecenie SELECT języka SQL z klauzulą FOR XML
        reader = cmd.ExecuteXmlReader()

        ' Znajdź i odczytaj dane z węzłów elementów
        While reader.Read()
            Select Case reader.NodeType
                Case Xml.XmlNodeType.Element
                    str.Append("<" & reader.Name & ">")
                Case Xml.XmlNodeType.EndElement
                    str.Append("</" & reader.Name & ">" & ControlChars.CrLf)
                Case Xml.XmlNodeType.Text
                    str.Append(reader.Value)
                Case Else
                    ' W tym przykładzie ignoruj ten przypadek
            End Select
        End While

        MsgBox(str.ToString)

        cn.Close()
    End Sub

    Private Sub btnNavigateSQL_Click(ByVal sender As System.Object, _
                    ByVal e As System.EventArgs) Handles btnNavigateSQL.Click
        Dim cn As New SqlConnection _
                    ("data source=localhost;initial catalog=pubs;user id=sa")
        Dim da As New SqlDataAdapter("Select * from authors", cn)
        Dim ds As New DataSet

        ' Wypełnij obiekt DataSet danymi z relacyjnej bazy danych
        da.Fill(ds, "authors")

        ' Stwórz obiekt klasy XmlDataDocument na bazie istniejącego obiektu DataSet
        Dim xmlDoc As New Xml.XmlDataDocument(ds)

        ' Stwórz obiekt XPathNavigator dla obiektu XmlDataDocument			
        Dim xmlNav As XPathNavigator = xmlDoc.CreateNavigator()

        ' Wyszukaj nazwiska wszystkich autorów z Kalifornii (state = CA)
        Dim xIterator As XPathNodeIterator
        xIterator = xmlNav.Select("//authors[state='CA']/au_lname")

        ' Przeszukaj kolejno wszystkie wybrane węzły i wyświetl
        ' nazwiska autorów
        Dim str As New System.Text.StringBuilder
        While (xIterator.MoveNext())
            str.Append(xIterator.Current.Value & ControlChars.CrLf)
        End While
        MsgBox(str.ToString)

    End Sub

    Private Sub btnAddRows_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddRows.Click
        Dim dsPubs As New DataSet

        ' Wczytaj dane XML z pliku
        dsPubs.ReadXml("..\Pubs.xml")

        ' Dodaj nowy wiersz
        Dim row As DataRow = dsPubs.Tables("Publishers").NewRow()
        row("pub_name") = "Newbie Publishing Corp. "
        row("city") = "New York"
        row("state") = "NY"
        row("Country") = "USA"
        dsPubs.Tables("Publishers").Rows.Add(row)
        ' Przypisz obiekt DataSet z kontrolką DataGrid, aby zobaczyć nowe dane
        grdData.DataMember = "publishers"
        grdData.DataSource = dsPubs

    End Sub


End Class
