Imports System.Data.SqlClient

Public Class Form1
    Inherits System.Windows.Forms.Form

    Private dsNorthwind As New DataSet("Northwind")

    Private dtRegion As New DataTable("Region")
    Private colRegionID As DataColumn = _
            dtRegion.Columns.Add("RegionID", GetType(System.Int32))
    Private colRegionDescription As DataColumn = _
            dtRegion.Columns.Add("RegionDescription", _
                                 GetType(System.String))
    Private pkRegion As Constraint = dtRegion.Constraints.Add( _
            "pkRegion", dtRegion.Columns("RegionID"), True)

    Private dtTerritories As New DataTable("Territories")
    Private colTerritoryID As DataColumn = _
            dtTerritories.Columns.Add("TerritoryID", _
                                      GetType(System.String))
    Private colTerritoryDescription As DataColumn = _
            dtTerritories.Columns.Add("TerritoryDescription", _
                                      GetType(System.String))
    Private colRegionIDFK As DataColumn = _
            dtTerritories.Columns.Add("RegionID", _
                                      GetType(System.Int32))
    Private pkTerritories As Constraint = _
            dtTerritories.Constraints.Add( _
            "pkTerritories", dtTerritories.Columns("TerritoryID"), _
                             True)
    Private fkTerritories As Constraint = _
            dtTerritories.Constraints.Add( _
            "fkRegionID", dtRegion.Columns("RegionID"), _
                          dtTerritories.Columns("RegionID"))

#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 grdNorthwind As System.Windows.Forms.DataGrid
    Friend WithEvents btnRetrieveNorthwind As System.Windows.Forms.Button
    Friend WithEvents btnReadXml As System.Windows.Forms.Button
    Friend WithEvents btnWriteXML As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.grdNorthwind = New System.Windows.Forms.DataGrid()
        Me.btnRetrieveNorthwind = New System.Windows.Forms.Button()
        Me.btnReadXml = New System.Windows.Forms.Button()
        Me.btnWriteXML = New System.Windows.Forms.Button()
        CType(Me.grdNorthwind, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'grdNorthwind
        '
        Me.grdNorthwind.DataMember = ""
        Me.grdNorthwind.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.grdNorthwind.Location = New System.Drawing.Point(8, 8)
        Me.grdNorthwind.Name = "grdNorthwind"
        Me.grdNorthwind.Size = New System.Drawing.Size(280, 200)
        Me.grdNorthwind.TabIndex = 0
        '
        'btnRetrieveNorthwind
        '
        Me.btnRetrieveNorthwind.Location = New System.Drawing.Point(8, 216)
        Me.btnRetrieveNorthwind.Name = "btnRetrieveNorthwind"
        Me.btnRetrieveNorthwind.Size = New System.Drawing.Size(112, 23)
        Me.btnRetrieveNorthwind.TabIndex = 1
        Me.btnRetrieveNorthwind.Text = "Retrieve Northwind"
        '
        'btnReadXml
        '
        Me.btnReadXml.Location = New System.Drawing.Point(8, 248)
        Me.btnReadXml.Name = "btnReadXml"
        Me.btnReadXml.Size = New System.Drawing.Size(112, 23)
        Me.btnReadXml.TabIndex = 3
        Me.btnReadXml.Text = "Read XML"
        '
        'btnWriteXML
        '
        Me.btnWriteXML.Location = New System.Drawing.Point(176, 248)
        Me.btnWriteXML.Name = "btnWriteXML"
        Me.btnWriteXML.Size = New System.Drawing.Size(104, 23)
        Me.btnWriteXML.TabIndex = 4
        Me.btnWriteXML.Text = "Write Xml"
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(292, 273)
        Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.btnWriteXML, Me.btnReadXml, Me.btnRetrieveNorthwind, Me.grdNorthwind})
        Me.Name = "Form1"
        Me.Text = "Territories"
        CType(Me.grdNorthwind, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub Form1_Load(ByVal sender As System.Object, _
                        ByVal e As System.EventArgs) Handles MyBase.Load
        colRegionDescription.AllowDBNull = False
        colRegionDescription.MaxLength = 50
        colTerritoryDescription.AllowDBNull = False
        colTerritoryID.MaxLength = 20
        colTerritoryDescription.MaxLength = 50
        dsNorthwind.Tables.Add(dtRegion)
        dsNorthwind.Tables.Add(dtTerritories)
        dsNorthwind.EnforceConstraints = True
        dsNorthwind.Relations.Add("fkRegionID", _
                    dtRegion.Columns("RegionID"), _
                    dtTerritories.Columns("RegionID"), True)

        grdNorthwind.DataSource = dsNorthwind.Tables("Region")
    End Sub

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

        Dim sqlConn As SqlConnection = _
                New SqlConnection("server=(local);" & _
                    "Trusted_Connection=yes;database=Northwind")
        Dim sqlRegion As SqlDataAdapter = _
                New SqlDataAdapter("select * from region", sqlConn)
        Dim sqlTerritories As SqlDataAdapter = _
                New SqlDataAdapter("select * from territories", sqlConn)

        sqlRegion.Fill(dtRegion)
        sqlTerritories.Fill(dtTerritories)

        sqlConn.Close()
    End Sub

    Private Sub btnModifyNorthwind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim sqlConn As SqlConnection = _
                New SqlConnection("server=(local);" & _
                    "Trusted_Connection=yes;database=Northwind")
        Dim sqlTerritories As SqlDataAdapter = _
                New SqlDataAdapter("select * from territories", sqlConn)
        Dim sqlCmd As New SqlCommandBuilder(sqlTerritories)

        sqlTerritories.DeleteCommand = New SqlCommand( _
                "DELETE FROM Territories WHERE TerritoryID = " & _
                "@TerritoryID", sqlConn)
        sqlTerritories.DeleteCommand.Parameters.Add("@TerritoryID", _
                SqlDbType.NVarChar, 20, _
                "TerritoryID").SourceVersion = DataRowVersion.Original

        sqlTerritories.Update(dsNorthwind, "Territories")
        sqlConn.Close()
    End Sub

    Private Sub btnReadXml_Click(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles btnReadXml.Click
        dsNorthwind.ReadXml("C:\DBAzine.xml")
    End Sub

    Private Sub btnWriteXML_Click(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles btnWriteXML.Click
        dsNorthwind.WriteXml("C:\DBAzine.xml", XmlWriteMode.DiffGram)
    End Sub
End Class