' File: Console_DataSet_With_Change.VB.txt
' (Re)written  By: Dan Garlen February 2004 and again on 4/15/08

Imports System
Imports System.Data
Imports sql = System.Data.SqlClient

Module Module1
    Private strSQL As String = "SELECT TOP 10 * FROM [Customers] "
    Private strConnectionString = "integrated security=true;Server='(local)';Database=Northwind;"

    Public Sub Main()
        Title("SQL DataAdapter / DataSet Demo")
        ChangeData("ALFKI")
        Footer()
    End Sub

    Public Enum CustomerFields As Integer
        ID = 0
        CompanyName = 1
        ContactName
    End Enum


    Private Sub ChangeData(ByVal strIDSearch)
        Dim i, Records As Integer

        Dim dr As DataRow
        Dim dt As DataTable

        Dim cn As New sql.SqlConnection(strConnectionString)
        Dim da As New sql.SqlDataAdapter(strSQL, cn)
        Dim cb As New sql.SqlCommandBuilder(da)
        Dim ds As New DataSet("myTable")

        Try
            da.Fill(ds, "myTable")

            dt = ds.Tables("myTable")

            Records = dt.Rows.Count
            WL(Records.ToString)

            ShowDataTable(dt)

            Dim strNewCompany As String = Now.ToLongTimeString

            For i = 0 To Records - 1
                dr = dt.Rows(i)

                Dim strID As String = dr.Item(CustomerFields.ID)
                Dim strCompanyName As String = dr.Item("CompanyName")

                Console.Write("Testing: [" + strID + "]  =? [" & strIDSearch & "]")

                If (strID = strIDSearch) Then
                    dr.BeginEdit()
                    dr.Item("CompanyName") = strNewCompany
                    dr.EndEdit()
                    da.Update(dt)
                    Console.WriteLine("  YES!")
                Else
                    Console.WriteLine("  no.")
                End If
            Next
            ShowDataTable(dt)
        Catch ex As Exception
            WL(ex.Message)
        End Try

    End Sub


    'Loop through DataTable and display fields
    Sub ShowDataTable(ByVal dtArg As DataTable)
        WL("")
        Dim dr As DataRow
        For Each dr In dtArg.Rows
            Dim strShowRecord As String
            strShowRecord = dr.Item(CustomerFields.ID)
            strShowRecord &= " = "
            strShowRecord &= dr.Item(CustomerFields.ContactName)
            strShowRecord &= ", "
            strShowRecord &= dr.Item(CustomerFields.CompanyName)
            WL(strShowRecord)
        Next
        WL("")

    End Sub

    Sub Title(ByVal strItem As String)
        Console.WriteLine("Starting: " + strItem + "...")
        WL("")
        AnyKey()
        WL("")
    End Sub

    Sub Footer()
        WL("")
        Console.WriteLine("Execution Complete.")
        WL("")
        AnyKey()
    End Sub

    Sub WL(ByVal strItem As String)
        Console.WriteLine(strItem)
    End Sub

    Sub RL()
        Console.ReadLine()
    End Sub

    Sub SuccessMsg(ByVal msg As String)
        Console.WriteLine("Success: [" + msg + "]")
        Console.ReadLine()
    End Sub

    Sub FailMsg(ByVal msg As String)
        Console.WriteLine("Failure: [" + msg + "]")
        Console.ReadLine()
    End Sub

    Public Sub AnyKey()
        WL("")
        Console.Write("Press enter key to continue...  ")
        RL()
    End Sub

End Module