Can VB scatter to variables like FoxPro?

Question

I need to take some code written in VisualFoxPro and rewrite it in VB. Having no experience with FoxPro I asked about a few commands used in the code and found one of particular use: scatter memvar, which made individual variable from columns in a table. Does VB have an equivalent function, or do I need to create each variable with a Dim statement?

Edit: I should have mentioned that I'm looking to use this to propagate datatables, very sorry. Later in the VFP program insert into memvarmatches the variables to their respective columns. I'm looking to avoid the need of a method along the lines of:

For Each row As DataRow in MyTable
    row.Item(0,i) = myVar1
    row.Item(1,i) = myVar2
    'etc.
    i += 1
Next

Sadly, the above is how I do most of my data entry.

Edit: In response to @DRapp's comment, I am using VB to read a .xin file and form an access data table from its code. There are two "collections" in the .xin file that are of interest to me: <NamedSymbologyCollection> and <FeatureStylesCollection>. Both of these collections are in the same line of the file, so I've written code to go tag-by-tag, pick out the information I want, and add it to a temporary data table.

Do Until reader.EndOfStream
    content = reader.ReadLine
    For Each code In content
        If content.Length > 0 Then
            crntTag = content.Substring(0, content.IndexOf(">") + 1)

            If crntTag.Contains("/FeatureStyleCollection>") Then
                Exit Do
            End If

            If crntTag.Contains("<NamedSymbology ItemName") Then
                wholeTag = GetFullLine(content)

                xinCompile.Rows.Add()
                    For Each entry In wholeTag
                        lcstring = wholeTag.Substring(0, wholeTag.IndexOf(">") + 1)

                        If wholeTag.Length = 0 Then
                            Exit For
                        End If

                        If lcstring.Contains("<NamedSymbology ") Then
                                    SymbName = GrabCodeElement(lcstring, "ItemName=")
                                    SymbDesc = GrabCodeElement(lcstring, "Description=")
                                    wholeTag = wholeTag.Remove(wholeTag.IndexOf(lcstring), wholeTag.IndexOf(">") + 1)
                                    xinCompile.Rows(i).Item("symbName") = SymbName
                                    xinCompile.Rows(i).Item("symbDesc") = SymbDesc
                        ElseIf lcstring.Contains("<BasePointSymbology ") Then
                                    CellLayer = GrabCodeElement(lcstring, "CellLayerName=")
                                    CellName = GrabCodeElement(lcstring, "Name=")
                                    wholeTag = wholeTag.Remove(wholeTag.IndexOf(lcstring), wholeTag.IndexOf(">") + 1)
                                    xinCompile.Rows(i).Item("cellLayer") = CellLayer
                                    xinCompile.Rows(i).Item("cellName") = CellName
                        ElseIf lcstring.Contains("<LineSymbology ") Then
                                    LineSymb = GrabCodeElement(lcstring, "<LineSymbology LayerName=")
                                    LineSymb = LineSymb.Substring(15, LineSymb.Length - 16)
                                    xinCompile.Rows(i).Item("lineSymb") = LineSymb
                                    wholeTag = wholeTag.Remove(wholeTag.IndexOf(lcstring), wholeTag.IndexOf(">") + 1)
                        Else
                                    wholeTag = wholeTag.Remove(wholeTag.IndexOf(lcstring), wholeTag.IndexOf(">") + 1)
                        End If
                    Next
                i += 1
            ElseIf crntTag.Contains("<FeatureStyle ItemName") Then
                wholeTag = GetFullLine(content)
                j = 0

                featStyles.Rows.Add()
                For Each entry In wholeTag
                    lcstring = wholeTag.Substring(0, wholeTag.IndexOf(">") + 1)

                    If wholeTag.Length = 0 Then
                        Exit For
                    End If

                    If lcstring.Contains("<FeatureStyle ") Then
                        SymbName = GrabCodeElement(lcstring, "ItemName=")
                        SymbDesc = GrabCodeElement(lcstring, "Description=")

                            For Each item As DataRow In xinCompile.Rows
                                If SymbName = item.Item("symbName") Then
                                    found = True
                                    Exit For
                                End If
                                j += 1
                            Next

                            If found = True Then
                                wholeTag = wholeTag.Remove(wholeTag.IndexOf(lcstring), wholeTag.IndexOf(">") + 1)
                            Else
                                Exit For
                            End If

                            xinCompile.Rows(j).Item("symbDesc") = SymbDesc
                    ElseIf lcstring.Contains("<SurveyFeature ") Then
                        NumCode = GrabCodeElement(lcstring, "NumericCode=")
                        DTMexclude = GrabCodeElement(lcstring, "ExcludeFromTriangulation=")
                        lineToPrev = GrabCodeElement(lcstring, "LineToPrevious=")
                        featType = GrabCodeElement(lcstring, "FeatureType=")

                        wholeTag = wholeTag.Remove(wholeTag.IndexOf(lcstring), wholeTag.IndexOf(">") + 1)
                        xinCompile.Rows(j).Item("numCode") = NumCode
                        xinCompile.Rows(j).Item("DTMexclude") = DTMexclude
                        xinCompile.Rows(j).Item("lineToPrev") = lineToPrev
                        xinCompile.Rows(j).Item("featType") = featType
                    ElseIf lcstring.Contains("<Attribute ") Then
                        LineLayer = GrabCodeElement(lcstring, "Name=")
                        wholeTag = wholeTag.Remove(wholeTag.IndexOf(lcstring), wholeTag.IndexOf(">") + 1)
                    ElseIf lcstring.Contains("<AlphaCode") Then
                        alphacode = GrabCodeElement(lcstring, "Code=")
                            If IsDBNull(xinCompile.Rows(j).Item("alphaCode")) Then
                                fullAlpha = ""
                                xinCompile.Rows(j).Item("alphaCode") = alphacode
                            Else
                                fullAlpha = xinCompile.Rows(j).Item("alphaCode")
                                xinCompile.Rows(j).Item("alphaCode") = fullAlpha & "," & alphacode
                            End If
                        wholeTag = wholeTag.Remove(wholeTag.IndexOf(lcstring), wholeTag.IndexOf(">") + 1)
                    Else
                        wholeTag = wholeTag.Remove(wholeTag.IndexOf(lcstring), wholeTag.IndexOf(">") + 1)
                    End If
                Next
            End If
            content = content.Remove(0, crntTag.Length)
        Else
            Exit For
        End If
    Next
Loop

If you have any recommendations on how to improve any of the above, please let me know.

Answer 1

First off, I've never dealt with FoxPro or Visual FoxPro, so don't expect what I say is going to work perfectly for every scenario. Looking at this MSDN page, it looks like the to perform a simple scatter command in FoxPro you would use something like this:

SCATTER FIELDS LIKE A*,P* EXCEPT PARTNO* TO myArray

The above looks is just getting all fields that start with A* and P* and is excluding items matching PARTNO* from the results. I think this would be best translated to a using LINQ query which could return an Object() array.

Dim testTable As New DataTable
Dim myArray As Object()

myArray = From rowItem In (From row As DataRow In testTable.Rows Select row.ItemArray) _
          Where (rowItem.ToString.StartsWith("A") Or rowItem.ToString.StartsWith("P")) _
                And Not rowItem.ToString.StartsWith("PARTNO") Select rowItem

If you start getting into the more complicated uses of the scatter command to break up the values into a new object where the object has properties which are the same as the columns in the original table, well the VB equivilent gets much harder because you'll have to make a class that inherits the System.Dynamic.DynamicObject class. So this example from the MSDN page

CREATE TABLE Test FREE ;
   (Object C(10), Color C(16), SqFt n(6,2))

SCATTER MEMVAR BLANK
m.Object="Box"
m.Color="Red"
m.SqFt=12.5
APPEND BLANK
GATHER MEMVAR
BROWSE

Turns into a complicated mess in VB. I'm going to provide a very basic example and it is very bare bones but hopefully it'll give you an idea on where to start.

Imports System.Dynamic

Private Class myDynObj : Inherits DynamicObject
    Private internalDict As Dictionary(Of String, Object)

    Public Sub New()
        internalDict = New Dictionary(Of String, Object)
    End Sub

    Public Sub AddProperty(ByVal PropertyName As String, Optional ByVal Value As Object = Nothing)
        addOrSetProperties(New KeyValuePair(Of String, Object)(PropertyName, Value))
    End Sub

    Public Sub addOrSetProperties(ByVal ParamArray newPropertyValuePairs() As KeyValuePair(Of String, Object))
        For Each kvPair As KeyValuePair(Of String, Object) In newPropertyValuePairs.Where(Function(x) Not IsNothing(x.Key))
            If internalDict.ContainsKey(kvPair.Key) Then
                internalDict.Item(kvPair.Key) = kvPair.Value
            Else
                internalDict.Add(kvPair.Key, kvPair.Value)
            End If
        Next
    End Sub

    Public Overrides Function GetDynamicMemberNames() As IEnumerable(Of String)
        Return internalDict.Keys.ToList.Cast(Of String)()
    End Function

    Public Overrides Function TryGetMember(binder As GetMemberBinder, ByRef result As Object) As Boolean
        Return internalDict.TryGetValue(binder.Name, result)
    End Function

    Public Overrides Function TrySetMember(binder As SetMemberBinder, value As Object) As Boolean
        internalDict(binder.Name) = value
        Return True
    End Function

End Class

And the usage of the above class:

Dim testTable As New DataTable("TheTable")
testTable.Columns.Add("foo")
testTable.Columns.Add("bar")
testTable.Columns.Add("Blargh")

Dim columnNames As New List(Of String)(From column As DataColumn In testTable.Columns Select column.ColumnName)

Dim m As Object = New myDynObj
columnNames.ForEach(Sub(x) DirectCast(m, myDynObj).AddProperty(x))

Console.WriteLine(String.Format("The current properties of the object are as follows: {0}", String.Join(", ", DirectCast(m, myDynObj).GetDynamicMemberNames.Cast(Of String))))
Console.WriteLine()

m.foo = "hoopla" : m.Blargh = 654219 : m.waffles = "I'm New!"

Console.WriteLine("And now the values are:")
DirectCast(m, myDynObj).GetDynamicMemberNames.ToList.ForEach(Sub(x) Console.WriteLine(String.Format("{0}, {1}", x, CallByName(m, x, CallType.Get, Nothing))))
Console.WriteLine()


testTable.Rows.Add(m.foo, m.bar, m.Blargh)
Console.WriteLine("XML result:")
Using sw As New StreamWriter(Console.OpenStandardOutput())
    sw.AutoFlush = True
    Console.SetOut(sw)
    testTable.WriteXml(sw, XmlWriteMode.WriteSchema)
End Using

Console.ReadLine()

Answer 2

The SCATTER command is a relic from Foxpro having no native objectified data. Just access the recordset/dataset directly in dotnet programming.