imports System
imports System.data
imports Microsoft.VisualBasic
imports sql = System.Data.SqlClient

public module MyModule
	Private intIndex as Integer = 0
	Private dv as New DataView

	sub Main
		NorthwindDataSetDataTableLoop()
        AnyKey
	end sub


Private Sub NorthwindDataSetDataTableLoop()
		Dim strSQL as String = "user id=sa;integrated security=SSPI;data source='(local)';Database=Northwind;"

		Dim cn AS new sql.SqlConnection
		cn.ConnectionString = strSQL

		Try
			' Setup Command
			Dim cmd as new sql.SqlCommand
			cmd.CommandText = "SELECT * FROM [Customers] WHERE ContactName LIKE 'A%'"
			cmd.CommandType = CommandType.Text
			cmd.Connection = cn

			Dim da as new sql.SqlDataAdapter
			da.SelectCommand = cmd

			Dim ds as New DataSet
			da.Fill(ds, "MyTableName")
			'Dim dt as DataTable = ds.Tables(0)
			Dim dt as DataTable = ds.Tables("MyTableName")

			Wpad("Customer ID")
			Wpad("Contact Name")
			Wpad("Company Name")
			WL("")
			Wline()
			Wline()
			Wline()
			WL("")
			WL("")

            Dim drNew As DataRow = dt.NewRow
            drNew("ContactName") = "New Name "
            drNew("CompanyName") = "New Co."
			dt.Rows.Add(drNew)

            Dim drChanged As DataRow = dt.Rows(3)
			drChanged.BeginEdit()
            	drChanged("ContactName") = "Manny"
            	drChanged("CompanyName") = "Pep Boys"

            	drChanged = dt.Rows(4)
            	drChanged("ContactName") = "Delete Me"
            	drChanged("CompanyName") = "Delete Me Now"
			drChanged.EndEdit()

			If 1 = 1 then
 		   		Dim drRemove As DataRow = dt.Rows(4)
 				dt.Rows.Remove(drRemove)
			'drRemove.Delete
			End If


			For intIndex = 0 TO dt.rows.count - 1


				Dim dr as DataRow = dt.rows(intIndex)
				Dim strCustomerID As String = dt.rows(intIndex).Item(0).tostring
				Dim strContactName As String = dr.Item("ContactName").tostring
				Dim strCompanyName As String = dt.rows(intIndex).Item("CompanyName").tostring
				Wpad(strCustomerID)
				Wpad(strContactName)
				Wpad(strCompanyName)
				WL("")
			next

			cmd = Nothing
			da = Nothing
			cn.Close()
			cn = Nothing

		Catch ex As Exception 
			WL("Error", ex.Message.tostring)
		End Try
	End Sub

	Sub Wline() ' paramarray 
		Dim str As String  = "--------------------------------------------"
		Dim strOutput AS sTring = Right(str,19).PadRight(20)
		Console.Write(strOutput)
	end sub

	Sub Wpad(str as string) ' paramarray 
		Dim strOutput As String 
		strOutput = Right(str,20).PadRight(20)
		Console.Write(strOutput)
	end sub

	sub WL(strItem as string, objValue as Object) ' paramarray 
		Dim strValue As String = CType(objValue, String)
		Console.WriteLine(strItem & ": [" & strValue & "]")
	end sub

	sub WL(strItem as string) 
		Console.WriteLine(strItem)
	end sub

	sub AnyKey()
		WL("")
		Console.Write("Press enter key to continue...  ")
		RL()
	end Sub


	sub Show(obj as Object)
		Dim str As String = Ctype(obj, String)
		Console.write(str & "... " )
		Console.Write("Press enter key to continue...  ")
		RL()
	end Sub

	sub RL
		Console.Readline()
	End Sub

end module